DuckDB LIST 函数完全手册：从 UNNEST 到聚合的终极指南

CREATE TABLE orders AS
SELECT * FROM (VALUES
    (1, 'Alice',   ['laptop', 'mouse', 'keyboard'], [5000, 150, 350]),
    (2, 'Bob',     ['phone'],                         [8000]),
    (3, 'Carol',   ['tablet', 'charger'],             [2000, 200]),
    (4, 'Dave',    ['laptop', 'monitor', 'webcam'],   [5000, 2800, 400]),
    (5, 'Eve',     ['headphones', 'mouse'],           [600, 150])
) AS t(order_id, customer, items, prices);

SELECT 
    o.order_id,
    o.customer,
    u.item
FROM orders AS o
CROSS JOIN UNNEST(o.items) AS u(item);

SELECT 
    o.order_id,
    u.item,
    p.price
FROM orders AS o
CROSS JOIN UNNEST(o.items) AS u(item)
CROSS JOIN UNNEST(o.prices) AS p(price);

SELECT 
    customer,
    array_agg(item) AS all_items,
    list_distinct(array_agg(item)) AS unique_items,
    list_count(array_agg(item)) AS total_count
FROM (
    SELECT o.order_id, o.customer, u.item
    FROM orders AS o
    CROSS JOIN UNNEST(o.items) AS u(item)
) AS flattened
GROUP BY customer;

SELECT 
    customer,
    list_transform(
        list_filter(items, x -> x IN (
            SELECT item FROM (
                SELECT o.customer, o.items, p.price
                FROM orders AS o
                CROSS JOIN UNNEST(o.items) AS items
                CROSS JOIN UNNEST(o.prices) AS prices(p, price)
                WHERE price > 500
            ) sub
            WHERE sub.customer = o.customer
        )),
        x -> upper(x)
    ) AS expensive_items_upper
FROM orders;

SELECT 
    customer,
    list_transform(
        list_filter(prices, p -> p > 500),
        p -> p * 0.9  -- 打九折
    ) AS discounted_prices,
    list_filter(items, i -> i IN (
        SELECT item FROM (
            SELECT o.customer, o.items, o.prices
            FROM orders AS o
        ) x
        WHERE ...
    )) AS corresponding_items
FROM orders;

SELECT list_transform(
    ['  Hello ', 'WORLD  ', '  DuckDB  '],
    x -> trim(lower(x))
) AS cleaned;
-- 结果：['hello', 'world', 'duckdb']

-- 筛选偶数
SELECT list_filter([1,2,3,4,5,6,7,8,9,10], x -> x % 2 = 0);
-- 结果：[2, 4, 6, 8, 10]

-- 筛选包含特定前缀的字符串
SELECT list_filter(['apple', 'banana', 'apricot', 'cherry'], x -> x LIKE 'app%');
-- 结果：['apple', 'apricot']

-- 两个列表的交集
SELECT list_intersect(['laptop', 'mouse', 'keyboard'], ['laptop', 'monitor']);
-- 结果：['laptop']

-- 检查列表是否包含某值
SELECT list_contains(['laptop', 'mouse', 'keyboard'], 'mouse');
-- 结果：true

-- 检查两个列表是否有共同元素
SELECT list_has_any(['laptop', 'mouse'], ['phone', 'tablet']);
-- 结果：false（无交集）

SELECT list_has_any(['laptop', 'mouse'], ['mouse', 'keyboard']);
-- 结果：true（有交集）

WITH customer_tags AS (
    SELECT 
        customer,
        array_agg(DISTINCT CASE WHEN price > 2000 THEN 'high-value' ELSE 'standard' END) AS segments
    FROM (
        SELECT o.customer, o.items, p.price
        FROM orders AS o
        CROSS JOIN UNNEST(o.items) AS items
        CROSS JOIN UNNEST(o.prices) AS prices(p, price)
    ) x
    GROUP BY customer
)
SELECT 
    a.customer AS customer_a,
    b.customer AS customer_b,
    list_intersect(a.segments, b.segments) AS shared_segments
FROM customer_tags AS a
CROSS JOIN customer_tags AS b
WHERE a.customer < b.customer;

SELECT 
    customer,
    list_slice(
        list_sort(prices, 'DESC'),
        1, 3
    ) AS top_3_prices,
    list_slice(
        list_sort(items, 'ASC'),
        1, 2
    ) AS first_2_items_alphabetically
FROM orders;

SELECT list_element(
    list_sort(prices),
    1
) AS lowest_price
FROM orders
WHERE customer = 'Bob';
-- 结果：8000（只有一个元素）

-- 拼接两个列表
SELECT list_concat([1, 2, 3], [4, 5, 6]);
-- 结果：[1, 2, 3, 4, 5, 6]

-- 反转列表
SELECT list_reverse([1, 2, 3, 4, 5]);
-- 结果：[5, 4, 3, 2, 1]

-- 带空值的排序
SELECT list_sort([3, NULL, 1, NULL, 5], 'ASC', 'NULLS FIRST');
-- 结果：[NULL, NULL, 1, 3, 5]

SELECT list_sort([3, NULL, 1, NULL, 5], 'ASC', 'NULLS LAST');
-- 结果：[1, 3, 5, NULL, NULL]

