471,081 Members | 882 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,081 software developers and data experts.

Lag Function in Mysql

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);
Jul 5 '21 #1
0 1890

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Mike Chirico | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.