DuckDB 5 个进阶 SQL 技巧：效能翻倍的完整指南

SELECT * FROM (
    SELECT *, 
           ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) as rn
    FROM sales
) WHERE rn <= 3

SELECT category, product, revenue
FROM sales
QUALIFY ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) <= 3

WITH RECURSIVE org_tree AS (
    -- 锚点：顶层管理者
    SELECT id, name, manager_id, 1 as level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- 递归：下级员工
    SELECT e.id, e.name, e.manager_id, ot.level + 1
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT * FROM org_tree ORDER BY level, name

WITH RECURSIVE approval_chain AS (
    -- 起始审批节点
    SELECT 
        task_id,
        approver_id,
        approver_name,
        status,
        1 as approval_level,
        CAST(approver_name AS VARCHAR) as chain
    FROM approvals
    WHERE task_id = 'ORDER_2024_001'
    
    UNION ALL
    
    -- 递归追踪下一层审批
    SELECT 
        a.task_id,
        a.approver_id,
        a.approver_name,
        a.status,
        ac.approval_level + 1,
        ac.chain || ' → ' || a.approver_name
    FROM approvals a
    JOIN approval_chain ac ON a.prev_approver_id = ac.approver_id
)
SELECT * FROM approval_chain ORDER BY approval_level

EXPLAIN ANALYZE
SELECT 
    DATE_TRUNC('month', order_date) as month,
    category,
    SUM(amount) as revenue
FROM sales
WHERE order_date >= '2025-01-01'
GROUP BY 1, 2
ORDER BY 1, 2

EXPLAIN ANALYZE
SELECT 
    customer_id,
    SUM(amount) as total_spend,
    COUNT(*) as order_count
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY customer_id
HAVING COUNT(*) > 10

-- 创建临时视图（会话内有效）
CREATE TEMP VIEW v_high_value_customers AS
SELECT 
    customer_id,
    SUM(amount) as total_spend,
    COUNT(*) as order_count,
    AVG(amount) as avg_order_value
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 10000;

-- 复用临时视图
SELECT 
    c.customer_name,
    v.total_spend,
    v.order_count
FROM customers c
JOIN v_high_value_customers v ON c.id = v.customer_id
ORDER BY v.total_spend DESC

-- 步骤1：创建临时表存储中间结果
CREATE TEMP TABLE daily_metrics AS
SELECT 
    DATE_TRUNC('day', order_date) as date,
    region,
    COUNT(*) as order_count,
    SUM(amount) as revenue
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY 1, 2;

-- 步骤2：基于临时表做进一步分析
SELECT 
    date,
    region,
    revenue,
    LAG(revenue) OVER (PARTITION BY region ORDER BY date) as prev_day_revenue,
    ROUND((revenue - LAG(revenue) OVER (PARTITION BY region ORDER BY date)) / 
          LAG(revenue) OVER (PARTITION BY region ORDER BY date) * 100, 2) as growth_pct
FROM daily_metrics
ORDER BY date, region;

import duckdb

# 注册自定义聚合函数
def weighted_avg(values, weights):
    """加权平均计算"""
    if not values or not weights:
        return None
    total_weight = sum(weights)
    if total_weight == 0:
        return None
    weighted_sum = sum(v * w for v, w in zip(values, weights))
    return weighted_sum / total_weight

con = duckdb.connect(':memory:')
con.create_function('weighted_avg', weighted_avg, ['DOUBLE', 'DOUBLE'])

# 使用自定义函数
result = con.execute("""
    SELECT weighted_avg(amount, weight) 
    FROM sales
""").fetchone()
print(f"加权平均销售额: {result[0]}")

import duckdb
import re

def extract_domain(email):
    """从邮箱提取域名"""
    if pd.isna(email):
        return None
    return email.split('@')[-1]

con = duckdb.connect(':memory:')
con.create_function('extract_domain', extract_domain, ['VARCHAR'])

# 使用自定义函数
result = con.execute("""
    SELECT extract_domain(email) as domain, COUNT(*) 
    FROM customers 
    GROUP BY domain 
    ORDER BY COUNT(*) DESC
""").fetchall()

import duckdb

class CoefficientOfVariation:
    """自定义聚合：变异系数（标准差/均值）"""
    
    def __init__(self):
        self.sum = 0.0
        self.sum_sq = 0.0
        self.count = 0
    
    def update(self, value):
        self.sum += value
        self.sum_sq += value * value
        self.count += 1
    
    def combine(self, other):
        self.sum += other.sum
        self.sum_sq += other.sum_sq
        self.count += other.count
    
    def finalize(self):
        if self.count < 2:
            return None
        mean = self.sum / self.count
        variance = (self.sum_sq / self.count) - (mean * mean)
        if variance < 0:
            return None
        std_dev = variance ** 0.5
        return std_dev / mean if mean != 0 else None

con = duckdb.connect(':memory:')
con.create_aggregate(CoefficientOfVariation, 'coeff_variation', ['DOUBLE'])

# 使用自定义聚合
result = con.execute("""
    SELECT region, 
           coeff_variation(amount) as cv
    FROM sales
    GROUP BY region
    ORDER BY cv DESC
""").fetchall()

-- 步骤1：创建临时视图组织逻辑
CREATE TEMP VIEW v_customer_metrics AS
SELECT 
    c.customer_id,
    c.customer_name,
    COUNT(o.order_id) as order_count,
    SUM(o.amount) as total_spend,
    AVG(o.amount) as avg_order_value,
    MIN(o.order_date) as first_order_date,
    MAX(o.order_date) as last_order_date
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.customer_id, c.customer_name;

-- 步骤2：使用递归 CTE 计算客户生命周期
WITH RECURSIVE customer_journey AS (
    -- 锚点：首次订单
    SELECT 
        customer_id,
        first_order_date as event_date,
        'first_order' as event_type,
        1 as stage
    FROM v_customer_metrics
    
    UNION ALL
    
    -- 递归：后续阶段（这里简化，实际可扩展）
    SELECT 
        customer_id,
        last_order_date as event_date,
        'last_order' as event_type,
        2 as stage
    FROM v_customer_metrics
)
SELECT * FROM customer_journey ORDER BY customer_id, event_date;

-- 步骤3：使用 QUALIFY 筛选高价值客户 Top 10
SELECT 
    customer_id,
    customer_name,
    total_spend,
    order_count,
    ROUND(total_spend / NULLIF(order_count, 0), 2) as avg_order_value
FROM v_customer_metrics
QUALIFY ROW_NUMBER() OVER (ORDER BY total_spend DESC) <= 10
ORDER BY total_spend DESC;

-- 步骤4：使用 EXPLAIN ANALYZE 验证性能
EXPLAIN ANALYZE
SELECT 
    customer_id,
    customer_name,
    total_spend,
    order_count
FROM v_customer_metrics
WHERE total_spend > 10000
ORDER BY total_spend DESC;