每个组上带有top-n的SQL Oracle汇总表查询

Question

我有两张桌子像这样

交易表CustID _ fk到customer表，在每个事务上花费的金额

客户表CustID + pk， age =客户年龄

我需要按年龄范围对组或客户进行查询，我需要使前3名金额最高，每个年龄组的金额也是最低的3名或最低的支出者，到目前为止，我所做的是11.2&fiddle=ccf988ec525dc133cf7825ba052e8054。

到目前为止我所管理的 内部查询

我已经尝试使用CTE，但仍然不确定如何在每个组/每个组的底部3上获得与cte的前3名。

问题是在第一行的第一张图片上，返回超过3/他们附加所有的记录，而我只需要前3。

谢谢你的帮助

Answer 1

您可以使用ROW_NUMBER()解析函数查找最高和最低的值，然后可以使用LISTAGG中的条件聚合在相应的列表中只显示最高和最低的值：

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 https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=f0f388ecf07a128a67ca9e3b2d701b30

我需要使它只在前3或底部3不同的客户身份。

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,
         CASE max_cust_rn
         WHEN 1
         THEN ROW_NUMBER() OVER (PARTITION BY age_bucket ORDER BY amount DESC)
         END AS max_rn,
         CASE min_cust_rn
         WHEN 1
         THEN ROW_NUMBER() OVER (PARTITION BY age_bucket ORDER BY amount ASC )
         END AS min_rn
  FROM   (
    SELECT amount,
           age_bucket,
           ROW_NUMBER() OVER (
             PARTITION BY age_bucket, cust_id ORDER BY amount DESC) AS max_cust_rn,
           ROW_NUMBER() OVER (
             PARTITION BY age_bucket, cust_id ORDER BY amount ASC ) AS min_cust_rn
    FROM   (
      select t.amount,
             t.cust_id,
             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_cust_rn = 1
  OR    min_cust_rn = 1
)
WHERE max_rn <= 3
OR    min_rn <= 3
group by age_bucket
order by age_bucket asc;

db<>fiddle https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=7932b78fda38627c775eca777c737943

Answer 2

由于没有对表的描述，我只是尝试更改一下，您能试一下：

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;