# #sql #google-bigquery
Вопрос:
У меня есть такой столик:
| Дата | идентификатор пользователя | доход |
|---|---|---|
| 2021-10-01 | 1 | 2 |
| 2021-10-02 | 1 | 3 |
| 2021-10-05 | 1 | 2 |
| 2021-10-09 | 1 | 3 |
| 2021-10-15 | 1 | 3 |
| 2021-10-01 | 2 | 2 |
| 2021-10-04 | 2 | 2 |
| 2021-10-10 | 2 | 1 |
| 2021-10-11 | 2 | 3 |
| 2021-10-11 | 2 | 3 |
| 2021-10-20 | 2 | 5 |
И я хочу добавить столбец со средним доходом за последние 5 дней, сгруппированный по идентификатору пользователя. Желаемый результат должен выглядеть следующим образом:
| Дата | идентификатор пользователя | доход | median_last_5_days |
|---|---|---|---|
| 2021-10-01 | 1 | 2 | нулевой |
| 2021-10-02 | 1 | 3 | 2 |
| 2021-10-05 | 1 | 2 | 2.5 |
| 2021-10-09 | 1 | 3 | 2 |
| 2021-10-16 | 1 | 3 | нулевой |
| 2021-10-01 | 2 | 2 | нулевой |
| 2021-10-02 | 2 | 3 | 2 |
| 2021-10-03 | 2 | 3 | 2.5 |
| 2021-10-04 | 2 | 2 | 3 |
| 2021-10-10 | 2 | 1 | нулевой |
| 2021-10-11 | 2 | 3 | 1 |
| 2021-10-11 | 2 | 3 | 2 |
| 2021-10-20 | 2 | 5 | нулевой |
Могу ли я создать это с помощью SQL BigQuery? Спасибо, что помогли мне)
Комментарии:
1. Что-то вроде:
PERCENTILE_CONT(x, 0.5) OVER (PARTITION BY user_id ORDER BY UNIX_DATE(date) RANGE BETWEEN 5 PRECEDING AND CURRENT ROW)Хотя я не уверен, как лучше всего выразить диапазон в большом запросе.
Ответ №1:
Использование PERCENTILE_CONT или PERCENTILE_DISC для получения медианы не будет работать в этих условиях, так как window_frame_clause это запрещено в навигационных функциях.
Попробуйте этот подход,
CREATE TEMP FUNCTION MEDIAN(arr ANY TYPE) AS ((
SELECT
IF(
MOD(ARRAY_LENGTH(arr), 2) = 0,
(arr[OFFSET(DIV(ARRAY_LENGTH(arr), 2) - 1)] arr[OFFSET(DIV(ARRAY_LENGTH(arr), 2))]) / 2,
arr[OFFSET(DIV(ARRAY_LENGTH(arr), 2))]
)
FROM (SELECT ARRAY_AGG(x ORDER BY x) AS arr FROM UNNEST(arr) AS x)
));
SELECT
date,
user_id,
revenue,
MEDIAN(
ARRAY_AGG(revenue) OVER (PARTITION BY user_id
ORDER BY datetime_diff(date, date('2000-01-01'), day)
RANGE BETWEEN 4 PRECEDING AND 1 PRECEDING)
) AS median_last_5_days
FROM `my-project.my-dataset.my-table`
GROUP BY date, user_id, revenue
ORDER BY user_id;
Примеры данных:
Выход:
