返回给定时间段内最短状态持续时间的mySQL查询

nbnkbykc 于 2023-03-17 发布在 Mysql

关注(0)|答案(1)|浏览(129)

我在一个MySQL数据库表中有以下数据，我想计算一个给定时间段的最短持续时间where state = 1，放入格拉法那状态。
在这个表格片段中，期望的结果是第10行和第8行之间的timestampdiff()，即2分钟。
| 身份证|洛克|温度|状态|日期时间|
| - ------|- ------|- ------|- ------|- ------|
| 1个|房间_1|十九点四|无|2022年12月29日18时14分09秒|
| 第二章|房间_1|十九点三|无|2022年12月29日18时15分09秒|
| 三个|房间_1|十九、二|1个|2022年12月29日18时16分09秒|
| 四个|房间_1|十九、二|1个|2022年12月29日18时17分09秒|
| 五个|房间_1|十九、二|1个|2022年12月29日18时18分09秒|
| 六个|房间_1|十九、二|无|2022年12月29日18时19分10秒|
| 七|房间_1|十九、二|无|2022年12月29日18时20分10秒|
| 八个|房间_1|十九、一|1个|2022年12月29日18时21分10秒|
| 九|房间_1|十九岁|1个|2022年12月29日18时22分10秒|
| 十个|房间_1|十九岁|无|2022年12月29日18时23分10秒|
我已经从chatGPT尝试了许多错误的解决方案，我不会在这里张贴的原因很明显。
周期部分非常简单，使用Grafana全局变量WHERE $__timeFilter(datetime)，并使用它计算 total duration WHERE state = 1 AND $__timeFilter(datetime)，因为记录生成基于1分钟，这使得对二进制0/1状态列求和非常容易（显然不需要state = 1）。
我完全搞不懂如何在一个期间内处理持续期间。

mysql

来源：https://stackoverflow.com/questions/75710495/mysql-query-to-return-minimum-state-duration-for-a-given-period

1条答案

按热度按时间

gcmastyq1#

你的查询要求有点复杂，我不得不使用窗口函数- LAG来获得结果。下面是你需要的查询。

WITH temp_prev_state AS (
SELECT id, loc, temp, state, datetime_, LAG(state,1)  OVER(  ORDER BY datetime_  ) AS prev_state FROM Temperature
), latest_on_off_stateSession AS (
(SELECT * from temp_prev_state where state = '0' and prev_state = '1' ORDER BY datetime_ DESC LIMIT 1 ) 
UNION
(SELECT * from temp_prev_state where state = '1' and prev_state = '0' ORDER BY datetime_ DESC LIMIT 1 )
), lat_on_off_prev_dt AS (
SELECT *, LAG(datetime_,1)  OVER(  ORDER BY datetime_  ) AS prev_datetime_ FROM latest_on_off_stateSession
)
select id, loc, temp, state, datetime_, ABS(TIMESTAMPDIFF(MINUTE,datetime_ ,prev_datetime_)) AS abs_Time_Diff_Mins FROM lat_on_off_prev_dt

**查询的输出：**x1c 0d1x

查看here演示

赞(0）回复(0）举报 2023-03-17

我来回答

返回给定时间段内最短状态持续时间的mySQL查询

1条答案

相关问题

热门标签

最新问答