基于PRODUCT表的SQL查询逻辑优化与目标结果实现咨询

现有PRODUCT表 #

业务规则 #

• 每个ProductID对应两个版本：ProductVersion=1（最低版本）和该ProductID下的最高版本；同一ProductID的ReceivedDate始终相同且非空，ResolvedDate可为空。
• DurationInMinutes计算规则：优先取最高版本的ResolvedDate与ReceivedDate的分钟差；若最高版本ResolvedDate为空，则使用最低版本的ResolvedDate；若两者都为空，则设为NULL。
• Resolved字段通常最低版本为0、最高版本为1，但当同一ProductID下两个版本的Resolved均为0时，需强制将最高版本的Resolved视为1。

目标输出表 #

已尝试的SQL（未得到正确结果） #

select 
    ProductID,
    RecordID,
    ReceivedDate,
    case 
        when UrgencyLevel = 0 then UrgencyLevel 
    end as FirstUrgencyLevel,
    case 
        when UrgencyLevel = 1 then UrgencyLevel 
    end as FinalUrgencyLevel,
    case 
        when UrgencyLevel = 0 then InvestigationDate 
    end as FirstInvestigationDate,
    case 
        when UrgencyLevel = 1 then InvestigationDate 
    end as FinalInvestigationDate,
    datediff(minute,ReceivedDate, ResolvedDate) as DurationInMinutes
from 
    PRODUCT

正确的SQL实现 #

WITH ProductVersions AS (
    -- 标记每个ProductID的最低版本（1）和最高版本，同时处理Resolved的特殊情况
    SELECT 
        *,
        CASE 
            WHEN ProductVersion = 1 THEN 'First' 
            WHEN ProductVersion = MAX(ProductVersion) OVER (PARTITION BY ProductID) THEN 'Final'
            ELSE NULL 
        END AS VersionType,
        -- 当同一ProductID下所有Resolved都是0时，最高版本Resolved视为1
        CASE 
            WHEN ProductVersion = MAX(ProductVersion) OVER (PARTITION BY ProductID) 
                 AND SUM(Resolved) OVER (PARTITION BY ProductID) = 0 THEN 1
            ELSE Resolved 
        END AS AdjustedResolved
    FROM PRODUCT
)
-- 聚合最低版本和最高版本的数据
SELECT 
    p1.ProductID,
    p1.RecordID,
    p1.ReceivedDate,
    p1.UrgencyLevel AS FirstUrgencyLevel,
    p2.UrgencyLevel AS FinalUrgencyLevel,
    p1.InvestigationDate AS FirstInvestigationDate,
    p2.InvestigationDate AS FinalInvestigationDate,
    -- 按规则计算DurationInMinutes
    DATEDIFF(MINUTE, p1.ReceivedDate, 
        CASE 
            WHEN p2.ResolvedDate IS NOT NULL THEN p2.ResolvedDate
            ELSE p1.ResolvedDate 
        END) AS DurationInMinutes
FROM ProductVersions p1
JOIN ProductVersions p2 
    ON p1.ProductID = p2.ProductID 
    AND p1.VersionType = 'First' 
    AND p2.VersionType = 'Final';

逻辑说明 #

1. 通过CTE ProductVersions 标记每个ProductID的最低版本（First）和最高版本（Final），并处理Resolved字段的特殊场景。
2. 将标记为First和Final的记录通过ProductID关联，聚合得到目标字段。
3. 严格按照业务规则计算DurationInMinutes，优先使用最高版本的ResolvedDate，为空则 fallback 到最低版本，都为空则返回NULL。

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