Как вывести точки которые попали в интервал?

Question

[Алексей Лебедев]

Сформулировал жутко.
Однако все проще.
Имеем 3 колонки:
1) id пользователя
2) время начала сессии (unixtime)
3) время конца сессии (unixtime)
Пример:
1, 111111-111211
2, 111111-111411
3, 111111-111211
1, 111311-111411

Вот, надо вывести пользователи в онлайн по часам.
Допустим
18:00-19:00 - 10 человек онлайн.
19:00-20:00 - 15 человек онлайн.

Есть ли простой вариант вариант решения?
Ибо мне в голову приходит 24 запроса такого вида

SELECT count(1) FROM table WHERE time_end>@time_end and time_start<@time_start;

Comments

[Андрей]

Чё то непонятно, а если сессия началась в 18:59, а закончилась в 20:01, то этот юзер не попадёт что он был онлайн ни в какой час?
Или не попадёт тот которые начал в 19:01 и закончил в 19:59.
Я думаю что если и агрегировать, то так:
time_start between @time_start and @time_end or time_end between @time_start and time_end or (time_start <@time_start and time_end>@time_end)
Но в этом случае юзер может попасть в онлайн в два промежутка.

Answer 1

[Nikolay]

Наверное нужно сгрупперовать по времени? В гугле куча ответов на вопрос "mysql group by 1 hour", выбирать какой понравится вам.

Comments

[Алексей Лебедев]

Проблема с группировкой пользователей. У пользователей может быть 20 сессий по 1 минуты за 1 час, либо одна на 5 часов.

Answer 2

[Immortal_pony]

Если сессии не очень длинные и в точном значении нет необходимости, то так:

SELECT 
	COUNT(`id`),
	DATE_FORMAT(`start`, '%Y%m%d%H') AS 'hour'
FROM 
	`users`
WHERE 1
	GROUP BY `hour`

Comments

[Алексей Лебедев]

Не решает проблемы с дублирующими сессиями, и теми которые занимают больше одного часа.

[Immortal_pony]

Дублируюющие сессии можно отсечь так:

SELECT 
	COUNT(`inner`.`sessions_quantity`),
	`inner`.hour
FROM (
	SELECT
		COUNT(`id`) AS 'sessions_quantity',
		DATE_FORMAT(`start`, '%Y%m%d%H') AS 'hour'
	FROM 
		`users`
	WHERE 1
		GROUP BY `hour`, `id`
) AS `inner`
GROUP BY `inner`.hour

Answer 3

[Артем Мудряк]

Я бы сделал справочник, с необходимыми интервалами времени (допустим сутки, разбитые по 1 часу), назовем calendar, и сделал JOIN, с вот таким ограничением:
calendar.MyHour between "время начала сессии" and "время конца сессии".
Тем самым, каждую строку исходной таблицы мы множим на количество временных интервалов, которые она охватывает.
Сразу вижу еще 1 место для проработки, если временной интервал охватывает несколько дней, то нужен второй справочник, с днями, у меня такой называется DAYS, где список дат (в прошлое и будущее) с кучей полезной информации по календарю, работаем с ним аналогичным образом.

Если что-то непонятно объяснил, могу подробнее...

Comments

[Алексей Лебедев]

Справочник делать каждый день? Это не круто.

[Виталий Литвинюк]

Алексей Лебедев: сделай группировку не по дате в целом, а только по ее части. Или приведение unix-time к чему то удобоваримому составляет проблему?

[Артем Мудряк]

Алексей Лебедев: Зачем каждый день. один раз, и на много лет, статичный, сразу заполнить вперед и назад.
2 справочника:
---Дни - содержит 5000 строк, по 1 строке на 1 день, на 15 лет запас :
---Часы - вообще менять не надо, их как было 24, так пока и есть.