데이터베이스 질문과 답변
안녕하세요
계층형으로 구성된 데이터 중 중간에 계층이 끊긴 데이터들을 찾아서 끊기지 않게 중간 데이터를 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]
마농님 정말 감사합니다!!
) 버튼을 클릭하여 작성 하시면 됩니다.