如何获得大于一定数量的总和 [英] How to get summation with count larger than certain amount

问题描述

我想使用一条SQL，使接收者收到的金额> = 1024，且转帐数量<== 3。

I want to have a sql that gives recipient that received of amount >= 1024, with number of transfer <=3.

例如，

结果是：

由于列出了
Johnson帐户，因此列出了Johnson，因为已收到Johnson帐户以下三种转帐中的1112 USD：512 USD + 100 USD + 500 USD，Taylor进行1次转账USD1024。威廉姆斯不在，因为他在四笔交易中收到1200。

Johnson is listed since Johnson account is listed because it has received 1112 USD in the following three transfers: 512 USD + 100 USD + 500 USD, Taylor is with 1 transfer of USD 1024. Williams is not there since he receives 1200 in four transactions.

我尝试

Select recipient as account_name from transfers group by recipient 
having sum(amount)>=1024  and count(amount)<=3

它无法正常工作。
我正在使用PostgreSQL，SQLLites语法也很好。

It doesn't work correctly. I am using PostgreSQL, SQLLites syntax is fine too.

为方便起见，附上了表格和行的创建

Attached is the table and row creation for your convenient

 create table transfers (
      sender varchar(1000) not null,
      recipient varchar(1000) not null,
      date date not null,
      amount integer not null
  );

insert into transfers values('Smith','Taylor',convert(date,'2002-09-27'),'1024')
insert into transfers values('Smith','Johnson',convert(date,'2005-06-26'),'512')
insert into transfers values('Williams','Johnson',convert(date,'2010-12-17'),'100')
insert into transfers values('Williams','Johnson',convert(date,'2004-03-22'),'10')
insert into transfers values('Brown','Johnson',convert(date,'2013-03-20'),'500')
insert into transfers values('Johnson','Williams',convert(date,'2007-06-02'),'400')
insert into transfers values('Johnson','Williams',convert(date,'2005-06-26'),'400')
insert into transfers values('Johnson','Williams',convert(date,'2005-06-26'),'200')

推荐答案

使用 row_number（）和派生表来限制每个收件人到他们收到的前三笔金额，然后按收件人分组，返回 sum（amount）> = 1024

using row_number() and a derived table to limit each recipient to their top 3 amounts received, then grouping by recipient returning those having sum(amount)>=1024

select recipient as account_name
from (
  select * 
    , row_number() over (
        partition by recipient
        order by amount desc
        ) as rn
  from transfers
  ) as i
where rn < 4
group by recipient
having sum(amount)>=1024

返回：

+--------------+
| account_name |
+--------------+
| Johnson      |
| Taylor       |
+--------------+

postgres演示： http://rextester.com/PFR74297

rextester postgres demo: http://rextester.com/PFR74297

已对问题进行了编辑，从第三版修订本中删除了一些相关信息。问题：已经尝试过什么。

The question was edited that removed some pertinent information from the 3rd revision of the question: what was tried already.

我尝试

I try

从以下位置选择收件人作为account_name按收件人分组转帐

总金额（金额）> = 1024，计数（金额）< = 3

它无法正常工作。

根据这些信息，我得出结论，OP希望找到收件人从任何收件人的任何3个或更少的转账中收到 sum（amount）> = 1024 -不限于那些转账少于3个且<$ c $的收件人c> sum（amount）> = 1024 。

Based on that information, I concluded that OP wanted to find recipients that received a sum(amount)>=1024 from 3 or fewer of any of that recipient's transfers -- not limited to those recipients with 3 or fewer transfers and sum(amount)>=1024.

这篇关于如何获得大于一定数量的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！