如何用WITH子句关联generate_series补全缺失日期为NULL

当前查询仅返回有数据的月份（如2021年10-12月），需要调整为返回2021年所有12个月份的数据，无数据的月份对应的sum_estimated和sum_annualiseds字段返回NULL。

原查询语句 #

WITH dailyhh as (
    SELECT distinct on(settlement_period_id) t1.estimated_consumption, t1.settlement_date,t1.annualised_consumption,
        rank() OVER (
            PARTITION BY settlement_period_id
                ORDER BY
                    CASE settlement_period_interval_count
                        WHEN 0 THEN 1
                        ELSE 2
                    END
        )
    FROM consumption_db_schema.halfhourlyconsumption t1
    where t1.mprn = '123456789'
    and t1.settlement_time between '2021-01-01' and '2021-12-31'
    )
SELECT DATE_TRUNC('month', settlement_date) as settle,
    sum(estimated_consumption) as sum_estimated,
    sum(annualised_consumption) as sum_annualiseds
FROM dailyhh
GROUP BY DATE_TRUNC('month', settlement_date)
ORDER BY settle ASC;

预期结果 #

用户尝试的日期序列生成代码 #

SELECT *
FROM  (
   SELECT day::date
   FROM   generate_series(timestamp '2007-12-01'
                        , timestamp '2008-12-01'
                        , interval  '1 month') day
   ) d

核心思路是先生成2021年所有月份的时间序列，再通过左连接关联原查询的聚合结果，确保所有月份被保留，无数据的月份自动填充NULL。

修改后的完整SQL #

WITH dailyhh as (
    SELECT distinct on(settlement_period_id) 
        t1.estimated_consumption, 
        t1.settlement_date,
        t1.annualised_consumption
    FROM consumption_db_schema.halfhourlyconsumption t1
    where t1.mprn = '123456789'
      and t1.settlement_time between '2021-01-01' and '2021-12-31'
    ORDER BY settlement_period_id, -- 补充distinct on要求的分组内排序规则
             CASE settlement_period_interval_count WHEN 0 THEN 1 ELSE 2 END
),
monthly_series as (
    SELECT generate_series(
        timestamp '2021-01-01', 
        timestamp '2021-12-01', 
        interval '1 month'
    ) as settle
)
SELECT 
    ms.settle,
    sum(dh.estimated_consumption) as sum_estimated,
    sum(dh.annualised_consumption) as sum_annualiseds
FROM monthly_series ms
LEFT JOIN (
    SELECT 
        DATE_TRUNC('month', settlement_date) as settle_month,
        estimated_consumption,
        annualised_consumption
    FROM dailyhh
) dh ON ms.settle = dh.settle_month
GROUP BY ms.settle
ORDER BY ms.settle ASC;

关键说明 #

1. 生成完整月份序列：用generate_series生成2021年1月到12月的每个月第一天的时间戳，作为查询的基础维度。
2. 左连接关联数据：将原dailyhh子查询的结果按月份截断后，与生成的月份序列左连接，保证所有月份都被保留。
3. 聚合计算：对左连接后的结果按月份分组求和，无数据的月份sum函数会返回NULL。
4. 修正distinct on语法：原查询中distinct on必须配合ORDER BY指定分组内的排序逻辑（即原rank的规则），否则结果可能不稳定，因此补充了对应的排序字段。

内容的提问来源于stack exchange，提问作者constantlyFlagged