SQL如何查询非零Written Premium对应的最新Booking Date

问题根因 #

你现有查询的逻辑是按booking_year分组后，判断整组的written_premium总和不为0，再取分组内的最大booking_date，没有提前过滤单条written_premium为0的记录，所以会把written_premium为0的8/20/21记录纳入最大值计算，返回错误结果。

最优实现方案 #

方案1：仅需返回2021年最新非零的Booking Date（性能最优） #

直接先过滤掉不符合条件的记录，再取最大值即可，无需分组或窗口函数：

SELECT MAX(booking_date) AS [Max Booking Date]
FROM DW.dbo.Table1
WHERE booking_year = '2021'
  AND written_premium <> 0

方案2：需要同时返回对应Written Premium数值 #

直接过滤后按日期倒序取第一条：

SELECT TOP 1 booking_date AS [Max Booking Date], written_premium
FROM DW.dbo.Table1
WHERE booking_year = '2021'
  AND written_premium <> 0
ORDER BY booking_date DESC

方案3：需要按年份分组，返回每年的最新非零记录 #

此时才需要用到PARTITION BY窗口函数：

SELECT booking_year, booking_date AS [Max Booking Date], written_premium
FROM (
    SELECT 
        booking_year,
        booking_date,
        written_premium,
        ROW_NUMBER() OVER (PARTITION BY booking_year ORDER BY booking_date DESC) AS rn
    FROM DW.dbo.Table1
    WHERE written_premium <> 0
) t
WHERE rn = 1
-- 仅需2021年时可添加额外条件：AND booking_year = '2021'

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