mySQL 8.0 -尝试开发一个查询来测量特定值的持续时间

Question

我有一个包含数百万条记录的表

+----------------------+-----------+------------+
| trnWhen              | Alias     | Value      |
+----------------------+-----------+------------+
| 2022-12-01 00:03:00  | DevID1    |  0         |
| 2022-12-01 00:04:00  | DevID2    |  1         |
| 2022-12-01 01:00:00  | DevID2    |  1         |
| 2022-12-01 01:25:00  | DevID1    |  1         |
| 2022-12-01 02:00:00  | DevID1    |  1         |
| 2022-12-01 02:00:00  | DevID2    |  1         |
| 2022-12-01 02:25:00  | DevID1    |  0         |
| 2022-12-01 02:45:00  | DevID2    |  0         |
| 2022-12-01 03:00:00  | DevID1    |  0         |
| 2022-12-01 03:00:00  | DevID2    |  0         |
| 2022-12-01 03:30:00  | DevID1    |  1         |
| 2022-12-01 04:00:00  | DevID2    |  1         |
| 2022-12-01 04:10:00  | DevID1    |  0         |
+----------------------+-----------+------------+

我想要的结果是总小时，其中值= 1每个别名之间的一组日期(如2022-12-01 00:00:00至2022-12-02 00:00:00)。通常我会每天查看这个，但不想将查询限制在此。预期结果如下:

Alias      RunHours
DevID1      1.33
DevID2     22.75

如果前面没有'0'，则假定该值在开始时间为1。(与DevID2的情况类似)。如果没有post '0'，则在结束时假定该值为1。(如DevID2的情况)

最终我想要的是它将Value更改为1的次数。如:

Alias      Runhours     Starts
DevID1      1.33          2
DevID2     22.75          1

我已经计算出一个别名的开始如下:

Set @AL = DevID1;
SELECT Alias, COUNT(*) as Starts
FROM history curr
WHERE Alias = @AL 
AND curr.value = 1
and trnwhen Between '2022-12-01 00:00:00' and '2022-12-05 00:00:00'
AND (
SELECT value
FROM history prev
WHERE Alias = @AL  
AND prev.trnWhen < curr.trnwhen

ORDER BY trnwhen DESC
LIMIT 1
) = 0;

但我被困在得到这个任何进一步

Answer 1

如果trnwhen是时间戳，表示事件(alias,value)的开始，并且情况持续到下一个事件，则:

1. 使用lead()计算每个事件(开始，结束)及其持续时间
2. 使用lag()得到之前的value，并与当前的value比较得到starts(0 ->1)

with cte as (
select alias,
coalesce(timestampdiff(minute, trnwhen, lead(trnwhen,1) over (partition by alias order by trnwhen)),0) as minutes,
value,
case when coalesce(lag(value, 1) over (partition by alias order by trnwhen),1) = 0 and value = 1 then 1 else 0 end as starts
from history)
select alias,
sum(case when value=1 then minutes else 0 end)/60.0 as runhours,
sum(starts) as starts
from cte
group by alias
order by alias;