[SQL 기초] generate_series (PostgreSQL)

2025.02.24 - [프로그래밍/DB관련 정보] - [SQL] Table of Contents

---

연속된 숫자나 타임스탬프(날짜/시간)의 시리즈(집합)를 동적으로 생성하는 함수입니다. 기본 syntax는 아래와 같습니다. 

generate_series(start, stop, step)

 

예시)

-- 0부터 10까지 2씩 증가하는 짝수 생성
SELECT * FROM generate_series(0, 10, 2) AS even_numbers;

 

-- 2025년 9월 20일부터 5일간의 날짜 생성
SELECT d::date
FROM generate_series(
    '2025-09-20'::timestamp,
    '2025-09-24'::timestamp,
    '1 day'
) AS s(d);

 

일별 매출을 기록하는 테이블이 있지만, 매출이 없는 날은 데이터가 아예 기록되지 않는다고 가정해 봅시다. 이 경우, 한 달 동안 매출이 '0'이었던 날을 찾아 리포트를 만들어야 합니다.

CREATE TABLE daily_sales (
    sale_date  DATE PRIMARY KEY,
    amount     INT
);
INSERT INTO daily_sales VALUES
    ('2025-09-01', 100),
    ('2025-09-02', 150),
    ('2025-09-04', 200),
    ('2025-09-05', 50);
-- 9월 3일 데이터는 누락되어 있음

generate_series로 9월의 모든 날짜를 생성한 후, daily_sales 테이블과 LEFT JOIN합니다.

SELECT
    all_days.d AS "날짜",
    COALESCE(s.amount, 0) AS "매출"
FROM
    generate_series(
        '2025-09-01'::date,
        '2025-09-05'::date,
        '1 day'
    ) AS all_days(d)
LEFT JOIN daily_sales s ON all_days.d = s.sale_date
ORDER BY all_days.d;

 

PostgreSQL아 아닌 다른 DBMS에서는 조금 덜 직관적인 방법들을 사용해야 합니다. 

1) Oracle : connect by level

-- 1부터 5까지 숫자 생성
SELECT LEVEL
FROM   DUAL
CONNECT BY LEVEL <= 5;

 

2) MySQL : 재귀 CTE (Recursive CTE)

-- 1부터 5까지 숫자 생성
WITH RECURSIVE numbers (n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM numbers WHERE n < 5
)
SELECT n FROM numbers;