SELECT 
    order_id,
    list_concat(
        list_distinct(tags_source_a),
        list_distinct(tags_source_b)
    ) AS combined_tags
FROM tag_merging_table;

import duckdb

con = duckdb.connect()

# 1. 展开嵌套列表
result = con.execute("""
    SELECT order_id, item
    FROM orders
    CROSS JOIN UNNEST(items) AS u(item)
""").fetchdf()

# 2. 分组收集标签并去重
result = con.execute("""
    SELECT category,
           list_sort(array_agg(tag)) AS tags,
           list_distinct(array_agg(tag)) AS unique_tags,
           list_count(array_agg(tag)) AS total_count
    FROM tags_table
    GROUP BY category
""").fetchdf()

# 3. 列表过滤 + 变换
result = con.execute("""
    SELECT list_transform(
        list_filter([1,2,3,4,5,6,7,8,9,10], x -> x % 2 = 0),
        x -> x * x
    ) AS squares_of_evens
""").fetchdf()
# 结果：[4, 16, 36, 64, 100]

# 4. 取 Top-N
result = con.execute("""
    SELECT list_slice(
        list_sort(array_agg(score), 'DESC'),
        1, 5
    ) AS top_5_scores
    FROM exam_results
""").fetchdf()

import duckdb
import pandas as pd
import time

# 生成 100 万条订单数据
con = duckdb.connect()
con.execute("""
    CREATE TABLE big_orders AS
    SELECT 
        generate_series AS order_id,
        'Customer_' || (generate_series % 1000)::VARCHAR AS customer,
        array_slice(
            array_generate(1, 10),
            1,
            (random() * 5 + 1)::INTEGER
        ) AS item_ids,
        array_select(
            array_generate(100, 10000),
            x -> x % 3 = 0
        ) AS prices
    FROM generate_series(1, 1000000)
""")

# DuckDB LIST 操作
start = time.time()
result = con.execute("""
    SELECT customer,
           list_count(array_agg(item_ids)) AS total_items,
           list_sort(array_agg(prices), 'DESC')[1] AS max_price
    FROM big_orders
    CROSS JOIN UNNEST(item_ids) AS u
    GROUP BY customer
""").fetchdf()
print(f"DuckDB: {time.time() - start:.3f}s")

# 对比：Pandas 等效操作
start = time.time()
df = con.execute("SELECT * FROM big_orders").fetchdf()
expanded = df.explode('item_ids')
result_pd = expanded.groupby('customer').agg(
    total_items=('item_ids', 'count')
)
print(f"Pandas: {time.time() - start:.3f}s")

-- 基本 Lambda
SELECT list_transform([1,2,3], x -> x * x);
-- 结果：[1, 4, 9]

-- 多参数 Lambda（通过索引访问）
SELECT list_transform(
    ['Alice', 'Bob', 'Carol'],
    (x, i) -> i || ': ' || upper(x)
);
-- 结果：['0: ALICE', '1: BOB', '2: CAROL']

-- 嵌套条件
SELECT list_filter(
    [1, 2, 3, 4, 5, 6, 7, 8, 9, 10],
    x -> CASE 
        WHEN x % 3 = 0 THEN true
        WHEN x % 2 = 0 THEN false
        ELSE true
    END
);
-- 结果：[1, 3, 5, 6, 7, 9, 10]

-- 创建嵌套列表数据
CREATE TABLE nested_data AS
SELECT * FROM (VALUES
    (1, [[1, 2], [3, 4]]),
    (2, [[5, 6]]),
    (3, [[7, 8], [9, 10], [11, 12]])
) AS t(id, matrix);

-- 展平嵌套列表：一行变多行
SELECT id, list_element(item, 1) AS first_col, list_element(item, 2) AS second_col
FROM nested_data
CROSS JOIN UNNEST(matrix) AS u(item);

-- 列表转 JSON 数组
SELECT list_to_json(['a', 'b', 'c']);
-- 结果：'["a","b","c"]'

-- JSON 数组转列表
SELECT json_array_elements('[1,2,3]'::JSON);
-- 结果：1, 2, 3（三行）

-- 列表转 JSON 对象（键值对）
SELECT zip_with(['name', 'age', 'city'], ['Alice', 30, 'Beijing']);
-- 结果：{'name': 'Alice', 'age': 30, 'city': 'Beijing'}

-- ❌ 危险：每个订单平均 50 个商品，100 万订单会产生 5000 万行
SELECT * FROM orders CROSS JOIN UNNEST(products) AS p;

-- ✅ 建议：先用 WHERE 过滤再展开
SELECT * FROM orders 
WHERE customer_id = 12345
  AND order_date >= '2026-01-01'
  CROSS JOIN UNNEST(products) AS p;

-- 如果 items 为空列表 []，CROSS JOIN UNNEST 会直接丢弃该行
-- 使用 LEFT JOIN LATERAL 保留空列表行
SELECT o.order_id, u.item
FROM orders AS o
LEFT JOIN LATERAL UNNEST(o.items) AS u(item) ON true;

-- ❌ 数字和字符串不能一起排序
SELECT list_sort([1, 'a', 2]);
-- 报错：Type INTEGER and VARCHAR cannot be compared

-- ✅ 先统一类型再排序
SELECT list_sort(list_transform([1, 'a', 2], x -> CAST(x AS VARCHAR)));