Article

列式数据库DuckDB

更新于：2026-07-16

第一章：DuckDB 概述与安装

1.1 什么是 DuckDB

概念名称	说明	注意事项
DuckDB	一个开源的、嵌入式的、列式存储的关系型数据库管理系统（RDBMS），专为 OLAP（在线分析处理）场景设计。	不适用于高并发 OLTP 场景；无独立服务器进程。
嵌入式数据库	数据库引擎直接集成在应用程序进程中，无需单独部署数据库服务。	类似 SQLite，但面向分析而非事务。
列式存储	数据按列而非按行存储，有利于向量化计算和减少 I/O，提升分析查询性能。	对点查（如 WHERE id = ?）效率较低。
进程内分析引擎	查询在调用程序的同一内存空间中执行，避免网络开销和序列化成本。	适合单机数据分析，不适合分布式部署。

1.2 DuckDB 的核心特性

特性名称	说明	注意事项
高性能 OLAP 引擎	支持向量化执行、SIMD 优化、并行查询，对聚合、连接等分析操作高度优化。	性能优势在大数据量（百万行以上）时更明显。
标准 SQL 支持	兼容 PostgreSQL 风格的 SQL 语法，支持窗口函数、CTE、子查询等高级特性。	不完全兼容所有 PostgreSQL 扩展语法。
零配置	无需初始化、无需守护进程，打开即用。	适合脚本、Notebook、临时分析任务。
多格式数据源支持	原生支持从 CSV、Parquet、JSON 等文件直接查询，无需先导入建表。	需启用对应扩展（如 parquet、httpfs）。
内存与磁盘混合模式	可在内存中运行（:memory:），也可持久化到本地文件。	默认为内存模式；持久化需指定文件路径。
跨语言支持	官方提供 C/C++、Python、R、Java、Node.js、WASM 等绑定。	Python 绑定最成熟，社区生态最丰富。
ACID 事务支持	支持原子性、一致性、隔离性、持久性，但仅限于单线程写入。	多线程写入可能引发锁竞争或失败。

1.3 安装与环境配置（Python / CLI / 其他语言）

Python 安装与使用

方法名称	语法	用途	代码示例	注意事项
pip 安装 duckdb	pip install duckdb	安装基础版 DuckDB（不含扩展）	`pip install duckdb`	推荐使用虚拟环境。
pip 安装带扩展版本	pip install duckdb[parquet,httpfs]	安装支持 Parquet 和 HTTP 的版本	`pip install duckdb[parquet,httpfs]`	方括号为可选依赖，部分 shell 需加引号。
导入并连接内存数据库	import duckdb con = duckdb.connect()	创建内存数据库连接	见下方代码示例 1	默认为内存模式，退出后数据丢失。
连接持久化数据库文件	duckdb.connect(‘my.db’)	创建或打开磁盘上的数据库文件	见下方代码示例 2	文件路径可为相对或绝对路径。

代码示例 1：导入并连接内存数据库

import duckdb
con = duckdb.connect()
print(con.execute("SELECT 1").fetchone())

代码示例 2：连接持久化数据库文件

con = duckdb.connect('analytics.db')
con.execute("CREATE TABLE t(x INT)")

CLI（命令行工具）安装与使用

操作步骤名称	操作细节	注意事项
下载 CLI 可执行文件	从 https://duckdb.org/download 获取对应操作系统的二进制文件（如 duckdb 或 duckdb.exe）	Linux/macOS 需 chmod +x 赋予执行权限。
启动内存数据库	在终端执行：./duckdb	进入交互式 SQL shell，提示符为 “v0.x.x”。
启动持久化数据库	./duckdb mydata.db	若文件不存在则自动创建。
执行 SQL 脚本文件	./duckdb mydata.db < script.sql	适用于批处理任务。
启用扩展（如 Parquet）	在 CLI 中执行：INSTALL parquet; LOAD parquet;	首次使用需 INSTALL，之后只需 LOAD。

其他语言绑定简要

语言	安装方式	连接示例	注意事项
R	install.packages(“duckdb”)	con <- dbConnect(duckdb::duckdb())	需加载 DBI 包。
Node.js	npm install duckdb	const db = require(‘duckdb’); new db.Database(‘:memory:‘)	支持有限，适合简单查询。
Java	Maven 引入 org.duckdb:duckdb_jdbc	DriverManager.getConnection(“jdbc:duckdb:“)	使用 JDBC 接口。
WASM	通过 CDN 加载 duckdb-wasm	await duckdb.InstantiateWorkerAsync()	适用于浏览器端数据分析。

第二章：基础操作

2.1 启动与连接数据库

方法名称	语法	用途	代码示例	注意事项
连接内存数据库（Python）	duckdb.connect()	创建临时内存数据库连接	见下方代码示例 1	数据在进程退出后丢失；适合临时分析。
连接持久化数据库文件（Python）	duckdb.connect(‘path/to/file.db’)	打开或创建磁盘上的 DuckDB 文件	见下方代码示例 2	路径可为相对或绝对；自动创建父目录（部分版本需手动）。
使用上下文管理器（Python）	with duckdb.connect(…) as con: …	自动关闭连接，避免资源泄漏	见下方代码示例 3	推荐用于脚本开发。
CLI 启动内存模式	./duckdb	启动交互式内存数据库 shell	`$ ./duckdb`	无需参数，默认内存模式。
CLI 启动文件模式	./duckdb mydata.db	启动并绑定到指定数据库文件	`$ ./duckdb analytics.db`	若文件不存在则新建。
多连接共享同一文件（Python）	多个 duckdb.connect(‘file.db’) 实例	允许多个连接读写同一数据库文件	见下方代码示例 4	写操作是互斥的；不支持高并发写入。

代码示例 1：连接内存数据库

import duckdb
con = duckdb.connect()
print(con.execute("SELECT version()").fetchone())

代码示例 2：连接持久化数据库文件

con = duckdb.connect('sales.db')
con.execute("CREATE TABLE IF NOT EXISTS logs(ts TIMESTAMP)")

代码示例 3：使用上下文管理器

