SQL Azure中LAST_VALUE函数未按预期工作的问题解决

我使用Microsoft SQL Azure (RTM) - 12.0.2000.8，现有一张记录服务商评分日期的表，需要输出2022年4月起每个月每个服务商各一行的数据，显示该日期对应的服务商最新评分。

现有评分表（Ratings） #

我尝试的代码 #

我原本想用LAST_VALUE函数实现，但无法向下填充最新评分：

WITH CTE_Dates AS ( -- 创建日期表，因为评分表不是每个月都有记录
SELECT
EOMONTH([Date], 0) AS [ReportingDate]
FROM [tbl_Dates]
WHERE [Date] >= '2022-04-01'
AND [DATE] <= '2022-08-31'
GROUP BY EOMONTH([Date], 0)
),
CTE_Ratings AS (
SELECT
[ReportingDate]
,[Provider]
,[Rating]
FROM Ratings
)
SELECT
CTE_Dates.[ReportingDate]
,[Provider]
,[Rating]
,LAST_VALUE([Rating]) OVER (PARTITION BY CTE_Dates.[ReportingDate], [Provider] ORDER BY CTE_Dates.[ReportingDate]
        ) AS latest_rating
FROM CTE_Dates
LEFT OUTER JOIN CTE_Ratings
    ON CTE_Dates.[ReportingDate] = CTE_Ratings.[ReportingDate]

当前输出 #

期望输出 #

解决方案 #

原代码存在两个核心问题：

1. 未生成所有服务商与所有月份的组合，导致缺失服务商在无评分月份的行
2. LAST_VALUE的分区和排序逻辑错误，无法跨月份继承最新评分

以下是修正后的SQL代码：

WITH CTE_Dates AS (
    -- 生成2022-04到2022-08的所有月末日期
    SELECT DISTINCT EOMONTH([Date]) AS ReportingDate
    FROM tbl_Dates
    WHERE [Date] >= '2022-04-01' AND [Date] <= '2022-08-31'
),
CTE_Providers AS (
    -- 获取所有唯一的服务商
    SELECT DISTINCT Provider
    FROM Ratings
),
CTE_AllCombinations AS (
    -- 交叉连接生成每个服务商每个月的行
    SELECT 
        d.ReportingDate,
        p.Provider
    FROM CTE_Dates d
    CROSS JOIN CTE_Providers p
),
CTE_RatingsWithMonth AS (
    -- 把原评分表的日期转换为月末日期，方便匹配
    SELECT
        EOMONTH([Date]) AS ReportingDate,
        Provider,
        Rating
    FROM Ratings
)
SELECT
    ac.ReportingDate,
    ac.Provider,
    r.Rating,
    -- 使用LAST_VALUE配合IGNORE NULLS，跨月份填充最新非空评分
    LAST_VALUE(r.Rating) OVER (
        PARTITION BY ac.Provider 
        ORDER BY ac.ReportingDate 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        IGNORE NULLS
    ) AS latest_rating
FROM CTE_AllCombinations ac
LEFT JOIN CTE_RatingsWithMonth r
    ON ac.ReportingDate = r.ReportingDate 
    AND ac.Provider = r.Provider
ORDER BY ac.Provider, ac.ReportingDate;

代码说明 #

1. CTE_Dates：生成所需的所有月末日期，确保覆盖2022-04至2022-08的每个月
2. CTE_Providers：提取所有存在的服务商，避免遗漏
3. CTE_AllCombinations：通过交叉连接生成每个服务商对应每个月份的基础行，保证无缺失
4. CTE_RatingsWithMonth：将原评分表的日期转换为月末格式，和日期表的格式统一
5. 窗口函数：
   • 按服务商分区，确保每个服务商的评分独立计算
   • 按报告日期排序，保证时间顺序正确
   • 使用IGNORE NULLS忽略空值，让LAST_VALUE能获取到之前最近的非空评分
   • 指定ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW确保只取当前行及之前的最新值

如果你的SQL Azure版本不支持IGNORE NULLS（12.0.2000.8版本已支持），可以用MAX() OVER()的替代方案：

-- 替代方案：用MAX窗口函数获取截至当前月份的最新评分
SELECT
    ac.ReportingDate,
    ac.Provider,
    r.Rating,
    MAX(r.Rating) OVER (
        PARTITION BY ac.Provider 
        ORDER BY ac.ReportingDate 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS latest_rating
FROM CTE_AllCombinations ac
LEFT JOIN CTE_RatingsWithMonth r
    ON ac.ReportingDate = r.ReportingDate 
    AND ac.Provider = r.Provider
ORDER BY ac.Provider, ac.ReportingDate;

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