最近遇到了个sql的问题。困扰许久
问题描述
数据库存储了机器的一些信号值。下面是这些信号的一些描述信息
signal_a中0代表机器正常，1代表异常。
time代表时间（单位秒）。
会存在设备断电的情况，断电时间内无信号。
数据样例
| signal_a | time |
|---|---|
| 1 | 1686988491 |
| 1 | 1686988492 |
| 1 | 1686988493 |
| 0 | 1686988494 |
| 1 | 1686988495 |
| 1 | 1686988496 |
| 1 | 1686988499 |
| 1 | 1686988500 |
| 1 | 1686988501 |
| 1 | 1686988502 |
问题：在时间连续前提下，统计每次异常的持续时间。得到目标数据
| start_time（异常开始时间） | duration（持续时间） |
|---|---|
| 1686988491 | 3 |
| 1686988495 | 2 |
| 1686988499 | 4 |
思考
如果使用java之类的语言，循环遍历这个问题就很容易。但问题要求的是使用sql处理。
展开分析。
1. 时间连续。
这个问题中，只需要考虑a信号值为1的情况。所以先加上where signal_a = 1
然后，需要对前一条后一条数据做判断。可以使用lag、lead函数。这里可以获取后一条数据，后一条数据时间-当前数据时间的值如果是定值1，表示连续。
sql大概是这样的
SELECT
signal_a,
time,
time - lag(time,1) over(ORDER BY time) dif
FROM `test2`
WHERE signal_a = 1;
这时候可以得到结果
肉眼可以分辨出，dif不是1即表示存在间断。但交给sql该怎么去分辨。（这里卡了很久，最后请教了大佬）
思考，我们在得到什么样的数据后可以继续分析？
大概是这种
如果可以根据dif字段得到时间连续的分区，再在每个分区内获取连续的signal_a为1的个数，问题就解决了
2. sum()over() 函数的运用
先放sql
SELECT
signal_a,
time,
dif,
sum(dif) over(ORDER BY time) part
FROM(
SELECT
signal_a,
time,
time - lag(time,1) over(ORDER BY time)-1 dif
FROM `test2`
WHERE signal_a = 1
) tmp;
把上面的查询当做成子查询，并将dif-1,这样窗口函数累加便会得到分区。处理结果如下
这时候需要对第一条数据进行特殊处理，判断肯定属于第一个分区，将null改为0。（这里使用的if处理的，jym有更好的可以教教我）。代码我省略了qaq
3.获取每个分区的个数
这里的逻辑就比较常规了。使用聚合函数加group by即可。
最终sql
SELECT
min(time) start_time,
count(1) duration
FROM (
SELECT
signal_a,
time,
dif,
sum(dif) over(ORDER BY time) part
FROM(
SELECT
signal_a,
time,
IFNULL(time - lag(time,1) over(ORDER BY time)-1 ,0) dif
FROM `test2`
WHERE signal_a = 1
) tmp
)tem2
GROUP BY part;
我觉得最巧妙的便是将时间差减1，得到0，再累加。
希望能对大家有所帮助。如果有更好的方案，或者这个sql有可以改进的，欢迎留言。
案例中，数据库版本 mysql 8.0.29 ，表名test2
