如何用SQL查询用户与客户的最新连续交互次数？

现有一张包含userID（用户ID）、FY（财年，格式如2020、2021）、clientID（客户ID）的数据表，我们需要统计每个用户与当前最新合作客户的连续交互财年次数。举个实际场景：

• 用户1：2022-2024年连续和clientID=10001合作，2021年切换到10002，2020年曾和10001合作，最终统计其最新连续交互次数为3
• 用户2：最新连续交互次数为2
• 用户3：最新连续交互次数为1

当然可以用SQL实现，下面是通用的实现方案（适用于大多数支持窗口函数的数据库，比如MySQL 8+、PostgreSQL、SQL Server等）：

实现思路 #

1. 为每个用户按财年从新到旧排序，确定最新的合作客户
2. 从最新的财年开始，向前追溯连续使用同一客户的财年数量
3. 聚合得到每个用户的最终统计结果

完整SQL代码 #

WITH user_client_fy AS (
    -- 为每个用户的财年记录按从新到旧排序，标记最新客户
    SELECT
        userID,
        FY,
        clientID,
        -- 获取当前用户最新财年对应的客户ID
        FIRST_VALUE(clientID) OVER (PARTITION BY userID ORDER BY FY DESC) AS latest_client,
        -- 计算当前财年与用户最新财年的差值
        MAX(FY) OVER (PARTITION BY userID) - FY AS fy_diff
    FROM your_table_name
),
continuous_series AS (
    -- 筛选出用户与最新客户的合作记录，标记连续财年段
    SELECT
        userID,
        FY,
        latest_client,
        fy_diff,
        -- 生成分组ID：连续财年的分组ID一致，中断则ID变化
        fy_diff - ROW_NUMBER() OVER (PARTITION BY userID, latest_client ORDER BY FY DESC) AS group_id
    FROM user_client_fy
    WHERE clientID = latest_client
)
-- 统计每个用户最新客户的连续交互次数
SELECT
    userID,
    latest_client AS current_latest_client,
    COUNT(*) AS continuous_interaction_count
FROM continuous_series
-- 仅保留时间最近的连续财年段
WHERE group_id = (SELECT MIN(group_id) FROM continuous_series cs WHERE cs.userID = continuous_series.userID)
GROUP BY userID, latest_client
ORDER BY userID;

代码逻辑解释 #

1. CTE user_client_fy：

   • 用FIRST_VALUE窗口函数抓取每个用户最新财年对应的客户ID（即latest_client）
   • 计算每条记录的财年与用户最新财年的差值fy_diff，用于后续判断连续性

2. CTE continuous_series：

   • 仅保留用户与latest_client合作的记录
   • 通过fy_diff - ROW_NUMBER()生成分组ID：连续财年的该值保持一致，若中间换过客户则值会突变，以此区分不同的连续区间

3. 最终查询：

   • 筛选出每个用户对应的最小group_id分组（对应时间最近的连续区间）
   • 统计该分组内的记录数，即为用户与最新客户的连续交互次数

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