我想计算用户开始时间和上次 session 的停止时间之间的差异..
表包含:用户数字、开始/停止unix时间戳。它在用户和开始之后排序
user start stop
1 150000 150010
1 160011 160050
1 160100 160200
2 150011 150023
2 155001 155055
2 160001 160500
3 159001 159550
我希望结果看起来像:
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..
是否有可能做到这一点,如果是这样,如何?
最佳答案
这在应用程序级别做得更好,但只是为了好玩,这里是在数据库级别:
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`
请注意,MySQL 中没有滞后/领先功能。你所能做的就是使用变量。
SELECT
子句一次处理一行。因此,您可以在 SELECT
子句的最后几行中分配当前行的值,从而将此变量用作 SELECT
子句第一行中“上一行”的值。另请注意,
ORDER BY
非常重要。表未排序。除非您使用 ORDER BY
子句指定顺序,否则不会对关系 DBMS 中的数据进行排序。编辑:
将其更改为
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滞后/领先功能?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/29556063/