我的问题表:
userid logintime logouttime
1 1/8/17 10am 1/8/17 9pm
2 1/8/17 9am 1/8/17 10pm
3 1/8/17 11am 1/8/17 6pm
1 2/8/17 11am 2/8/17 7pm
2 2/8/17 6am 2/8/17 4pm
3 2/8/17 8am 2/8/17 3pm
1 3/8/17 4am 3/8/17 1pm
2 3/8/17 11am 3/8/17 11pm
3 3/8/17 5am 3/8/17 5pm
我需要sql查询用户从第一天到第二天之间的时间间隔
示例答案表:
userid logintime logouttime timegap
1 1/8/17 10am 1/8/17 9pm 25 hours
1 2/8/17 11am 2/8/17 7pm
用户 ID = 1 的时间间隔从 1/8/17 10am 到 2/8/17 11am 是 25小时
下面我在现有表上使用一个公用表表达式 (CTE(,该表达式按用户 ID 递增并按登录时间从最早到倒数排序,然后我将其连接到行号为 +1 的地方
WITH CTE AS(
SELECT
userid,
logintime,
logouttime,
ROW_NUMBER() OVER (PARTITION BY userid ORDER BY logintime ASC NULLS LAST) AS rn
FROM table
)
SELECT
CTE.userid,
CTE.logintime,
CTE.logouttime,
(CTE.logintime-CTE2.logintime) AS timegap
FROM CTE
LEFT JOIN ON CTE AS CTE2 ON (CTE.rn +1)=CTE2.rn AND CTE.userid=CTE2.userid