Блог

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

Рассмотрим следующий подход

 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          
 

Если применить к образцам данных в вашем вопросе — вывод будет

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

Это просто последовательно назначает последний столбец, что, по-видимому, соответствует вашему вопросу.