文章目录
一、窗口函数:
窗口函数也称为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()