[SQL 문제] 일별 누적 접속자 통계 구하기
https://tychejin.tistory.com/203
[SQL 문제] 일별 누적 접속자 통계 구하기
[문제]사용자 접속기록을 관리하는 테이블입니다. 사용자가 접속할 때 마다 기록이 되기 때문에 동일 사용자가 하루에 여러번 기록될 수 있습니다. 이 기록을 토대로 일별 접속 현황 통계자료를
tychejin.tistory.com
-- 접속건수, 접속자수SELECT DT AS 접속일자 , COUNT(DT) AS 접속건수 , COUNT(DISTINCT ID) AS 접속자수 FROM TEMP GROUP BY DT ORDER BY DT ASC; -- 누적접속건수SELECT T1.* , SUM(DT_COUNT) OVER(ORDER BY DT) AS 누적접속건수 FROM (SELECT DT , COUNT(*) AS DT_COUNT FROM TEMP GROUP BY DT ) T1 ORDER BY T1.DT ASC; SELECT DT , SUM(COUNT(*)) OVER(ORDER BY DT) AS 누적접속건수 FROM TEMP GROUP BY DT ORDER BY DT ASC; -- 접속건수, 접속자수, 누적접속건수 SELECT DT AS 접속일자 , COUNT(DT) AS 접속건수 , COUNT(DISTINCT ID) AS 접속자수 , SUM(COUNT(*)) OVER(ORDER BY DT) AS 누적접속건수 FROM TEMP GROUP BY DT ORDER BY DT ASC; -- 잘못된 누적접속자수 -- 다음 쿼리는 누적접속자수를 카운트 할 때 해당 일자에서만 중복을 제거 했기 때문에, 다른 일자에 동일한 ID가 존재하면 카운트가 증가힙니다.-- 전체 일자에서 ID를 한번만 카운트 하기 위해서는 다음 쿼리는 사용할 수 없습니다.SELECT DT AS 접속일자 , COUNT(DISTINCT ID) AS 접속자수 , SUM(COUNT(DISTINCT ID)) OVER(ORDER BY DT) AS 누적접속자수 FROM TEMP GROUP BY DT ORDER BY DT ASC; -- ID별 카운드 대상 확인-- ROW_NUMBER 분석함수를 이용해 ID별로 순번을 부여한 후 순번이 1인 경우만 1을 표시SELECT DT , ID , DECODE(ROW_NUMBER() OVER(PARTITION BY ID ORDER BY DT), 1, 1) X FROM TEMP; -- 누적접속자수SELECT T1.DT , SUM(COUNT(X)) OVER(ORDER BY T1.DT) AS 누적접속자수 FROM (SELECT DT , ID , DECODE(ROW_NUMBER() OVER(PARTITION BY ID ORDER BY DT), 1, 1) X FROM TEMP ) T1 GROUP BY T1.DT ORDER BY T1.DT; -- 접속건수, 접속자수, 누적접속건수, 누적접속자수SELECT T1.DT , COUNT(DT) AS 접속건수 , COUNT(DISTINCT ID) AS 접속자수 , SUM(COUNT(*)) OVER(ORDER BY DT) AS 누적접속건수 , SUM(COUNT(X)) OVER(ORDER BY T1.DT) AS 누적접속자수 FROM (SELECT DT , ID , DECODE(ROW_NUMBER() OVER(PARTITION BY ID ORDER BY DT), 1, 1) X FROM TEMP ) T1 GROUP BY T1.DT ORDER BY T1.DT;
