如何编写SQL查询获取账户当前BLOCK_CATEGORY的变更时间

这是我在Stack Overflow的第一个问题，若提问方式有问题请告知，我会补充更多信息。

上下文 #

我有一个包含四列的SQL表，列名为：ACC_ID、BLOCK_CATEGORY、START_DATE、VALUE
列类型分别为：(数值型)、(数值型)、(日期型)、(数值型)
该表以新行记录账户的所有变更，ACC_ID是账户的唯一标识，START_DATE为变更发生日期，本次可忽略VALUE列。

我需要编写查询以获取所有账户切换到当前BLOCK_CATEGORY的时间。问题在于BLOCK_CATEGORY为1-8的数值，账户可能曾处于相同的BLOCK_CATEGORY，但我们需要的是本次切换到该分类的时间。

样本数据（日期格式为DD/MM/YYYY） #

期望的最终结果 #

当前错误结果 #

当前使用的查询语句 #

SELECT *
FROM (
    SELECT ACC_ID,
           BLOCK_CATEGORY,
           START_DATE,
           ROW_NUMBER() OVER (PARTITION BY acc_id order by start_date DESC) RowNum
    FROM (
        SELECT ACC_ID,
               BLOCK_CATEGORY,
               MIN(START_DATE) 'START_DATE'
        FROM [dbo].[ACCOUNTCHANGES]
        WHERE BLOCK_CATEGORY IS NOT NULL
        GROUP BY ACC_ID,BLOCK_CATEGORY
    ) A
) B
WHERE B.RowNum = 1

解决方案 #

你的查询错误在于内层用MIN(START_DATE)按ACC_ID和BLOCK_CATEGORY分组，这会拿到该账户对应分类的最早日期，而不是最后一次切换到该分类的日期。要解决这个问题，需要先识别出账户每次分类变更的节点，再取每个账户最新的变更节点。

可以用LAG()窗口函数获取每个账户上一条记录的分类，筛选出分类发生变化的记录（包括账户的第一条记录），然后对每个账户取最新的那条记录，就是当前分类的切换时间：

WITH CategoryChanges AS (
    SELECT 
        ACC_ID,
        BLOCK_CATEGORY,
        START_DATE,
        -- 标记当前记录是否是分类变更节点
        CASE 
            WHEN LAG(BLOCK_CATEGORY) OVER (PARTITION BY ACC_ID ORDER BY START_DATE) != BLOCK_CATEGORY
                 OR LAG(BLOCK_CATEGORY) OVER (PARTITION BY ACC_ID ORDER BY START_DATE) IS NULL
            THEN 1 
            ELSE 0 
        END AS IsChangePoint
    FROM [dbo].[ACCOUNTCHANGES]
    WHERE BLOCK_CATEGORY IS NOT NULL
),
LatestChange AS (
    SELECT 
        ACC_ID,
        BLOCK_CATEGORY,
        START_DATE,
        ROW_NUMBER() OVER (PARTITION BY ACC_ID ORDER BY START_DATE DESC) AS RowNum
    FROM CategoryChanges
    WHERE IsChangePoint = 1
)
SELECT ACC_ID, BLOCK_CATEGORY, START_DATE
FROM LatestChange
WHERE RowNum = 1

逻辑说明 #

1. CategoryChanges CTE：用LAG()函数对比当前记录和上一条记录的BLOCK_CATEGORY，如果不同（或者是账户的第一条记录），标记为分类变更节点。
2. LatestChange CTE：对每个账户的所有变更节点按日期倒序排序，取第一条（即最新的变更节点）。
3. 最终筛选出每个账户的最新变更节点，就是当前分类的切换时间。

这个逻辑会正确处理账户多次切换到同一分类的情况，比如ACC_ID 1001，最后一次切换到7的时间是17/08/2022，而不是最早的14/08/2022。

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