퀴즈로 배우는 SQL
목록
[퀴즈] 일별 누적 접속자 통계 구하기 0 9 99,999+

by 마농 DISTINCT 누적건수 누적집계 누적집계중복제거 분석함수 [2015.12.15]

이번 퀴즈로 배워보는 SQL 시간에는 사용자 접속기록을 분석하여 통계를 구하는 SQL 문제를 풀어본다. 지면 특성상 문제와 정답 그리고 해설이 같이 있다.

진정으로 자신의 SQL 실력을 키우고 싶다면 스스로 문제를 해결한 다음 정답과 해설을 참조하길 바란다. 공부를 잘하는 학생의 문제집은 항상 문제지면의 밑바닥은 까맣지만 정답과 해설지면은 하얗다는 사실을 기억하자.

문제

  • [리스트 1] 원본리스트 
  • CREATE TABLE t
AS
SELECT '20150801' dt, 1 id FROM dual
UNION ALL SELECT '20150801', 2 FROM dual
UNION ALL SELECT '20150801', 1 FROM dual
UNION ALL SELECT '20150802', 1 FROM dual
UNION ALL SELECT '20150802', 2 FROM dual
UNION ALL SELECT '20150802', 2 FROM dual
UNION ALL SELECT '20150803', 3 FROM dual
UNION ALL SELECT '20150804', 4 FROM dual
UNION ALL SELECT '20150804', 1 FROM dual
UNION ALL SELECT '20150805', 1 FROM dual;

SELECT * FROM t;

  • [표 1] 원본테이블
  • DT                       ID
---------------- ----------
20150801                  1
20150801                  2
20150801                  1
20150802                  1
20150802                  2
20150802                  2
20150803                  3
20150804                  4
20150804                  1
20150805                  1

  • [표 2] 결과테이블 
  • DT                 접속건수   접속자수 누적접속건수 누적접속자수
---------------- ---------- ---------- ------------ ------------
20150801                  3          2            3            2
20150802                  3          2            6            2
20150803                  1          1            7            3
20150804                  2          2            9            4
20150805                  1          1           10            4

문제설명

<리스트 1>은 사용자 접속기록을 관리하는 테이블입니다. 사용자가 접속할 때 마다 기록이 되기 때문에 동일 사용자가 하루에 여러번 기록될 수 있습니다. 이 기록을 토대로 일별 접속 현황 통계자료를 작성해야 합니다. 접속일자 기준으로 다음 네 가지 통계를 한 화면에 보여줘야 합니다.

  • 1. 접속건수 : 접속 기록을 일별로 카운트합니다.
  • 2. 접속자수 : 동일 유저는 한번만 카운트 합니다.
  • 3. 누적접속건수 : 현재일자까지의 누적 건수입니다.
  • 4. 누적접속자수 : 현재일자까지의 누적 접속자수입니다.

정답

문제를 스스로 해결해 보셨나요? 이제 정답을 알아보겠습니다.

  • [리스트 2] 정답 리스트
SELECT dt
     , COUNT(*) 접속건수
     , COUNT(DISTINCT id) 접속자수
     , SUM(COUNT(*)) OVER(ORDER BY dt) 누적접속건수
     , SUM(COUNT(x)) OVER(ORDER BY dt) 누적접속자수
  FROM (SELECT dt, id
             , DECODE(
               ROW_NUMBER() OVER(
               PARTITION BY id ORDER BY dt)
               , 1, 1) x
          FROM t
        )
 GROUP BY dt
 ORDER BY dt
;

어떤가요? 여러분이 만들어본 리스트와 같은가요? 틀렸다고 좌절할 필요는 없답니다. 첫 술에 배부를 순 없는 것이니까요. 해설을 꼼꼼히 보고 자신이 잘못한 점을 비교해 보는 것이 더 중요합니다.

해설

이번 문제는 네 가지 서로 다른 통계를 하나로 보여주는 문제입니다. 각각의 건수를 집계하는 방법을 살펴보고 이를 하나로 묵어보도록 하겠습니다. 우선 접속건수부터 살펴볼까요?

  • [리스트 3] 접속건수
  • SELECT dt
     , COUNT(*) 접속건수
  FROM t
 GROUP BY dt
 ORDER BY dt
