PHP小码农

Appearance

STDDEV / VARIANCE / REGR_xxx ​

STDDEV / VARIANCE 一族把一列数字折叠成离散程度，REGR_xxx 一族则描述两列之间的线性关系——斜率、截距、拟合优度、相关系数。它们是数据分析类 SQL 的核心工具，也是陷阱最集中区：stddev 与 stddev_pop 差一个分母（n-1 vs n）、regr_slope(Y, X) 的参数顺序是 Y 在前、单行输入一律返回 NULL、返回类型随入参类型在 numeric 与 double precision 之间切换。本节把这些细节逐一钉死，并给出 A/B 测试与趋势拟合的完整写法。

前置知识

阅读本节前，建议先了解：BOOL_AND / BOOL_OR / EVERY

基础概念 ​

概念术语说明
样本标准差stddev / stddev_samp分母为 n-1，估计总体用
总体标准差stddev_pop分母为 n，手上就是全体数据用
样本方差variance / var_samp样本标准差的平方
总体方差var_pop总体标准差的平方
样本协方差covar_samp分母 n-1
总体协方差covar_pop分母 n
相关系数corr(Y, X)归一化到 [-1, 1]，与量纲无关
线性回归regr_slope / regr_intercept最小二乘直线的斜率与截距
拟合优度regr_r2判定系数，corr 的平方
有效行数regr_count两列同时非 NULL 的行数
有序集聚合Ordered-Set AggregateWITHIN GROUP (ORDER BY ...) 语法，如分位数
分位数percentile_cont / percentile_disc连续插值 / 离散取值

统计聚合函数速查：

函数计算内容分母返回类型（整数入参）
stddev(x)样本标准差n-1numeric
stddev_samp(x)样本标准差（同 stddev）n-1numeric
stddev_pop(x)总体标准差nnumeric
variance(x)样本方差n-1numeric
var_samp(x)样本方差（同 variance）n-1numeric
var_pop(x)总体方差nnumeric
covar_samp(Y, X)样本协方差n-1double precision
covar_pop(Y, X)总体协方差ndouble precision
corr(Y, X)相关系数归一化double precision
regr_count(Y, X)有效数据点数—bigint

回归函数族（全部为 (Y, X) 顺序，Y 是因变量、X 是自变量）：

函数含义数学定义
regr_slope(Y, X)斜率sxy / sxx
regr_intercept(Y, X)截距avgy - slope * avgx
regr_r2(Y, X)判定系数corr^2
regr_count(Y, X)有效点数两列同时非 NULL 的行数
regr_avgx(Y, X)自变量均值sum(X) / N
regr_avgy(Y, X)因变量均值sum(Y) / N
regr_sxx(Y, X)X 的离差平方和sum(X^2) - sum(X)^2 / N
regr_syy(Y, X)Y 的离差平方和sum(Y^2) - sum(Y)^2 / N
regr_sxy(Y, X)X、Y 的离差积和sum(X*Y) - sum(X)*sum(Y)/N

参数顺序是 (Y, X)，不是 (X, Y)

文档签名写作 regr_slope(Y double precision, X double precision)。Y 是因变量（被解释、被预测的那个），X 是自变量。传反的顺序会让斜率变成 1/slope（在 r2 = 1 时），在有噪声的数据上则得到一个完全无关的数字，而且不会报错。写之前先确认业务含义：预测"销售额（Y）随广告费（X）的变化"→ regr_slope(sales, ad_spend)。

返回类型随入参变化：

入参类型stddev / variance 返回说明
smallint / int / bigint / numericnumeric精确计算，无浮点误差
real / double precisiondouble precision跟随浮点
回归 / 相关 / 协方差函数恒为 double precision无论入参是什么，都先转 double precision

最小可运行示例：

sql
-- 样本数据：5 个观测值
SELECT
  count(*)              AS n,
  round(stddev(x), 6)   AS stddev_samp,
  round(stddev_pop(x), 6) AS stddev_pop,
  round(variance(x), 6) AS var_samp,
  round(var_pop(x), 6)  AS var_pop
FROM (VALUES (2.0), (4.0), (4.0), (4.0), (5.0), (5.0), (7.0), (9.0)) AS t(x);
text
 n | stddev_samp | stddev_pop | var_samp | var_pop
---+-------------+------------+----------+---------
 8 |    2.138090 |   2.000000 | 4.571429 | 4.000000

准备贯穿本节的示例表：

