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

组内拼接字符串

中等 07-string 字符串 stringaggregatejoindistinct

把每个订单买的商品名拼成一个字符串 products。

  • 通过 product_id 关联 products 拿到商品名 name;在 products 里找不到的商品直接忽略。一个订单的商品如果全都找不到,这个订单不输出。
  • 同一订单里商品名去重(不同 product_id 可能同名,名字完全相同才算重复,区分大小写)。
  • 去重后按商品名升序排列,比较规则是按字符编码逐个比较(所以大写字母排在小写字母前面,例如 Banana 在 apple 前面)。
  • 用 ,(逗号加一个空格)连接。

输入

  • order_items(order_id, product_id, qty, unit_price, discount)
  • products(product_id, name, category, price, cost, launched_on)

输出

列为 order_id, products,每个订单一行,行顺序不限。

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

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,行顺序不限)

#order_idInt64productsString
1 1183 Bluetooth Speaker, Python Cookbook, 统计学习方法
2 1193 Desk Lamp
3 1196 Ceramic Mug, RC Car
4 1162 Face Cream, Jump Rope, Wireless Mouse
5 1185 4K Monitor, Jump Rope, USB-C Hub, 数据分析实战, 统计学习方法
6 1199 Bluetooth Speaker, Plush Panda
7 1180 Bluetooth Speaker, Building Blocks, Mechanical Keyboard, RC Car
8 1168 Desk Lamp, Jump Rope, Puzzle 1000pc, RC Car, 数据分析实战
9 1175 Building Blocks, SQL Pocket Guide, Water Bottle
10 1184 Face Cream, Puzzle 1000pc, Shampoo
11 1171 Clean Code, Sunscreen
12 1156 4K Monitor, Building Blocks, Storage Box
13 1191 RC Car
14 1174 Lip Balm, Water Bottle
15 1070 4K Monitor, Python Cookbook, Storage Box
16 1181 Desk Lamp, Puzzle 1000pc, SQL Pocket Guide
17 1155 Bluetooth Speaker, Building Blocks, Mechanical Keyboard, 统计学习方法
18 1160 Bluetooth Speaker, Face Cream, Jump Rope
19 1143 Lip Balm
20 1127 4K Monitor, Desk Lamp, Noise-cancel Headphones
21 1098 4K Monitor, Storage Box, Wireless Mouse
22 1114 USB-C Hub
23 1105 4K Monitor, Noise-cancel Headphones, Sunscreen
24 1102 Shampoo, Sunscreen
25 1037 Desk Lamp, Python Cookbook
26 1124 Plush Panda, Storage Box
27 1135 Face Cream
28 1072 RC Car, Sunscreen, Water Bottle
29 1141 Building Blocks, Mechanical Keyboard
30 1027 Python Cookbook
31 1136 Noise-cancel Headphones, SQL Pocket Guide
32 1084 Bluetooth Speaker, Jump Rope, Plush Panda, 数据分析实战
33 1133 Clean Code, Mechanical Keyboard
34 1132 SQL Pocket Guide, Storage Box
35 1089 4K Monitor, Ceramic Mug, Jump Rope, Lip Balm, Storage Box
36 1071 Yoga Mat
37 1034 Building Blocks, Lip Balm, Wall Clock, 数据分析实战
38 1094 Lip Balm, Shampoo, USB-C Hub
39 1038 Clean Code, Wall Clock
40 1088 Bluetooth Speaker
41 1035 Dumbbell Set, Mechanical Keyboard, Puzzle 1000pc, 数据分析实战
42 1044 Building Blocks, Jump Rope, Noise-cancel Headphones, Plush Panda
43 1021 Building Blocks, Jump Rope, RC Car, 数据分析实战
44 1086 Clean Code, Shampoo
45 1111 Desk Lamp
46 1039 Ceramic Mug
47 1033 Dumbbell Set, Mechanical Keyboard, 统计学习方法
48 1110 Building Blocks, Desk Lamp
49 1024 Building Blocks, Ceramic Mug, USB-C Hub
50 1075 Ceramic Mug, Dumbbell Set, Water Bottle
显示前 50 行,共 200 行