;

  • [표 3] 접속건수
  • DT                 접속건수
---------------- ----------
20150801                  3
20150802                  3
20150803                  1
20150804                  2
20150805                  1

<리스트 3>은 GROUP BY를 사용한 집계 쿼리의 가장 기본적인 구문입니다. 설명이 필요 없죠. 다음은 접속자수입니다. 접속자 수를 구할 때 중요한 것은 중복 접속자의 제거입니다. 중복제거 키워드인 DISTINCT를 사용해 볼까요?

  • [리스트 4] 접속자수
  • SELECT dt
     , COUNT(*) 접속자수
  FROM (SELECT DISTINCT dt, id
          FROM t
        )
 GROUP BY dt
 ORDER BY dt
;

  • [표 4] 접속자수
  • DT                 접속자수
---------------- ----------
20150801                  2
20150802                  2
20150803                  1
20150804                  2
20150805                  1

<리스트 4>은 인라인 뷰에서 DISTINCT를 이용해 동일일자 동일 ID 중복을 제거한 뒤 집계하는 형식입니다. <리스트 3>과 <리스트 4>를 어떻게 합쳐야 할지 난감합니다.

  • [리스트 5] 접속자수
  • SELECT dt
     , COUNT(DISTINCT id) 접속자수
  FROM t
 GROUP BY dt
 ORDER BY dt
;

  • [표 5] 접속자수
  • DT                 접속자수
---------------- ----------
20150801                  2
20150802                  2
20150803                  1
20150804                  2
20150805                  1

<리스트 5>는 <리스트 4>의 인라인뷰를 없애고, DISTINCT를 집계함수인 COUNT 안에서 사용했습니다. 이렇게 함으로써 SQL 도 간결해지고, <리스트 3>과 자연스럽게 연결이 가능해 졌습니다. 이번에는 누적접속건수를 구해보겠습니다.

  • [리스트 6] 누적접속건수
  • SELECT dt
     , SUM(접속건수) OVER(ORDER BY dt) 누적접속건수
  FROM (SELECT dt
             , COUNT(*) 접속건수
          FROM t
         GROUP BY dt
        )
;

  • [표 6] 누적접속건수
  • DT               누적접속건수
---------------- ------------
20150801                    3
20150802                    6
20150803                    7
20150804                    9
20150805                   10

<리스트 6>에서는 <리스트 3>의 쿼리를 인라인뷰로 하여 바깥쪽에서 분석함수를 사용해 누적건수를 구하고 있습니다.

  • [리스트 7] 누적접속건수
  • SELECT dt
     , SUM(COUNT(*)) OVER(ORDER BY dt) 누적접속건수
  FROM t
 GROUP BY dt
 ORDER BY dt
;

  • [표 7] 누적접속건수
  • DT               누적접속건수
---------------- ------------
20150801                    3
20150802                    6
20150803                    7
20150804                    9
20150805                   10

<리스트 7>에서는 인라인뷰 없이 GROUP BY와 분석함수를 동시에 사용합니다. 분석함수는 SQL구문에서 ORDER BY구문을 제외하고는 가장 마지막 단계에 적용이 되기 때문에 그룹바이의 집계 결과를 그대로 적용 할 수 있는 것입니다. 누적접속자수도 같은 방법으로 구해볼까요.

  • [리스트 8] 잘못된 누적접속자수 
  • SELECT dt
     , COUNT(DISTINCT id) 접속자수
     , SUM(COUNT(DISTINCT id))
       OVER(ORDER BY dt) 누적접속자수
  FROM t
 GROUP BY dt
 ORDER BY dt
;

  • [표 8] 잘못된 누적접속자수
  • DT                 접속자수 누적접속자수
---------------- ---------- ------------
20150801                  2            2
20150802                  2            4
20150803                  1            5
20150804                  2            7
20150805                  1            8

<리스트 8>에서 마찬가지로 접속자수인 COUNT(DISTINCT id)를 분석함수를 통해 누적집계 했습니다. 하지만 <표 8>의 결과를 보면 원하는 결과가 아닙니다. <리스트 7>에서 단순 건수를 구할 때는 누적 결과가 맞았지만, 중복을 제거한 건수를 구할 때는 결과가 틀립니다. 중복 제거된 건수가 그대로 누적된 결과죠.

