Lag() function along with group by SQL

I have a table of this format

time_stamp	sys_id	product_name	valuename	value
2024-01-03 00:35:00+05:30	1	a	bytes	10
2024-01-03 00:35:00+05:30	2	a	bytes	20
2024-01-03 00:35:00+05:30	3	a	bytes	30
2024-01-03 00:40:00+05:30	1	a	bytes	20
2024-01-03 00:40:00+05:30	2	a	bytes	20
2024-01-03 00:40:00+05:30	3	a	bytes	40

for a given set of time_stamp, sys_id, Product_name, valuename i want to find the difference between present and previous entry for that set and if the difference is equal to 0 then i want to have another columns breach_status for that set to be marked as 1 else 0 hence I'm using lag() function when i give sys_id in where clause it works fine bellow is the sql query and table output


    SELECT 
        TO_CHAR(time_stamp) as Time, 
        TO_CHAR(sys_id) as System_ID, 
        product_name, 
        valuename, 
        TO_CHAR(value), 
        (value - LAG(value, 1) OVER (ORDER BY time_stamp)) as diff,
        CASE WHEN value = 0 or diff = 0 or diff is null THEN 0 ELSE 1 END AS breach_status
    FROM table
    WHERE valuename='bytes' and system_id = '2' and 
        time_stamp BETWEEN '2024-01-03 00:35:00' AND '2024-01-03 00:40:00'
    ORDER BY time_stamp

time_stamp	sys_id	product_name	valuename	value	diff	breach_status
2024-01-03 00:35:00+05:30	1	a	bytes	10		0
2024-01-03 00:35:00+05:30	2	a	bytes	20		0
2024-01-03 00:35:00+05:30	3	a	bytes	30		0
2024-01-03 00:40:00+05:30	1	a	bytes	20	10	0
2024-01-03 00:40:00+05:30	2	a	bytes	20	0	1
2024-01-03 00:40:00+05:30	3	a	bytes	40	10	0

Here for timestamp = '2024-01-03 00:40:00+05:30', sys_id = 2, product_name = 'a' and valuename = 'bytes' the value difference is 0 hence breach_status for it is 1

But here i want to use group by because there are multiple different combination of timestamp,sys_id,product_name, valuename records.

Hence i wanted to use group by in the above query


    SELECT 
        TO_CHAR(time_stamp) as Time, 
        TO_CHAR(system_id) as System_ID, 
        customer_name, 
        valuename, 
        TO_CHAR(max(value)) as val,
        (max(value) - LAG(max(value), 1) OVER (ORDER BY time_stamp)) as diff,
        CASE WHEN value = 0 or diff = 0 or diff is null THEN 0 ELSE 1 END AS breach_status
    FROM table
    WHERE 
        time_stamp BETWEEN '2024-01-03 00:35:00' AND '2024-01-03 00:40:00'
    group by 
        time_stamp,
        system_id,
        customer_name,
        valuename
    ORDER BY time_stamp, diff, breach_status

but here instead of using of checking difference between its sets previous entry its checking difference with its above entry

time_stamp	sys_id	product_name	valuename	value	diff	breach_status
2024-01-03 00:35:00+05:30	1	a	bytes	10		0
2024-01-03 00:35:00+05:30	2	a	bytes	20	10	0
2024-01-03 00:35:00+05:30	3	a	bytes	30	10	0
2024-01-03 00:40:00+05:30	1	a	bytes	20	-10	0
2024-01-03 00:40:00+05:30	2	a	bytes	20	0	1
2024-01-03 00:40:00+05:30	3	a	bytes	40	-20	0

How I will be using this query In the end i will be using this query in grafana dashboard to raise alert based on breach value hence in select statement i want all to be in CHAR hence i converted it using to_char() but if i convert diff column to char then we cant compare it in the breach condition as its a string not a numeric.

Solution

you can refer this sql difference between two rows with group by

Here's you need to add partition by

    SELECT 
        TO_CHAR(time_stamp) as Time, 
        TO_CHAR(system_id) as System_ID, 
        customer_name, 
        valuename, 
        TO_CHAR(max(value)) as val,
        (max(value) - LAG(max(value), 1) OVER (partition by system_id, customer_name, valuename ORDER BY time_stamp)) as diff,
        CASE WHEN value = 0 or diff = 0 or diff is null THEN 0 ELSE 1 END AS breach_status
    FROM table
    WHERE 
        time_stamp BETWEEN '2024-01-03 00:35:00' AND '2024-01-03 00:40:00'
    group by 
        time_stamp,
        system_id,
        customer_name,
        valuename
    ORDER BY time_stamp```