with duckdb.connect() as con:
    result = con.execute("SELECT 42").fetchone()

代码示例 4：CLI 启动及多连接共享

# CLI 启动内存模式
$ ./duckdb
v0.10.0 1234567890abcdef
DuckDB> SELECT 1;

# CLI 启动文件模式
$ ./duckdb analytics.db
DuckDB> CREATE TABLE t(x INT);

# 多连接共享同一文件
con1 = duckdb.connect('shared.db')
con2 = duckdb.connect('shared.db')

2.2 执行 SQL 查询

方法名称	语法	用途	代码示例	注意事项
execute() + fetchone()	con.execute(sql).fetchone()	获取单行结果（元组）	`row = con.execute("SELECT 1 AS a, 'hello' AS b").fetchone() // (1, 'hello')`	适用于标量查询或 LIMIT 1。
execute() + fetchall()	con.execute(sql).fetchall()	获取全部结果（元组列表）	`rows = con.execute("SELECT * FROM generate_series(1,3)").fetchall() // [(1,), (2,), (3,)]`	结果集大时可能占用大量内存。
execute() + df()	con.execute(sql).df()	将结果转为 Pandas DataFrame	`df = con.execute("SELECT x FROM range(5) t(x)").df()`	需已安装 pandas；列名自动推断。
execute() + arrow()	con.execute(sql).arrow()	将结果转为 PyArrow Table	`table = con.execute("SELECT 1").arrow()`	需安装 pyarrow；适合高效数据交换。
直接调用 SQL 函数（CLI）	在 DuckDB CLI 中直接输入 SQL	交互式执行查询	`DuckDB> SELECT current_date;`	支持多行输入（以分号结束）。
参数化查询（Python）	con.execute(sql, parameters)	防止 SQL 注入，安全传参	`con.execute("SELECT ? + ?", [10, 20]).fetchone()`	参数使用 ? 占位符；不支持命名参数。

2.3 创建与管理表

方法名称	语法	用途	代码示例	注意事项
CREATE TABLE	CREATE TABLE table_name (col1 TYPE, col2 TYPE, …)	显式定义表结构	`con.execute("CREATE TABLE users(id INTEGER, name VARCHAR)")`	支持标准 SQL 类型（INTEGER, VARCHAR, TIMESTAMP 等）。
CREATE TABLE AS	CREATE TABLE new_table AS SELECT …	通过查询结果创建新表	`con.execute("CREATE TABLE summary AS SELECT COUNT(*) FROM logs")`	表结构由 SELECT 推断；数据会被物化存储。
DROP TABLE	DROP TABLE [IF EXISTS] table_name	删除表	`con.execute("DROP TABLE IF EXISTS temp_data")`	IF EXISTS 可避免报错。
DESCRIBE / SHOW COLUMNS	DESCRIBE table_name 或 SHOW COLUMNS FROM table_name	查看表结构	`con.execute("DESCRIBE users").fetchall()`	返回列名、类型、是否为空等信息。
ALTER TABLE ADD COLUMN	ALTER TABLE table_name ADD COLUMN col_name TYPE	添加新列	`con.execute("ALTER TABLE users ADD COLUMN email VARCHAR")`	不支持删除列或修改列类型（截至 v0.10）。
查看所有表	SHOW TABLES	列出当前数据库中所有用户表	`con.execute("SHOW TABLES").fetchall()`	不包含系统表或视图（除非显式创建）。
创建视图	CREATE VIEW view_name AS SELECT …	创建虚拟表（逻辑视图）	`con.execute("CREATE VIEW active_users AS SELECT * FROM users WHERE status = 'active'")`	视图不存储数据，每次查询实时计算。

2.4 插入、更新与删除数据

方法名称	语法	用途	代码示例	注意事项
INSERT INTO … VALUES	INSERT INTO table VALUES (val1, val2, …)	插入单行或多行常量数据	`con.execute("INSERT INTO users VALUES (1, 'Alice'), (2, 'Bob')")`	多行用逗号分隔；值数量需匹配列数。
INSERT INTO … SELECT	INSERT INTO table SELECT … FROM …	从查询结果插入数据	`con.execute("INSERT INTO backup SELECT * FROM users WHERE created > '2024-01-01'")`	常用于 ETL 或备份场景。
UPDATE	UPDATE table SET col = expr WHERE condition	修改满足条件的行	`con.execute("UPDATE users SET name = 'Charlie' WHERE id = 1")`	若省略 WHERE，则更新全表（谨慎使用）。
DELETE FROM	DELETE FROM table WHERE condition	删除满足条件的行	`con.execute("DELETE FROM logs WHERE ts < '2023-01-01'")`	若省略 WHERE，则清空整表。
TRUNCATE TABLE	TRUNCATE TABLE table_name	快速清空表（保留结构）	`con.execute("TRUNCATE TABLE temp_cache")`	比 DELETE FROM 更高效；不可回滚（但 DuckDB 支持事务回滚）。
使用参数化插入（Python）	con.execute(“INSERT INTO t VALUES (?, ?)”, [a, b])	安全批量插入	`con.execute("INSERT INTO events VALUES (?, ?)", [123, "click"])`	可结合 executemany 批量处理（见下条）。
executemany（Python）	con.executemany(“INSERT INTO t VALUES (?, ?)”, data)	批量插入多组参数	见下方代码示例	性能优于循环 execute；自动提交事务。

代码示例：executemany 批量插入

data = [(1, 'Alice'), (2, 'Bob'), (3, 'Charlie')]
con.executemany("INSERT INTO t VALUES (?, ?)", data)

第三章：数据导入与导出

3.1 从 CSV 文件导入数据

