Вычислительные процентили в BigQuery

Question

Я использую BigQuery, и мне нужно вычислить 25-й, 50-й и 75-й процентиль столбца набора данных.

Например, как я могу получить вышеупомянутые числа, используя BigQuery и STANDARD SQL. Я посмотрел на функции PERCENT_RANK, RANK и NTILE, но, похоже, не могу их взломать.

Вот код, который может помочь мне

Ценю помощь!

Answer 1

Проверьте функцию APPROX_QUANTILES в стандартном SQL. Если вы попросите 100 квантилей - вы получите процентили. Таким образом, запрос будет выглядеть следующим образом:

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

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   |
+-----+-------------+--------+--------------+-----+

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

Затем вы можете получить любой процентиль по мере необходимости.