SQL查询：如何添加日期累计总和列？求Over/Partition By窗口函数用法

嘿，这个需求很常见，我来帮你快速搞定！你已经写好了每日各班次的统计SQL，现在只需要用窗口函数来计算累计总和就行，其实比你想的简单～

修改后的SQL查询（两种方式） #

方式一：直接在原查询中添加累计列 #

这种方式更简洁，直接在GROUP BY后的SELECT语句中加入窗口函数：

SELECT 
    TO_CHAR(DTTM,'YYYY-MM-DD') as "DATE", 
    COUNT(CASE WHEN TO_CHAR(DTTM, 'HH24:MI') BETWEEN '14:00' AND '22:00' THEN TKTNUM ELSE NULL END) AS "DAYS", 
    COUNT(CASE WHEN TO_CHAR(DTTM, 'HH24:MI') BETWEEN '06:00' AND '14:00' THEN TKTNUM ELSE NULL END) AS "MIDS", 
    COUNT(CASE WHEN TO_CHAR(DTTM, 'HH24:MI') NOT BETWEEN '06:00' AND '22:00' THEN TKTNUM ELSE NULL END) AS "SWINGS", 
    COUNT(TKTNUM) AS "TOTAL",
    -- 新增累计总和列：按日期顺序累加每日TOTAL
    SUM(COUNT(TKTNUM)) OVER (ORDER BY TO_CHAR(DTTM,'YYYY-MM-DD')) AS "CUMULATIVE_TOTAL"
FROM TKTHISTORY 
GROUP BY TO_CHAR(DTTM,'YYYY-MM-DD') 
ORDER BY TO_CHAR(DTTM,'YYYY-MM-DD');

方式二：用CTE（公共表表达式）拆分逻辑 #

如果觉得嵌套函数看着乱，可以用CTE先算出每日统计结果，再在外层计算累计，可读性更强：

WITH daily_tickets AS (
    SELECT 
        TO_CHAR(DTTM,'YYYY-MM-DD') as "DATE", 
        COUNT(CASE WHEN TO_CHAR(DTTM, 'HH24:MI') BETWEEN '14:00' AND '22:00' THEN TKTNUM ELSE NULL END) AS "DAYS", 
        COUNT(CASE WHEN TO_CHAR(DTTM, 'HH24:MI') BETWEEN '06:00' AND '14:00' THEN TKTNUM ELSE NULL END) AS "MIDS", 
        COUNT(CASE WHEN TO_CHAR(DTTM, 'HH24:MI') NOT BETWEEN '06:00' AND '22:00' THEN TKTNUM ELSE NULL END) AS "SWINGS", 
        COUNT(TKTNUM) AS "TOTAL"
    FROM TKTHISTORY 
    GROUP BY TO_CHAR(DTTM,'YYYY-MM-DD')
)
SELECT 
    *,
    SUM("TOTAL") OVER (ORDER BY "DATE") AS "CUMULATIVE_TOTAL"
FROM daily_tickets
ORDER BY "DATE";

查询结果展示 #

执行后你会得到包含累计总和的完整报表：

窗口函数的简单说明 #

这里用到的SUM("TOTAL") OVER (ORDER BY "DATE")逻辑很直观：

• SUM("TOTAL")：指定要累加的列是每日总数TOTAL
• OVER (ORDER BY "DATE")：表示按照日期的顺序，逐行计算从第一行到当前行的累计值
• 如果以后需要按其他维度（比如部门）分组累计，只需要加上PARTITION BY 部门列即可，比如SUM("TOTAL") OVER (PARTITION BY DEPT ORDER BY "DATE")

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