Oracle分析函数- count()/sum() over(partition by 分组 order by 排序) 详解

优点:代码简单明了, 并且执行效率高,(不影响总的记录数)

如果不用这种函数去写, 按照平时我们的思路首先想到的可能是子查询,那么将至少会走4次以上的全表扫描:

(1)每个订单中产品数量大于3的产品至少1个(003,004)
(2)每个订单中折扣标志为'1'的产品至少有2个 (002,004)
(3)每个订单中产品数量总和至少5个(001,004)

以上三种条件每个会走一次全表扫描,还需要从orderinfo表中过滤掉这三种情况,所以至少四次.

 创建表

 -- 订单信息表 
 create table cux_orderinfo(
  order_no          VARCHAR2(20),--订单号
  product_no        VARCHAR2(10),--产品编号
  product_quantity  VARCHAR2(1), --产品数量
  is_discount       VARCHAR2(2)  --是否折扣 1是0否
);  

 

初始数据

复制代码
insert into cux_orderinfo values('001','101','1','0');
insert into cux_orderinfo values('001','102','2','0');
insert into cux_orderinfo values('001','103','2','0');
 
insert into cux_orderinfo values('002','201','2','1');
insert into cux_orderinfo values('002','202','1','0');
insert into cux_orderinfo values('002','203','1','1');
 
insert into cux_orderinfo values('003','301','7','1');
insert into cux_orderinfo values('003','302','1','0');
insert into cux_orderinfo values('003','303','3','0');
 
insert into cux_orderinfo values('004','401','3','1');
insert into cux_orderinfo values('004','402','6','0');
insert into cux_orderinfo values('004','403','9','1');
insert into cux_orderinfo values('004','404','7','1');
复制代码

 

3、需求:查询表中数据,满足一下三个条件
(1)每个订单中产品数量大于3的产品至少1个(003,004)
(2)每个订单中折扣标志为'1'的产品至少有2个 (002,004)
(3)每个订单中产品数量总和至少5个(001,004)

复制代码
select *
  from (select order_no,
               product_no,
               product_quantity,
               is_discount,
               count(case
                       when product_quantity > 3 then
                        1
                     end) over(partition by order_no) cnt_1, --每个订单中产品数量大于3的产品个数
               count(case
                       when is_discount = '1' then
                        1
                     end) over(partition by order_no) cnt_2, --每个订单中折扣标志为‘1’的产品个数
               sum(product_quantity) over(partition by order_no) sum_5 --每个订单中的产品总数
          from cux_orderinfo)
 where cnt_1 >= 1
   and cnt_2 >= 2
   and sum_5 >= 5;
View Code
复制代码

 

 

posted @   Iven_lin  阅读(1152)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 【.NET】调用本地 Deepseek 模型
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
点击右上角即可分享
微信分享提示