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

聚合成列表

中等 10-groupby 分组聚合 groupbylistdistinctsort

列出每个客户买过的商品品类。

  • 订单 → 订单行 → 商品,得到每个客户买过的所有 category;不区分订单状态。
  • categories 是一个字符串列表:去重,并按字母顺序升序排列。
  • customer_id 为 null 的订单忽略;没有订单行的订单不贡献任何品类,所以只出现在这类订单里的客户不输出。
  • 客户表不参与本题,customer_id 在客户表里查不到的订单照常统计。

输入

  • orders(order_id, customer_id, order_ts, status, channel)
  • order_items(order_id, product_id, qty, unit_price, discount)
  • products(product_id, name, category, price, cost, launched_on)

输出

列为 customer_id, categories,categories 的类型是 list<string>。行顺序不限,但列表内部的顺序要符合要求。

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

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 行
order_items shop/order_items
#order_idint64product_idint64qtyint64unit_pricefloat64discountfloat64
1 1001 22 3 298.8 null
2 1001 26 1 190.0 0.1
3 1002 6 2 254.2 0.0
4 1002 9 3 79.0 null
5 1002 19 5 305.1 0.2
6 1003 9 2 79.0 0.1
7 1003 28 2 47.4 0.0
8 1004 5 1 500.0 0.1
9 1005 3 1 89.0 0.0
10 1005 12 4 45.0 null
11 1006 13 1 19.9 0.1
12 1006 25 2 39.9 null
13 1007 27 1 12.5 null
14 1008 3 1 89.0 null
15 1008 16 1 29.9 null
16 1008 17 1 129.0 null
17 1009 2 1 49.9 0.0
18 1009 10 2 42.5 null
19 1009 20 1 84.5 0.05
20 1010 1 1 23.8 0.1
显示前 20 行,共 500 行
products shop/products
#product_idint64namestringcategorystringpricefloat64costfloat64launched_ondate
1 1 Wireless Mouse Electronics 25.0 16.77 2025-05-07
2 2 USB-C Hub Electronics 49.9 34.17 2025-07-27
3 3 Bluetooth Speaker Electronics 89.0 61.47 2025-02-10
4 4 Mechanical Keyboard Electronics 199.0 105.48 2024-09-19
5 5 4K Monitor Electronics 500.0 282.38 2024-07-10
6 6 Noise-cancel Headphones Electronics 299.0 211.57 2025-01-14
7 7 Python Cookbook Books 59.0 30.08 2025-09-15
8 8 SQL Pocket Guide Books 39.9 22.14 2025-01-20
9 9 数据分析实战 Books 79.0 40.83 2024-12-15
10 10 Clean Code Books 50.0 28.48 2025-03-13
11 11 统计学习方法 Books 99.0 70.22 2025-03-13
12 12 Desk Lamp Home 45.0 24.99 2025-11-04
13 13 Ceramic Mug Home 19.9 11.34 2025-12-02
14 14 Throw Pillow Home 35.0 19.23 2024-06-29
15 15 Wall Clock Home 99.0 47.98 2025-02-23
16 16 Storage Box Home 29.9 15.06 2025-02-27
17 17 Puzzle 1000pc Toys 129.0 59.53 2025-08-19
18 18 Building Blocks Toys 199.0 110.19 2025-04-18
19 19 RC Car Toys 359.0 208.55 2025-11-06
20 20 Plush Panda Toys 89.0 43.14 2026-01-12
显示前 20 行,共 30 行

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

#customer_idInt64categoriesList(String)
1 11 ["Beauty", "Books", "Electronics", "Home", "Sports", "Toys"]
2 47 ["Books", "Electronics", "Home", "Sports", "Toys"]
3 26 ["Beauty", "Books", "Electronics", "Home", "Sports", "Toys"]
4 18 ["Books", "Electronics", "Home", "Sports", "Toys"]
5 28 ["Beauty", "Books", "Electronics", "Sports", "Toys"]
6 32 ["Home", "Toys"]
7 39 ["Beauty", "Books", "Home", "Sports"]
8 50 ["Beauty", "Books", "Electronics", "Sports", "Toys"]
9 16 ["Books", "Electronics", "Toys"]
10 30 ["Home"]
11 17 ["Beauty", "Books", "Electronics", "Home", "Sports", "Toys"]
12 34 ["Beauty", "Books", "Electronics", "Home", "Sports", "Toys"]
13 37 ["Beauty", "Sports", "Toys"]
14 35 ["Beauty", "Home"]
15 5 ["Beauty", "Books", "Electronics", "Home", "Sports", "Toys"]
16 2 ["Beauty", "Books", "Electronics", "Home", "Sports", "Toys"]
17 14 ["Beauty", "Books", "Home", "Toys"]
18 9 ["Beauty", "Books", "Electronics", "Home", "Sports", "Toys"]
19 31 ["Beauty", "Books", "Electronics", "Toys"]
20 42 ["Books", "Electronics", "Home", "Sports", "Toys"]
21 49 ["Beauty", "Books", "Electronics", "Home", "Toys"]
22 29 ["Beauty", "Books", "Electronics", "Home", "Sports"]
23 22 ["Books", "Electronics", "Home", "Sports"]
24 44 ["Beauty", "Electronics", "Toys"]
25 10 ["Sports"]
26 36 ["Books", "Electronics", "Home", "Sports"]
27 3 ["Beauty", "Books", "Electronics", "Sports"]
28 12 ["Beauty", "Books", "Electronics", "Home", "Sports", "Toys"]
29 46 ["Books", "Home", "Sports"]
30 45 ["Books", "Electronics", "Home", "Toys"]
31 4 ["Beauty", "Books", "Electronics", "Home", "Sports", "Toys"]
32 998 ["Beauty", "Electronics", "Home", "Sports"]
33 43 ["Home", "Sports", "Toys"]
34 8 ["Beauty", "Books", "Home", "Toys"]
35 999 ["Toys"]
36 6 ["Books", "Electronics", "Sports", "Toys"]
37 21 ["Books", "Electronics", "Home", "Sports", "Toys"]
38 27 ["Beauty", "Electronics", "Home", "Sports"]
39 38 ["Beauty", "Books", "Electronics", "Home", "Sports"]
40 25 ["Beauty", "Books", "Electronics", "Sports"]
41 20 ["Books", "Electronics", "Home", "Sports", "Toys"]
42 40 ["Beauty", "Books", "Electronics", "Toys"]
43 19 ["Beauty", "Sports", "Toys"]
44 1 ["Beauty", "Books", "Home", "Toys"]
45 24 ["Beauty", "Books", "Electronics", "Home", "Sports", "Toys"]
46 13 ["Home", "Sports"]
共 46 行