MySQL 如何编写 SQL 以在 15 分钟的窗口中查找过多的事务？

Question

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), hour(tran_dt), merchant_id, count(*)
from tran
group by card_id, date(tran_dt), hour(tran_dt), merchant_id
having 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 的任何想法？我有一种感觉，也许我需要 MySQL 中尚不可用的 sql 窗口函数。或者写一个可以查看每15个 block 的存储过程。

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

Answer 1

您可以将日期/时间转换为秒，并对秒进行算术运算，以获得 15 分钟时钟间隔内的值:

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;

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

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

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;

此查询的性能可能存在问题。 trans(card_id, Merchant_id, tran_dt) 上的索引应该会有很大帮助。