开窗函数除了用于排序,还可以用在聚合函数上,比如类似场景:
求每天订单量占当月订单量的比例。
不用开窗函数也能实现,但是SQL会比较长
with o as
(
select
order_id
,user_id
,create_time
,pay_time
,is_delete
,date(create_time) as create_date
,date_format(create_time,'%Y-%m') as create_mon
from order_info
)
select
a.create_date
,a.date_cnt
,b.create_mon
,b.mon_cnt
,a.date_cnt/b.mon_cnt as d_per
from
(
select
create_date
,count(distinct order_id) as date_cnt
from o
group by create_date
)a
join (
select
create_mon
,count(distinct order_id) as mon_cnt
from o
group by create_mon
)b on date_format(a.create_date,'%Y-%m')=b.create_mon
order by a.create_date
如果用开窗函数,可以比较简洁的写出来:
with o as
(
select
distinct
order_id
,user_id
,create_time
,pay_time
,is_delete
,date(create_time) as create_date
,date_format(create_time,'%Y-%m') as create_mon
from order_info
)
select
distinct
create_date
,count(order_id) over(partition by create_date) as date_cnt
,count(order_id) over(partition by create_mon) as mon_cnt
,count(order_id) over(partition by create_date)/count(order_id) over(partition by create_mon) as d_per
from
o
这是count()的用法,类似的max(),min(),sum()也都是可以用的
但是这种方式有写需要注意的点:
1、开窗函数的count()使用是,不能加distinct,不然会报错,所以如果有需求中有去重要求,还是用group by的方式比较稳妥一点,max(),min(),sum()倒是影响不大
2、用开窗函数的方式时,相比group by的方式虽然代码比较简洁,但是SQL的执行时间会相比略长一点