MySQL 如何在任何 15 分钟的窗口中总结过多的交易量？

Question

MySQL

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

#Customer #1 
insert into tran values(11, '2015-01-01 01:59:00', 1, 1, 2);
insert into tran values(12, '2015-01-01 02:01:00', 1, 1, 4);
insert into tran values(13, '2015-01-01 02:02:00', 1, 1, 6);

#Customer #2 
insert into tran values(21, '2015-01-01 01:00:00', 2, 1, 10);
insert into tran values(22, '2015-01-01 01:01:00', 2, 1, 20);
insert into tran values(23, '2015-01-01 01:02:00', 2, 1, 30);
insert into tran values(24, '2015-01-01 01:03:00', 2, 1, 20);
insert into tran values(29, '2015-01-02 01:03:00', 2, 1, 10);

#Customer #3 
insert into tran values(31, '2015-01-01 01:00:00', 3, 1, 100);
insert into tran values(32, '2015-01-01 01:00:00', 3, 1, 200);
insert into tran values(33, '2015-01-01 01:00:00', 3, 1, 100);
insert into tran values(34, '2015-01-01 01:00:00', 3, 1, 200);
insert into tran values(35, '2015-01-01 01:00:00', 3, 1, 100);

在同一商户的任何 15 分钟窗口中哪些卡已被使用 3 次以上的报告:

SELECT t1.card_id, t1.merchant_id, count(distinct t1.id)+1 as Count, sum(t1.amount) 'SumAmount'
FROM tran t1 
INNER JOIN tran t2
on t2.card_id=t1.card_id 
  and t2.merchant_id=t1.merchant_id 
  and t2.tran_dt <= DATE_ADD(t1.tran_dt, INTERVAL 15 MINUTE)
  and t2.id>t1.id
GROUP BY t1.card_id,t1.merchant_id
HAVING Count>2;

查询现在可以完美地计算卡片。

最后，我试图添加一个名为 SumAmount 的列来给出金额的总和。但是 SumAmount 显示错误的对数大数额，因为它是自连接。我想我只需要 distinct 行 ID 的总和，就像 Count 列一样。 SumAmount 如何固定？

http://www.sqlfiddle.com/#!9/1bbd0/6

Answer 1

您的查询有点不对劲，因为它缺少交易 ID。但是，您真正的问题是您想从 t2 而不是 t1 中求和:

SELECT t1.card_id, t1.merchant_id, t1.tran_dt,
       count(distinct t2.id) + 1 as Count, sum(t2.amount) as SumAmount
FROM tran t1 INNER JOIN
     tran t2
     on t2.card_id = t1.card_id and
        t2.merchant_id = t1.merchant_id and
        t2.tran_dt >= t1.tran_dt and
        t2.tran_dt <= DATE_ADD(t1.tran_dt, INTERVAL 15 MINUTE) 
GROUP BY t1.id, t1.card_id, t1.merchant_id, t1.tran_dt
HAVING Count > 2;

注意:我将 join 条件从 > 更改为 >= 以包含第一笔交易。另外，我在日期上添加了 >=，并将 SELECT 中的计数更改为第二个表，而不是第一个。