SQL Oracle summary table query with top-n on each group-CodePudding

i have two table with desc like this

Transaction Table CustID | fk to customer table, Amount | amount spend on each transaction

Customer Table CustID | pk, Age | customer age

i need to make a query to group or bucketing the customer by age range and i need to make top-3 amount highest amount each age group also the bottom 3 amount or lowest spender so far what i have done is https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=ccf988ec525dc133cf7825ba052e8054

what i manage so far inner query

I have try to use CTE, but still not sure how to get the top 3 on each group/bottom 3 of each group with cte

the problem is on the first picture on the first row, the return is more than 3/ they append all of the records while i just need only the top 3

Thank you for the help

CodePudding user response：

Because there are no descriptions of tables, I just tried to change a little bit Could you try:

SELECT   age_bucket,
         Listagg(
         CASE
                  WHEN seqnumbyeachcusttop = 1 THEN amount
         END, ',') within GROUP (ORDER BY amount DESC)            AS test_top_3_amouny,
         listagg(amount, ',') within GROUP (ORDER BY amount DESC) AS top_3_principal
FROM     (
                  SELECT   age_bucket,
                           seqnumbyeachcusttop,
                           amount,
                           row_number() OVER (partition BY amount ORDER BY amount DESC) rn
                  FROM     (
                                    SELECT   t.amount                                                          AS amount,
                                             t.cust_id                                                         AS cust_id,
                                             row_number() OVER (partition BY t.cust_id ORDER BY t.amount DESC) AS seqnumbyeachcusttop,
                                             CASE
                                                      WHEN c.age <= 20 THEN ' <= 20'
                                                      WHEN c.age BETWEEN 21 AND      30 THEN '21-30'
                                                      WHEN c.age BETWEEN 31 AND      40 THEN '31-40'
                                                      WHEN c.age BETWEEN 41 AND      50 THEN '41-50'
                                                      WHEN c.age >= 51 THEN '>= 51'
                                             END AS age_bucket
                                    FROM     TRANSACTION t
                                    JOIN     customer c
                                    ON       t.cust_id=c.cust_id ))
WHERE    rn <= 3) GROUP BY age_bucket ORDER BY age_bucket ASC;

CodePudding user response：

You can use the ROW_NUMBER() analytic function to find the highest and the lowest and then you can use conditional aggregation within LISTAGG to only display the highest and lowest in their corresponding lists:

select age_bucket, 
       LISTAGG(CASE WHEN max_rn <= 3 THEN amount END, ',')
         WITHIN GROUP (ORDER BY amount DESC) AS TOP_3_AMOUNT,
       LISTAGG(CASE WHEN min_rn <= 3 THEN amount END, ',')
         WITHIN GROUP (ORDER BY amount ASC ) AS BOTTOM_3_AMOUNT
FROM   (
  SELECT amount,
         age_bucket,
         ROW_NUMBER() OVER (PARTITION BY age_bucket ORDER BY amount DESC) AS max_rn,
         ROW_NUMBER() OVER (PARTITION BY age_bucket ORDER BY amount ASC ) AS min_rn
  FROM   (
    select t.amount,
           case
           when c.age <= 20             THEN '<= 20'
           when c.age between 21 and 30 THEN '21-30'
           when c.age between 31 and 40 THEN '31-40'
           when c.age between 41 and 50 THEN '41-50'
           when c.age >= 51             THEN '>= 51'
           END as age_bucket 
    from   transaction t
           join customer c on t.cust_id=c.cust_id
  )
)
WHERE max_rn <= 3
OR    min_rn <= 3
group by age_bucket
order by age_bucket asc;

db<>fiddle here