Oracle: 쿼리를 통해 전체 비율을 얻는 방법은 무엇입니까?

Oracle: 쿼리를 통해 전체 비율을 얻는 방법은 무엇입니까?

저는 다음과 같은 질문이 있는데 그 결과는 저에게 매우 뜻밖입니다.

select stato, (count(1) *100) / sum(1)
from LOG_BONIFICA
group by stato;

모든 행에 대해 100을 반환합니다.달라야 하나요?

count(1)와 같음sum(1)당신의 경우에는

다음과 같은 방법을 사용해 보십시오.

18:39:36 SYSTEM@dwal> ed
Wrote file S:\\tools\buffer.sql

  1  select owner,
  2         count(*) group_cnt,
  3         sum(count(*)) over() total_cnt,
  4         round(100*(count(*) / sum(count(*)) over ()),2) perc
  5    from dba_objects
  6   group by owner
  7*  order by 4 desc
18:39:57 SYSTEM@dwal> /

OWNER                           GROUP_CNT  TOTAL_CNT       PERC
------------------------------ ---------- ---------- ----------
SYS                                 31609      59064      53.52
PUBLIC                              24144      59064      40.88
XDB                                  1660      59064       2.81
SYSTEM                                597      59064       1.01
WMSYS                                 332      59064        .56
EXFSYS                                312      59064        .53
IRKAZDATA                             158      59064        .27
STRMADMIN                              92      59064        .16
DBSNMP                                 55      59064        .09
RI                                     25      59064        .04
PASS                                   16      59064        .03
POTS                                   19      59064        .03
TI                                     11      59064        .02
STRMODS                                11      59064        .02
OUTLN                                  10      59064        .02
APPQOSSYS                               5      59064        .01
ORACLE_OCM                              8      59064        .01

17 rows selected.

Elapsed: 00:00:00.16

업데이트: 또는 ratio_to_report를 통해 훨씬 더 단순화

18:53:36 SYSTEM@dwal> ed
Wrote file S:\\tools\buffer.sql

  1  select owner,
  2         round(100*ratio_to_report(count(*)) over (), 2) perc
  3    from dba_objects
  4   group by owner
  5*  order by 2 desc
18:54:03 SYSTEM@dwal> /

OWNER                                PERC
------------------------------ ----------
SYS                                 53.52
PUBLIC                              40.88
XDB                                  2.81
SYSTEM                               1.01
WMSYS                                 .56
EXFSYS                                .53
IRKAZDATA                             .27
STRMADMIN                             .16
DBSNMP                                .09
RI                                    .04
PASS                                  .03
POTS                                  .03
TI                                    .02
STRMODS                               .02
OUTLN                                 .02
APPQOSSYS                             .01
ORACLE_OCM                            .01

17 rows selected.

Elapsed: 00:00:00.20

다음 사항을 말씀하시는 건가요?당신은 총계를 따로 찾아야 합니다.

• SQLFIDDLE 참조

표:

ID  NAME    AMOUNT  STATE
1   john    1000    fl
2   jane    5000    ga
3   james   2000    ca
4   tom     6000    ga
5   tim     8000    fl
6   jim     2000    ga
7   kate    8000    fl
8   jack    3000    tx

결과 1:

STATE   SUM(AMOUNT)
ca      2000
fl      17000
ga      13000
tx      3000

최종 쿼리:

select t1.state, concat(
  round(((sum(t1.amount)/t2.total))*100,2),
'%') as pct
from t1, 
(select sum(amount) as total from t1) as t2
group by t1.state
;

결과:

   STATE    COUNT(T1.ID)    SUM(T1.AMOUNT)  TOTAL   PCT
   ca       1               2000           35000    5.71%
   fl       3               17000          35000    48.57%
   ga       3               13000          35000    37.14%
   tx       1               3000           35000    8.57%

ratio_to_report 분석 함수 사용:

SELECT STATO, 
    COUNT(1) STATO_COUNT, 
    RATIO_TO_REPORT(COUNT(1)) OVER() * 100 STATO_PERCENT
FROM LOG_BONIFICA
GROUP BY STATO

따라서 전체 행 수와 비율을 직접 계산할 필요가 없습니다.

언급URL : https://stackoverflow.com/questions/13721093/oracle-how-to-get-percent-of-total-by-a-query