7 个值得复用的 DuckDB SQL 模式

实用、快速、可复制的DuckDB技巧，让你的笔记本电脑变身小型OLAP引擎——无需离开Python环境

七大DuckDB SQL模式：直接查询文件、窗口函数去重、数据透视/逆透视、JSON处理、Parquet导出、与Pandas/Polars交互

并非每个分析任务都需要数据仓库。 有时候，你只需要立刻得到结果——无需支付平台费用。

DuckDB正是为此而生。以下是我在日常Python工作中持续复用的七个SQL模式。它们简洁、高效，几乎无需任何配置即可融入你的分析流程。

核心工作流

将你的分析路径想象为：文件 → DuckDB SQL → 小型结果集 → Python处理。你可以直接用SQL查询Parquet/CSV/JSON文件，尽早过滤和聚合，最后才将精简的结果集导入DataFrame。这样既能保证笔记本电脑保持流畅运行，又能快速获得分析结果。

模式1

-- 像查询表一样查询文件（支持下推）

直接用SQL查询本地文件，让DuckDB在Python接触数据前完成列投影和行过滤

-- 单行命令查询文件：列投影+谓词下推
SELECT user_id, SUM(amount) AS total_spend
FROM read_parquet('data/transactions/*.parquet')
WHERE tx_date BETWEENDATE'2025-01-01'ANDDATE'2025-03-31'
AND country = 'CN'
GROUPBY user_id
ORDERBY total_spend DESC
LIMIT20;

核心价值：read_parquet + WHERE 让DuckDB仅读取必要的行组和列。这是在你本地磁盘上实现的数据仓库级行为。同样的技巧也适用于read_csv_auto()和read_json_auto()。

Python衔接（仅返回精简结果）：

import duckdb
import pandas as pd

q = """
SELECT user_id, SUM(amount) AS total_spend
FROM read_parquet('data/transactions/*.parquet')
WHERE tx_date >= DATE '2025-01-01' AND tx_date < DATE '2025-04-01'
GROUP BY user_id ORDER BY total_spend DESC LIMIT 20
"""
df = duckdb.query(q).to_df()  # 精简、整洁、可直接绘图的数据

模式2

-- 将分区文件夹视为表（HIVE分区）

自动将目录名称（如country=CN/yyyymm=202501/）转换为列

-- 目录结构: data/country=CN/yyyymm=202501/part-*.parquet
SELECT country, yyyymm, COUNT(*) AS n, SUM(amount) AS total_amount
FROM read_parquet('data/country=*/yyyymm=*/part-*.parquet', hive_partitioning=1)
WHERE yyyymm BETWEEN'202501'AND'202503'
GROUPBY country, yyyymm
ORDERBY yyyymm, country;

核心价值： 无需元数据存储即可实现快速、整洁的分析。特别适用于事件日志或上游工具导出的即席汇总。

模式3

-- 按主键保留最新记录（QUALIFY技巧）

无需嵌套子查询即可获取每个实体的最新记录

-- 根据updated_at字段保留每个user_id的最新档案
WITHprofilesAS (
SELECT *
FROM read_parquet('data/user_profiles/*.parquet')
)
SELECT *
FROMprofiles
QUALIFY ROW_NUMBER() OVER (
PARTITIONBY user_id ORDERBY updated_at DESC
) = 1;

核心价值：QUALIFY让你能直接基于窗口函数结果进行过滤。比在子查询中包装窗口函数更简洁。特别适用于CDC文件、增量数据转储和混乱的数据导出。

模式4

-- 真正适合内存的滚动指标计算

在SQL中完成时间序列计算，而非Python循环

-- 7日滚动营收和周同比变化
WITH s AS (
SELECT tx_date::DATEAS d, SUM(amount) AS daily_rev
FROM read_parquet('data/transactions/*.parquet')
GROUPBY1
)
SELECT
  d,
  daily_rev,
SUM(daily_rev) OVER (
ORDERBY d
RANGEBETWEENINTERVAL6DAYPRECEDINGANDCURRENTROW
  ) AS rev_7d,
  (daily_rev - LAG(daily_rev, 7) OVER (ORDERBY d)) AS week_delta
FROM s
ORDERBY d;

核心价值： 让Python专注于可视化，而非繁重的计算任务。窗口函数在你的机器上以流式处理，内存占用极低。

模式5

-- 轻松实现数据透视/逆透视

单一语句完成指标仪表板所需的数据重塑

-- 将长格式转换为宽格式（分类作为列）
WITH daily AS (
SELECT
    DATE_TRUNC('day', ts) AS d,
category,
COUNT(*) ASevents
FROM read_parquet('data/events/*.parquet')
GROUPBY1,2
)
PIVOT daily
ONcategory
USINGSUM(events)
GROUPBY d
ORDERBY d;

