如何用SQL窗口函数捕获用户状态变更前的最后活跃日期？

我有一张每日记录用户状态的用户表，想要用window function捕获用户状态变更前的最后活跃日期。比如示例中用户切换为deleted状态前，最后一次处于active状态的日期是2022-01-02，期望得到对应结果。尝试过row_number、dense_rank、rank以及嵌套窗口函数，但不确定能否用单个窗口函数实现，求解决思路。

示例数据SQL：

with base as (
SELECT
1            AS USER_ID
,'2022-01-01' AS DATE
,'Active'     AS STATE
UNION ALL 
SELECT
1            AS USER_ID
,'2022-01-02' AS DATE
,'Active'    AS STATE    
UNION ALL    
SELECT
1            AS USER_ID
,'2022-01-03' AS DATE
,'Deleted'    AS STATE
UNION ALL 
SELECT
1            AS USER_ID
,'2022-01-04' AS DATE
,'Active'    AS STATE
UNION ALL 
SELECT
1            AS USER_ID
,'2022-01-05' AS DATE
,'mute'    AS STATE
)

解决方法 #

方法一：单窗口函数实现（简洁版） #

利用LAST_VALUE结合条件过滤和窗口范围，直接获取当前行之前最近的Active状态日期：

WITH base AS (
    SELECT 1 AS USER_ID, '2022-01-01' AS DATE, 'Active' AS STATE
    UNION ALL 
    SELECT 1 AS USER_ID, '2022-01-02' AS DATE, 'Active' AS STATE    
    UNION ALL    
    SELECT 1 AS USER_ID, '2022-01-03' AS DATE, 'Deleted' AS STATE
    UNION ALL 
    SELECT 1 AS USER_ID, '2022-01-04' AS DATE, 'Active' AS STATE
    UNION ALL 
    SELECT 1 AS USER_ID, '2022-01-05' AS DATE, 'mute' AS STATE
)
SELECT 
    USER_ID,
    DATE,
    STATE,
    LAST_VALUE(CASE WHEN STATE = 'Active' THEN DATE END IGNORE NULLS) 
    OVER (PARTITION BY USER_ID ORDER BY DATE ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS last_active_before_change
FROM base
ORDER BY DATE;

• 逻辑：通过CASE只保留Active状态的日期，LAST_VALUE结合IGNORE NULLS跳过空值，窗口范围限定为当前行之前的所有行，直接拿到状态变更前最后一次活跃的日期。
• 注意：IGNORE NULLS语法在PostgreSQL、MySQL 8.0+、SQL Server 2022+等主流数据库中支持，若你的数据库不支持，可改用MAX函数实现。

方法二：分组连续状态（兼容性更强） #

先给连续相同的状态分组，再基于分组获取之前的最后活跃日期，适配更多数据库：

WITH base AS (
    SELECT 1 AS USER_ID, '2022-01-01' AS DATE, 'Active' AS STATE
    UNION ALL 
    SELECT 1 AS USER_ID, '2022-01-02' AS DATE, 'Active' AS STATE    
    UNION ALL    
    SELECT 1 AS USER_ID, '2022-01-03' AS DATE, 'Deleted' AS STATE
    UNION ALL 
    SELECT 1 AS USER_ID, '2022-01-04' AS DATE, 'Active' AS STATE
    UNION ALL 
    SELECT 1 AS USER_ID, '2022-01-05' AS DATE, 'mute' AS STATE
),
state_groups AS (
    SELECT 
        *,
        -- 给连续相同状态的记录标记分组ID
        SUM(CASE WHEN STATE != LAG(STATE, 1, '') OVER (PARTITION BY USER_ID ORDER BY DATE) THEN 1 ELSE 0 END) 
        OVER (PARTITION BY USER_ID ORDER BY DATE) AS state_group
    FROM base
)
SELECT 
    USER_ID,
    DATE,
    STATE,
    -- 取当前分组之前所有Active状态的最大日期
    MAX(CASE WHEN STATE = 'Active' THEN DATE END) 
    OVER (PARTITION BY USER_ID ORDER BY state_group ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS last_active_before_change
FROM state_groups
ORDER BY DATE;

• 逻辑：先用LAG识别状态变更节点，通过累加得到连续状态的分组；再针对每个分组，取之前所有分组中Active状态的最大日期，即为当前状态变更前的最后活跃日期。

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