SQL求助：实现各ID各Phase月度时长累计及缺失补全

表结构 #

CREATE TABLE [dbo].[Tab_Status_Test](
    [ID] [int] NULL,
    [Phase] [nvarchar](50) NULL,
    [Phase_duration] [int] NULL,
    [EOM_Date] [date] NULL
) ON [PRIMARY]

测试数据 #

insert into Tab_status_test
(ID ,Phase,Phase_duration, EOM_Date)
values
    ('1' ,'C' , '22','2021/02/28')
    ,('1' ,'A' , '13','2021/03/31')
    ,('1' ,'A' , '5','2021/03/31')
    ,('1' ,'B' , '2','2021/03/31')
    ,('1' ,'B' , '19','2021/04/30')
    ,('1' ,'A' , '3','2021/04/30')
    ,('1' ,'B' , '1','2021/04/30')
    ,('1' ,'A' , '3','2021/04/30')
    ,('1' ,'B' , '22','2021/05/31')
    ,('1' ,'C' , '22','2021/06/30')
    ,('1' ,'D' , '20','2021/07/31')
    ,('1' ,'A' , '2','2021/07/31')
    ,('2' ,'C' , '22','2021/02/28')
    ,('2' ,'A' , '13','2021/03/31')
    ,('2' ,'A' , '5','2021/03/31')
    ,('3' ,'B' , '2','2021/03/31')
    ,('3' ,'B' , '19','2021/04/30')
    ,('2' ,'A' , '3','2021/04/30')
    ,('3' ,'B' , '1','2021/04/30')
    ,('2' ,'A' , '3','2021/04/30')
    ,('2' ,'B' , '22','2021/05/31')
    ,('3' ,'C' , '22','2021/06/30')
    ,('3' ,'D' , '20','2021/07/31')
    ,('3' ,'A' , '2','2021/07/31')

需求说明 #

• 按月份对每个ID的每个Phase的Phase_duration进行累计计算
• 若某月份某ID未出现对应Phase，沿用该ID该Phase上月的累计值
• 确保每个月份都包含过往所有Phase的累计结果（当月有数据则累加）

现有代码 #

WITH Sum_Dur
AS
(
SELECT  ID
          ,EOM_Date
          ,phase
          ,Phase_duration

,LAG(Phase_duration) OVER (Partition BY phase, eom_date ORDER BY phase,eom_date) as PrevEvent
FROM [CM_PT].[dbo].Tab_Status_Test
)

    SELECT *,
    SUM(PrevEvent+Phase_duration) AS SummedCount

     FROM Sum_Dur
     GROUP BY   ID
          ,EOM_Date
          ,phase
          ,Phase_duration
          , PrevEvent

当前问题 #

• 无法计算各Phase的月度累计值
• 无法补全当月未出现的Phase

解决方案 #

思路

1. 生成所有ID、Phase、月份的全组合，确保无遗漏记录
2. 计算每个ID+Phase+月份的当月总时长（无数据则补0）
3. 基于全组合数据，按ID+Phase分组、月份排序，计算累计值

完整SQL代码

-- 提取所有唯一ID、Phase和月份
WITH AllIDs AS (
    SELECT DISTINCT ID FROM Tab_Status_Test
),
AllPhases AS (
    SELECT DISTINCT Phase FROM Tab_Status_Test
),
AllMonths AS (
    SELECT DISTINCT EOM_Date FROM Tab_Status_Test ORDER BY EOM_Date
),
-- 生成ID+Phase+月份的全组合
ID_Phase_Month AS (
    SELECT 
        a.ID,
        p.Phase,
        m.EOM_Date
    FROM AllIDs a
    CROSS JOIN AllPhases p
    CROSS JOIN AllMonths m
),
-- 计算每个ID+Phase+月份的当月总时长
MonthlyDuration AS (
    SELECT 
        ID,
        Phase,
        EOM_Date,
        SUM(ISNULL(Phase_duration, 0)) AS Monthly_Total
    FROM Tab_Status_Test
    GROUP BY ID, Phase, EOM_Date
),
-- 关联全组合与当月时长，补全缺失值为0
CombinedData AS (
    SELECT 
        ipm.ID,
        ipm.Phase,
        ipm.EOM_Date,
        ISNULL(md.Monthly_Total, 0) AS Monthly_Total
    FROM ID_Phase_Month ipm
    LEFT JOIN MonthlyDuration md 
        ON ipm.ID = md.ID 
        AND ipm.Phase = md.Phase 
        AND ipm.EOM_Date = md.EOM_Date
)
-- 计算累计时长
SELECT 
    ID,
    Phase,
    EOM_Date,
    SUM(Monthly_Total) OVER (
        PARTITION BY ID, Phase 
        ORDER BY EOM_Date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS Cumulative_Duration
FROM CombinedData
ORDER BY ID, EOM_Date, Phase;

代码解释

• AllIDs/AllPhases/AllMonths：提取维度数据，为生成全组合做准备
• ID_Phase_Month：通过交叉连接生成所有可能的维度组合，解决缺失Phase的问题
• MonthlyDuration：按维度聚合，得到每个分组的当月总时长
• CombinedData：补全缺失的当月时长为0，保证每个维度组合都有值
• 最后用窗口函数SUM() OVER()实现逐月累计，同时保留所有维度组合的记录

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