sql
-- 示例库：demo
DROP TABLE IF EXISTS ab_test_daily;
CREATE TABLE ab_test_daily (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  exp_name    TEXT          NOT NULL,     -- 实验名
  group_name  TEXT          NOT NULL,     -- control / variant
  stat_date   DATE          NOT NULL,
  users       INTEGER       NOT NULL,     -- 当天进入实验的用户数
  conversions INTEGER       NOT NULL,     -- 当天转化数
  revenue     NUMERIC(12,2) NOT NULL,     -- 当天收入
  ad_spend    NUMERIC(12,2),              -- 广告花费，可为 NULL
  cvr         NUMERIC(8,6)  GENERATED ALWAYS AS
                (CASE WHEN users > 0 THEN conversions::numeric / users ELSE NULL END) STORED
);

INSERT INTO ab_test_daily (exp_name, group_name, stat_date, users, conversions, revenue, ad_spend) VALUES
  ('checkout_v2', 'control', '2026-03-01', 1200, 108, 21600.00, 3000.00),
  ('checkout_v2', 'control', '2026-03-02', 1300, 117, 23400.00, 3200.00),
  ('checkout_v2', 'control', '2026-03-03', 1250, 106, 21000.00, 3100.00),
  ('checkout_v2', 'control', '2026-03-04', 1400, 126, 25200.00, 3400.00),
  ('checkout_v2', 'control', '2026-03-05', 1350, 122, 24400.00, 3300.00),
  ('checkout_v2', 'variant', '2026-03-01', 1180, 128, 26800.00, 3100.00),
  ('checkout_v2', 'variant', '2026-03-02', 1290, 142, 29700.00, 3400.00),
  ('checkout_v2', 'variant', '2026-03-03', 1240, 131, 27300.00, 3250.00),
  ('checkout_v2', 'variant', '2026-03-04', 1380, 152, 31900.00, 3600.00),
  ('checkout_v2', 'variant', '2026-03-05', 1330, 145, 30400.00, 3500.00);

CREATE INDEX idx_ab_test_daily_exp_group ON ab_test_daily (exp_name, group_name);
ANALYZE ab_test_daily;

语法与用法 ​

sql
-- 1) 离散程度
stddev(numeric_type)     → double precision（浮点入参）/ numeric（其余）
stddev_samp(numeric_type) → 同上（stddev 是它的历史别名）
stddev_pop(numeric_type)  → 同上
variance(numeric_type)    → 同上（variance 是 var_samp 的历史别名）
var_samp(numeric_type)    → 同上
var_pop(numeric_type)     → 同上

-- 2) 相关与协方差（恒为 double precision）
covar_pop(Y, X) → double precision
covar_samp(Y, X) → double precision
corr(Y, X)       → double precision

-- 3) 线性回归（恒为 double precision，regr_count 为 bigint）
regr_slope(Y, X) / regr_intercept(Y, X) / regr_r2(Y, X)
regr_count(Y, X) / regr_avgx(Y, X) / regr_avgy(Y, X)
regr_sxx(Y, X) / regr_syy(Y, X) / regr_sxy(Y, X)

-- 4) 有序集聚合（WITHIN GROUP 语法）
mode() WITHIN GROUP (ORDER BY anyelement)                    → anyelement
percentile_disc(fraction) WITHIN GROUP (ORDER BY anyelement) → anyelement
percentile_cont(fraction) WITHIN GROUP (ORDER BY double precision) → double precision
percentile_cont(fraction) WITHIN GROUP (ORDER BY interval)   → interval
percentile_cont(fractions[]) WITHIN GROUP (ORDER BY double precision) → double precision[]

-- 5) 与 GROUP BY / FILTER / OVER 组合
stddev(cvr) FILTER (WHERE users > 1000)
stddev(cvr) OVER (PARTITION BY exp_name)

最小可运行示例：

sql
SELECT
  group_name,
  count(*)                          AS days,
  round(avg(cvr), 6)                AS mean_cvr,
  round(stddev(cvr), 6)             AS sd_cvr,
  round(stddev_pop(cvr), 6)         AS sd_pop,
  round(100.0 * stddev(cvr) / avg(cvr), 2) AS cv_pct
FROM ab_test_daily
WHERE exp_name = 'checkout_v2'
GROUP BY group_name
ORDER BY group_name;
text
 group_name | days | mean_cvr |  sd_cvr  |  sd_pop  | cv_pct
------------+------+----------+----------+----------+--------
 control    |    5 | 0.089857 | 0.004368 | 0.003907 |   4.86
 variant    |    5 | 0.109373 | 0.004841 | 0.004330 |   4.43

详细说明 ​

stddev vs stddev_pop：差在分母 ​

两者只差一个分母：

