计算百分比的分析函数

以下sql是求出销售额在前二十名的客户，并把每个客户所占二十名的比例显示出来：

select ih.line_number,
en.entity_name,
ct.customer_number || ' ' || ct.customer_name customer,
ih.qty,
ih.amount,
round(RATIO_TO_REPORT(ih.amount) OVER(), 4) AS rr
from (select a.customer_id,
a.entity_id,
sum(nvl(b.quantity, 0)) qty,
sum(round(nvl(b.unit_price, 0) * nvl(b.quantity, 0) *
nvl(a.currency_rate, 1) *
(nvl(a.tax_rate, 0) / 100 + 1),
0)) amount,
row_number() over(order by sum(round(nvl(b.unit_price, 0) * nvl(b.quantity, 0) * nvl(a.currency_rate, 1) * (nvl(a.tax_rate, 0) / 100 + 1), 0)) desc) line_number
from INVOICE_HEADERS A, INVOICE_LINES B
WHERE A.ENTITY_ID = B.ENTITY_ID
AND A.INVOICE_ID = B.INVOICE_ID
and a.entity_id = 2
AND A.TRANSACTION_TYPE_ID = 31
group by a.customer_id, a.entity_id) ih,
customers ct,
entities en
where ih.entity_id = ct.entity_id
and ih.customer_id = ct.customer_id
and ih.entity_id = en.entity_id
and ih.line_number <= 20
-- order by ih.line_number

RATIO_TO_REPORT:产生一个百分比

row_number()：产生一个排名序号

ratio_to_report函数的介绍

1 select bill_month,area_code,sum(local_fare) local_fare,
2 ratio_to_report(sum(local_fare)) over
3 ( partition by bill_month ) area_pct
4 from t
5* group by bill_month,area_code
SQL> break on bill_month skip 1
SQL> compute sum of local_fare on bill_month
SQL> compute sum of area_pct on bill_month
SQL> /

BILL_MONTH AREA_CODE LOCAL_FARE AREA_PCT
---------- --------- ---------------- ----------
200405 5761 13060.433 .171149279
5762 12643.791 .165689431
5763 13060.433 .171149279
5764 12487.791 .163645143
5765 25057.736 .328366866
********** ---------------- ----------
sum 76310.184 1

200406 5761 13318.930 .169050772
5762 12795.060 .162401542
5763 13318.930 .169050772
5764 13295.187 .168749414
5765 26058.460 .330747499
********** ---------------- ----------
sum 78786.567 1

200407 5761 13710.265 .170545197
5762 13224.297 .164500127
5763 13710.265 .170545197
5764 13444.093 .167234221
5765 26301.881 .327175257
********** ---------------- ----------
sum 80390.801 1

200408 5761 14135.781 .170911147
5762 13376.468 .161730539
5763 14135.781 .170911147
5764 13929.694 .168419416
5765 27130.638 .328027751
********** ---------------- ----------
sum 82708.362 1


20 rows selected.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/77580/viewspace-212745/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/77580/viewspace-212745/