DuckDB 数据分析实战：比 Pandas 快 10 倍的查询技巧

import pandas as pd
df = pd.read_csv("huge_file.csv")  # 10GB 文件，直接爆内存

import duckdb

# 直接查询 CSV 文件，不加载到内存
result = duckdb.query("""
    SELECT city, AVG(price) as avg_price
    FROM 'sales.csv'
    GROUP BY city
    ORDER BY avg_price DESC
""").df()

import duckdb

# 自动并行执行
result = duckdb.query("""
    SELECT 
        date_trunc('month', order_date) as month,
        category,
        SUM(amount) as total
    FROM 'orders.csv'
    GROUP BY month, category
    ORDER BY month, total DESC
""").df()

import duckdb

# 每个城市的销售额 Top3 商品
result = duckdb.query("""
    SELECT *
    FROM (
        SELECT 
            city,
            product,
            sales,
            ROW_NUMBER() OVER (PARTITION BY city ORDER BY sales DESC) as rank
        FROM 'sales_data.csv'
    ) t
    WHERE rank <= 3
""").df()

import duckdb

# 查询嵌套 JSON
result = duckdb.query("""
    SELECT 
        user_id,
        data->>'name' as username,
        data->'orders'->0->>'product' as first_order_product,
        array_length(data->'orders') as order_count
    FROM 'users.json'
    LIMIT 10
""").df()

import duckdb

# 直接查询 Parquet
result = duckdb.query("""
    SELECT 
        category,
        AVG(price) as avg_price,
        COUNT(*) as count
    FROM 'data.parquet'
    WHERE date >= '2024-01-01'
    GROUP BY category
    ORDER BY avg_price DESC
""").df()

import duckdb

# 创建视图
duckdb.execute("""
    CREATE VIEW sales_summary AS
    SELECT 
        city,
        category,
        SUM(amount) as total_sales,
        COUNT(*) as order_count,
        AVG(amount) as avg_order_value
    FROM 'sales_data.csv'
    GROUP BY city, category
""")

# 查询视图
result = duckdb.query("""
    SELECT *
    FROM sales_summary
    WHERE total_sales > 100000
    ORDER BY total_sales DESC
""").df()