根据日期条件合并不同的行 [英] Merge different rows according to date conditions

查看：39
本文介绍了根据日期条件合并不同的行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我在 SO 上找到了这篇文章，但它用 bigquery 解决了，我用 SQL 查询尝试过，但我在使用太多变量和循环时遇到了问题.尽管结果如此，但此查询可能不是最佳的.希望大家帮帮我

我有一个包含 playid、userid、创建和停止的表.我想通过保留最早创建的行并在最早创建的 120 分钟内停止最新行来合并某些行.

预期结果:

<头>
playid用户名创建停止
1a012021-01-30 02:29:58.5598582021-01-30 04:16:25.948311
8a012021-01-30 04:26:28.9685682021-01-30 06:12:37.210234
21a012021-01-30 14:41:01.5296662021-01-30 15:05:09.532786
23a012021-01-31 15:11:08.5471112021-01-31 17:03:43.464444

数据:

<头>
playid用户名创建停止
1a012021-01-30 02:29:58.5598582021-01-30 02:40:56.558436
2a012021-01-30 02:41:24.0233582021-01-30 02:55:24.112713
3a012021-01-30 02:57:30.1785792021-01-30 03:11:14.866678
4a012021-01-30 03:11:41.0984242021-01-30 03:22:50.155918
5a012021-01-30 03:23:20.5452882021-01-30 03:36:37.027486
6a012021-01-30 03:46:10.2379712021-01-30 03:59:17.526151
7a012021-01-30 03:59:57.0203262021-01-30 04:16:25.948311
8a012021-01-30 04:26:28.9685682021-01-30 04:30:11.863079
9a012021-01-30 04:30:14.437862021-01-30 04:41:42.231915
10a012021-01-30 04:43:17.6699452021-01-30 04:45:10.443101
11a012021-01-30 04:45:14.503462021-01-30 04:47:07.082114
12a012021-01-30 04:47:10.3345742021-01-30 04:49:30.96017
13a012021-01-30 04:49:34.1460112021-01-30 04:50:54.45988
14a012021-01-30 04:50:57.9483052021-01-30 05:11:01.246284
15a012021-01-30 05:39:29.3873962021-01-30 05:41:39.508654
16a012021-01-30 05:41:44.5249512021-01-30 05:43:38.231266
17a012021-01-30 05:43:40.7858092021-01-30 05:54:40.711381
18a012021-01-30 05:55:10.8517252021-01-30 05:58:24.262351
19a012021-01-30 05:58:29.438212021-01-30 06:00:50.870644
20a012021-01-30 06:00:54.1686962021-01-30 06:12:37.210234
21a012021-01-30 14:41:01.5296662021-01-30 14:53:01.349479
22a012021-01-30 14:53:48.0531362021-01-30 15:05:09.532786
23a012021-01-31 15:11:08.5471112021-01-31 15:11:37.564802
24a012021-01-31 15:11:40.2484992021-01-31 15:13:40.21787
25a012021-01-31 15:13:59.3731452021-01-31 15:31:54.099898
26a012021-01-31 15:32:23.204482021-01-31 15:46:33.993751
27a012021-01-31 16:55:19.1410512021-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).

输出:

<头>
playiduserid创建停止minstopped
1a012021-01-30 02:29:58.5602021-01-30 02:40:56.5572021-01-30 04:16:25.947
2a012021-01-30 02:41:24.0232021-01-30 02:55:24.1132021-01-30 04:41:42.233
3a012021-01-30 02:57:30.1802021-01-30 03:11:14.8672021-01-30 04:50:54.460
4a012021-01-30 03:11:41.0972021-01-30 03:22:50.1572021-01-30 05:11:01.247
5a012021-01-30 03:23:20.5472021-01-30 03:36:37.0272021-01-30 05:11:01.247
6a012021-01-30 03:46:10.2372021-01-30 03:59:17.5272021-01-30 05:43:38.230
7a012021-01-30 03:59:57.0202021-01-30 04:16:25.9472021-01-30 05:58:24.263
8a012021-01-30 04:26:28.9702021-01-30 04:30:11.8632021-01-30 06:12:37.210
9a012021-01-30 04:30:14.4372021-01-30 04:41:42.2332021-01-30 06:12:37.210
10a012021-01-30 04:43:17.6702021-01-30 04:45:10.4432021-01-30 06:12:37.210
11a012021-01-30 04:45:14.5032021-01-30 04:47:07.0832021-01-30 06:12:37.210
12a012021-01-30 04:47:10.3372021-01-30 04:49:30.9602021-01-30 06:12:37.210
13a012021-01-30 04:49:34.1472021-01-30 04:50:54.4602021-01-30 06:12:37.210
14a012021-01-30 04:50:57.9472021-01-30 05:11:01.2472021-01-30 06:12:37.210
15a012021-01-30 05:39:29.3872021-01-30 05:41:39.5102021-01-30 06:12:37.210
16a012021-01-30 05:41:44.5272021-01-30 05:43:38.2302021-01-30 06:12:37.210
17a012021-01-30 05:43:40.7872021-01-30 05:54:40.7102021-01-30 06:12:37.210
18a012021-01-30 05:55:10.8532021-01-30 05:58:24.2632021-01-30 06:12:37.210
19a012021-01-30 05:58:29.4372021-01-30 06:00:50.8702021-01-30 06:12:37.210
20a012021-01-30 06:00:54.1702021-01-30 06:12:37.210null
21a012021-01-30 14:41:01.5302021-01-30 14:53:01.3502021-01-30 15:05:09.533
22a012021-01-30 14:53:48.0532021-01-30 15:05:09.533null
23a012021-01-31 15:11:08.5472021-01-31 15:11:37.5672021-01-31 17:03:43.463
24a012021-01-31 15:11:40.2472021-01-31 15:13:40.2172021-01-31 17:03:43.463
25a012021-01-31 15:13:59.3732021-01-31 15:31:54.1002021-01-31 17:03:43.463
26a012021-01-31 15:32:23.2032021-01-31 15:46:33.9932021-01-31 17:03:43.463
27a012021-01-31 16:55:19.1402021-01-31 17:03:43.463null

第二部分将停止日期与上一组第一行的创建日期相距超过 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:

<块引用>

<头>
playiduseridcreatedstopped
1a012021-01-30 02:29:58.5602021-01-30 04:16:25.947
8a012021-01-30 04:26:28.9702021-01-30 06:12:37.210
21a012021-01-30 14:41:01.5302021-01-30 15:05:09.533
23a012021-01-31 15:11:08.5472021-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

这篇关于根据日期条件合并不同的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