在BigQuery中计算列的差值中位数：分析函数不能作聚合函数参数的解决办法

问题核心是SQL不允许在聚合函数（比如AVG、PERCENTILE_CONT）里直接嵌套分析函数（比如LAG），得先把分析函数计算出的差值作为独立列生成一个中间结果集，再对这个结果集做聚合计算。

方法一：用子查询生成差值列 #

先通过子查询算出所有id的差值，过滤掉第一个行的null值，再计算中位数：

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY id_diff) AS median_diff
FROM (
    SELECT id - LAG(id) OVER (ORDER BY id) AS id_diff
    FROM `table`
) AS sub_query
WHERE id_diff IS NOT NULL;

方法二：用CTE（公共表表达式）生成差值列 #

CTE写法更直观，逻辑和子查询一致：

WITH id_diffs AS (
    SELECT id - LAG(id) OVER (ORDER BY id) AS id_diff
    FROM `table`
)
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY id_diff) AS median_diff
FROM id_diffs
WHERE id_diff IS NOT NULL;

适配不同数据库的中位数写法 #

• 如果用MySQL 8.0及以上版本，可直接用MEDIAN函数简化：

WITH id_diffs AS (
    SELECT id - LAG(id) OVER (ORDER BY id) AS id_diff
    FROM `table`
)
SELECT MEDIAN(id_diff) AS median_diff
FROM id_diffs
WHERE id_diff IS NOT NULL;

• PostgreSQL或SQL Server也可以用PERCENTILE_DISC(0.5)，它会返回实际存在的差值中的中位数（比如你的示例里会返回5，和PERCENTILE_CONT结果一致）。

关键说明 #

• 必须过滤id_diff IS NOT NULL：因为排序后的第一个id没有前一个值，LAG(id)返回null，对应的差值也是null，这个值不需要参与中位数计算。
• 先计算差值再聚合：把分析函数的结果先落地为中间表/子查询的列，再让聚合函数处理这个列，就能避开语法限制。

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