如何查询每行特定时间窗口内次新值及状态更新事件历史状态

嘿，这个需求其实用窗口函数就能轻松搞定！我来给你拆解下怎么实现，两种方法任你选：

思路先理清楚 #

我们要找所有paymentType = 'UpdateStatus'的记录，还要给每条记录加上它发生之前用户的最后一个状态。核心就是先把所有记录按用户和时间排好序，然后给每个状态更新事件“揪出”它前面最近的那个状态值就行。

方法一：窗口函数（推荐，简洁高效） #

用LAG()窗口函数是最直接的方式，它能帮我们在排序后的记录里，直接拿到上一条的状态值。看代码：

WITH ordered_records AS (
    SELECT 
        UserName,
        paymentType,
        date,
        Status,
        -- 按用户分组，日期从早到晚排，取上一条的Status
        LAG(Status) OVER (PARTITION BY UserName ORDER BY date) AS Previous_status
    FROM your_table_name -- 记得把这里换成你的实际表名哦
)
SELECT 
    UserName,
    paymentType,
    date,
    Status,
    Previous_status
FROM ordered_records
WHERE paymentType = 'UpdateStatus'
ORDER BY date;

方法二：关联查询（兼容老版本数据库） #

如果你的数据库不支持窗口函数（比如一些老版本的MySQL），那用关联查询也能实现，就是稍微啰嗦一点：

SELECT 
    us.UserName,
    us.paymentType,
    us.date,
    us.Status,
    prev.Status AS Previous_status
FROM your_table_name us
LEFT JOIN your_table_name prev
    ON us.UserName = prev.UserName
    AND prev.date = (
        -- 找到当前状态更新之前，同一用户的最新记录日期
        SELECT MAX(date) 
        FROM your_table_name 
        WHERE UserName = us.UserName AND date < us.date
    )
WHERE us.paymentType = 'UpdateStatus'
ORDER BY us.date;

看看结果对不对 #

用你给的示例数据跑这两个查询，都会得到这样的结果：

小提醒 #

• 确保date字段是日期类型，不然排序可能会乱
• 如果同一天有好几条记录，LAG()会取同天的上一条，要是需要更精准，可以加个主键或者其他字段一起排序
• 如果用户第一条记录就是状态更新，那Previous_status会是NULL，你可以用COALESCE(Previous_status, 0)之类的语句把空值换成你想要的默认值

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