在AWS Athena中为有限行的百分比字段计算中位数窗口函数

你的SQL语句存在两个核心问题：

1. 窗口框架错误：需求是「前3行（含当前行）」，即窗口包含当前行及之前2行（共3行），但原语句用ROWS BETWEEN 3 PRECEDING AND CURRENT ROW会包含当前行及之前3行（共4行），不符合需求。
2. 函数适配问题：APPROX_PERCENTILE是近似百分位函数，多数SQL引擎中它作为窗口函数时不支持滑动窗口的ROWS框架，且近似计算会导致结果偏差。

以下是不同SQL引擎的精确实现方案：

PostgreSQL 实现 #

PostgreSQL支持PERCENTILE_CONT/PERCENTILE_DISC窗口函数结合滑动框架，直接计算精确中位数：

SELECT
  date,
  company_id,
  capacity_percentage,
  -- PERCENTILE_CONT返回连续型中位数，PERCENTILE_DISC返回离散型，按需选择
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY capacity_percentage) OVER (
    PARTITION BY company_id
    ORDER BY date ASC
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS median_capacity
FROM your_table;

Spark SQL 实现 #

通过collect_list收集窗口内数据并排序，再通过下标取中位数：

SELECT
  date,
  company_id,
  capacity_percentage,
  CASE
    WHEN size(window_vals) = 1 THEN window_vals[0]
    WHEN size(window_vals) = 2 THEN (window_vals[0] + window_vals[1])/2
    WHEN size(window_vals) = 3 THEN window_vals[1]
  END AS median_capacity
FROM (
  SELECT
    date,
    company_id,
    capacity_percentage,
    array_sort(collect_list(capacity_percentage) OVER (
      PARTITION BY company_id
      ORDER BY date ASC
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    )) AS window_vals
  FROM your_table
) t;

BigQuery 实现 #

BigQuery窗口函数不支持PERCENTILE_CONT结合ROWS框架，需用ARRAY_AGG收集排序后的数据再计算：

SELECT
  date,
  company_id,
  capacity_percentage,
  CASE
    WHEN ARRAY_LENGTH(sorted_vals) = 1 THEN sorted_vals[OFFSET(0)]
    WHEN ARRAY_LENGTH(sorted_vals) = 2 THEN (sorted_vals[OFFSET(0)] + sorted_vals[OFFSET(1)])/2
    WHEN ARRAY_LENGTH(sorted_vals) = 3 THEN sorted_vals[OFFSET(1)]
  END AS median_capacity
FROM (
  SELECT
    date,
    company_id,
    capacity_percentage,
    ARRAY_AGG(capacity_percentage ORDER BY capacity_percentage) OVER (
      PARTITION BY company_id
      ORDER BY date ASC
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS sorted_vals
  FROM your_table
) t;

内容的提问来源于stack exchange，提问作者J-snow