좌표를 이용해 부품의 삭제, 생성, 대체를 찾기 0 2 170

by MS [SQL Query] [2019.11.08 10:38:33]


매일 새로운 3D CAD 정보가 입력이 됩니다. 시스템이 변경된 부분을 update해주어야 하는데요.

변경 여부의 판단은 3D정보의 부품 위치가 중요합니다.

신규 위치가 생기면 생성, 기존 위치가 사라지면 삭제, 같은 위치에 다른 부품이 들어오면 대체로 인식합니다. 판단 근거는 기존 부품의 위치 좌표와 x,y,z값이 5mm이내 이면 같은 위치로 인식하는 것입니다.

f_id가 여러번 사용되는 경우도 있습니다.

테이블에 새로운x,y,z좌표를 가지고 있으면 생성입니다

테이블에 있던x,y,z좌표가 사라지면 삭제입니다.

같은데 f_id가 다르면 대체입니다.

first_coordinate 는 전날 정보이고 second_coordinate 은 다음날 정보입니다. 두개를 비교해 차이를 확인하는 것입니다.

WITH first_coordinate AS (           
    SELECT 'A324556'  f_id,   '001' revision,   '123.34533' x, '3458.02354' y, '0.08776' z, '1001' serial from dual union all
    SELECT 'A322557'  f_id,   '001' revision,   '275.85473' x, '234.75547' y, '0.67675' z, '1002' serial from dual union all
    SELECT 'A322117'  f_id,   '001' revision,   '1234.45345' x, '356.76756' y, '0.65447' z, '1003' serial from dual union all
    SELECT 'B456333'  f_id,   '001' revision,   '8737.34552' x, '6785.7555' y, '0.43675' z, '1004' serial from dual union all
    SELECT 'B467889'  f_id,   '001' revision,   '24.45276' x, '7655.67677' y, '0.54564' z, '1005' serial from dual  union all          
    SELECT 'B467889'  f_id,   '001' revision,   '6787.32677' x, '5552.59876' y, '0.56455' z, '1006' serial from dual  union all         
    SELECT 'C436578'  f_id,   '001' revision,   '244.32544' x, '7567.56457' y, '0.98766' z, '1007' serial from dual           
), second_coordinate as (    
    SELECT 'A324556'  f_id,   '001' revision,   '123.34533' x, '3458.02354' y, '0.08776' z, '2001' serial from dual  union all
    SELECT 'A322557'  f_id,   '001' revision,   '278.85473' x, '234.75547' y, '0.67675' z, '2002' serial from dual  union all
    SELECT 'A322117'  f_id,   '001' revision,   '1234.45345' x, '356.76756' y, '0.65447' z, '2003' serial from dual  union all
    SELECT 'B456333'  f_id,   '001' revision,   '8737.34552' x, '6785.7555' y, '0.43675' z, '2004' serial from dual  union all
    SELECT 'B467889'  f_id,   '001' revision,   '24.45276' x, '7655.67677' y, '0.54564' z, '2005' serial from dual  union all          
    SELECT 'B467889'  f_id,   '001' revision,   '6787.32677' x, '5544.54456' y, '0.56455' z, '2006' serial from dual  union all        
    SELECT 'D342566'  f_id,   '001' revision,   '244.32544' x, '7567.56457' y, '0.98766' z, '2007' serial from dual          
);

 

결과 table Result

ACT,  Before,  AFTER
DEL                 1006
ADD                2006
REP   1007       2007 

확인해 보면 1006의 Y좌표가 5mm이상 벗어난 값이 2006에 나타납니다.

그리고 1007, 2007은 좌표가 같은데 부품이 다르기 때문에 대체품으로 나타납니다.

1002와 2002의 x좌표가 다르지만 5mm 범위를 벋어나지 않아서 변경이 아닙니다.

꼭 1001, 2001과 대응관계는 아닙니다. 순서가 바뀔수도 있습니다.

감사합니다.

 

 

by 마농 [2019.11.08 13:22:07]
WITH first_coordinate AS
(
SELECT 'A324556' f_id, '001' revision, 123.34533 x, 3458.02354 y, 0.08776 z, '1001' serial FROM dual
UNION ALL SELECT 'A322557', '001',  275.85473,  234.75547, 0.67675, '1002' FROM dual
UNION ALL SELECT 'A322117', '001', 1234.45345,  356.76756, 0.65447, '1003' FROM dual
UNION ALL SELECT 'B456333', '001', 8737.34552, 6785.75550, 0.43675, '1004' FROM dual
UNION ALL SELECT 'B467889', '001',   24.45276, 7655.67677, 0.54564, '1005' FROM dual
UNION ALL SELECT 'B467889', '001', 6787.32677, 5552.59876, 0.56455, '1006' FROM dual
UNION ALL SELECT 'C436578', '001',  244.32544, 7567.56457, 0.98766, '1007' FROM dual
)
, second_coordinate AS
(
SELECT 'A324556' f_id, '001' revision, 123.34533 x, 3458.02354 y, 0.08776 z, '2001' serial FROM dual
UNION ALL SELECT 'A322557', '001',  278.85473,  234.75547, 0.67675, '2002' FROM dual
UNION ALL SELECT 'A322117', '001', 1234.45345,  356.76756, 0.65447, '2003' FROM dual
UNION ALL SELECT 'B456333', '001', 8737.34552, 6785.75550, 0.43675, '2004' FROM dual
UNION ALL SELECT 'B467889', '001',   24.45276, 7655.67677, 0.54564, '2005' FROM dual
UNION ALL SELECT 'B467889', '001', 6787.32677, 5544.54456, 0.56455, '2006' FROM dual
UNION ALL SELECT 'D342566', '001',  244.32544, 7567.56457, 0.98766, '2007' FROM dual
)
SELECT a.serial bef_ser
     , b.serial aft_ser
     , a.f_id   bef_fid
     , b.f_id   aft_fid
     , CASE WHEN a.f_id != b.f_id THEN 'REP'
            WHEN a.f_id IS NULL   THEN 'ADD'
            WHEN b.f_id IS NULL   THEN 'DEL'
        END act
  FROM first_coordinate a
  FULL OUTER JOIN second_coordinate b
    ON ABS(a.x - b.x) <= 5
   AND ABS(a.y - b.y) <= 5
   AND ABS(a.z - b.z) <= 5
 WHERE LNNVL(a.f_id = b.f_id)
;

 


by MS [2019.11.08 14:16:25]

역시 마농님이시군요. 감사하게 하겠습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입