方法名称	语法	用途	代码示例	注意事项
直接查询 CSV（无需建表）	SELECT * FROM ‘file.csv’	临时查询 CSV 内容	`con.execute("SELECT COUNT(*) FROM 'sales.csv'").fetchone()`	自动推断列名和类型；首行为标题。
显式指定 CSV 选项	SELECT * FROM read_csv(‘file.csv’, header=true, delim=’,’, columns={‘col’: ‘INT’})	控制解析行为	`con.execute("SELECT * FROM read_csv('data.csv', columns={'id': 'INTEGER', 'name': 'VARCHAR'})")`	支持 delim（分隔符）、quote、escape、nullstr 等参数。
导入 CSV 到新表	CREATE TABLE t AS SELECT * FROM ‘file.csv’	将 CSV 数据持久化为表	`con.execute("CREATE TABLE orders AS SELECT * FROM 'orders.csv'")`	表结构由 CSV 推断；后续可对表执行 DML。
使用 COPY FROM（兼容模式）	COPY table_name FROM ‘file.csv’ (FORMAT CSV, HEADER)	类似 PostgreSQL 的导入方式	见下方代码示例	需先创建目标表；性能略低于直接 SELECT。
从压缩 CSV 导入	SELECT * FROM ‘file.csv.gz’	支持 gzip 压缩文件	`con.execute("SELECT * FROM 'large_data.csv.gz' LIMIT 5")`	自动识别 .gz 后缀；也支持 .bz2（需启用扩展）。

代码示例：COPY FROM 导入

con.execute("CREATE TABLE logs(ts TIMESTAMP, msg VARCHAR)")
con.execute("COPY logs FROM 'app.log' (FORMAT CSV, HEADER)")

3.2 从 Parquet 文件导入数据

方法名称	语法	用途	代码示例	注意事项
直接查询 Parquet 文件	SELECT * FROM ‘file.parquet’	查询 Parquet 数据（列式高效读取）	`con.execute("SELECT user_id, SUM(price) FROM 'events.parquet' GROUP BY user_id").df()`	自动利用列裁剪和谓词下推优化。
查询 Parquet 目录（分区）	SELECT * FROM ‘path/to/partitioned/‘	读取 Hive 分区格式的 Parquet 目录	`con.execute("SELECT * FROM 's3://bucket/data/year=2024/month=01/'")`	需启用 httpfs 扩展才能访问 S3。
指定列过滤（投影下推）	SELECT col1, col2 FROM ‘file.parquet’	仅读取所需列，提升 I/O 效率	`con.execute("SELECT id, status FROM 'users.parquet' WHERE created > '2024-01-01'")`	DuckDB 自动跳过未使用的列。
导入 Parquet 到表	CREATE TABLE t AS SELECT * FROM ‘file.parquet’	将 Parquet 数据转存为本地表	`con.execute("CREATE TABLE backup AS SELECT * FROM 'archive.parquet'")`	适用于需要频繁查询或更新的场景。
启用 Parquet 扩展（CLI）	INSTALL parquet; LOAD parquet;	在 CLI 中启用 Parquet 支持	见下方代码示例	Python 包默认包含 parquet 支持，CLI 需手动加载。

代码示例：CLI 启用 Parquet 扩展

DuckDB> INSTALL parquet;
DuckDB> LOAD parquet;
DuckDB> SELECT * FROM 'data.parquet';

3.3 导出查询结果到文件（CSV / Parquet / JSON）