text
样本标准差（stddev / stddev_samp）：s = sqrt( Σ(x - x̄)² / (n - 1) )
总体标准差（stddev_pop）：          σ = sqrt( Σ(x - x̄)² / n )
sql
SELECT
  count(*)               AS n,
  round(variance(x), 8)  AS var_samp,
  round(var_pop(x), 8)   AS var_pop,
  round(variance(x) / var_pop(x), 6) AS ratio   -- 应等于 n/(n-1)
FROM (VALUES (2.0), (4.0), (4.0), (4.0), (5.0), (5.0), (7.0), (9.0)) AS t(x);
text
 n | var_samp  | var_pop  |  ratio
---+-----------+----------+---------
 8 | 4.57142857 | 4.00000000 | 1.142857

n / (n - 1) = 8 / 7 = 1.142857 —— 完全吻合。这意味着 n 越小时两者差异越大：

nn/(n-1)两标准差之比 sqrt(ratio)说明
22.0001.414差异高达 41%
31.5001.225差异 22%
51.2501.118差异 12%
101.1111.054差异 5%
1001.0101.005差异 0.5%
100001.00011.00005可忽略

该选哪个：看"手上这批数据是不是全部"

  • 样本（stddev / variance）：这批数据是从更大总体里抽出来的一部分，你想用它估计总体离散度。绝大多数业务场景（A/B 测试的每日指标、抽样质检、用户行为抽样）都属于这一类。
  • 总体（stddev_pop / var_pop）：你手里的就是全体，不存在"更大的总体"。例如"本季度全部 5 个月"、"全省所有门店"、"系统里全部已注册设备"。

判断口诀：能不能再取到更多同类数据？能 → 样本；不能 → 总体。

Excel 的 STDEV.S / STDEV.P、Python 的 statistics.stdev / statistics.pstdev 与 PostgreSQL 的 stddev / stddev_pop 一一对应。

日常报表里最常见的错误是随手用 stddev 算"全体"的标准差。n 很大时影响可以忽略；但月度报表只有 5～12 个点时，用错函数会把数字抬高 5%～12%，在阈值判定（如"波动率是否超过 5%"）场景下足以改变结论。

单行与零行：一律返回 NULL ​

文档明确："In all cases, null is returned if the computation is meaningless, for example when N is zero." 统计聚合在输入不足时返回 NULL，而不是 0：

sql
SELECT
  stddev(x)      AS sd_one,        -- NULL（1 行无法算样本标准差）
  stddev_pop(x)  AS sd_pop_one,    -- 0（1 行的总体离散度为 0）
  variance(x)    AS var_one,       -- NULL
  var_pop(x)     AS varpop_one     -- 0
FROM (VALUES (42.0)) AS t(x);
text
 sd_one | sd_pop_one | var_one | varpop_one
--------+------------+---------+------------
        |          0 |         |          0
sql
-- 零行：全部 NULL
SELECT stddev(x), stddev_pop(x), variance(x), var_pop(x) FROM (SELECT 1.0 AS x) t WHERE false;
text
 stddev | stddev_pop | variance | var_pop
--------+------------+----------+---------
        |            |          |

这个规则的合理性：一个点的"样本离散度"是未定义的（分母是 n - 1 = 0），但它的"总体离散度"确实是 0。

NULL 会顺着 CASE / 比较运算符静默传播

"波动率超过阈值就告警"的判定在只有 1 天数据时会得到 NULL > 0.05 → NULL，被 WHERE 过滤掉，告警不会触发：

sql
-- 只有 1 天数据的分组会被静默跳过
SELECT group_name, stddev(cvr) AS sd
FROM ab_test_daily GROUP BY group_name
HAVING stddev(cvr) > 0.01;

要保留这类"数据不足"的分组，必须显式兜底：

sql
SELECT group_name,
       stddev(cvr) AS sd,
       CASE WHEN count(*) < 2 THEN 'insufficient_data' ELSE 'ok' END AS data_status
FROM ab_test_daily GROUP BY group_name
HAVING count(*) < 2 OR stddev(cvr) > 0.01;

返回类型：numeric 还是 double precision ​

sql
SELECT
  pg_typeof(stddev(1))          AS int_arg,       -- numeric
  pg_typeof(stddev(1::bigint))  AS bigint_arg,    -- numeric
  pg_typeof(stddev(1.5::numeric)) AS numeric_arg, -- numeric
  pg_typeof(stddev(1.5::real))  AS real_arg,      -- double precision
  pg_typeof(stddev(1.5::float8)) AS float8_arg,   -- double precision
  pg_typeof(corr(1.5, 1.5))     AS corr_arg;      -- double precision
text
 int_arg | bigint_arg | numeric_arg |  real_arg   |  float8_arg  |  corr_arg
