Агрегация SQL на основе времени и изменения столбца

У меня есть ситуация, когда я пытаюсь рассчитать время начала, окончания и время, проведенное в разных областях в одном месте.

У меня есть данные с отметкой времени и местоположением человека, когда он был захвачен системой.

Обычным случаем является изменение местоположения, и в этом случае время окончания должно быть предыдущим увиденным значением. Исключением является случай, когда человек не виден в течение 5 м или более, и в этом случае endTime должен быть последним увиденным (см. выше строки 2 и 3 требуемых агрегированных выходных данных).

Исходные данные

date, zone
8h10m, room1
8h12m, room1
8h15m, hall
8h16m, hall
8h25m, hall
8h29m, hall
8h30m, room2
8h34m, room2
8h38m, room2
8h42m, room2

Агрегация/Сводка требуется следующим образом (или подобным):

startDate, endDate, time, zone
8h10m, 8h12m, 3m, room1
8h15m, 8h16m, 2m, hall   <-- special case time >5m
8h25m, 8h29m, 5m, hall
8h30, 8h42m, 9n, room2

Можете ли вы дать мне представление о том, как я могу сделать такое «агрегирование/сводку» в SQL? Я использую BigQuery, но я считаю, что стандартный SQL должен работать.

Спасибо,

Руи

какой тип данных поля date? это действительно строка, как в примере данных в вашем вопросе? или это дата и время или временная метка, как и должно быть в идеале?

— 20.03.2022 19:52

В последней строке указано: 8h30, 8h42m, 9n, room2 ---> 13m ?

— 20.03.2022 20:43

да Сэмюэл, 13м... моя ошибка ;-)

— 21.03.2022 07:55

sql google-bigquery

20.03.2022 18:53

ReactJs | Supabase | Добавление данных в базу данных

Это и есть ваш редактор таблиц в supabase.👇

Понимание Python и переход к SQL

Перед нами лабораторная работа по BloodOath:

Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Из решения Михаила Берлянта концепция с countif была использована для упрощения этого запроса. Этот ответ способен идентифицировать каждое движение, даже если человек повторно входит в комнату в течение 5 минут. Дополнительные данные см. в таблице.

Необходимо несколько шагов:

добавить строки с зонами ---, когда 5 минут нет данных: Установите over_5:minutes true, если разница между предыдущей строкой (lag) и текущей строкой превышает 5 минут. unnest([0,1]) as x для дублирования набора данных и qualify для включения набора данных в этом случае.
отсортируйте все следующие утверждения по столбцу date, x в over(order by date, x)
с lag получить последнюю комнату и последнюю дату. Из-за невложенного x посмотрите на две строки назад.
сравните последнюю комнату с текущей, если они отличаются, установите zone_change на true.
countif (zone_change) с 1 по текущую дату, чтобы получить zone_id. Это соответствует одной зоне.
Для этого zone_id посчитайте время, когда x было равно 0; это тот случай, когда 5 минут ни одной позиции не дали.
group by zone_id и рассчитать минимальную и максимальную дату
удалить зоны --- путем фильтрации

With tbl as
(
SELECT TIME "8:10:00" as date, "room1" as zone
UNION ALL SELECT TIME "8:12:00", "room1"
UNION ALL SELECT TIME "8:15:00", "hall"
UNION ALL SELECT TIME "8:16:00", "hall"
UNION ALL SELECT TIME "8:25:00", "hall"
UNION ALL SELECT TIME "8:29:00", "hall"
UNION ALL SELECT TIME "8:30:00", "room2"
UNION ALL SELECT TIME "8:34:00", "room2"
UNION ALL SELECT TIME "8:38:00", "room2"
UNION ALL SELECT TIME "8:42:00", "room2"
UNION ALL SELECT TIME "8:43:00", "hall"
UNION ALL SELECT TIME "8:44:00", "room2"
)

SELECT 
zone_id,
zone,
min(date) as startDate,
max(date) as endDate,
time_diff(max(date),min(date),minute)+1 as time_minutes
FROM
(
SELECT *,
countif (x=0)  over (ORDER BY date,x RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)+
countif (zone_change) over (ORDER BY date,x RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as zone_id
FROM
(
SELECT date,x,if (x=1,zone,"---") as zone,
time_diff(date,lag(date,2) over (order by date),minute)>5 as over_5_minutes,
zone!=lag(zone,2) over (order by date,x) as zone_change
FROM tbl, unnest([0,1]) as x
Qualify over_5_minutes or x=1
)
)
where zone! = "---"
group by 1,2
order by 1

Спасибо @Samuel! Я тоже попробую ваше решение. Хорошо, когда есть решения на выбор. Ценю твою поддержку. Руи

— 21.03.2022 08:14

20.03.2022 20:40

Ответ принят как подходящий

Рассмотрим ниже подход

select 
  min(date) as startDate, max(date) as endDate, 
  time_diff(max(date), min(date), minute) + 1 as time, zone
from (
  select *, countif (new_zone) over (partition by zone order by date) as zone_number
  from (
    select *, 
      ifnull(date - lag(date) over (partition by zone order by date) > make_interval(minute => 5)
        or zone != lag(zone) over(order by date), true) as new_zone
    from your_table
  )
)
group by zone, zone_number

если применяется к выборочным данным в вашем вопросе

with your_table as (
  select time "8:10:00" as date, "room1" as zone union all 
  select "8:12:00", "room1" union all 
  select "8:15:00", "hall" union all 
  select "8:16:00", "hall" union all 
  select "8:25:00", "hall" union all 
  select "8:29:00", "hall" union all 
  select "8:30:00", "room2" union all 
  select "8:34:00", "room2" union all 
  select "8:38:00", "room2" union all 
  select "8:42:00", "room2"
)

выход

Очень хороший и короткий запрос для этой задачи. Если человек снова войдет в ту же зону в течение 5 минут, он не будет показан. Когда человек идет в 9:43 в холл, а затем возвращается в комнату 2 в 9:44, будет показан 2-й зал, но не 2-я комната2.

— 20.03.2022 23:21

спасибо! @Samuel - ты прав - исправлено :о)

— 21.03.2022 03:08

Большое спасибо! работает, как и ожидалось... просто пришлось изменить time_diff на date_diff, так как я использую поле типа TIMESTAMP в BigQuery. все еще пытаюсь понять логику, но я доберусь ;-) Еще раз спасибо, Руи

— 21.03.2022 08:11

20.03.2022 21:19