DuckDB vs ClickHouse：2026 年终极生产基准测试

-- Daily revenue by category
SELECT 
    DATE_TRUNC('day', o.order_date) as day,
    p.category,
    SUM(o.amount) as revenue,
    COUNT(*) as order_count
FROM orders o
JOIN products p ON o.product_id = p.id
GROUP BY 1, 2
ORDER BY 1 DESC, 3 DESC;

-- Customer lifetime value with event attribution
SELECT 
    c.customer_id,
    c.segment,
    SUM(o.amount) as total_spent,
    COUNT(DISTINCT o.order_id) as order_count,
    COUNT(DISTINCT e.event_id) as touchpoints,
    AVG(e.session_duration) as avg_session
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
LEFT JOIN events e ON c.customer_id = e.customer_id
    AND e.event_date BETWEEN o.order_date - INTERVAL '30 days' AND o.order_date
GROUP BY 1, 2
HAVING total_spent > 1000;

-- Rolling 7-day revenue with ranking
SELECT 
    DATE_TRUNC('day', order_date) as day,
    category,
    SUM(amount) as daily_revenue,
    SUM(SUM(amount)) OVER (
        PARTITION BY category 
        ORDER BY day 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as rolling_7d_revenue,
    RANK() OVER (
        PARTITION BY DATE_TRUNC('month', order_date) 
        ORDER BY SUM(amount) DESC
    ) as monthly_rank
FROM orders
JOIN products ON orders.product_id = products.id
GROUP BY 1, 2;

-- Search product descriptions with relevance scoring
SELECT 
    p.id,
    p.name,
    p.category,
    p.price,
    ts_rank(
        to_tsvector('english', p.description),
        plainto_tsquery('wireless bluetooth headphones'),
        4
    ) as relevance
FROM products p
WHERE to_tsvector('english', p.description) @@ 
      plainto_tsquery('wireless bluetooth headphones')
ORDER BY relevance DESC
LIMIT 100;

-- Hourly transaction patterns with anomaly detection
SELECT 
    DATE_TRUNC('hour', order_date) as hour,
    COUNT(*) as transactions,
    SUM(amount) as revenue,
    AVG(amount) as avg_order_value,
    STDDEV(amount) OVER (
        ORDER BY hour 
        ROWS BETWEEN 167 PRECEDING AND CURRENT ROW
    ) as volatility
FROM orders
WHERE order_date >= NOW() - INTERVAL '365 days'
GROUP BY 1
HAVING volatility > 2 * (
    SELECT AVG(volatility) FROM (
        SELECT 
            STDDEV(amount) OVER (
                ORDER BY hour 
                ROWS BETWEEN 167 PRECEDING AND CURRENT ROW
            ) as volatility
        FROM orders
        WHERE order_date >= NOW() - INTERVAL '365 days'
        GROUP BY DATE_TRUNC('hour', order_date)
    ) sub
);

-- Extract nested JSON fields from event logs
SELECT 
    JSON_EXTRACT_STRING(event_data, '$.user.country') as country,
    JSON_EXTRACT_STRING(event_data, '$.device.type') as device_type,
    JSON_EXTRACT_FLOAT(event_data, '$.session.duration') as duration,
    COUNT(*) as event_count,
    AVG(JSON_EXTRACT_FLOAT(event_data, '$.session.duration')) as avg_duration
FROM events
WHERE event_data IS NOT NULL
GROUP BY 1, 2
ORDER BY 3 DESC;

-- Simple linear regression using DuckDB's ML extension
SELECT 
    *,
    linear_reg(
        ARRAY[price, rating, review_count],
        revenue
    ) OVER (PARTITION BY category) as prediction
FROM products
WHERE price > 0 AND rating > 0;

-- Find stores within 50km radius of coordinates
SELECT 
    store_id,
    store_name,
    ST_Distance(
        ST_MakePoint(longitude, latitude)::geography,
        ST_MakePoint(-73.9857, 40.7484)::geography
    ) as distance_km
FROM stores
WHERE ST_DWithin(
    ST_MakePoint(longitude, latitude)::geography,
    ST_MakePoint(-73.9857, 40.7484)::geography,
    50000
)
ORDER BY distance_km ASC;

-- Export aggregated data to Parquet with transformation
COPY (
    SELECT 
        DATE_TRUNC('month', order_date) as month,
        category,
        SUM(amount) as total_revenue,
        COUNT(DISTINCT customer_id) as unique_customers,
        AVG(amount) as avg_order_value
    FROM orders
    JOIN products ON orders.product_id = products.id
    GROUP BY 1, 2
) TO '/tmp/monthly_report.parquet' (FORMAT PARQUET);