问个 SQL 查询问题.

197 天前
 vvtf

现在有一张表如下: 

create table iot (
  client_id bigint not null,
  device_time datetime not null,
  runstate int not null comment '运行状态:0-停止,1-运行'
);

需求是查询疲劳运行的设备, 规则是持续运行60s时为疲劳, 然后停止运行持续120s时为解除疲劳.

这是我的 sql, 这个正确不?

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(second, start_time, end_time) AS duration,
        -- 前一个时长
        lag(
          TIMESTAMPDIFF(second, start_time, end_time),1,0
        ) over(
          partition by client_id,runstate, rn - grp
          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
1967 次点击
所在节点    数据库
4 条回复
pyang6984
197 天前
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;
Nooooobycat
197 天前
WITH state_groups AS (
SELECT
client_id,
device_time,
runstate,
SUM(change_flag) OVER (PARTITION BY client_id ORDER BY device_time) AS grp
FROM (
SELECT
client_id,
device_time,
runstate,
CASE WHEN LAG(runstate) OVER (PARTITION BY client_id ORDER BY device_time) = runstate THEN 0 ELSE 1 END AS change_flag
FROM iot
) AS tmp
),
group_durations AS (
SELECT
client_id,
grp,
runstate,
MIN(device_time) AS start_time,
MAX(device_time) AS end_time,
TIMESTAMPDIFF(SECOND, MIN(device_time), MAX(device_time)) AS duration_seconds
FROM state_groups
GROUP BY client_id, grp, runstate
),
ranked_runs AS (
SELECT
client_id,
end_time AS last_run_end,
duration_seconds,
ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY end_time DESC) AS rn
FROM group_durations
WHERE runstate = 1
)
SELECT DISTINCT rr.client_id
FROM ranked_runs rr
WHERE rr.rn = 1
AND rr.duration_seconds >= 60
AND NOT EXISTS (
SELECT 1
FROM group_durations gd
WHERE gd.client_id = rr.client_id
AND gd.runstate = 0
AND gd.start_time >= rr.last_run_end
AND gd.duration_seconds >= 120
);
Rache1
197 天前
果然还是 V2 提问者靠谱一些

一些社区里面那些提问的，问个数据库问题，数据库版本、表结构、填充数据啥都没有，让补充问题吧，有的就上个图片就来了
vvtf
194 天前
改成这样就行了,
使用 row_number 有问题就是当发生疲劳了,
而后面紧跟着的几个状态都是解除疲劳的触发就会出现错误.
所以使用 last_value 解决.

```sql

WITH t0 as (
...
)
, 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 ,
rn - grp gap
FROM
t1
)
,t3 AS (
SELECT
*,
-- 持续时长
TIMESTAMPDIFF(second, start_time, device_time) AS duration
FROM
t2
)

,t4 AS (
SELECT
*,
case
-- 触发疲劳时,设置 1
-- 解除疲劳设置 0
-- 其他设置 null
when runstate=1 and duration>=run then 1
when runstate=0 and duration>=rest then 0
else null
end fatigue
FROM
t3
)

select
*,
-- 使用前面最后非 null 的状态为疲劳状态
ifnull(last_value(fatigue) ignore nulls over(
partition by client_id
order by device_time
rows between unbounded preceding and current row
),0) fatigue_final
from t4
order by device_time


```

这是一个专为移动设备优化的页面（即为了让你能够在 Google 搜索结果里秒开这个页面），如果你希望参与 V2EX 社区的讨论，你可以继续到 V2EX 上打开本讨论主题的完整版本。

https://www.v2ex.com/t/1113319

V2EX 是创意工作者们的社区，是一个分享自己正在做的有趣事物、交流想法，可以遇见新朋友甚至新机会的地方。

V2EX is a community of developers, designers and creative people.

© 2021 V2EX