SQL技术问询：如何关联已流失客户表并筛选最新快照记录

针对你需要从百万级快照表中筛选流失客户最新记录的需求，以下是几种高效的SQL实现方案，同时附带优化建议：

方法1：窗口函数（推荐，大数据量场景效率最优） #

通过ROW_NUMBER()窗口函数按客户ID分组，对快照日期降序排序，取每个客户的第一条记录：

WITH RankedRecords AS (
    SELECT 
        r.Customer_ID,
        r.[Customer Name],
        r.[Recorded as of],
        -- 按客户分组，最新日期排第1位
        ROW_NUMBER() OVER (PARTITION BY r.Customer_ID ORDER BY r.[Recorded as of] DESC) AS rn
    FROM Records_List r
    -- 先关联流失客户表，缩小处理范围
    INNER JOIN Former_Customer_ID_List f ON r.Customer_ID = f.Customer_ID
)
SELECT Customer_ID, [Customer Name], [Recorded as of]
FROM RankedRecords
WHERE rn = 1;

如果存在同一客户同一天有多条快照的情况，可将ROW_NUMBER()替换为RANK()或DENSE_RANK()，保留所有当天的记录。

方法2：子查询获取最大日期后关联 #

先找出每个流失客户的最新快照日期，再关联回原表获取完整记录：

SELECT r.Customer_ID, r.[Customer Name], r.[Recorded as of]
FROM Records_List r
INNER JOIN Former_Customer_ID_List f ON r.Customer_ID = f.Customer_ID
-- 关联子查询得到的每个客户最新日期
INNER JOIN (
    SELECT 
        Customer_ID, 
        MAX([Recorded as of]) AS Latest_Snapshot_Date
    FROM Records_List
    INNER JOIN Former_Customer_ID_List ON Records_List.Customer_ID = Former_Customer_ID_List.Customer_ID
    GROUP BY Customer_ID
) latest_dates 
    ON r.Customer_ID = latest_dates.Customer_ID 
    AND r.[Recorded as of] = latest_dates.Latest_Snapshot_Date;

这个方法逻辑直观，若同一客户同一日期有多条记录，会返回所有匹配的快照。

方法3：TOP 1 WITH TIES（仅适用于SQL Server） #

利用SQL Server特有的语法，简洁实现需求：

SELECT TOP 1 WITH TIES
    r.Customer_ID,
    r.[Customer Name],
    r.[Recorded as of]
FROM Records_List r
INNER JOIN Former_Customer_ID_List f ON r.Customer_ID = f.Customer_ID
-- 按窗口函数排序，自动返回每个客户的最新记录
ORDER BY ROW_NUMBER() OVER (PARTITION BY r.Customer_ID ORDER BY r.[Recorded as of] DESC);

关键效率优化 #

• 创建复合索引：给Records_List表的Customer_ID和[Recorded as of]创建复合索引，能大幅提升分组、排序和关联的速度：

  CREATE INDEX idx_records_customer_date ON Records_List(Customer_ID, [Recorded as of] DESC);
  

• 优化流失客户表：确保Former_Customer_ID_List的Customer_ID是主键或拥有唯一索引，减少关联时的查找开销。

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