SQL Server：按特许经营商获取最新交易数据并去重需求

当前查询结果 #

ConcessionaireId    EndReading  TransactionDate
               1    3606        9/1/2022
               1    3586        8/1/2022
               1    3565        7/1/2022
               2    4174        9/1/2022
               2    4163        8/1/2022
               2    4151        7/1/2022
               3    7125        9/1/2022
               3    7090        8/1/2022
               3    7051        7/1/2022
               4    6019        9/1/2022
               4    5992        8/1/2022
               4    5963        7/1/2022
               5    3165        9/1/2022
               5    3151        8/1/2022
               5    3139        7/1/2022

期望结果 #

ConcessionaireId    EndReading  TransactionDate
               1    3606        9/1/2022
               2    4174        9/1/2022
               3    7125        9/1/2022
               4    6019        9/1/2022
               5    3165        9/1/2022

原SQL语句 #

SELECT top 100 percent
 ConcessionaireId, EndReading, FORMAT(TransactionDate, 'MM/dd/yyyy') AS 'TransactionDate'
 FROM Transactions
 WHERE  EXISTS (SELECT * FROM Transactions
 GROUP BY ConcessionaireId, Particulars) 
 AND ConcessionaireId IS NOT NULL AND Particulars = 'Billing'
 GROUP BY ConcessionaireId, EndReading, TransactionDate
 HAVING ConcessionaireId > 0
 ORDER BY ConcessionaireId,  MAX(CONVERT(int,(FORMAT(TransactionDate,'yyyyMMdd')))) DESC

问题分析 #

原SQL存在几个无效或低效的点：

• EXISTS子句完全冗余，内部的分组查询没有起到任何过滤作用
• 按ConcessionaireId, EndReading, TransactionDate分组会把每一行都当成独立分组，无法筛选出每个特许经营商的最新记录
• TOP 100 PERCENT搭配排序并不能实现只取每组第一条的效果
• 日期转换的方式复杂且没必要，直接用日期字段排序即可

优化后的SQL方案 #

用窗口函数ROW_NUMBER()是实现这类需求的高效方案：

SELECT 
    ConcessionaireId,
    EndReading,
    FORMAT(TransactionDate, 'MM/dd/yyyy') AS TransactionDate
FROM (
    SELECT 
        ConcessionaireId,
        EndReading,
        TransactionDate,
        -- 按特许经营商分组，交易日期倒序编号，最新记录编号为1
        ROW_NUMBER() OVER (PARTITION BY ConcessionaireId ORDER BY TransactionDate DESC) AS rn
    FROM Transactions
    WHERE 
        ConcessionaireId IS NOT NULL 
        AND ConcessionaireId > 0
        AND Particulars = 'Billing'
) t
WHERE rn = 1 -- 只保留每组的最新记录
ORDER BY ConcessionaireId;

方案说明 #

1. 内层子查询通过PARTITION BY ConcessionaireId将数据按特许经营商分组，ORDER BY TransactionDate DESC让每组内最新的交易排在最前面，并给每条记录分配编号rn
2. 外层查询只筛选rn=1的记录，也就是每个特许经营商的最新交易数据
3. 去掉了冗余的EXISTS和无效的GROUP BY，逻辑更清晰，性能也更优

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