PostgreSQL中如何获取两个日期间的年度区间并处理无数据返回

实现逻辑 #

• 先生成输入日期覆盖的所有自然年份序列
• 为每个年份计算对应拆分后的实际查询区间（和输入起止日期取交集）
• 左关联业务表统计指标，无匹配数据的区间自动补0

完整SQL示例 #

WITH params AS (
    -- 定义输入的起止日期，支持dd-mm-yyyy格式转换
    SELECT 
        to_date('15-09-2019', 'dd-mm-yyyy') AS fromdate,
        to_date('21-09-2021', 'dd-mm-yyyy') AS todate
),
year_series AS (
    -- 生成覆盖的所有自然年份
    SELECT generate_series(
        EXTRACT(YEAR FROM (SELECT fromdate FROM params))::int,
        EXTRACT(YEAR FROM (SELECT todate FROM params))::int
    ) AS year
),
periods AS (
    -- 计算每个年度的实际拆分区间
    SELECT
        y.year,
        GREATEST(p.fromdate, (y.year || '-01-01')::date) AS period_start,
        LEAST(p.todate, (y.year || '-12-31')::date) AS period_end
    FROM year_series y
    CROSS JOIN params p
)
-- 关联业务表统计数据，空值补0
SELECT
    pe.year,
    to_char(pe.period_start, 'dd-mm-yyyy') AS 区间起始,
    to_char(pe.period_end, 'dd-mm-yyyy') AS 区间结束,
    COALESCE(COUNT(bd.traincount), 0) AS Traincount,
    COALESCE(SUM(bd.tonnage), 0) AS tonnage
FROM periods pe
LEFT JOIN business_data bd -- 替换为实际业务表名
    ON bd.operate_date >= pe.period_start 
    AND bd.operate_date <= pe.period_end -- 替换为实际业务表的日期字段名
GROUP BY pe.year, pe.period_start, pe.period_end
ORDER BY pe.year;

说明 #

1. 需将SQL中的business_data替换为实际业务表名称，operate_date替换为业务表中存储业务发生日期的字段名
2. 若统计逻辑不是简单计数和求和，可自行调整COUNT、SUM聚合规则
3. 最终返回结果会自动包含所有拆分后的年度区间，无业务数据的年份Traincount、tonnage自动返回0

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