mysql - 将 Lead/Lag 函数转换为 MySQL

Question

我如何翻译下面的代码以便它在 MySQL 中运行。我是 MySQL 的新手，发现很难理解一些东西。

代码 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;

代码 1 的期望输出

代码 2:

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

代码 3:

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

代码 4:

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

Answer 1

你可以表达 lead()和 lag()通过 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); 

在上面你只需要指定 </>表达lead()和 lag()子查询中的功能，不要忘记使用 order by子句

编辑(主管):

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);