# #sql #google-bigquery #gaps-and-islands #windowed
#sql #google-bigquery #пробелы и острова #оконный
Вопрос:
мир!
У меня есть таблица первого «уровня», которая выглядит примерно так:
| Уровень | ID | level_date |
|---|---|---|
| 1 | A | 2021-12-02 |
| 2 | A | 2021-12-04 |
| 3 | A | 2021-12-08 |
| 1 | B | 2021-12-02 |
| 2 | B | 2021-12-05 |
| 3 | B | 2021-12-09 |
и вторая таблица «сражений» :
| ID | дата битвы |
|---|---|
| A | 2021-12-01 |
| A | 2021-12-03 |
| A | 2021-12-06 |
| A | 2021-12-07 |
| B | 2021-12-01 |
| B | 2021-12-02 |
| B | 2021-12-03 |
То, что я пытаюсь сделать, — это найти среднее количество сражений, необходимое для прохождения каждого уровня.
Когда battle_date gt; level_X-1_date, но battle_date gt;
Таким образом, для игрока А у нас есть одна битва, чтобы добраться до уровня 1, одна битва, чтобы добраться до уровня 2, и две битвы, чтобы добраться до уровня 3. А для игрока В у нас есть одна битва, чтобы добраться до уровня 1, две битвы, чтобы добраться до уровня 2, и ноль сражений, чтобы добраться до уровня 3
Результирующая таблица должна выглядеть следующим образом:
| Уровень | avg_battle_count |
|---|---|
| 1 | 1 |
| 2 | 1.5 |
| 3 | 1 |
Я почти уверен, что это своего рода проблема «пробелов и островов», но я не знаю, как именно я должен построить запрос, который рассматривает оконную функцию для подсчета среднего(battle_count) для уровней
Комментарии:
1. MySQL или BogQuery?
2. Помечайте только ту базу данных, которую вы используете.
3. обычно, когда и BigQuery, и MySQL помечены — это заканчивается BigQuery :o) но было бы здорово подтвердить!
Ответ №1:
Рассмотрим следующий подход (BigQuery)
select level, avg(battle_count) as avg_battle_count from ( select level, id, battle_count - ifnull(lag(battle_count) over(partition by id order by level), 0) as battle_count from ( select level, t1.id, count(*) battle_count from levels t1 left join battles t2 on t1.id = t2.id and battle_date lt; level_date group by level, id ) ) group by level если применимо к образцам данных в вашем вопросе
with levels as ( select 1 level, 'A' id, '2021-12-02' level_date union all select 2, 'A', '2021-12-04' union all select 3, 'A', '2021-12-08' union all select 1, 'B', '2021-12-02' union all select 2, 'B', '2021-12-05' union all select 3, 'B', '2021-12-09' ), battles as ( select 'A' id, '2021-12-01' battle_date union all select 'A', '2021-12-03' union all select 'A', '2021-12-06' union all select 'A', '2021-12-07' union all select 'B', '2021-12-01' union all select 'B', '2021-12-02' union all select 'B', '2021-12-03' ) выход есть
Ответ №2:
Я не пробовал это, я думаю, что это должно привести к результату, который вы ищете:
select level, avg(battle_count) avg_battle_count from ( select x.level, x.id, count(*) battle_count from level x left join level x_1 on x.id = x_1.id and x.level = x_1.level-1 join battles b on x.id = b.id and b.battle_date lt; x.level_date and (b.battle_date gt; x_1.level_date or x_1.level_date is null) group by x.level, x.id ) group by level order by level