[통계 쿼리] 재귀함수를 이용한 시간대별, 요일별, 일별, 월별 집계 쿼리

-나의 상황

: 통계 집계 화면에서 시간대별/요일별/일별/월별 테이블을 보여줘야함.

집계 테이블에 기준 값 별로 데이터가 모두 잘 들어가있으면 그대로 보여주면 되는데..데이터가 있을때만 집계테이블에 넣어주고 있어서 화면에 그대로 뿌려줄 수 없어서 아래 방법을 이용함..

 

- 시간대별

WITH RECURSIVE TIME_TB AS (
 SELECT 0 AS TIME
 UNION ALL
 SELECT TIME + 1 FROM TIME_TB WHERE TIME  <  23
 ) 
 SELECT 
 	CONCAT(LPAD(TIME_TB.TIME, 2, 0), ' ~ ', LPAD(TIME_TB.TIME+1, 2, 0))	AS TIME
 	, TIME_TB.TIME AS TIME2
	, IFNULL(B.합1, 0) 			AS 합1
	, IFNULL(B.합2, 0)			AS 합2
	, IFNULL(B.합3, 0)			AS 합3
	, IFNULL(B.합4, 0)			AS 합4
FROM TIME_TB
LEFT JOIN (
	집계할 테이블
) B
 ON TIME_TB.TIME = B.TIME

시간대별 조회 결과

 

- 요일별

WITH RECURSIVE TIME_TB AS (
 SELECT 1 AS TIME
 UNION ALL
 SELECT TIME + 1 FROM TIME_TB WHERE TIME  <  7
 ) 
 SELECT
 	CASE WHEN TIME_TB.TIME = 1 THEN '일'
	  WHEN TIME_TB.TIME = 2 THEN '월'
	  WHEN TIME_TB.TIME = 3 THEN '화'
	  WHEN TIME_TB.TIME = 4 THEN '수'
	  WHEN TIME_TB.TIME = 5 THEN '목'
	  WHEN TIME_TB.TIME = 6 THEN '금'
	  WHEN TIME_TB.TIME = 7 THEN '토' END AS TIME
FROM TIME_TB
---join 은 생략---

요일별 조회결과

 

- 일별

WITH RECURSIVE TIME_TB AS (
	 SELECT STR_TO_DATE('20230301', '%Y%m%d') AS TIME
	 UNION
	 SELECT DATE_ADD(TIME_TB.TIME, INTERVAL 1 DAY)
	 FROM   TIME_TB
	 WHERE  DATE_ADD(TIME_TB.TIME, INTERVAL 1 DAY)  <=  STR_TO_DATE('20230312', '%Y%m%d')
 ) 
SELECT
	DATE_FORMAT(TIME_TB.TIME, '%Y.%m.%d') AS TIME
FROM TIME_TB;

일별 조회 결과

  

- 월별 

WITH RECURSIVE TIME_TB AS (
	 SELECT STR_TO_DATE('20221001', '%Y%m%d') AS TIME
	 UNION
	 SELECT DATE_ADD(TIME_TB.TIME, INTERVAL 1 MONTH)
	 FROM   TIME_TB
	 WHERE  DATE_ADD(TIME_TB.TIME, INTERVAL 1 MONTH)  <=  STR_TO_DATE('20230331', '%Y%m%d')
	 )
SELECT
 	DATE_FORMAT(TIME_TB.TIME, '%Y.%m') AS TIME						 		
FROM TIME_TB

월별 조회 결과

 

- 나중에 내가 다시 보려고 기록용을 남김....

잘못된 것이 있거나 더 좋은 방법이 있다면 공유해주세요.