df-drills pandas · Polars · DuckDB SQL · DuckDB Relational 题目列表 登录
← 列表 ‹ 上一题 下一题 ›

展开再聚合

中等 18-nested 列表与结构体 explodeimplodelistdistinctsort

整理每篇文章的标签列表：去掉重复的标签，再排序，结果仍然是一个列表。

  • 去重区分大小写：sql 和 SQL 是两个不同的标签，都保留。
  • 排序按字符串的码点顺序升序（区分大小写，大写字母排在小写字母之前，例如 SQL 在 duckdb 之前）。
  • 空列表保持为空列表，null 列表保持为 null，两者不能混淆。
  • 每篇文章一行。

输入

  • articles(article_id, title, author, tags, published_at, word_count)：tags 是字符串列表，可能为空列表或 null

输出

列为 article_id, tags，tags 为字符串列表，行顺序不限。

输入表 （case base，共 2 个 case：base、edge）

articles blog/articles
#article_idint64titlestringauthorstruct<name: string, country: string>tagslist<string>published_atdatetimeword_countint64
1 1 A gentle intro to DuckDB {"name": "Hans Muller", "country": "DE"} ["etl", "Python", "rust"] 2026-02-07 18:20:00 353
2 2 Testing Python {"name": "Bob Smith", "country": "US"} ["SQL"] 2026-09-02 17:13:00 3726
3 3 10 tips for dataframes {"name": "Bob Smith", "country": "US"} ["pandas"] 2026-07-23 20:39:00 362
4 4 Debugging DuckDB {"name": "Amy Chen", "country": "US"} [] 2026-01-20 22:03:00 3845
5 5 Debugging window functions {"name": "Ana Souza", "country": "BR"} [] 2026-08-20 10:17:00 3899
6 6 Getting started with Polars {"name": "王芳", "country": "CN"} ["polars"] 2026-05-17 19:28:00 2086
7 7 Testing dataframes {"name": "Ana Souza", "country": "BR"} ["rust", "ml", "go"] 2026-08-01 17:58:00 1251
8 8 Benchmarking pandas {"name": "王芳", "country": "CN"} ["Python", "pandas", "ml", "go"] 2026-02-27 20:10:00 2984
9 9 Benchmarking Python {"name": "Hans Muller", "country": "DE"} ["etl"] 2026-09-09 09:30:00 1709
10 10 10 tips for dataframes {"name": "Amy Chen", "country": "US"} null 2026-08-11 21:07:00 2000
11 11 Debugging pandas {"name": "Amy Chen", "country": "US"} ["python", "viz"] 2026-06-23 17:24:00 3908
12 12 Getting started with SQL {"name": "Ana Souza", "country": "BR"} ["sql"] 2026-03-01 18:14:00 2493
13 13 Common pitfalls in SQL {"name": "Li Wei", "country": "CN"} ["python", "pandas", "python"] 2026-03-25 19:46:00 473
14 14 A gentle intro to ETL {"name": "王芳", "country": "CN"} ["viz", "SQL", "etl", "go"] 2026-07-21 18:59:00 3850
15 15 Common pitfalls in Python {"name": "Amy Chen", "country": "US"} ["SQL", "polars"] 2026-06-08 15:27:00 1357
16 16 Getting started with ETL {"name": "Ana Souza", "country": "BR"} ["rust", "ml", "python"] 2026-07-16 18:30:00 2713
17 17 A gentle intro to pandas {"name": "Kenji Sato", "country": "JP"} ["Python", "ml"] 2026-05-27 20:35:00 2305
18 18 Getting started with pandas {"name": "Li Wei", "country": "CN"} null 2026-06-17 11:01:00 1790
19 19 A gentle intro to ETL {"name": "Kenji Sato", "country": "JP"} ["duckdb", "SQL", "viz"] 2026-02-27 21:10:00 3630
20 20 Benchmarking DuckDB {"name": "Li Wei", "country": "CN"} ["Python", "duckdb"] 2026-05-07 13:35:00 1067
显示前 20 行，共 40 行

期望输出 （case base，行顺序不限）

#article_idInt64tagsList(String)
1 1 ["Python", "etl", "rust"]
2 2 ["SQL"]
3 3 ["pandas"]
4 4 []
5 5 []
6 6 ["polars"]
7 7 ["go", "ml", "rust"]
8 8 ["Python", "go", "ml", "pandas"]
9 9 ["etl"]
10 10 null
11 11 ["python", "viz"]
12 12 ["sql"]
13 13 ["pandas", "python"]
14 14 ["SQL", "etl", "go", "viz"]
15 15 ["SQL", "polars"]
16 16 ["ml", "python", "rust"]
17 17 ["Python", "ml"]
18 18 null
19 19 ["SQL", "duckdb", "viz"]
20 20 ["Python", "duckdb"]
21 21 ["Python", "ml"]
22 22 ["etl", "polars", "rust"]
23 23 ["SQL", "duckdb", "sql"]
24 24 ["duckdb"]
25 25 ["python", "viz"]
26 26 ["SQL", "etl", "polars", "python"]
27 27 ["polars", "python", "rust", "sql"]
28 28 ["duckdb", "go", "pandas"]
29 29 ["pandas"]
30 30 ["sql"]
31 31 []
32 32 ["python", "viz"]
33 33 []
34 34 ["ml", "pandas"]
35 35 ["duckdb"]
36 36 []
37 37 ["Python", "go"]
38 38 ["etl", "ml"]
39 39 ["duckdb"]
40 40 ["SQL", "go", "ml"]
共 40 行