在BigQuery及标准SQL中实现偏度与峰度计算

由于BigQuery默认未提供这两个统计量，且普通UDF不支持聚合/窗口操作，我们可以通过内置聚合函数组合或**自定义SQL聚合函数（UDAF）**来实现，完全贴合pandas/scipy的计算逻辑。

一、核心公式说明 #

• 偏度（Skewness）：衡量数据分布的不对称程度，scipy/pandas默认使用样本偏度，公式为：

  样本偏度 = [n / ((n-1)*(n-2))] * [E[(X-μ)³] / σ³]
  

  （n为样本量，μ为均值，σ为总体标准差）

• 峰度（Kurtosis）：衡量数据分布的陡峭程度，pandas/scipy默认返回超额峰度（Excess Kurtosis），即原始峰度减3，样本超额峰度公式为：

  样本超额峰度 = [n(n+1)/((n-1)(n-2)(n-3))] * [E[(X-μ)⁴]/σ⁴] - [3(n-1)²/((n-2)(n-3))]
  

二、直接用SQL计算（无需创建函数） #

1. 分组计算偏度和峰度 #

WITH base_stats AS (
  SELECT
    group_id, -- 替换为你的分组字段
    COUNT(value) AS n,
    AVG(value) AS mu,
    STDDEV_POP(value) AS sigma,
    SUM(POW(value - AVG(value), 3)) AS third_moment,
    SUM(POW(value - AVG(value), 4)) AS fourth_moment
  FROM your_table -- 替换为你的表名
  GROUP BY group_id
)
SELECT
  group_id,
  -- 样本偏度（对应scipy.skew()、pandas.Series.skew()）
  CASE WHEN sigma > 0 THEN (n / ((n-1)*(n-2))) * (third_moment / POW(sigma, 3)) ELSE NULL END AS skewness,
  -- 样本超额峰度（对应scipy.kurtosis(fisher=True)、pandas.Series.kurt()）
  CASE WHEN sigma > 0 THEN
    (n*(n+1)/((n-1)*(n-2)*(n-3))) * (fourth_moment/(POW(sigma,4)*n)) - (3*POW(n-1,2)/((n-2)*(n-3)))
  ELSE NULL END AS excess_kurtosis
FROM base_stats;

2. 窗口函数计算（按分组滑动统计） #

如果需要在窗口范围内计算（比如按用户、时间分区），直接将GROUP BY替换为窗口子句：

SELECT
  *,
  -- 窗口样本偏度
  CASE WHEN STDDEV_POP(value) OVER w > 0 THEN
    (COUNT(value) OVER w) / ((COUNT(value) OVER w -1)*(COUNT(value) OVER w -2)) *
    (SUM(POW(value - AVG(value) OVER w, 3)) OVER w) / POW(STDDEV_POP(value) OVER w, 3)
  ELSE NULL END AS window_skewness,
  -- 窗口样本超额峰度
  CASE WHEN STDDEV_POP(value) OVER w > 0 THEN
    (COUNT(value) OVER w*(COUNT(value) OVER w+1))/((COUNT(value) OVER w-1)*(COUNT(value) OVER w-2)*(COUNT(value) OVER w-3)) *
    (SUM(POW(value - AVG(value) OVER w, 4)) OVER w)/(POW(STDDEV_POP(value) OVER w,4)*COUNT(value) OVER w) -
    (3*POW(COUNT(value) OVER w-1,2))/((COUNT(value) OVER w-2)*(COUNT(value) OVER w-3))
  ELSE NULL END AS window_excess_kurtosis
FROM your_table
WINDOW w AS (PARTITION BY group_id); -- 替换为你的窗口分区字段

三、创建可复用的自定义聚合函数（UDAF） #

如果需要多次使用，可以创建SQL UDAF，像内置函数一样直接调用：

1. 创建样本偏度函数 #

CREATE OR REPLACE FUNCTION `your_project.your_dataset.skewness`(val NUMERIC)
RETURNS NUMERIC
AGGREGATE AS (
  WITH agg_stats AS (
    SELECT
      COUNT(val) AS n,
      AVG(val) AS mu,
      STDDEV_POP(val) AS sigma,
      SUM(POW(val - AVG(val), 3)) AS third_moment
  )
  SELECT CASE WHEN sigma > 0 THEN (n / ((n-1)*(n-2))) * (third_moment / POW(sigma, 3)) ELSE NULL END
);

2. 创建样本超额峰度函数 #

CREATE OR REPLACE FUNCTION `your_project.your_dataset.excess_kurtosis`(val NUMERIC)
RETURNS NUMERIC
AGGREGATE AS (
  WITH agg_stats AS (
    SELECT
      COUNT(val) AS n,
      AVG(val) AS mu,
      STDDEV_POP(val) AS sigma,
      SUM(POW(val - AVG(val), 4)) AS fourth_moment
  )
  SELECT CASE WHEN sigma > 0 THEN
    (n*(n+1)/((n-1)*(n-2)*(n-3))) * (fourth_moment/(POW(sigma,4)*n)) - (3*POW(n-1,2)/((n-2)*(n-3)))
  ELSE NULL END
);

调用自定义函数 #

-- 分组统计
SELECT
  group_id,
  `your_project.your_dataset.skewness`(value) AS skewness,
  `your_project.your_dataset.excess_kurtosis`(value) AS excess_kurtosis
FROM your_table
GROUP BY group_id;

-- 窗口统计
SELECT
  *,
  `your_project.your_dataset.skewness`(value) OVER (PARTITION BY group_id) AS window_skewness,
  `your_project.your_dataset.excess_kurtosis`(value) OVER (PARTITION BY group_id) AS window_excess_kurtosis
FROM your_table;

注意事项 #

• 如果处理浮点型数据，可将函数中的NUMERIC替换为FLOAT64；
• 当样本量n<3时，偏度计算会返回NULL（因公式分母为0）；n<4时，超额峰度返回NULL；
• 计算逻辑完全对齐scipy和pandas的默认参数，确保统计结果一致。

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