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!

Источник

4 ответа

Решение

Документация 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;

Диапазон RANGE также может быть чрезвычайно полезным. Если в вашей таблице отсутствовали даты для какого-либо пользователя, то 27 строк PRECEDING вернутся назад более чем на 27 дней, но RANGE создаст окно, основанное на самих значениях даты. В следующем запросе поле даты представляет собой BigQuery TIMESTAMP, а диапазон указывается в микросекундах. Я бы посоветовал вам, когда вы встречаетесь с математикой в ​​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;
Источник

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

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

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

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

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

Обходной путь, который я нашел, заключался в использовании 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

Источник

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

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со сгенерированным массивом дат, чтобы увидеть, как пользователи меняются с течением времени.

Источник

Я нашел простой и элегантный способ сделать это, даже если в последние дни у вас не было данных .

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

В UNIX_DATE() возвращает количество дней с 01.01.1970, поэтому мы можем легко вычислить, сколько дней назад осталось, используя его в сочетании с RANGE() функция.

Источник
Другие вопросы по тегам