如何用SQL查询账户code从7100变更为7000的记录并按时间排序

我有包含Time_id、account、code三列的数据，需求是找出所有code从7100变更为7000的账户记录，并按时间由近到远排序。各字段说明：

• Time_id：每月生成的日期（格式yyyymmdd）
• account：客户唯一账户ID
• code：四位数字编码

我曾尝试使用LAG函数，但错误地按time_id分区，导致返回其他账户的历史code，无法按同一账户追踪变更。尝试的SQL语句如下：

SELECT time_id, account, code
    ,LAG(code, 1) OVER (partition by time_id order by time_id) LAG_1
  FROM my_table
  group by time_id, account, code

期望得到code从7100变为7000的账户及变更时间，例如从下表中返回账户12500和15500的对应变更记录：

寻求改进原有查询或新的解决方案。

你的核心问题是分区键错误：应该按account分区，而不是time_id，这样才能追踪同一个账户的code变更历史。同时需要按time_id排序，确保LAG函数取到的是该账户上一条时间的code值。

正确SQL语句 #

WITH account_code_history AS (
    SELECT 
        time_id,
        account,
        code,
        -- 取同一账户的上一条code记录
        LAG(code) OVER (PARTITION BY account ORDER BY time_id) AS prev_code
    FROM my_table
    -- 先过滤掉无关code，提升效率（可选）
    WHERE code IN (7100, 7000)
)
SELECT 
    time_id AS change_time,
    account,
    prev_code AS old_code,
    code AS new_code
FROM account_code_history
-- 筛选从7100变为7000的变更记录
WHERE prev_code = 7100 AND code = 7000
-- 按变更时间由近到远排序
ORDER BY time_id DESC;

逻辑说明 #

1. CTE部分：通过PARTITION BY account确保只追踪单个账户的code变化，ORDER BY time_id保证时间顺序正确，用LAG(code)获取该账户上一次的code值。
2. 筛选条件：直接过滤出prev_code=7100且code=7000的记录，就是你要的变更记录。
3. 排序：最后按time_id DESC实现时间由近到远排序。

针对示例数据的返回结果 #

执行上述SQL后，会得到以下结果：

这完全符合需求，只返回了两次有效的7100→7000变更记录，且按时间倒序排列。

额外优化点 #

如果你的表中有大量重复的（account, code, time_id）记录，可以先去重再处理，避免多余计算：

WITH distinct_records AS (
    SELECT DISTINCT time_id, account, code FROM my_table
),
account_code_history AS (
    SELECT 
        time_id,
        account,
        code,
        LAG(code) OVER (PARTITION BY account ORDER BY time_id) AS prev_code
    FROM distinct_records
    WHERE code IN (7100, 7000)
)
SELECT 
    time_id AS change_time,
    account,
    prev_code AS old_code,
    code AS new_code
FROM account_code_history
WHERE prev_code = 7100 AND code = 7000
ORDER BY time_id DESC;

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