一.SQL中的进阶函数-窗口函数

1.窗口函数

窗口函数和普通聚合函数的区别：

①聚合函数是将多条记录聚合为一条；窗口函数是每条记录都会执行，有几条记录执行完还是几条。

②聚合函数也可以用于窗口函数。

注:窗口函数是在where之后执行的，所以如果where子句需要用窗口函数作为条件，需要多一层查询，在子查询外面进行,例如:

select user_id,avg(diff)
from
(
	select user_id,lead(log_time)over(partition 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

2.窗口函数的基本用法：

①window_name:给窗口指定一个别名。如果SQL中涉及的窗口较多,采用别名可以看起来更清晰易读

②partition by子句：窗口按照哪些字段进行分组,窗口函数在不同的分组上分别执行

③order by子句：按照哪些字段进行排序,窗口函数将按照排序后的记录顺序进行编号

④frame子句：frame是当前分区的一个子集,子句用来定义子集的规则,通常用来作为滑动窗口使用

3.（面试考点）序号函数:row_number(),rank(),dense_rank()的区别

ROW_NUMBER():顺序排序——1、2、3

RANK():并列排序，跳过重复序号——1、1、3

DENSE_RANK():并列排序，不跳过重复序号——1、1、2

4.分布函数:percent_rank(),cume_dist()

percent_rank():

每行按照公式(rank-1) / (rows-1)进行计算。其中，rank为RANK()函数产生的序号，rows为当前窗口的记录总行数

--给窗口指定别名：WINDOW w AS (PARTITION BY stu_id ORDER BY score) rows = 5
mysql> SELECT
    -> RANK() OVER w AS rk,
    -> PERCENT_RANK() OVER w AS prk,
    -> stu_id, lesson_id, score
    -> FROM t_score
    -> WHERE stu_id = 1
    -> WINDOW w AS (PARTITION BY stu_id ORDER BY score)
    -> ;