oracle 分析函数之ratio_to_report

最新推荐文章于 2021-08-16 17:19:35 发布

原创最新推荐文章于 2021-08-16 17:19:35 发布 · 197 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#Oracle

oracle基础专栏收录该内容

27 篇文章

订阅专栏

本文介绍如何使用ratio_to_report函数计算每个客户ID的订单在总订单中的百分比，通过实例展示建表、插入数据及应用此函数的方法。

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;