# 运行包含 pg_duckdb 的 PostgreSQL
docker run -d \
  -e POSTGRES_PASSWORD=duckdb \
  -p 5432:5432 \
  pgduckdb/pgduckdb:18-v1.1.1

git clone https://github.com/duckdb/pg_duckdb
cd pg_duckdb
make install

-- 开启 DuckDB 执行引擎
SET duckdb.force_execution = true;

-- 创建示例订单表
CREATE TABLE orders (
    order_id BIGSERIAL PRIMARY KEY,
    product_name VARCHAR(100),
    category VARCHAR(50),
    amount DECIMAL(10, 2),
    quantity INTEGER,
    order_date DATE,
    customer_id BIGINT,
    region VARCHAR(50)
);

-- 插入 100 万行模拟数据
INSERT INTO orders (product_name, category, amount, quantity, order_date, customer_id, region)
SELECT
    ('Product_' || (random() * 100)::INT) AS product_name,
    (ARRAY['Electronics', 'Clothing', 'Food', 'Books', 'Home'])[
        (random() * 4 + 1)::INT
    ] AS category,
    (random() * 1000)::DECIMAL(10, 2) AS amount,
    (random() * 10 + 1)::INT AS quantity,
    (DATE '2025-01-01' + (random() * 500)::INT) AS order_date,
    (random() * 10000)::BIGINT AS customer_id,
    (ARRAY['North', 'South', 'East', 'West'])[
        (random() * 3 + 1)::INT
    ] AS region
FROM generate_series(1, 1000000);

-- 运行分析查询（自动使用 DuckDB 加速）
SET duckdb.force_execution = true;

SELECT
    category,
    region,
    DATE_TRUNC('month', order_date) AS month,
    COUNT(*) AS order_count,
    SUM(amount) AS total_revenue,
    AVG(amount) AS avg_order_value,
    SUM(quantity) AS total_items
FROM orders
WHERE order_date >= '2025-06-01'
GROUP BY category, region, DATE_TRUNC('month', order_date)
ORDER BY total_revenue DESC
LIMIT 20;

-- 配置 S3 访问
SELECT duckdb.create_simple_secret(
    type := 'S3',
    key_id := 'your_access_key',
    secret := 'your_secret_key',
    region := 'us-east-1'
);

-- 查询 S3 上的 Parquet 文件
SELECT
    r['product_name'] AS product_name,
    AVG(r['rating']) AS average_rating,
    COUNT(*) AS review_count
FROM read_parquet('s3://your-bucket/reviews/*.parquet') r
GROUP BY r['product_name']
HAVING COUNT(*) > 10
ORDER BY average_rating DESC;

-- 将 PostgreSQL 本地订单表与远程评论 Parquet 联合分析
SELECT
    o.category,
    COUNT(DISTINCT o.product_name) AS products_sold,
    SUM(o.amount) AS total_revenue,
    AVG(r.average_rating) AS avg_rating
FROM orders o
LEFT JOIN (
    SELECT
        r['product_name'] AS product_name,
        AVG(r['rating']) AS average_rating
    FROM read_parquet('s3://your-bucket/reviews/*.parquet') r
    GROUP BY r['product_name']
) r ON o.product_name = r.product_name
GROUP BY o.category
ORDER BY total_revenue DESC;

-- 查询 Iceberg 表（带时间旅行）
SELECT duckdb.install_extension('iceberg');
SELECT * FROM iceberg_scan(
    's3://warehouse/sales_iceberg',
    version := '2026-01-15-snapshot'
);

-- 查询 Delta Lake 表
SELECT duckdb.install_extension('delta');
SELECT * FROM delta_scan('s3://lakehouse/user_events');

-- 连接 MotherDuck
CALL duckdb.enable_motherduck('your_motherduck_token');

-- 查询云端表
SELECT region, COUNT(*) FROM my_cloud_analytics_table;

-- 创建云端同步表
CREATE TABLE real_time_kpis USING duckdb AS
SELECT
    date_trunc('day', created_at) AS date,
    COUNT(*) AS daily_signups,
    SUM(revenue) AS daily_revenue
FROM user_events
GROUP BY date;