mysql выбрать количество строк между временными интервалами

Я пытаюсь получить общее количество строк между определенным временем времени или временным интервалом. В основном, скажем, в следующей таблице:

CREATE TABLE IF NOT EXISTS `downloads` ( `id` int(7) NOT NULL AUTO_INCREMENT, `stuff_id` int(7) NOT NULL, `user_id` int(7) NOT NULL, `dl_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; 

И эта таблица заполняется каждый раз, когда кто-то загружает что-то.

Так что мне действительно нужно получить список пользователей (user_id), которые сделали больше, чем, например, 100 загрузок за промежутки времени, например, 24 часа. Не в последние 24 часа, но в тот точный период времени, даже если это было во время Рождества в прошлом году =)

Есть идеи вообще?!

Хорошо, я понимаю, что я немного опоздал, но я все равно хотел опубликовать свой ответ 🙂

То, что вам нужно, может быть выполнено с помощью подзапроса, но это может занять много времени на большой таблице …

Думая о вопросе, я пришел к двум различным подходам.

Один из них уже рассмотрен в других ответах, он работает, начиная с определенного момента времени, глядя на интервал, который начинается в это время, а затем смотрит на промежуток с равной продолжительностью, который сразу следует. Это приводит к ясным, понятным результатам и, вероятно, потребует (например, пользователь не должен превышать 100 загрузок за календарный день). Это, однако, полностью упустило бы ситуации, когда пользователь делает 99 загрузок за час до полуночи и еще 99 в первый час нового дня.

Поэтому, если требуемый результат больше относится к «десятку списков загрузчиков», то это другой подход. Результаты здесь могут быть не столь понятными с первого взгляда, потому что одна загрузка может рассчитывать на несколько интервалов. Это связано с тем, что интервалы будут (и должны) перекрываться.

Вот моя настройка. Я создал таблицу из вашего заявления и добавил два индекса:

 CREATE INDEX downloads_timestamp on downloads (dl_date); CREATE INDEX downloads_user_id on downloads (user_id); 

Данные, которые я вставил в таблицу:

 SELECT * FROM downloads; +----+----------+---------+---------------------+ | id | stuff_id | user_id | dl_date | +----+----------+---------+---------------------+ | 1 | 1 | 1 | 2011-01-24 09:00:00 | | 2 | 1 | 1 | 2011-01-24 09:30:00 | | 3 | 1 | 1 | 2011-01-24 09:35:00 | | 4 | 1 | 1 | 2011-01-24 10:00:00 | | 5 | 1 | 1 | 2011-01-24 11:00:00 | | 6 | 1 | 1 | 2011-01-24 11:15:00 | | 7 | 1 | 1 | 2011-01-25 09:15:00 | | 8 | 1 | 1 | 2011-01-25 09:30:00 | | 9 | 1 | 1 | 2011-01-25 09:45:00 | | 10 | 1 | 2 | 2011-01-24 08:00:00 | | 11 | 1 | 2 | 2011-01-24 12:00:00 | | 12 | 1 | 2 | 2011-01-24 12:01:00 | | 13 | 1 | 2 | 2011-01-24 12:02:00 | | 14 | 1 | 2 | 2011-01-24 12:03:00 | | 15 | 1 | 2 | 2011-01-24 12:00:00 | | 16 | 1 | 2 | 2011-01-24 12:04:00 | | 17 | 1 | 2 | 2011-01-24 12:05:00 | | 18 | 1 | 2 | 2011-01-24 12:06:00 | | 19 | 1 | 2 | 2011-01-24 12:07:00 | | 20 | 1 | 2 | 2011-01-24 12:08:00 | | 21 | 1 | 2 | 2011-01-24 12:09:00 | | 22 | 1 | 2 | 2011-01-24 12:10:00 | | 23 | 1 | 2 | 2011-01-25 14:00:00 | | 24 | 1 | 2 | 2011-01-25 14:12:00 | | 25 | 1 | 2 | 2011-01-25 14:25:00 | +----+----------+---------+---------------------+ 25 rows in set (0.00 sec) 

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

Теперь нам нужно помнить следующее: математически существует бесконечное количество 24-часовых интервалов (или интервалов любой другой продолжительности) между '2011-01-24 0:00' и '2011-01-25 23 : 59: 59' . Но поскольку точность сервера составляет одну секунду, это составляет до 86 400 интервалов:

 First interval: 2011-01-24 0:00:00 -> 2011-01-25 0:00:00 Second interval: 2011-01-24 0:00:01 -> 2011-01-25 0:00:01 Third interval: 2011-01-24 0:00:02 -> 2011-01-25 0:00:02 . . . 86400th interval: 2011-01-24 23:59:59 -> 2011-01-25 23:59:59 

Таким образом, мы могли бы использовать цикл для повторения всех этих интервалов и вычисления количества загрузок на пользователя и за интервал. Конечно, не все интервалы представляют для нас такой же интерес, поэтому мы можем пропустить некоторые из них, используя временные метки в таблице как «начало интервала».

Вот что делает следующий запрос. Он использует каждую временную метку загрузки в таблице как «начало интервала», добавляет продолжительность интервала и затем запрашивает количество загрузок на пользователя за этот интервал.

 SET @duration = '24:00:00'; SET @limit = 5; SELECT * FROM (SELECT t1.user_id, t1.dl_date startOfPeriod, ADDTIME(t1.dl_date,@duration) endOfPeriod, (SELECT COUNT(1) FROM downloads t2 WHERE t1.user_id = t2.user_id AND t1.dl_date <= t2.dl_date AND ADDTIME(t1.dl_date,@duration) >= t2.dl_date) count FROM downloads t1) t3 WHERE count > @limit; 

Вот результат:

 +---------+---------------------+---------------------+-------+ | user_id | startOfPeriod | endOfPeriod | count | +---------+---------------------+---------------------+-------+ | 1 | 2011-01-24 09:00:00 | 2011-01-25 09:00:00 | 6 | | 1 | 2011-01-24 09:30:00 | 2011-01-25 09:30:00 | 7 | | 1 | 2011-01-24 09:35:00 | 2011-01-25 09:35:00 | 6 | | 1 | 2011-01-24 10:00:00 | 2011-01-25 10:00:00 | 6 | | 2 | 2011-01-24 08:00:00 | 2011-01-25 08:00:00 | 13 | | 2 | 2011-01-24 12:00:00 | 2011-01-25 12:00:00 | 12 | | 2 | 2011-01-24 12:01:00 | 2011-01-25 12:01:00 | 10 | | 2 | 2011-01-24 12:02:00 | 2011-01-25 12:02:00 | 9 | | 2 | 2011-01-24 12:03:00 | 2011-01-25 12:03:00 | 8 | | 2 | 2011-01-24 12:00:00 | 2011-01-25 12:00:00 | 12 | | 2 | 2011-01-24 12:04:00 | 2011-01-25 12:04:00 | 7 | | 2 | 2011-01-24 12:05:00 | 2011-01-25 12:05:00 | 6 | +---------+---------------------+---------------------+-------+ 12 rows in set (0.00 sec) 

Это возвращает список user_id, который сделал более 100 загрузок за любой период в 1 день:

 SELECT user_id, count(user_id) as downloads_count, DATE(dl_date) FROM downloads GROUP BY user_id, DATE(dl_date) HAVING count(user_id) > 100; 

Если у вас есть такой период, который меньше или равен 24 часам:

 SET @period_start='2010-10-10 06:00:00'; SET @period_end='2010-10-11 05:59:59'; 

тогда,

 SELECT user_id, COUNT(id) AS num FROM downloads WHERE dl_date>= @period_start AND dl_date<= @period_end GROUP BY user_id HAVING num> 100; 

Но если у вас такой период, который превышает 24 часа:

 SET @period_start='2010-10-10 06:00:00'; SET @period_end='2011-09-17 13:15:12'; 

как вы хотите рассчитать количество загрузок? Это в 24h кусках от @period_end или от @period_start. Или вам просто нужен последний 24-часовой кусок?

Вы хотите отфильтровать два значения даты, используя группу BETWEEN, на user_id, а затем используйте HAVING для фильтрации сгруппированных результатов.

Три параметра: -Date1–, -Date2– и -Threshhold–

 select user_id , count(*) from downloads where dl_date between --Date1-- and --Date2-- group by user_id having count(*) > --Threshhold--