如何根据Date_key查询指定学期内表中用户名变更的用户

问题背景 #

现有一张包含Date_key、user_name、user_id三列的表，数据如下：

Date_key    | user_name  | user_id
2022-07-12  | milkcotton | 1
2022-09-12  | cereal     | 2
2022-06-12  | musicbox1  | 3
2022-12-31  | harrybel1  | 1
2022-12-25  | milkcotton1| 4
2023-01-01  | cereal     | 2

需要查询2022年7月1日至12月31日期间发生用户名变更的用户，期望输出结果：

previous_name| new_name  | user_id
milkcotton  | harrybel1 | 1

实现方案 #

完全可以实现，以下提供两种主流的SQL写法：

方法1：基础关联查询（兼容多数数据库） #

SELECT
    t1.user_name AS previous_name,
    t2.user_name AS new_name,
    t1.user_id
FROM
    -- 筛选出时间范围内有多个不同用户名的用户
    (SELECT user_id
     FROM your_table
     WHERE Date_key BETWEEN '2022-07-01' AND '2022-12-31'
     GROUP BY user_id
     HAVING COUNT(DISTINCT user_name) > 1) AS u
JOIN
    -- 获取用户时间范围内最早的记录（原用户名）
    (SELECT user_id, user_name
     FROM your_table
     WHERE Date_key BETWEEN '2022-07-01' AND '2022-12-31'
     AND Date_key = (SELECT MIN(Date_key) FROM your_table WHERE user_id = your_table.user_id AND Date_key BETWEEN '2022-07-01' AND '2022-12-31')) AS t1
ON u.user_id = t1.user_id
JOIN
    -- 获取用户时间范围内最晚的记录（新用户名）
    (SELECT user_id, user_name
     FROM your_table
     WHERE Date_key BETWEEN '2022-07-01' AND '2022-12-31'
     AND Date_key = (SELECT MAX(Date_key) FROM your_table WHERE user_id = your_table.user_id AND Date_key BETWEEN '2022-07-01' AND '2022-12-31')) AS t2
ON u.user_id = t2.user_id
WHERE t1.user_name != t2.user_name;

方法2：窗口函数写法（支持PostgreSQL、SQL Server、MySQL 8.0+等） #

利用窗口函数更简洁地获取每个用户时间范围内的首尾用户名：

WITH user_name_timeline AS (
    SELECT
        user_id,
        user_name,
        -- 获取时间范围内最早的用户名
        FIRST_VALUE(user_name) OVER (PARTITION BY user_id ORDER BY Date_key) AS previous_name,
        -- 获取时间范围内最晚的用户名
        LAST_VALUE(user_name) OVER (
            PARTITION BY user_id 
            ORDER BY Date_key 
            RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS new_name
    FROM your_table
    WHERE Date_key BETWEEN '2022-07-01' AND '2022-12-31'
)
SELECT DISTINCT
    previous_name,
    new_name,
    user_id
FROM user_name_timeline
WHERE previous_name != new_name;

逻辑说明 #

两种方法的核心思路一致：

1. 先锁定在指定时间范围内存在多个不同用户名的用户；
2. 再获取该用户此时间段内最早（原用户名）和最晚（新用户名）的记录；
3. 过滤掉前后用户名未变更的情况，得到最终结果。

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