---------+------------+-------------+-------------+--------------+-------------
 numeric | numeric    | numeric     | double precision | double precision | double precision

两个实用推论：

  1. 整数列直接算 stddev 会走 numeric 路径，结果精确但比浮点慢。大表上追求速度可以显式转浮点：stddev(x::double precision)（同时也隔离了 numeric 的精度开销）。
  2. 回归/相关函数无论如何都返回 double precision，即使传入 numeric 也会先转浮点。因此 corr 的结果不能期待精确到小数末位，比较时用容差而不是等号：
sql
-- ❌ 浮点等号比较不可靠
SELECT ... WHERE corr(y, x) = 1;

-- ✅ 用容差
SELECT ... WHERE abs(corr(y, x) - 1) < 1e-9;

corr / covar 与它们的等价关系 ​

四个函数之间存在恒等关系，可以作为交叉验证手段：

sql
SELECT
  round(corr(revenue, ad_spend)::numeric, 8)                              AS corr_yx,
  round((covar_pop(revenue, ad_spend)
         / (stddev_pop(revenue) * stddev_pop(ad_spend)))::numeric, 8)     AS corr_from_pop,
  round((covar_samp(revenue, ad_spend)
         / (stddev(revenue) * stddev(ad_spend)))::numeric, 8)             AS corr_from_samp,
  round(power(corr(revenue, ad_spend)::numeric, 2), 8)                    AS r2_from_corr,
  round(regr_r2(revenue, ad_spend)::numeric, 8)                           AS r2_direct
FROM ab_test_daily;
text
 corr_yx  | corr_from_pop | corr_from_samp | r2_from_corr | r2_direct
----------+---------------+----------------+--------------+-----------
 0.99016254 | 0.99016254    | 0.99016254     |  0.98042185  | 0.98042185

要点：

关系公式说明
样本相关covar_samp / (stddev · stddev)用样本版本
总体相关covar_pop / (stddev_pop · stddev_pop)用总体版本
两者相等样本与总体的 n/(n-1) 在同除式中约掉相关性与分母选择无关
判定系数corr^2 = regr_r2r2 恒在 [0, 1]，方向信息靠 corr 的符号

corr 的取值范围是 [-1, 1]，符号表示方向；regr_r2 只能告诉你"拟合得好不好"，看方向必须用 corr 或 regr_slope。

相关系数不能证明因果，也不能识别非线性

corr 只度量线性关系。"广告费与销售额相关性 0.99"不代表加预算就能提销售；而 y = x² 这类完全确定的非线性关系，其 corr 可能是 0。判断非线性要先画散点图（或至少分桶看均值趋势），不要只依赖一个数字。

regr_xxx：手工做最小二乘拟合 ​

回归函数可以逐项还原拟合过程，这是验证"这些函数到底算的是什么"的最好方式：

sql
SELECT
  regr_count(revenue, ad_spend)                                      AS n,
  round(regr_slope(revenue, ad_spend)::numeric, 4)                   AS slope,
  round(regr_intercept(revenue, ad_spend)::numeric, 2)               AS intercept,
  round(regr_r2(revenue, ad_spend)::numeric, 6)                      AS r2,
  round(regr_avgx(revenue, ad_spend)::numeric, 2)                    AS avg_x,
  round(regr_avgy(revenue, ad_spend)::numeric, 2)                    AS avg_y
FROM ab_test_daily;
text
 n  |  slope   | intercept |   r2    |  avg_x  |   avg_y
----+----------+-----------+---------+---------+-----------
 10 | 236.2073 | -721574.20 | 0.980422 | 3285.00 | 54370.00

用同样的六个值可以手工算出斜率，验证恒等式 slope = sxy / sxx：

sql
SELECT
  round((regr_sxy(revenue, ad_spend) / regr_sxx(revenue, ad_spend))::numeric, 4) AS slope_by_sxy_sxx,
  round(regr_slope(revenue, ad_spend)::numeric, 4)                               AS slope_direct,
  round((regr_avgy(revenue, ad_spend)
         - regr_slope(revenue, ad_spend) * regr_avgx(revenue, ad_spend))::numeric, 2) AS intercept_by_formula,
  round(regr_intercept(revenue, ad_spend)::numeric, 2)                           AS intercept_direct
FROM ab_test_daily;
text
 slope_by_sxy_sxx | slope_direct | intercept_by_formula | intercept_direct
------------------+--------------+----------------------+------------------
         236.2073 |     236.2073 |         -721574.20    |      -721574.20

三条恒等关系（都可作为单元测试的断言）：

text
slope     = regr_sxy / regr_sxx
intercept = regr_avgy - slope * regr_avgx
r2        = corr^2

