df-drills pandas · Polars · DuckDB SQL · DuckDB Relational 题目列表 登录

下一次事件

中等 15-window-offset 窗口:位移 leadshiftduration

对每个用户的每条事件,找出该用户的下一条事件的时间,以及两者相隔的秒数。

  • 每个用户的事件按 ts 从早到晚排序,ts 相同时按 event_id 从小到大排;“下一条”就是排序后的下一行。
  • 同一秒内的两条事件互为前后,间隔为 0 秒。
  • 每个用户的最后一条事件没有下一条,next_ts 和 gap_sec 都为 null。
  • gap_sec = next_ts - ts,单位为秒(数值)。
  • 所有事件类型都参与;表里的行不保证有序。

输入

  • events(event_id, user_id, ts, event_type, page)

输出

列为 event_id, user_id, ts, next_ts, gap_sec,每条事件一行,行顺序不限。

输入表 (case base,共 2 个 case:base、edge)

events web/events
#event_idint64user_idint64tsdatetimeevent_typestringpagestring
1 1 53 2026-06-03 17:57:33 login /home
2 2 53 2026-06-04 15:21:00 visit /home
3 3 53 2026-06-04 15:22:00 view /product/303
4 4 53 2026-06-04 15:32:04 add_to_cart /cart
5 5 56 2026-06-05 23:24:19 login /home
6 6 56 2026-06-05 23:29:18 view /product/202
7 7 56 2026-06-05 23:36:15 view /product/202
8 8 56 2026-06-05 23:47:36 add_to_cart /cart
9 9 56 2026-06-05 23:56:24 checkout /checkout
10 10 56 2026-06-06 19:14:00 visit /home
11 11 56 2026-06-06 19:22:53 view /product/303
12 12 56 2026-06-06 19:25:12 view /home
13 13 56 2026-06-06 19:28:25 add_to_cart /cart
14 14 29 2026-06-07 10:40:01 login /home
15 15 29 2026-06-07 10:46:43 view /product/303
16 16 29 2026-06-07 11:04:46 add_to_cart /cart
17 17 29 2026-06-07 11:09:25 checkout /checkout
18 18 34 2026-06-07 12:57:49 login /home
19 19 34 2026-06-07 13:03:57 view /product/202
20 20 34 2026-06-07 13:11:07 view /home
显示前 20 行,共 1500 行

期望输出 (case base,行顺序不限)

#event_idInt64user_idInt64tsDatetime(time_unit='us', time_zone=None)next_tsDatetime(time_unit='us', time_zone=None)gap_secInt64
1 406 15 2026-08-17 10:39:06 2026-08-17 10:47:30 504
2 407 15 2026-08-17 10:47:30 2026-08-17 10:48:44 74
3 408 15 2026-08-17 10:48:44 2026-08-17 10:52:58 254
4 409 15 2026-08-17 10:52:58 2026-08-18 18:23:00 113402
5 419 15 2026-08-18 18:23:00 2026-08-18 18:32:24 564
6 420 15 2026-08-18 18:32:24 2026-08-18 18:43:32 668
7 421 15 2026-08-18 18:43:32 2026-08-18 18:54:24 652
8 422 15 2026-08-18 18:54:24 2026-08-21 18:17:14 256970
9 448 15 2026-08-21 18:17:14 2026-08-21 18:26:59 585
10 449 15 2026-08-21 18:26:59 2026-08-21 18:30:28 209
11 450 15 2026-08-21 18:30:28 2026-08-21 20:00:14 5386
12 451 15 2026-08-21 20:00:14 2026-08-21 20:03:17 183
13 452 15 2026-08-21 20:03:17 2026-08-21 20:10:18 421
14 453 15 2026-08-21 20:10:18 2026-08-21 20:11:43 85
15 454 15 2026-08-21 20:11:43 2026-08-21 20:29:12 1049
16 455 15 2026-08-21 20:29:12 2026-08-21 20:43:33 861
17 456 15 2026-08-21 20:43:33 2026-08-24 08:55:00 216687
18 481 15 2026-08-24 08:55:00 2026-08-24 08:56:23 83
19 482 15 2026-08-24 08:56:23 2026-08-24 08:57:51 88
20 483 15 2026-08-24 08:57:51 2026-08-24 09:04:43 412
21 484 15 2026-08-24 09:04:43 2026-08-24 09:15:14 631
22 485 15 2026-08-24 09:15:14 2026-08-28 10:27:22 349928
23 524 15 2026-08-28 10:27:22 2026-08-28 10:35:20 478
24 525 15 2026-08-28 10:35:20 2026-08-28 13:25:22 10202
25 526 15 2026-08-28 13:25:22 2026-08-28 13:29:27 245
26 527 15 2026-08-28 13:29:27 2026-09-17 02:03:57 1686870
27 859 15 2026-09-17 02:03:57 2026-09-17 04:18:57 8100
28 864 15 2026-09-17 04:18:57 2026-09-17 04:25:27 390
29 865 15 2026-09-17 04:25:27 2026-10-04 20:12:53 1525646
30 1270 15 2026-10-04 20:12:53 2026-10-04 20:13:08 15
31 1271 15 2026-10-04 20:13:08 2026-10-04 20:26:44 816
32 1272 15 2026-10-04 20:26:44 2026-10-04 20:40:23 819
33 1273 15 2026-10-04 20:40:23 2026-10-04 21:19:53 2370
34 1274 15 2026-10-04 21:19:53 2026-10-04 21:28:01 488
35 1275 15 2026-10-04 21:28:01 2026-10-04 21:30:50 169
36 1276 15 2026-10-04 21:30:50 2026-10-04 21:36:34 344
37 1277 15 2026-10-04 21:36:34 2026-10-04 21:49:55 801
38 1278 15 2026-10-04 21:49:55 2026-10-12 15:11:56 667321
39 1460 15 2026-10-12 15:11:56 2026-10-12 15:15:05 189
40 1461 15 2026-10-12 15:15:05 null null
41 466 22 2026-08-23 09:23:36 2026-08-23 09:25:51 135
42 467 22 2026-08-23 09:25:51 2026-08-23 09:33:19 448
43 468 22 2026-08-23 09:33:19 2026-09-01 05:19:49 762390
44 567 22 2026-09-01 05:19:49 2026-09-01 05:28:25 516
45 568 22 2026-09-01 05:28:25 2026-09-01 05:33:01 276
46 569 22 2026-09-01 05:33:01 2026-09-01 05:40:09 428
47 570 22 2026-09-01 05:40:09 2026-09-01 05:41:09 60
48 571 22 2026-09-01 05:41:09 2026-09-01 05:46:11 302
49 572 22 2026-09-01 05:46:11 2026-09-07 04:57:36 515485
50 640 22 2026-09-07 04:57:36 2026-09-07 05:16:16 1120
显示前 50 行,共 1500 行