如何使用SQL对比各ID当前小时与前一小时的数值

核心思路 #

利用SQL窗口函数LAG()，针对每个id按时间顺序获取其前一小时的value，再通过条件判断生成got_changes字段：

• 若前一小时无对应数据，或当前value与前一小时value不同，标记为yes
• 若当前value与前一小时value完全相同，标记为no

实现代码 #

假设你的表名为hourly_data，执行以下SQL语句：

SELECT
    id,
    value,
    time,
    CASE
        WHEN LAG(value) OVER (PARTITION BY id ORDER BY STR_TO_DATE(time, '%I%p')) IS NULL THEN 'yes'
        WHEN value != LAG(value) OVER (PARTITION BY id ORDER BY STR_TO_DATE(time, '%I%p')) THEN 'yes'
        ELSE 'no'
    END AS got_changes
FROM hourly_data
ORDER BY STR_TO_DATE(time, '%I%p') DESC, id;

代码说明 #

1. PARTITION BY id：确保仅在同一个id的范围内进行数据对比
2. ORDER BY STR_TO_DATE(time, '%I%p')：将文本格式的time（如3pm）转换为可排序的日期时间类型，保证按时间顺序获取前一小时数据
3. LAG(value)：获取当前行的上一行（前一小时）的value值，若当前是该id的最早记录，返回NULL
4. CASE语句：按照规则判断并生成got_changes字段

结果验证 #

执行上述代码后，将得到你期望的结果：

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