牛客刷题——窗口函数

一、知识点总结

（一）参数含义

<窗口函数> window_name OVER ([PARTITION BY <列清单>]
ORDER BY <排序用列清单>)

over：用来指定函数执行的窗口范围,若后面括号中什么都不写,则意味着窗口包含满足WHERE条件的所有行，窗口函数基于所有行进行计算；如果不为空,则支持以下4中语法来设置窗口。

window_name:给窗口指定一个别名。如果SQL中涉及的窗口较多,采用别名可以看起来更清晰易读
partition by子句：窗口按照哪些字段进行分组,窗口函数在不同的分组上分别执行
order by子句：按照哪些字段进行排序,窗口函数将按照排序后的记录顺序进行编号
frame子句：frame是当前分区的一个子集,子句用来定义子集的规则,通常用来作为滑动窗口使用

（二）功能

• 窗口函数具有groupby的分组、orderby的排序功能，但是不具备GROUP BY
  子句的汇总功能。因此和聚合函数不一样，有几条记录执行完还是几条。
  窗口函数其实就是运用分组和排序构造出一个新变量，和casewhen的本质是一样的，case是在条件符合情况下构造特征。

（三）执行顺序（where）

窗口函数的执行顺序（逻辑上的）是在FROM，JOIN，WHERE，GROUP　BY，HAVING之后，在ORDER　BY，LIMIT，SELECT　DISTINCT之前。它执行时GROUP　BY的聚合过程已经完成了，所以不会再产生数据聚合。

sql的执行顺序
from–join–on–where–groupby–avg,sum–having–select–distinct–order by–limit
先做表连接，然后筛选，再分组汇总，选择变量即列数，先排序然后筛选行数（先行后列）
groupby开始可以用到select中的别名

注:窗口函数是在where之后执行的，所以如果where子句需要用窗口函数作为条件，需要多一层查询，在子查询外面进行
【例】求30天内后一天比前一天平均时间差

select user_id,avg(diff)
from
(
	select user_id,lead(log_time)over(partition by user_id order by log_time) - log_time as diff
	from user_log
)t
where datediff(now(),t.log_time)<=30
group by user_id

【例】SQL74 考试分数(三)

• 思路：先得到有排名的子表，然后连接后筛选
• 每种语言的成绩排名：dense_rank()over(PARTITION BY language_id ORDER BY score DESC)

SELECT a.id, name, score
FROM language AS l
right JOIN
(SELECT id, language_id, score, dense_rank()over(PARTITION BY language_id ORDER BY score DESC) AS rank_num 
FROM grade)a
ON l.id = a.language_id
WHERE rank_num <=2
ORDER BY name, score DESC, a.id;

【例】SQL76 考试分数(五)
查询各个岗位分数的中位数位置上的所有grade信息，并且按id升序排序

• 思路：01-创建子表：包含了根据job分组的排名和计数变量；02-筛选出符合中位数的情况，也就是当前排名和(counts+1)/2差值小于1

select id,job,score,t_rank FROM
(select id,job,score,
row_number() over (partition by job order by score desc) as t_rank,
count(id) over (partition by job) as counts
from grade)t1
where abs(t_rank-(counts+1)/2)<1
order by id 

（四）窗口函数的选择

1、聚合函数

sum,max,min,avg

2、排序函数

ROW_NUMBER():顺序排序——1、2、3
RANK():并列排序，跳过重复序号——1、1、3（横向加）
DENSE_RANK():并列排序，不跳过重复序号——1、1、2（纵向加）
first_value/last_value：分组内排序后，截止到当前行第一个/最后一个值

3、分布函数

percent_rank()=(rank-1) / (rows-1)
cume_dist()：分组内小于、等于当前值的行数 /分组内总行数，如果倒序就是大于等于。
【例】

-- 获取income字段的top10%的阈值
select percentile(CAST (salary AS int),0.9)) as income_top10p_threshold from table_1;

