mysql - 如何获取哪些用户连续进行了交易？

Question

table_1 包含以下列:user_id、order_id、transaction_time

transaction_time 的数据类型是 timestamp

SELECT user_id 
FROM table_1
WHERE transaction_time > date_sub(transaction_time, interval 20 MINUTE_SECOND)

我想找到连续交易时间不超过 20 分钟的人，但我缺乏这样做的知识。

Answer 1

试试这个，

SELECT user_id,  
       case when sum(case when  transaction_time between transaction_time and dateadd(minute,20,transaction_time) then 1 else 0 end )  > 1 
            then 'User did transaction consecutively' 
            else 'No consecutive Transaction' end
FROM table_1
group by user_id