根据日期条件合并不同的行
我在 SO 上找到了这篇文章，但它用 bigquery 解决了，我用 SQL 查询尝试过，但我在使用太多变量和循环时遇到了问题.尽管结果如此，但此查询可能不是最佳的.希望大家帮帮我
我有一个包含 playid、userid、创建和停止的表.我想通过保留最早创建的行并在最早创建的 120 分钟内停止最新行来合并某些行.
预期结果:
| playid | 用户名 | 创建 | 停止 |
|---|---|---|---|
| 1 | a01 | 2021-01-30 02:29:58.559858 | 2021-01-30 04:16:25.948311 |
| 8 | a01 | 2021-01-30 04:26:28.968568 | 2021-01-30 06:12:37.210234 |
| 21 | a01 | 2021-01-30 14:41:01.529666 | 2021-01-30 15:05:09.532786 |
| 23 | a01 | 2021-01-31 15:11:08.547111 | 2021-01-31 17:03:43.464444 |
数据:
| playid | 用户名 | 创建 | 停止 |
|---|---|---|---|
| 1 | a01 | 2021-01-30 02:29:58.559858 | 2021-01-30 02:40:56.558436 |
| 2 | a01 | 2021-01-30 02:41:24.023358 | 2021-01-30 02:55:24.112713 |
| 3 | a01 | 2021-01-30 02:57:30.178579 | 2021-01-30 03:11:14.866678 |
| 4 | a01 | 2021-01-30 03:11:41.098424 | 2021-01-30 03:22:50.155918 |
| 5 | a01 | 2021-01-30 03:23:20.545288 | 2021-01-30 03:36:37.027486 |
| 6 | a01 | 2021-01-30 03:46:10.237971 | 2021-01-30 03:59:17.526151 |
| 7 | a01 | 2021-01-30 03:59:57.020326 | 2021-01-30 04:16:25.948311 |
| 8 | a01 | 2021-01-30 04:26:28.968568 | 2021-01-30 04:30:11.863079 |
| 9 | a01 | 2021-01-30 04:30:14.43786 | 2021-01-30 04:41:42.231915 |
| 10 | a01 | 2021-01-30 04:43:17.669945 | 2021-01-30 04:45:10.443101 |
| 11 | a01 | 2021-01-30 04:45:14.50346 | 2021-01-30 04:47:07.082114 |
| 12 | a01 | 2021-01-30 04:47:10.334574 | 2021-01-30 04:49:30.96017 |
| 13 | a01 | 2021-01-30 04:49:34.146011 | 2021-01-30 04:50:54.45988 |
| 14 | a01 | 2021-01-30 04:50:57.948305 | 2021-01-30 05:11:01.246284 |
| 15 | a01 | 2021-01-30 05:39:29.387396 | 2021-01-30 05:41:39.508654 |
| 16 | a01 | 2021-01-30 05:41:44.524951 | 2021-01-30 05:43:38.231266 |
| 17 | a01 | 2021-01-30 05:43:40.785809 | 2021-01-30 05:54:40.711381 |
| 18 | a01 | 2021-01-30 05:55:10.851725 | 2021-01-30 05:58:24.262351 |
| 19 | a01 | 2021-01-30 05:58:29.43821 | 2021-01-30 06:00:50.870644 |
| 20 | a01 | 2021-01-30 06:00:54.168696 | 2021-01-30 06:12:37.210234 |
| 21 | a01 | 2021-01-30 14:41:01.529666 | 2021-01-30 14:53:01.349479 |
| 22 | a01 | 2021-01-30 14:53:48.053136 | 2021-01-30 15:05:09.532786 |
| 23 | a01 | 2021-01-31 15:11:08.547111 | 2021-01-31 15:11:37.564802 |
| 24 | a01 | 2021-01-31 15:11:40.248499 | 2021-01-31 15:13:40.21787 |
| 25 | a01 | 2021-01-31 15:13:59.373145 | 2021-01-31 15:31:54.099898 |
| 26 | a01 | 2021-01-31 15:32:23.20448 | 2021-01-31 15:46:33.993751 |
| 27 | a01 | 2021-01-31 16:55:19.141051 | 2021-01-31 17:03:43.464444 |
这是我的查询
DECLARE @FAKELIST TABLE(PLAYID INT, USERID VARCHAR(10), CREATED DATETIME, [STOPPED] DATETIME)声明@result TABLE(PLAYID INT, USERID VARCHAR(10), CREATED DATETIME, [STOPPED] DATETIME)INSERT @FAKELIST(PLAYID, USERID, CREATED, [STOPPED]) SELECT * FROM MyTable声明@CREATED DATETIME声明@STOPPED1 日期时间声明@STOPPED2 日期时间声明@playid int声明@userid varchar(10)while(exists (select top(1) * from @FAKELIST))开始设置@CREATED =(从@FAKELIST 选择top(1) CREATED)set @playid =(从@FAKELIST 中选择 top(1) PLAYID)set @userid=(从@FAKELIST 中选择 top(1) USERID)设置@STOPPED1 =(从@FAKELIST 顺序中选择[STOPPED] PLAYID OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY)设置@STOPPED2 =(从@FAKELIST 顺序中选择[STOPPED] PLAYID OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY)while(DATEDIFF(MINUTE,@CREATED,@STOPPED2) <120)开始从@FAKELIST 中删除 [STOPPED] =@STOPPED1设置@STOPPED1 =(从@FAKELIST 顺序中选择[STOPPED] PLAYID OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY)设置@STOPPED2 =(从@FAKELIST 顺序中选择[STOPPED] PLAYID OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY)结尾插入@result(PLAYID,USERID,CREATED,[STOPPED]) 值 (@playid,@userid,@CREATED,@STOPPED1)从@FAKELIST 删除其中 PLAYID = @playid从@FAKELIST 中删除 [STOPPED] = @STOPPED1结尾SELECT * FROM @result这里我将尝试逐个解释查询(恐怕我可能不是一个很好的解释者):
架构和插入语句:
创建表数据(playid int, userid varchar(10), created datetime,stopped datetime)；插入数据值(1,'a01','01/30/2021 02:29:58.560','01/30/2021 02:40:56.558');插入数据值(2,'a01','01/30/2021 02:41:24.023','01/30/2021 02:55:24.113');插入数据值(3,'a01','01/30/2021 02:57:30.179','01/30/2021 03:11:14.867');插入数据值(4,'a01','01/30/2021 03:11:41.098','01/30/2021 03:22:50.156');插入数据值(5,'a01','01/30/2021 03:23:20.545','01/30/2021 03:36:37.027');插入数据值(6,'a01','01/30/2021 03:46:10.238','01/30/2021 03:59:17.526');插入数据值(7,'a01','01/30/2021 03:59:57.020','01/30/2021 04:16:25.948');插入数据值(8,'a01','01/30/2021 04:26:28.969','01/30/2021 04:30:11.863');插入数据值(9,'a01','01/30/2021 04:30:14.438','01/30/2021 04:41:42.232');插入数据值(10,'a01','01/30/2021 04:43:17.670','01/30/2021 04:45:10.443');插入数据值(11,'a01','01/30/2021 04:45:14.503','01/30/2021 04:47:07.082');插入数据值(12,'a01','01/30/2021 04:47:10.335','01/30/2021 04:49:30.960');插入数据值(13,'a01','01/30/2021 04:49:34.146','01/30/2021 04:50:54.460');插入数据值(14,'a01','01/30/2021 04:50:57.948','01/30/2021 05:11:01.246');插入数据值(15,'a01','01/30/2021 05:39:29.387','01/30/2021 05:41:39.509');插入数据值(16,'a01','01/30/2021 05:41:44.525','01/30/2021 05:43:38.231');插入数据值(17,'a01','01/30/2021 05:43:40.786','01/30/2021 05:54:40.711');插入数据值(18,'a01','01/30/2021 05:55:10.852','01/30/2021 05:58:24.262');插入数据值(19,'a01','01/30/2021 05:58:29.438','01/30/2021 06:00:50.871');插入数据值(20,'a01','01/30/2021 06:00:54.169','01/30/2021 06:12:37.210');插入数据值(21,'a01','01/30/2021 14:41:01.530','01/30/2021 14:53:01.349');插入数据值(22,'a01','01/30/2021 14:53:48.053','01/30/2021 15:05:09.533');插入数据值(23,'a01','01/31/2021 15:11:08.547','01/31/2021 15:11:37.565');插入数据值(24,'a01','01/31/2021 15:11:40.248','01/31/2021 15:13:40.218');插入数据值(25,'a01','01/31/2021 15:13:59.373','01/31/2021 15:31:54.100');插入数据值(26,'a01','01/31/2021 15:32:23.204','01/31/2021 15:46:33.994');插入数据值(27,'a01','01/31/2021 16:55:19.141','01/31/2021 17:03:43.464');查询的第一部分:
从数据d中选择d.playid,d.userid,d.created,d.stopped,dt.minstopped外申请(从数据 dt 中选择 max(stopped) minstopped其中 d.playid在上面的查询中，我添加了一个名为 minstopped 的新列，它将计算最大停止日期时间.背后的逻辑是，当我们计算第一行的 minstopped 时，我们将从停止日期不大于第一行的创建日期加上 120 分钟的所有行计算 max(sopped).
输出:
| playid | userid | 创建 | 停止 | minstopped |
|---|---|---|---|---|
| 1 | a01 | 2021-01-30 02:29:58.560 | 2021-01-30 02:40:56.557 | 2021-01-30 04:16:25.947 |
| 2 | a01 | 2021-01-30 02:41:24.023 | 2021-01-30 02:55:24.113 | 2021-01-30 04:41:42.233 |
| 3 | a01 | 2021-01-30 02:57:30.180 | 2021-01-30 03:11:14.867 | 2021-01-30 04:50:54.460 |
| 4 | a01 | 2021-01-30 03:11:41.097 | 2021-01-30 03:22:50.157 | 2021-01-30 05:11:01.247 |
| 5 | a01 | 2021-01-30 03:23:20.547 | 2021-01-30 03:36:37.027 | 2021-01-30 05:11:01.247 |
| 6 | a01 | 2021-01-30 03:46:10.237 | 2021-01-30 03:59:17.527 | 2021-01-30 05:43:38.230 |
| 7 | a01 | 2021-01-30 03:59:57.020 | 2021-01-30 04:16:25.947 | 2021-01-30 05:58:24.263 |
| 8 | a01 | 2021-01-30 04:26:28.970 | 2021-01-30 04:30:11.863 | 2021-01-30 06:12:37.210 |
| 9 | a01 | 2021-01-30 04:30:14.437 | 2021-01-30 04:41:42.233 | 2021-01-30 06:12:37.210 |
| 10 | a01 | 2021-01-30 04:43:17.670 | 2021-01-30 04:45:10.443 | 2021-01-30 06:12:37.210 |
| 11 | a01 | 2021-01-30 04:45:14.503 | 2021-01-30 04:47:07.083 | 2021-01-30 06:12:37.210 |
| 12 | a01 | 2021-01-30 04:47:10.337 | 2021-01-30 04:49:30.960 | 2021-01-30 06:12:37.210 |
| 13 | a01 | 2021-01-30 04:49:34.147 | 2021-01-30 04:50:54.460 | 2021-01-30 06:12:37.210 |
| 14 | a01 | 2021-01-30 04:50:57.947 | 2021-01-30 05:11:01.247 | 2021-01-30 06:12:37.210 |
| 15 | a01 | 2021-01-30 05:39:29.387 | 2021-01-30 05:41:39.510 | 2021-01-30 06:12:37.210 |
| 16 | a01 | 2021-01-30 05:41:44.527 | 2021-01-30 05:43:38.230 | 2021-01-30 06:12:37.210 |
| 17 | a01 | 2021-01-30 05:43:40.787 | 2021-01-30 05:54:40.710 | 2021-01-30 06:12:37.210 |
| 18 | a01 | 2021-01-30 05:55:10.853 | 2021-01-30 05:58:24.263 | 2021-01-30 06:12:37.210 |
| 19 | a01 | 2021-01-30 05:58:29.437 | 2021-01-30 06:00:50.870 | 2021-01-30 06:12:37.210 |
| 20 | a01 | 2021-01-30 06:00:54.170 | 2021-01-30 06:12:37.210 | null |
| 21 | a01 | 2021-01-30 14:41:01.530 | 2021-01-30 14:53:01.350 | 2021-01-30 15:05:09.533 |
| 22 | a01 | 2021-01-30 14:53:48.053 | 2021-01-30 15:05:09.533 | null |
| 23 | a01 | 2021-01-31 15:11:08.547 | 2021-01-31 15:11:37.567 | 2021-01-31 17:03:43.463 |
| 24 | a01 | 2021-01-31 15:11:40.247 | 2021-01-31 15:13:40.217 | 2021-01-31 17:03:43.463 |
| 25 | a01 | 2021-01-31 15:13:59.373 | 2021-01-31 15:31:54.100 | 2021-01-31 17:03:43.463 |
| 26 | a01 | 2021-01-31 15:32:23.203 | 2021-01-31 15:46:33.993 | 2021-01-31 17:03:43.463 |
| 27 | a01 | 2021-01-31 16:55:19.140 | 2021-01-31 17:03:43.463 | null |
第二部分将停止日期与上一组第一行的创建日期相距超过 120 分钟的所有行组成一个组.所以我们现在有一组行，用于创建日期和停止日期之间的每 120 分钟间隔.
with cte as(从数据 d 中选择 d.playid,d.userid,d.created,d.stopped,dt.minstopped外申请(从数据 dt 中选择 max(stopped) minstopped其中 d.playid cte2.minstopped)从 cte2 中选择 *按级别排序，playid <块引用><前>playid |用户名 |创建 |停止|停顿|等级-----: |:----- |:-------------- |:-------------- |:-------------- |----:1 |a01 |2021-01-30 02:29:58.560 |2021-01-30 02:40:56.557 |2021-01-30 04:16:25.947 |18 |a01 |2021-01-30 04:26:28.970 |2021-01-30 04:30:11.863 |2021-01-30 06:12:37.210 |39 |a01 |2021-01-30 04:30:14.437 |2021-01-30 04:41:42.233 |2021-01-30 06:12:37.210 |310 |a01 |2021-01-30 04:43:17.670 |2021-01-30 04:45:10.443 |2021-01-30 06:12:37.210 |311 |a01 |2021-01-30 04:45:14.503 |2021-01-30 04:47:07.083 |2021-01-30 06:12:37.210 |312 |a01 |2021-01-30 04:47:10.337 |2021-01-30 04:49:30.960 |2021-01-30 06:12:37.210 |313 |a01 |2021-01-30 04:49:34.147 |2021-01-30 04:50:54.460 |2021-01-30 06:12:37.210 |314 |a01 |2021-01-30 04:50:57.947 |2021-01-30 05:11:01.247 |2021-01-30 06:12:37.210 |315 |a01 |2021-01-30 05:39:29.387 |2021-01-30 05:41:39.510 |2021-01-30 06:12:37.210 |316 |a01 |2021-01-30 05:41:44.527 |2021-01-30 05:43:38.230 |2021-01-30 06:12:37.210 |317 |a01 |2021-01-30 05:43:40.787 |2021-01-30 05:54:40.710 |2021-01-30 06:12:37.210 |318 |a01 |2021-01-30 05:55:10.853 |2021-01-30 05:58:24.263 |2021-01-30 06:12:37.210 |319 |a01 |2021-01-30 05:58:29.437 |2021-01-30 06:00:50.870 |2021-01-30 06:12:37.210 |320 |a01 |2021-01-30 06:00:54.170 |2021-01-30 06:12:37.210 |空 |321 |a01 |2021-01-30 14:41:01.530 |2021-01-30 14:53:01.350 |2021-01-30 15:05:09.533 |322 |a01 |2021-01-30 14:53:48.053 |2021-01-30 15:05:09.533 |空 |323 |a01 |2021-01-31 15:11:08.547 |2021-01-31 15:11:37.567 |2021-01-31 17:03:43.463 |324 |a01 |2021-01-31 15:11:40.247 |2021-01-31 15:13:40.217 |2021-01-31 17:03:43.463 |325 |a01 |2021-01-31 15:13:59.373 |2021-01-31 15:31:54.100 |2021-01-31 17:03:43.463 |326 |a01 |2021-01-31 15:32:23.203 |2021-01-31 15:46:33.993 |2021-01-31 17:03:43.463 |327 |a01 |2021-01-31 16:55:19.140 |2021-01-31 17:03:43.463 |空 |321 |a01 |2021-01-30 14:41:01.530 |2021-01-30 14:53:01.350 |2021-01-30 15:05:09.533 |521 |a01 |2021-01-30 14:41:01.530 |2021-01-30 14:53:01.350 |2021-01-30 15:05:09.533 |521 |a01 |2021-01-30 14:41:01.530 |2021-01-30 14:53:01.350 |2021-01-30 15:05:09.533 |521 |a01 |2021-01-30 14:41:01.530 |2021-01-30 14:53:01.350 |2021-01-30 15:05:09.533 |521 |a01 |2021-01-30 14:41:01.530 |2021-01-30 14:53:01.350 |2021-01-30 15:05:09.533 |521 |a01 |2021-01-30 14:41:01.530 |2021-01-30 14:53:01.350 |2021-01-30 15:05:09.533 |521 |a01 |2021-01-30 14:41:01.530 |2021-01-30 14:53:01.350 |2021-01-30 15:05:09.533 |521 |a01 |2021-01-30 14:41:01.530 |2021-01-30 14:53:01.350 |2021-01-30 15:05:09.533 |521 |a01 |2021-01-30 14:41:01.530 |2021-01-30 14:53:01.350 |2021-01-30 15:05:09.533 |521 |a01 |2021-01-30 14:41:01.530 |2021-01-30 14:53:01.350 |2021-01-30 15:05:09.533 |521 |a01 |2021-01-30 14:41:01.530 |2021-01-30 14:53:01.350 |2021-01-30 15:05:09.533 |521 |a01 |2021-01-30 14:41:01.530 |2021-01-30 14:53:01.350 |2021-01-30 15:05:09.533 |522 |a01 |2021-01-30 14:53:48.053 |2021-01-30 15:05:09.533 |空 |522 |a01 |2021-01-30 14:53:48.053 |2021-01-30 15:05:09.533 |空 |522 |a01 |2021-01-30 14:53:48.053 |2021-01-30 15:05:09.533 |空 |522 |a01 |2021-01-30 14:53:48.053 |2021-01-30 15:05:09.533 |空 |522 |a01 |2021-01-30 14:53:48.053 |2021-01-30 15:05:09.533 |空 |522 |a01 |2021-01-30 14:53:48.053 |2021-01-30 15:05:09.533 |空 |522 |a01 |2021-01-30 14:53:48.053 |2021-01-30 15:05:09.533 |空 |522 |a01 |2021-01-30 14:53:48.053 |2021-01-30 15:05:09.533 |空 |522 |a01 |2021-01-30 14:53:48.053 |2021-01-30 15:05:09.533 |空 |522 |a01 |2021-01-30 14:53:48.053 |2021-01-30 15:05:09.533 |空 |522 |a01 |2021-01-30 14:53:48.053 |2021-01-30 15:05:09.533 |空 |522 |a01 |2021-01-30 14:53:48.053 |2021-01-30 15:05:09.533 |空 |523 |a01 |2021-01-31 15:11:08.547 |2021-01-31 15:11:37.567 |2021-01-31 17:03:43.463 |523 |a01 |2021-01-31 15:11:08.547 |2021-01-31 15:11:37.567 |2021-01-31 17:03:43.463 |523 |a01 |2021-01-31 15:11:08.547 |2021-01-31 15:11:37.567 |2021-01-31 17:03:43.463 |523 |a01 |2021-01-31 15:11:08.547 |2021-01-31 15:11:37.567 |2021-01-31 17:03:43.463 |523 |a01 |2021-01-31 15:11:08.547 |2021-01-31 15:11:37.567 |2021-01-31 17:03:43.463 |523 |a01 |2021-01-31 15:11:08.547 |2021-01-31 15:11:37.567 |2021-01-31 17:03:43.463 |523 |a01 |2021-01-31 15:11:08.547 |2021-01-31 15:11:37.567 |2021-01-31 17:03:43.463 |523 |a01 |2021-01-31 15:11:08.547 |2021-01-31 15:11:37.567 |2021-01-31 17:03:43.463 |523 |a01 |2021-01-31 15:11:08.547 |2021-01-31 15:11:37.567 |2021-01-31 17:03:43.463 |523 |a01 |2021-01-31 15:11:08.547 |2021-01-31 15:11:37.567 |2021-01-31 17:03:43.463 |523 |a01 |2021-01-31 15:11:08.547 |2021-01-31 15:11:37.567 |2021-01-31 17:03:43.463 |523 |a01 |2021-01-31 15:11:08.547 |2021-01-31 15:11:37.567 |2021-01-31 17:03:43.463 |523 |a01 |2021-01-31 15:11:08.547 |2021-01-31 15:11:37.567 |2021-01-31 17:03:43.463 |524 |a01 |2021-01-31 15:11:40.247 |2021-01-31 15:13:40.217 |2021-01-31 17:03:43.463 |524 |a01 |2021-01-31 15:11:40.247 |2021-01-31 15:13:40.217 |2021-01-31 17:03:43.463 |524 |a01 |2021-01-31 15:11:40.247 |2021-01-31 15:13:40.217 |2021-01-31 17:03:43.463 |524 |a01 |2021-01-31 15:11:40.247 |2021-01-31 15:13:40.217 |2021-01-31 17:03:43.463 |524 |a01 |2021-01-31 15:11:40.247 |2021-01-31 15:13:40.217 |2021-01-31 17:03:43.463 |524 |a01 |2021-01-31 15:11:40.247 |2021-01-31 15:13:40.217 |2021-01-31 17:03:43.463 |524 |a01 |2021-01-31 15:11:40.247 |2021-01-31 15:13:40.217 |2021-01-31 17:03:43.463 |524 |a01 |2021-01-31 15:11:40.247 |2021-01-31 15:13:40.217 |2021-01-31 17:03:43.463 |524 |a01 |2021-01-31 15:11:40.247 |2021-01-31 15:13:40.217 |2021-01-31 17:03:43.463 |524 |a01 |2021-01-31 15:11:40.247 |2021-01-31 15:13:40.217 |2021-01-31 17:03:43.463 |524 |a01 |2021-01-31 15:11:40.247 |2021-01-31 15:13:40.217 |2021-01-31 17:03:43.463 |524 |a01 |2021-01-31 15:11:40.247 |2021-01-31 15:13:40.217 |2021-01-31 17:03:43.463 |524 |a01 |2021-01-31 15:11:40.247 |2021-01-31 15:13:40.217 |2021-01-31 17:03:43.463 |525 |a01 |2021-01-31 15:13:59.373 |2021-01-31 15:31:54.100 |2021-01-31 17:03:43.463 |525 |a01 |2021-01-31 15:13:59.373 |2021-01-31 15:31:54.100 |2021-01-31 17:03:43.463 |525 |a01 |2021-01-31 15:13:59.373 |2021-01-31 15:31:54.100 |2021-01-31 17:03:43.463 |525 |a01 |2021-01-31 15:13:59.373 |2021-01-31 15:31:54.100 |2021-01-31 17:03:43.463 |525 |a01 |2021-01-31 15:13:59.373 |2021-01-31 15:31:54.100 |2021-01-31 17:03:43.463 |525 |a01 |2021-01-31 15:13:59.373 |2021-01-31 15:31:54.100 |2021-01-31 17:03:43.463 |525 |a01 |2021-01-31 15:13:59.373 |2021-01-31 15:31:54.100 |2021-01-31 17:03:43.463 |525 |a01 |2021-01-31 15:13:59.373 |2021-01-31 15:31:54.100 |2021-01-31 17:03:43.463 |525 |a01 |2021-01-31 15:13:59.373 |2021-01-31 15:31:54.100 |2021-01-31 17:03:43.463 |525 |a01 |2021-01-31 15:13:59.373 |2021-01-31 15:31:54.100 |2021-01-31 17:03:43.463 |525 |a01 |2021-01-31 15:13:59.373 |2021-01-31 15:31:54.100 |2021-01-31 17:03:43.463 |525 |a01 |2021-01-31 15:13:59.373 |2021-01-31 15:31:54.100 |2021-01-31 17:03:43.463 |525 |a01 |2021-01-31 15:13:59.373 |2021-01-31 15:31:54.100 |2021-01-31 17:03:43.463 |526 |a01 |2021-01-31 15:32:23.203 |2021-01-31 15:46:33.993 |2021-01-31 17:03:43.463 |526 |a01 |2021-01-31 15:32:23.203 |2021-01-31 15:46:33.993 |2021-01-31 17:03:43.463 |526 |a01 |2021-01-31 15:32:23.203 |2021-01-31 15:46:33.993 |2021-01-31 17:03:43.463 |526 |a01 |2021-01-31 15:32:23.203 |2021-01-31 15:46:33.993 |2021-01-31 17:03:43.463 |526 |a01 |2021-01-31 15:32:23.203 |2021-01-31 15:46:33.993 |2021-01-31 17:03:43.463 |526 |a01 |2021-01-31 15:32:23.203 |2021-01-31 15:46:33.993 |2021-01-31 17:03:43.463 |526 |a01 |2021-01-31 15:32:23.203 |2021-01-31 15:46:33.993 |2021-01-31 17:03:43.463 |526 |a01 |2021-01-31 15:32:23.203 |2021-01-31 15:46:33.993 |2021-01-31 17:03:43.463 |526 |a01 |2021-01-31 15:32:23.203 |2021-01-31 15:46:33.993 |2021-01-31 17:03:43.463 |526 |a01 |2021-01-31 15:32:23.203 |2021-01-31 15:46:33.993 |2021-01-31 17:03:43.463 |526 |a01 |2021-01-31 15:32:23.203 |2021-01-31 15:46:33.993 |2021-01-31 17:03:43.463 |526 |a01 |2021-01-31 15:32:23.203 |2021-01-31 15:46:33.993 |2021-01-31 17:03:43.463 |526 |a01 |2021-01-31 15:32:23.203 |2021-01-31 15:46:33.993 |2021-01-31 17:03:43.463 |527 |a01 |2021-01-31 16:55:19.140 |2021-01-31 17:03:43.463 |空 |527 |a01 |2021-01-31 16:55:19.140 |2021-01-31 17:03:43.463 |空 |527 |a01 |2021-01-31 16:55:19.140 |2021-01-31 17:03:43.463 |空 |527 |a01 |2021-01-31 16:55:19.140 |2021-01-31 17:03:43.463 |空 |527 |a01 |2021-01-31 16:55:19.140 |2021-01-31 17:03:43.463 |空 |527 |a01 |2021-01-31 16:55:19.140 |2021-01-31 17:03:43.463 |空 |527 |a01 |2021-01-31 16:55:19.140 |2021-01-31 17:03:43.463 |空 |527 |a01 |2021-01-31 16:55:19.140 |2021-01-31 17:03:43.463 |空 |527 |a01 |2021-01-31 16:55:19.140 |2021-01-31 17:03:43.463 |空 |527 |a01 |2021-01-31 16:55:19.140 |2021-01-31 17:03:43.463 |空 |527 |a01 |2021-01-31 16:55:19.140 |2021-01-31 17:03:43.463 |空 |527 |a01 |2021-01-31 16:55:19.140 |2021-01-31 17:03:43.463 |空 |527 |a01 |2021-01-31 16:55:19.140 |2021-01-31 17:03:43.463 |空 |523 |a01 |2021-01-31 15:11:08.547 |2021-01-31 15:11:37.567 |2021-01-31 17:03:43.463 |723 |a01 |2021-01-31 15:11:08.547 |2021-01-31 15:11:37.567 |2021-01-31 17:03:43.463 |723 |a01 |2021-01-31 15:11:08.547 |2021-01-31 15:11:37.567 |2021-01-31 17:03:43.463 |723 |a01 |2021-01-31 15:11:08.547 |2021-01-31 15:11:37.567 |2021-01-31 17:03:43.463 |723 |a01 |2021-01-31 15:11:08.547 |2021-01-31 15:11:37.567 |2021-01-31 17:03:43.463 |723 |a01 |2021-01-31 15:11:08.547 |2021-01-31 15:11:37.567 |2021-01-31 17:03:43.463 |723 |a01 |2021-01-31 15:11:08.547 |2021-01-31 15:11:37.567 |2021-01-31 17:03:43.463 |723 |a01 |2021-01-31 15:11:08.547 |2021-01-31 15:11:37.567 |2021-01-31 17:03:43.463 |723 |a01 |2021-01-31 15:11:08.547 |2021-01-31 15:11:37.567 |2021-01-31 17:03:43.463 |723 |a01 |2021-01-31 15:11:08.547 |2021-01-31 15:11:37.567 |2021-01-31 17:03:43.463 |723 |a01 |2021-01-31 15:11:08.547 |2021-01-31 15:11:37.567 |2021-01-31 17:03:43.463 |723 |a01 |2021-01-31 15:11:08.547 |2021-01-31 15:11:37.567 |2021-01-31 17:03:43.463 |724 |a01 |2021-01-31 15:11:40.247 |2021-01-31 15:13:40.217 |2021-01-31 17:03:43.463 |724 |a01 |2021-01-31 15:11:40.247 |2021-01-31 15:13:40.217 |2021-01-31 17:03:43.463 |724 |a01 |2021-01-31 15:11:40.247 |2021-01-31 15:13:40.217 |2021-01-31 17:03:43.463 |724 |a01 |2021-01-31 15:11:40.247 |2021-01-31 15:13:40.217 |2021-01-31 17:03:43.463 |724 |a01 |2021-01-31 15:11:40.247 |2021-01-31 15:13:40.217 |2021-01-31 17:03:43.463 |724 |a01 |2021-01-31 15:11:40.247 |2021-01-31 15:13:40.217 |2021-01-31 17:03:43.463 |724 |a01 |2021-01-31 15:11:40.247 |2021-01-31 15:13:40.217 |2021-01-31 17:03:43.463 |724 |a01 |2021-01-31 15:11:40.247 |2021-01-31 15:13:40.217 |2021-01-31 17:03:43.463 |724 |a01 |2021-01-31 15:11:40.247 |2021-01-31 15:13:40.217 |2021-01-31 17:03:43.463 |724 |a01 |2021-01-31 15:11:40.247 |2021-01-31 15:13:40.217 |2021-01-31 17:03:43.463 |724 |a01 |2021-01-31 15:11:40.247 |2021-01-31 15:13:40.217 |2021-01-31 17:03:43.463 |724 |a01 |2021-01-31 15:11:40.247 |2021-01-31 15:13:40.217 |2021-01-31 17:03:43.463 |725 |a01 |2021-01-31 15:13:59.373 |2021-01-31 15:31:54.100 |2021-01-31 17:03:43.463 |725 |a01 |2021-01-31 15:13:59.373 |2021-01-31 15:31:54.100 |2021-01-31 17:03:43.463 |725 |a01 |2021-01-31 15:13:59.373 |2021-01-31 15:31:54.100 |2021-01-31 17:03:43.463 |725 |a01 |2021-01-31 15:13:59.373 |2021-01-31 15:31:54.100 |2021-01-31 17:03:43.463 |725 |a01 |2021-01-31 15:13:59.373 |2021-01-31 15:31:54.100 |2021-01-31 17:03:43.463 |725 |a01 |2021-01-31 15:13:59.373 |2021-01-31 15:31:54.100 |2021-01-31 17:03:43.463 |725 |a01 |2021-01-31 15:13:59.373 |2021-01-31 15:31:54.100 |2021-01-31 17:03:43.463 |725 |a01 |2021-01-31 15:13:59.373 |2021-01-31 15:31:54.100 |2021-01-31 17:03:43.463 |725 |a01 |2021-01-31 15:13:59.373 |2021-01-31 15:31:54.100 |2021-01-31 17:03:43.463 |725 |a01 |2021-01-31 15:13:59.373 |2021-01-31 15:31:54.100 |2021-01-31 17:03:43.463 |725 |a01 |2021-01-31 15:13:59.373 |2021-01-31 15:31:54.100 |2021-01-31 17:03:43.463 |725 |a01 |2021-01-31 15:13:59.373 |2021-01-31 15:31:54.100 |2021-01-31 17:03:43.463 |726 |a01 |2021-01-31 15:32:23.203 |2021-01-31 15:46:33.993 |2021-01-31 17:03:43.463 |726 |a01 |2021-01-31 15:32:23.203 |2021-01-31 15:46:33.993 |2021-01-31 17:03:43.463 |726 |a01 |2021-01-31 15:32:23.203 |2021-01-31 15:46:33.993 |2021-01-31 17:03:43.463 |726 |a01 |2021-01-31 15:32:23.203 |2021-01-31 15:46:33.993 |2021-01-31 17:03:43.463 |726 |a01 |2021-01-31 15:32:23.203 |2021-01-31 15:46:33.993 |2021-01-31 17:03:43.463 |726 |a01 |2021-01-31 15:32:23.203 |2021-01-31 15:46:33.993 |2021-01-31 17:03:43.463 |726 |a01 |2021-01-31 15:32:23.203 |2021-01-31 15:46:33.993 |2021-01-31 17:03:43.463 |726 |a01 |2021-01-31 15:32:23.203 |2021-01-31 15:46:33.993 |2021-01-31 17:03:43.463 |726 |a01 |2021-01-31 15:32:23.203 |2021-01-31 15:46:33.993 |2021-01-31 17:03:43.463 |726 |a01 |2021-01-31 15:32:23.203 |2021-01-31 15:46:33.993 |2021-01-31 17:03:43.463 |726 |a01 |2021-01-31 15:32:23.203 |2021-01-31 15:46:33.993 |2021-01-31 17:03:43.463 |726 |a01 |2021-01-31 15:32:23.203 |2021-01-31 15:46:33.993 |2021-01-31 17:03:43.463 |727 |a01 |2021-01-31 16:55:19.140 |2021-01-31 17:03:43.463 |空 |727 |a01 |2021-01-31 16:55:19.140 |2021-01-31 17:03:43.463 |空 |727 |a01 |2021-01-31 16:55:19.140 |2021-01-31 17:03:43.463 |空 |727 |a01 |2021-01-31 16:55:19.140 |2021-01-31 17:03:43.463 |空 |727 |a01 |2021-01-31 16:55:19.140 |2021-01-31 17:03:43.463 |空 |727 |a01 |2021-01-31 16:55:19.140 |2021-01-31 17:03:43.463 |空 |727 |a01 |2021-01-31 16:55:19.140 |2021-01-31 17:03:43.463 |空 |727 |a01 |2021-01-31 16:55:19.140 |2021-01-31 17:03:43.463 |空 |727 |a01 |2021-01-31 16:55:19.140 |2021-01-31 17:03:43.463 |空 |727 |a01 |2021-01-31 16:55:19.140 |2021-01-31 17:03:43.463 |空 |727 |a01 |2021-01-31 16:55:19.140 |2021-01-31 17:03:43.463 |空 |727 |a01 |2021-01-31 16:55:19.140 |2021-01-31 17:03:43.463 |空 |7
最终查询:在最终查询中，我们将从每个组中选择具有最小 playid 的第一行.这样，我们将只得到在一行的创建日期和下一行的停止日期之间有超过 120 mintuts 间隔的行.
<块引用> with cte as(从数据 d 中选择 d.playid,d.userid,d.created,d.stopped,dt.minstopped外申请(从数据 dt 中选择 max(stopped) minstoppedwhere d.playid<dt.playid and datediff(minute,d.created,dt.stopped)<=120and d.userid=dt.userid)dt),cte2 as(select top 1 *, 1 level from cte order by playid联合所有select cte.* ,level+2 level from cte inner join cte2 on cte.stopped>cte2.minstopped),cte3 as(select *,row_number()over(partition by level order by playid) rn from cte2)SELECT playid,userid,created,minstopped stopped FROM CTE3 WHERE RN=1走Final output:
<块引用>| playid | userid | created | stopped |
|---|---|---|---|
| 1 | a01 | 2021-01-30 02:29:58.560 | 2021-01-30 04:16:25.947 |
| 8 | a01 | 2021-01-30 04:26:28.970 | 2021-01-30 06:12:37.210 |
| 21 | a01 | 2021-01-30 14:41:01.530 | 2021-01-30 15:05:09.533 |
| 23 | a01 | 2021-01-31 15:11:08.547 | 2021-01-31 17:03:43.463 |
db<>fiddle here
I found this post on SO, but it solved with bigquery, I tried it with SQL query and I have problems using too many variables and loops. Despite the results, this query may not be optimal. Hope everyone help me
I have a table with playid, userid, created, and stopped. I want to merge certain rows by keeping the earliest created and the latest stopped within 120 minutes from the earliest created.
Desired Result:
| playid | userid | created | stopped |
|---|---|---|---|
| 1 | a01 | 2021-01-30 02:29:58.559858 | 2021-01-30 04:16:25.948311 |
| 8 | a01 | 2021-01-30 04:26:28.968568 | 2021-01-30 06:12:37.210234 |
| 21 | a01 | 2021-01-30 14:41:01.529666 | 2021-01-30 15:05:09.532786 |
| 23 | a01 | 2021-01-31 15:11:08.547111 | 2021-01-31 17:03:43.464444 |
Data:
| playid | userid | created | stopped |
|---|---|---|---|
| 1 | a01 | 2021-01-30 02:29:58.559858 | 2021-01-30 02:40:56.558436 |
| 2 | a01 | 2021-01-30 02:41:24.023358 | 2021-01-30 02:55:24.112713 |
| 3 | a01 | 2021-01-30 02:57:30.178579 | 2021-01-30 03:11:14.866678 |
| 4 | a01 | 2021-01-30 03:11:41.098424 | 2021-01-30 03:22:50.155918 |
| 5 | a01 | 2021-01-30 03:23:20.545288 | 2021-01-30 03:36:37.027486 |
| 6 | a01 | 2021-01-30 03:46:10.237971 | 2021-01-30 03:59:17.526151 |
| 7 | a01 | 2021-01-30 03:59:57.020326 | 2021-01-30 04:16:25.948311 |
| 8 | a01 | 2021-01-30 04:26:28.968568 | 2021-01-30 04:30:11.863079 |
| 9 | a01 | 2021-01-30 04:30:14.43786 | 2021-01-30 04:41:42.231915 |
| 10 | a01 | 2021-01-30 04:43:17.669945 | 2021-01-30 04:45:10.443101 |
| 11 | a01 | 2021-01-30 04:45:14.50346 | 2021-01-30 04:47:07.082114 |
| 12 | a01 | 2021-01-30 04:47:10.334574 | 2021-01-30 04:49:30.96017 |
| 13 | a01 | 2021-01-30 04:49:34.146011 | 2021-01-30 04:50:54.45988 |
| 14 | a01 | 2021-01-30 04:50:57.948305 | 2021-01-30 05:11:01.246284 |
| 15 | a01 | 2021-01-30 05:39:29.387396 | 2021-01-30 05:41:39.508654 |
| 16 | a01 | 2021-01-30 05:41:44.524951 | 2021-01-30 05:43:38.231266 |
| 17 | a01 | 2021-01-30 05:43:40.785809 | 2021-01-30 05:54:40.711381 |
| 18 | a01 | 2021-01-30 05:55:10.851725 | 2021-01-30 05:58:24.262351 |
| 19 | a01 | 2021-01-30 05:58:29.43821 | 2021-01-30 06:00:50.870644 |
| 20 | a01 | 2021-01-30 06:00:54.168696 | 2021-01-30 06:12:37.210234 |
| 21 | a01 | 2021-01-30 14:41:01.529666 | 2021-01-30 14:53:01.349479 |
| 22 | a01 | 2021-01-30 14:53:48.053136 | 2021-01-30 15:05:09.532786 |
| 23 | a01 | 2021-01-31 15:11:08.547111 | 2021-01-31 15:11:37.564802 |
| 24 | a01 | 2021-01-31 15:11:40.248499 | 2021-01-31 15:13:40.21787 |
| 25 | a01 | 2021-01-31 15:13:59.373145 | 2021-01-31 15:31:54.099898 |
| 26 | a01 | 2021-01-31 15:32:23.20448 | 2021-01-31 15:46:33.993751 |
| 27 | a01 | 2021-01-31 16:55:19.141051 | 2021-01-31 17:03:43.464444 |
Here is my query
DECLARE @FAKELIST TABLE(PLAYID INT, USERID VARCHAR(10), CREATED DATETIME, [STOPPED] DATETIME)
DECLARE @result TABLE(PLAYID INT, USERID VARCHAR(10), CREATED DATETIME, [STOPPED] DATETIME)
INSERT @FAKELIST(PLAYID, USERID, CREATED, [STOPPED]) SELECT * FROM MyTable
DECLARE @CREATED DATETIME
DECLARE @STOPPED1 DATETIME
DECLARE @STOPPED2 DATETIME
declare @playid int
declare @userid varchar(10)
while(exists (select top(1) * from @FAKELIST))
begin
set @CREATED = (select top(1) CREATED from @FAKELIST)
set @playid =(select top(1) PLAYID from @FAKELIST)
set @userid=(select top(1) USERID from @FAKELIST)
set @STOPPED1 = (select [STOPPED] from @FAKELIST order by PLAYID OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY)
set @STOPPED2 = (select [STOPPED] from @FAKELIST order by PLAYID OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY)
while(DATEDIFF(MINUTE,@CREATED,@STOPPED2) <120)
begin
delete from @FAKELIST where [STOPPED] =@STOPPED1
set @STOPPED1 = (select [STOPPED] from @FAKELIST order by PLAYID OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY)
set @STOPPED2 = (select [STOPPED] from @FAKELIST order by PLAYID OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY)
end
insert into @result(PLAYID,USERID,CREATED,[STOPPED]) values (@playid,@userid,@CREATED,@STOPPED1)
delete from @FAKELIST where PLAYID = @playid
delete from @FAKELIST where [STOPPED] = @STOPPED1
end
SELECT * FROM @result
解决方案
Here I will try to explain the query part by part (I am afraid that I might not a good explainer):
Schema and insert statement:
create table data (playid int, userid varchar(10), created datetime,stopped datetime);
insert into data values(1,'a01','01/30/2021 02:29:58.560','01/30/2021 02:40:56.558');
insert into data values(2,'a01','01/30/2021 02:41:24.023','01/30/2021 02:55:24.113');
insert into data values(3,'a01','01/30/2021 02:57:30.179','01/30/2021 03:11:14.867');
insert into data values(4,'a01','01/30/2021 03:11:41.098','01/30/2021 03:22:50.156');
insert into data values(5,'a01','01/30/2021 03:23:20.545','01/30/2021 03:36:37.027');
insert into data values(6,'a01','01/30/2021 03:46:10.238','01/30/2021 03:59:17.526');
insert into data values(7,'a01','01/30/2021 03:59:57.020','01/30/2021 04:16:25.948');
insert into data values(8,'a01','01/30/2021 04:26:28.969','01/30/2021 04:30:11.863');
insert into data values(9,'a01','01/30/2021 04:30:14.438','01/30/2021 04:41:42.232');
insert into data values(10,'a01','01/30/2021 04:43:17.670','01/30/2021 04:45:10.443');
insert into data values(11,'a01','01/30/2021 04:45:14.503','01/30/2021 04:47:07.082');
insert into data values(12,'a01','01/30/2021 04:47:10.335','01/30/2021 04:49:30.960');
insert into data values(13,'a01','01/30/2021 04:49:34.146','01/30/2021 04:50:54.460');
insert into data values(14,'a01','01/30/2021 04:50:57.948','01/30/2021 05:11:01.246');
insert into data values(15,'a01','01/30/2021 05:39:29.387','01/30/2021 05:41:39.509');
insert into data values(16,'a01','01/30/2021 05:41:44.525','01/30/2021 05:43:38.231');
insert into data values(17,'a01','01/30/2021 05:43:40.786','01/30/2021 05:54:40.711');
insert into data values(18,'a01','01/30/2021 05:55:10.852','01/30/2021 05:58:24.262');
insert into data values(19,'a01','01/30/2021 05:58:29.438','01/30/2021 06:00:50.871');
insert into data values(20,'a01','01/30/2021 06:00:54.169','01/30/2021 06:12:37.210');
insert into data values(21,'a01','01/30/2021 14:41:01.530','01/30/2021 14:53:01.349');
insert into data values(22,'a01','01/30/2021 14:53:48.053','01/30/2021 15:05:09.533');
insert into data values(23,'a01','01/31/2021 15:11:08.547','01/31/2021 15:11:37.565');
insert into data values(24,'a01','01/31/2021 15:11:40.248','01/31/2021 15:13:40.218');
insert into data values(25,'a01','01/31/2021 15:13:59.373','01/31/2021 15:31:54.100');
insert into data values(26,'a01','01/31/2021 15:32:23.204','01/31/2021 15:46:33.994');
insert into data values(27,'a01','01/31/2021 16:55:19.141','01/31/2021 17:03:43.464');
First Part of Query:
select d.playid,d.userid,d.created,d.stopped,dt.minstopped from data d
outer apply (
select max(stopped) minstopped from data dt
where d.playid<dt.playid and datediff(minute,d.created,dt.stopped)<=120
and d.userid=dt.userid)dt
In above query I have added a new column named minstopped which will calculate the maximum stopped date time. The logic behind is when we are calculating minstopped for first row we will calculate max(sopped) from all the rows having stopped date no greater than created date from first row plus 120 minutes.
Output:
| playid | userid | created | stopped | minstopped |
|---|---|---|---|---|
| 1 | a01 | 2021-01-30 02:29:58.560 | 2021-01-30 02:40:56.557 | 2021-01-30 04:16:25.947 |
| 2 | a01 | 2021-01-30 02:41:24.023 | 2021-01-30 02:55:24.113 | 2021-01-30 04:41:42.233 |
| 3 | a01 | 2021-01-30 02:57:30.180 | 2021-01-30 03:11:14.867 | 2021-01-30 04:50:54.460 |
| 4 | a01 | 2021-01-30 03:11:41.097 | 2021-01-30 03:22:50.157 | 2021-01-30 05:11:01.247 |
| 5 | a01 | 2021-01-30 03:23:20.547 | 2021-01-30 03:36:37.027 | 2021-01-30 05:11:01.247 |
| 6 | a01 | 2021-01-30 03:46:10.237 | 2021-01-30 03:59:17.527 | 2021-01-30 05:43:38.230 |
| 7 | a01 | 2021-01-30 03:59:57.020 | 2021-01-30 04:16:25.947 | 2021-01-30 05:58:24.263 |
| 8 | a01 | 2021-01-30 04:26:28.970 | 2021-01-30 04:30:11.863 | 2021-01-30 06:12:37.210 |
| 9 | a01 | 2021-01-30 04:30:14.437 | 2021-01-30 04:41:42.233 | 2021-01-30 06:12:37.210 |
| 10 | a01 | 2021-01-30 04:43:17.670 | 2021-01-30 04:45:10.443 | 2021-01-30 06:12:37.210 |
| 11 | a01 | 2021-01-30 04:45:14.503 | 2021-01-30 04:47:07.083 | 2021-01-30 06:12:37.210 |
| 12 | a01 | 2021-01-30 04:47:10.337 | 2021-01-30 04:49:30.960 | 2021-01-30 06:12:37.210 |
| 13 | a01 | 2021-01-30 04:49:34.147 | 2021-01-30 04:50:54.460 | 2021-01-30 06:12:37.210 |
| 14 | a01 | 2021-01-30 04:50:57.947 | 2021-01-30 05:11:01.247 | 2021-01-30 06:12:37.210 |
| 15 | a01 | 2021-01-30 05:39:29.387 | 2021-01-30 05:41:39.510 | 2021-01-30 06:12:37.210 |
| 16 | a01 | 2021-01-30 05:41:44.527 | 2021-01-30 05:43:38.230 | 2021-01-30 06:12:37.210 |
| 17 | a01 | 2021-01-30 05:43:40.787 | 2021-01-30 05:54:40.710 | 2021-01-30 06:12:37.210 |
| 18 | a01 | 2021-01-30 05:55:10.853 | 2021-01-30 05:58:24.263 | 2021-01-30 06:12:37.210 |
| 19 | a01 | 2021-01-30 05:58:29.437 | 2021-01-30 06:00:50.870 | 2021-01-30 06:12:37.210 |
| 20 | a01 | 2021-01-30 06:00:54.170 | 2021-01-30 06:12:37.210 | null |
| 21 | a01 | 2021-01-30 14:41:01.530 | 2021-01-30 14:53:01.350 | 2021-01-30 15:05:09.533 |
| 22 | a01 | 2021-01-30 14:53:48.053 | 2021-01-30 15:05:09.533 | null |
| 23 | a01 | 2021-01-31 15:11:08.547 | 2021-01-31 15:11:37.567 | 2021-01-31 17:03:43.463 |
| 24 | a01 | 2021-01-31 15:11:40.247 | 2021-01-31 15:13:40.217 | 2021-01-31 17:03:43.463 |
| 25 | a01 | 2021-01-31 15:13:59.373 | 2021-01-31 15:31:54.100 | 2021-01-31 17:03:43.463 |
| 26 | a01 | 2021-01-31 15:32:23.203 | 2021-01-31 15:46:33.993 | 2021-01-31 17:03:43.463 |
| 27 | a01 | 2021-01-31 16:55:19.140 | 2021-01-31 17:03:43.463 | null |
Second part to make a group with all the rows where stopped date is more than 120 minutes from the created date of first row from previous group. So we now have a group of rows for every 120 minutes gap between created date and stopped date.
with cte as
(
select d.playid,d.userid,d.created,d.stopped,dt.minstopped from data d
outer apply (
select max(stopped) minstopped from data dt
where d.playid<dt.playid and datediff(minute,d.created,dt.stopped)<=120
and d.userid=dt.userid)dt
),
cte2 as
(
select top 1 *, 1 level from cte order by playid
union all
select cte.* ,level+2 level from cte inner join cte2 on cte.stopped>cte2.minstopped
)
select * from cte2
order by level, playid
playid | userid | created | stopped | minstopped | level -----: | :----- | :---------------------- | :---------------------- | :---------------------- | ----: 1 | a01 | 2021-01-30 02:29:58.560 | 2021-01-30 02:40:56.557 | 2021-01-30 04:16:25.947 | 1 8 | a01 | 2021-01-30 04:26:28.970 | 2021-01-30 04:30:11.863 | 2021-01-30 06:12:37.210 | 3 9 | a01 | 2021-01-30 04:30:14.437 | 2021-01-30 04:41:42.233 | 2021-01-30 06:12:37.210 | 3 10 | a01 | 2021-01-30 04:43:17.670 | 2021-01-30 04:45:10.443 | 2021-01-30 06:12:37.210 | 3 11 | a01 | 2021-01-30 04:45:14.503 | 2021-01-30 04:47:07.083 | 2021-01-30 06:12:37.210 | 3 12 | a01 | 2021-01-30 04:47:10.337 | 2021-01-30 04:49:30.960 | 2021-01-30 06:12:37.210 | 3 13 | a01 | 2021-01-30 04:49:34.147 | 2021-01-30 04:50:54.460 | 2021-01-30 06:12:37.210 | 3 14 | a01 | 2021-01-30 04:50:57.947 | 2021-01-30 05:11:01.247 | 2021-01-30 06:12:37.210 | 3 15 | a01 | 2021-01-30 05:39:29.387 | 2021-01-30 05:41:39.510 | 2021-01-30 06:12:37.210 | 3 16 | a01 | 2021-01-30 05:41:44.527 | 2021-01-30 05:43:38.230 | 2021-01-30 06:12:37.210 | 3 17 | a01 | 2021-01-30 05:43:40.787 | 2021-01-30 05:54:40.710 | 2021-01-30 06:12:37.210 | 3 18 | a01 | 2021-01-30 05:55:10.853 | 2021-01-30 05:58:24.263 | 2021-01-30 06:12:37.210 | 3 19 | a01 | 2021-01-30 05:58:29.437 | 2021-01-30 06:00:50.870 | 2021-01-30 06:12:37.210 | 3 20 | a01 | 2021-01-30 06:00:54.170 | 2021-01-30 06:12:37.210 | null | 3 21 | a01 | 2021-01-30 14:41:01.530 | 2021-01-30 14:53:01.350 | 2021-01-30 15:05:09.533 | 3 22 | a01 | 2021-01-30 14:53:48.053 | 2021-01-30 15:05:09.533 | null | 3 23 | a01 | 2021-01-31 15:11:08.547 | 2021-01-31 15:11:37.567 | 2021-01-31 17:03:43.463 | 3 24 | a01 | 2021-01-31 15:11:40.247 | 2021-01-31 15:13:40.217 | 2021-01-31 17:03:43.463 | 3 25 | a01 | 2021-01-31 15:13:59.373 | 2021-01-31 15:31:54.100 | 2021-01-31 17:03:43.463 | 3 26 | a01 | 2021-01-31 15:32:23.203 | 2021-01-31 15:46:33.993 | 2021-01-31 17:03:43.463 | 3 27 | a01 | 2021-01-31 16:55:19.140 | 2021-01-31 17:03:43.463 | null | 3 21 | a01 | 2021-01-30 14:41:01.530 | 2021-01-30 14:53:01.350 | 2021-01-30 15:05:09.533 | 5 21 | a01 | 2021-01-30 14:41:01.530 | 2021-01-30 14:53:01.350 | 2021-01-30 15:05:09.533 | 5 21 | a01 | 2021-01-30 14:41:01.530 | 2021-01-30 14:53:01.350 | 2021-01-30 15:05:09.533 | 5 21 | a01 | 2021-01-30 14:41:01.530 | 2021-01-30 14:53:01.350 | 2021-01-30 15:05:09.533 | 5 21 | a01 | 2021-01-30 14:41:01.530 | 2021-01-30 14:53:01.350 | 2021-01-30 15:05:09.533 | 5 21 | a01 | 2021-01-30 14:41:01.530 | 2021-01-30 14:53:01.350 | 2021-01-30 15:05:09.533 | 5 21 | a01 | 2021-01-30 14:41:01.530 | 2021-01-30 14:53:01.350 | 2021-01-30 15:05:09.533 | 5 21 | a01 | 2021-01-30 14:41:01.530 | 2021-01-30 14:53:01.350 | 2021-01-30 15:05:09.533 | 5 21 | a01 | 2021-01-30 14:41:01.530 | 2021-01-30 14:53:01.350 | 2021-01-30 15:05:09.533 | 5 21 | a01 | 2021-01-30 14:41:01.530 | 2021-01-30 14:53:01.350 | 2021-01-30 15:05:09.533 | 5 21 | a01 | 2021-01-30 14:41:01.530 | 2021-01-30 14:53:01.350 | 2021-01-30 15:05:09.533 | 5 21 | a01 | 2021-01-30 14:41:01.530 | 2021-01-30 14:53:01.350 | 2021-01-30 15:05:09.533 | 5 22 | a01 | 2021-01-30 14:53:48.053 | 2021-01-30 15:05:09.533 | null | 5 22 | a01 | 2021-01-30 14:53:48.053 | 2021-01-30 15:05:09.533 | null | 5 22 | a01 | 2021-01-30 14:53:48.053 | 2021-01-30 15:05:09.533 | null | 5 22 | a01 | 2021-01-30 14:53:48.053 | 2021-01-30 15:05:09.533 | null | 5 22 | a01 | 2021-01-30 14:53:48.053 | 2021-01-30 15:05:09.533 | null | 5 22 | a01 | 2021-01-30 14:53:48.053 | 2021-01-30 15:05:09.533 | null | 5 22 | a01 | 2021-01-30 14:53:48.053 | 2021-01-30 15:05:09.533 | null | 5 22 | a01 | 2021-01-30 14:53:48.053 | 2021-01-30 15:05:09.533 | null | 5 22 | a01 | 2021-01-30 14:53:48.053 | 2021-01-30 15:05:09.533 | null | 5 22 | a01 | 2021-01-30 14:53:48.053 | 2021-01-30 15:05:09.533 | null | 5 22 | a01 | 2021-01-30 14:53:48.053 | 2021-01-30 15:05:09.533 | null | 5 22 | a01 | 2021-01-30 14:53:48.053 | 2021-01-30 15:05:09.533 | null | 5 23 | a01 | 2021-01-31 15:11:08.547 | 2021-01-31 15:11:37.567 | 2021-01-31 17:03:43.463 | 5 23 | a01 | 2021-01-31 15:11:08.547 | 2021-01-31 15:11:37.567 | 2021-01-31 17:03:43.463 | 5 23 | a01 | 2021-01-31 15:11:08.547 | 2021-01-31 15:11:37.567 | 2021-01-31 17:03:43.463 | 5 23 | a01 | 2021-01-31 15:11:08.547 | 2021-01-31 15:11:37.567 | 2021-01-31 17:03:43.463 | 5 23 | a01 | 2021-01-31 15:11:08.547 | 2021-01-31 15:11:37.567 | 2021-01-31 17:03:43.463 | 5 23 | a01 | 2021-01-31 15:11:08.547 | 2021-01-31 15:11:37.567 | 2021-01-31 17:03:43.463 | 5 23 | a01 | 2021-01-31 15:11:08.547 | 2021-01-31 15:11:37.567 | 2021-01-31 17:03:43.463 | 5 23 | a01 | 2021-01-31 15:11:08.547 | 2021-01-31 15:11:37.567 | 2021-01-31 17:03:43.463 | 5 23 | a01 | 2021-01-31 15:11:08.547 | 2021-01-31 15:11:37.567 | 2021-01-31 17:03:43.463 | 5 23 | a01 | 2021-01-31 15:11:08.547 | 2021-01-31 15:11:37.567 | 2021-01-31 17:03:43.463 | 5 23 | a01 | 2021-01-31 15:11:08.547 | 2021-01-31 15:11:37.567 | 2021-01-31 17:03:43.463 | 5 23 | a01 | 2021-01-31 15:11:08.547 | 2021-01-31 15:11:37.567 | 2021-01-31 17:03:43.463 | 5 23 | a01 | 2021-01-31 15:11:08.547 | 2021-01-31 15:11:37.567 | 2021-01-31 17:03:43.463 | 5 24 | a01 | 2021-01-31 15:11:40.247 | 2021-01-31 15:13:40.217 | 2021-01-31 17:03:43.463 | 5 24 | a01 | 2021-01-31 15:11:40.247 | 2021-01-31 15:13:40.217 | 2021-01-31 17:03:43.463 | 5 24 | a01 | 2021-01-31 15:11:40.247 | 2021-01-31 15:13:40.217 | 2021-01-31 17:03:43.463 | 5 24 | a01 | 2021-01-31 15:11:40.247 | 2021-01-31 15:13:40.217 | 2021-01-31 17:03:43.463 | 5 24 | a01 | 2021-01-31 15:11:40.247 | 2021-01-31 15:13:40.217 | 2021-01-31 17:03:43.463 | 5 24 | a01 | 2021-01-31 15:11:40.247 | 2021-01-31 15:13:40.217 | 2021-01-31 17:03:43.463 | 5 24 | a01 | 2021-01-31 15:11:40.247 | 2021-01-31 15:13:40.217 | 2021-01-31 17:03:43.463 | 5 24 | a01 | 2021-01-31 15:11:40.247 | 2021-01-31 15:13:40.217 | 2021-01-31 17:03:43.463 | 5 24 | a01 | 2021-01-31 15:11:40.247 | 2021-01-31 15:13:40.217 | 2021-01-31 17:03:43.463 | 5 24 | a01 | 2021-01-31 15:11:40.247 | 2021-01-31 15:13:40.217 | 2021-01-31 17:03:43.463 | 5 24 | a01 | 2021-01-31 15:11:40.247 | 2021-01-31 15:13:40.217 | 2021-01-31 17:03:43.463 | 5 24 | a01 | 2021-01-31 15:11:40.247 | 2021-01-31 15:13:40.217 | 2021-01-31 17:03:43.463 | 5 24 | a01 | 2021-01-31 15:11:40.247 | 2021-01-31 15:13:40.217 | 2021-01-31 17:03:43.463 | 5 25 | a01 | 2021-01-31 15:13:59.373 | 2021-01-31 15:31:54.100 | 2021-01-31 17:03:43.463 | 5 25 | a01 | 2021-01-31 15:13:59.373 | 2021-01-31 15:31:54.100 | 2021-01-31 17:03:43.463 | 5 25 | a01 | 2021-01-31 15:13:59.373 | 2021-01-31 15:31:54.100 | 2021-01-31 17:03:43.463 | 5 25 | a01 | 2021-01-31 15:13:59.373 | 2021-01-31 15:31:54.100 | 2021-01-31 17:03:43.463 | 5 25 | a01 | 2021-01-31 15:13:59.373 | 2021-01-31 15:31:54.100 | 2021-01-31 17:03:43.463 | 5 25 | a01 | 2021-01-31 15:13:59.373 | 2021-01-31 15:31:54.100 | 2021-01-31 17:03:43.463 | 5 25 | a01 | 2021-01-31 15:13:59.373 | 2021-01-31 15:31:54.100 | 2021-01-31 17:03:43.463 | 5 25 | a01 | 2021-01-31 15:13:59.373 | 2021-01-31 15:31:54.100 | 2021-01-31 17:03:43.463 | 5 25 | a01 | 2021-01-31 15:13:59.373 | 2021-01-31 15:31:54.100 | 2021-01-31 17:03:43.463 | 5 25 | a01 | 2021-01-31 15:13:59.373 | 2021-01-31 15:31:54.100 | 2021-01-31 17:03:43.463 | 5 25 | a01 | 2021-01-31 15:13:59.373 | 2021-01-31 15:31:54.100 | 2021-01-31 17:03:43.463 | 5 25 | a01 | 2021-01-31 15:13:59.373 | 2021-01-31 15:31:54.100 | 2021-01-31 17:03:43.463 | 5 25 | a01 | 2021-01-31 15:13:59.373 | 2021-01-31 15:31:54.100 | 2021-01-31 17:03:43.463 | 5 26 | a01 | 2021-01-31 15:32:23.203 | 2021-01-31 15:46:33.993 | 2021-01-31 17:03:43.463 | 5 26 | a01 | 2021-01-31 15:32:23.203 | 2021-01-31 15:46:33.993 | 2021-01-31 17:03:43.463 | 5 26 | a01 | 2021-01-31 15:32:23.203 | 2021-01-31 15:46:33.993 | 2021-01-31 17:03:43.463 | 5 26 | a01 | 2021-01-31 15:32:23.203 | 2021-01-31 15:46:33.993 | 2021-01-31 17:03:43.463 | 5 26 | a01 | 2021-01-31 15:32:23.203 | 2021-01-31 15:46:33.993 | 2021-01-31 17:03:43.463 | 5 26 | a01 | 2021-01-31 15:32:23.203 | 2021-01-31 15:46:33.993 | 2021-01-31 17:03:43.463 | 5 26 | a01 | 2021-01-31 15:32:23.203 | 2021-01-31 15:46:33.993 | 2021-01-31 17:03:43.463 | 5 26 | a01 | 2021-01-31 15:32:23.203 | 2021-01-31 15:46:33.993 | 2021-01-31 17:03:43.463 | 5 26 | a01 | 2021-01-31 15:32:23.203 | 2021-01-31 15:46:33.993 | 2021-01-31 17:03:43.463 | 5 26 | a01 | 2021-01-31 15:32:23.203 | 2021-01-31 15:46:33.993 | 2021-01-31 17:03:43.463 | 5 26 | a01 | 2021-01-31 15:32:23.203 | 2021-01-31 15:46:33.993 | 2021-01-31 17:03:43.463 | 5 26 | a01 | 2021-01-31 15:32:23.203 | 2021-01-31 15:46:33.993 | 2021-01-31 17:03:43.463 | 5 26 | a01 | 2021-01-31 15:32:23.203 | 2021-01-31 15:46:33.993 | 2021-01-31 17:03:43.463 | 5 27 | a01 | 2021-01-31 16:55:19.140 | 2021-01-31 17:03:43.463 | null | 5 27 | a01 | 2021-01-31 16:55:19.140 | 2021-01-31 17:03:43.463 | null | 5 27 | a01 | 2021-01-31 16:55:19.140 | 2021-01-31 17:03:43.463 | null | 5 27 | a01 | 2021-01-31 16:55:19.140 | 2021-01-31 17:03:43.463 | null | 5 27 | a01 | 2021-01-31 16:55:19.140 | 2021-01-31 17:03:43.463 | null | 5 27 | a01 | 2021-01-31 16:55:19.140 | 2021-01-31 17:03:43.463 | null | 5 27 | a01 | 2021-01-31 16:55:19.140 | 2021-01-31 17:03:43.463 | null | 5 27 | a01 | 2021-01-31 16:55:19.140 | 2021-01-31 17:03:43.463 | null | 5 27 | a01 | 2021-01-31 16:55:19.140 | 2021-01-31 17:03:43.463 | null | 5 27 | a01 | 2021-01-31 16:55:19.140 | 2021-01-31 17:03:43.463 | null | 5 27 | a01 | 2021-01-31 16:55:19.140 | 2021-01-31 17:03:43.463 | null | 5 27 | a01 | 2021-01-31 16:55:19.140 | 2021-01-31 17:03:43.463 | null | 5 27 | a01 | 2021-01-31 16:55:19.140 | 2021-01-31 17:03:43.463 | null | 5 23 | a01 | 2021-01-31 15:11:08.547 | 2021-01-31 15:11:37.567 | 2021-01-31 17:03:43.463 | 7 23 | a01 | 2021-01-31 15:11:08.547 | 2021-01-31 15:11:37.567 | 2021-01-31 17:03:43.463 | 7 23 | a01 | 2021-01-31 15:11:08.547 | 2021-01-31 15:11:37.567 | 2021-01-31 17:03:43.463 | 7 23 | a01 | 2021-01-31 15:11:08.547 | 2021-01-31 15:11:37.567 | 2021-01-31 17:03:43.463 | 7 23 | a01 | 2021-01-31 15:11:08.547 | 2021-01-31 15:11:37.567 | 2021-01-31 17:03:43.463 | 7 23 | a01 | 2021-01-31 15:11:08.547 | 2021-01-31 15:11:37.567 | 2021-01-31 17:03:43.463 | 7 23 | a01 | 2021-01-31 15:11:08.547 | 2021-01-31 15:11:37.567 | 2021-01-31 17:03:43.463 | 7 23 | a01 | 2021-01-31 15:11:08.547 | 2021-01-31 15:11:37.567 | 2021-01-31 17:03:43.463 | 7 23 | a01 | 2021-01-31 15:11:08.547 | 2021-01-31 15:11:37.567 | 2021-01-31 17:03:43.463 | 7 23 | a01 | 2021-01-31 15:11:08.547 | 2021-01-31 15:11:37.567 | 2021-01-31 17:03:43.463 | 7 23 | a01 | 2021-01-31 15:11:08.547 | 2021-01-31 15:11:37.567 | 2021-01-31 17:03:43.463 | 7 23 | a01 | 2021-01-31 15:11:08.547 | 2021-01-31 15:11:37.567 | 2021-01-31 17:03:43.463 | 7 24 | a01 | 2021-01-31 15:11:40.247 | 2021-01-31 15:13:40.217 | 2021-01-31 17:03:43.463 | 7 24 | a01 | 2021-01-31 15:11:40.247 | 2021-01-31 15:13:40.217 | 2021-01-31 17:03:43.463 | 7 24 | a01 | 2021-01-31 15:11:40.247 | 2021-01-31 15:13:40.217 | 2021-01-31 17:03:43.463 | 7 24 | a01 | 2021-01-31 15:11:40.247 | 2021-01-31 15:13:40.217 | 2021-01-31 17:03:43.463 | 7 24 | a01 | 2021-01-31 15:11:40.247 | 2021-01-31 15:13:40.217 | 2021-01-31 17:03:43.463 | 7 24 | a01 | 2021-01-31 15:11:40.247 | 2021-01-31 15:13:40.217 | 2021-01-31 17:03:43.463 | 7 24 | a01 | 2021-01-31 15:11:40.247 | 2021-01-31 15:13:40.217 | 2021-01-31 17:03:43.463 | 7 24 | a01 | 2021-01-31 15:11:40.247 | 2021-01-31 15:13:40.217 | 2021-01-31 17:03:43.463 | 7 24 | a01 | 2021-01-31 15:11:40.247 | 2021-01-31 15:13:40.217 | 2021-01-31 17:03:43.463 | 7 24 | a01 | 2021-01-31 15:11:40.247 | 2021-01-31 15:13:40.217 | 2021-01-31 17:03:43.463 | 7 24 | a01 | 2021-01-31 15:11:40.247 | 2021-01-31 15:13:40.217 | 2021-01-31 17:03:43.463 | 7 24 | a01 | 2021-01-31 15:11:40.247 | 2021-01-31 15:13:40.217 | 2021-01-31 17:03:43.463 | 7 25 | a01 | 2021-01-31 15:13:59.373 | 2021-01-31 15:31:54.100 | 2021-01-31 17:03:43.463 | 7 25 | a01 | 2021-01-31 15:13:59.373 | 2021-01-31 15:31:54.100 | 2021-01-31 17:03:43.463 | 7 25 | a01 | 2021-01-31 15:13:59.373 | 2021-01-31 15:31:54.100 | 2021-01-31 17:03:43.463 | 7 25 | a01 | 2021-01-31 15:13:59.373 | 2021-01-31 15:31:54.100 | 2021-01-31 17:03:43.463 | 7 25 | a01 | 2021-01-31 15:13:59.373 | 2021-01-31 15:31:54.100 | 2021-01-31 17:03:43.463 | 7 25 | a01 | 2021-01-31 15:13:59.373 | 2021-01-31 15:31:54.100 | 2021-01-31 17:03:43.463 | 7 25 | a01 | 2021-01-31 15:13:59.373 | 2021-01-31 15:31:54.100 | 2021-01-31 17:03:43.463 | 7 25 | a01 | 2021-01-31 15:13:59.373 | 2021-01-31 15:31:54.100 | 2021-01-31 17:03:43.463 | 7 25 | a01 | 2021-01-31 15:13:59.373 | 2021-01-31 15:31:54.100 | 2021-01-31 17:03:43.463 | 7 25 | a01 | 2021-01-31 15:13:59.373 | 2021-01-31 15:31:54.100 | 2021-01-31 17:03:43.463 | 7 25 | a01 | 2021-01-31 15:13:59.373 | 2021-01-31 15:31:54.100 | 2021-01-31 17:03:43.463 | 7 25 | a01 | 2021-01-31 15:13:59.373 | 2021-01-31 15:31:54.100 | 2021-01-31 17:03:43.463 | 7 26 | a01 | 2021-01-31 15:32:23.203 | 2021-01-31 15:46:33.993 | 2021-01-31 17:03:43.463 | 7 26 | a01 | 2021-01-31 15:32:23.203 | 2021-01-31 15:46:33.993 | 2021-01-31 17:03:43.463 | 7 26 | a01 | 2021-01-31 15:32:23.203 | 2021-01-31 15:46:33.993 | 2021-01-31 17:03:43.463 | 7 26 | a01 | 2021-01-31 15:32:23.203 | 2021-01-31 15:46:33.993 | 2021-01-31 17:03:43.463 | 7 26 | a01 | 2021-01-31 15:32:23.203 | 2021-01-31 15:46:33.993 | 2021-01-31 17:03:43.463 | 7 26 | a01 | 2021-01-31 15:32:23.203 | 2021-01-31 15:46:33.993 | 2021-01-31 17:03:43.463 | 7 26 | a01 | 2021-01-31 15:32:23.203 | 2021-01-31 15:46:33.993 | 2021-01-31 17:03:43.463 | 7 26 | a01 | 2021-01-31 15:32:23.203 | 2021-01-31 15:46:33.993 | 2021-01-31 17:03:43.463 | 7 26 | a01 | 2021-01-31 15:32:23.203 | 2021-01-31 15:46:33.993 | 2021-01-31 17:03:43.463 | 7 26 | a01 | 2021-01-31 15:32:23.203 | 2021-01-31 15:46:33.993 | 2021-01-31 17:03:43.463 | 7 26 | a01 | 2021-01-31 15:32:23.203 | 2021-01-31 15:46:33.993 | 2021-01-31 17:03:43.463 | 7 26 | a01 | 2021-01-31 15:32:23.203 | 2021-01-31 15:46:33.993 | 2021-01-31 17:03:43.463 | 7 27 | a01 | 2021-01-31 16:55:19.140 | 2021-01-31 17:03:43.463 | null | 7 27 | a01 | 2021-01-31 16:55:19.140 | 2021-01-31 17:03:43.463 | null | 7 27 | a01 | 2021-01-31 16:55:19.140 | 2021-01-31 17:03:43.463 | null | 7 27 | a01 | 2021-01-31 16:55:19.140 | 2021-01-31 17:03:43.463 | null | 7 27 | a01 | 2021-01-31 16:55:19.140 | 2021-01-31 17:03:43.463 | null | 7 27 | a01 | 2021-01-31 16:55:19.140 | 2021-01-31 17:03:43.463 | null | 7 27 | a01 | 2021-01-31 16:55:19.140 | 2021-01-31 17:03:43.463 | null | 7 27 | a01 | 2021-01-31 16:55:19.140 | 2021-01-31 17:03:43.463 | null | 7 27 | a01 | 2021-01-31 16:55:19.140 | 2021-01-31 17:03:43.463 | null | 7 27 | a01 | 2021-01-31 16:55:19.140 | 2021-01-31 17:03:43.463 | null | 7 27 | a01 | 2021-01-31 16:55:19.140 | 2021-01-31 17:03:43.463 | null | 7 27 | a01 | 2021-01-31 16:55:19.140 | 2021-01-31 17:03:43.463 | null | 7
Final Query: In final query we will chose first row with the minimum playid from each group. In that way we will get only rows having more than 120 mintuts gap between created date of a row and stopped date of next row.
with cte as ( select d.playid,d.userid,d.created,d.stopped,dt.minstopped from data d outer apply ( select max(stopped) minstopped from data dt where d.playid<dt.playid and datediff(minute,d.created,dt.stopped)<=120 and d.userid=dt.userid)dt ), cte2 as ( select top 1 *, 1 level from cte order by playid union all select cte.* ,level+2 level from cte inner join cte2 on cte.stopped>cte2.minstopped ), cte3 as ( select *,row_number()over(partition by level order by playid) rn from cte2 ) SELECT playid,userid,created,minstopped stopped FROM CTE3 WHERE RN=1 GO
Final output:
playid userid created stopped 1 a01 2021-01-30 02:29:58.560 2021-01-30 04:16:25.947 8 a01 2021-01-30 04:26:28.970 2021-01-30 06:12:37.210 21 a01 2021-01-30 14:41:01.530 2021-01-30 15:05:09.533 23 a01 2021-01-31 15:11:08.547 2021-01-31 17:03:43.463
db<>fiddle here
相关文章