方法名称	语法	用途	代码示例	注意事项
导出为 CSV	COPY (SELECT …) TO ‘output.csv’ (HEADER, DELIMITER ’,‘)	将查询结果保存为 CSV 文件	`con.execute("COPY (SELECT * FROM users) TO 'export.csv' (HEADER 1, DELIMITER ',')")`	HEADER 1 表示写入列名；默认不写。
导出为 Parquet	COPY (SELECT …) TO ‘output.parquet’ (FORMAT PARQUET)	高效列式存储导出	`con.execute("COPY (SELECT * FROM events WHERE ts > '2024-01-01') TO 'recent.parquet' (FORMAT PARQUET)")`	自动压缩；适合大数据分析交换。
导出为 JSON（每行一个对象）	COPY (SELECT …) TO ‘output.json’ (FORMAT JSON, ARRAY false)	导出为 JSON Lines 格式	`con.execute("COPY (SELECT id, name FROM users) TO 'users.json' (FORMAT JSON, ARRAY false)")`	ARRAY true 会输出整个数组（单个 JSON 对象）。
导出到标准输出（CLI）	COPY (SELECT …) TO STDOUT (FORMAT CSV, HEADER)	在终端直接打印结果	`$ ./duckdb my.db "COPY (SELECT * FROM t) TO STDOUT (FORMAT CSV, HEADER)"`	适用于管道处理（如 `
控制导出选项	支持 NULLSTR, QUOTE, ESCAPE, COMPRESSION 等	定制导出格式	`con.execute("COPY t TO 'out.csv' (NULL 'NULL', QUOTE '', ESCAPE '')")`	COMPRESSION 可设为 GZIP（仅 Parquet/CSV）。

3.4 与 Pandas DataFrame 互操作

方法名称	语法	用途	代码示例	注意事项
从 DataFrame 创建表	con.register(‘df_name’, df) 或 con.sql(”… FROM df_name”)	将 Pandas DataFrame 注册为虚拟表	见下方代码示例 1	register 是轻量引用，不复制数据（零拷贝）。
直接查询 DataFrame（快捷方式）	con.sql(“SELECT … FROM df”)	无需 register，直接在 SQL 中使用变量	`result = con.sql("SELECT x FROM df WHERE y = 'a'").df()`	要求变量名与 SQL 中一致；仅限顶层作用域。
将查询结果转为 DataFrame	con.execute(“SELECT …“).df()	获取 Pandas DataFrame 结果	`df_out = con.execute("SELECT AVG(value) FROM generate_series(1,1000) t(value)").df()`	自动映射 DuckDB 类型到 Pandas 类型（如 TIMESTAMP → datetime64）。
从 DataFrame 插入到表	con.insert_into(‘table_name’, df)	批量插入 DataFrame 数据到现有表	`con.execute("CREATE TABLE t(x INT, y VARCHAR)"); con.insert_into('t', df)`	列名和顺序必须匹配；自动转换类型。
替代表内容（replace）	con.replace_table(‘table_name’, df)	用 DataFrame 完全替换表	`con.replace_table('cache', new_df)`	若表不存在则创建；存在则清空并插入。
与 Polars 兼容（间接）	通过 Arrow 中转：df.to_arrow(); con.from_arrow(…)	支持 Polars DataFrame（需 PyArrow）	见下方代码示例 2	DuckDB 原生不支持 Polars，但可通过 Arrow 交换。

代码示例 1：从 DataFrame 创建表

import pandas as pd
df = pd.DataFrame({'x': [1, 2], 'y': ['a', 'b']})
con.register('mydf', df)
con.execute("SELECT * FROM mydf").df()

代码示例 2：与 Polars 兼容（通过 Arrow 中转）

import polars as pl
lf = pl.DataFrame({'a': [1, 2]})
tbl = lf.to_arrow()
con.from_arrow(tbl, 'polars_table')

第四章：高级 SQL 功能

4.1 窗口函数

方法名称	语法	用途	代码示例	注意事项
ROW_NUMBER()	ROW_NUMBER() OVER (PARTITION BY … ORDER BY …)	为每行分配唯一序号	`con.execute("SELECT id, sale, ROW_NUMBER() OVER (ORDER BY sale DESC) AS rank FROM sales").df()`	不可重复；即使值相同序号也不同。
RANK()	RANK() OVER (ORDER BY col)	跳跃排名（相同值同排名，后续跳过）	`con.execute("SELECT name, score, RANK() OVER (ORDER BY score DESC) FROM students").fetchall()`	如两个第一，则下一个是第三名。
DENSE_RANK()	DENSE_RANK() OVER (ORDER BY col)	密集排名（相同值同排名，后续连续）	`con.execute("SELECT product, price, DENSE_RANK() OVER (ORDER BY price) FROM items").df()`	排名无跳跃。
LAG() / LEAD()	LAG(col, offset, default) OVER (ORDER BY …)	获取前/后第 N 行的值	`con.execute("SELECT date, revenue, LAG(revenue,1) OVER (ORDER BY date) AS prev_rev FROM daily").df()`	offset 默认为 1；default 可省略（默认 NULL）。
FIRST_VALUE()	FIRST_VALUE(col) OVER (ORDER BY …)	获取窗口内第一个值	`con.execute("SELECT month, sales, FIRST_VALUE(sales) OVER (ORDER BY month) AS base FROM monthly").df()`	常用于同比/环比基准。
SUM() 累计	SUM(col) OVER (ORDER BY time ROWS UNBOUNDED PRECEDING)	计算累计和	`con.execute("SELECT day, income, SUM(income) OVER (ORDER BY day) AS cumsum FROM records").df()`	必须指定 ORDER BY；默认为 RANGE，建议用 ROWS 明确。
NTILE(n)	NTILE(4) OVER (ORDER BY score)	将结果分为 n 个桶（分位数）	`con.execute("SELECT student, score, NTILE(4) OVER (ORDER BY score DESC) AS quartile FROM exam").fetchall()`	桶大小尽可能均等。

4.2 CTE（公共表表达式）

方法名称	语法	用途	代码示例	注意事项
单层 CTE	WITH cte_name AS (SELECT …) SELECT * FROM cte_name	提高查询可读性	`con.execute("""WITH top_users AS (SELECT user_id, SUM(amount) AS total FROM orders GROUP BY user_id ORDER BY total DESC LIMIT 10) SELECT * FROM top_users""").df()`	CTE 仅在当前语句有效；不可跨查询复用。
多层 CTE	WITH a AS (…), b AS (SELECT … FROM a) SELECT … FROM b	构建多阶段数据处理流程	见下方代码示例	各 CTE 可引用前面定义的 CTE。
递归 CTE	WITH RECURSIVE t(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM t WHERE n < 10) SELECT * FROM t	生成序列或遍历树形结构	`con.execute("WITH RECURSIVE series(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM series WHERE x < 5) SELECT * FROM series").fetchall()`	必须包含终止条件，否则无限循环。
CTE 与 INSERT 结合	WITH cte AS (…) INSERT INTO target SELECT * FROM cte	将中间结果插入表	`con.execute("WITH new_data AS (SELECT generate_series(1,100) AS id) INSERT INTO temp_ids SELECT * FROM new_data")`	支持与 UPDATE/DELETE 结合（需子查询支持）。

代码示例：多层 CTE

con.execute("""
    WITH raw AS (
        SELECT * FROM logs WHERE status = 'error'
    ),
    hourly AS (
        SELECT strftime('%Y-%m-%d %H', ts) AS h, COUNT(*) AS cnt
        FROM raw
        GROUP BY h
    )
    SELECT * FROM hourly
""").df()

4.3 子查询与关联查询

方法名称	语法	用途	代码示例	注意事项
标量子查询	SELECT (SELECT MAX(price) FROM products) AS max_price	返回单值的子查询	`con.execute("SELECT department, (SELECT AVG(salary) FROM emp e2 WHERE e2.dept = e1.dept) AS avg_sal FROM emp e1").df()`	必须返回单行单列，否则报错。
IN 子查询	SELECT * FROM t1 WHERE id IN (SELECT id FROM t2 WHERE cond)	过滤匹配子查询结果的行	`con.execute("SELECT name FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 100)").fetchall()`	可被 EXISTS 替代以提升性能。
EXISTS 子查询	SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.id = t1.id)	检查是否存在关联记录	`con.execute("SELECT name FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.cust_id = c.id)").df()`	通常比 IN 更高效，尤其当子查询结果大时。
相关子查询	SELECT * FROM t1 WHERE col > (SELECT AVG(col) FROM t2 WHERE t2.key = t1.key)	子查询引用外层表字段	`con.execute("SELECT employee, salary FROM emp e1 WHERE salary > (SELECT AVG(salary) FROM emp e2 WHERE e2.dept = e1.dept)").df()`	性能较低，因每行都执行子查询。
JOIN 替代子查询	SELECT t1.* FROM t1 JOIN (SELECT … FROM t2) t2 ON …	用连接替代复杂子查询	`con.execute("SELECT u.name, o.total FROM users u JOIN (SELECT user_id, SUM(amount) AS total FROM orders GROUP BY user_id) o ON u.id = o.user_id").df()`	通常更高效且可读性更好。
LEFT JOIN + IS NULL	SELECT t1.* FROM t1 LEFT JOIN t2 ON … WHERE t2.id IS NULL	查找”不存在”关联的记录（反连接）	`con.execute("SELECT name FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE o.user_id IS NULL").fetchall()`	实现 NOT EXISTS 语义。

4.4 聚合与分组优化

方法名称	语法	用途	代码示例	注意事项
GROUP BY 多列	SELECT col1, col2, COUNT(*) FROM t GROUP BY col1, col2	按多个维度聚合	`con.execute("SELECT region, product, SUM(sales) FROM transactions GROUP BY region, product").df()`	列顺序影响结果排序（非保证）。
HAVING 过滤分组	SELECT dept, AVG(sal) FROM emp GROUP BY dept HAVING AVG(sal) > 5000	对聚合结果进行条件过滤	`con.execute("SELECT category, COUNT() FROM items GROUP BY category HAVING COUNT() > 10").fetchall()`	不能使用 SELECT 中的别名（如 HAVING cnt > 10 错误）。
DISTINCT 聚合	SELECT COUNT(DISTINCT user_id) FROM events	去重计数	`con.execute("SELECT COUNT(DISTINCT session_id) FROM logs").fetchone()`	支持任意表达式：COUNT(DISTINCT expr)。
聚合函数组合	SELECT MIN(x), MAX(x), AVG(x), STDDEV_POP(x) FROM t	同时计算多种统计量	`con.execute("SELECT MIN(temp), MAX(temp), AVG(temp) FROM weather").df()`	STDDEV_POP / STDDEV_SAMP 分别对应总体/样本标准差。
使用 FILTER 子句	SELECT COUNT(*) FILTER (WHERE status = ‘success’) FROM jobs	条件聚合（无需 CASE WHEN）	见下方代码示例	更简洁高效；等价于 SUM(CASE WHEN … THEN 1 ELSE 0 END)。
GROUPING SETS	SELECT a, b, SUM(c) FROM t GROUP BY GROUPING SETS ((a), (b), ())	多维汇总（类似 ROLLUP/CUBE）	`con.execute("SELECT region, product, SUM(sales) FROM sales GROUP BY GROUPING SETS ((region), (product), ())").fetchall()`	生成多个分组级别的结果；空值表示该维度未参与分组。
自动向量化聚合	无需特殊语法，DuckDB 自动优化	利用列式存储加速聚合	所有上述聚合在 DuckDB 中自动使用向量化执行引擎	数据量越大，性能优势越明显；避免在 GROUP BY 中使用复杂表达式。

代码示例：FILTER 子句条件聚合

con.execute("""
    SELECT
        COUNT(*) FILTER (WHERE type = 'A') AS count_a,
        COUNT(*) FILTER (WHERE type = 'B') AS count_b
    FROM events
""").df()

第五章：性能调优与执行计划

5.1 查看查询执行计划（EXPLAIN）

方法名称	语法	用途	代码示例	注意事项
EXPLAIN（逻辑计划）	EXPLAIN SELECT …	显示优化后的逻辑执行计划	`con.execute("EXPLAIN SELECT user_id, COUNT(*) FROM events GROUP BY user_id").fetchall()`	输出为树状结构，包含 Projection、Aggregate、Filter 等算子。
EXPLAIN ANALYZE（物理+耗时）	EXPLAIN ANALYZE SELECT …	显示实际执行的物理计划及各阶段耗时	`con.execute("EXPLAIN ANALYZE SELECT * FROM large_table WHERE x > 1000").fetchall()`	包含运行时间（ms）、处理行数、内存使用等；会真实执行查询。
输出格式（JSON）	EXPLAIN (FORMAT JSON) SELECT …	以结构化 JSON 格式输出计划	`con.execute("EXPLAIN (FORMAT JSON) SELECT COUNT(*) FROM t").fetchone()[0]`	便于程序解析；Python 中返回字符串需 json.loads()。
在 CLI 中查看	直接输入 EXPLAIN …	交互式查看执行计划	`DuckDB> EXPLAIN SELECT AVG(value) FROM generate_series(1,1000000);`	CLI 默认以表格形式美化输出。
分析谓词下推效果	对比带/不带过滤条件的 EXPLAIN	验证是否下推到扫描阶段	`con.execute("EXPLAIN SELECT * FROM 'data.parquet' WHERE id = 42")`	若 Filter 出现在 TableScan 节点内，说明已下推。

5.2 使用索引（如有）与分区策略

方法名称	语法	用途	代码示例	注意事项
创建索引（实验性）	CREATE INDEX idx_name ON table(col)	为列创建 B-Tree 索引（v0.8+ 实验支持）	`con.execute("CREATE INDEX idx_user ON orders(user_id)")`	截至 v0.10，DuckDB 默认不使用索引；主要用于未来兼容或特定场景。
检查索引是否生效	EXPLAIN SELECT … WHERE indexed_col = ?	观察执行计划是否使用 IndexScan	`con.execute("EXPLAIN SELECT * FROM orders WHERE user_id = 123")`	大多数情况下仍使用全表扫描 + 向量化过滤，因列存对 OLAP 更高效。
利用 Parquet 分区剪枝	查询 Hive 分区目录（如 /year=2024/month=01/）	自动跳过无关分区	`con.execute("SELECT COUNT(*) FROM 's3://bucket/logs/year=2024/month=02/'")`	需启用 httpfs 扩展；路径必须符合分区命名规范。
手动分区（应用层）	将大表按时间/类别拆分为多个文件	减少单次 I/O 量	见下方代码示例	适用于超大规模数据；DuckDB 本身不提供自动表分区功能。
使用物化视图缓存结果	CREATE TABLE mv AS SELECT … （定期刷新）	预计算聚合结果	`con.execute("CREATE TABLE daily_summary AS SELECT date, SUM(amount) FROM transactions GROUP BY date")`	非自动刷新；需应用层维护一致性。

代码示例：手动分区

# 应用逻辑：
for month in ['01', '02']:
    df = con.execute(f"SELECT * FROM 'sales_2024_{month}.parquet' WHERE region='EU'").df()

重要说明： DuckDB 是列式 OLAP 引擎，通常不需要传统行存数据库的索引。其性能优势来自向量化扫描、谓词下推、列裁剪等技术。仅在极少数点查（point lookup）场景下可尝试索引，但收益有限。

5.3 内存与并行配置

方法名称	语法	用途	代码示例	注意事项
设置内存限制	SET memory_limit=‘2GB’	限制单个查询最大内存使用	见下方代码示例 1	超出后可能溢出到磁盘或报错；默认无硬限制（受系统内存约束）。
设置并行线程数	SET threads TO 4	控制并行执行线程数	`con.execute("SET threads TO 8")`	默认为 CPU 核心数；过高可能导致上下文切换开销。
查看当前配置	SELECT * FROM duckdb_settings()	列出所有运行时配置参数	`con.execute("SELECT name, value FROM duckdb_settings() WHERE name LIKE '%thread%'").fetchall()`	包括 memory_limit、threads、temp_directory 等。
配置临时目录	SET temp_directory=‘/tmp/duckdb’	指定磁盘溢出（spill）的临时路径	`con.execute("SET temp_directory='/fast_ssd/duckdb_tmp'")`	建议使用高速 SSD；避免系统临时目录空间不足。
禁用并行（调试用）	SET threads TO 1	强制单线程执行	见下方代码示例 2	用于性能对比或确定性调试。
动态调整（Python 连接级）	con.execute(“SET …”)	配置仅对当前连接生效	见下方代码示例 3	不影响其他连接；适合多租户隔离场景。

代码示例 1：设置内存限制

con.execute("SET memory_limit='4GB'")
con.execute("SELECT * FROM huge_table ORDER BY x")

代码示例 2：禁用并行

con.execute("SET threads TO 1")
con.execute("EXPLAIN ANALYZE SELECT ...")

代码示例 3：动态调整（Python 连接级）

with duckdb.connect() as con:
    con.execute("SET memory_limit='1GB'")
    con.execute("...")

5.4 向量化执行原理简介

概念名称	说明	注意事项
向量化执行（Vectorized Execution）	查询引擎以”批”（batch，通常 1024 行）为单位处理数据，而非逐行处理。	显著减少函数调用开销，提升 CPU 缓存命中率和 SIMD 利用率。
列式存储（Columnar Storage）	数据按列连续存储，使聚合、过滤等操作只需读取相关列，减少 I/O。	对宽表（列多）分析特别高效；不适合频繁更新单行。
谓词下推（Predicate Pushdown）	将 WHERE 条件尽可能下推到数据扫描阶段，提前过滤无效数据。	在 Parquet/CSV 查询中自动生效；减少后续算子处理量。
迟绑定（Late Materialization）	仅在最终输出阶段才组合所需列，中间过程只传递行 ID 或位图。	减少中间数据搬运；DuckDB 在复杂查询中自动应用。
SIMD 优化	利用 CPU 的单指令多数据（SIMD）指令并行处理多个数据元素（如 8 个 INT32）。	在整数/浮点运算、比较、过滤中自动启用；无需用户干预。
自适应执行	根据数据分布和统计信息动态选择最优算法（如 Hash Join vs Nested Loop）。	用户无需手动调优；EXPLAIN 可观察实际选择的算子。

实践建议：

优先使用 Parquet 格式存储数据（支持列裁剪、压缩、统计信息）。

避免在 WHERE/GROUP BY 中使用复杂表达式（如 substr(col,1,3)），可预计算为新列。

大查询前用 EXPLAIN ANALYZE 定位瓶颈（如是否发生磁盘溢出、并行度不足）。

第六章：扩展与集成

6.1 自定义函数（Scalar / Aggregate）

方法名称	语法	用途	代码示例	注意事项
注册标量函数（Python）	con.create_function(‘func_name’, func, [return_type], [parameters])	将 Python 函数注册为 SQL 标量函数	见下方代码示例 1	支持任意 Python 逻辑；性能低于内置函数（因跨语言调用开销）。
注册聚合函数（Python）	con.create_aggregate(‘agg_name’, init, update, finalize, …)	定义自定义聚合逻辑	见下方代码示例 2	需实现 init/update/finalize；不支持并行聚合（截至 v0.10）。
使用 lambda 简化标量注册	con.create_function(‘square’, lambda x: x*x)	快速注册简单函数	见下方代码示例 3	类型推断可能不准确，建议显式指定参数和返回类型。
删除自定义函数	DROP FUNCTION func_name	移除已注册的函数	`con.execute("DROP FUNCTION add_one")`	仅影响当前连接；重启后自动消失。
在 CLI 中注册（不支持）	—	CLI 不支持动态注册 Python 函数	—	CLI 仅能使用内置或 C++ 扩展函数。

代码示例 1：注册标量函数

def add_one(x):
    return x + 1

con.create_function('add_one', add_one, 'INTEGER', ['INTEGER'])
con.execute("SELECT add_one(5)").fetchone()

代码示例 2：注册聚合函数

class SumAgg:
    def init(self):
        self.total = 0
    def update(self, val):
        self.total += val
    def finalize(self):
        return self.total

con.create_aggregate('my_sum', SumAgg, 'INTEGER')

代码示例 3：使用 lambda 注册

con.create_function('square', lambda x: x * x)
con.execute("SELECT square(4)").fetchone()

6.2 与 Python 生态集成（pandas, Polars, SQLAlchemy）

方法名称	语法	用途	代码示例	注意事项
查询 Pandas DataFrame	con.sql(“SELECT … FROM df”)	直接在 SQL 中操作本地 DataFrame	见下方代码示例 1	变量名需与 SQL 中一致；零拷贝引用（高效）。
注册 DataFrame 为虚拟表	con.register(‘table_name’, df)	显式命名 DataFrame 供多次引用	见下方代码示例 2	适用于复杂查询或多表 JOIN。
与 Polars 通过 Arrow 交互	tbl = pl_df.to_arrow(); con.from_arrow(tbl, ‘polars_table’)	将 Polars DataFrame 导入 DuckDB	见下方代码示例 3	需安装 pyarrow；DuckDB 原生不直接支持 Polars。
通过 SQLAlchemy 连接	from sqlalchemy import create_engine; engine = create_engine(‘duckdb:///file.db’)	使用 ORM 或通用 DB 接口	见下方代码示例 4	支持标准 SQLAlchemy 操作；适合已有 ORM 项目迁移。
返回结果为 PyArrow Table	con.execute(“SELECT …“).arrow()	高效数据交换格式	`table = con.execute("SELECT * FROM generate_series(1,1000) t(x)").arrow()`	适合传给 Polars、Ray、Dask 等 Arrow 兼容库。

代码示例 1：查询 Pandas DataFrame

import pandas as pd
df = pd.DataFrame({'x': [1, 2, 3]})
result = con.sql("SELECT x*2 AS y FROM df").df()

代码示例 2：注册 DataFrame 为虚拟表

con.register('sales', sales_df)
con.execute("SELECT region, SUM(amount) FROM sales GROUP BY region").df()

代码示例 3：与 Polars 通过 Arrow 交互

import polars as pl
lf = pl.DataFrame({'a': [1, 2]})
con.from_arrow(lf.to_arrow(), 't')
con.execute("SELECT * FROM t").df()

代码示例 4：通过 SQLAlchemy 连接

from sqlalchemy import create_engine

engine = create_engine('duckdb:///:memory:')
# 或连接文件：engine = create_engine('duckdb:///file.db')
with engine.connect() as conn:
    result = conn.execute(text("SELECT 1")).fetchone()

6.3 与 Jupyter Notebook 集成

方法名称	语法	用途	代码示例	注意事项
直接显示查询结果（自动）	con.sql(“SELECT …”)	在 Notebook 中自动渲染为表格	`con.sql("SELECT * FROM generate_series(1,5) t(x)")`	需 duckdb >= 0.7；自动调用 repr_html。
启用 IPython 魔法命令	%load_ext duckdb	加载 DuckDB 魔法扩展	见下方代码示例 1	首次使用需安装 duckdb；魔法命令简化交互。
使用魔法查询变量	%%duckdb -c df	将结果存入 Python 变量	见下方代码示例 2	-c 指定输出变量名；默认为 duckdb_output。
从单元格变量查询	%%duckdb	在魔法中引用 Notebook 变量	见下方代码示例 3	使用花括号 {} 插入变量；类似 f-string。
可视化集成（配合 Matplotlib）	df = con.sql(”…“).df(); df.plot(…)	快速分析+绘图	`con.sql("SELECT x FROM range(100) t(x)").df().plot.hist()`	结合 Pandas 绘图生态，无需导出中间文件。

代码示例 1：启用 IPython 魔法命令

%load_ext duckdb

%%duckdb
SELECT * FROM range(3) t(x)

代码示例 2：使用魔法查询变量

%%duckdb -c result_df
SELECT user_id, COUNT(*) AS cnt FROM events GROUP BY user_id

代码示例 3：从单元格变量查询

my_df = pd.DataFrame({'a': [1, 2]})

%%duckdb
SELECT a*10 FROM {my_df}

6.4 支持的第三方扩展（如 HTTPFS、JSON、SPATIAL 等）

扩展名称	安装方式（CLI）	加载方式（SQL）	用途	代码示例	注意事项
httpfs	INSTALL httpfs;	LOAD httpfs;	从 HTTP/HTTPS/S3/GCS 读取文件	见下方代码示例 1	访问 S3 需配置 AWS 凭据；支持 Parquet/CSV 直读。
json	INSTALL json;	LOAD json;	解析 JSON 字符串或文件	`con.execute("SELECT json_extract('{\"a\":1}', '$.a')")`	支持 json_each、json_tree 等函数。
parquet	（通常默认包含）	LOAD parquet;	高级 Parquet 功能（如 schema evolution）	见下方代码示例 2	Python 包默认启用；CLI 需手动 LOAD。
spatial	INSTALL spatial;	LOAD spatial;	地理空间数据处理（WKT、距离计算等）	见下方代码示例 3	基于 GEOS 库；支持 PostGIS 风格函数。
icu	INSTALL icu;	LOAD icu;	国际化文本处理（排序、正则等）	见下方代码示例 4	提供更强大的正则和 Unicode 支持。
visualizer	INSTALL visualizer;	LOAD visualizer;	生成查询计划可视化图（DOT 格式）	见下方代码示例 5	需 Graphviz 渲染；用于教学或调试。
Python 扩展	pip install duckdb[all]	—	一次性安装所有常用扩展	`pip install duckdb[httpfs,spatial,json]`	部分扩展（如 spatial）需编译依赖，安装较慢。

代码示例 1：httpfs

con.execute("LOAD httpfs")
con.execute("SELECT * FROM 'https://example.com/data.csv' LIMIT 5").df()

代码示例 2：parquet

con.execute("LOAD parquet")
con.execute("SELECT * FROM 'data.parquet'")

代码示例 3：spatial

con.execute("LOAD spatial")
con.execute("SELECT ST_Distance('POINT(0 0)', 'POINT(1 1)')")

代码示例 4：icu

con.execute("LOAD icu")
con.execute("SELECT regexp_extract('abc123', '\\d+', 0)")

代码示例 5：visualizer

con.execute("LOAD visualizer")
con.execute("EXPLAIN (FORMAT DOT) SELECT * FROM t")

扩展管理提示：

所有扩展首次使用需 INSTALL（下载），之后只需 LOAD（加载到内存）。

扩展存储在用户目录（如 ~/.duckdb/extensions/），可离线分发。

在 Python 中，多数扩展随包预编译，无需手动 INSTALL。

第七章：应用场景与最佳实践

7.1 OLAP 分析场景

场景名称	说明	最佳实践	注意事项
交互式 BI 探索	快速对百万至十亿行数据执行聚合、分组、窗口函数等分析操作	使用 Parquet 存储原始数据；通过 DuckDB 直接查询，避免 ETL 预处理	不适合高并发多用户访问；建议配合缓存层（如物化视图）。
日志/事件分析	分析用户行为日志、系统监控事件流	将日志按天分区为 Parquet 文件；利用谓词下推快速筛选时间范围	单文件不宜过大（建议 <1GB）；可结合 generate_series 模拟时间维度。
财务/销售报表生成	按区域、产品、时间等维度汇总指标	使用 CTE 构建多阶段计算逻辑；导出结果为 CSV/Parquet 供下游使用	复杂报表可拆分为多个中间表（临时表）提升可维护性。
A/B 测试统计	计算实验组 vs 对照组的转化率、均值差异	见下方代码示例	确保随机分组；使用 EXPLAIN ANALYZE 验证是否高效执行。
大宽表分析	处理含数百列的用户画像或特征表	仅 SELECT 所需列；DuckDB 自动跳过无关列（列裁剪）	避免 SELECT *；宽表建议用 Parquet（列式压缩率高）。

代码示例：A/B 测试统计

# 利用 FILTER 聚合或条件聚合避免多次扫描
con.execute("""
    SELECT
        COUNT(*) FILTER (WHERE variant='A') AS count_a,
        COUNT(*) FILTER (WHERE variant='B') AS count_b
    FROM events
""")

7.2 数据探索与原型开发

场景名称	说明	最佳实践	注意事项
Jupyter Notebook 快速分析	在 Notebook 中加载数据、清洗、可视化	使用 con.sql(”…”) 直接返回 DataFrame；配合 %load_ext duckdb 魔法命令	内存数据库适合中小数据集（<10GB）；大文件用 Parquet + 惰性读取。
临时数据验证	快速验证数据质量、分布、异常值	`con.execute("SELECT MIN(x), MAX(x), COUNT(CASE WHEN x IS NULL THEN 1 END) FROM df")`	可直接查询 Pandas DataFrame，无需写入磁盘。
原型 ETL 流程	构建端到端数据处理流水线（提取 → 转换 → 加载）	从 CSV/Parquet 读取 → SQL 清洗 → 导出为新格式：`COPY (SELECT ...) TO 'output.parquet'`	使用内存数据库避免 I/O；生产环境再迁移到持久化存储。
与 Polars/pandas 对比性能	评估不同引擎在相同任务下的表现	分别用 duckdb.sql(…).df() 和 pl.scan_parquet(…).collect() 执行相同逻辑	DuckDB 在复杂 SQL（多 JOIN、窗口函数）上通常更简洁高效。
自动生成数据	用于测试或演示	`con.execute("SELECT * FROM generate_series(1,1000000) t(id), random() AS value")`	generate_series 是内置函数；可结合字符串/时间函数构造复杂样本。

7.3 嵌入式分析系统架构

架构组件	说明	实现方式	注意事项
单机嵌入式分析引擎	将 DuckDB 作为应用内部分析模块，无需外部数据库	Python 应用中直接 import duckdb；前端通过后端 API 查询	适用于桌面应用、CLI 工具、边缘设备分析。
Web 应用集成	在 Flask/FastAPI 后端嵌入 DuckDB 提供分析 API	见下方代码示例	需防范 SQL 注入（建议参数化或白名单校验）；限制查询复杂度。
持久化分析仓库	使用 .db 文件作为轻量级分析数据湖	定期将原始数据导入 DuckDB 表；建立索引（实验性）或物化视图	单文件大小建议 <100GB；超大场景改用 Parquet + 目录分区。
与前端可视化联动	后端 DuckDB 执行查询，前端用 ECharts/Plotly 渲染	查询返回 JSON；前端动态构建图表	聚合结果应轻量（<10k 行）；明细数据需分页或采样。
容器化部署	将含 DuckDB 的应用打包为 Docker 镜像	Dockerfile 中 pip install duckdb；挂载数据卷	镜像体积小（<100MB）；适合 Serverless 或微服务场景。

代码示例：Web 应用集成（FastAPI）

app = FastAPI()

@app.get("/query")
def run(sql: str):
    return con.execute(sql).df().to_dict()

7.4 与其他数据库（SQLite、PostgreSQL）对比

对比维度	DuckDB	SQLite	PostgreSQL	适用建议
设计目标	嵌入式 OLAP（分析优先）	嵌入式 OLTP（事务优先）	客户端-服务器 OLTP/OLAP 混合	分析选 DuckDB，事务选 SQLite/PostgreSQL。
存储模型	列式存储	行式存储	行式存储（支持列存扩展如 cstore_fdw）	列存对聚合/扫描快，行存对点查快。
并发写入	单写多读（写互斥）	单写多读（WAL 模式支持并发读）	多写多读（MVCC）	DuckDB 不适合高并发写入场景。
SQL 功能	兼容 PostgreSQL 风格，支持窗口函数、CTE 等	基础 SQL，有限窗口函数（v3.25+）	完整 SQL 标准，丰富扩展	复杂分析 SQL 在 DuckDB 和 PG 中均可运行。
数据源支持	原生支持 Parquet/CSV/JSON，无需导入	需先导入为表	需 FDW 或 COPY 导入	DuckDB 更适合即席查询外部文件。
部署复杂度	零配置，单文件库	零配置，单文件库	需独立服务进程、用户管理、备份等	原型/嵌入场景首选 DuckDB/SQLite。
性能（OLAP）	极高（向量化 + 列存）	低（行存 + 无向量化）	中高（需调优，如物化视图、索引）	百万行以上聚合，DuckDB 通常快 10-100 倍。
内存使用	可配置内存限制，支持溢出到磁盘	全内存或 WAL 文件	共享内存 + 磁盘缓冲	DuckDB 更适合内存受限但需分析大文件的场景。
扩展生态	支持 HTTPFS、SPATIAL、JSON 等扩展	支持 FTS、RTree 等扩展	丰富官方/第三方扩展	DuckDB 扩展更聚焦分析场景（如地理空间、云存储）。

总结建议：

用 DuckDB：当你需要在单机上快速分析 CSV/Parquet 数据，执行复杂 SQL，且无需多用户写入。

用 SQLite：当你需要轻量级事务、键值存储或移动 App 本地数据库。

用 PostgreSQL：当你需要多用户并发、ACID 强一致性、分布式部署或企业级功能。