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;