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:

If countervalue is changing, there is consumption, the Cycle_ID does not change

If countervalue doesn’t change, there is no consumption, the equipment is stopped, Cycle_ID does not change

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.

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