Перемещение (переходящий) медиана с BigQuery

Question

В настоящее время у меня есть таблица в 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 BigQuery. Я попытался вычислить скользящее среднее, но понял, что на вычисления влияет "выброс".

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

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

Answer 1

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

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;

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