꿈꾸는 개발자, DBA 커뮤니티 구루비

좀 막막한 이력 정리 SQL 질문드립니다.

by h2wshot [SQL Query] [2017.11.17 22:35:04]

제목 없음.png (19,962Bytes)

안녕하십니까.

품질이 형편 없는 이력테이블에서 단일 테이블로 이행해야하는데, 답이 안나와서 염치를 무릅쓰고 질문글 올립니다.

<요건>

1. A_ID와 B_ID와 날짜별로 쌓여있는 이력에서 유니크한 개체 데이터 생성
2. A_ID와 B_ID가 연속되는 경우 같은 개체로 간주한다.
3. 같은 개체는 중 최신의 이력으로만 1개의 데이터를 생성.

2번이 조금 설명드리기 어렵지만, 아래 예시로 풀어보겠습니다.

ex) 커뮤니티에서 이름: '홍길동'이 ID:'HGD12' 를 사용하다가,
ID를 'gildong' 으로 변경하였고 다음날 'HONG79'로
변경하였고, 그 다음날 이름을 '홍철수' 로 변경하였지만,
이것을 하나의 개체로 간주한다. (최종데이터 이름: 홍철수, ID : HONG79)

이름, ID, 날짜만 존재
이름	ID	날짜	분석된 활동(실제론 존재하지 않는 컬럼)	구분(존재X)	비고
홍길동	HGD12	20160315	회원가입	개체1
김영희	yh1234	20160315	회원가입	개체2	-> 중간중간 껴있는 데이터
홍길동	gildong	20160316	ID변경	개체1
홍길동	HONG79	20160317	ID변경	개체1
홍철수	HONG79	20160321	같은ID의 이름변경	개체1	-> 홍길동(홍철수)의 최종 데이터는 이것만 가져온다.

현재 날짜별로 쌓이는 이력 테이블이 아니며, 샘플 데이터만 천만건 이상 되는 데이터라 쉽사리 날짜별로 order by 하기도 겁나는 상황입니다만,,, 해결 방법만 있다면 조금씩 나눠서 처리하던지 해봐야할 것 같습니다.

혹시 비슷한 처리경험이나 힌트가 될만한 분석함수, 셀프조인 방법 아시는분 계신지 여쭤봅니다.

편안함 밤 되십쇼.
감사합니다.

-- 두서 없는 질문에 해석하기 어려워, 추가적으로 필요한 요건이 있다면 말씀 부탁드립니다...

샘플 데이터 드립니다.

-- 원본

A_ID	B_ID	날짜	가져올 데이터
adf8	3546	20170513
bi43	6563	20170513	V
ue12	1879	20170513	V
adf8	1265	20170514
adf8	6473	20170514
adf8	1687	20170515
ka64	1687	20170516
yur3	1687	20170517
kg35	1687	20170518
kv23	1687	20170519
av23	1687	20170519
av23	8768	20170520
av23	2413	20170521
av23	4562	20170522
bg25	4562	20170523
cf25	4562	20170523
cf25	7636	20170523	V
fd12	6563	20170524	V
vfsf	4342	20170525
cfdg	4342	20170526	V
av34	1653	20170612
av34	7647	20170613
av34	1767	20170613
cfd1	1767	20170613	V

-- 최종상태

A_ID	B_ID	날짜
bi43	6563	20170513
ue12	1879	20170513
cf25	7636	20170523
fd12	6563	20170524
cfdg	4342	20170526
cfd1	1767	20170613

--샘플 쿼리

