DuckDB 教程:SQL 里做 t 检验、ANOVA 和回归,18 个函数实测与 3 处参数坑
我把 stats_duck 的函数逐个跑了一遍:18 个假设检验、48 个分布函数,9 项结果与 scipy 对账 8 项一致(回归系数最大差 3.1e-15)。但双样本检验要两列而不是「值+分组标签」、STRUCT 不能用 ( ).* 解构、卡方只认 VARCHAR——这三处照直觉写会直接报错。
先看四个数字
你以为是这样的,实际是这样的
01 安装:一行 SQL,没有 Python 依赖
02 关键一步:长表怎么喂给双样本检验
03 一次 SQL 跑多个分组检验
04 和 scipy 对账:到底准不准
05 回归:两种写法,稳健标准误只在一个里
06 还有几个省事的函数
07 SAS / SPSS / Stata 文件读写
08 分布函数:算临界值,或者做蒙特卡洛
3 个参数坑,照直觉写必报错
常见问题
现在做这一件事
做风控或数据分析的人,大概都干过同一件事:从库里导一份数据出来,用 Python 调 scipy 算个检验,再把结果贴回报表。
问题不在算得对不对,在于数据要来回搬。导出的那一份,和库里的那一份,过两天就对不上了。
stats_duck 这个 DuckDB 社区扩展想解决的就是这件事——把检验直接做成 SQL 聚合函数。我把它的函数逐个跑了一遍,结论比标题更复杂一点:能跑,但参数约定和文档直觉有三处冲突,照常见的写法抄必报错。
核心结论:stats_duck(DuckDB 1.5.5,扩展版本 efdd24d,来源 community)加载后新增 95 个函数,其中假设检验 18 个、分布函数 48 个、VISUALIZE 可视化 15 个。实测 9 项结果与 scipy / numpy 对账 8 项一致:回归系数最大差 3.1e-15、p 值最大差 3.7e-10,均为浮点精度量级;唯一例外是 Mann-Whitney 的 U 取值约定(p 值差 9.1e-05)。但有三个反直觉的约定:双样本检验要传两列数值而不是「值 + 分组标签」、返回的 STRUCT 不能用
( ).*解构、卡方检验只认 VARCHAR 分类列。另外官方 README 里「read_sas/read_spss」这类函数名并不存在,真实入口只有一个read_stat()。
先看四个数字
| 实测项 | 结果 | 怎么理解 |
|---|---|---|
| 扩展版本 | efdd24d | DuckDB 1.5.5 · 来自 community 仓库 |
| 假设检验 | 18 个 | 全部为聚合函数,天然支持 GROUP BY |
| 与 scipy 对账 | 8 / 9 一致 | t / W / F / r / χ² / OLS 系数对齐;MW 因 U 约定不同 |
| 参数坑 | 3 处 | 照直觉写会直接报 Binder Error |
图 1 · stats_duck 的做法是让数据留在库里;传统路径要搬四次
你以为是这样的,实际是这样的
ttest_2samp(conv, grp) —— 把「数值列 + 分组标签」当作两个参数。这是 R 的 t.test(x ~ g) 直觉,在 stats_duck 里直接报类型错误。
SELECT (ttest_2samp(a,b)).* —— 想用 .* 展开 STRUCT,DuckDB 不支持这个语法。
ttest_2samp(a_col, b_col) —— 两个参数都是 DOUBLE 列,长表要先透成两列。
SELECT ttest_2samp(a,b).p_value —— 按字段名取,或先给 STRUCT 起别名再取字段。
01 安装:一行 SQL,没有 Python 依赖
INSTALL stats_duck FROM community;LOAD stats_duck;装完这一行,DuckDB 里就多了 95 个函数。可以用扩展自己的元数据表核对,不用信任何二手文档:
-- 加载前先记一份函数名,再用 EXCEPT 做差集SELECT count(*) FROM ( SELECT DISTINCT function_name FROM duckdb_functions() EXCEPT SELECT DISTINCT function_name FROM duckdb_functions());这个「差集法」是核实任何 DuckDB 扩展真实能力最稳的办法——扩展自报的函数表可能滞后于二进制。
02 关键一步:长表怎么喂给双样本检验
这是整篇文章最实用的一段。你的数据大概率是长表——一行一条申请记录,用一个字段标实验组/对照组。但 ttest_2samp 要的是两列。
实测下来,用 row_number() 对齐行号、FULL JOIN 拼起来就行,而且两组样本量不等也没问题:
WITH ctrl AS ( SELECT row_number() OVER (ORDER BY conv) AS rn, conv FROM ab WHERE grp = 'control'), trt AS ( SELECT row_number() OVER (ORDER BY conv) AS rn, conv FROM ab WHERE grp = 'treat')SELECT ttest_2samp(trt.conv, ctrl.conv) AS rFROM trt FULL JOIN ctrl USING (rn);上面的表 ab 里 control 组 180 条、treat 组 120 条,样本量并不相等。分开对齐行号再 FULL JOIN,短的那一侧自然会补 NULL,聚合函数会把 NULL 排除掉——结果是对的。
返回的是一个 STRUCT,实测字段如下:
| 字段 | 含义 |
|---|---|
test_type | 检验类型,如 Welch Two-sample |
t_statistic / df / p_value | 统计量、自由度、p 值 |
mean_diff | 两组均值差 |
ci_lower / ci_upper | 均值差的置信区间 |
cohens_d | 效应量 |
03 一次 SQL 跑多个分组检验
检验函数是聚合函数,所以 GROUP BY 直接能用。十个地区各跑一个检验,在 Python 里要写循环,在这里是一句:
SELECT region, ttest_2samp(a_score, b_score).p_value AS p_valueFROM applicantsGROUP BY regionORDER BY region;实测输出(三个区域各一个 Welch 检验):
| region | p_value |
|---|---|
| 华东 | 0.094026 |
| 华南 | … |
| 华北 | … |
它还能跟窗口函数、并行执行自然组合——因为每个检验都是单遍流式聚合,不是把数据收集到内存里再算。
04 和 scipy 对账:到底准不准
统计函数最怕的就是「能跑但数不对」。我造了一份可复现的数据(控制组 180 条、实验组 120 条,固定随机种子),同一份数据分别走 stats_duck 和 scipy,逐项比对:
| 检验 | stats_duck | scipy / numpy | 差异 |
|---|---|---|---|
| Welch 双样本 t | t=2.138347 | ||
| df=191.537176 | |||
| p=0.03375549 | t=2.138347 | ||
| df=191.537176 | |||
| p=0.03375549 | 1.2e-15 | ||
| Shapiro-Wilk | W=0.99201941 | ||
| p=0.42469826 | W=0.99201941 | ||
| p=0.42469826 | 0 | ||
| ANOVA | F=1.92325255 | ||
| p=0.14733818 | F=1.92325255 | ||
| p=0.14733818 | 4.9e-15 | ||
| Pearson | r=0.81127559 | r=0.81127559 | 4.4e-16 |
| Spearman | rho=0.79317989 | rho=0.79317989 | 1.1e-16 |
| Kendall | tau=0.60061691 | tau=0.60061691 | 0 |
| 卡方独立性 | χ²=0.00000000 | χ²=0.00000000 | 0 |
| OLS 系数(3 项) | 0.5074167 / −0.00054142 | ||
| −0.00042197 | 0.5074167 / −0.00054142 | ||
| −0.00042197 | 3.1e-15 |
8 项一致:回归系数最大差 3.1e-15、p 值最大差 3.7e-10(来自 Shapiro-Wilk),都属浮点精度量级,不是实现差异。
唯一的例外是 Mann-Whitney U。两边 U 值同为 12191.5,但 p 值差 9.08e-05(duck 0.058698 vs scipy 0.058789),差在正态近似与连续性修正的取法上。两者都在 0.05 边缘、结论一致(不显著),但不能拿 U 数值直接互相比对。
图 2 · 18 种假设检验按用途分五组
05 回归:两种写法,稳健标准误只在一个里
回归有两个入口。lm 是表函数,传列名,返回系数表:
SELECT * FROM lm('applicants', y := 'a_rate', x := ['a_score', 'gap']);-- term | estimate | std_error | t_statistic | p_valueSELECT * FROM lm_summary('applicants', y := 'a_rate', x := ['a_score', 'gap']);-- r_squared | adj_r_squared | f_statistic-- f_p_value | df_model | df_residual | sigma | n想让标准误稳健(HC0–HC3),不能用 lm——实测传 vcov := 'HC3' 直接报 Invalid named parameter "vcov"。稳健标准误在聚合版 lm_fit 里:
-- 每个区域一个模型,HC3 稳健标准误SELECT region, lm_fit(a_rate, [a_score], 'HC3') AS modelFROM applicants GROUP BY region;实测 HC3 与默认的差异(同一条 x1):标准误 0.00203695(HC3)对比 0.00200923(默认),p 值 0.6395 对比 0.6349。点数估计完全一致,变的只是推断。风控数据常见异方差,这里建议直接用 HC3。
再往上一层是集群稳健标准误(CR0 / CR1),适合同一家企业多期数据这种「组内相关」场景。它的 cluster key 是一个真正的行级列,不是常量:
SELECT unnest((lm_fit(a_rate, [a_score], 'CR1', firm_id::VARCHAR)).coefficients) AS uFROM panel;06 还有几个省事的函数
Table 1(基线特征表):医学论文那种「按分组列均值±标准差 + 组间 p 值」的表,一句话出。数值变量自动用 ANOVA,分类变量自动用卡方:
SELECT * FROM table_one('applicants', variables := ['a_score', 'region'], by := ['arm']);corr_matrix(相关矩阵):皮尔逊、斯皮尔曼、肯德尔三种方法都能选,长格式输出,直接能与其它表 JOIN:
SELECT * FROM corr_matrix('applicants', variables := ['a_rate', 'a_score'], method := 'spearman');-- row_var | col_var | coef | p_value | nadjust_p(多重检验校正):跑了一批检验要做 FDR 校正,不用回 Python。实测对 [0.01, 0.02, 0.03, 0.2, 0.5] 五个 p 值:
| 方法 | 校正后结果 |
|---|---|
bonferroni | 0.05 / 0.1 / 0.15 / 1.0 / 1.0 |
holm | 0.05 / 0.08 / 0.09 / 0.4 / 0.5 |
BH(别名 fdr) | 0.05 / 0.05 / 0.05 / 0.25 / 0.5 |
BY | 0.1142 / 0.1142 / 0.1142 / 0.5708 / 1.0 |
none | 原样返回 |
07 SAS / SPSS / Stata 文件读写:支持范围比文档窄
这一段我踩得最狠。网上流传的 read_sas() / read_spss() / read_stata() 三个函数在扩展里都不存在——实测全部报 Catalog Error: Table Function with name read_sas does not exist。
真实入口只有一个,用 format 参数区分格式:
-- 显式指定格式(推荐,最稳)SELECT * FROM read_stat('survey.sav', format := 'sav');SELECT * FROM read_stat('panel.dta', format := 'dta');SELECT * FROM read_stat('clinical.sas7bdat', format := 'sas7bdat');-- 也支持按扩展名自动识别SELECT * FROM read_stat('survey.sav');写回是用 COPY,但能写的格式和能读的格式并不对称,这点文档里没写清楚:
| 格式 | 读(read_stat) | 写(COPY TO) |
|---|---|---|
Stata .dta | ✓ 实测成功 | ✗ Copy Function with name dta does not exist |
SPSS .sav | ✓ 实测成功 | ✓ 实测写出 475 B |
| SAS7BDAT | ✓ 实测成功 | ✓ 实测写出 16384 B |
| SAS XPT | ✓ 实测成功 | ✓ 实测写出 1120 B |
| CSV / XLSX | 不是统计格式,不走这个函数 | — |
一处要留意的官方提示:SAS7BDAT 的写入器是逆向实现的,它写出的文件能被 stats_duck / pyreadstat / haven 读回,但真 SAS 打不开。要给 SAS 交付,用 XPT 格式。
08 分布函数:算临界值,或者在 SQL 里做蒙特卡洛
48 个分布函数覆盖正态、t、卡方、F、Gamma、Beta、指数、Weibull、对数正态、泊松等。它们能直接在 SQL 里算临界值和尾概率,不用查表:
SELECT pnorm(1.96) AS p_1_96, -- 0.975002 qnorm(0.975) AS z_975, -- 1.959964 pt(2.0, 30) AS t_cdf, -- 0.972687 pchisq(3.84, 1) AS chi_p, -- 0.949956 qt(0.975, 10) AS t_975; -- 2.228139更实用的是随机数生成器(rnorm / rt / rchisq 等),能在 SQL 里直接做蒙特卡洛——比如验一下正态分布 95% 区间到底落在哪:
SELECT count(*) AS n, round(avg(v), 4) AS mean, round(stddev(v), 4) AS sd, round(quantile_cont(v, 0.025), 4) AS p2_5, round(quantile_cont(v, 0.975), 4) AS p97_5FROM (SELECT rnorm(0, 1) AS v FROM range(20000));3 个参数坑,照直觉写必报错
图 3 · 三处参数约定:报错原文来自真实运行
| 坑 1 · 双样本检验要两列,不是「值 + 分组标签」
写 ttest_2samp(conv, grp) 会得到 Binder Error: No function matches the given name and argument types 'ttest_2samp(DOUBLE, VARCHAR)'。真实签名是 (col0 DOUBLE, col1 DOUBLE)——两列数值。长表要先按 02 节透成两列。 |
|---|
坑 2 · 返回 STRUCT 不能用 ( ).* 解构 |
SELECT (ttest_2samp(a,b)).* 直接 Parser Error: syntax error at or near "*"。要用 ttest_2samp(a,b).p_value 按字段取,或者先 AS r 起别名再取 r.p_value。 |
| 坑 3 · 卡方检验按分类列判定,必须 VARCHAR |
传数值列(哪怕是分箱 id)会报 No function matches ... 'chisq_goodness_of_fit(INTEGER)'。真实签名是 chisq_goodness_of_fit(VARCHAR),把分箱转成 'bin_1' 这类字符串才行。 |
另外两处不那么显眼、但会让人白折腾一阵的:
| 列表字面量不能直接传
ttest_2samp([1,2,3],[2,3,4]) 报类型错误。函数只接受列或聚合表达式,临时数据用 VALUES 或 unnest(range(...)) 造表。 |
|---|
| 官方 README 有滞后 |
README 写「Spearman / Kendall 相关是未来版本功能」,但实测这两个函数都已经存在且能跑。判断一个函数有没有,用 duckdb_functions() 查,别只读 README。 |
常见问题
Q:需要装 Python 或 R 吗?
不需要。检验、回归、Table 1 全在 DuckDB 内完成。只有在做「与 scipy 对账」这类交叉验证时才用得上 Python。
Q:结果和 scipy 一致,那我为什么不用 scipy?
不是二选一。差异在于数据要不要搬:几千万行的表,导出去再读进来,时间和内存都在搬运上;在库内算完只返回一行结果。另外 GROUP BY 天然帮你并行分组,不用自己写循环。
Q:能上生产吗?
社区扩展、Apache-2.0、实测数值与 scipy 对齐,日常统计工作流可以用。混合效应模型、生存分析目前没有;回归诊断也只有 R²/F/σ 这一层,复杂的还得回 R 或 statsmodels。另外它是 INSTALL ... FROM community 来源,生产环境请评估供应链要求。
现在做这一件事
打开你的 DuckDB,跑这三行:
INSTALL stats_duck FROM community;LOAD stats_duck;SELECT ttest_2samp(a_score, b_score).p_value FROM applicants;把你日常写 Python 的那个 t 检验,改成一列 SQL 试试。数据搬来搬去那一步省掉之后,你会发现很多「分析」其实只是「取数 + 算一个数」。
▶ stats_duck 仓库(KoliStat/the-stats-duck) — Apache-2.0,含完整函数表与 VISUALIZE 语法
▶ DuckDB 社区扩展目录 — 查扩展版本与来源
▶ QIAN数据 · 开源数据库存储结构研究报告 — 从写入落地到 RUM 三角,讲清列存为什么适合这种聚合
关注「QIAN数据 · AI工具实验室」
本文含 AI 辅助创作内容,实测数据均来自本机真实运行并留档。