RETURN THE SUM OF MAXIMUM 3 VALUES IN SQL

Navigation
4

I have a table and need to return the following: check based on the recipient column, add up the 3 largest amounts of that recipient, bring in the query only if the sum of the 3 largest are > = 1024: 

CREATE TABLE TRANSFERS(
SENDER VARCHAR2(200) NOT NULL,
RECIPIENT VARCHAR2(200) NOT NULL,
DATA DATE NOT NULL,
AMOUNT INTEGER NOT NULL);


INSERT INTO TRANSFERS VALUES ('Smith', 'Williams', '01/01/2000', 200);
INSERT INTO TRANSFERS VALUES ('Smith', 'Taylor', '27/09/2002', 1024);
INSERT INTO TRANSFERS VALUES ('Smith', 'JOHNSON', '26/06/2005', 512);
INSERT INTO TRANSFERS VALUES ('Williams', 'JOHNSON', '17/12/2010', 100);
INSERT INTO TRANSFERS VALUES ('Williams', 'JOHNSON', '22/03/2004', 10);
INSERT INTO TRANSFERS VALUES ('Brown', 'JOHNSON', '20/03/2013', 500);
INSERT INTO TRANSFERS VALUES ('JOHNSON', 'Williams', '02/06/2007', 400);
INSERT INTO TRANSFERS VALUES ('JOHNSON', 'Williams', '26/06/2005', 400);
INSERT INTO TRANSFERS VALUES ('JOHNSON', 'Williams', '26/06/2005', 200);

In this scenario, SELECT must include the names of Taylor and JHONSON because, in a transfer, Taylor already has the value of 1024, since Jhonson is the sum of the 3 largest of it as Recipient is: 512, 500 and 100 = 1112; / p>

I just managed to bring Taylor: 

SELECT T.RECIPIENT AS NOME
FROM TRANSFERS T
WHERE T.AMOUNT >= 1024;

Thank you!

    
asked by anonymous 12.06.2018 / 19:56

3 answers

4

Another approach, using row_number () : 

select 
    x.recipient,
    sum(x.amount) as soma
from 
    (select
         t.recipient,
         t.amount,
         row_number() over (partition by recipient order by amount desc) as i
     from transfers t) x
where x.i <=3
group by x.recipient
having sum(x.amount) >= 1024;

As for performance, I do not know which would be better, just doing a test.

    
12.06.2018 / 21:15
2

You can create a PL/PgSQL function to perform this calculation, see: 

CREATE OR REPLACE FUNCTION fc_obter_top3()
RETURNS TABLE ( RECIPIENT  VARCHAR(200), AMOUNT BIGINT ) AS 
$$
DECLARE
   rec RECORD;
BEGIN
   FOR rec IN SELECT trsf.RECIPIENT FROM TRANSFERS AS trsf GROUP BY trsf.RECIPIENT
   LOOP
       RETURN QUERY SELECT
                      rec.RECIPIENT,
                      SUM(top.AMOUNT) 
                    FROM
                      (SELECT t.AMOUNT FROM TRANSFERS AS t WHERE t.RECIPIENT = rec.RECIPIENT ORDER BY t.AMOUNT DESC LIMIT 3) AS top
                    HAVING
                      SUM(top.AMOUNT) >= 1024;
   END LOOP;
END;
$$
LANGUAGE plpgsql;

Testing: 

SELECT * FROM fc_obter_top3();

Output: 

| recipient | amount |
|-----------|--------|
|   JOHNSON |   1112 |
|    Taylor |   1024 |

SQLFiddle

    
12.06.2018 / 22:47
1
SELECT 
  T.RECIPIENT AS NOME,
  SUM(T_TMP.AMOUNT) AS TOTAL
FROM TRANSFERS T
JOIN (SELECT TOP 3 T2.RECIPIENT, T2.AMOUNT FROM TRANSFERS T2 ORDER BY T2.AMOUNT DESC) AS T_TMP  ON T.RECIPIENT = T_TMP.RECIPIENT
GROUP BY T.RECIPIENT
HAVING SUM(T.AMOUNT) >= 1024;

It's not very performative but I think it should solve the problem.

obs: in this link you can test the above solution ( sql server ).

edited

to postgresql (also with a example here ): / p> 

SELECT 
  T.RECIPIENT AS NOME,
  SUM(T_TMP.AMOUNT) AS TOTAL
FROM TRANSFERS T
JOIN (SELECT T2.RECIPIENT, T2.AMOUNT FROM TRANSFERS T2 ORDER BY T2.AMOUNT DESC LIMIT 3) AS T_TMP  ON T.RECIPIENT = T_TMP.RECIPIENT
GROUP BY T.RECIPIENT
HAVING SUM(T.AMOUNT) >= 1024;
    
12.06.2018 / 20:02
Expression-bodied is recommended? Does it have performance differences? How to add a div or p within a table