[표준 SQL] 퀴즈로 배우는 SQL : 일별 누적 접속자 통계 구하기

퀴즈로 배우는 SQL : 일별 누적 접속자 통계 구하기

퀴즈로 배우는 SQL

일별 누적 접속자 통계 구하기

이번 퀴즈로 배워보는 SQL 시간에는 사용자 접속기록을 분석하여 통계를 구하는 SQL 문제를 풀어본다. 지면 특성상 문제와 정답 그리고 해설이 같이 있다. 진정으로 자신의 SQL 실력을 키우고 싶다면 스스로 문제를 해결한 다음 정답과 해설을 참조하길 바란다. 공부를 잘하는 학생의 문제집은 항상 문제지면의 밑바닥은 까맣지만 정답과 해설지면은 하얗다는 사실을 기억하자.

[문제]

사용자별 접속 기록을 관리하는 <리스트 1>의 원본테이블로부터 사용자 접속 통계 결과인 <리스트 2>를 도출하는 SQL을 작성하세요.

<리스트 1> 원본리스트
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 * FROM t;

[문제설명]

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

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

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

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

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

경축! 아무것도 안하여 에스천사게임즈가 새로운 모습으로 재오픈 하였습니다.
어린이용이며, 설치가 필요없는 브라우저 게임입니다.
https://s1004games.com

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

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

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

<리스트 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>에서 마찬가지로 접속자수인 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>에서는 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>에서는 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 사용
- 분석함수에서 집계함수 함께 사용하기
- 분석함수를 이용해 누적집계 구하기
- 누적집계에서 중복제거를 위한 팁

출처 : 마이크로소프트웨어 9월호

제공 : 데이터 전문가 지식포털 DBguide.net

[출처] https://dataonair.or.kr/db-tech-reference/d-lounge/technical-data/?pageid=11&mod=document&uid=235843