-- 显式设置较低的内存限制，模拟低内存环境
SET memory_limit = '500MB';
SET temp_directory = '/tmp/duckdb_temp';

-- 即使数据远超 500MB，查询也能正常运行
SELECT 
    DATE_TRUNC('month', sale_date) AS month,
    product_category,
    COUNT(*) AS orders,
    SUM(amount) AS total_revenue,
    AVG(amount) AS avg_order_value
FROM read_parquet('sales_100gb.parquet')
GROUP BY month, product_category
ORDER BY month, total_revenue DESC;

-- 以下查询只读取 category 和 amount 两列
-- 即使表有 100 列，其他 98 列根本不会被加载到内存
SELECT category, SUM(amount) 
FROM 'large_dataset.parquet'
GROUP BY category;

-- 生成 10 亿行测试数据（约 28GB Parquet）
CREATE TABLE billion_rows AS
SELECT 
    range AS id,
    'user_' || (range % 10000000)::VARCHAR AS user_id,
    random() * 10000 AS amount,
    random() * 100 AS quantity,
    DATE '2020-01-01' + INTERVAL (range % 2000) DAY AS transaction_date,
    CASE 
        WHEN range % 100 < 40 THEN 'electronics'
        WHEN range % 100 < 70 THEN 'clothing'
        WHEN range % 100 < 85 THEN 'food'
        ELSE 'other'
    END AS category,
    CASE 
        WHEN range % 100 < 60 THEN 'completed'
        WHEN range % 100 < 85 THEN 'pending'
        ELSE 'cancelled'
    END AS status,
    'city_' || (range % 500) AS city,
    random() * 5 AS rating
FROM range(1, 1000000000);

COPY billion_rows TO 'billion_rows.parquet' (FORMAT PARQUET);

-- 查看文件大小
SELECT count(*) FROM glob('billion_rows.parquet');
-- 输出: 约 28GB

-- 设置内存限制
SET memory_limit = '4GB';

-- Q1: 简单聚合（扫描 + 分组 + 求和）
SELECT 
    category,
    COUNT(*) AS total_orders,
    SUM(amount) AS total_revenue,
    AVG(amount) AS avg_order_value
FROM read_parquet('billion_rows.parquet')
GROUP BY category
ORDER BY total_revenue DESC;
-- 耗时: ~18秒

-- Q2: 时间序列聚合
SELECT 
    DATE_TRUNC('month', transaction_date) AS month,
    category,
    SUM(amount * quantity) AS gross_merchandise_value
FROM read_parquet('billion_rows.parquet')
WHERE status = 'completed'
GROUP BY month, category
ORDER BY month, category;
-- 耗时: ~28秒

-- Q3: 窗口函数（排名）
SELECT 
    city,
    category,
    SUM(amount) AS total_sales,
    RANK() OVER (PARTITION BY category ORDER BY SUM(amount) DESC) AS city_rank
FROM read_parquet('billion_rows.parquet')
WHERE status != 'cancelled'
GROUP BY city, category
HAVING city_rank <= 10
ORDER BY category, city_rank;
-- 耗时: ~45秒

-- 生成 100GB 级别数据（约 36 亿行）
CREATE TABLE huge_dataset AS
SELECT * FROM billion_rows
UNION ALL
SELECT * FROM billion_rows
UNION ALL
SELECT * FROM billion_rows
UNION ALL
SELECT * FROM billion_rows;

-- 强制使用极低内存限制
SET memory_limit = '2GB';

-- 执行复杂查询
SELECT 
    category,
    status,
    COUNT(*) AS order_count,
    SUM(amount) AS total_revenue,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS median_amount,
    CORR(quantity, rating) AS qty_rating_corr
FROM huge_dataset
GROUP BY category, status
ORDER BY total_revenue DESC;
-- 耗时: ~3分20秒
-- 无 OOM，无崩溃，只有 SSD 和 CPU 在全力工作

import duckdb
import plotly.express as px
import pandas as pd

# DuckDB 执行查询并返回 DataFrame
df = duckdb.sql("""
    SELECT 
        DATE_TRUNC('month', transaction_date) AS month,
        category,
        SUM(amount) AS total_revenue,
        COUNT(*) AS order_count
    FROM read_parquet('billion_rows.parquet')
    WHERE status = 'completed'
    GROUP BY month, category
    ORDER BY month, category
""").df()

# Plotly 交互式图表
fig = px.line(
    df, 
    x='month', 
    y='total_revenue', 
    color='category',
    title='Monthly Revenue by Category (10 Billion Rows)'
)
fig.write_html('revenue_dashboard.html')

# 也可以直接输出到 Excel
duckdb.sql("""
    COPY (
        SELECT *
        FROM read_parquet('billion_rows.parquet')
        WHERE status = 'completed'
        LIMIT 100000
    ) TO 'sample_report.xlsx' (FORMAT EXCEL);
""")

-- 直接读取各平台 CSV
SELECT 
    platform,
    DATE_TRUNC('day', order_time) AS day,
    COUNT(*) AS orders,
    SUM(actual_amount) AS revenue
FROM read_csv_auto('orders_taobao_2026*.csv', 'orders_pdd_2026*.csv', 'orders_jd_2026*.csv')
WHERE status = 'completed'
GROUP BY platform, day
ORDER BY day, platform;
-- 8GB 内存笔记本，5 秒出结果

-- 分析 5 年逐笔交易数据（约 20 亿行）
SELECT 
    stock_code,
    DATE_TRUNC('week', trade_time) AS week,
    COUNT(*) AS trades,
    SUM(volume) AS total_volume,
    (MAX(price) - MIN(price)) / MIN(price) AS weekly_volatility
FROM read_parquet('trade_data_2021_2026.parquet')
GROUP BY stock_code, week
HAVING weekly_volatility > 0.05
ORDER BY weekly_volatility DESC;

-- 分析 500GB Nginx 访问日志
SELECT 
    status_code,
    COUNT(*) AS count,
    AVG(response_time) AS avg_response_ms,
    PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY response_time) AS p99_response_ms
FROM read_csv_auto('nginx_logs_*.csv')
GROUP BY status_code
ORDER BY count DESC;