PostgreSQL中如何统计指定时段内每日事件数（含无事件日0值）

需求 #

统计指定时段内每日发生的事件数量，必须包含无事件发生的日期（对应值为0）。

尝试的SQL查询 #

SELECT f.day, COALESCE(count(e.*), 0) account
FROM (SELECT d::date day
        FROM generate_series('2023-01-19'::date - '39 day'::interval, '2023-01-19', '1 day'::interval) d) f 
LEFT JOIN events e ON f.day::date = e.event_date::date
WHERE e.event_date BETWEEN ('2023-01-19'::date - interval '39 days') AND '2023-01-19'
GROUP BY f.day
ORDER BY f.day desc

问题现象 #

未得到预期的0值结果：时段共40天，但仅返回31条记录，9个无事件的日期未被显示。

问题原因 #

原查询中，LEFT JOIN后使用WHERE子句过滤e.event_date，会将LEFT JOIN产生的、无匹配事件的行（此时e.event_date为NULL）直接过滤掉，相当于把LEFT JOIN变成了INNER JOIN，因此丢失了无事件的日期记录。

解决方案 #

将WHERE子句中的所有过滤条件（包括事件日期范围、业务过滤条件如e.state = 2 AND e.other_field IS NOT NULL）移至LEFT JOIN的ON子句中，这样过滤逻辑会在关联时执行，不会过滤掉主表（日期生成表）的行。

修改后的关联语句：

LEFT JOIN events e ON f.day::date = e.event_date::date 
    AND e.event_date BETWEEN ('2023-01-19'::date - interval '39 days') AND '2023-01-19'
    AND e.state = 2 
    AND e.other_field IS NOT NULL

最终完整SQL #

SELECT f.day, COALESCE(count(e.*), 0) account
FROM (SELECT d::date day
        FROM generate_series('2023-01-19'::date - '39 day'::interval, '2023-01-19', '1 day'::interval) d) f 
LEFT JOIN events e ON f.day::date = e.event_date::date 
    AND e.event_date BETWEEN ('2023-01-19'::date - interval '39 days') AND '2023-01-19'
    AND e.state = 2 
    AND e.other_field IS NOT NULL
GROUP BY f.day
ORDER BY f.day desc

调整后即可获取包含无事件日期、对应值为0的完整统计结果。

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