* 구하고자 하는 Data : 전날 18시에 해당되는 Type과 다음날 00시에 해당되는 Type을 비교했을 때(예 : 8/1 18시와 8/2 00시를 비교),
중복값이 있으면 다음날 22시에서는 제외하고, 그 외 모든 Data는 유지한다
구분 | TYPE |
8/1 00시 | A |
8/1 00시 | B |
8/1 00시 | C |
8/1 06시 | C |
8/1 06시 | E |
8/1 06시 | G |
8/1 12시 | A |
8/1 12시 | I |
8/1 12시 | F |
8/1 18시 | B |
8/1 18시 | D |
8/1 18시 | G |
8/2 00시 | B |
8/2 00시 | C |
8/2 00시 | E |
8/2 00시 | H |
8/2 06시 | A |
8/2 06시 | D |
8/2 06시 | J |
8/2 06시 | B |
8/2 12시 | S |
8/2 12시 | E |
8/2 18시 | D |
8/2 18시 | Y |
8/3 00시 | C |
8/3 00시 | E |
8/3 00시 | G |
8/3 00시 | A |
8/3 06시 | I |
8/3 06시 | F |
8/3 06시 | B |
8/3 12시 | D |
8/3 12시 | G |
8/3 18시 | B |
8/3 18시 | C |
8/3 18시 | A |
WITH t AS ( SELECT TO_DATE('2017/08/01 00', 'yyyy/mm/dd hh24') dt, 'A' tp FROM dual UNION ALL SELECT TO_DATE('2017/08/01 00', 'yyyy/mm/dd hh24'), 'B' FROM dual UNION ALL SELECT TO_DATE('2017/08/01 00', 'yyyy/mm/dd hh24'), 'C' FROM dual UNION ALL SELECT TO_DATE('2017/08/01 06', 'yyyy/mm/dd hh24'), 'C' FROM dual UNION ALL SELECT TO_DATE('2017/08/01 06', 'yyyy/mm/dd hh24'), 'E' FROM dual UNION ALL SELECT TO_DATE('2017/08/01 06', 'yyyy/mm/dd hh24'), 'G' FROM dual UNION ALL SELECT TO_DATE('2017/08/01 12', 'yyyy/mm/dd hh24'), 'A' FROM dual UNION ALL SELECT TO_DATE('2017/08/01 12', 'yyyy/mm/dd hh24'), 'I' FROM dual UNION ALL SELECT TO_DATE('2017/08/01 12', 'yyyy/mm/dd hh24'), 'F' FROM dual UNION ALL SELECT TO_DATE('2017/08/01 18', 'yyyy/mm/dd hh24'), 'B' FROM dual UNION ALL SELECT TO_DATE('2017/08/01 18', 'yyyy/mm/dd hh24'), 'D' FROM dual UNION ALL SELECT TO_DATE('2017/08/01 18', 'yyyy/mm/dd hh24'), 'G' FROM dual UNION ALL SELECT TO_DATE('2017/08/02 00', 'yyyy/mm/dd hh24'), 'B' FROM dual -- 제외대상 UNION ALL SELECT TO_DATE('2017/08/02 00', 'yyyy/mm/dd hh24'), 'C' FROM dual UNION ALL SELECT TO_DATE('2017/08/02 00', 'yyyy/mm/dd hh24'), 'E' FROM dual UNION ALL SELECT TO_DATE('2017/08/02 00', 'yyyy/mm/dd hh24'), 'H' FROM dual UNION ALL SELECT TO_DATE('2017/08/02 06', 'yyyy/mm/dd hh24'), 'A' FROM dual UNION ALL SELECT TO_DATE('2017/08/02 06', 'yyyy/mm/dd hh24'), 'D' FROM dual UNION ALL SELECT TO_DATE('2017/08/02 06', 'yyyy/mm/dd hh24'), 'J' FROM dual UNION ALL SELECT TO_DATE('2017/08/02 06', 'yyyy/mm/dd hh24'), 'B' FROM dual UNION ALL SELECT TO_DATE('2017/08/02 12', 'yyyy/mm/dd hh24'), 'S' FROM dual UNION ALL SELECT TO_DATE('2017/08/02 12', 'yyyy/mm/dd hh24'), 'E' FROM dual UNION ALL SELECT TO_DATE('2017/08/02 18', 'yyyy/mm/dd hh24'), 'D' FROM dual UNION ALL SELECT TO_DATE('2017/08/02 18', 'yyyy/mm/dd hh24'), 'Y' FROM dual UNION ALL SELECT TO_DATE('2017/08/03 00', 'yyyy/mm/dd hh24'), 'C' FROM dual UNION ALL SELECT TO_DATE('2017/08/03 00', 'yyyy/mm/dd hh24'), 'E' FROM dual UNION ALL SELECT TO_DATE('2017/08/03 00', 'yyyy/mm/dd hh24'), 'G' FROM dual UNION ALL SELECT TO_DATE('2017/08/03 00', 'yyyy/mm/dd hh24'), 'A' FROM dual UNION ALL SELECT TO_DATE('2017/08/03 06', 'yyyy/mm/dd hh24'), 'I' FROM dual UNION ALL SELECT TO_DATE('2017/08/03 06', 'yyyy/mm/dd hh24'), 'F' FROM dual UNION ALL SELECT TO_DATE('2017/08/03 06', 'yyyy/mm/dd hh24'), 'B' FROM dual UNION ALL SELECT TO_DATE('2017/08/03 12', 'yyyy/mm/dd hh24'), 'D' FROM dual UNION ALL SELECT TO_DATE('2017/08/03 12', 'yyyy/mm/dd hh24'), 'G' FROM dual UNION ALL SELECT TO_DATE('2017/08/03 18', 'yyyy/mm/dd hh24'), 'B' FROM dual UNION ALL SELECT TO_DATE('2017/08/03 18', 'yyyy/mm/dd hh24'), 'C' FROM dual UNION ALL SELECT TO_DATE('2017/08/03 18', 'yyyy/mm/dd hh24'), 'A' FROM dual ) SELECT TO_CHAR(a.dt, 'yyyymmdd') dt , COUNT(DISTINCT a.tp) cnt FROM t a LEFT OUTER JOIN t b ON TO_CHAR(a.dt, 'hh24') = '00' AND TO_CHAR(a.dt - 6/24, 'yyyymmdd hh24') = TO_CHAR(b.dt, 'yyyymmdd hh24') AND a.tp = b.tp WHERE b.tp IS NULL GROUP BY TO_CHAR(a.dt, 'yyyymmdd') ;