問題描述
我在 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
這篇關于根據日期條件合并不同的行的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！