DuckDB 1.5 窗口函数宏革命：一行 SQL 实现 JSON 聚合分组

CREATE TABLE fruit_sales (
    person VARCHAR,
    fruit VARCHAR,
    quantity INT,
    price DOUBLE
);

INSERT INTO fruit_sales VALUES
    ('Alice', 'Apple',   3,  2.50),
    ('Alice', 'Banana',  2,  1.20),
    ('Alice', 'Cherry',  5,  3.00),
    ('Bob',   'Apple',   1,  2.50),
    ('Bob',   'Orange',  4,  1.80),
    ('Charlie','Banana', 6,  1.20);

SELECT DISTINCT
    person,
    (SELECT json_group_object(fruit, quantity)
     FROM fruit_sales fs2
     WHERE fs2.person = fs1.person) AS fruit_orders
FROM fruit_sales fs1;

import duckdb
import json

con = duckdb.connect(':memory:')
# ... 加载数据 ...

result = con.execute("""
    SELECT person, json_group_object(fruit, quantity)
    FROM fruit_sales GROUP BY person
""").fetchall()

# 然后需要用 Python 代码将结果合并回原始表

SELECT
    person,
    fruit,
    quantity,
    json_group_object(fruit, quantity)
        OVER (PARTITION BY person) AS fruit_orders
FROM fruit_sales;

-- 每个用户在每个会话中的点击序列
CREATE TABLE user_clicks (
    user_id VARCHAR,
    session_id INT,
    page VARCHAR,
    timestamp TIMESTAMP
);

SELECT
    user_id,
    session_id,
    page,
    array_agg(page) OVER (PARTITION BY user_id, session_id ORDER BY timestamp) AS click_sequence
FROM user_clicks;

-- 为每个产品的每日销售额生成累积 JSON 数组
SELECT
    product_id,
    sale_date,
    amount,
    json_group_array(amount) OVER (
        PARTITION BY product_id
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_sales
FROM daily_sales;

-- 生成部门级别的汇总报告
SELECT
    department,
    employee_name,
    salary,
    json_group_object(employee_name, salary)
        OVER (PARTITION BY department) AS dept_salary_map
FROM employees;

import duckdb
import time

con = duckdb.connect(':memory:')

# 生成测试数据
con.execute("""
    CREATE TABLE test_data AS
    SELECT
        ('User' || (random() * 1000)::INT) AS user_id,
        ('Item' || (random() * 100)::INT) AS item,
        (random() * 100)::INT AS quantity
    FROM range(1000000)
""")

# 方法 1: 窗口函数宏（最快）
start = time.time()
result1 = con.execute("""
    SELECT user_id,
           json_group_object(item, quantity) OVER (PARTITION BY user_id)
    FROM test_data
""").fetchall()
print(f"窗口函数宏: {time.time() - start:.2f}s")

# 方法 2: 子查询（最慢）
start = time.time()
result2 = con.execute("""
    SELECT DISTINCT user_id,
           (SELECT json_group_object(item, quantity)
            FROM test_data t2
            WHERE t2.user_id = t1.user_id)
    FROM test_data t1
""").fetchall()
print(f"子查询法: {time.time() - start:.2f}s")