1
Could you please help me to resolve my UAT issue. I need to do some calculation in my configs values (on 5 Mins Interval). For that i need to find delta between 2 times using LAG function. LAG Function works fine if the config values in Ascending order. if any of the values less that previous values it failed . Let me give Ex
Device ID Interface Time Config
rddbernsg23oop42 32583 rdd_test 7/2/2021 2:30 12552774
rddbernsg23oop42 32583 rdd_test 7/2/2021 2:35 12587612
rddbernsg23oop42 32583 rdd_test 7/2/2021 2:40 12640452
rddbernsg23oop42 32583 rdd_test 7/2/2021 2:45 12901869
rddbernsg23oop42 32583 rdd_test 7/2/2021 2:50 13374174
rddbernsg23oop42 32583 rdd_test 7/2/2021 2:55 1600005 ---- failes here
rddbernsg23oop42 32583 rdd_test 7/2/2021 3:00 1601825
rddbernsg23oop42 32583 rdd_test 7/2/2021 3:05 1604848
for that i use below query .It works only when the config have values in Ascending order it means if the table T1 have only first 5 rows. ( time between 2;30 to 2.50)
SELECT Device, ID , Interface, Time, Config , lag(Config ,1) over (order by Time) as "Lag time" ,
Config-lag(Config,1) OVER( order by Time)) as 'Delta config'
FROM T1
WHERE Time BETWEEN FROM_UNIXTIME(1625192700) AND FROM_UNIXTIME(1625194200);
It fails when i include 6th row onwards by adjusting time in where clause
SELECT Device , ID , Interface, Time, Config , lag(Config ,1) over (order by Time) as "Lag time" ,
Config-lag(Config,1) OVER( order by Time)) as 'Delta config'
FROM T1
WHERE Time BETWEEN FROM_UNIXTIME(1625192700) AND FROM_UNIXTIME(1625195400);
