oracle 分析函数之ratio_to_report
ratio_to_report函数是某个值占总值的百分比。建表：
create table SMALL_CUSTOMERS(CUSTOMER_ID NUMBER,SUM_ORDERSNUMBER); insert into SMALL_CUSTOMERS (CUSTOMER_ID, SUM_ORDERS) values (1000, 10); insert into SMALL_CUSTOMERS (CUSTOMER_ID, SUM_ORDERS) values (1000, 20); insert into SMALL_CUSTOMERS (CUSTOMER_ID, SUM_ORDERS) values (1000, 30); insert into SMALL_CUSTOMERS (CUSTOMER_ID, SUM_ORDERS) values (800, 5); insert into SMALL_CUSTOMERS (CUSTOMER_ID, SUM_ORDERS) values (800, 10); insert into SMALL_CUSTOMERS (CUSTOMER_ID, SUM_ORDERS) values (800, 1);
具体用法：
select customer_id, sum_orders, sum(sum_orders) over(partition by customer_id) totalOrder, round(ratio_to_report(sum_orders) over(partition by customer_id), 5) rowNumberfrom small_customers t;
http://dl.iteye.com/upload/attachment/0075/3419/8c1b00ea-a60a-371a-a336-2d05ea774ff6.jpg
页:
[1]