MySQL的滞后/提前功能? [英] MySQL lag/lead function?

问题描述

我想计算上次会话的用户开始时间和停止时间之间的差..

I want to calculate the difference between a users start time and stop time from previous session..

该表包含:用户数字，开始/停止unix时间戳.在用户AND开始之后对其进行排序

Table contains: user numeric, start/stop unix timestamp. It is sorted after both user AND start

user  start   stop
1    150000 150010
1    160011 160050
1    160100 160200
2    150011 150023
2    155001 155055
2    160001 160500
3    159001 159550

我希望结果看起来像这样:

Im expecting the result to look like:

user start   stop  diff
1   150000 150010  160011-150010=10001
1   160011 160050  160100-160050
1   160100 160200  0 (new user on next row)
2   150011 150023  155001-150023
2   155001 155055  160001-155055
2   160001 160500  0 (new user on next row)
3   159001 159550  and so on..

是否可以这样做，如果可以，怎么办?

Is it possible to do this, if so, how?

推荐答案

最好在应用程序级别完成此操作，但这只是出于娱乐目的，这里是在数据库级别:

This is better done on application level, but just for fun, here it is on database level:

select `user`, `start`, `stop`, diff from (
    select
    t.*
    , if(@prev_user = `user`, (`stop` - @prev) * -1, 0) as diff
    , @prev := `start`
    , @prev_user := `user`
    from
    t
    , (select @prev := null, @prev_user := null) var_init
    order by `user`, `start` desc
) sq
order by `user`, `start`

• 在 sqlfiddle
中实时查看它的运行情况
• see it working live in an sqlfiddle

请注意，MySQL中没有滞后/超前功能.您所能做的就是使用变量. SELECT子句一次被处理一行.因此，您可以在SELECT子句的最后几行中分配当前行的值，并因此在SELECT子句的第一行中将此变量用作上一行"的值.
另请注意，ORDER BY非常重要.表未排序.除非您使用ORDER BY子句指定顺序，否则关系DBMS中的数据不会排序.

Note, that there are no lag/lead functions in MySQL. All you can do, is to use variables. The SELECT clause gets processed one line at a time. So you can assign the value of the current row in the last lines of the SELECT clause and therefore use this variable as the value of "the previous row" in the first lines of the SELECT clause.
Also note, that the ORDER BY is very important. A table is not sorted. Data in a relational DBMS is not sorted unless you specify an order with the ORDER BY clause.

• 在此处
中了解有关在查询中使用变量的更多信息.
• read more about using variables in queries here

将其更改为

UPDATE inactivitytmp
JOIN (
SELECT
inactivitytmp.*
, if(@prev_user_id = `user_id`, (`end_ts` - @prev) * -1, 0) as diff2
, @prev := `start_ts`
, @prev_user_id := `user_id`
FROM
inactivitytmp
, (SELECT @prev := null, @prev_user_id := null) var_init
ORDER BY `user_id`, `start_ts` DESC
) query_alias
ON inactivitytmp.user_id=query_alias.user_id AND inactivitytmp.start_ts=q uery_alias.start_ts AND inactivitytmp.end_ts=query_alias.end_ts
SET inactivitytmp.diff=query_alias.diff2;

这篇关于MySQL的滞后/提前功能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！