如何选择日期间隔

Question

这是我的桌子：

+------+--------+-------------------+
| 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次或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 |
+------+--------+-------------------+

Answer 1

是的，您需要按照@Will的建议添加主键(id)。

要获得每一项一次(而且只有一次)，无论在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)

-编辑--关于你被编辑的问题，这完全取决于你的意思。您的意思是“用户在60秒内购买了3个或3个以上的商品”还是“在输出中出现超过2次的用户”。后者很容易完成:假设上述查询的结果保存在临时表(或视图)“temp1”中：

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

Answer 2

您必须针对自己加入表(让我们调用表别名T1和T2)。然后编写WHERE子句，只过滤T1.user等于T2.user且T1.date_time和T2.date_time之间差的绝对值小于1分钟的行。

但问题是，每一行都将被选中，因为您的表中没有主键，因此无法检测某一行何时与其连接。创建一个主键(自动编号序列工作得很好)，并在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;

Answer 3

让我们试试这个：

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