CODEWITHSUNDEEP
mysqlsql
HPT
HPT

Reputation: 39

Translate Lead/Lag function to MySQL

How do I translated the below codes so that it runs in MySQL. I am new to MySQL and finding it difficult to understand few things.

CODE 1:

SELECT t1.user_id,
           t1.visit_month LEAD (t1.visit_month, 1) OVER (partition BY t1.user_id ORDER BY t1.user_id, t1.visit_month)
    FROM (SELECT 
    user_id,
    month(date_time) as visit_month
FROM
    tbl_activity
    group by 1, 2
ORDER BY 1 , 2) t1;

Desired Output for Code 1

enter image description here

CODE 2:

SELECT user_id,
       visit_month,
       lead,
       lead — visit_month AS time_diff
FROM table3

CODE 3: 

SELECT user_id,
       Visit_month,
       lag(visit_month, 1) over (partition BY user_id ORDER BY user_id, visit_month)
 FROM table

CODE 4:

SELECT user_id,
       visit_month,
       lag,
       visit_month — lag AS time_diff
 FROM table2

Upvotes: 2

Views: 2905

Answers (1)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You could express the lead() and lag() function via subquery 

select user_id, month(date_time) as visit_month,
       month(date_time)-(select month(date_time) from tbl_activity 
                         where user_id = a.user_id and 
                         month(date_time) < month(a.date_time)
                         order by month(date_time) desc LIMIT 1) as time_diff -- This could be re-express via lag() function
from tbl_activity a
group by user_id, month(date_time);

In above you would need to just specify < / > to express the lead() and lag() functionality in subquery and don't forget to use order by clause

EDIT (Lead) :

select user_id, month(date_time) as visit_month,
       (select month(date_time) from tbl_activity 
       where user_id = a.user_id and 
       month(date_time) > month(a.date_time)
       order by month(date_time) LIMIT 1) as lead -- This could be re-express via lead() function
from tbl_activity a
group by user_id, month(date_time);

Upvotes: 6

Related Questions