掌握DuckDB高级SQL：解锁数据分析的超能力_duckdb 支持 with 语法吗-CSDN博客

本文链接：https://blog.csdn.net/neweastsun/article/details/147232093

你是否厌倦了基础SQL的限制？想要像数据专家一样轻松处理复杂分析？本文将带你深入探索DuckDB强大的高级SQL功能！从让数据"开口说话"的窗口函数到简化复杂逻辑的CTE魔法，再到处理层级数据的递归查询绝技 - 每个知识点都配有实战代码示例。无论你是数据分析师、工程师还是数据科学家，这些技巧都将大幅提升你的工作效率和分析能力！

1. 为什么选择DuckDB进行高级数据分析？

DuckDB作为一款轻量级分析型数据库系统，以其卓越的性能和对标准SQL的完整支持而闻名。它特别适合处理复杂的数据分析任务：

完全支持SQL:2011标准的高级功能
内存计算架构带来极速响应
零配置、嵌入式设计简化部署
对大数据集的高效处理能力

-- 检查DuckDB版本和支持的功能
SELECT library_version, sqlite_version();

在这里插入图片描述

2. 窗口函数：数据分析的"放大镜"

2.1 什么是窗口函数？

窗口函数允许你在不改变原始行的情况下对数据集进行"透视"分析。它们就像为你的数据添加了一个智能分析层。

-- 基本窗口函数结构
SELECT 
    列名,
    窗口函数() OVER (
        PARTITION BY 分组列
        ORDER BY 排序列
        [frame_specification]
    ) AS 结果列
FROM 表名;

2.2 实用案例集锦

案例1：销售趋势分析 - 移动平均

-- 计算7天移动平均销售额
SELECT 
    sale_date,
    amount,
    AVG(amount) OVER (
        ORDER BY sale_date 
        RANGE BETWEEN INTERVAL 3 DAYS PRECEDING 
                  AND INTERVAL 3 DAYS FOLLOWING
    ) AS moving_avg_7day
FROM sales
ORDER BY sale_date;

案例2：员工绩效排名

-- 按部门分组计算销售排名
SELECT 
    employee_name,
    department,
    sales_amount,
    RANK() OVER (PARTITION BY department ORDER BY sales_amount DESC) AS dept_rank,
    PERCENT_RANK() OVER (PARTITION BY department ORDER BY sales_amount DESC) AS percentile
FROM employee_performance;

案例3：累积分析

-- 计算年度累积销售额和占比
SELECT 
    month,
    monthly_sales,
    SUM(monthly_sales) OVER (ORDER BY month) AS ytd_sales,
    monthly_sales*1.0/SUM(monthly_sales) OVER () AS pct_of_total
FROM monthly_sales_data;

3. CTE：SQL的"乐高积木"

3.1 CTE的强大之处

公共表表达式(CTE)让你的SQL代码更加模块化和可读：

-- CTE基本语法
WITH cte_name AS (
    SELECT ... FROM ... WHERE ...
)
SELECT * FROM cte_name;

3.2 CTE实战应用

案例1：多步骤数据处理

-- 分步处理电商数据
WITH 
user_orders AS (
    SELECT user_id, COUNT(*) AS order_count 
    FROM orders 
    GROUP BY user_id
),
big_spenders AS (
    SELECT user_id, SUM(amount) AS total_spent 
    FROM orders 
    GROUP BY user_id 
    HAVING SUM(amount) > 1000
)
SELECT 
    u.user_id,
    u.name,
    uo.order_count,
    bs.total_spent
FROM users u
LEFT JOIN user_orders uo ON u.user_id = uo.user_id
LEFT JOIN big_spenders bs ON u.user_id = bs.user_id;

案例2：数据清洗管道

-- 创建数据清洗管道
WITH 
raw_data AS (SELECT * FROM messy_table),
cleaned_data AS (
    SELECT 
        user_id,
        TRIM(name) AS clean_name,
        CASE WHEN email LIKE '%@%' THEN email ELSE NULL END AS valid_email,
        CAST(REGEXP_REPLACE(phone, '[^0-9]', '') AS VARCHAR) AS clean_phone
    FROM raw_data
),
enriched_data AS (
    SELECT 
        *,
        LENGTH(clean_name) AS name_length,
        SPLIT_PART(valid_email, '@', 2) AS email_domain
    FROM cleaned_data
)
SELECT * FROM enriched_data;

4. 递归查询：处理层级数据的利器

4.1 理解递归CTE

-- 递归查询基本结构
WITH RECURSIVE hierarchy AS (
    -- 基础查询(起点)
    SELECT ... FROM ... WHERE ...
    
    UNION [ALL]
    
    -- 递归部分
    SELECT ... FROM ... JOIN hierarchy ON ...
)
SELECT * FROM hierarchy;

4.2 递归查询实战

案例1：组织结构图

