17,377
社区成员
发帖
与我相关
我的任务
分享
SQL> with tmp as(
2 select 1 id, '不开机' as x, 900 n from dual union all
3 select 2 id, '短路' x, 200 n from dual union all
4 select 3 id, 'LanFail' x, 200 n from dual union all
5 select 4 id, 'USB' x, 150 n from dual union all
6 select 5 id, '其他' x, 100 n from dual union all
7 select 6 id, '不开机' x, 50 n from dual union all
8 select 7 id, 'network' x, 30 n from dual
9 )
10 select x,
11 sum(n) n,
12 RATIO_TO_REPORT(sum(n)) over() perct
13 from (
14 select case when rn > 3 then 'others'
15 else x
16 end x,
17 n
18 from (
19 select x,
20 n,
21 row_number() over(order by n desc) rn
22 from tmp
23 ) tmp
24 ) tmp
25 group by x
26 order by 2 desc
27 /
X N PERCT
--------- ---------- ----------
不开机 900 0.55214723
others 330 0.20245398
短路 200 0.12269938
LanFail 200 0.12269938
select decode(rn, 1, ''||标签, 2, ''||标签,3,''||标签, 'OTHERS') 标签,
decode(rn, 1, 描述, 2, 描述,3,描述, 'OTHERS') 描述,
sum(数量) 数量,
trunc(100*sum(数量)/(select sum(数量) from tab),2)||'%' 率
from (select 标签,描述,数量,row_number()over(order by 数量 desc) rn
from tab)
group by decode(rn, 1, 描述, 2, 描述,3,描述, 'OTHERS'),decode(rn, 1, ''||标签, 2, ''||标签,3,''||标签, 'OTHERS')
order by max(rn)
1 1 不开机 900 55.21%
2 2 短路 200 12.26%
3 3 LanFail 200 12.26%
4 OTHERS OTHERS 330 20.24%