Как выбрать интервал дат

Это моя таблица:

+------+--------+-------------------+
| user | item | date_time |
| 10 | 01 | 10-10-10 20:10:05 |
| 10 | 02 | 10-10-10 20:10:10 |
| 10 | 03 | 10-10-10 20:10:10 |
| 20 | 02 | 10-10-10 20:15:10 |
| 20 | 02 | 10-10-10 20:20:10 |
| 30 | 10 | 10-10-10 20:01:10 |
| 30 | 20 | 10-10-10 20:01:20 |
| 30 | 30 | 10-10-10 20:05:20 |
+------+--------+-------------------+

Я хочу сделать запрос, который возвращает пользователя, который взял несколько элементов за 1мин, например, этот результат:

+------+--------+-------------------+
| user | item | date_time |
| 10 | 01 | 10-10-10 20:10:05 |
| 10 | 02 | 10-10-10 20:10:10 |
| 10 | 03 | 10-10-10 20:10:10 |
| 30 | 10 | 10-10-10 20:01:10 |
| 30 | 20 | 10-10-10 20:01:20 |
+------+--------+-------------------+

Как я могу это сделать?

Изменить

И если я хочу показывать только пользователей, которые появляются на этом выходе 2 или более раз?

Пример:

+------+--------+-------------------+
| user | item | date_time |
| 10 | 01 | 10-10-10 20:10:05 |
| 10 | 02 | 10-10-10 20:10:10 |
| 10 | 03 | 10-10-10 20:10:10 |
+------+--------+-------------------+
3 ответа

Да, вам нужно добавить первичный ключ (id), как было предложено @Will.

Чтобы получить каждый элемент один раз (и только один раз) независимо от того, сколько совпадений было в течение 1-минутного окна, попробуйте подзапрос вместо полного соединения:

Select user,item,date_time from my_table t1
 where id in (select t2.id from my_table t2,my_table t3
 where t2.id <> t3.id and t2.user = t3.user 
 and abs(t2.date_time - t3.date_time) < 60)

- edit-- Для вашего отредактированного вопроса это зависит от того, что вы имеете в виду. Вы имеете в виду "пользователей, которые купили 3 или более элементов в течение 60 секунд" или "пользователи, которые появляются более чем в 2 раза на выходе". Последнее легко сделать: предположим, что результаты вышеуказанного запроса сохраняются во временной таблице (или в представлении) "temp1":

select * from my_table where user in 
 (select user from temp1 group by user having count(*) > 2);


Вам придется присоединиться к таблице против себя (позвоните в табличные псевдонимы T1 и T2). Затем напишите предложение WHERE, чтобы фильтровать только строки, где T1.user равно T2.user, а абсолютное значение разницы между T1.date_time и T2.date_time меньше минуты.

Но проблема в том, что каждая строка будет выбрана, потому что у вас нет первичного ключа в вашей таблице, поэтому нет способа обнаружить, когда строка соединяется с самим собой. Создайте первичный ключ (последовательность автонабора будет работать нормально) и добавьте условие в предложение WHERE, которое говорит T1.id <> T2.id.

Итак, в (непроверенной) форме кода:

SELECT *
FROM stuff T1, stuff T2
WHERE T1.user = T2.user
 AND ABS(UNIX_TIMESTAMP(T1.date_time) - UNIX_TIMESTAMP(T2.date_time)) < 60
 AND T1.id <> T2.id;


попробуйте следующее:

SELECT * FROM myTable 
JOIN (SELECT MAX(date_time) AS maxi, MIN(date_time) AS mini, user AS uid FROM myTable GROUP BY uid) AS otherTable 
ON date_time<=maxi AND date_time>=mini AND user = uid AND UNIX_TIMESTAMP(maxi) - UNIX_TIMESTAMP(mini) < 60 
GROUP BY uid, date_time

licensed under cc by-sa 3.0 with attribution.