SQL如何计算分组内的方差与标准差_窗口聚合函数实操

首页

数据库

热心网友

转载

2026-04-24

SQL中VARIANCE和STDDEV默认按样本计算（除以n-1），PostgreSQL、Oracle、Snowflake均如此；MySQL的VARIANCE()等价VAR_SAMP()，STDDEV()等价STDDEV_SAMP()；SQL Server需显式用STDEV()或STDEVP()。

SQL如何计算分组内的方差与标准差_窗口聚合函数实操

免费影视、动漫、音乐、游戏、小说资源长期稳定更新！ 👉 点此立即查看 👈

SQL里`VARIANCE`和`STDDEV`默认算的是总体还是样本？

这个问题看似基础，却是个实实在在的“暗坑”。先说结论：在主流数据库里，VARIANCE和STDDEV这两个函数，默认计算的通常是**样本**统计量，也就是分母用 n-1，对应统计学里的样本方差 s² 和样本标准差 s。PostgreSQL、Oracle、Snowflake 都是这个规矩。

不过，总有那么几个“特立独行”的选手。比如 MySQL，它的 VARIANCE() 函数确实等价于样本方差 VAR_SAMP()，但 STDDEV() 你得注意，它默认指向的是 STDDEV_SAMP()。至于 SQL Server，它干脆不提供无前缀的 STDDEV，你必须明确写出 STDEV()（样本）或者 STDEVP()（总体），没有模糊地带。

这里有几个容易踩进去的坑，值得划个重点：

当你发现 STDDEV(col) 和 STDDEV_POP(col) 在同一组数据上算出不同的结果时——前者小，后者大——千万别以为是精度问题，这纯粹是分母不同（n-1 与 n）导致的。
在 PostgreSQL 里，VAR_POP() 和 VARIANCE() 可不是一回事。后者等价于 VAR_SAMP()，代表样本方差。
如果业务场景明确要求计算“总体标准差”（例如，你分析的是全量用户数据，而非抽样样本），那就必须老老实实调用 VAR_POP() 或 STDDEV_POP()，用默认函数大概率会出错。

窗口函数里怎么对分组内数据算标准差？

想在分组内计算标准差？窗口函数是你的得力工具。方法很直接：在 OVER 子句里套用 STDDEV_SAMP() 或 STDDEV_POP()，并通过 PARTITION BY 指定分组维度就行。举个例子，要计算每个部门内员工薪资的标准差，可以这么写：

SELECT
  dept,
  name,
  salary,
  STDDEV_SAMP(salary) OVER (PARTITION BY dept) AS dept_salary_stddev
FROM employees;

看起来简单，但有几个细节需要留心：

STDDEV_SAMP() 作为窗口函数时，要求每个分区至少有两行数据，否则它会返回 NULL。道理很简单，单行数据没法计算样本标准差。
如果想在单行分区里也得到一个数值（比如强制返回0），就需要更复杂的逻辑，例如：CASE WHEN COUNT(*) OVER (PARTITION BY dept) = 1 THEN 0 ELSE STDDEV_SAMP(salary) OVER (PARTITION BY dept) END。
注意，在计算标准差的窗口里，通常不需要加 ORDER BY。因为方差和标准差是无序的聚合运算。强行加上 ORDER BY 可能会触发默认的窗口帧（比如 range frame），导致计算结果与预期不符，这就属于画蛇添足了。

为什么`STDDEV`窗口结果和先`GROUP BY`再`JOIN`不一致？

有时候，你会发现用窗口函数直接算出的标准差，和先用 GROUP BY 聚合再 JOIN 回去的结果对不上。这背后，通常是两个原因在作祟：空值（NULL）的处理方式，以及聚合的粒度。

窗口函数在处理时，会自动忽略当前行中参与计算列的 NULL 值，但该行结果依然会输出。而 GROUP BY 再 JOIN 的方案呢？如果 JOIN 条件没能妥善处理空值对齐，或者分组时 NULL 值被单独分成了一组，结果自然就会产生错位。

还有一个更隐蔽的问题：重复行。窗口函数是逐行计算的，每一行都能得到基于整个分组的 STDDEV。但如果你用子查询做 GROUP BY 得到一个汇总表，然后 LEFT JOIN 回原表，一旦 ON 的连接条件没有覆盖所有分组键（比如，忽略了某些过滤条件后产生的 NULL 分组），就会导致某些行匹配不到聚合值，最终 STDDEV 显示为 NULL。

遇到这种不一致的情况，可以试试以下调试方法：

先用 COUNT(*) OVER (PARTITION BY ...) 和 COUNT(column) OVER (PARTITION BY ...) 对比一下，看看分区总行数和非空值数量是否符合你的预期。
除非有明确的物化中间结果的需求，否则尽量避免用 GROUP BY + JOIN 这种更复杂的方式来模拟窗口函数的逻辑。
在 PostgreSQL 中，可以考虑使用 SELECT DISTINCT ON (dept) ... 配合窗口函数，这通常比手动写 JOIN 更稳定、更简洁。

ClickHouse / BigQuery / SQLite 怎么办？

当场景切换到 ClickHouse、BigQuery 或 SQLite 这些数据库时，语法差异就凸显出来了，直接复制粘贴大概率会碰壁。

ClickHouse：它没有 STDDEV_SAMP 这个函数名。取而代之的是 stddevPop()（总体标准差）和 stddevSamp()（样本标准差）。注意，函数名是全小写且必须带括号，写成大写的 STDDEV_SAMP() 会直接报错。
BigQuery：这里 STDDEV() 代表样本标准差，STDDEV_POP() 代表总体标准差。但在窗口函数中使用时，必须明确加上 OVER() 子句。另外，BigQuery 的方差/标准差窗口函数不支持 ORDER BY，如果加了会报错：“Analytic function cannot ha ve ORDER BY without window frame”。
SQLite：情况比较直接——它完全不支持窗口函数形式的 STDDEV。唯一的办法就是先通过 GROUP BY 计算出分组的标准差，然后利用 CTE（公共表表达式）或者应用程序层的逻辑，将结果关联回每一行原始数据。没有更便捷的语法糖。