Group values stores against time in SQL (maria DB) until Zero and do a summation along with count

Question

I have SQL table which has data stored in 15min intervals against a variable(unique key). The value can be any number for each interval of time including zero. Wanted to group the values until zero against the unique key.

Like if the values are 0,0,100,900,30,0,0,0,400,900,100,0,0,0,0,400,500,0,0 over time wanted to add values until zero (like sum as 1030, 1400, 900, ... ) against each unique key and also count number of instances the values are not zero (like in this case it is 3 & so on).

We have data stored in SQL (mariaDB) against time(15-min interval). Now this data determines the performance of an entity and it is secs (like number of secs within that 15min interval, it has issues wherein it can take any value from zero to 900). Now we are able to group them for any time interval period and plot the total impacted secs easily

Parameter_health Database contains two tables --

1. Parameter_detail table contains fixed data information against the parameter
2. Parameter_uasvalue table contains the un-available secs(UAS) for each 15 min interval against time for that parameter

I used the SQL query as below which is overall sum of the total UAS grouped to-gether but I would like to group them until next zero value and also count the total instance of such events (not non zero events but total incidents when it was not zero) for any interval.

Using the below query in 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

Wanted to group each values until zero (do summation) and then count such instances for any time interval in sql query. Table structure of parameter_uasvalue table is shown below

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

enter image description here

Answer 1

This is kind of gaps and islands problem. First process and group parameter_uasvalue data then join 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;

Grouping demo