删除会话记录中的时间重叠

ryevplcw 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(302)

我有一个sessions表，其中包含列（user\u id、sessions\u id、logon、logout），用户可以在同一时间打开多个会话，我的目标是计算每个用户在我的系统上花费的纯时间。我使用了以下查询：

SELECT
 T1.User_ID,
 SUM(T1.Duration) AS Duration
FROM (
 SELECT
  T2.User_ID,
  T2.Logon,
  (CASE WHEN T3.LogOn IS NULL OR T3.LogOn > T2.LogOut THEN T2.LogOut ELSE T3.LogOn END) AS LogOutEdited,
  DATEDIFF(MINUTE, T2.Logon, (CASE WHEN T3.LogOn IS NULL OR T3.LogOn > T2.LogOut THEN T2.LogOut ELSE T3.LogOn END)) AS Duration
 FROM (
  SELECT
   (DENSE_RANK() OVER (PARTITION BY User_ID ORDER BY LogOn)) AS Serial,
   User_ID, LogOn, LogOut
  FROM Sessions
 ) AS T2

 LEFT JOIN (
  SELECT
   (DENSE_RANK() OVER (PARTITION BY User_ID ORDER BY LogOn)) AS Serial,
   User_ID, LogOn, LogOut
  FROM Sessions
 ) AS T3
 ON T2.User_ID = T3.User_ID
  AND T2.Serial = T3.Serial - 1
) AS T1
GROUP BY T1.User_ID

此查询比较会话的结束和下一个会话的开始，并调整第一个会话的结束以消除重叠时间。它确实给出了正确的结果（我认为：）），但它的性能不受赞赏，有没有更有效的逻辑我可以在这里应用？
编辑：
样本数据：

--------------------------------------------------------------------
| User_ID | Session_ID |        LogOn        |        LogOut       |
--------------------------------------------------------------------
|    1    |    100     | 2020-01-01 01:00:00 | 2020-01-01 01:30:00 |
--------------------------------------------------------------------
|    1    |    101     | 2020-01-01 01:15:00 | 2020-01-01 01:45:00 |
--------------------------------------------------------------------
|    1    |    102     | 2020-01-01 01:35:00 | 2020-01-01 01:40:00 |
--------------------------------------------------------------------
|    2    |    103     | 2020-01-01 03:13:00 | 2020-01-01 03:23:00 |
--------------------------------------------------------------------
|    1    |    104     | 2020-01-01 04:00:00 | 2020-01-01 04:15:00 |
--------------------------------------------------------------------

预期结果：

----------------------
| User_ID | Duration |
----------------------
|    1    |    60    |
----------------------
|    2    |    10    |
----------------------

意外结果：

----------------------
| User_ID | Duration |
----------------------
|    1    |    80    |
----------------------
|    2    |    10    |
----------------------

sql window-functions sql-server Date gaps-and-islands

来源：https://stackoverflow.com/questions/62524877/remove-time-overlaps-in-sessions-records

1条答案

按热度按时间

erhoui1w1#

这是一个缺口和孤岛问题，您试图识别孤岛，并为每个用户合计它们的总持续时间。
下面是一种使用 lag() 还有一扇Windows sum() 定义组。以下查询为每组重叠会话提供一行：

select user_id, min(log_in) log_in, max(log_out) log_out
from (
    select 
        t.*,
        sum(case when log_in <= lag_log_out then 0 else 1 end) 
            over(partition by user_id order by log_in)  as grp
    from (
        select 
            t.*, 
            lag(log_out) over(partition by user_id order by log_in) as lag_log_out
        from mytable t
    ) t
) t
group by user_id, grp

您可以添加一个聚合级别来计算每个用户花费的总时间：

select user_id, sum(datediff(minute, login, log_out)) duration
from (
    select user_id, min(log_in) log_in, max(log_out) log_out
    from (
        select 
            t.*,
            sum(case when log_in <= lag_log_out then 0 else 1 end) 
                over(partition by user_id order by log_in)  as grp
        from (
            select 
                t.*, 
                lag(log_out) over(partition by user_id order by log_in) as lag_log_out
            from mytable t
        ) t
    ) t
    group by user_id, grp
) t
group by user_id

赞(0）回复(0）举报 2021-07-24

我来回答

删除会话记录中的时间重叠

1条答案

相关问题

热门标签

最新问答