-- 获取income字段的10个百分位点
select percentile(CAST (salary AS int),array(0.0,0.1,0.2,0.3,0.4,0.5,0.6,0.7,0.8,0.9,1.0)) as income_percentiles
from table_1;

4、前后函数:lag(expr,n),lead(expr,n)

lead和lag函数，这两个函数一般用于计算差值，最适用的场景是计算花费时间。举个例子，有数据是每个用户浏览网页的时间记录，将记录的时间错位之后，进行两列相减就可以得到每个用户浏览每个网页实际花费的时间。

-- 向前偏移n期，如果取不到值就取default
lag(var,n,default) OVER (partition by order by)as name  
-- 向后偏移n期，如果取不到值就取default
lead(var,n,default) OVER (partition by order by)as name  

【例】用户行为分析
用户行为表tracking_log

（1）统计每天符合以下条件的用户数：A操作之后是B操作，AB操作必须相邻

# 按天分组统计求和
# A操作之后是B，且AB操作必须相邻，那就涉及一个前后问题，所以想到用窗口函数中的lag()或lead()
select date,count(*)
from(
		select user_id
		from(
				select user_id,convert(log_time,date) date,opr_id f,lag(opr_id,1) over(partition by user_id,convert(log_time,date) order by log_time) l
				from tracking_log
				) a
		where f='A' and l='B'
		) b
group by date;
-- a构造所有用户的前后期数据，b筛选，c计数

（2）统计用户行为序列为A-B-D的用户数,其中:A-B之间可以有任何其他浏览记录(如C,E等),B-D之间除了C记录可以有任何其他浏览记录(如A,E等)

• 代码注意点：group_concat用于连接字段

select count(*)
from(
		select user_id,group_concat(opr_id) ubp
		from tracking_log
		group by user_id
		) a
where ubp like '%A%B%D%' and ubp not like '%A%B%C%D%'
# 先提取子表后where筛选

【例】用户Peter在浏览网页，在某个时刻，Peter点进了某个页面，过一段时间后，Peter又进入了另外一个页面，如此反复，那怎么去统计Peter在某个特定网页的停留时间呢，又或是怎么统计某个网页用户停留的总时间呢？

select nvl(url,'-1') url,
       nvl(userid,'-1') userid,
       sum(period) totol_peroid from (
select userid,
       time stime,
       lead(time) over(partition by userid order by time) etime,
       UNIX_TIMESTAMP(lead(time) over(partition by userid order by time),'yyyy-MM-dd HH:mm:ss')- UNIX_TIMESTAMP(time,'yyyy-MM-dd HH:mm:ss') period,
       url 
  from test.user_log
) a 
group by url, userid with rollup;
-- 根据url和userid来分组，并把同一个url的结果with rollup加总
-- nvl(url,'-1')相当于ifnull(url,'-1')

5、头尾函数：FIRST_VALUE(expr),LAST_VALUE(expr)

截止到当前成绩，按照日期排序查询第1个和最后1个同学的分数

二、实际应用

（一）累积求和、累积百分比

1、累积求和

SELECT product_id, product_name, sale_price,
　     SUM (sale_price) OVER (ORDER BY product_id) AS current_sum
FROM Product;

【例】SQL71 牛客每个人最近的登录日期(六)

• 每个人截至某个日期的次数总和：sum(p.number)over(partition by u.name order by date)

-- 截止到某天，累计总共》》sum(number) over (partition by user_id order by date)
-- 最后按照日期升序排序，再按照姓名升序排序》》order by
-- 名字，日期，数量>>join
select user.name as u_n,p.date,
(sum(p.number) over (partition by user.name order by p.date))  as ps_num
from user right join passing_number p
on user.id=p.user_id
order by p.date,user.name

-- 函数连接的on也可以用where代替
select u.name as u_n
        ,p.date as date
        ,sum(p.number)over(partition by u.name order by date)as ps_num
