Virtual Cycle number creation

Grafana MySQL
,
1

Hi, I’d need your help how to create a virtual Cycle_ID in an SQL table. It is necessary to calculate the total consumption of a certain cycle.
I have a database, fields are ID, Timestamp, Countervalue. Countervalue shows the gas consumption meter counter. We have records in every 15 minutes.
Initial Cycle_ID is 1.
The cases are:

  1. If countervalue is changing, there is consumption, the Cycle_ID does not change
  2. If countervalue doesn’t change, there is no consumption, the equipment is stopped, Cycle_ID does not change
  3. If contervalue is changing after a 1 hour period when there was no change, Cycle_ID should be increased by 1 (but only once)
    If you have an idea please let me know.
2

please provide some sample data with DDL and DML?

3

I succesfully solved it in the following way:

WITH CTE AS (
    SELECT 
        ID,
        Zeit as Time,
        Zaehlerstand as Counter,
        LAG(Zaehlerstand, 1) OVER (ORDER BY Zeit) AS Prev_Counter,
        Zaehlerstand-LAG(Zaehlerstand, 1) OVER (ORDER BY Zeit) AS P1_Cons,
        Zaehlerstand-LAG(Zaehlerstand, 3) OVER (ORDER BY Zeit) AS P2_Cons,
        Zaehlerstand-LAG(Zaehlerstand, 5) OVER (ORDER BY Zeit) AS P3_Cons
    FROM 
        emspressiggas.$Kiln

),
CTE2 AS (
    SELECT 
        ID,
        Time,
        Counter,
        Prev_Counter,
        P1_Cons,
        P2_Cons,
        P3_Cons,
        CASE 
            WHEN Prev_Counter IS NULL THEN 0
            WHEN P1_Cons = P2_Cons AND P2_Cons = P3_Cons and P1_Cons = P3_Cons and P1_Cons+P2_Cons+P3_Cons != 0 THEN 1
            ELSE 0
        END AS Cycle_Change
    FROM 
        CTE
),
CTE3 AS (
    SELECT 
        ID,
        Time,
        Counter,
        Prev_Counter,
        P1_Cons,
        P2_Cons,
        P3_Cons,
        Cycle_Change,
        CASE 
            WHEN P1_Cons+P2_Cons+P3_Cons = 0 THEN null
            ELSE SUM(Cycle_Change) OVER (ORDER BY Time)
        END AS Cycle_ID
    FROM 
        CTE2
)
SELECT 
    Cycle_ID,
    Time as Time_from,
    max(Time) as Time_to,
    Prev_Counter as Counter_from,
    max(Counter) as Counter_to,
    sum(P1_Cons)

FROM 
    CTE3
where $__timeFilter(Time) 
Group by Cycle_ID
ORDER BY Time
1 Like