DuckDB：数据分析的新标配

-- 一行代码完成以前需要 10 行 Pandas 的操作
SELECT 
  category,
  COUNT(*) as orders,
  ROUND(SUM(price * quantity), 2) as revenue,
  ROUND(AVG(price * quantity), 2) as avg_order
FROM read_csv_auto('ecommerce_1m.csv')
GROUP BY category
ORDER BY revenue DESC;

# 安装（一行命令）
pip install duckdb

# 启动（零配置）
duckdb

# 开始分析
SELECT * FROM read_csv_auto('data/sales.csv');

import pandas as pd

# 读取数据
df = pd.read_csv('sales_2025_q3.csv')

# 清洗数据
df = df[df['amount'] > 0]
df['order_date'] = pd.to_datetime(df['order_date'])

# 按区域聚合
region_summary = df.groupby('region').agg({
    'amount': ['sum', 'mean', 'count'],
    'discount': 'mean'
}).round(2)

# 按品类聚合
category_summary = df.groupby('category').agg({
    'amount': 'sum',
    'id': 'count'
}).rename(columns={'id': 'orders'}).round(2)

# 按月趋势
df['month'] = df['order_date'].dt.to_period('M')
monthly_trend = df.groupby('month').agg({
    'amount': 'sum',
    'id': 'count'
}).round(2)

# 导出报告
region_summary.to_excel('region_report.xlsx')
category_summary.to_excel('category_report.xlsx')
monthly_trend.to_excel('monthly_report.xlsx')

-- 一个查询文件，完成所有分析
CREATE OR REPLACE TABLE sales AS
SELECT 
  id, category, product_name, price, quantity, discount,
  order_date, region, user_id,
  price * quantity as line_total
FROM read_csv_auto('sales_2025_q3.csv');

-- 区域汇总
COPY (
  SELECT region,
    COUNT(*) as orders,
    ROUND(SUM(line_total), 2) as revenue,
    ROUND(AVG(line_total), 2) as avg_order,
    ROUND(AVG(discount), 2) as avg_discount
  FROM sales
  GROUP BY region
  ORDER BY revenue DESC
) TO 'region_report.csv' (HEADER);

-- 品类汇总
COPY (
  SELECT category,
    COUNT(*) as orders,
    ROUND(SUM(line_total), 2) as revenue,
    ROUND(AVG(line_total), 2) as avg_order,
    ROUND(MIN(line_total), 2) as min_order,
    ROUND(MAX(line_total), 2) as max_order
  FROM sales
  GROUP BY category
  ORDER BY revenue DESC
) TO 'category_report.csv' (HEADER);

-- 月度趋势
COPY (
  SELECT 
    DATE_TRUNC('month', order_date) as month,
    COUNT(*) as orders,
    ROUND(SUM(line_total), 2) as revenue
  FROM sales
  GROUP BY month
  ORDER BY month
) TO 'monthly_report.csv' (HEADER);

-- 读 CSV
SELECT * FROM read_csv_auto('data.csv');

-- 读 Parquet
SELECT * FROM read_parquet('data.parquet');

-- 读 JSON
SELECT * FROM read_json_auto('data.json');

-- 读 Excel
SELECT * FROM read_excel('data.xlsx');

-- 读多个文件（自动合并）
SELECT * FROM read_csv_auto('data/sales_*.csv', union_by_name=true);

-- 直接从 URL 读取
SELECT * FROM read_csv_auto('https://example.com/data.csv');

-- 挂载 PostgreSQL
ATTACH 'postgresql://user:pass@host:5432/db' AS pg (TYPE postgres);

-- 直接跨库 JOIN
SELECT d.*, p.order_status
FROM duckdb_table d
JOIN pg.schema.table p ON d.id = p.order_id;

-- 挂载 SQLite
ATTACH 'mobile.db' AS sqlite (TYPE sqlite);

-- 跨库查询
SELECT * FROM main_table 
JOIN sqlite.mobile_data ON main_table.id = sqlite.mobile_data.id;

# Python
pip install duckdb

# CLI
brew install duckdb  # macOS
# 或下载: https://duckdb.org/docs/installers/

# 浏览器版
# https://duckdb.org/docs/api/browser/overview

duckdb

-- 直接在 DuckDB CLI 中运行
SELECT 'Hello DuckDB!' as message;

-- 读取本地文件
SELECT * FROM read_csv_auto('my_data.csv');

-- 创建表并分析
CREATE TABLE sales AS SELECT * FROM read_csv_auto('sales.csv');
SELECT region, SUM(amount) FROM sales GROUP BY region;

import duckdb

# 快速查询
result = duckdb.query("SELECT * FROM read_csv_auto('data.csv') LIMIT 10")
print(result.df())

# 持久化数据库
con = duckdb.connect("analysis.duckdb")
con.execute("CREATE TABLE sales AS SELECT * FROM read_csv_auto('sales.csv')")
con.execute("SELECT region, SUM(amount) FROM sales GROUP BY region")