from passing_number p,user u
where p.user_id=u.id
order by p.date,u.name;

2、累积百分比

cume_dist / sum() over

【例】前XX%的用户贡献了XX%的总额

select *,
cume_dist() OVER (PARTITION BY department ORDER BY cost desc) as cum_dist,
sum(cost) OVER (PARTITION BY department ORDER BY cost desc)/sum(cost) OVER (PARTITION BY department) as s
from table
where department = 'A'

（二）计算移动平均

指定“最靠近的3行”作为汇总对象：在orderby中添加rows 2 preceding进行限定，表示当前记录+ 之前1行的记录+ 之前2行的记录。若是在此之后的~行，则使用FOLLOWING。

select product_id,product_name,sale_price,
avg(sale_price) over (order by product_id rows 2 preceding) as moving_avg
from product

（三）where in等价于rank窗口排序

【例】SQL89 获得积分最多的人(一)

1、对于排名没有并列的情况：
用聚合函数或者窗口函数聚合求和之后，通过排序limit1来取最大。
2、对于排名有并列的情况：
很可能存在分数最大的并列有好几个id，这时就用不了limit了，可以通过子表筛选来做。
（1）where筛选出=子表max的行
（2）筛选出=窗口函数排序为1的行

【没有分数并列的情况】

## 法一：窗口
select name,
sum(grade_num)over(partition by user_id) as grade_sum
from grade_info join user on user.id=grade_info.user_id
order by grade_sum desc
limit 1
## 法二：聚合
select name,
sum(grade_num) as grade_sum
from grade_info join user on user.id=grade_info.user_id
group by name
order by grade_sum desc
limit 1

【有分数并列的情况】

• 思路1：先聚合函数求每个人的总分，再用where筛选出等于最高分的情况；思路2：先聚合函数求每个人的总分，再用窗口函数筛选出排序值为1。
• 易错点：再聚合之后直接select max(grade_sum) from a是错误的

-- 求每个用户的分数和
with a as (
  select user.*,sum(grade_num) as grade_sum
  from user join grade_info
  on id=user_id
  group by user_id)  
-- 用where筛选
select id,name,grade_sum from a
where grade_sum=(select max(grade_sum) from a)  
order by id  -- 可能存在多个id分数都是最大
-- 用窗口函数筛选
select id,name,grade_sum from 
(select *,dense_rank() over (order by grade_sum desc) as ranks from a)b
where ranks=1

【例】查询每位学生获得的最高成绩和它所对应的科目，若科目成绩并列，取 course_id 最小的一门
【法一】窗口函数

• 思路：用窗口函数构造出排名这个变量，然后where筛选出排名第一的信息。

SELECT student_id,course_id,grade
FROM (SELECT student_id,course_id,grade
	  RANK_NUMBR() OVER (PARTITION BY student_id order by grade DESC) as Rank
	  FROM Enrollments
	  ORDER BY Rank, course_id) as A
where A.Rank = 1
order by student_id 

【法二】where in

select student_id,min(course_id)
from Enrollments
where (student_id,grade) in (
						select student_id,max(grade)
						from Enrollments
						group by student_id)
group by student_id
order by student_id;

（四）窗口和case when的结合

【例】查询每一科目成绩最高和最低分数的学生
【法一】按科目查找成绩最高的同学和最低分的同学，然后利用union连接起来

select c_id,s_id
from(
		select *,row_number() over(partition by c_id order by s_score desc) r
		from score
		) a
where r=1
union
select c_id,s_id
from(
		select *,row_number() over(partition by c_id order by s_score) r
		from score
		) a
where r=1;

【法二】case-when

select c_id,
       max(case when r1=1 then s_id else null end) '最高分学生',
	   max(case when r2=1 then s_id else null end) '最低分学生'
from(
		select *,row_number() over(partition by c_id order by s_score desc) r1,
		       row_number() over(partition by c_id order by s_score) r2
		from score
		) a
group by c_id；

参考