결과가 틀린 이유는 일별로만 중복제거가 되었기 때문이죠. 다른 일자에 동인 ID가 있어도 그대로 카운트가 되는 것입니다. 즉, 전체 일자에서 ID는 오직 한번만 카운트 해야 합니다. COUNT(DISTINCT id)를 이용할 수 는 없습니다.

  • [리스트 9] ID 별 카운트 대상 확인 
  • SELECT dt
     , id
     , DECODE(
       ROW_NUMBER() OVER(
       PARTITION BY id ORDER BY dt)
       , 1, 1) x
  FROM t
;

  • [표 9] ID 별 카운트 대상 확인
  • DT                       ID          X
---------------- ---------- ----------
20150801                  1          1
20150801                  1
20150802                  1
20150804                  1
20150805                  1
20150801                  2          1
20150802                  2
20150802                  2
20150803                  3          1
20150804                  4          1

<리스트 9>에서는 ROW_NUMBER 분석함수를 이용해 ID별로 순번을 부여했습니다. 그리고 순번이 1인 경우만 1을 표시하도록 했습니다. <표 9>의 결과를 보면 ID별 최초 한건만 1이 표시됩니다. 이 값을 누적하여 카운트 한다면 원하는 결과를 얻을 수 있겠지요.

  • [리스트 10] 누적접속자수 
  • SELECT dt
     , SUM(COUNT(x)) OVER(ORDER BY dt) 누적접속자수
  FROM (SELECT dt, id
             , DECODE(
               ROW_NUMBER() OVER(
               PARTITION BY id ORDER BY dt)
               , 1, 1) x
          FROM t
        )
 GROUP BY dt
 ORDER BY dt
;

  • [표 10] 누적접속자수
  • DT               누적접속자수
---------------- ------------
20150801                    2
20150802                    2
20150803                    3
20150804                    4
20150805                    4

<리스트 10>에서는 ID별 최초 한건을 표시해주는 X값을 카운트하고 이결과를 다시 분석함수로 누적 합산 했습니다. <표 10>의 누적접속자수가 완성되었네요.

이제 개별로 집계했던 <리스트 3>, <리스트 5>, <리스트 7>, <리스트 10>을 하나의 쿼리로 통합시키면 정답리스트가 완성됩니다.

  • [리스트 11] 통합 리스트 
  • SELECT dt
     , COUNT(*) 접속건수
     , COUNT(DISTINCT id) 접속자수
     , SUM(COUNT(*)) OVER(ORDER BY dt) 누적접속건수
     , SUM(COUNT(x)) OVER(ORDER BY dt) 누적접속자수
  FROM (SELECT dt, id
             , DECODE(
               ROW_NUMBER() OVER(
               PARTITION BY id ORDER BY dt)
               , 1, 1) x
          FROM t
        )
 GROUP BY dt
 ORDER BY dt
;

이번 퀴즈로 배우는 SQL 시간에 다룬 내용을 정리해 볼까요?

  • - COUNT 에서 DISTINCT 사용
  • - 분석함수에서 집계함수 함께 사용하기
  • - 분석함수를 이용해 누적집계 구하기
  • - 누적집계에서 중복제거를 위한 팁

- 강좌 URL : http://www.gurubee.net/lecture/2955

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

목록
by 우리집아찌 [2015.12.16 11:32:02] 
/* 실력이 미천하여 ㅜㅜ */

WITH T AS (
SELECT '20150801' dt, 1 id FROM dual
UNION ALL SELECT '20150801', 2 FROM dual
UNION ALL SELECT '20150801', 1 FROM dual
UNION ALL SELECT '20150802', 1 FROM dual
UNION ALL SELECT '20150802', 2 FROM dual
UNION ALL SELECT '20150802', 2 FROM dual
UNION ALL SELECT '20150803', 3 FROM dual
UNION ALL SELECT '20150804', 4 FROM dual
UNION ALL SELECT '20150804', 1 FROM dual
UNION ALL SELECT '20150805', 1 FROM dual
)


