google-bigquery - 如何有效地计算 Google BigQuery 中数字序列的中位数？

Question

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

Answer 1

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

Returns values that are based upon linear interpolation between the values of the group, after ordering them per the ORDER BY clause.

must be between 0 and 1.

This window function requires ORDER BY in the OVER clause.

所以一个示例查询就像(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 |
+------+-------------+