按Segment/Type组合提取各Level最新Med Spend值的SQL实现需求

需求 #

拥有月度历史数据，需针对两个Segment，计算每种Type与Level组合的Med Spend，并创建两个字段，展示各Segment、Type组合下对应Level的最新Med Spend值。

初始SQL代码 #

select Segment, Type, (select max([med spend]) from source where level = 'Gold') as 'Gold Spend',
    (select max([med spend]) from source where level = 'Silver') as 'Silver Spend'
        from source a
where a.date = (select max(b.date) from source b
where b.segment = a.segment and b.type = a.type)

源表 #

期望输出 #

初始SQL的问题 #

1. 子查询未关联维度：两个子查询select max([med spend]) from source where level = 'Gold'没有关联外层的a.Segment和a.Type，取的是全表所有对应Level记录的最大值，而非当前Segment、Type下的最新值。
2. 结果重复：where a.date = (select max(b.date)...)会筛选出每个Segment、Type下所有Level的最新日期记录，导致每个Segment、Type对应两条结果（Gold和Silver各一条），且字段值不符合需求。

修正后的SQL方案 #

方案1：使用窗口函数筛选最新记录 #

WITH latest_data AS (
    SELECT 
        Segment,
        Type,
        Level,
        [Med Spend],
        -- 按Segment、Type、Level分组，日期倒序排序，取第一条为最新记录
        ROW_NUMBER() OVER (PARTITION BY Segment, Type, Level ORDER BY Date DESC) AS rn
    FROM source
)
SELECT 
    Segment,
    Type,
    -- 条件聚合将Level转为列
    MAX(CASE WHEN Level = 'Silver' THEN [Med Spend] END) AS [Silver Spend],
    MAX(CASE WHEN Level = 'Gold' THEN [Med Spend] END) AS [Gold Spend]
FROM latest_data
WHERE rn = 1 -- 只保留最新记录
GROUP BY Segment, Type
ORDER BY Segment, Type;

方案2：先获取最新日期再关联数据 #

WITH latest_dates AS (
    -- 先获取每个Segment、Type的最新日期
    SELECT Segment, Type, MAX(Date) AS latest_date
    FROM source
    GROUP BY Segment, Type
)
SELECT 
    ld.Segment,
    ld.Type,
    -- 关联后用条件聚合转列
    MAX(CASE WHEN s.Level = 'Silver' THEN s.[Med Spend] END) AS [Silver Spend],
    MAX(CASE WHEN s.Level = 'Gold' THEN s.[Med Spend] END) AS [Gold Spend]
FROM latest_dates ld
JOIN source s 
    ON ld.Segment = s.Segment 
    AND ld.Type = s.Type 
    AND ld.latest_date = s.Date
GROUP BY ld.Segment, ld.Type
ORDER BY ld.Segment, ld.Type;

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