预测式就是一条直线

拿到 slope 与 intercept 后，预测 X = 3500 时的 Y 可以在 SQL 里直接算，也可以存下来在应用层算：

sql
SELECT round((regr_slope(revenue, ad_spend) * 3500
            + regr_intercept(revenue, ad_spend))::numeric, 2) AS predicted_revenue
FROM ab_test_daily;
text
 predicted_revenue
-------------------
          105547.35

注意这是外推：3500 略高于样本区间 [3000, 3600] 内，尚可接受；把 X 拉到 100000 就完全没有依据了。线性拟合只在样本覆盖的 X 区间内可信。

有效行数是"两列同时非 NULL" ​

regr_count(Y, X) 与 count(*) 经常不等。文档定义："Computes the number of rows in which both inputs are non-null."

sql
SELECT
  count(*)                        AS rows_all,
  count(ad_spend)                 AS spend_not_null,
  regr_count(revenue, ad_spend)   AS valid_pairs,
  regr_slope(revenue, ad_spend)   AS slope
FROM ab_test_daily;
text
 rows_all | spend_not_null | valid_pairs |   slope
----------+----------------+-------------+-----------
       10 |             10 |          10 | 236.20730

示例数据里没有 NULL，所以三者相等。一旦 ad_spend 有缺失值：

sql
-- 让 2 行 ad_spend 变 NULL 后再看
SELECT
  count(*)                      AS rows_all,      -- 10
  count(ad_spend)               AS spend_ok,      -- 8
  regr_count(revenue, ad_spend) AS valid_pairs    -- 8
FROM ab_test_daily;

回归函数悄悄丢行，但其他聚合没丢

同一个 SELECT 里，count(*) 用的是 10 行，regr_* 用的是 8 行。如果一行 X 缺失，它对 avg(revenue) 有贡献、对 regr_slope 没有贡献——分母不同。做"斜率 × 样本量"这类复合指标前，务必先确认 regr_count(Y, X) = count(*)，不一致就说明数据有缺口。

有序集聚合：percentile_cont / percentile_disc / mode ​

分位数不用统计函数族，而是有序集聚合（ordered-set aggregate），语法是 WITHIN GROUP (ORDER BY ...)：

sql
SELECT
  percentile_cont(0.5) WITHIN GROUP (ORDER BY cvr) AS p50_cont,
  percentile_disc(0.5) WITHIN GROUP (ORDER BY cvr) AS p50_disc,
  percentile_cont(0.9) WITHIN GROUP (ORDER BY cvr) AS p90,
  mode() WITHIN GROUP (ORDER BY group_name)        AS most_common_group
FROM ab_test_daily;

percentile_cont 与 percentile_disc 的差别在插值：

函数语义[1, 2, 3, 4] 的 p50输出类型
percentile_cont连续插值，可取中间值2.5double precision / interval
percentile_disc离散取值，必是数据中已有的值2与输入同类型
sql
-- 对比同一组数据的两个第 50 百分位
SELECT
  percentile_cont(0.5) WITHIN GROUP (ORDER BY x) AS cont_p50,   -- 2.5
  percentile_disc(0.5) WITHIN GROUP (ORDER BY x) AS disc_p50,   -- 2
  percentile_cont(0.5) WITHIN GROUP (ORDER BY x::int) AS cont_p50_int  -- 2.5（仍为 double precision）
FROM (VALUES (1), (2), (3), (4)) AS t(x);
text
 cont_p50 | disc_p50 | cont_p50_int
----------+----------+--------------
      2.5 |        2 |          2.5

约定：

  • fraction 必须在 [0, 1] 之间，越界直接报错；但 NULL 的 fraction 只产生 NULL 结果，不报错。
  • 一次拿多个分位数用数组形式，只需扫描一遍：percentile_cont(ARRAY[0.5, 0.9, 0.99]) WITHIN GROUP (ORDER BY x)。
  • mode() 返回出现次数最多的值，并列时任意取第一个（文档原文 "arbitrarily choosing the first one"），因此结果是不确定的。
sql
-- 一次取多个分位数
SELECT
  percentile_cont(ARRAY[0.25, 0.5, 0.75, 0.9]) WITHIN GROUP (ORDER BY cvr) AS quartiles
FROM ab_test_daily;
text
                   quartiles
-----------------------------------------------
 {0.0890222222222222,0.0996153846153846,0.106125,0.1109223...}

mode() 并列时结果不确定

"取最常见的值"在存在并列时没有唯一答案。把 mode() 用在订单状态、错误码这类低基数且分布均匀的列上，结果可能在两次执行间变化。需要确定性时改用"按计数排序取第一，并用第二个键打破并列"的写法：

