甲骨文前5名及其他

Question

我有以下代码，它按NetTotal返回前5位：

SELECT
  *
  FROM (SELECT
      b.BROKERAGE_NAME,
      SUM(s.STATEMENT_NET) AS NetTotal
    FROM TBLSTATEMENTSNEW s
    LEFT JOIN TBLBROKERAGESNEW b
      ON s.BROKERAGE_ID = b.ID
    WHERE s.STATEMENT_DATE BETWEEN To_date('01-AUG-2017') AND To_date('05-AUG-2017')
    GROUP BY b.BROKERAGE_NAME
    ORDER BY NetTotal DESC) st
WHERE rownum <= 5
ORDER BY rownum;

是否有可能将非前5位NetTotals相加为第六行，BROKERAGE_NAME为"Other"？

我希望产出如下:-

BROKERAGE_NAME  NetTotal
--------------  --------
Bro1                 222
Bro2                 333
Bro3                 444
Bro4                 555
Bro5                 666
Other            3143514

Answer 1

使用子查询保理子句重新使用您生成的有序查询：

WITH totals AS (
  SELECT ROWNUM AS rn,
         t.*
  FROM   (
    SELECT b.BROKERAGE_NAME,
           SUM(s.STATEMENT_NET) AS NetTotal
    FROM   TBLSTATEMENTSNEW s
           LEFT JOIN TBLBROKERAGESNEW b
           ON s.BROKERAGE_ID = b.ID
    WHERE s.STATEMENT_DATE BETWEEN DATE '2017-08-01' AND DATE '2017-08-05'
    GROUP BY b.BROKERAGE_NAME
    ORDER BY NetTotal DESC
  ) t
)
SELECT BROKERAGE_NAME,
       NetTotal
FROM   totals
WHERE  rn <= 5
UNION ALL
SELECT 'Other',
       SUM( NetTotal )
FROM   totals
WHERE  rn > 5;

Answer 2

以下是另一种选择：

SELECT CASE WHEN rn <= 5 THEN rn
            ELSE 6
       END row_num,
       CASE WHEN rn <= 5 THEN brokerage_name
            ELSE 'Other'
       END brokerage_name,
       SUM(nettotal) AS nettotal
FROM   (SELECT b.brokerage_name,
           SUM(s.statement_net) AS nettotal,
               row_number() OVER (ORDER BY SUM(s.statement_net) DESC) rn
      FROM   tblstatementsnew s
               LEFT JOIN tblbrokeragesnew b ON s.brokerage_id = b.id
      WHERE  s.statement_date BETWEEN to_date('01-AUG-2017', 'dd-MON-yyyy') AND to_date('05-AUG-2017', 'dd-MON-yyyy')
      GROUP  BY b.brokerage_name
      ORDER  BY nettotal DESC) st
GROUP BY CASE WHEN rn <= 5 THEN rn
              ELSE 6
         END row_num,
         CASE WHEN rn <= 5 THEN brokerage_name
              ELSE 'Other'
         END;

您应该测试每一种解决方案，看看哪种解决方案最适合您的数据。

Answer 3

您可以使用解析的row_number()函数：

SELECT case when rn <= 5 then BROKERAGE_NAME else 'Other' end
     , sum(NetTotal) 
  FROM (SELECT BROKERAGE_NAME, NetTotal, row_number() over (order by NetTotal DESC) rn
          FROM (SELECT b.BROKERAGE_NAME
                     , SUM(s.STATEMENT_NET) AS NetTotal
                  FROM TBLSTATEMENTSNEW s
                       LEFT JOIN TBLBROKERAGESNEW b
                              ON s.BROKERAGE_ID = b.ID
                 WHERE s.STATEMENT_DATE BETWEEN To_date('01-AUG-2017') AND To_date('05-AUG-2017')
                 GROUP BY b.BROKERAGE_NAME
                ) 
       )
group by case when rn <= 5 then BROKERAGE_NAME else 'Other' end
ORDER BY min(case when rn <= 5 then rn else 6 end);

如果您需要处理一个不太可能(但可能)被称为“其他”的券商的情况，那么这将解决这一问题：

SELECT BROKERAGE_NAME, NetTotal
  FROM (SELECT case when rn <= 5 then BROKERAGE_NAME else 'Other' end BROKERAGE_NAME
             , case when rn <= 5 then rn else 6 end as rn2
             , sum(NetTotal) NetTotal
          FROM (SELECT BROKERAGE_NAME, NetTotal, row_number() over (order by NetTotal DESC) rn
                  FROM (SELECT b.BROKERAGE_NAME
                             , SUM(s.STATEMENT_NET) AS NetTotal
                          FROM TBLSTATEMENTSNEW s
                               LEFT JOIN TBLBROKERAGESNEW b
                                    ON s.BROKERAGE_ID = b.ID
                         WHERE s.STATEMENT_DATE BETWEEN To_date('01-AUG-2017') AND To_date('05-AUG-2017')
                         GROUP BY b.BROKERAGE_NAME
                       )
                ) 
          group by case when rn <= 5 then BROKERAGE_NAME else 'Other' end BROKERAGE_NAME
                 , case when rn <= 5 then rn else 6 end as rn2
          ORDER BY rn2);