如何在谷歌大查询中有效地计算数字序列的中位数

Question

我需要有效地计算Google BigQuery中数字序列的中值。可能一样吗？

Answer 1

是的，可以使用PERCENTILE_CONT窗口函数。

返回的值基于以下 组的值，在按照 ORDER BY 子句对它们进行排序后。

必须介于 0 和 1 之间。

此窗口函数需要在 OVER 子句中执行 ORDER BY。

所以一个示例查询就像（max（）只是为了跨组工作，但它没有被用作数学逻辑，不应该让你感到困惑）

SELECT room,
      max(median) FROM   (SELECT room,
         percentile_cont(0.5) OVER (PARTITION BY room
                                    ORDER BY temperature) AS median    FROM
    (SELECT 1 AS room,
            11 AS temperature),
    (SELECT 1 AS room,
            12 AS temperature),
    (SELECT 1 AS room,
            14 AS temperature),
    (SELECT 1 AS room,
            19 AS temperature),
    (SELECT 1 AS room,
            13 AS temperature),
    (SELECT 2 AS room,
            20 AS temperature),
    (SELECT 2 AS room,
            21 AS temperature),
    (SELECT 2 AS room,
            29 AS temperature),
    (SELECT 3 AS room,
            30 AS temperature)) GROUP BY room

这将返回：

+------+-------------+
| room | temperature |
+------+-------------+
|    1 |          13 |
|    2 |          21 |
|    3 |          30 |
+------+-------------+

Answer 2

替代解决方案，当您不需要绝对精确的结果并且近似值很好时 - 您可以使用 N 和分位数聚合函数的组合。此方法的优点是它比分析窗口函数更具可扩展性，但缺点是它给出了近似结果。

SELECT room,
       NTH(50, QUANTILES(temperature, 101)) FROM
    (SELECT 1 AS room,
            11 AS temperature),
    (SELECT 1 AS room,
            12 AS temperature),
    (SELECT 1 AS room,
            14 AS temperature),
    (SELECT 1 AS room,
            19 AS temperature),
    (SELECT 1 AS room,
            13 AS temperature),
    (SELECT 2 AS room,
            20 AS temperature),
    (SELECT 2 AS room,
            21 AS temperature),
    (SELECT 2 AS room,
            29 AS temperature),
    (SELECT 3 AS room,
            30 AS temperature) GROUP BY room

这返回

room temperature 
1    13  
2    21  
3    30

Answer 3

2018 年更新，包含更多指标：

BigQuery SQL：平均值、几何平均值、删除异常值、中位数

---

出于我自己的记忆目的，使用出租车数据进行查询：

近似分位数：

SELECT MONTH(pickup_datetime) month, NTH(51, QUANTILES(tip_amount,101)) median
FROM [nyc-tlc:green.trips_2015]
WHERE tip_amount > 0
GROUP BY 1
ORDER BY 1

给出与 PERCENTILE_DISC 相同的结果：

SELECT month, FIRST(median) median
FROM (
  SELECT MONTH(pickup_datetime) month, tip_amount, PERCENTILE_DISC(0.5) OVER(PARTITION BY month ORDER BY tip_amount) median
  FROM [nyc-tlc:green.trips_2015]
  WHERE tip_amount > 0
)
GROUP BY 1
ORDER BY 1

标准SQL：

#StandardSQL
SELECT DATE_TRUNC(DATE(pickup_datetime), MONTH) month, APPROX_QUANTILES(tip_amount,1000)[OFFSET(500)] median
FROM `nyc-tlc.green.trips_2015`
WHERE tip_amount > 0
GROUP BY 1
ORDER BY 1