Выберите время, когда в зоне нет людей - mysql

ProgramBox

Выберите время, когда в зоне нет людей — mysql

Post author:admin
Запись опубликована:11 мая, 2023
Post category:Вопросы по программированию

#mysql #select #time #range

#mysql #выберите #время #диапазон

Вопрос:

У меня есть таблица, в которой хранятся данные о том, как люди заходят в область, время, когда они заходят внутрь, и время, когда они выходят на улицу, каждый раунд равен 1 человеку.
Как я могу написать запрос для проверки каждые 5 минут, если в этой области нет людей?
Например, в таблице выше:

диапазон с 10:10:00 до 10:15:00 => нет клиента с 10: 10:00 до 10:14:58
диапазон с 10:15:00 до 10: 20:00 = > клиент с 10: 15:00 до 10:20:00
диапазон с 10:20:00 до 10:25:00 = > клиент с 10: 20:00 до 10:25:00
диапазон с 10: 25:00 до 10: 30:00 = > нет клиента с 10: 26: 33 до 10:30:00
диапазон с 10:30:00 до 10:35:00 = > нет клиента с 10:30:33 по 10:33:42

действительно сложно создать интеллектуальный запрос

1. Два комментария: некоторые диапазоны охватывают более одного 5-минутного интервала, а также, если данный диапазон не отображается в наборе данных, вам придется сделать что-то вроде использования таблицы календаря для включения этой информации.

