oracle 分析函数之ratio_to_report

ratio_to_report函数是某个值占总值的百分比。

 

建表：

create table SMALL_CUSTOMERS(CUSTOMER_ID NUMBER,SUM_ORDERS  NUMBER);     
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) rowNumber
  from small_customers t;