DuckDB 物化视图完全指南：别再反复计算同一份数据了

-- 创建模拟订单表（100 万行）
CREATE TABLE orders AS
SELECT 
    (DATE '2024-01-01' + INTERVAL (random() * 1000) DAY)::DATE AS order_date,
    CASE random() * 5
        WHEN 0 THEN 'electronics'
        WHEN 1 THEN 'clothing'
        WHEN 2 THEN 'food'
        WHEN 3 THEN 'books'
        ELSE 'other'
    END AS category,
    ROUND(random() * 500 + 10, 2) AS amount,
    floor(random() * 10000) + 1 AS user_id
FROM generate_series(1, 1000000);

-- 创建月度销售汇总表
CREATE MATERIALIZED VIEW mv_monthly_sales AS
SELECT 
    date_trunc('month', order_date) AS month,
    category,
    SUM(amount) AS total_sales,
    COUNT(*) AS order_count,
    AVG(amount) AS avg_order_value
FROM orders
GROUP BY 1, 2;

-- 查询秒级返回
SELECT * FROM mv_monthly_sales 
ORDER BY month DESC, total_sales DESC;

-- 每天凌晨刷新一次
REFRESH MATERIALIZED VIEW mv_monthly_sales;

-- 清空并重新计算
TRUNCATE mv_monthly_sales;
INSERT INTO mv_monthly_sales
SELECT 
    date_trunc('month', order_date) AS month,
    category,
    SUM(amount) AS total_sales,
    COUNT(*) AS order_count,
    AVG(amount) AS avg_order_value
FROM orders
GROUP BY 1, 2;

-- 只处理新增数据
INSERT INTO mv_monthly_sales
SELECT 
    date_trunc('month', o.order_date) AS month,
    o.category,
    SUM(o.amount) AS total_sales,
    COUNT(*) AS order_count,
    AVG(o.amount) AS avg_order_value
FROM orders o
WHERE o.order_date > (SELECT MAX(month) FROM mv_monthly_sales)
GROUP BY 1, 2;

-- 第一步：创建基础物化视图
CREATE MATERIALIZED VIEW mv_daily_active AS
SELECT 
    event_date,
    COUNT(DISTINCT user_id) AS active_users,
    COUNT(DISTINCT CASE WHEN is_first_visit THEN user_id END) AS new_users
FROM user_events
GROUP BY event_date;

-- 第二步：创建聚合物化视图
CREATE MATERIALIZED VIEW mv_channel_funnel AS
SELECT 
    channel,
    COUNT(DISTINCT user_id) AS visitors,
    COUNT(DISTINCT CASE WHEN action = 'purchase' THEN user_id END) AS purchasers,
    ROUND(
        COUNT(DISTINCT CASE WHEN action = 'purchase' THEN user_id END) * 100.0 / 
        COUNT(DISTINCT user_id), 2
    ) AS conversion_rate
FROM user_events
GROUP BY channel;

-- 第三步：查询看板
SELECT * FROM mv_daily_active ORDER BY event_date DESC LIMIT 30;
SELECT * FROM mv_channel_funnel ORDER BY conversion_rate DESC;

import duckdb
import schedule
import time

# 连接 DuckDB
conn = duckdb.connect("dashboard.db")

# 创建物化视图
conn.execute("""
    CREATE MATERIALIZED VIEW IF NOT EXISTS mv_sales_summary AS
    SELECT 
        date_trunc('month', order_date) AS month,
        category,
        SUM(amount) AS total_sales
    FROM orders
    GROUP BY 1, 2
""")

# 刷新物化视图
def refresh_views():
    conn.execute("REFRESH MATERIALIZED VIEW mv_sales_summary")
    print(f"{time.strftime('%Y-%m-%d %H:%M')} - 刷新完成")

# 定时任务
schedule.every().day.at("02:00").do(refresh_views)

while True:
    schedule.run_pending()
    time.sleep(60)