oracle 分析函数之ratio_to_report

最新推荐文章于 2022-11-18 16:44:02 发布

iteye_15396

最新推荐文章于 2022-11-18 16:44:02 发布

阅读量184

点赞数

CC 4.0 BY-SA版权

分类专栏： oracle基础文章标签： Oracle

本文链接：https://blog.youkuaiyun.com/iteye_15396/article/details/82437009

oracle基础专栏收录该内容

27 篇文章

订阅专栏

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

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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;