查找用户在线时间

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，因为它从不更改状态，所以我们使用当前的时间登录时间戳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

.

编辑:让问题clearer.Now来假设它在Server中，但是使用其他DBMS是可以的。

输入时间戳作为默认的日期时间格式存储，如YYYY: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分析函数的解决方案，您可能无法使用该解决方案。我还使用时间戳作为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子句中(我对“时间戳”使用时间戳类型；而有些数据库不喜欢对列名使用保留词("user“、”时间戳“))，尝试如下：

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