在BigQuery中计算百分位数

Question

我正在使用BigQuery，需要计算数据集中一列的25th、50th和75th百分位数。

例如，如何使用BigQuery和标准SQL获取上述数字？我查看了PERCENT_RANK、RANK和NTILE函数，但似乎无法解决问题。

以下是一些可能指导我的代码。

感谢帮助！

- Praangrammer

---

你说“这是一些可能指引我的代码”，意思是你想包含一些示例代码吗？ - lebelinoz

Answer 1

查看Standard SQL中的APPROX_QUANTILES函数。如果您要请求100个分位数，则会得到百分位数。因此，查询将如下所示：

SELECT percentiles[offset(25)], percentiles[offset(50)], percentiles[offset(75)]
FROM (SELECT APPROX_QUANTILES(column, 100) percentiles FROM Table)

- Mosha Pasumansky

---

有没有一种方法可以获取精确的百分位数值？看起来APPROX_QUANTILES的聚合是近似的... - Hoda

Answer 2

如果近似聚合不适用于您的情况，您可能需要使用PERCENTILE_CONT函数（尽管它会使用更多内存，因此可能无法处理大量数据），例如以下示例来自这里

SELECT
  PERCENTILE_CONT(x, 0) OVER() AS min,
  PERCENTILE_CONT(x, 0.01) OVER() AS percentile1,
  PERCENTILE_CONT(x, 0.5) OVER() AS median,
  PERCENTILE_CONT(x, 0.9) OVER() AS percentile90,
  PERCENTILE_CONT(x, 1) OVER() AS max
FROM UNNEST([0, 3, NULL, 1, 2]) AS x LIMIT 1;

+-----+-------------+--------+--------------+-----+
| min | percentile1 | median | percentile90 | max |
+-----+-------------+--------+--------------+-----+
| 0   | 0.03        | 1.5    | 2.7          | 3   |
+-----+-------------+--------+--------------+-----+

- Hoda

---

为了完整起见，这展示了如何使用“PERCENTILE_CONT”作为聚合函数：https://dev59.com/UVcO5IYBdhLWcg3wkij2#45579962 - arielhasidim

Answer 3

您可以使用以下代码获得细粒度的百分位数分析。

  SELECT
  offset + 1 AS percentile,
  value AS percentile_value,
  FROM UNNEST(( 
    SELECT 
      APPROX_QUANTILES(value, 100) as percentile_value,
    FROM data_set
  )) AS percentile_value  WITH OFFSET as offset

然后您可以根据需要获得任何百分位。

- Hui Zheng