Блог

У меня есть набор данных, содержащий информацию о событии определенного типа, включая starttime endtime , и duration (в минутах). Я хотел бы сгруппировать эти данные по date (будут извлечены из starttime ), но у меня есть строка, представляющая событие, которое происходит через несколько дней, например, index 33, starttime 2020-07-13 10:19:54 UTC, endtime 2020-07-15 13:13:21 UTC и duration 3053. Например, эта строка должна быть разбита на три строки:

 starttime          endtime            duration
2020-07-13 10:19   2020-07-13 23:59    821
2020-07-14 00:00   2020-07-14 23:59   1440
2020-07-15 00:00   2020-07-15 13:13    793
  

Чтобы было понятно, я пытаюсь использовать эти 3 строки для замены исходной строки, поэтому при группировании по date число будет правильным.

Как мне это сделать в BigQuery?

Используйте genereate_date_array() и unnest() :

 select t.ind,
       greatest(start_ts, timestamp(dt)) as start_ts,
       least(timestamp(date_add(dt, interval 1 day)), end_ts)
from (select 32 as ind, timestamp('2020-07-13 10:19:54 UTC') as start_ts,  timestamp('2020-07-13 13:13:21 UTC') as end_ts union all
      select 33 as ind, timestamp('2020-07-13 10:19:54 UTC') as start_ts,  timestamp('2020-07-15 13:13:21 UTC') as end_ts
     ) t cross join
     unnest(generate_date_array(date(start_ts), date(end_ts))) dt ;
  

Примечание: это дает конечную временную метку как полночь, а не за минуту или секунду до этого. Таким образом, промежуточные дни имеют 24 часа. Конечно, вы можете вычесть секунду или минуту, но мне это кажется менее точным.