468,790 Members | 1,876 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,790 developers. It's quick & easy.

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);
4 Weeks Ago #1
0 1385

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
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.