时间戳

首页 > 解决方案 > SQL查询根据开始和结束时间查找并发会话

mysql - SQL查询根据开始和结束时间查找并发会话

问题描述

下面是一个示例数据集，显示了每个家庭的每台电视机的电视会话。家庭“111”在 500 时打开他们的电视“1”，在 570 时将其关闭。但是，这已在数据中作为 2 个单独的行捕获。您必须编写一个查询将其转换为单行。需要对所有其他后续事件进行类似的修改。请注意，一个有效的电视会话也可以分成 2 行以上（如第 5-8 行所示）。

输入： 表[会话]

Household_ID    TV_Set_ID   Start_time    End_time
    111               1           500         550
    111               1           550         570
    111               1           590         620
    111               1           650         670
    111               2           660         680
    111               2           680         700
    111               2           700         750
    111               2           750         770
    112               2           1050        1060
    113               1           1060        1080
    113               1           1080        1100
    113               1           1100        1120
    113               1           1500        1520

预期输出：-

Household_ID  TV_Set_ID     Start_time        End_time
111             1               500             570
111             1               590             620
111             1               650             670
111             2               660             770
112             2               1050            1060
113             1               1060            1120
113             1               1500            1520

我试图找到提前期并找到差异并计算该时间与结束时间之间的差异，并认为我可以分组，但随后该逻辑将不起作用，因为我们不仅想要开始和结束时间，甚至想要会话中的间隙. 我被逻辑困住了。有人可以告诉如何进一步进行吗？

with result as
 (
 select Household_ID, TV_Set_ID, Start_time, End_time, lead(Start_time)
 over (partition by Household_ID, TV_Set_ID order by Household_ID, TV_Set_ID) as lead_start
from session  )
 select *,lead_start - End_time as diff from result ;

标签： mysqlsql

解决方案

这是完成此操作的一种方法

在数据块中，我创建了组，该组被定义为之前的 end_time 与我的 start_time 不匹配的任何记录，如果它不同，则为其分配一个 group_number，否则我保持相同。

之后，在主块中，我按此 group_number 分组，以及 home_id、tv_set_id 以获得结果。

with data
  as (
select *
      ,case when lag(end_time) over(partition by household_id,tv_set_id order by end_time)
                <> start_time then 
            sum(1) over(partition by household_id,tv_set_id order by end_time)
            else 
            sum(0) over(partition by household_id,tv_set_id order by end_time)
        end as group_number
  from t
      )
select household_id
       ,tv_set_id
       ,min(start_time) as start_time
       ,max(end_time) as end_time
  from data
group by household_id,tv_set_id,group_number



+--------------+-----------+------------+----------+
| household_id | tv_set_id | start_time | end_time |
+--------------+-----------+------------+----------+
|          111 |         1 |        500 |      570 |
|          111 |         1 |        590 |      620 |
|          111 |         1 |        650 |      670 |
|          111 |         2 |        660 |      770 |
|          112 |         2 |       1050 |     1060 |
|          113 |         1 |       1060 |     1120 |
|          113 |         1 |       1500 |     1520 |
+--------------+-----------+------------+----------+

数据库小提琴链接 https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=ba5ade186ebc3cf693c505d863691670

推荐阅读