連続生成関数とかいうSQLコマンド

Daily Blogging82日目

月毎のデータを集計したいけど、データがない月もあるんだよなぁ
データがない月は0を出力しておきたいけどなんかいい方法ないかなぁ

generateしちゃえばいいんだよ

そんな時はgenerate_series関数が使えるよ
https://www.postgresql.jp/document/8.2/html/functions-srf.html

こうすると

SELECT generate_series(1, 100);

こんな感じになる

使い方はこう

-- startからstopまでstep刻みで連続する値を生成する --
generate_series(start, stop, step)

stepを指定すると

SELECT generate_series(1, 100, 10);

こうなる

各月の売り上げを出したいとしよう

こういうデータがあったとして

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    date DATE NOT NULL,
    amount INTEGER NOT NULL
);

INSERT INTO sales (date, amount) VALUES
    ('2025-01-15', 5000),
    ('2025-01-20', 3000),
    ('2025-02-10', 7000),
    ('2025-02-25', 4000),
    ('2025-03-05', 6000),
    ('2025-03-15', 8000);

2025年の各月の売り上げを出したいなぁと思ってたとする。

普通に出力するとこうなっちゃう

SELECT 
    date_trunc('month', date) AS month, 
    SUM(amount) AS total_sales
FROM sales
GROUP BY month
ORDER BY month;

売り上げがない月、例えば2025-05も行としては出力して、amountは0にしておきたいっていう時にはこうする

WITH all_months AS (
  SELECT generate_series('2025-01-01'::date, '2025-12-01'::date, '1 month') AS month
)
SELECT 
    all_months.month, 
    COALESCE(SUM(sales.amount), 0) AS total_sales
FROM all_months
LEFT JOIN sales ON date_trunc('month', sales.date) = all_months.month
GROUP BY all_months.month
ORDER BY all_months.month;

これでデータあるなしに関わらず毎月の結果を出力できる