배경

서비스를 구축하다보면 일자별 통계를 구현할 상황이 있습니다. 가령 최근 한 주간의 가입된 회원, 작성된 글, 작성된 댓글의 수를 수치화 해야한다고 가정하겠습니다.

TABLE

본 글은 집계쿼리를 작성하는데 포커스가 맞춰져있으므로 테이블간의 세세한 관계는 간소화 하겠습니다. 집계쿼리를 작성할 때 테이블이 3개이상일 확률이 크나 해결방법은 같습니다.

현황

각 테이블의 1주일간 데이터 (2021.06.16 ~ 2021.06.22)

• 게시글BBS의 1주일간 데이터 조회 (3건)

SELECT   TITLE
		,CONTENT
		, DATE_FORMAT(REG_DT, "%Y-%m-%d") AS REG_DT
FROM BBS
WHERE REG_DT BETWEEN DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 7 DAY ), "%Y-%m-%d") AND now();

• 게시글에대한 댓글COMMENT의 1주일간 데이터 조회 (6건)

SELECT   BBS_COMMENT
		,REG_NAME
		, DATE_FORMAT(REG_DT, "%Y-%m-%d") AS REG_DT
FROM `COMMENT`
WHERE REG_DT BETWEEN DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 7 DAY ), "%Y-%m-%d") AND NOW();

• 회원가입에 대한 1주일간의 데이터 조회 (1건)

SELECT   USER_ID
	 	,DATE_FORMAT(REG_DT, "%Y-%m-%d") AS REG_DT
FROM `USER` 
WHERE REG_DT BETWEEN DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 7 DAY ), "%Y-%m-%d") AND NOW();

Idea

1) 서비스는 최근 1주일 (2021.06.16 ~ 2021.06.22) 간 댓글을 작성되지 않은날, 게시글이 작성되지 않은날, 가입자가 존재하지 않는날도 존재하므로 1주간의 날짜를 가지는 임의의 durmmy table이 필요하므로 가상의 table을 조회하는 작업을 실시한다.

SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 0 DAY), "%Y-%m-%d") AS DT
 UNION ALL SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 1 DAY), "%Y-%m-%d")
 UNION ALL SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 2 DAY), "%Y-%m-%d")
 UNION ALL SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 3 DAY), "%Y-%m-%d")
 UNION ALL SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 4 DAY), "%Y-%m-%d")
 UNION ALL SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 5 DAY), "%Y-%m-%d")
 UNION ALL SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 6 DAY), "%Y-%m-%d") 

2) USER, COMMENT, BBS에서 기간별 각 각의 날짜의 투플의 수를 더한 테이블을 조회한다.
이 때 가입은 JN, 댓글은 CM, 글 작성에는 BD 라는 문자열로 라벨링을 한다.

SELECT
	DATE_FORMAT(REG_DT, "%Y-%m-%d") AS DT
	,COUNT(*) AS CNT
	, 'JN' AS GRB
FROM `USER`
GROUP BY DT


UNION ALL 

SELECT 
	 DATE_FORMAT(REG_DT, "%Y-%m-%d") AS DT
	,COUNT(*) AS CNT
	,'BD' AS GRB
FROM BBS
GROUP BY DT

UNION ALL

SELECT 
	DATE_FORMAT(REG_DT, '%Y-%m-%d') AS DT
	,COUNT(*) AS CNT
	,'CM' AS GRB
FROM `COMMENT`
GROUP BY DT

3) 1번 table에 LEFT OUTTER JOIN 을 통해 2번 table을 JOIN -> 최근 1주간의 날짜별 정렬
-> 2번 table에 라벨링한 데이터를 CASE WHEN THEN ELSE END 구문을 통해 카운팅 한다.

 SELECT CRT_DT.DT
	,SUM(CASE WHEN ORN_DT.GRB = 'JN' THEN ORN_DT.CNT ELSE 0 END) AS JN_CNT
	
	,SUM(CASE WHEN ORN_DT.GRB = 'BD' THEN ORN_DT.CNT ELSE 0 END) AS BD_CNT
	,SUM(CASE WHEN ORN_DT.GRB = 'CM' THEN ORN_DT.CNT ELSE 0 END) AS CM_CNT
		
FROM
(
 SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 0 DAY), "%Y-%m-%d") AS DT
 UNION ALL SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 1 DAY), "%Y-%m-%d")
 UNION ALL SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 2 DAY), "%Y-%m-%d")
 UNION ALL SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 3 DAY), "%Y-%m-%d")
 UNION ALL SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 4 DAY), "%Y-%m-%d")
 UNION ALL SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 5 DAY), "%Y-%m-%d")
 UNION ALL SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 6 DAY), "%Y-%m-%d") 

) AS CRT_DT

LEFT OUTER JOIN 

(
SELECT
	DATE_FORMAT(REG_DT, "%Y-%m-%d") AS DT
	,COUNT(*) AS CNT
	, 'JN' AS GRB
FROM `USER`
GROUP BY DT


UNION ALL 

SELECT 
	 DATE_FORMAT(REG_DT, "%Y-%m-%d") AS DT
	,COUNT(*) AS CNT
	,'BD' AS GRB
FROM BBS
GROUP BY DT

UNION ALL

SELECT 
	DATE_FORMAT(REG_DT, '%Y-%m-%d') AS DT
	,COUNT(*) AS CNT
	,'CM' AS GRB
FROM `COMMENT`
GROUP BY DT

) AS ORN_DT


ON CRT_DT.DT = ORN_DT.DT
GROUP BY CRT_DT.DT

마치며

이러한 통계쿼리를 통해 google chart나 여러 오픈소스의 grid등 통해 시각화 할 수 있습니다.