-- 在 DuckDB 中生成 100 万行测试数据
COPY (
  SELECT 
    range + 1 AS id,
    CASE WHEN random() < 0.3 THEN 'electronics'
         WHEN random() < 0.5 THEN 'clothing'
         WHEN random() < 0.65 THEN 'home'
         WHEN random() < 0.78 THEN 'books'
         WHEN random() < 0.88 THEN 'sports'
         ELSE 'food' END AS category,
    'product_' || (range % 10000 + 1) AS product_name,
    ROUND(random() * 500 + 5, 2) AS price,
    (random() * 10 + 1)::INT AS quantity,
    ROUND(random() * 100 + 1, 2) AS discount,
    DATE '2025-01-01' + INTERVAL (random() * 364) DAY AS order_date,
    CASE WHEN random() < 0.5 THEN 'CN' ELSE 'US' END AS region,
    'user_' || (range % 50000 + 1) AS user_id
  FROM range(1000000)
) TO 'ecommerce_1m.csv' (HEADER, DELIMITER ',');

-- DuckDB：直接从 CSV 加载（零 ETL）
CREATE TABLE sales AS 
  SELECT * FROM read_csv_auto('ecommerce_1m.csv');

-- Q1: 简单计数
SELECT COUNT(*) FROM sales;

-- Q2: 总收入计算
SELECT SUM(price * quantity) AS total_revenue FROM sales;

-- Q3: 按品类分组聚合
SELECT category, COUNT(*) AS orders, 
       SUM(price * quantity) AS revenue, AVG(price) AS avg_price
FROM sales GROUP BY category ORDER BY revenue DESC;

-- Q4: 日期范围过滤
SELECT COUNT(*), SUM(price * quantity) 
FROM sales WHERE order_date BETWEEN '2025-06-01' AND '2025-08-31';

-- Q5: 多维度分组
SELECT region, category, COUNT(*) AS cnt, SUM(price * quantity) AS revenue
FROM sales GROUP BY region, category ORDER BY revenue DESC;

-- Q6: 窗口函数 - 月度累计
SELECT strftime(order_date, '%Y-%m') AS month, 
       SUM(price * quantity) AS monthly_revenue,
       SUM(SUM(price * quantity)) OVER (ORDER BY strftime(order_date, '%Y-%m')) AS running_total
FROM sales GROUP BY month ORDER BY month;

-- Q7: Top 10 商品
SELECT product_name, SUM(price * quantity) AS revenue, COUNT(*) AS orders
FROM sales GROUP BY product_name ORDER BY revenue DESC LIMIT 10;

-- Q8: 按区域统计客单价
SELECT region, AVG(price * quantity) AS avg_order_value, 
       COUNT(*) AS orders, SUM(price * quantity) AS total_revenue
FROM sales GROUP BY region;

-- Q9: 高频用户（下单 > 5 次）
SELECT user_id, COUNT(*) AS order_count, SUM(price * quantity) AS total_spent
FROM sales GROUP BY user_id 
HAVING COUNT(*) > 5 ORDER BY total_spent DESC LIMIT 20;

-- Q10: 条件聚合
SELECT 
  SUM(CASE WHEN price > 200 THEN 1 ELSE 0 END) AS expensive_orders,
  SUM(CASE WHEN discount > 50 THEN 1 ELSE 0 END) AS high_discount_orders,
  AVG(CASE WHEN region = 'CN' THEN price ELSE NULL END) AS cn_avg_price,
  AVG(CASE WHEN region = 'US' THEN price ELSE NULL END) AS us_avg_price
FROM sales;

-- SQLite：需先导入 CSV
.mode csv
.import ecommerce_1m.csv sales

-- 查询与 DuckDB 相同，唯一区别：
-- DuckDB 用 strftime() 而 SQLite 的 strftime() 参数顺序略有不同，但功能等价

-- DuckDB 直接查询 SQLite 数据库！
INSTALL sqlite;
LOAD sqlite;
SELECT category, SUM(price * quantity) AS revenue
FROM sqlite_scan('myapp.db', 'orders')
GROUP BY category ORDER BY revenue DESC;

# Python 示例：用 DuckDB 分析 SQLite 数据
import duckdb

con = duckdb.connect()
con.execute("INSTALL sqlite; LOAD sqlite;")

# 直接分析 SQLite 数据，无需导出
result = con.execute("""
    SELECT strftime(order_date, '%Y-%m') AS month,
           category,
           SUM(price * quantity) AS revenue
    FROM sqlite_scan('ecommerce.db', 'orders')
    GROUP BY month, category
    ORDER BY month, revenue DESC
""").fetchdf()

print(result)