Skip to content

DuckDB 教程:SQL 里做 t 检验、ANOVA 和回归,18 个函数实测与 3 处参数坑 ​

我把 stats_duck 的函数逐个跑了一遍:18 个假设检验、48 个分布函数,9 项结果与 scipy 对账 8 项一致(回归系数最大差 3.1e-15)。但双样本检验要两列而不是「值+分组标签」、STRUCT 不能用 ( ).* 解构、卡方只认 VARCHAR——这三处照直觉写会直接报错。

  1. 先看四个数字

  2. 你以为是这样的,实际是这样的

  3. 01 安装:一行 SQL,没有 Python 依赖

  4. 02 关键一步:长表怎么喂给双样本检验

  5. 03 一次 SQL 跑多个分组检验

  6. 04 和 scipy 对账:到底准不准

  7. 05 回归:两种写法,稳健标准误只在一个里

  8. 06 还有几个省事的函数

  9. 07 SAS / SPSS / Stata 文件读写

  10. 08 分布函数:算临界值,或者做蒙特卡洛

  11. 3 个参数坑,照直觉写必报错

  12. 常见问题

  13. 现在做这一件事

做风控或数据分析的人,大概都干过同一件事:从库里导一份数据出来,用 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()。

先看四个数字 ​

实测项结果怎么理解
扩展版本efdd24dDuckDB 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 检验):

regionp_value
华东0.094026
华南…
华北…

它还能跟窗口函数、并行执行自然组合——因为每个检验都是单遍流式聚合,不是把数据收集到内存里再算。

04 和 scipy 对账:到底准不准 ​

统计函数最怕的就是「能跑但数不对」。我造了一份可复现的数据(控制组 180 条、实验组 120 条,固定随机种子),同一份数据分别走 stats_duck 和 scipy,逐项比对:

检验stats_duckscipy / numpy差异
Welch 双样本 tt=2.138347
df=191.537176
p=0.03375549t=2.138347
df=191.537176
p=0.033755491.2e-15
Shapiro-WilkW=0.99201941
p=0.42469826W=0.99201941
p=0.424698260
ANOVAF=1.92325255
p=0.14733818F=1.92325255
p=0.147338184.9e-15
Pearsonr=0.81127559r=0.811275594.4e-16
Spearmanrho=0.79317989rho=0.793179891.1e-16
Kendalltau=0.60061691tau=0.600616910
卡方独立性χ²=0.00000000χ²=0.000000000
OLS 系数(3 项)0.5074167 / −0.00054142
−0.000421970.5074167 / −0.00054142
−0.000421973.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_value
SELECT * 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 | n

adjust_p(多重检验校正):跑了一批检验要做 FDR 校正,不用回 Python。实测对 [0.01, 0.02, 0.03, 0.2, 0.5] 五个 p 值:

方法校正后结果
bonferroni0.05 / 0.1 / 0.15 / 1.0 / 1.0
holm0.05 / 0.08 / 0.09 / 0.4 / 0.5
BH(别名 fdr)0.05 / 0.05 / 0.05 / 0.25 / 0.5
BY0.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 辅助创作内容,实测数据均来自本机真实运行并留档。

关注公众号:QIAN数据