Impala分组手动计算带频率的总体标准差SQL实现方案

行展开方案在Impala中无原生支持的表函数，依赖递归或序列表实现的性能极差，不推荐使用。直接通过加权总体标准差的代数展开式做分组聚合计算，无需窗口函数、无需行展开，一次GROUP BY即可得到正确结果，是最优实现方式。

计算逻辑 #

针对带频率权重的总体标准差，原生计算公式为：

总体标准差 $\sigma = \sqrt{\frac{\sum f_i*(x_i-\mu)^2}{N}}$
其中：

• $x_i$ 为values字段值，$f_i$为frequencies字段值
• $N$ 为分组总样本量：$N = \sum f_i$
• $\mu$ 为分组加权均值：$\mu = \frac{\sum f_i*x_i}{N}$

对离均差平方和做代数展开后，可完全消除提前计算均值带来的关联、窗口函数依赖，展开后离均差平方和等价于：

$\sum f_i*(x_i-\mu)^2 = \sum f_ix_i^2 - \frac{(\sum f_ix_i)^2}{N}$

实现代码 #

将源表名替换为实际表名即可执行，注意values是SQL保留字，需要用反引号包裹避免语法错误：

SELECT
  grpng,
  SQRT(
    (SUM(frequencies * POW(`values`, 2)) - POW(SUM(frequencies * `values`), 2) / SUM(frequencies))
    / SUM(frequencies)
  ) AS sddev
FROM your_table
GROUP BY grpng;

结果校验 #

基于提供的样例数据代入计算：

• cat1分组：总样本量N=4，加权值总和=12，加权平方和=38，计算得$\sqrt{(38 - 12^2/4)/4}=\sqrt{0.5}≈0.70710678118655$，与预期值一致
• cat2分组：总样本量N=5，加权值总和=15，加权平方和=59，计算得$\sqrt{(59 - 15^2/5)/5}=\sqrt{2.8}≈1.6733200530682$，与预期值一致

内容的提问来源于stack exchange，提问作者Dimitris Petridis