MySQL 滞后函数

Question

我正在编写代码以获得延迟和提前期。我能够成功获得正确的超前值，但获得错误的滞后值。下面是代码:

Select t1.user_id, t1.time_period,

t1.time_period - (select t2.time_period from monthly_usage as t2
                         where t2.user_id = t1.user_id and 
                         t2.time_period < t1.time_period
                         order by t1.time_period desc LIMIT 1) as lag,
(SELECT 
            t2.time_period
        FROM
            monthly_usage as t2
        WHERE
            t2.user_id = t1.user_id
                AND t2.time_period > t1.time_period
        ORDER by t1.time_period
        LIMIT 1) AS lead

from monthly_usage as t1;

我的输出是这样的

但我想要如下输出。

我的代码有错误吗？

Answer 1

让我更正您对滞后的查询

select t1.user_id, t1.time_period,
       (select t2.time_period 
        from monthly_usage as t2
        where t2.user_id = t1.user_id and 
              t2.time_period < t1.time_period
              order by t2.time_period desc LIMIT 1) as lag, -- order by t2.time_period desc
....
from monthly_usage as t1;