组值在SQL(maria DB)中按时间存储，直到为零，并在计数的同时进行求和

Question

我有一个SQL表，它的数据以15分钟的间隔存储在一个变量(唯一键(中。该值可以是每个时间间隔的任何数字，包括零。想要根据唯一键将值分组到零。

就像如果值是0,0100900,30,0,0400900100,0,0,0400500,0,0一样，随着时间的推移，希望对每个唯一键添加值直到零(就像10301400900…的总和(，并计算值不为零的实例数(就像在这种情况下是3&依此类推(。

我们将数据存储在SQL(mariaDB(中，时间间隔为15分钟。现在，这些数据决定了一个实体的性能，它是秒(就像15分钟间隔内的秒数一样，它存在可以取0到900之间的任何值的问题(。现在，我们可以在任何时间间隔内对它们进行分组，并轻松绘制受影响的总秒数

Parameter_health数据库包含两个表——

1. Parameter_detail表包含针对参数的固定数据信息
2. Parameter_uasvalue表包含该参数每15分钟间隔的不可用秒(UAS(

我使用了下面的SQL查询，它是分组在一起的总UAS的总和，但我想将它们分组到下一个零值，并计算任何间隔内此类事件的总实例(不是非零事件，而是非零事件时的总事件(。

在Grafana 中使用以下查询

SELECT parameter_detail.system, parameter_detail.parameter_label, sum(parameter_uasvalue.uas) as 'Total UAS'
FROM parameter_health.parameter_uasvalue left join parameter_health.parameter_detail on parameter_detail.id=parameter_uasvalue.id
WHERE $__timeFilter(parameter_uasvalue.time) and parameter_uasvalue.uas != '-1' and parameter_uasvalue.uas !=' 0' 
GROUP BY parameter_detail.system, parameter_detail.parameter_label

希望将每个值分组到零(求和(，然后在sql查询中的任何时间间隔内对这些实例进行计数。parameter_uasvalue表的表结构如下所示

id  time    uas
Parameter-Unique Value1 15/11/2021 0:00 0
Parameter-Unique Value1 15/11/2021 0:15 100
Parameter-Unique Value1 15/11/2021 0:30 900
Parameter-Unique Value1 15/11/2021 0:45 30
Parameter-Unique Value1 15/11/2021 1:00 0
Parameter-Unique Value1 15/11/2021 1:15 0
Parameter-Unique Value1 15/11/2021 1:30 400
Parameter-Unique Value1 15/11/2021 1:45 900
Parameter-Unique Value1 15/11/2021 2:00 0
Parameter-Unique Value1 15/11/2021 2:15 0
Parameter-Unique Value1 15/11/2021 2:30 0
Parameter-Unique Value1 15/11/2021 2:45 400
Parameter-Unique Value1 15/11/2021 3:00 500
Parameter-Unique Value1 15/11/2021 3:15 0
Parameter-Unique Value1 15/11/2021 3:30 0
Parameter-Unique Value1 15/11/2021 3:45 0

在此处输入图像描述

Answer 1

这是一种缺口和孤岛问题。首先对parameter_uasvalue数据进行处理和分组，然后加入Parameter_detail。

SELECT d.system, d.parameter_label, seriesStart, s, nonzc 
from (
select id, min(time) seriesStart, sum(uas) s, count(case when uas > 0 then uas end) nonzc
from (
select id, time, uas, sum(flag) over(partition by id order by  time) grp
from (
select id, time, uas, (coalesce(lag(uas, 1) over(partition by id order by  time),0) = 0 and uas != 0) flag
from parameter_health.parameter_uasvalue t
where $__timeFilter(time) and uas != -1 
-- it will break series detection 
-- and uas != 0
) t
) t
group by id, grp
) t
left join parameter_health.parameter_detail d on d.id=t.id
order by d.system, d.parameter_label, seriesStart;

分组演示