SQL Server Sum Over Partition By重置求和问题求助

核心思路 #

要解决这个问题，关键是先把同一个D_building_Id和D_Entity_Id下的行，按「连续的空缺段」分成不同的组——也就是从vacant=0转到vacant>0时，算一个新的组。之后在每个组里，只对vacant>0的行做累加，新组就从1开始重新计算。

具体实现步骤 #

1. 给空缺段打组号：
   统计每个分组（D_building_Id+D_Entity_Id）里，到当前行为止出现过多少行vacant=0。每碰到一行vacant=0，这个统计数就加1。这样连续的vacant>0行会共享同一个组号，新的空缺段会拿到新的组号。

2. 组内累加计算：
   基于上面的组号，在每个(D_building_Id, D_Entity_Id, 组号)的范围内，对vacant>0的行做累加。如果vacant是用来标记空缺的（比如1代表空缺），直接用行号生成递增序列就行；如果是具体数值，就对符合条件的vacant求和。

SQL代码示例 #

假设你的表叫fact_table，包含D_building_Id、D_Entity_Id、vacant，还有用来排序的时间列record_date（必须有排序列才能确定行的先后顺序，不然分组逻辑会混乱）：

WITH grouped_data AS (
    SELECT 
        D_building_Id,
        D_Entity_Id,
        vacant,
        record_date,
        -- 生成组号：每遇到vacant=0，组号加1
        SUM(CASE WHEN vacant = 0 THEN 1 ELSE 0 END) OVER (
            PARTITION BY D_building_Id, D_Entity_Id 
            ORDER BY record_date 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS vacancy_group
    FROM fact_table
)
SELECT 
    D_building_Id,
    D_Entity_Id,
    vacant,
    record_date,
    -- 仅vacant>0时累加，否则显示0或空值
    CASE 
        WHEN vacant > 0 THEN SUM(vacant) OVER (
            PARTITION BY D_building_Id, D_Entity_Id, vacancy_group 
            ORDER BY record_date
        )
        ELSE 0 -- 按需改成NULL也可以
    END AS accumulated_vacant
FROM grouped_data
ORDER BY D_building_Id, D_Entity_Id, record_date;

补充说明 #

• 如果vacant只是标记是否空缺（比如1=空缺，0=非空缺），那累加部分可以换成ROW_NUMBER() OVER (PARTITION BY D_building_Id, D_Entity_Id, vacancy_group ORDER BY record_date)，这样直接生成从1开始的序号，完全符合重置要求。
• 排序列一定要选能准确反映行顺序的字段，比如时间戳、自增ID，不然没法正确识别连续的空缺段。

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