SELECT 
       DT , CONN_CNT , ID_CNT
     , SUM(CONN_CNT) OVER(ORDER BY DT ) ACC_CNT
     ,(SELECT COUNT(DISTINCT ID) FROM T BB WHERE BB.DT <= A.DT )
 FROM
  (SELECT DT , COUNT(ID) CONN_CNT , COUNT(DISTINCT ID) ID_CNT  
     FROM T
     GROUP BY DT
  ) A

 

by 꿈꾸는자 [2017.04.21 17:31:30] 
SELECT DT,
       CNN_CNT,
       USER_CNT,
       SUM(CNN_CNT)  OVER(ORDER BY DT RANGE UNBOUNDED PRECEDING) AGG_CNN_CNT,
       SUM(USER_CNT) OVER(ORDER BY DT RANGE UNBOUNDED PRECEDING) AGG_USER_CNT
  FROM(
        SELECT DT, 
               COUNT(*) CNN_CNT, 
               COUNT(DISTINCT ID) USER_CNT
          FROM (
                SELECT '20150801' DT, 1 ID FROM DUAL
                UNION ALL SELECT '20150801', 2 FROM DUAL
                UNION ALL SELECT '20150801', 1 FROM DUAL
                UNION ALL SELECT '20150802', 1 FROM DUAL
                UNION ALL SELECT '20150802', 2 FROM DUAL
                UNION ALL SELECT '20150802', 2 FROM DUAL
                UNION ALL SELECT '20150803', 3 FROM DUAL
                UNION ALL SELECT '20150804', 4 FROM DUAL
                UNION ALL SELECT '20150804', 1 FROM DUAL
                UNION ALL SELECT '20150805', 1 FROM DUAL
          )
        GROUP BY DT
  )

 

by 꿈꾸는자 [2017.04.21 17:33:07] 
DT          CNN_CNT   USER_CNT AGG_CNN_CNT AGG_USER_CNT
-------- ---------- ---------- ----------- ------------
20150801          3          2           3            2
20150802          3          2           6            4
20150803          1          1           7            5
20150804          2          2           9            7
20150805          1          1          10            8
by 꿈꾸는자 [2017.04.21 17:34:14]

일별 누적 접속자 수는 위와 같이 출력이 되어야 하지 않나요? 결과 집합이 잘못된것 같은데요.

by jkson [2017.04.21 19:12:49]

누적접속자수는 새로운 id값이 들어올 때만 올라가는 값입니다.

by 이세나 [2017.06.02 11:08:27]

select t.dt, count(t.id) as "접속건수",
count(distinct t.id) "접속자",
 (select count(distinct(a.id))  from t  a where a.dt = t.dt )   as "접속자수",
 (select count(a.id) from t  a where a.dt <= t.dt )    as "누적접속건수",
 (select count(distinct(a.id))  from t  a where a.dt <= t.dt )   as "누적접속자수"
from t  t
group by t.dt

 

저는 알고 있는 함수들이 없네요.. ㅜㅜ

by 백종현 [2017.09.04 17:02:28] 
WITH T AS (
    SELECT '20150801' DT, 1 ID FROM DUAL
    UNION ALL SELECT '20150801', 2 FROM DUAL
    UNION ALL SELECT '20150801', 1 FROM DUAL
    UNION ALL SELECT '20150802', 1 FROM DUAL
    UNION ALL SELECT '20150802', 2 FROM DUAL
    UNION ALL SELECT '20150802', 2 FROM DUAL
    UNION ALL SELECT '20150803', 3 FROM DUAL
    UNION ALL SELECT '20150804', 4 FROM DUAL
    UNION ALL SELECT '20150804', 1 FROM DUAL
    UNION ALL SELECT '20150805', 1 FROM DUAL)
SELECT
    DT,
    접속건수,
    접속자수,
    누적접속건수,
    SUM(SUM(X)) OVER(ORDER BY DT) 누적접속자수
