[PostgreSQL] generate_series 달력 축으로 빈 날짜를 0으로 채운다

설치·접속: PostgreSQL 설치와 접속

부제: 일별 매출 리포트를 GROUP BY로 뽑았더니 주문 없는 날은 행 자체가 빠져서 그래프에 구멍이 나 있을 때

-- 14일 창에서 몇몇 날짜엔 매출 행이 아예 없다 (검증용 scratch)
CREATE TABLE sales (sold_on date, amount numeric);
INSERT INTO sales VALUES
 ('2026-07-01',120),('2026-07-01',80),('2026-07-02',200),
 ('2026-07-05',50),('2026-07-05',50),('2026-07-05',90),
 ('2026-07-08',300),('2026-07-09',40),('2026-07-13',175);

-- 달력 축을 generate_series로 만들고 LEFT JOIN으로 gap-fill
SELECT d::date AS day,
       coalesce(sum(s.amount), 0) AS revenue
FROM generate_series('2026-07-01'::date, '2026-07-14'::date, interval '1 day') AS d
LEFT JOIN sales s ON s.sold_on = d::date
GROUP BY d ORDER BY d;

    day     | revenue 
------------+---------
 2026-07-01 |     200
 2026-07-02 |     200
 2026-07-03 |       0
 2026-07-04 |       0
 2026-07-05 |     190
 2026-07-06 |       0
 2026-07-07 |       0
 2026-07-08 |     300
 2026-07-09 |      40
 2026-07-10 |       0
 2026-07-11 |       0
 2026-07-12 |       0
 2026-07-13 |     175
 2026-07-14 |       0
(14 rows)

그냥 GROUP BY sold_on 하면 데이터가 있는 날만 나와서 6행이 전부다 — 07-03, 04, 06, 07, 10, 11, 12, 14는 아예 없다. 매출이 0인 날은 "0"이 아니라 "행이 없음"으로 표현되니, 이걸 그대로 차트에 넘기면 없는 날짜가 그냥 건너뛰어져 축이 찌그러진다.

해결은 데이터가 아니라 날짜 축을 먼저 만드는 것이다. generate_series(시작, 끝, interval '1 day')가 14일을 빠짐없이 생성하고, 여기에 매출 테이블을 LEFT JOIN 하면 매칭 안 되는 날은 NULL이 되며, coalesce(sum(...), 0)가 그 NULL을 0으로 메운다. 축이 왼쪽(달력)에서 오니 매출이 없어도 행이 사라지지 않는다. 함정은 JOIN 순서 — 매출 테이블을 왼쪽에 두면 gap-fill이 안 된다. 반드시 달력이 왼쪽(또는 매출이 RIGHT JOIN)이어야 한다.

이렇게도 쓴다

gap-fill한 축 위에서 바로 누적합을 낸다. (조합: window 함수)

SELECT d::date AS day,
       coalesce(sum(s.amount),0) AS rev,
       sum(sum(coalesce(s.amount,0))) OVER (ORDER BY d) AS cumulative
FROM generate_series('2026-07-01'::date,'2026-07-07'::date, interval '1 day') AS d
LEFT JOIN sales s ON s.sold_on = d::date
GROUP BY d ORDER BY d;
-- 07-01 200 200 / 07-02 200 400 / 07-05 190 590 ...

 

step을 '1 week'으로 주면 주 단위 축이 된다. 주별 리포트의 뼈대.

SELECT g::date AS week_start
FROM generate_series('2026-07-01'::date, '2026-07-31'::date, interval '1 week') g;
-- 07-01 / 07-08 / 07-15 / 07-22 / 07-29

 

날짜 대신 시간 축(0~23시)을 만들어 시간대별 트래픽을 0까지 채운다.

SELECT h::int AS hour
FROM generate_series(0, 23) AS h;   -- 여기에 로그를 LEFT JOIN

 

매출을 date_trunc로 일자 정규화해서 타임스탬프 컬럼과 조인한다. (조합: date_trunc)

LEFT JOIN orders o ON date_trunc('day', o.ordered_at)::date = d::date

 

빠진 날을 CROSS JOIN으로 카테고리 × 날짜 격자로 확장한다(제품별 일매출 0 채우기).

SELECT d::date, p.id
FROM generate_series('2026-07-01'::date,'2026-07-07'::date, interval '1 day') d
CROSS JOIN products p;   -- 여기에 매출 LEFT JOIN