BigQuery SQL для агрегата со скользящим окном за 28 дней (без написания 28 строк SQL)

Я пытаюсь вычислить 28-дневную скользящую сумму в BigQuery с помощью функции LAG.

Главный ответ на этот вопрос

Bigquery SQL для агрегатора скользящего окна

от Фелипе Хоффа указывает, что вы можете использовать функцию LAG. Примером этого может быть:

SELECT
    spend + spend_lagged_1day + spend_lagged_2day + spend_lagged_3day + ... +  spend_lagged_27day as spend_28_day_sum,
    user,
    date
FROM (
  SELECT spend,
         LAG(spend, 1) OVER (PARTITION BY user ORDER BY date) spend_lagged_1day,
         LAG(spend, 2) OVER (PARTITION BY user ORDER BY date) spend_lagged_2day,
         LAG(spend, 3) OVER (PARTITION BY user ORDER BY date) spend_lagged_3day,
         ...
         LAG(spend, 28) OVER (PARTITION BY user ORDER BY date) spend_lagged_day,
         user,
         date
  FROM user_spend
)

Есть ли способ сделать это, не выписывая 28 строк SQL!


sql google-bigquery sliding-window
person alan    schedule 19.12.2014    source источник

Ответы (3)


arrow_upward
41
arrow_downward

Документация BigQuery не очень хорошо объясняет сложность оконных функций, которые поддерживает инструмент, поскольку в ней не указывается, какие выражения могут появляться после ROWS или RANGE. Фактически он поддерживает стандарт SQL 2003 для оконных функций, который вы можете найти в других местах в Интернете, задокументированных, например, здесь.

Это означает, что вы можете получить желаемый эффект с помощью функции одного окна. Диапазон равен 27, потому что это количество строк перед текущей, которое нужно включить в сумму.

SELECT spend,
       SUM(spend) OVER (PARTITION BY user ORDER BY date ROWS BETWEEN 27 PRECEDING AND CURRENT ROW),
       user,
       date
FROM user_spend;

Граница ДИАПАЗОНА также может быть чрезвычайно полезной. Если в вашей таблице отсутствуют даты для какого-то пользователя, тогда 27 PRECEDING строк вернутся более чем на 27 дней, но RANGE создаст окно, основанное на самих значениях даты. В следующем запросе поле даты - это TIMESTAMP BigQuery, а диапазон указан в микросекундах. Я бы посоветовал, чтобы каждый раз, когда вы выполняете такую ​​математику в BigQuery, вы тщательно тестируете ее, чтобы убедиться, что она дает ожидаемый ответ.

SELECT spend,
       SUM(spend) OVER (PARTITION BY user ORDER BY date RANGE BETWEEN 27 * 24 * 60 * 60 * 1000000 PRECEDING AND CURRENT ROW),
       user,
       date
FROM user_spend;
person sprocket    schedule 19.12.2014

arrow_upward
14
arrow_downward

Bigquery: Как получить скользящий временной диапазон в оконном предложении .....

Это старый пост, но я долго искал решение, и этот пост появился, так что, возможно, это кому-то поможет.

ЕСЛИ ваш раздел вашего оконного предложения не имеет записи для каждого дня, вам нужно использовать предложение RANGE, чтобы точно получить скользящий временной диапазон (ROWS будет искать записи номеров, которые уйдут слишком далеко назад, так как вы не у вас в PARTITION BY есть запись на каждый день). Проблема в том, что в предложении Bigquery RANGE не поддерживаются даты.

Из документации BigQuery:

Числовое_выражение должно иметь числовой тип. DATE и TIMESTAMP в настоящее время не поддерживаются. Кроме того, числовое_выражение должно быть константой, неотрицательным целым числом или параметром.

Обходной путь, который я нашел, заключался в использовании UNIX_DATE (date_expression) в предложении ORDER BY вместе с предложением RANGE:

SUM(value) OVER (PARTITION BY Column1 ORDER BY UNIX_DATE(Date) RANGE BETWEEN 5 PRECEDING AND CURRENT ROW

person ChrisL    schedule 25.10.2019
comment
Так полезно, спасибо! Не думал о функции UNIX_DATE () для выполнения запросов RANGE по полям DATE. Я хочу, чтобы BigQuery изначально поддерживал DATE в предложениях RANGE! Я бы хотел вернуться на 3 месяца назад, я должен приблизительно 90 дней с UNIX_RANGE - person Albert Casademont; 23.10.2020
comment
UNIX_DATE () подходит для этого в большом запросе, спасибо! - person Sami Navesi; 19.05.2021

arrow_upward
0
arrow_downward

Вот альтернативный вариант, который я нашел гибким и эффективным:

WITH users AS
 (SELECT 'Isabella' as user, 1 as spend, DATE(2020, 03, 28) as date
  UNION ALL SELECT 'Isabella', 2, DATE(2020, 03, 29)
  UNION ALL SELECT 'Daniel', 3, DATE(2020, 03, 24)
  UNION ALL SELECT 'Andrew', 4, DATE(2020, 03, 23)
  UNION ALL SELECT 'Daniel', 5, DATE(2020, 03, 11)
  UNION ALL SELECT 'Jose', 6, DATE(2020, 03, 17))
SELECT 
user,
max(sum(case date_diff(date(2020,04,15), date, day) between 0 and 28
        when true then spend else 0 end)) over(partition by user) as spend_28_day_sum
FROM users
group by user

+------------------------------+
| user      | spend_28_day_sum |
+------------------------------+
| Andrew    | 4                |
| Daniel    | 3                |
| Isabella  | 3                |
| Jose      | 0                |
+------------------------------+

Вы можете изменить указанную дату для "оконной функции" на current_date() или cross join с помощью сгенерированный массив дат, чтобы увидеть, как пользователи меняются с течением времени.

person Mitchell Tracy    schedule 31.05.2020