Разделите строку, содержащую данные за несколько дней, по отдельной дате (BigQuery).

У меня есть набор данных, который содержит информацию об определенном типе события, включая 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?


sql google-bigquery timestamp group-by
person Reesie Cup    schedule 06.10.2020    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Используйте 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 часа. Конечно, можно вычесть секунду или минуту, но мне это кажется менее точным.

person Gordon Linoff    schedule 06.10.2020
comment
Именно то, что мне было нужно. Благодарю вас! - person Reesie Cup; 07.10.2020