FROM(
SELECT
    DT,
    SUM(COUNT(DT)) OVER(PARTITION BY DT) 접속건수,
    COUNT(DISTINCT ID) OVER(PARTITION BY DT) 접속자수,
    SUM(COUNT(DT)) OVER(ORDER BY DT) 누적접속건수,
    DECODE(RANK() OVER(PARTITION BY ID ORDER BY DT),1,1,0) X
FROM T
GROUP BY DT,ID)
GROUP BY DT,접속건수,접속자수,누적접속건수;

 

SELECT
    A.DT,
    A.CNN_CNT,
    A.USER_CNT,
    SUM(CNN_CNT) OVER(ORDER BY A.DT RANGE UNBOUNDED PRECEDING) AGG_CNN_CNT,
    SUM(SUM(X)) OVER(ORDER BY A.DT)
FROM
(SELECT
    DT,
    COUNT(*) CNN_CNT,
    COUNT(DISTINCT ID) USER_CNT
FROM T
GROUP BY DT) A,
(
 SELECT
    DT,X
 FROM(
 SELECT
    DT,
    ROW_NUMBER() OVER(PARTITION BY ID ORDER BY DT) X
 FROM T)
 WHERE X = 1
) B 
WHERE A.DT = B.DT(+)
GROUP BY A.DT,A.CNN_CNT,A.USER_CNT;

 

by 봄빛 [2018.10.05 15:29:38] 
--postgreSQL에서 실행했습니다.

WITH data_t (dt,id) AS 
(
   SELECT '20150801', 1 UNION ALL
   SELECT '20150801', 2 UNION ALL
   SELECT '20150801', 1 UNION ALL
   SELECT '20150802', 1 UNION ALL
   SELECT '20150802', 2 UNION ALL
   SELECT '20150802', 2 UNION ALL
   SELECT '20150803', 3 UNION ALL
   SELECT '20150804', 4 UNION ALL
   SELECT '20150804', 1 UNION ALL
   SELECT '20150805', 1 
)
,result_t(dt,cnt_a,cnt_b) AS(
	SELECT dt ,count(distinct id) cnt_a,count(id) AS cnt_b 
	FROM data_t
	GROUP BY ROLLUP(dt)
	order by dt
)
SELECT dt
      ,cnt_a AS "접속자수"
	  ,cnt_b AS "접속건수"
      ,sum(cnt_a) over(order by dt) AS "접속자수누적"
	  ,sum(cnt_b) over(order by dt) AS "접속건수누적"
FROM result_t a
GROUP BY dt,cnt_a,cnt_b
ORDER BY dt

 

by sarahpark [2020.03.03 14:31:31] 
WITH TMP
as (
SELECT '20150801' dt, 1 id FROM dual
UNION ALL SELECT '20150801', 2 FROM dual
UNION ALL SELECT '20150801', 1 FROM dual
UNION ALL SELECT '20150802', 1 FROM dual
UNION ALL SELECT '20150802', 2 FROM dual
UNION ALL SELECT '20150802', 2 FROM dual
UNION ALL SELECT '20150803', 3 FROM dual
UNION ALL SELECT '20150804', 4 FROM dual
UNION ALL SELECT '20150804', 1 FROM dual
UNION ALL SELECT '20150805', 1 FROM dual
)

--
----1. 접속건수 : 접속 기록을 일별로 카운트합니다. 
--SELECT DT, COUNT(*)
--FROM TMP 
--GROUP BY DT
--ORDER BY DT
--;
----2. 접속자수 : 동일 유저는 한번만 카운트 합니다. 
--SELECT DT, COUNT(*)
--FROM (SELECT DISTINCT DT, ID FROM TMP) 
--GROUP BY DT
--ORDER BY DT
--;
--
----3. 누적접속건수 : 현재일자까지의 누적 건수입니다. 
--SELECT DT, COUNT(*)
--, SUM(COUNT(*)) OVER (ORDER BY DT)
--FROM TMP 
--GROUP BY (DT)
--ORDER BY DT
--;
--4. 누적접속자수 : 현재일자까지의 누적 접속자수입니다. 
--SELECT DT, COUNT(*)
--, SUM(COUNT(*)) OVER (ORDER BY DT)
--FROM (SELECT DISTINCT DT, ID FROM TMP) 
--GROUP BY DT
--ORDER BY DT
--;

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입