SQL 窗口函数应用

这里主要是记录窗口函数的相关应用，把有用的知识形成笔记，以方便自个儿复习😊

简介

窗口函数是 SQL 中一类特别的函数。与聚合函数相似，窗口函数的输入也是多行记录。不 同的是，聚合函数的作用于由 GROUP BY 子句聚合的组，而窗口函数则作用于一个窗口， 这里，窗口是由一个 OVER 子句 定义的多行记录。聚合函数对其所作用的每一组记录输 出一条结果，而窗口函数对其所作用的窗口中的每一行记录输出一条结果。一些聚合函 数，如 sum, max, min, avg,count 等也可以当作窗口函数使用。

一、知识点汇总

（一）知识点1

1、窗口函数

有三种排序方式

1. rank() over() 1 2 2 4 4 6 (计数排名，跳过相同的几个，eg.没有3没有5)
2. row_number() over() 1 2 3 4 5 6 (赋予唯一排名)
3. dense_rank() over() 1 2 2 3 3 4 (不跳过排名，可以理解为对类别进行计数)

2、聚合函数

通常查找最大值最小值的时候，首先会想到使用聚合函数。

• group by的常见搭配：常和以下聚合函数搭配

  avg（）-- 求平均值
  count（）-- 计数
  sum（）-- 求和
  max（） – 最大值
  min（）-- 最小值

• group by 的进阶用法，和with rollup一起使用。

3、左右连接

• 左连接：表1 left join 表2 on 表1.字段=表2.字段 （以表1为准，表2进行匹配）
• 右连接：表1 right join 表2 on 表1.字段=表2.字段 （以表2为准，表1进行匹配）
• 全连接：表1 union all 表2 （表1 和表2的列数必须一样多，union 去除重复项，union all 不剔除重复项）
• 内连接：表1 inner join 表2（取表1和表2相交部分）
• 外连接：表1 full outer join 表2 （取表1和表2不相交的部分）

注意：MYSQL 不支持外连接，可以用左右连接后再全连接代替

（二）知识点2

1、lead的窗口函数用法

1. lead(字段名，n) over () :取值向后偏移n行（空间的理解就是直接将一列数据往前推n个位置，后面的位置就空出来了，具体配合图片理解）;
2. lag(字段名，n) over () :取值向前偏移n行（空间的理解就是直接将一列数据往前后n个位置，前面的位置就空出来了，具体配合图片理解）;
3. lag(字段名，n，x) over () :取值向前偏移n行，并将空值填充为数字x（空间的理解就是直接将一列数据往前后n个位置，前面的空出来的位置用X填充上，具体配合图片理解）。

（1）示例1

SELECT id,score,Lead(score,2) over(order by id) lead_score,-- score数列向前推动2位，后面就腾空了2个位置
      Lag(score,2) over(order by id) lag_score, -- score数列向后推2位，腾空2个位置
      lag(score,2,666) over(order by id) lag_score_3 -- score数列向后推动2位，空值被填充为666
FROM exam_record;

2、datediff 函数

1. datediff(时间1，时间2)：计算两个日期之间间隔的天数，单位为日
2. timestampdiff(时间单位，开始时间，结束时间):两个日期的时间差，返回的时间差形式由时间单位决定(日，周，月，年)
3. date_add(日期，INTERVAL n 时间单位) ：返回加上n个时间单位后的日期
4. date_sub(日期，INTERVAL n 时间单位 )：返回减去n个时间单位后的日期
5. date_format(时间，‘%Y-%m-%d’)：强制转换时间为所需要的格式

3、datediff和timestampdiff函数的区别

1. datediff()函数的作用是求日期差，也就是把一个时间的日期部分取出来求差。例如：'2021-09-05 12:00:00’和’2021-09-04 11:00:00’这两个日期，datediff只取2021-09-05和2021-09-04求日期差，并不会管后面的时间部分。
2. timestampdiff()函数的作用则是求时间戳的差，例如：'2021-09-05 12:00:00’和’2021-09-04 11:00:00’这两个日期，datediff只会先求出这个日期的时分秒差，之后再转换成天数来求日期差。

直接说可能有点懵，看完差别后，具体来看下面这个例子：

（1）示例1

先看第一组时间差是23小时，日期差（9月5日-9月4日）是1天

#先看第一组时间差是23小时，日期差（9月5日-9月4日）是1天
select DATEDIFF('2021-09-05 12:00:00','2021-09-04 11:00:00') datediff_1,
TIMESTAMPDIFF(hour,'2021-09-04 12:00:00','2021-09-05 11:00:00') hourdiff_1,
TIMESTAMPDIFF(day,'2021-09-04 12:00:00','2021-09-05 11:00:00')timediff_1; 

（2）示例2

再看第二组时间差是28小时，日期差（9月5日-9月4日）是1天

#再看第二组时间差是28小时，日期差（9月5日-9月4日）是1天
select DATEDIFF('2021-09-05 16:00:00','2021-09-04 11:00:00') datediff_2,
TIMESTAMPDIFF(hour,'2021-09-04 12:00:00','2021-09-05 16:00:00') hourdiff_2,
TIMESTAMPDIFF(day,'2021-09-04 12:00:00','2021-09-05 16:00:00')timediff_2; 

看完这个是不是就很容易理解啦~😊😊😊

二、专用窗口函数

（1）示例1

select tag,uid,ranking
from (
select b.tag,a.uid,max(a.score),min(a.score),
row_number() over(partition by b.tag order by max(a.score) desc,min(a.score) desc,a.uid desc) ranking
from exam_record a
left join examination_info b on a.exam_id = b.exam_id
group by b.tag,a.uid
) t
where ranking < 4

（2）示例2

select exam_id,duration,release_time
from (
select exam_id,duration,release_time,
sum(case when rk_desc=2 then time_diff 
		 when rk_asc=2  then - time_diff else 0 end) sum_time
from (
select
	a.exam_id,b.duration ,timestampdiff(minute,a.start_time,a.submit_time) time_diff,b.release_time,
	row_number() over(partition by a.exam_id order by timestampdiff(minute,a.start_time,a.submit_time) desc ) rk_desc,
	row_number() over(partition by a.exam_id order by timestampdiff(minute,a.start_time,a.submit_time) asc) rk_asc
from exam_record a left join examination_info b on a.exam_id=b.exam_id
where a.submit_time is not null 
) t1 group by exam_id
) t2 
where sum_time*2 >= duration
order by exam_id desc 

（3）示例3

WITH t2 AS (
SELECT 
	uid,
	COUNT(start_time) total, -- 用户2021年作答的次数
	DATEDIFF(MAX(start_time),MIN(start_time))+1  diff_time, -- 头尾作答时间窗 
	MAX(DATEDIFF(next_time,start_time))+1 days_window -- 最大间隔天数
FROM (
	SELECT uid,start_time,
	LEAD(start_time,1)OVER(PARTITION BY uid ORDER BY start_time) AS next_time -- 第二次作答时间
	FROM exam_record
    WHERE YEAR(start_time)=2021 -- 2021年的数据
	) t1
GROUP BY uid
)
SELECT uid,days_window,ROUND(total* days_window/diff_time,2) avg_exam_cnt
FROM t2
WHERE diff_time>1
ORDER BY days_window DESC,avg_exam_cnt DESC

三、聚合窗口函数

关注林哥，持续更新哦！！！★,°:.☆(￣▽￣)/$:.°★ 。