sql语句中开窗函数的使用

开窗函数主要分为2类：

1.排序开窗函数：

rank() over(partition by xxx order by yyy)　　//各分区按照yyy字段排序，如果yyy字段值一样，则rank值一样，且下一个序号为rank值加1，如1、2、2、3、4(假如分区一共有5条记录)

dense_rank() over(partition by xxx order by yyy)　　//各分区按照yyy字段排序，如果yyy字段值一样，则rank值一样，且下一个序号为该记录在所以记录中的位置，即第几条记录，如1、2、2、4、5

row_number() over(partition by xxx order by yyy)　　//各分区按照yyy字段排序，即使yyy字段值一样，rank的值也不一样，从1一直往上加，如1、2、3、4、5

以上，partition by xxx 表示按照xxx字段分区(分区就是分组的意思)，如果没有partition by子句的话，所有的记录当做一个分区。

2.聚合开窗函数：

sum(xxx) over(partition by yyy order by zzz)

count(1) over(partition by xxx order by yyy)

max(xxx) over(partition by yyy order by zzz)

min(xxx) over(partition by yyy order by zzz)

avg(xxx) over(partition by yyy order by zzz)

这5个聚合开窗函数用的时候再研究，粗略的看了一下各运行结果，跟平时的聚合函数有非常大的不同。

 

使用案例：

之前用group by分组时select的字段除了group by字段外，其他字段都要用聚合函数包起来(如果有的话)，如count(1)，sum(amount)，max(created_date)，这样对其他非分组字段的处理只能满足一些常规的需求，如分组查询created_date最大的那条记录

平安寿险红包项目

需求一：查询投保表中每个子红包最近一次的投保失败记录

with r as (
    select child_redpacket_id, max(updated_date) from red_packet_customerinfo 
    where insure_status = -1
    group by child_redpacket_id
)
select rpc.child_redpacket_id, rpc.error_msg, rpc.updated_date from red_packet_customerinfo rpc
where (rpc.child_redpacket_id, rpc.updated_date) in (select * from r)
order by rpc.updated_date desc, rpc.child_redpacket_id

需求二：查询投保表中每个子红包最近两次的投保失败记录

用group by 绞尽脑汁也想不出来怎么写，但是用row_number() over(...) 相当简单

row_number() over(partition by xxx order by yyy)，按照xxx字段分组并且按照yyy字段排列，默认是升序排列，后面跟上desc则是降序排列

这样每一条记录都会增加一个row_number虚拟字段(可以重命名为任意字段)，每一组row_number都是从1开始往上累计，且排序是按照yyy字段严格排序的。

这样满足需求二的sql就可以这样写：

with r as (
    select rpc.*, row_number() over(partition by child_redpacket_id order by updated_date desc)
    from red_packet_customerinfo rpc
    where insure_status = -1
)
select r.child_redpacket_id, r.error_msg, r.updated_date from r 
where r.row_number <= 2 
order by r.updated_date desc, child_redpacket_id

 

转载于:https://www.cnblogs.com/koushr/p/5873407.html