Postgres中如何引用CTE的min/max值替代子查询实现无间隙日期统计

问题原因 #

你之前的complete_date_series_using_cte执行报错，核心原因是date_range是独立的CTE结果集，没有被加入到当前CTE的查询范围内，无法直接在WHERE子句中引用其字段。由于date_range仅返回单行的起止日期结果，只需要通过关联或者标量子查询的方式引入即可。

两种可行实现方式 #

方式1：CROSS JOIN关联单行CTE（推荐） #

由于date_range仅返回1行结果，使用CROSS JOIN关联不会产生行数膨胀，且print_job的min/max日期仅计算一次，性能最优：

complete_date_series_using_cte AS (
     select date_actual
       from calendar_day
       CROSS JOIN date_range
   where date_actual >= date_range.start_date  
     and date_actual <= date_range.end_date
)

方式2：标量子查询取值 #

如果仅需要引用CTE的单个字段值，也可以直接通过标量子查询从CTE中取值，写法更简洁：

complete_date_series_using_cte AS (
     select date_actual
       from calendar_day
   where date_actual >= (SELECT start_date FROM date_range)
     and date_actual <= (SELECT end_date FROM date_range)
)

完整改写后的查询代码 #

WITH 
daily_status AS (
    select created_dts::date as created_date,
           count(*)                                        AS daily_total,
           count(*) FILTER (where status = 'Error')        AS status_error,
           count(*) FILTER (where status = 'Processing')   AS status_processing,
           count(*) FILTER (where status = 'Aborted')      AS status_aborted,
           count(*) FILTER (where status = 'Done')         AS status_done
     from print_job
     group by created_dts::date
),
date_range AS (
    select min(created_dts::date) AS start_date,
           max(created_dts::date) AS end_date
   from print_job
),
complete_date_series_using_cte AS (
     select date_actual
       from calendar_day
       CROSS JOIN date_range
   where date_actual >= date_range.start_date  
     and date_actual <= date_range.end_date
)
select date_actual,
       coalesce(daily_total,0)        AS total, 
       coalesce(status_error,0)       AS errors,
       coalesce(status_processing,0)  AS processing,
       coalesce(status_aborted,0)     AS aborted,
       coalesce(status_done,0)        AS done
   from complete_date_series_using_cte
left join daily_status
       on daily_status.created_date = complete_date_series_using_cte.date_actual
order by date_actual

扩展说明 #

如果你的环境没有预先建好的calendar_day维度表，也可以直接用Postgres内置的generate_series函数生成连续日期序列，不需要依赖物理表：

complete_date_series_using_cte AS (
     select generate_series::date as date_actual
       from date_range,
            generate_series(start_date, end_date, interval '1 day')
)

内容的提问来源于stack exchange，提问作者Morris de Oryx