-- 查找所有下属员工(包括间接下属)
WITH RECURSIVE org_chart AS (
    -- 基础查询: CEO(没有经理的员工)
    SELECT id, name, title, manager_id, 1 AS level
    FROM employees 
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- 递归查询: 所有下属员工
    SELECT e.id, e.name, e.title, e.manager_id, oc.level + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT 
    REPEAT(' ', (level-1)*4) || name AS hierarchy,
    title,
    level
FROM org_chart
ORDER BY level, name;

案例2：物料清单(BOM)展开

-- 展开产品组件层次结构(包括数量计算)
WITH RECURSIVE product_structure AS (
    -- 基础查询: 顶级产品
    SELECT 
        component_id, 
        component_name,
        parent_id,
        1 AS level,
        quantity,
        quantity AS total_quantity,
        CAST(component_name AS VARCHAR) AS path
    
    FROM bom WHERE parent_id IS NULL
    
    UNION ALL
    
    -- 递归查询: 所有子组件
    SELECT 
        b.component_id,
        b.component_name,
        b.parent_id,
        ps.level + 1,
        b.quantity,
        b.quantity * ps.total_quantity,
        ps.path || ' > ' || b.component_name
    
    FROM bom b
    JOIN product_structure ps ON b.parent_id = ps.component_id
)
SELECT 
    REPEAT(' ', (level-1)*4) || component_name AS component,
    total_quantity,
    level,
    path
FROM product_structure
ORDER BY path;

5. DuckDB的其他高级武器库

5.1 PIVOT/UNPIVOT魔法

-- PIVOT示例: 将行转为列(销售数据透视)
SELECT * FROM (
    SELECT region, product_category, sales_amount 
    FROM regional_sales
) PIVOT (
    SUM(sales_amount) FOR product_category IN ('Electronics', 'Clothing', 'Furniture')
);

-- UNPIVOT示例: 将列转为行(调查问卷数据处理)
WITH survey_data AS (
    SELECT respondent_id, q1, q2, q3 FROM survey_responses
)
SELECT respondent_id, question, answer 
FROM survey_data 
UNPIVOT (answer FOR question IN (q1, q2, q3));

5.2 JSON处理超能力

-- JSON数据提取和转换示例
SELECT 
    order_id,
    JSON_EXTRACT(customer_info, '$.name') AS customer_name,
    JSON_EXTRACT(customer_info, '$.address.city') AS city,
    CAST(JSON_EXTRACT_ARRAY(items) AS VARCHAR[]) AS items_array,
    JSON_EXTRACT_SCALAR(items[1], '$.product') AS first_product_name,
    
FROM orders_with_json;

-- JSON聚合创建嵌套结构
SELECT 
    department_id,
    JSON_GROUP_OBJECT(
        employee_id, 
        JSON_BUILD_OBJECT(
            'name', employee_name,
            'salary', salary,
            'hire_date', hire_date
        )
    ) AS department_staff_json
FROM employees
GROUP BY department_id;

6. DuckDB性能优化秘籍

6.1 EXPLAIN是你的好朋友

-- 分析查询执行计划(关键性能工具)
EXPLAIN ANALYZE 
WITH big_query AS (...复杂查询...)
SELECT * FROM big_query;

-- DuckDB特有的PRAGMA命令获取统计信息
PRAGMA table_info('sales_data');
PRAGMA storage_info('sales_data');

6.2 分区策略示例

-- DuckDB的分区视图技巧(替代物理分区)
CREATE VIEW partitioned_sales AS 
SELECT * FROM sales_data 
WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31';

-- 对于真正的大数据集考虑使用DuckDB的外部扩展分区支持
INSTALL 'httpfs';
LOAD 'httpfs';
CREATE TABLE partitioned_external AS 
FROM read_parquet('s3://bucket/data/year=*/month=*/*.parquet');

7. DuckDB生态系统集成

7.1 Python集成示例

# Python中使用DuckDB的高级SQL功能示例
import duckdb

conn = duckdb.connect()
conn.execute("""
WITH sales_stats AS (
   SELECT product_id, AVG(amount) as avg_sale 
   FROM sales GROUP BY product_id HAVING COUNT(*) > 1000  
)
SELECT p.*, s.avg_sale 
FROM products p JOIN sales_stats s ON p.id = s.product_id""")

results = conn.fetchall()

7.2 R语言集成示例

# R中使用DuckDB的递归CTE处理层级数据
library(DBI)
con <- dbConnect(duckdb::duckdb())

query <- "
WITH RECURSIVE org_tree AS (
   SELECT id, name, manager_id, 1 as depth FROM employees WHERE id = :ceo_id
  
   UNION ALL
  
   SELECT e.id, e.name, e.manager_id, ot.depth + 1 
   FROM employees e JOIN org_tree ot ON e.manager_id = ot.id  
)
SELECT * FROM org_tree ORDER BY depth"

result <- dbGetQuery(con, query, params = list(ceo_id = "E001"))