返回前 N 行并将剩余行汇总为一行 - oracle sql

发布于 2024-11-09 08:02:51 字数 821 浏览 0 评论 0原文

我试图从查询中获取包含前 N 行的结果集，并将其余行汇总为一行。我提出了如下查询 - 我需要有关使用任何内置 Oracle sql 函数的建议，这些函数可以帮助解决这种情况并消除我在此 sql 中的大量冗余。

    select label, count_id from 
(
select table1.NAME as label, count(table1.id) as count_id, 
      ROW_NUMBER() OVER (order by  count(table1.id) desc) AS rn  
      from table1 
      where table1.NAME like 'D%'
      group by table1.NAME
      )
      where rn <= 9  -- get top 9 rows
union

select 'Other' as label, sum(count_id) as count_id from 
(
select label, count_id from 
(
select table1.NAME as label, count(table1.id) as count_id, 
      ROW_NUMBER() OVER (order by  count(table1.id) desc) AS rn  
      from table1 
      where table1.NAME like 'D%'
      group by table1.NAME
      )
      where rn > 9 -- get rows after row-num 9
 )

如果您对改进此查询有任何建议，请分享。

原文

I am trying to get a result set with the top N rows from the query, and have the remaining rows rolled up into a single row. I have come up with a query as below - i am needing suggestions about using any built-in oracle sql functions that can help with this scenario and eliminate a lot of the redundancy i have in this sql.

    select label, count_id from 
(
select table1.NAME as label, count(table1.id) as count_id, 
      ROW_NUMBER() OVER (order by  count(table1.id) desc) AS rn  
      from table1 
      where table1.NAME like 'D%'
      group by table1.NAME
      )
      where rn <= 9  -- get top 9 rows
union

select 'Other' as label, sum(count_id) as count_id from 
(
select label, count_id from 
(
select table1.NAME as label, count(table1.id) as count_id, 
      ROW_NUMBER() OVER (order by  count(table1.id) desc) AS rn  
      from table1 
      where table1.NAME like 'D%'
      group by table1.NAME
      )
      where rn > 9 -- get rows after row-num 9
 )

please share if you have any suggestions on improving this query.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

待天淡蓝洁白时 2024-11-16 08:02:51

这似乎是说明子查询重构的教科书案例。

以下是我的建议：

WITH q AS
(
SELECT table1.NAME AS label, COUNT(table1.id) AS count_id, 
      ROW_NUMBER() OVER (ORDER BY COUNT(table1.id) DESC) AS rn  
  FROM table1 
 WHERE table1.name LIKE 'D%'
 GROUP BY table1.name
)
SELECT label, count_id FROM q WHERE rn <= 9
UNION ALL
SELECT 'Other' AS label, SUM(count_id) AS count_id 
  FROM q
 WHERE rn > 9 
 GROUP BY 'Other';

在我的数据库表中，我尝试了这个，实际上我也得到了成本改进 - YMMV。

This appears to be a textbook case for illustrating subquery refactoring.

Here are my suggestions:

WITH q AS
(
SELECT table1.NAME AS label, COUNT(table1.id) AS count_id, 
      ROW_NUMBER() OVER (ORDER BY COUNT(table1.id) DESC) AS rn  
  FROM table1 
 WHERE table1.name LIKE 'D%'
 GROUP BY table1.name
)
SELECT label, count_id FROM q WHERE rn <= 9
UNION ALL
SELECT 'Other' AS label, SUM(count_id) AS count_id 
  FROM q
 WHERE rn > 9 
 GROUP BY 'Other';

On the table in my database I tried this on I actually got a cost improvement as well - YMMV.

回复收藏 0 原文

人间不值得 2024-11-16 08:02:51

@DCookie 的答案的另一种变化可以尝试：

WITH
q as ( 
    select
        table1.name as label,
        count(table1.id) as count_id,        
        row_number() over (order by count(table1.id) desc) as rn    
    from table1   
    where table1.name like 'D%'  
    group by table1.name
)
select
    case
        when rn <= 9
        then label
        else 'Other'
    end label,
    sum(count_id) count_id
from q
group by
    case
        when rn <= 9
        then label
        else 'Other'
    end;

Another variation on @DCookie's answer to try:

WITH
q as ( 
    select
        table1.name as label,
        count(table1.id) as count_id,        
        row_number() over (order by count(table1.id) desc) as rn    
    from table1   
    where table1.name like 'D%'  
    group by table1.name
)
select
    case
        when rn <= 9
        then label
        else 'Other'
    end label,
    sum(count_id) count_id
from q
group by
    case
        when rn <= 9
        then label
        else 'Other'
    end;

回复收藏 0 原文

~没有更多了~