# #sql #google-bigquery
Вопрос:
У меня есть следующая таблица :
| когорта | месячная когорта | заказы | совокупные заказы |
|---|---|---|---|
| 2021-01 | 0 | 126 | 126 |
| 2021-01 | 1 | 5 | 131 |
| 2021-01 | 2 | 4 | 135 |
| 2021-02 | 0 | 131 | 131 |
| 2021-02 | 1 | 9 | 140 |
| 2021-02 | 2 | 8 | 148 |
И теперь я хочу иметь следующую таблицу, в которой я разделяю каждый повторный заказ на количество заказов за месяц 0 :
| когорта | месячная когорта | заказы | совокупные заказы | накапливается в % |
|---|---|---|---|---|
| 2021-01 | 0 | 126 | 126 | 100% |
| 2021-01 | 1 | 5 | 131 | 104% |
| 2021-01 | 2 | 4 | 135 | 107% |
| 2021-02 | 0 | 131 | 131 | 100% |
| 2021-02 | 1 | 9 | 140 | 107% |
| 2021-02 | 2 | 8 | 148 | 114% |
Мой единственный совет — создать инструкцию по обращению, но я не хочу каждый месяц обновлять запрос, добавляя строку
WHEN cohort="2021-08" THEN cumulated orders / 143
где 143 — количество заказов когорты 2021-08 в месячной когорте =0
У кого — нибудь есть идея, как получить этот стол ?
Ответ №1:
case Выражение не требуется. Вы можете использовать first_value() :
select t.*,
( cumulated_order /
first_value(orders) over (partition by cohort order by month_cohort)
) as ratio
from t;
Если бы вы действительно хотели case , вы могли бы использовать:
select t.*,
( cumulated_order /
max(case when month_cohort = 0 then orders end) over (partition by cohort)
) as ratio
from t;
Ответ №2:
Рассмотрим ниже
select *,
round(100 * cumulated_orders /
sum(if(month_cohort = 0, orders, 0)) over(partition by cohort)
) as cumulated_in_percent
from `project.dataset.table`
если применить к образцам данных в вашем вопросе — вывод будет
