데이터베이스 질문과 답변

끊긴 계층형 데이터 찾기 2

by jps [SQL Query] Oracle Hierarchy Query [2025.01.16 16:50:26]

안녕하세요

계층형으로 구성된 데이터 중 중간에 계층이 끊긴 데이터들을 찾아서 끊기지 않게 중간 데이터를 insert 해주려고 합니다.

끊긴 데이터를 찾아서 입력해야 되는 데이터가 어떤건지 출력 하고 싶습니다.

도움 부탁드립니다.

예시데이터 : 

상품코드 소유자사원코드 변경전사원코드 변경후사원코드 변경시간
PRD_001 EMP_01 EMP_01 EMP_03 2025-01-16 01:00:00
PRD_001 EMP_01 EMP_03 EMP_04 2025-01-16 02:00:00
PRD_001 EMP_01 EMP_07 EMP_08 2025-01-16 03:00:00
PRD_002 EMP_02 EMP_02 EMP_04 2025-01-16 03:00:00
PRD_002 EMP_02 EMP_04 EMP_08 2025-01-16 04:00:00

 

결과 데이터 :

상품코드 변경전사원코드 변경후사원코드
PRD_001 EMP_04 EMP_07

 

by 마농 [2025.01.16 17:41:10] 
WITH t AS
(
SELECT 'PRD_001' cd, 'EMP_01' id, 'EMP_01' pid, 'EMP_03' cid, TO_DATE('2025-01-16 01', 'yyyy-mm-dd hh24') dt FROM dual
UNION ALL SELECT 'PRD_001', 'EMP_01', 'EMP_03', 'EMP_04', TO_DATE('2025-01-16 02', 'yyyy-mm-dd hh24') FROM dual
UNION ALL SELECT 'PRD_001', 'EMP_01', 'EMP_07', 'EMP_08', TO_DATE('2025-01-16 03', 'yyyy-mm-dd hh24') FROM dual
UNION ALL SELECT 'PRD_002', 'EMP_02', 'EMP_02', 'EMP_04', TO_DATE('2025-01-16 03', 'yyyy-mm-dd hh24') FROM dual
UNION ALL SELECT 'PRD_002', 'EMP_02', 'EMP_04', 'EMP_08', TO_DATE('2025-01-16 04', 'yyyy-mm-dd hh24') FROM dual
)
SELECT a.cd
     , a.id
     , b.cid pid
     , a.pid cid
     , a.dt - (a.dt - b.dt) / 2 dt
  FROM (-- 부모가 없는 정보 --
        SELECT a.*
          FROM t a
          LEFT OUTER JOIN t b
            ON a.cd = b.cd
           AND a.pid = b.cid
         WHERE a.id != a.pid
           AND b.cid IS NULL
        ) a
     , (-- 계층 전개 마지막 노드 정보 --
        SELECT *
          FROM t
         WHERE CONNECT_BY_ISLEAF = 1
         START WITH id = pid
         CONNECT BY PRIOR cd = cd
                AND PRIOR cid = pid
        ) b
 WHERE a.cd = b.cd
   AND a.dt > b.dt
;

 

by jps [2025.01.17 10:51:13]

마농님 정말 감사합니다!

실 데이터를 다시 확인 해보니 중간에 끊긴것이 아닌, 시작지점이 없는 데이터도 있는데 이 건들을 찾는 쿼리도 부탁드립니다.

예시데이터 :

상품코드 소유자사원 변경전사원코드 변경후사원코드 변경시간
PRD_003 EMP_01 EMP_03 EMP_04  
PRD_003 EMP_01 EMP_04 EMP_07  
PRD_004 EMP_04 EMP_05 EMP_08  

결과데이터 :

상품코드 소유자사원 변경전사원코드 변경후사원코드
PRD_003 EMP_01 EMP_01 EMP_03
PRD_004 EMP_04 EMP_04 EMP_05

 

by 마농 [2025.01.16 17:42:27]

혹시 두번 이상 끊기는 경우도 발생 가능한지?

by 마농 [2025.01.17 08:04:59] 
WITH t AS
(
SELECT 'PRD_001' cd, 'EMP_01' id, 'EMP_01' pid, 'EMP_03' cid, TO_DATE('2025-01-16 01', 'yyyy-mm-dd hh24') dt FROM dual
UNION ALL SELECT 'PRD_001', 'EMP_01', 'EMP_03', 'EMP_04', TO_DATE('2025-01-16 02', 'yyyy-mm-dd hh24') FROM dual
UNION ALL SELECT 'PRD_001', 'EMP_01', 'EMP_07', 'EMP_08', TO_DATE('2025-01-16 03', 'yyyy-mm-dd hh24') FROM dual
UNION ALL SELECT 'PRD_002', 'EMP_02', 'EMP_02', 'EMP_04', TO_DATE('2025-01-16 03', 'yyyy-mm-dd hh24') FROM dual
UNION ALL SELECT 'PRD_002', 'EMP_02', 'EMP_04', 'EMP_08', TO_DATE('2025-01-16 04', 'yyyy-mm-dd hh24') FROM dual
)
SELECT cd
     , id
     , cid_1 pid
     , pid   cid
     , dt - (dt - dt_1) / 2 dt
  FROM (SELECT cd, id, pid, cid, dt
             , LAG(cid) OVER(PARTITION BY cd ORDER BY dt) cid_1
             , LAG(dt)  OVER(PARTITION BY cd ORDER BY dt) dt_1
          FROM t
        )
 WHERE pid != cid_1
;

 

by 마농 [2025.01.17 11:06:54] 
WITH t AS
(
SELECT 'PRD_001' cd, 'EMP_01' id, 'EMP_01' pid, 'EMP_03' cid, TO_DATE('2025-01-16 01', 'yyyy-mm-dd hh24') dt FROM dual
UNION ALL SELECT 'PRD_001', 'EMP_01', 'EMP_03', 'EMP_04', TO_DATE('2025-01-16 02', 'yyyy-mm-dd hh24') FROM dual
UNION ALL SELECT 'PRD_001', 'EMP_01', 'EMP_07', 'EMP_08', TO_DATE('2025-01-16 03', 'yyyy-mm-dd hh24') FROM dual
UNION ALL SELECT 'PRD_002', 'EMP_02', 'EMP_02', 'EMP_04', TO_DATE('2025-01-16 03', 'yyyy-mm-dd hh24') FROM dual
UNION ALL SELECT 'PRD_002', 'EMP_02', 'EMP_04', 'EMP_08', TO_DATE('2025-01-16 04', 'yyyy-mm-dd hh24') FROM dual
UNION ALL SELECT 'PRD_003', 'EMP_01', 'EMP_03', 'EMP_04', TO_DATE('2025-01-16 02', 'yyyy-mm-dd hh24') FROM dual
UNION ALL SELECT 'PRD_003', 'EMP_01', 'EMP_04', 'EMP_07', TO_DATE('2025-01-16 03', 'yyyy-mm-dd hh24') FROM dual
UNION ALL SELECT 'PRD_004', 'EMP_04', 'EMP_05', 'EMP_08', TO_DATE('2025-01-16 03', 'yyyy-mm-dd hh24') FROM dual
)
SELECT cd
     , id
     , cid_1 pid
     , pid   cid
     , dt - NVL((dt - dt_1) / 2, 0.00001) dt
  FROM (SELECT cd, id, pid, cid, dt
             , LAG(cid, 1, id) OVER(PARTITION BY cd ORDER BY dt) cid_1
             , LAG(dt)  OVER(PARTITION BY cd ORDER BY dt) dt_1
          FROM t
        )
 WHERE pid != cid_1
;

 

by jps [2025.01.17 11:14:46]

마농님 정말 감사합니다!!

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