# #sql #group-by #google-bigquery
Вопрос:
У меня возникли проблемы с попытками сгруппировать события, которые происходят в течение 60 секунд после предыдущего. Моя текущая группировка, если это происходит в течение той же минуты дня, но это приводит к ошибкам, когда задача выполняется, скажем, в 12:00:45, а следующая-в 12:01:35. Мне нужно, чтобы они были сгруппированы вместе как одна и та же работа, но если сейчас 12:00:35, а следующая-12:01:45, это должна быть другая работа.
Например, текущий (задание # просто должно быть уникальным номером, поэтому я использую unix_seconds/60, чтобы в данный момент выдавать по существу unix_minutes):
SELECT time,
event,
TRUNC(UNIX_SECONDS(datetime)/60)) AS job
FROM table
datetime event # job #
------------------- ------- -----
2021-07-06 12:00:35 event1 27091680
2021-07-06 12:00:55 event2 27091680
2021-07-06 12:01:43 event3 27091681
2021-07-06 12:03:08 event4 27091683
2021-07-06 12:04:01 event5 27091684
2021-07-06 12:05:02 event6 27091685
Что мне нужно:
datetime event # job #
------------------- ------- -----
2021-07-06 12:00:35 event1 27091680
2021-07-06 12:00:55 event2 27091680
2021-07-06 12:01:43 event3 27091680
2021-07-06 12:03:08 event4 27091683
2021-07-06 12:04:01 event5 27091683
2021-07-06 12:05:02 event6 27091685
Комментарии:
1. Что делать, если у вас есть 10 строк с интервалом в 30 секунд? Все ли они сочетаются?
2. Да, он должен продолжать объединяться до тех пор, пока не появится промежуток в 60 секунд или более.
Ответ №1:
Рассмотрим следующий подход
select datetime, event, first_value(job) over(partition by grp order by datetime) job
from (
select datetime, event, job, countif(gap) over(order by datetime) grp
from (
select *, datetime_diff(datetime, lag(datetime) over(order by datetime), second) > 60 gap
from `project.dataset.table`
)
)
# order by datetime
Если применить к образцам данных в вашем вопросе — вывод будет
Ответ №2:
Если вы хотите назначить группы заданиям, которые находятся в пределах 60 секунд от другой строки в группе, вы можете выполнить следующие действия:
- Определите, где есть промежуток в 60 секунд.
- Используйте совокупное количество пробелов для присвоения нумерации.
В контексте вашего запроса:
select t.* except(datetime),
sum(case when prev_datetime > datetime_add(datetime, interval -60 second) then 0 else 1
end) over (order by datetime) as job
from (select t.*,
lag(datetime) over (order by datetime) as prev_datetime
from `table` t
) t;
Это просто последовательно назначает последний столбец, что, по-видимому, соответствует вашему вопросу.
Комментарии:
1. Привет, Гордон, приношу свои извинения, так как кажется, что я, должно быть, неправильно воспроизвел ваш запрос при моем первоначальном тестировании. Когда сначала сработал другой ответ, я принял его. Я только что снова проверил ваш ответ, и он делает то, о чем я просил, хотя в моем сценарии сохранение значений unixtime было полезно для меня. Еще раз приношу свои извинения, так как я здесь новичок и все еще изучаю этикет сайта.
2. @sbaker . . . Ваш вопрос был довольно явным, что время unix не важно («(задание # просто должно быть уникальным номером»), иначе я бы включил это в ответ. Порядковый номер также имеет свои преимущества.