sql
SELECT status FROM t GROUP BY status ORDER BY count(*) DESC, status LIMIT 1;

percentile_cont 也支持 interval 入参，用于算响应时长的中位数：

sql
SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY (finished_at - started_at)) AS median_duration
FROM tasks;

FILTER 与 GROUP BY 的组合 ​

统计聚合同样支持 FILTER，可以在一次扫描里产出"分组 × 条件"的全部统计量：

sql
SELECT
  group_name,
  count(*)                                                        AS days,
  round(stddev(cvr), 6)                                           AS sd_all,
  round(stddev(cvr) FILTER (WHERE users >= 1300), 6)              AS sd_large_days,
  round(stddev(cvr) FILTER (WHERE stat_date >= DATE '2026-03-04'), 6) AS sd_recent
FROM ab_test_daily
GROUP BY group_name
ORDER BY group_name;
text
 group_name | days |  sd_all  | sd_large_days | sd_recent
------------+------+----------+---------------+-----------
 control    |    5 | 0.004368 | 0.002333      | 0.003536
 variant    |    5 | 0.004841 | 0.005586      | 0.005515

使用注意：FILTER 减少的是参与计算的行数，因此 FILTER 后的 n 可能不满足样本标准差的 n >= 1 前提，返回 NULL。多条件报表里要给每个 FILTER 配一个对应的 count(*) FILTER (...)，方便下游判断"这个 NULL 是没数据还是真为零"。

窗口用法：滚动统计 ​

统计聚合作为窗口函数时，天然适合做滚动波动率——默认帧是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，但滚动窗口通常要显式指定 ROWS：

sql
SELECT
  group_name,
  stat_date,
  round(cvr, 6)                                              AS cvr,
  round(avg(cvr) OVER w, 6)                                  AS roll_mean_3d,
  round(stddev(cvr) OVER w, 6)                               AS roll_sd_3d,
  round(100.0 * stddev(cvr) OVER w / avg(cvr) OVER w, 2)     AS roll_cv_pct
