前五名和其他甲骨文

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;

是否可以将非TOP5 NetTotals求和为第六行，并使用"其他"的经纪人name？

我希望输出为： -

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);