WITH t0 as (
SELECT 1 client_id, '2025-02-01 00:00:00' device_time, 1 runstate
UNION ALL SELECT 1, '2025-02-01 00:00:30', 1
UNION ALL SELECT 1, '2025-02-01 00:01:00', 1
UNION ALL SELECT 1, '2025-02-01 00:01:30', 0
UNION ALL SELECT 1, '2025-02-01 00:02:00', 1
UNION ALL SELECT 1, '2025-02-01 00:02:30', 1
UNION ALL SELECT 1, '2025-02-01 00:03:00', 0
UNION ALL SELECT 1, '2025-02-01 00:03:30', 0
UNION ALL SELECT 1, '2025-02-01 00:04:00', 0
UNION ALL SELECT 1, '2025-02-01 00:04:30', 0
UNION ALL SELECT 1, '2025-02-01 00:05:00', 0
UNION ALL SELECT 1, '2025-02-01 00:05:30', 0
UNION ALL SELECT 1, '2025-02-01 00:06:00', 0
UNION ALL SELECT 1, '2025-02-01 00:06:30', 0
UNION ALL SELECT 1, '2025-02-01 00:07:00', 0
UNION ALL SELECT 1, '2025-02-01 00:08:00', 0
UNION ALL SELECT 1, '2025-02-01 00:09:00', 0
UNION ALL SELECT 1, '2025-02-01 00:10:00', 1
UNION ALL SELECT 1, '2025-02-01 00:11:00', 0
UNION ALL SELECT 1, '2025-02-01 00:12:00', 1
UNION ALL SELECT 1, '2025-02-01 00:13:00', 0
UNION ALL SELECT 1, '2025-02-01 00:14:00', 0
UNION ALL SELECT 1, '2025-02-01 00:15:00', 0
UNION ALL SELECT 1, '2025-02-01 00:16:00', 0
UNION ALL SELECT 1, '2025-02-01 00:17:00', 0
UNION ALL SELECT 1, '2025-02-01 00:18:00', 1
UNION ALL SELECT 1, '2025-02-01 00:19:00', 0
UNION ALL SELECT 1, '2025-02-01 00:20:00', 1
UNION ALL SELECT 1, '2025-02-01 00:21:00', 0
UNION ALL SELECT 1, '2025-02-01 00:22:00', 0
UNION ALL SELECT 1, '2025-02-01 00:23:00', 1
UNION ALL SELECT 1, '2025-02-01 00:24:00', 0
UNION ALL SELECT 1, '2025-02-01 00:25:00', 0
UNION ALL SELECT 1, '2025-02-01 00:26:00', 0
UNION ALL SELECT 1, '2025-02-01 00:27:00', 0
UNION ALL SELECT 1, '2025-02-01 00:28:00', 0
UNION ALL SELECT 1, '2025-02-01 00:29:00', 1
UNION ALL SELECT 1, '2025-02-01 00:30:00', 1
UNION ALL SELECT 1, '2025-02-01 00:31:00', 1
UNION ALL SELECT 1, '2025-02-01 00:32:00', 1
UNION ALL SELECT 1, '2025-02-01 00:33:00', 0
UNION ALL SELECT 1, '2025-02-01 00:34:00', 0
UNION ALL SELECT 1, '2025-02-01 00:35:00', 0
UNION ALL SELECT 1, '2025-02-01 00:36:00', 0
UNION ALL SELECT 1, '2025-02-01 00:37:00', 0
UNION ALL SELECT 1, '2025-02-01 00:38:00', 1
UNION ALL SELECT 1, '2025-02-01 00:39:00', 1
UNION ALL SELECT 1, '2025-02-01 00:40:00', 1
),
t1 AS (
-- 这里的 run 和 rest 会去查询配置表
SELECT
client_id,
-- 运行时长: 60 秒
60 run,
-- 休息时长: 120 秒
120 rest,
device_time,
-- 运行状态: 0-停止,1-运行
runstate,
ROW_NUMBER() OVER (ORDER BY device_time) AS rn,
ROW_NUMBER() OVER (
PARTITION BY client_id, runstate
ORDER BY device_time) AS grp
FROM t0
ORDER BY device_time
),
t2 AS (
SELECT
*,
MIN(device_time) OVER (
PARTITION BY client_id, runstate, rn - grp
ORDER BY device_time
) AS start_time,
MAX(device_time) OVER (
PARTITION BY client_id, runstate, rn - grp
ORDER BY device_time
) AS end_time,
rn - grp gap
FROM
t1
),
t3 AS (
SELECT
*,
-- 持续时长
--在计算持续时长时，TIMESTAMPDIFF 函数使用 end_time 和 start_time 进行计算，然而 end_time 和 start_time 是同---一组内的最大和最小时间，若组内只有一条记录，该计算结果会为 0 。所以，应当使用当前行的 device_time 减去组
--内的起始时间来计算持续时长。
TIMESTAMPDIFF(SECOND, start_time, device_time) AS duration,
-- 前一个时长
LAG(TIMESTAMPDIFF(SECOND, start_time, device_time), 1, 0) OVER (
PARTITION BY client_id
ORDER BY device_time
) prev_duration
FROM
t2
),
t4 AS (
SELECT
*,
CASE
-- 触发疲劳时,设置状态为当前行号
WHEN runstate = 1 AND duration >= run AND prev_duration < run THEN rn
ELSE 0
END fatigue
FROM
t3
),
t5 as (
SELECT
*,
CASE
-- 触发解除疲劳是,设置状态为前面所有的疲劳的负值
WHEN runstate = 0 AND duration >= rest AND prev_duration < rest THEN
-SUM(fatigue) OVER (PARTITION BY client_id ORDER BY device_time)
ELSE fatigue
END fatigue1
FROM
t4
)
SELECT
*,
-- >0 为疲劳, <=0 为非疲劳
SUM(fatigue1) OVER (PARTITION BY client_id ORDER BY device_time) fatigue_final
FROM t5
ORDER BY device_time;
Select Language