FROM ab_test_daily
WHERE exp_name = 'checkout_v2'
WINDOW w AS (PARTITION BY group_name ORDER BY stat_date
             ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
ORDER BY group_name, stat_date;
text
 group_name | stat_date  |   cvr    | roll_mean_3d | roll_sd_3d | roll_cv_pct
------------+------------+----------+--------------+------------+-------------
 control    | 2026-03-01 | 0.090000 | 0.090000     |            |
 control    | 2026-03-02 | 0.090000 | 0.090000     | 0.000000   |        0.00
 control    | 2026-03-03 | 0.084800 | 0.088267     | 0.003001   |        3.40
 control    | 2026-03-04 | 0.090000 | 0.088267     | 0.003001   |        3.40
 control    | 2026-03-05 | 0.090370 | 0.088390     | 0.003051   |        3.45
 variant    | 2026-03-01 | 0.108475 | 0.108475     |            |
 variant    | 2026-03-02 | 0.110078 | 0.110277     | 0.001134   |        1.03
 variant    | 2026-03-03 | 0.105645 | 0.108066     | 0.002243   |        2.08
 variant    | 2026-03-04 | 0.110145 | 0.108623     | 0.002527   |        2.33
 variant    | 2026-03-05 | 0.109023 | 0.108271     | 0.002325   |        2.15

两个要点：

  1. 窗口的第 1 行还是 NULL（只有 1 个点），与聚合版语义一致；
  2. 必须写 ROWS BETWEEN ...，默认的 RANGE 帧按值而非按行数计算，在有重复值时行为不符合直觉。窗口帧细节见 窗口聚合函数。

实战示例 ​

第 1 步：A/B 测试的分组统计概览 ​

sql
SELECT
  group_name,
  count(*)                     AS days,
  sum(users)                   AS users,
  sum(conversions)             AS conversions,
  round(sum(conversions)::numeric / sum(users), 6)  AS cvr_pooled,
  round(avg(cvr), 6)           AS cvr_mean,
  round(stddev(cvr), 6)        AS cvr_sd,
  round(stddev(cvr) / sqrt(count(*)), 6) AS cvr_se
FROM ab_test_daily
WHERE exp_name = 'checkout_v2'
GROUP BY group_name
ORDER BY group_name;
text
 group_name | days | users | conversions | cvr_pooled | cvr_mean | cvr_sd   | cvr_se
------------+------+-------+-------------+------------+----------+----------+---------
 control    |    5 |  6500 |         579 |   0.089077 | 0.089857 | 0.004368 | 0.001953
 variant    |    5 |  6420 |         698 |   0.108723 | 0.109373 | 0.004841 | 0.002165

注意 cvr_pooled 与 cvr_mean 不相等——前者是"总转化 / 总用户"，后者是"每日 CVR 的算术平均"。两者都是合法口径，但结论可能不同。报表里必须注明用的是哪个。

第 2 步：效应量与显著性判定 ​

sql
WITH stats AS (
  SELECT
    group_name,
    avg(cvr)                    AS mean_cvr,
    stddev(cvr)                 AS sd_cvr,
    count(*)                    AS n
  FROM ab_test_daily
  WHERE exp_name = 'checkout_v2'
  GROUP BY group_name
),
p AIRED AS (
  SELECT
    max(mean_cvr) FILTER (WHERE group_name = 'control') AS c_mean,
    max(sd_cvr)   FILTER (WHERE group_name = 'control') AS c_sd,
    max(n)        FILTER (WHERE group_name = 'control') AS c_n,
    max(mean_cvr) FILTER (WHERE group_name = 'variant') AS v_mean,
    max(sd_cvr)   FILTER (WHERE group_name = 'variant') AS v_sd,
    max(n)        FILTER (WHERE group_name = 'variant') AS v_n
  FROM stats
)
SELECT
  round(100.0 * (v_mean - c_mean) / c_mean, 2)                                    AS lift_pct,
  round(sqrt(c_sd^2 / c_n + v_sd^2 / v_n), 6)                                      AS se_diff,
  round((v_mean - c_mean) / sqrt(c_sd^2 / c_n + v_sd^2 / v_n), 3)                   AS t_stat
FROM paired;
text
 lift_pct |  se_diff  | t_stat
----------+-----------+--------
    21.72 |  0.002917 | 6.682

这是双样本 t 检验的合并标准误形式（Welch 近似）。要点：

  • stddev 与 count 必须成对使用（都来自每天的样本）；se_diff 的分母是"组内标准误的平方和开根号"；
  • t_stat 的绝对值越大越显著，|t| > 2 大致对应 95% 置信（大样本下）；
  • 用 max(...) FILTER (WHERE ...) 是标准行转列手法，把两行统计量摊平成一行，这样 lift_pct / t_stat 才能在同一行里算。

FILTER 与 max 的配合要注意 NULL

max(...) FILTER (WHERE group_name = 'control') 在某个分组缺失时返回 NULL，整个表达式变 NULL。上线前用 count(*) FILTER (WHERE group_name IN ('control','variant')) = 2 做前置断言，避免"实验组没数据"时静默产出 NULL 报表。

第 3 步：相关性分析（收入 vs 广告花费） ​

sql
SELECT
  regr_count(revenue, ad_spend)                     AS valid_days,
  round(corr(revenue, ad_spend)::numeric, 6)        AS corr_r,
  round(regr_r2(revenue, ad_spend)::numeric, 6)     AS r2,
  round(regr_slope(revenue, ad_spend)::numeric, 4)  AS slope,
  round(regr_intercept(revenue, ad_spend)::numeric, 2) AS intercept
FROM ab_test_daily;
text
 valid_days |  corr_r   |   r2    |  slope   | intercept
------------+-----------+---------+----------+-----------
         10 | 0.990163  | 0.980422 | 236.2073 | -721574.20

第 4 步：分位数看分布形状 ​

均值与标准差描述不了偏态分布。补齐分位数维度：

sql
SELECT
  group_name,
  round(avg(cvr), 6)                                            AS mean_cvr,
  round(percentile_cont(0.5)  WITHIN GROUP (ORDER BY cvr), 6)    AS p50,
  round(percentile_cont(0.9)  WITHIN GROUP (ORDER BY cvr), 6)    AS p90,
  round(percentile_disc(0.5)  WITHIN GROUP (ORDER BY cvr), 6)    AS p50_disc,
  round(stddev(cvr), 6)                                         AS sd
FROM ab_test_daily
GROUP BY group_name
ORDER BY group_name;
text
 group_name | mean_cvr |   p50    |   p90    | p50_disc |   sd
------------+----------+----------+----------+----------+---------
 control    | 0.089857 | 0.090000 | 0.090000 | 0.090000 | 0.004368
 variant    | 0.109373 | 0.110078 | 0.110922 | 0.110078 | 0.004841

mean 与 p50 接近说明分布大致对称；差异大就说明有偏态或长尾，此时标准差作为"波动"的代表性会打折扣。

第 5 步：滚动波动率（窗口） ​

见"详细说明 → 窗口用法"。把 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 改成 6 PRECEDING 就得到 7 日滚动波动率；注意窗口越大，前几行的 NULL 越多。

第 6 步：恒等式自检 ​

把三条恒等关系做成断言，任何一条不成立就说明数据或写法有问题：

sql
SELECT
  abs(regr_slope(revenue, ad_spend)
      - regr_sxy(revenue, ad_spend) / regr_sxx(revenue, ad_spend)) < 1e-9  AS slope_ok,
  abs(regr_intercept(revenue, ad_spend)
      - (regr_avgy(revenue, ad_spend)
         - regr_slope(revenue, ad_spend) * regr_avgx(revenue, ad_spend))) < 1e-9 AS intercept_ok,
  abs(regr_r2(revenue, ad_spend) - power(corr(revenue, ad_spend), 2)) < 1e-9      AS r2_ok
FROM ab_test_daily;
text
 slope_ok | intercept_ok | r2_ok
----------+--------------+-------
 t        | t            | t

注意事项 ​

  1. stddev 是 stddev_samp 的历史别名，variance 是 var_samp 的别名，两者分母都是 n-1；要 n 分母必须显式写 stddev_pop / var_pop。
  2. regr_* 与 corr / covar_* 的参数顺序是 (Y, X)：因变量在前。传反不会报错，但结果毫无意义。
  3. 单行输入时 stddev / variance 返回 NULL，而 stddev_pop / var_pop 返回 0；零行时全部返回 NULL。
  4. stddev / variance 对整数与 numeric 入参返回 numeric，对浮点返回 double precision；corr / covar_* / regr_* 恒为 double precision（regr_count 为 bigint）。
  5. regr_count(Y, X) 统计的是两列同时非 NULL 的行数，常小于 count(*)；同一查询里两者混用会导致分母不一致。
  6. percentile_cont(fraction) 的 fraction 必须在 [0, 1] 之间，越界报错；NULL 的 fraction 返回 NULL 而不报错。
  7. percentile_disc 返回数据中真实存在的值，percentile_cont 会插值，输出类型也不同（前者与输入同类型，后者 double precision / interval）。
  8. mode() 在并列时任意取值，结果不确定，不要依赖它在低基数均匀分布列上的输出。
  9. corr 只衡量线性关系，y = x² 的 corr 可能为 0；判定因果更需要实验设计而非相关系数。
  10. corr / regr_* 的结果是浮点，比较时用容差（abs(a - b) < 1e-9）而非等号。
  11. FILTER 之后的 n 可能不足，样本标准差会因此返回 NULL；报表里为每个 FILTER 统计量配一个对应的 count(*) FILTER。
  12. 窗口形式的第 1 行必然是 NULL（只有 1 个点），且应显式写 ROWS BETWEEN ... 而不是依赖默认的 RANGE 帧。

最佳实践 ​

  1. 默认用 stddev / variance（样本版），只有在"数据即全体"的场景才用 _pop 版本，并把选择理由写在字段注释或报表说明里。
  2. 报表里同时输出 mean 与 sd，只给均值无法判断"波动是否可接受"。
  3. 样本量很小时标注 n（count(*) 与 regr_count 都带上），避免读者把 3 个点的标准差当成稳定结论。
  4. 对 stddev 的结果做 round，或显式 ::numeric(12,6)，避免把 30 位小数暴露给前端。
  5. regr_slope(Y, X) 在注释里写明 Y / X 的业务含义，参数顺序错误是这一族函数最高频且最隐蔽的 bug：
    sql
    regr_slope(revenue, ad_spend)  -- revenue = Y（因变量）, ad_spend = X（自变量）
  6. 回归前先查 regr_count 是否等于 count(*)，不等就说明有缺失值，先处理数据再说。
  7. 拟合只在样本 X 区间内解释，外推值必须在报表里标注"超出样本范围"。
  8. 分位数与均值一起给，偏态分布下均值会误导；percentile_cont(ARRAY[0.5, 0.9, 0.99]) 一次拿三个。
  9. mode() 只在确实需要"最常见值"且并列影响可接受时使用，否则改写为 ORDER BY count(*) DESC LIMIT 1。
  10. A/B 测试的分母口径统一：要么全部用池化值（sum/sum），要么全部用逐日均值的平均，绝不在同一个指标上混用。
  11. 把三条恒等式写成回归测试（slope = sxy/sxx、intercept = avgy - slope*avgx、r2 = corr^2），能一次性抓出参数顺序错误与 NULL 处理错误。
  12. 大表上优先转 double precision 再统计：stddev(x::double precision) 走浮点路径更快；numeric 路径只在需要精确小数时保留。

下一节

继续学习：FILTER 子句

参考链接 ​