-- 反向操作：宽格式转长格式，便于整洁绘图
UNPIVOT read_parquet('data/agg/daily_by_category.parquet')
ON COLUMNS(* EXCLUDE d)
INTO NAME category VALUE events;

核心价值： 你将不再需要手动拼接连接或编写脆弱的Pandas重塑代码；所需的数据形状仅需一条SQL语句。

模式6

--JSON和列表处理：展开、整理、重建

许多日志以半结构化字段形式出现，DuckDB让它们重新变得规整

-- 展开订单行中的JSON商品数组
WITH orders AS (
SELECT *
FROM read_json_auto('data/orders_2025.json')  -- 每行包含items[]
),
items AS (
SELECT
    o.order_id,
    i->>'sku'AS sku,
CAST(i->>'qty'ASINTEGER) AS qty,
CAST(i->>'price'ASDOUBLE) AS price
FROM orders o, UNNEST(o.items) AS t(i)
)
SELECT sku, SUM(qty) AS units, SUM(qty*price) AS revenue
FROM items
GROUPBY sku
ORDERBY revenue DESC;

-- 在需要时重新构建整洁的JSON
SELECT order_id,
       to_json( struct_pack(
         total_items := SUM(qty),
         total_price := SUM(qty*price)
       )) AS order_summary_json
FROM items
GROUPBY order_id;

核心价值：UNNEST将嵌套数组转换为可聚合的行；struct_pack/to_json为API或下游工具提供清晰、轻量的输出。

模式7

-- 将清晰数据切片导出至Parquet（便于交接）

经过深度过滤和聚合后，持久化一个精简的分析结果

-- 为团队成员和未来的你保存一个"黄金"数据切片
COPY (
SELECT user_id,
SUM(amount) AS total_spend,
COUNT(*)    AS tx_count
FROM read_parquet('data/transactions/*.parquet')
WHERE tx_date >= DATE'2025-01-01'
GROUPBY user_id
) TO'out/spend_2025_q1.parquet'
  (FORMAT PARQUET, COMPRESSION ZSTD, ROW_GROUP_SIZE 128000);

核心价值： 单个压缩的Parquet文件非常适合共享或后续即时重新加载。避免在每个笔记本中重复进行全局计算。

附加技巧

--直接查询Pandas/Polars数据

让SQL处理繁重的扫描任务；Python仅负责小型连接或绘图

import duckdb
import pandas as pd

users = pd.read_csv("data/users.csv")  # 小型维度表

q = """
SELECT u.user_id, u.segment, t.total_spend
FROM users AS u
JOIN (
  SELECT user_id, SUM(amount) AS total_spend
  FROM read_parquet('data/transactions/*.parquet')
  GROUP BY 1
) AS t
USING (user_id)
ORDER BY total_spend DESC
LIMIT 50
"""
df = duckdb.query(q).to_df()  # 数据分析师的理想工作流

核心价值： DuckDB能够以零拷贝的方式将DataFrame读取为表（如上文的"users"），因此你可以无缝地将SQL扫描与Python原生维度表结合使用。

实用建议（团队易忽略的细节）

• 优选高效格式：对于重复读取，Parquet > CSV。使用COPY (SELECT …) TO 'x.parquet'一次性完成转换。
• 尽早过滤，延迟提取：向Python返回小型结果集。仅带回你需要绘图的数据。
• 保持模式稳定：在读取混乱的JSON时，显式转换类型（CAST(… AS DOUBLE)），然后持久化清晰的Parquet切片。
• 确保确定性排序：在LIMIT之前始终使用ORDER BY，以保证可复现的Top-N列表。
• 构建可重复的笔记本：将SQL封装到小型Python函数中，使得重新运行仅需一次按键，而非繁琐的查找。

微型案例研究（真实场景体验）

某增长团队在分析购买漏斗时，面对大量CSV转储文件。Pandas处理缓慢，连接操作耗时数分钟，有时甚至更长。他们转而采用模式1和模式3：

1. 直接查询Parquet文件（他们一次性将CSV转换为Parquet）
2. 通过QUALIFY去重至最新的客户状态

成果： 在MacBook上，端到端的漏斗表在约5秒内生成，而非在云环境中耗时数分钟。图表快速更新，团队因即时反馈循环而迭代速度提升了一倍。无需数据仓库工单，无需Airflow作业，无需等待。

总结

现实而言：能够立即运行的分析才是最快的分析。DuckDB的优势在于极致的实用性——谓词下推、整洁的数据重塑、轻松易用的窗口函数，以及与Python可视化或建模的流畅衔接。

复用这些模式。根据你的数据灵活调整。如果其中某个模式为你的工作流节省了宝贵时间，请告诉我——然后关注更多能够在笔记本电脑上发挥超出预期效果的实用技巧。