[PostgreSql]일자별 통계 날짜 생성방법 (GENERATE_SERIES 함수 사용)

통계 데이터 쿼리 만들때 유용하게 사용하자

WITH AA AS
	(
		SELECT TO_CHAR(LOOP_DAY, 'YYYYMMDD') AS STAT_DT
		FROM GENERATE_SERIES(
			((REPLACE('2025-11-18', '-', '')::DATE) - '1MONTH'::INTERVAL)::DATE
			,REPLACE('2025-11-18', '-', '')::DATE
			, '1DAY'::INTERVAL
		) AS LOOP_DAY
	), BB AS (
		SELECT AA.STAT_DT, COALESCE(BB.CNT, 0) AS CNT
		FROM AA LEFT JOIN LATERAL (
			SELECT COUNT(1) AS CNT
			FROM TB_TEST_STAT
			WHERE SUBSTRING(INSERT_DATE, 1, 8) = AA.STAT_DT
			AND STATUS_LG = '01'
		) BB
		ON TRUE
	) 
	SELECT TO_CHAR(STAT_DT::DATE, 'YYYY-MM-DD') AS DAY, CNT FROM BB

쿼리 실행결과

|-----------|----|
|day        |cnt |
|-----------|----|
|2025-10-18 |0   |
|2025-10-19 |0   |
|2025-10-20 |0   |
|2025-10-21 |0   |
|2025-10-22 |0   |
|2025-10-23 |0   |
|2025-10-24 |0   |
|2025-10-25 |0   |
|2025-10-26 |0   |
|2025-10-27 |0   |
|2025-10-28 |0   |
|2025-10-29 |0   |
|2025-10-30 |0   |
|2025-10-31 |0   |
|2025-11-01 |0   |
......이하 생략