데이터베이스 질문과 답변
월별 누적된 인원수를 구하는 쿼리 구하려고 합니다.
1월에는 2명, 2월에는 3명, 3월에는 4명이 나오도록 하려고 합니다.
혹시 윈도우 함수를 사용하면 되려나요?
with tst as ( select '201901' yyyymm, 'A' cust from dual union all select '201901' yyyymm, 'B' cust from dual union all select '201902' yyyymm, 'A' cust from dual union all select '201902' yyyymm, 'B' cust from dual union all select '201902' yyyymm, 'C' cust from dual union all select '201903' yyyymm, 'A' cust from dual union all select '201903' yyyymm, 'D' cust from dual ) select * from tst order by 1 ; YYYYMM COUNT(DISTINCT) ---------- --------------------- 201901 2 --> A,B 201902 3 --> A,B,C 201903 4 --> A,B,C,D
by 생각
[2019.09.20 08:15:46]
잘못 이해했네요 제가 작성한건 펑!^^
by 마농
[2019.09.20 08:18:17]
WITH tst AS
(
SELECT '201901' yyyymm, 'A' cust FROM dual
UNION ALL SELECT '201901', 'B' FROM dual
UNION ALL SELECT '201902', 'A' FROM dual
UNION ALL SELECT '201902', 'B' FROM dual
UNION ALL SELECT '201902', 'C' FROM dual
UNION ALL SELECT '201903', 'A' FROM dual
UNION ALL SELECT '201903', 'D' FROM dual
)
SELECT yyyymm
, SUM(COUNT(*)) OVER(ORDER BY yyyymm) cnt
FROM (SELECT yyyymm, cust
, ROW_NUMBER() OVER(PARTITION BY cust ORDER BY yyyymm) rn
FROM tst
)
WHERE rn = 1
GROUP BY yyyymm
;
-- http://gurubee.net/lecture/2955
by jkson
[2019.09.20 08:26:05]
마농님과 똑같아서 삭제;;
) 버튼을 클릭하여 작성 하시면 됩니다.