안녕하세요, 질문이 있어 이렇게 글을 남깁니다.
6개월 동안 특정 인물의 방문기록 중에서 한 사람이 방문한 횟수가 총 9번이고,
1월부터 6월까지의 기간 중, 1월에 2번, 4월에 3번, 6월에 4번 방문했다고 가정했을 때,
1월 1일부터 30일 간격으로 distinct해서, 총 3번 방문한 것으로 카운트를 내고자 합니다.
오라클 쿼리로 갖은 함수를 다 찾아보고 있는데 어떻게 풀어나가야 할 지 감이 오질 않습니다.
인터벌 별로 디스팅트 하는 것이 가능한지, 이와 연관있는 함수가 있는지 궁금합니다.
고수님 들의 의견이나 힌트 부탁드립니다. ㅠㅠ
WITH TB_SAMPLE AS ( SELECT 'TEST' ID, '20140102' VDATE FROM DUAL UNION ALL SELECT 'TEST' ID, '20140106' VDATE FROM DUAL UNION ALL SELECT 'TEST' ID, '20140401' VDATE FROM DUAL UNION ALL SELECT 'TEST' ID, '20140403' VDATE FROM DUAL UNION ALL SELECT 'TEST' ID, '20140410' VDATE FROM DUAL UNION ALL SELECT 'TEST' ID, '20140602' VDATE FROM DUAL UNION ALL SELECT 'TEST' ID, '20140612' VDATE FROM DUAL UNION ALL SELECT 'TEST' ID, '20140615' VDATE FROM DUAL UNION ALL SELECT 'TEST' ID, '20140624' VDATE FROM DUAL ) SELECT SUBSTR(VDATE,1,6) FROM TB_SAMPLE WHERE ID ='TEST' AND VDATE BETWEEN '20140101' AND '20140630' GROUP BY SUBSTR(VDATE,1,6)
WITH t AS ( SELECT 1 id, '20140101' dt FROM dual UNION ALL SELECT 1, '20140201' FROM dual UNION ALL SELECT 1, '20140301' FROM dual UNION ALL SELECT 1, '20140401' FROM dual UNION ALL SELECT 1, '20140501' FROM dual UNION ALL SELECT 1, '20140601' FROM dual UNION ALL SELECT 1, '20140701' FROM dual UNION ALL SELECT 2, '20140101' FROM dual UNION ALL SELECT 2, '20140111' FROM dual UNION ALL SELECT 2, '20140401' FROM dual UNION ALL SELECT 2, '20140411' FROM dual UNION ALL SELECT 2, '20140421' FROM dual UNION ALL SELECT 2, '20140601' FROM dual UNION ALL SELECT 2, '20140610' FROM dual UNION ALL SELECT 2, '20140620' FROM dual UNION ALL SELECT 2, '20140630' FROM dual ) -- 10G MODEL -- SELECT COUNT(DISTINCT p_dt) cnt FROM (SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY dt) rn , TO_DATE(dt, 'yyyymmdd') dt FROM t WHERE id = 2 ) MODEL DIMENSION BY (rn) MEASURES(dt, dt p_dt) RULES AUTOMATIC ORDER ( p_dt[rn > 1] = CASE WHEN dt[CV()] - p_dt[CV()-1] > 30 THEN dt[CV()] ELSE p_dt[CV()-1] END ) ) ;
WITH t AS ( SELECT 1 id, '20140101' dt FROM dual UNION ALL SELECT 1, '20140201' FROM dual UNION ALL SELECT 1, '20140301' FROM dual UNION ALL SELECT 1, '20140401' FROM dual UNION ALL SELECT 1, '20140501' FROM dual UNION ALL SELECT 1, '20140601' FROM dual UNION ALL SELECT 1, '20140701' FROM dual UNION ALL SELECT 2, '20140101' FROM dual UNION ALL SELECT 2, '20140111' FROM dual UNION ALL SELECT 2, '20140401' FROM dual UNION ALL SELECT 2, '20140411' FROM dual UNION ALL SELECT 2, '20140421' FROM dual UNION ALL SELECT 2, '20140601' FROM dual UNION ALL SELECT 2, '20140610' FROM dual UNION ALL SELECT 2, '20140620' FROM dual UNION ALL SELECT 2, '20140630' FROM dual ) -- 11G Recursive SQL -- , t1 AS ( SELECT ROW_NUMBER() OVER(ORDER BY dt) rn , dt FROM t WHERE id = 1 ) , t2(rn, dt, p_dt) AS ( SELECT rn, dt , dt p_dt FROM t1 WHERE rn = 1 UNION ALL SELECT a.rn, a.dt , CASE WHEN TO_DATE(a.dt, 'yyyymmdd') - TO_DATE(b.p_dt, 'yyyymmdd') > 30 THEN a.dt ELSE b.p_dt END p_dt FROM t1 a , t2 b WHERE a.rn = b.rn + 1 ) SELECT COUNT(DISTINCT p_dt) cnt FROM t2 ;