[MSSQL] 일별 누적 접속자 통계 구하기 (누적 합계)

 

 

 

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

 

문제

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

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

이 기록을 토대로 일별 접속 현황 통계자료를 작성해 보려고 한다.

접속일자를 기준으로 다음 4가지 통계를 한 화면에 보여주는 쿼리를 작성해보자

 

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

2. 접속자수 : 동일 사용자는 한 번만 카운트한다.

3. 누적접속건수 : 현재일까지의 누적 건수를 카운트한다.

4. 누적접속자수 : 현재일까지의 누적 접속자수를 카운트한다.

 

원본 데이터

결과 데이터

 

 

 

원본 데이터

 

CREATE TABLE t2 (dt VARCHAR(8), id INT)

INSERT INTO t2
SELECT '20150801', 1 id 
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

 

 

 

쿼리

 

WITH T AS (
	SELECT A.dt
		, COUNT(*) AS '접속건수'
		, COUNT(DISTINCT A.id) AS '접속자수'
		, B.신규접속자수
	FROM t2 A
		LEFT JOIN (
			SELECT dt, ISNULL(SUM([신규접속자수]),0) AS 신규접속자수
			FROM (
				SELECT dt
				, (CASE WHEN ROW_NUMBER() OVER(PARTITION BY id ORDER BY dt) = 1 THEN 1 ELSE NULL END) AS '신규접속자수'
				FROM t2
			) A
			GROUP BY dt
		) B ON A.dt = b.dt
	GROUP BY A.dt, B.신규접속자수
)

SELECT A.dt, A.접속건수, A.접속자수
	, SUM(B.접속건수) AS '누적접속건수'
	, SUM(B.신규접속자수) AS '누적접속자수'
FROM T AS A, T AS B
WHERE B.dt <= A.dt
GROUP BY A.dt, A.접속건수, A.접속자수
ORDER BY A.dt

 

 

 

출처

http://www.gurubee.net/lecture/2955