SQL多种窗口函数行为分析用法

一、窗口函数：

窗口函数也称为OLAP函数，OLAP 是OnLine Analytical Processing 的简称，意思是对数据库数据
进行实时分析处理。例如，市场分析、创建财务报表、创建计划等日常性商务工作。窗口函数就是为了实现OLAP 而添加的标准SQL 功能。

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

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

over关键字用来指定函数执行的窗口范围,若后面括号中什么都不写,则意味着窗口包含满足WHERE条件的所有行，窗口函数基于所有行进行计算；如果不为空,则支持以下4中语法来设置窗口。
①window_name:给窗口指定一个别名。如果SQL中涉及的窗口较多,采用别名可以看起来更清晰易读

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

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

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

2.窗口函数与普通聚合函数的区别：

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

窗口函数兼具之前我们学过的GROUP BY 子句的分组功能以及ORDER BY 子句的排序功能。但是，PARTITION BY子句并不具备GROUP BY 子句的汇总功能。因此，使用RANK 函数并不会减少原表中 记录的行数。

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

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

注:窗口函数是在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

计算移动平均

窗口函数

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

• rank函数：相同值排名相同，下一排名根据之前的记录个数而定
• dense_rank函数：相同值排名相同，排名连续不间断
• row_number函数：不管值是否相同，依次连续排名
  

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

• percent_rank():

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

SELECT
RANK() OVER w AS 排名,
PERCENT_RANK()