如何在数据透视表的聚合函数中添加条件判断？

要实现你需要的逻辑，关键是先锁定每个CaseID+State组合的最新记录，再根据该记录的Cleared状态决定是否保留时间戳，最后进行透视。直接在PIVOT中使用MAX(EventTimeStamp)无法关联对应的Cleared值，所以需要先预处理数据。

完整解决方案SQL #

WITH LatestEvents AS (
    SELECT 
        [CaseID],
        [State],
        -- 按Cleared状态决定是否保留时间戳
        CASE WHEN [Cleared] = 0 THEN [EventTimeStamp] ELSE NULL END AS ValidEventTimeStamp,
        -- 按Case+State分组，倒序取最新记录
        ROW_NUMBER() OVER (PARTITION BY [CaseID], [State] ORDER BY [EventTimeStamp] DESC) AS RowNum
    FROM CaseTracking
)
SELECT 
    [CaseID],
    [OrderReceived],
    [Completed]
    -- 此处添加其余23种状态字段
FROM (
    SELECT 
        [CaseID],
        [State],
        [ValidEventTimeStamp]
    FROM LatestEvents
    WHERE RowNum = 1 -- 仅保留每个Case+State的最新记录
) AS SourceTable
PIVOT (
    MAX(ValidEventTimeStamp)
    FOR [State] IN ([OrderReceived], [Completed]) -- 补充其余23种状态
) AS PivotTable;

逻辑说明 #

1. 预处理最新记录：

   • 使用ROW_NUMBER()窗口函数，按CaseID和State分组，对每组内的记录按EventTimeStamp降序排序，最新的记录会被标记为RowNum=1。
   • 同时用CASE语句提前处理：如果最新记录的Cleared=1，直接将时间戳设为NULL；如果Cleared=0，保留原时间戳。

2. 透视处理：

   • 从预处理结果中筛选出RowNum=1的记录（每个CaseID+State仅一条最新记录），再进行透视。此时MAX(ValidEventTimeStamp)等价于直接取该记录的处理后时间戳，因为每组只有一条数据。

特殊场景适配 #

如果同一CaseID+State下存在多条时间戳完全相同的最新记录，可以将ROW_NUMBER()替换为RANK()。此时如果这些最新记录中存在Cleared=0的条目，MAX(ValidEventTimeStamp)会保留时间戳；只有当所有最新记录都为Cleared=1时，才会返回NULL。

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