SELECT 'adf8' AS A_ID, '3546' AS B_ID, '20170513' AS "날짜" FROM DUAL UNION ALL
SELECT 'bi43' AS A_ID, '6563' AS B_ID, '20170513' AS "날짜" FROM DUAL UNION ALL
SELECT 'ue12' AS A_ID, '1879' AS B_ID, '20170513' AS "날짜" FROM DUAL UNION ALL
SELECT 'adf8' AS A_ID, '1265' AS B_ID, '20170514' AS "날짜" FROM DUAL UNION ALL
SELECT 'adf8' AS A_ID, '6473' AS B_ID, '20170514' AS "날짜" FROM DUAL UNION ALL
SELECT 'adf8' AS A_ID, '1687' AS B_ID, '20170515' AS "날짜" FROM DUAL UNION ALL
SELECT 'ka64' AS A_ID, '1687' AS B_ID, '20170516' AS "날짜" FROM DUAL UNION ALL
SELECT 'yur3' AS A_ID, '1687' AS B_ID, '20170517' AS "날짜" FROM DUAL UNION ALL
SELECT 'kg35' AS A_ID, '1687' AS B_ID, '20170518' AS "날짜" FROM DUAL UNION ALL
SELECT 'kv23' AS A_ID, '1687' AS B_ID, '20170519' AS "날짜" FROM DUAL UNION ALL
SELECT 'av23' AS A_ID, '1687' AS B_ID, '20170519' AS "날짜" FROM DUAL UNION ALL
SELECT 'av23' AS A_ID, '8768' AS B_ID, '20170520' AS "날짜" FROM DUAL UNION ALL
SELECT 'av23' AS A_ID, '2413' AS B_ID, '20170521' AS "날짜" FROM DUAL UNION ALL
SELECT 'av23' AS A_ID, '4562' AS B_ID, '20170522' AS "날짜" FROM DUAL UNION ALL
SELECT 'bg25' AS A_ID, '4562' AS B_ID, '20170523' AS "날짜" FROM DUAL UNION ALL
SELECT 'cf25' AS A_ID, '4562' AS B_ID, '20170523' AS "날짜" FROM DUAL UNION ALL
SELECT 'cf25' AS A_ID, '7636' AS B_ID, '20170523' AS "날짜" FROM DUAL UNION ALL
SELECT 'fd12' AS A_ID, '6563' AS B_ID, '20170524' AS "날짜" FROM DUAL UNION ALL
SELECT 'vfsf' AS A_ID, '4342' AS B_ID, '20170525' AS "날짜" FROM DUAL UNION ALL
SELECT 'cfdg' AS A_ID, '4342' AS B_ID, '20170526' AS "날짜" FROM DUAL UNION ALL
SELECT 'av34' AS A_ID, '1653' AS B_ID, '20170612' AS "날짜" FROM DUAL UNION ALL
SELECT 'av34' AS A_ID, '7647' AS B_ID, '20170613' AS "날짜" FROM DUAL UNION ALL
SELECT 'av34' AS A_ID, '1767' AS B_ID, '20170613' AS "날짜" FROM DUAL UNION ALL
SELECT 'cfd1' AS A_ID, '1767' AS B_ID, '20170613' AS "날짜" FROM DUAL ;

by h2wshot [2017.11.17 22:39:28]

아.. DB 는 ORACLE 입니다.

by h2wshot [2017.11.18 23:47:03]

감사합니다. ㅎㅎ
일단 개발환경은 oracle 엑사 환경이며(실제 트랜잭션 처리량 천만건 이상 커버 가능), 한번말 실행할 이행 SQL이지만, 부담된다는 요지는 셀프조인을 여러번 한다던지... 이런 부분을 최소화 하고 싶다는 의미로 언지드렸었습니다. ^^

좋은 의견 감사합니다.

by 마농 [2017.11.23 19:54:58]

날짜가 동일하네요? 동일 날짜로는 어떤게 먼저인지 알기 어렵습니다.
동일 날짜끼리는 유니크한 정렬 기준이 필요합니다.
예를 들면 날짜가 아닌 일시 기준이라던가 하는...

WITH t AS
(
SELECT 'adf8' a_id, '3546' b_id, '20170513 00' dt FROM dual
UNION ALL SELECT 'bi43', '6563', '20170513 00' FROM dual
UNION ALL SELECT 'ue12', '1879', '20170513 00' FROM dual
UNION ALL SELECT 'adf8', '1265', '20170514 00' FROM dual
UNION ALL SELECT 'adf8', '6473', '20170514 01' FROM dual
UNION ALL SELECT 'adf8', '1687', '20170515 00' FROM dual
UNION ALL SELECT 'ka64', '1687', '20170516 00' FROM dual
UNION ALL SELECT 'yur3', '1687', '20170517 00' FROM dual
UNION ALL SELECT 'kg35', '1687', '20170518 00' FROM dual
UNION ALL SELECT 'kv23', '1687', '20170519 00' FROM dual
UNION ALL SELECT 'av23', '1687', '20170519 01' FROM dual
UNION ALL SELECT 'av23', '8768', '20170520 00' FROM dual
UNION ALL SELECT 'av23', '2413', '20170521 00' FROM dual
UNION ALL SELECT 'av23', '4562', '20170522 00' FROM dual
UNION ALL SELECT 'bg25', '4562', '20170523 00' FROM dual
UNION ALL SELECT 'cf25', '4562', '20170523 01' FROM dual
UNION ALL SELECT 'cf25', '7636', '20170523 02' FROM dual
UNION ALL SELECT 'fd12', '6563', '20170524 00' FROM dual
UNION ALL SELECT 'vfsf', '4342', '20170525 00' FROM dual
UNION ALL SELECT 'cfdg', '4342', '20170526 00' FROM dual
UNION ALL SELECT 'av34', '1653', '20170612 00' FROM dual
UNION ALL SELECT 'av34', '7647', '20170613 00' FROM dual
UNION ALL SELECT 'av34', '1767', '20170613 01' FROM dual
UNION ALL SELECT 'cfd1', '1767', '20170613 02' FROM dual
)
SELECT a.*
  FROM t a
  LEFT OUTER JOIN t b
    ON a.dt < b.dt
   AND (a.a_id = b.a_id OR a.b_id = b.b_id)
 WHERE b.a_id IS NULL
;

그리고 또 한가지.
a_id, b_id 외에 변경 불가능한 id 가 있으면 더 좋겠네요.
a_id, b_id 둘다 변경 가능하므로 실제로 연관이 없는 두 사람이 동일한 a_id 또는 b_id 로 변경한 경우에
두사람이 연관된 것처럼 보일 수 있습니다.

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변