SQL和多组聚合

Question

我需要编写一个MySQL查询，该查询返回在最多三次交易中至少收到$1000的收件人列表。

以下是一个表的示例(第一列是接收者(文本列)，第二列对应于他/她的交易金额(int))：

recipient  amount 
1          200
1          10000
2          100
2          10
3          500
4          400
4          400
4          200
3          200
3          100

我知道怎么做：

select user
from t
group by user
having count(*) <= 3 and
       sum(amount) >= 1000;

但在给出下一个注释的任务中：“请注意，该收件人可以有三笔以上的交易，只要三笔或更少的交易至少达到1000美元。”

我完全卡住了，在网上找不到答案。

Answer 1

假设性能不是你的主要关注点，这应该是可行的，并且还会显示有3个以上交易但前3个交易超过1000的收件人：

select distinct t1.recipient
from 
  (select recipient,amount from t order by amount desc) t1 
left join
  (select recipient,amount from t order by amount desc) t2
on t1.recipient = t2.recipient and t2.amount < t1.amount 

left join
  (select recipient,amount from t order by amount desc) t3
     on t1.recipient = t3.recipient and t3.amount < t2.amount

where 
  t1.amount + coalesce(t2.amount, 0) + coalesce(t3.amount, 0) >= 1000;

经过进一步思考后，您实际上需要一个独立的唯一列才能使此功能100%正常工作。由于您可以清楚地检查，收件人"4“没有显示在这里。我们可以通过使用另一个id列并将金额值与<=而不是<：

select distinct t1.recipient
from 
  (select id,recipient,amount from t order by amount desc) t1 
left join
  (select id,recipient,amount from t order by amount desc) t2
on t1.recipient = t2.recipient and t1.id <> t2.id and t2.amount <= t1.amount 

left join
  (select id,recipient,amount from t order by amount desc) t3
     on t1.recipient = t3.recipient and t1.id<>t3.id and t3.id<>t2.id and t3.amount <= t2.amount

where 
  t1.amount + coalesce(t2.amount, 0) + coalesce(t3.amount, 0) >= 1000;

Answer 2

这是其中一种方法。首先选择条目不超过3个的收件人，然后仅筛选该收件人集中的总和：

SELECT recipient 
FROM usr GROUP BY recipient 
HAVING recipient IN 
    (SELECT recipient AS c FROM usr GROUP BY recipient HAVING COUNT(*) <= 3)  AND 
    sum(amount) >= 1000