[SQL 문제] 일별 누적 접속자 통계 구하기

[문제]

사용자 접속기록을 관리하는 테이블입니다. 

사용자가 접속할 때 마다 기록이 되기 때문에 동일 사용자가 하루에 여러번 기록될 수 있습니다. 

이 기록을 토대로 일별 접속 현황 통계자료를 작성해야 합니다. 

접속일자 기준으로 다음 네 가지 통계를 한 화면에 보여줘야 합니다.

1. 접속건수 : 접속 기록을 일별로 카운트합니다.

2. 접속자수 : 동일 유저는 한번만 카운트 합니다.

3. 누적접속건수 : 현재일자까지의 누적 건수입니다.

4. 누적접속자수 : 현재일자까지의 누적 접속자수입니다.

[원본 데이터]

CREATE TABLE TEMP
AS
SELECT '20200901' DT, 1 ID FROM DUAL
UNION ALL SELECT '20200901', 2 FROM DUAL
UNION ALL SELECT '20200901', 1 FROM DUAL
UNION ALL SELECT '20200902', 1 FROM DUAL
UNION ALL SELECT '20200902', 2 FROM DUAL
UNION ALL SELECT '20200902', 2 FROM DUAL
UNION ALL SELECT '20200903', 3 FROM DUAL
UNION ALL SELECT '20200904', 4 FROM DUAL
UNION ALL SELECT '20200904', 1 FROM DUAL
UNION ALL SELECT '20200905', 1 FROM DUAL;
 
SELECT *
  FROM TEMP;

[결과 데이터]

[문제 풀이]

-- 접속건수, 접속자수
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;

[참고]

그룹 내 순위(RANK) 관련 함수, 그룹 내 집계(AGGREGATE) 관련 함수, ROW_NUMBER 함수 

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