2. Обычно проблемы отображения данных наиболее эффективно решаются в коде уровня представления / уровня приложения, если это доступно (например, простой цикл PHP.

Ответ №1:

Следующий запрос вернет все временные диапазоны, когда никто не был в этой области:

 select vo.timegoout as empty_start, min(vi.startentry) as empty_end
from visits vo -- leave area (visit out)
join visits vi -- enter area (visit in)
    on vi.startentry > vo.timegoout
where not exists (
    select *
    from visits v
    where v.timegoout  >  vo.timegoout
      and v.startentry <= vo.timegoout
)
group by vo.timegoout
union all
select '00:00:00', (select min(startentry) from visits)
union all
select (select max(timegoout) from visits), '23:59:59'
order by empty_start

Идея в том:

Область пуста, когда кто-то покидает область ( vo.timegoout as empty_start ), и в этот момент в области больше никого нет ( not exists (...) ). Область пуста, пока кто-нибудь не войдет в нее ( min(vi.startentry) as empty_end ). Кроме того, область пуста с 00:00:00 момента, пока ее не посетит первый посетитель ( union all select '00:00:00', (select min(startentry) from visits) ). Она также пуста после того, как последний посетитель покинет ее до 23:59:59 ( union all select (select max(timegoout) from visits), '23:59:59' )

Учитывая следующие примерные данные

 | startentry | timegoout |
|   10:00:00 | 10:01:00  |
|   10:10:10 | 10:11:11  |
|   10:12:12 | 10:13:13  |
|   12:33:33 | 12:55:55  |
|   12:34:56 | 12:44:44  |
|   14:31:00 | 14:33:00  |
|   14:32:00 | 14:34:00  |
|   16:00:00 | 16:10:00  |

мы получим этот результат:

 | empty_start | empty_end |
|    00:00:00 |  10:00:00 |
|    10:01:00 |  10:10:10 |
|    10:11:11 |  10:12:12 |
|    10:13:13 |  12:33:33 |
|    12:55:55 |  14:31:00 |
|    14:34:00 |  16:00:00 |
|    16:10:00 |  23:59:59 |

Если вы хотите быть точным до секунды, запрос нуждается в некоторых модификациях:

 select addtime(vo.timegoout, '00:00:01') as empty_start,
       subtime(min(vi.startentry), '00:00:01') as empty_end
from visits vo -- leave area (visit out)
join visits vi -- enter area (visit in)
    on vi.startentry > vo.timegoout
where not exists (
    select *
    from visits v
    where v.timegoout  >  vo.timegoout
      and v.startentry <= vo.timegoout
)
group by vo.timegoout
union all
select '00:00:00' as empty_start, (select subtime(min(startentry), '00:00:01') from visits) as empty_end from (select 1) dummy
having empty_start <= empty_end
union all
select (select addtime(max(timegoout), '00:00:01') from visits) as empty_start, '23:59:59' as empty_end from (select 1) dummy
having empty_start <= empty_end
order by empty_start

Результат:

 | empty_start | empty_end |
|    00:00:00 |  09:59:59 |
|    10:01:01 |  10:10:09 |
|    10:11:12 |  10:12:11 |
|    10:13:14 |  12:33:32 |
|    12:55:56 |  14:30:59 |
|    14:34:01 |  15:59:59 |
|    16:10:01 |  23:59:59 |

Для обработки диапазонов в 5 минут вам следует создать таблицу halper, содержащую все эти диапазоны:

 drop table if exists tmp_sequence;
create table tmp_sequence (seq mediumint unsigned null);

insert into tmp_sequence(seq) 
    values (null),(null),(null),(null),(null),(null),(null),(null),(null),(null);

insert into tmp_sequence(seq) select seq from tmp_sequence;
insert into tmp_sequence(seq) select seq from tmp_sequence;
insert into tmp_sequence(seq) select seq from tmp_sequence;
insert into tmp_sequence(seq) select seq from tmp_sequence;
insert into tmp_sequence(seq) select seq from tmp_sequence;

ALTER TABLE `tmp_sequence`
    CHANGE COLUMN `seq` `seq` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
    ADD PRIMARY KEY (`seq`);

drop table if exists helper_5_minutes_ranges;
create table helper_5_minutes_ranges(
    range_start time,
    range_end time,
    primary key (range_start, range_end)
);
insert into helper_5_minutes_ranges (range_start, range_end)
    select sec_to_time((seq-1)*5*60) as range_start, sec_to_time((seq)*5*60-1) as range_end
    from tmp_sequence ts
    where ts.seq <= 288
;

drop table if exists tmp_sequence;

Данные в helper_5_minutes_ranges таблице:

 | range_start | range_end |
|    00:00:00 |  00:04:59 |
|    00:05:00 |  00:09:59 |
...
|    23:50:00 |  23:54:59 |
|    23:55:00 |  23:59:59 |

Теперь вы можете запрашивать такие вещи, как: Выберите все 5-минутные диапазоны, когда в зоне никого не было

 select r.*
from helper_5_minutes_ranges r
left join visits v
    on  v.startentry <= r.range_end
    and v.timegoout  >= r.range_start
where v.startentry is null

Результат:

 | range_start | range_end |
|    00:00:00 |  00:04:59 |
...
|    10:05:00 |  10:09:59 |
|    10:15:00 |  10:19:59 |
...
|    12:25:00 |  12:29:59 |
|    13:00:00 |  13:04:59 |
...
|    14:25:00 |  14:29:59 |
|    14:35:00 |  14:39:59 |
...
|    15:55:00 |  15:59:59 |
|    16:15:00 |  16:19:59 |
...
|    23:55:00 |  23:59:59 |

Выберите все интервалы в 5 минут, когда кто-то был в этой области

 select r.*
from helper_5_minutes_ranges r
join visits v
    on  v.startentry <= r.range_end
    and v.timegoout  >= r.range_start

И, наконец, этот монстр, который вернет что-то вроде вашего ожидаемого результата:

 select r.*, 'visited' as type,
    greatest(v.startentry, r.range_start) as vr_start,
    least(v.timegoout, r.range_end) as vr_end
from helper_5_minutes_ranges r
join visits v
    on  v.startentry <= r.range_end
    and v.timegoout  >= r.range_start

union all

select mr.*, 'empty' as type,
    greatest(er.empty_start, mr.range_start) as vr_start,
    least(er.empty_end, mr.range_end) as vr_end
from (
    select addtime(vo.timegoout, '00:00:01') as empty_start,
           subtime(min(vi.startentry), '00:00:01') as empty_end
    from visits vo -- leave area (visit out)
    join visits vi -- enter area (visit in)
        on vi.startentry > vo.timegoout
    where not exists (
        select *
        from visits v
        where v.timegoout  >  vo.timegoout
          and v.startentry <= vo.timegoout
    )
    group by vo.timegoout
    union all
    select '00:00:00' as empty_start, (select subtime(min(startentry), '00:00:01') from visits) as empty_end from (select 1) dummy
    having empty_start <= empty_end
    union all
    select (select addtime(max(timegoout), '00:00:01') from visits) as empty_start, '23:59:59' as empty_end from (select 1) dummy
    having empty_start <= empty_end
) er
join helper_5_minutes_ranges mr
    on  mr.range_start <= er.empty_end
    and mr.range_end   >= er.empty_start

order by range_start, vr_start

Результат:

 | range_start | range_end | type    | vr_start       | vr_end          |
...
|    09:55:00 | 09:59:59 | empty   | 09:55:00.000000 | 09:59:59.000000 |
|    10:00:00 | 10:04:59 | visited | 10:00:00.000000 | 10:01:00.000000 |
|    10:00:00 | 10:04:59 | empty   | 10:01:01.000000 | 10:04:59.000000 |
|    10:05:00 | 10:09:59 | empty   | 10:05:00.000000 | 10:09:59.000000 |
|    10:10:00 | 10:14:59 | empty   | 10:10:00.000000 | 10:10:09.000000 |
|    10:10:00 | 10:14:59 | visited | 10:10:10.000000 | 10:11:11.000000 |
|    10:10:00 | 10:14:59 | empty   | 10:11:12.000000 | 10:12:11.000000 |
|    10:10:00 | 10:14:59 | visited | 10:12:12.000000 | 10:13:13.000000 |
|    10:10:00 | 10:14:59 | empty   | 10:13:14.000000 | 10:14:59.000000 |
...