在10分钟内找到连续的交易 [英] find consecutive transaction within 10 minutes

问题描述

我有这样的桌子

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上的演示

这篇关于在10分钟内找到连续的交易的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！