Перемещение (прокручивание) медианы с помощью BigQuery

В настоящее время у меня есть таблица в BigQuery, которая содержит некоторые выбросы, и я хотел бы рассчитать скользящую медиану для этой таблицы.

Пример таблицы:

port - qty - datetime
--------------------------------
TCP1 - 13 - 2018/06/11 11:20:23
UDP2 - 15 - 2018/06/11 11:24:24
TCP3 - 12 - 2018/06/11 11:24:27
TCP1 - 2  - 2018/06/12 11:24:26 
UDP2 - 15 - 2018/06/12 11:35:32
TCP3 - 200- 2018/06/13 11:45:23
TCP3 - 14 - 2018/06/13 11:54:22
TCP3 - 13 - 2018/06/14 11:55:33
TCP1 - 17 - 2018/06/15 11:43:33
UDP2 - 12 - 2018/06/15 11:55:25
TCP3 - 14 - 2018/06/15 11:26:21
TCP3 - 11 - 2018/06/16 11:55:46
TCP1 - 14 - 2018/06/17 11:34:33
UDP2 - 15 - 2018/06/17 11:43:24
TCP3 - 13 - 2018/06/17 11:47:54
and ...

Я хотел бы иметь возможность рассчитать 7-дневную медиану на различных портах в 11 часов, используя стандартный SQL для больших запросов. Я попытался вычислить скользящее среднее, но понял, что на расчеты влияет «выброс».

Я не знаю, как написать SQL-запрос для вычисления движущейся медианы. Любая помощь будет оценена.

(это ближайшая ветка, которую я смог найти по этой теме: BigQuery — вычисление движущейся медианы, но Мне нужен bigquery, чтобы получить количество из таблицы, так как я не знаю точно количество кол-во на каждый конкретный день)


sql google-bigquery median bigquery-standard-sql
person taN    schedule 26.12.2018    source источник
comment
слишком широкий - вы можете представить ожидаемый результат на основе примера ввода в вашем вопросе   -  person Mikhail Berlyant    schedule 26.12.2018

Ответы (1)


arrow_upward
2
arrow_downward

Я думаю, что это достаточно близко к тому, что вы хотите:

select t.*,
       qtys[ordinal(cast(array_length(qtys) / 2 as int64))]
from (select t.*,
             array_agg(qty) over (partition by port
                                  order by datetime_diff(datetime, datetime('2000-01-01'), day)
                                  range between 7 preceding and current day
                                 ) as qtys
      from t
      where extract(hour from datetime) = 11
     ) t;

Медианы немного сложны, когда в результирующем наборе четное количество строк. Это выбирает произвольное значение.

person Gordon Linoff    schedule 26.12.2018
comment
привет, хотел бы спросить, что делает порядок по datetime_diff (datetime, datetime ('2000-01-01'), день) в диапазоне между 7 предыдущими и текущими днями ?? Повлияет ли это на расчет скользящей медианы на 1 месяц раньше? - person taN; 26.12.2018
comment
@taN . . . array_agg() не поддерживает оконное предложение range для дат/даты/времени/временных меток. Но он поддерживает оконное предложение range для чисел. Таким образом, это преобразует даты в числа. - person Gordon Linoff; 26.12.2018