PostgreSQL中如何在同一行显示每日及年度求和结果？

嘿，我来帮你搞定这个需求！你想要在同一条查询结果里同时拿到每日雷击次数和年度相关的统计值对吧？结合你给出的表结构和现有查询逻辑，我给你调整了两种SQL方案，分别对应不同的年度统计需求：

方案1：每日统计 + 全年总次数 #

如果你需要的是当天的雷击次数和整个年度的总雷击次数（每一行都显示对应年度的总次数），可以用这个查询：

SELECT
    to_char(time, 'MM/DD/YYYY') AS day,
    COUNT(*) AS daily_strikes,
    SUM(COUNT(*)) OVER (PARTITION BY EXTRACT(YEAR FROM time)) AS annual_total_strikes
FROM counties
JOIN ltg_data ON ST_contains(counties.the_geom, ltg_data.ltg_geom)
WHERE 
    cwa = 'MFR'
    -- 筛选当前年度的数据，可根据需求调整时间范围
    AND time >= DATE_TRUNC('year', CURRENT_DATE)
GROUP BY to_char(time, 'MM/DD/YYYY'), EXTRACT(YEAR FROM time)
ORDER BY day;

关键逻辑说明： #

• COUNT(*)：计算当天落在指定区域（cwa='MFR'的counties）内的雷击次数，别名daily_strikes
• SUM(COUNT(*)) OVER (PARTITION BY EXTRACT(YEAR FROM time))：用窗口函数对每年的每日统计值求和，得到该年度的总雷击次数，这样每一行都会显示对应年度的总次数
• GROUP BY：必须包含日期格式化后的字段和年份提取值，因为窗口函数依赖年份分组，同时保证按天聚合统计

方案2：每日统计 + 年初至当日累计次数 #

如果你需要的是当天的雷击次数和从年初到当天的累计雷击次数（逐日累加的数值），可以用这个查询：

SELECT
    to_char(time, 'MM/DD/YYYY') AS day,
    COUNT(*) AS daily_strikes,
    SUM(COUNT(*)) OVER (
        PARTITION BY EXTRACT(YEAR FROM time) 
        ORDER BY to_char(time, 'MM/DD/YYYY')
    ) AS year_to_date_strikes
FROM counties
JOIN ltg_data ON ST_contains(counties.the_geom, ltg_data.ltg_geom)
WHERE 
    cwa = 'MFR'
    AND time >= DATE_TRUNC('year', CURRENT_DATE)
GROUP BY to_char(time, 'MM/DD/YYYY'), EXTRACT(YEAR FROM time)
ORDER BY day;

关键逻辑说明： #

• 相比方案1，这里在窗口函数里增加了ORDER BY to_char(time, 'MM/DD/YYYY')，让统计值按日期顺序累加，最终得到从年初到当前日期的累计雷击次数
• 这个结果里，第一行是年初第一天的次数（累计值等于当日次数），之后每一行的累计值都是之前所有天数的次数之和

额外注意事项： #

• 确保ltg_data.time字段的时区处理和你的查询条件一致，如果原查询里用了now() at time zone...，记得把CURRENT_DATE替换成对应的时区处理逻辑，避免时间范围筛选出错
• 如果你的“年度”不是自然年（比如水文年、财政年），可以修改PARTITION BY的条件，比如按自定义的年度区间划分（例如CASE WHEN EXTRACT(MONTH FROM time) >= 10 THEN EXTRACT(YEAR FROM time)+1 ELSE EXTRACT(YEAR FROM time) END来定义10月起始的水文年）

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