MySQL如何编写SQL以在15分钟的窗口中查找过多的事务？ [英] MySQL how to write SQL to find excessive transactions in 15 minute windows?

问题描述

MySQL的
可以说有一个信用卡处理公司。每次使用信用卡时，都会将一行插入表中。

  create table tran（
 id int， 
 tran_dt datetime，
 card_id int，
 merchant_id int，
 amount int 
）; 
  

想要知道在同一商家的任何15分钟窗口中使用了3次以上的卡。

我的尝试：

  select card_id，date（tran_dt） ，小时（tran_dt），merchant_id，计数（*）
来自tran 
 group by card_id，date（tran_dt），hour（tran_dt），merchant_id 
有count（*）> = 3 
  

第一个问题是每小时会产生过多的交易，而不是每15分钟的窗口。第二个问题是，无法捕捉到跨越小时标记的交易，即在下午1点59分和下午2点01分。

为了使这更简单，可以将它分开小时到5分钟的增量。因此，我们不必检查1：00-1：15，1：01-1：16，等等。可以检查1：00-1：15，1：05-1：20等等，如果这样更容易。

任何想法如何解决sql？我有一种感觉，也许我需要SQL窗口功能，这是MySQL尚未提供。或者编写一个可以查看每个15块的存储过程。

http://sqlfiddle.com/#!9/f2d74/1

解决方案

您可以将日期/时间到秒，并对秒进行算术运算以在15分钟的时间间隔内获得该值：

  select card_id，min（date （tran_dt））作为first_charge_time，merchant_id，通过card_id，floor（to_seconds（tran_dt）/（60 * 15）从tran 
计数（*）
，merchant_id 
计数（* ）> = 3; 
  

以上使用 to_seconds（）。在早期版本的MySQL中，您可以使用 unix_timestamp（）。

获得任何15分钟间隔更具挑战性。您可以 将查询表示为：

  select t1。*，count（*）as numTransactions $来自tran t1的b $ b通过t1.merchant_id = t2.merchanti_d和
 t1.card_id = t2.card_id和
 t2.tran_dt> = t1加入
 tran t2 
。 tran_dt和
 t2.tran_dt< t1.tran_dt + interval 15分钟
 group by t1.id 
 having numTransactions> = 3; 
  

此查询的性能可能有问题。 trans（card_id，merchant_id，tran_dt）上的索引应该有很大的帮助。

MySQL
Lets say there is a credit card processing company. Every time a credit card is used a row gets inserted into a table.

create table tran(
  id int,
  tran_dt datetime, 
  card_id int,
  merchant_id int,
  amount int
);

One wants to know what cards have been used 3+ times in any 15 minute window at the same merchant.

My attempt:

select card_id, date(tran_dt), hour(tran_dt), merchant_id, count(*)
from tran
group by card_id, date(tran_dt), hour(tran_dt), merchant_id
having count(*)>=3

The first problem is that would give excessive transactions per hour, not per a 15 minute window. The second problem is that would not catch transactions that cross the hour mark ie at 1:59pm and 2:01pm.

To make this simpler, it would ok to split up the hour into 5 minute increments. So we would not have to check 1:00-1:15pm, 1:01-1:16pm, etc. It would be ok to check 1:00-1:15pm, 1:05-1:20pm, etc., if that is easier.

Any ideas how to fix the sql? I have a feeling maybe I need sql window functions, that are not yet available in MySQL. Or write a stored procedure that can look at each 15 block.

http://sqlfiddle.com/#!9/f2d74/1

解决方案

You can convert the date/time to seconds and do arithmetic on the seconds to get the value within a 15 minute clock interval:

select card_id, min(date(tran_dt)) as first_charge_time, merchant_id, count(*)
from tran
group by card_id, floor(to_seconds(tran_dt) / (60 * 15)), merchant_id
having count(*) >= 3;

The above uses to_seconds(). In earlier versions of MySQL, you can use unix_timestamp().

Getting any 15 minute interval is more challenging. You can express the query as:

select t1.*, count(*) as numTransactions
from tran t1 join
     tran t2
     on t1.merchant_id = t2.merchanti_d and
        t1.card_id = t2.card_id and
        t2.tran_dt >= t1.tran_dt and
        t2.tran_dt < t1.tran_dt + interval 15 minute 
group by t1.id
having numTransactions >= 3;

Performance of this query might be problematic. An index on trans(card_id, merchant_id, tran_dt) should help a lot.

这篇关于MySQL如何编写SQL以在15分钟的窗口中查找过多的事务？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！