以下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.