如何查询PWPH_Pmp_2_FBSts值从1切换为2时的Time_stamp

问题背景 #

我有一张名为per_hour_log的表，包含Time_stamp和PWPH_Pmp_2_FBSts两列，需要获取PWPH_Pmp_2_FBSts值从1切换为2时对应的Time_stamp。原查询仅能返回单个最大时间戳，无法满足获取多条切换记录的需求。

原查询语句 #

SELECT MAX(time_stamp) AS recent_datetime
FROM per_hour_log
WHERE PWPH_Pmp_2_FBSts = 2
AND time_stamp BETWEEN '2023-02-01 01:00:00' AND '2023-02-05 20:00:00'
AND EXISTS (
  SELECT 1 FROM per_hour_log
  WHERE PWPH_Pmp_2_FBSts = 1
  AND time_stamp BETWEEN '2023-02-01 01:00:00' AND '2023-02-05 20:00:00'
)

预期输出 #

03-02-2023  05:00:00
02-02-2023  13:00:00 

解决方案 #

要捕获所有状态从1切换到2的时间点，需使用窗口函数LAG()获取每条记录的上一条状态，再筛选符合切换条件的记录：

基础查询（获取原始时间戳） #

SELECT 
    time_stamp AS switch_datetime
FROM (
    SELECT 
        time_stamp,
        PWPH_Pmp_2_FBSts,
        -- 按时间顺序获取上一条记录的状态
        LAG(PWPH_Pmp_2_FBSts) OVER (ORDER BY time_stamp) AS prev_status
    FROM per_hour_log
    WHERE time_stamp BETWEEN '2023-02-01 01:00:00' AND '2023-02-05 20:00:00'
) AS status_changes
-- 筛选当前状态为2、上一条状态为1的切换记录
WHERE PWPH_Pmp_2_FBSts = 2 
  AND prev_status = 1
ORDER BY time_stamp;

格式适配查询（匹配预期输出格式） #

如果需要将时间戳格式调整为DD-MM-YYYY HH:MI:SS，可根据数据库类型使用对应格式化函数：

MySQL版本

SELECT 
    DATE_FORMAT(time_stamp, '%d-%m-%Y  %H:%i:%s') AS switch_datetime
FROM (
    SELECT 
        time_stamp,
        PWPH_Pmp_2_FBSts,
        LAG(PWPH_Pmp_2_FBSts) OVER (ORDER BY time_stamp) AS prev_status
    FROM per_hour_log
    WHERE time_stamp BETWEEN '2023-02-01 01:00:00' AND '2023-02-05 20:00:00'
) AS status_changes
WHERE PWPH_Pmp_2_FBSts = 2 
  AND prev_status = 1
ORDER BY time_stamp;

PostgreSQL版本

SELECT 
    TO_CHAR(time_stamp, 'DD-MM-YYYY  HH24:MI:SS') AS switch_datetime
FROM (
    SELECT 
        time_stamp,
        PWPH_Pmp_2_FBSts,
        LAG(PWPH_Pmp_2_FBSts) OVER (ORDER BY time_stamp) AS prev_status
    FROM per_hour_log
    WHERE time_stamp BETWEEN '2023-02-01 01:00:00' AND '2023-02-05 20:00:00'
) AS status_changes
WHERE PWPH_Pmp_2_FBSts = 2 
  AND prev_status = 1
ORDER BY time_stamp;

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