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

第几单

简单 14-window-rank 窗口:排名 row-numberwindowtie-break

给每个客户的订单编号:这是该客户的第几单。

  • 每个客户内部按 order_ts 从早到晚编号,nth 从 1 开始,连续不跳号。
  • 同一客户在同一时刻下了多单时,再按 order_id 从小到大排,保证编号唯一。
  • 所有状态的订单都参与编号。
  • customer_id 为 null 的订单不属于任何客户,不输出。

输入

  • orders(order_id, customer_id, order_ts, status, channel)

输出

列为 order_id, customer_id, nth(整数),每个有客户的订单一行,行顺序不限。

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

orders shop/orders
#order_idint64customer_idint64order_tsdatetimestatusstringchannelstring
1 1001 39 2026-01-01 19:38:00 paid app
2 1002 16 2026-01-17 14:07:00 paid app
3 1003 4 2026-01-08 15:40:20 cancelled web
4 1004 2 2026-05-23 10:48:58 paid null
5 1005 12 2026-06-22 11:07:22 paid store
6 1006 17 2026-08-15 05:15:25 paid web
7 1007 9 2026-06-11 01:00:32 refunded store
8 1008 45 2026-09-19 20:47:10 paid web
9 1009 2 2025-10-05 04:53:38 paid app
10 1010 4 2026-02-19 01:39:54 paid app
11 1011 28 2026-03-05 03:04:45 refunded store
12 1012 2 2026-08-01 07:09:17 paid web
13 1013 26 2026-01-22 03:48:14 refunded store
14 1014 2 2026-02-09 01:41:41 paid app
15 1015 5 2026-08-03 19:49:04 refunded web
16 1016 26 2026-02-16 00:43:43 refunded app
17 1017 9 2025-12-06 04:48:57 pending app
18 1018 999 2026-05-19 12:13:43 paid store
19 1019 26 2026-02-05 15:02:26 paid store
20 1020 44 2025-11-24 11:19:00 cancelled web
显示前 20 行,共 200 行

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

#order_idInt64customer_idInt64nthInt64
1 1086 3 1
2 1185 3 2
3 1171 3 3
4 1200 6 1
5 1044 6 2
6 1170 6 3
7 1037 38 1
8 1079 38 2
9 1162 38 3
10 1067 12 1
11 1110 12 2
12 1195 12 3
13 1199 12 4
14 1088 12 5
15 1040 12 6
16 1051 12 7
17 1053 12 8
18 1094 12 9
19 1111 12 10
20 1005 12 11
21 1187 12 12
22 1174 12 13
23 1150 20 1
24 1069 20 2
25 1149 20 3
26 1183 36 1
27 1028 36 2
28 1064 36 3
29 1089 998 1
30 1003 4 1
31 1010 4 2
32 1169 4 3
33 1096 4 4
34 1035 24 1
35 1108 24 2
36 1061 40 1
37 1137 40 2
38 1092 43 1
39 1078 43 2
40 1142 45 1
41 1045 45 2
42 1113 45 3
43 1008 45 4
44 1018 999 1
45 1121 1 1
46 1022 1 2
47 1084 21 1
48 1177 21 2
49 1191 21 3
50 1175 21 4
显示前 50 行,共 198 行