查找用户在线时间

Question

对如何编写自连接查询有疑问。 联机会话表包含所有用户活动。每个活动都有一个状态ID，TimeStap记录用户登录时间。

就像： 例：

State    TimeStamp     User
X        1300         A
Y        1700         A
X        0700         B
Z        1500         B
Y        1600         B
X        2100         C

一点解释：在上表中，用户 A 在 1300 上登录状态 X，然后在 1700 上登录状态 Y，因此用户 A 在状态 X 中花费 0400(假设是 4 小时)。 应用于用户 B 的相同逻辑。 然后是用户 C，因为它永远不会改变 sate，所以我们使用当前时间 - X 的登录时间戳。

输出应如下所示：

State    Time             User
X   0400(or 4)           A
X   0800(or 8)           B
Z   0100(or 1)           B
X   result of Now-2100   C

.......

编辑：只是让问题更清楚。现在让我们假设它在SQL Server DMBS中，但是可以使用其他DBMS。

输入时间戳存储为默认日期时间格式，如 YYYY-MM-DD HH：MM：SS。

Answer 1

你没有提到你正在使用哪个DBMS，所以我写了这个如何在MS SQL Server(TSQL)中做到这一点。 您需要访问LAG功能，该功能不是通用的。

LAG的作用是允许您根据某些共享列值比较上一行的值，在本例中为User. 此代码在prev_字段中捕获这些比较。 我正在使用count()来区分具有多行的用户和仅具有一行的用户。 单行用户在union all后单独处理。

您会注意到，在最后的输出步骤之前，我不会使用您的字段名称。 这是因为State、Timestamp和User都是保留字，即在SQL代码中做某事的字。 我强烈建议您使用不是保留字的字段名称。

此代码确实有一个主要限制;如果不是同一天，则它不适用于现在减去时间部分。 因此，在您的示例中，它必须在同一天的 21：01 和 23：59 之间才能正常工作。 如果你想稳健地做到这一点，你会使用datetime格式来适应你的时代，这将使这更容易并消除限制。 但这个答案是针对你的数据，所以：

SELECT 
b.prev_state AS [State]
,b.Online_time - b.prev_time AS [Time]
,b.U_ID as [User]
FROM
(SELECT 
t.Online_state
,t.U_ID
,t.Online_time
,LAG(t.online_time) OVER (PARTITION BY t.U_ID ORDER BY t.U_ID, t.online_time) AS prev_time
,LAG(t.online_state) OVER (PARTITION BY t.U_ID ORDER BY t.U_ID, t.online_time) AS prev_state
FROM online_t AS t
inner join 
(SELECT 
U_ID, 
count(U_ID) AS tot
FROM online_t
GROUP BY U_ID) AS a
on t.U_ID = a.U_ID
WHERE tot > 1) AS b
WHERE prev_time is not null
union all
SELECT
t.Online_state AS [State]
,concat(datepart(hh,getdate()),'00') - t.Online_time AS [Time]
,t.U_ID AS [USER]
FROM online_t AS t 
inner join
(SELECT 
U_ID
,count(U_ID) as tot
FROM online_t
GROUP BY U_ID) as a
on t.U_ID = a.U_ID
WHERE tot = 1

Answer 2

我有一个使用 Oracle 分析函数的解决方案，您可能无法使用。我也使用时间戳作为预言机varchars。

我在子查询中使用 LEAD() 来返回"下一个用户"和"下一次"。 然后使用 CASE 语句来处理不同的方案。

SELECT M.THESTATE,
CASE 
WHEN M.USERID = M2.NEXT_USER THEN M2.NEXT_TIME-M.THETIME
WHEN M.USERID <> M2.NEXT_USER THEN NULL
ELSE M.THETIME-0 END AS TOTALTIME 
,M.USERID
FROM MYTEST M
JOIN 
(
SELECT USERID, THESTATE, THETIME
,LEAD(THETIME) OVER (ORDER BY USERID, THETIME) AS NEXT_TIME
,LEAD(USERID) OVER (ORDER BY USERID, THETIME) AS NEXT_USER
FROM MYTEST
ORDER BY USERID
) M2 ON M2.USERID = M.USERID AND M2.THESTATE=M.THESTATE
WHERE 
CASE     
WHEN M.USERID = M2.NEXT_USER THEN M2.NEXT_TIME-M.THETIME
WHEN M.USERID <> M2.NEXT_USER THEN NULL
ELSE M.THETIME-0 END 
IS NOT NULL;

Answer 3

将您的输入包含在 WITH 子句中(我使用 TIMESTAMP 类型作为您的"时间戳";如果您使用保留字("user"、"timestamp")作为列名，有些数据库不喜欢)，请尝试以下操作：

WITH
-- input, don't use in query
input(state,"timestamp","user") AS (
SELECT 'X',TIMESTAMP '2017-03-15 13:00:00','A'
UNION ALL SELECT 'Y',TIMESTAMP '2017-03-15 17:00:00','A'
UNION ALL SELECT 'X',TIMESTAMP '2017-03-15 07:00:00','B'
UNION ALL SELECT 'Z',TIMESTAMP '2017-03-15 15:00:00','B'
UNION ALL SELECT 'Y',TIMESTAMP '2017-03-15 16:00:00','B'
UNION ALL SELECT 'X',TIMESTAMP '2017-03-15 21:00:00','C'
)
,
-- start real query here, comma above would 
-- be the WITH keyword
state_duration_user AS (
SELECT
state
, IFNULL(
LEAD("timestamp") OVER(ORDER BY "timestamp")
, CURRENT_TIMESTAMP
) - "timestamp"
AS "time"
, "user"
FROM input
)
SELECT 
state
, CAST(SUM("time") AS TIME(0)) AS "time"
, "user"    
FROM state_duration_user
GROUP BY
state
, "user"
;
state|time    |user
Y    |04:00:00|A
Y    |01:00:00|B
Z    |01:00:00|B
X    |02:00:00|A
X    |06:00:00|B
X    |07:59:19|C