count()/sum() over(partition by) 详解

1、创建表

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

2、初始化数据

insert into orderinfo values('001','101','1','0');
insert into orderinfo values('001','102','2','0');
insert into orderinfo values('001','103','2','0');

insert into orderinfo values('002','201','2','1');
insert into orderinfo values('002','202','1','0');
insert into orderinfo values('002','203','1','1');

insert into orderinfo values('003','301','7','1');
insert into orderinfo values('003','302','1','0');
insert into orderinfo values('003','303','3','0');

insert into orderinfo values('004','401','3','1');
insert into orderinfo values('004','402','6','0');
insert into orderinfo values('004','403','9','1');
insert into orderinfo values('004','404','7','1');

3、需求：查询表中数据，满足一下三个条件

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

4、查询代码

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 orderinfo) 
where cnt_1 >= 1 
and cnt_2 >= 2
and sum_5 >=5;

5、查询结果

6、优点

代码简单明了， 并且执行效率高。

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

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

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

网友们如果有更好的方法可以给我留言,我们一起学习,共同进步哈哈!!!