Postgresで歯抜けデータをゼロ埋めする

なにがしたいのか

• 過去30日の日別の売上を集計したい
• 休日は明細データがないケースがある（休日は歯抜けになる）
• 歯抜けの日はゼロ埋めしたい

こういうグラフを作りたい

日付テーブルを用意する

generate_series()がミソ

  SELECT
    days.dt::date as date
  FROM generate_series(
    date_trunc('day', current_date)+'-1 month',
    date_trunc('day', current_date), '1 days') as days(dt)

売上を日別に集計

この時点では、歯抜けがあります。

売上明細テーブル: sales

売上金額はamount、日付をdateとする

SELECT
	date::date AS date
  , SUM(amount) AS total
FROM sales, DATE_TRUNC('day', date) AS date
GROUP BY 1
ORDER BY 1

２つをジョインしてゼロ埋めする

WITH month_calendar AS (
  SELECT
    days.dt::date as date
  FROM generate_series(
    date_trunc('day', current_date)+'-1 month',
    date_trunc('day', current_date), '1 days' ) as days(dt)
  )
, sales_group_by_day AS (
  SELECT
    date::date AS date
    , SUM(amount) AS total
  FROM sales, DATE_TRUNC('day', date) AS date 
  GROUP BY 1
  ORDER BY 1
)
  
SELECT
  m.date
  , COALESCE(s.total, 0) AS total
FROM month_calendar AS m
LEFT JOIN sales_group_by_day s ON s.date = m.date