如何使用OVER PARTITION BY实现单台设备月度销售累计（月度重置）

原代码问题分析 #

你的原SQL存在两个核心问题：

1. 多余的自连接：通过自连接同设备同日期的行，会导致数据重复，且完全没必要——窗口函数本身就能实现行级累计求和。
2. 分区逻辑缺失月份维度：仅按Machine分区会让累计跨月延续，无法实现月末重置的需求。

正确实现思路 #

要实现每台设备每月内滚动累计，月末自动重置，核心是在窗口函数的PARTITION BY中同时包含设备标识和月份标识，让每个设备的每个单独月份成为独立的累计分区。

修正后的SQL代码（SQL Server） #

SELECT 
    [Device ID],
    Machine,
    CONVERT(date, [Prcd Date]) AS Date,
    LineTotal,
    -- 按「设备+当月第一天」分区，按日期排序实现月度内滚动累计
    SUM(LineTotal) OVER (
        PARTITION BY 
            Machine, 
            DATEFROMPARTS(YEAR([Prcd Date]), MONTH([Prcd Date]), 1)
        ORDER BY [Prcd Date]
    ) AS RollingMonthlySales
FROM vending_machine_sales
-- WHERE [Device ID] = 'VJ300205292'
ORDER BY Machine, Date;

代码说明 #

• 月份分组标识：DATEFROMPARTS(YEAR([Prcd Date]), MONTH([Prcd Date]), 1)生成当前日期所在月份的第一天，确保同一个设备的同一月份数据被分到同一个分区。
• 滚动累计逻辑：SUM(LineTotal) OVER (...)在每个「设备+月份」分区内，按日期从小到大累计求和，进入新月份后，分区变更，累计自动从0开始重置。
• 性能优化：移除了冗余的自连接，避免数据重复，大幅提升查询效率。

扩展场景处理 #

如果你的业务是自定义月度周期（比如每月25日到次月24日），可以调整月份分组标识的计算逻辑，示例如下：

-- 自定义月度周期：每月25日至次月24日
SELECT 
    [Device ID],
    Machine,
    CONVERT(date, [Prcd Date]) AS Date,
    LineTotal,
    SUM(LineTotal) OVER (
        PARTITION BY 
            Machine,
            -- 若日期≥25日，归属到下一个月的分组
            DATEFROMPARTS(
                YEAR(CASE WHEN DAY([Prcd Date]) >=25 THEN DATEADD(MONTH,1,[Prcd Date]) ELSE [Prcd Date] END),
                MONTH(CASE WHEN DAY([Prcd Date]) >=25 THEN DATEADD(MONTH,1,[Prcd Date]) ELSE [Prcd Date] END),
                1
            )
        ORDER BY [Prcd Date]
    ) AS RollingCustomMonthlySales
FROM vending_machine_sales
ORDER BY Machine, Date;

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