在10分钟内找到连续的交易

本文介绍了在10分钟内找到连续的交易的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有这样的桌子

user_id order_id create_time payment_amount product 101 10001 2018-04-02 5:26 48000 chair 102 10002 2018-04-02 7:44 25000 sofa 101 10003 2018-04-02 8:34 320000 ac 101 10004 2018-04-02 8:37 180000 water 103 10005 2018-04-02 9:32 21000 chair 102 10006 2018-04-02 9:33 200000 game console 103 10007 2018-04-02 9:36 11000 chair 107 10008 2018-04-02 11:05 10000 sofa 105 10009 2018-04-02 11:06 49000 ac 101 10010 2018-04-02 12:05 1200000 cc 105 10011 2018-04-02 12:12 98000 ac 103 10012 2018-04-02 13:11 85000 insurance 106 10013 2018-04-02 13:11 240000 cable tv 108 10014 2018-04-02 13:15 800000 financing 106 10015 2018-04-02 13:18 210000 phone

我的目标是找出哪个用户在10分钟内连续进行交易. 我正在使用mysql

my goal is to find which user did transaction consecutively less than 10min. I'm using mysql

推荐答案

根据表中日期的格式，您需要使用STR_TO_DATE进行转换以在查询中使用它们.如果您的列是实际上是datetime类型，而这仅仅是显示该格式的显示代码，则只需将该查询中的STR_TO_DATE(xxx, '%m/%d/%Y %k:%i')替换为xxx.

Based on the format of your dates in the table, you will need to convert them using STR_TO_DATE to use them in a query. If your column is actually a datetime type, and that is just your display code outputting that format, just replace STR_TO_DATE(xxx, '%m/%d/%Y %k:%i') in this query with xxx.

在彼此之间10分钟之内查找订单的方法是在user_id，order_id上自动联接表，而第二个订单的时间则在第一个订单的时间之内且在10分钟后: /p>

The way to find orders within 10 minutes of each other is to self-join your table on user_id, order_id and the time on the second order being within the time of the first order and 10 minutes later:

SELECT t1.user_id, t1.create_time AS order1_time, t2.create_time AS order2_time FROM transactions t1 JOIN transactions t2 ON t2.user_id = t1.user_id AND t2.order_id != t1.order_id AND STR_TO_DATE(t2.create_time, '%m/%d/%Y %k:%i') BETWEEN STR_TO_DATE(t1.create_time, '%m/%d/%Y %k:%i') AND STR_TO_DATE(t1.create_time, '%m/%d/%Y %k:%i') + INTERVAL 10 MINUTE

输出:

user_id order1_time order2_time 101 4/2/2018 8:34 4/2/2018 8:37 103 4/2/2018 9:32 4/2/2018 9:36 106 4/2/2018 13:11 4/2/2018 13:18

dbfiddle上的演示