SQL干货|为你打开一扇窗—窗口函数

最新推荐文章于 2024-05-16 13:23:22 发布

weixin_46020540

最新推荐文章于 2024-05-16 13:23:22 发布

阅读量196

点赞数

文章标签：数据分析 sql mysql

本文链接：https://blog.csdn.net/weixin_46020540/article/details/119755438

版权

SQL干货|为你打开一扇窗—窗口函数

前言

很早之前就想写一篇关于窗口函数的文章，因精力有限所以一直搁置了（一脸认真的自我检讨），在这篇文章的准备阶段，我也拜读了一些相关文章，总体来说基本上所涉及的窗口函数相关知识均有涉及，但一万个读者有一万个哈姆雷特，每个人的文章角度不同，本文主要是面向基础，希望能够用通俗易懂的话语同时结合实际高频面试题为大家打开一扇“窗”。

一、窗口函数简介

窗口函数也称为OLAP函数。OLAP是online analytical processing的简称，意思是对数据库数据进行实时分析处理。——百度百科

窗口函数的基本语法如下：

<窗口函数> over (partition by <用于分组的列名> order by <用于排序的列名>)

窗口函数是针对定义的行集合执行聚集分析，不减少原表的行数同时具备分组与排序的功能；通俗一点讲，窗口函数会按照指定的列名分组，在组内进行排序，同时保持原表行数不发生变化；这与group by 分组聚合后每组返回一个值（原表行数改变）有明显的区别。

窗口函数关键字：
over() : over子句中的内容为窗口函数的作用域（范围）
partition by ：分组
order by : 排序

二、常用窗口函数介绍

1.排序函数

rank() 如果有并列名次的行，会占用下一名次的位置
dense_rank() 如果有并列名次的行，不会占用下一名次的位置
row_number() 不考虑并列名次的情况

1select *, rank() over (order by 金额 asc) as ranking, 
2dense_rank() over (order by 金额 asc) as dens_rank, 
3row_number() over (order by 金额 asc) as row_num from table

常用场景：排序函数常用于组内排序问题，如：topN问题 (找出每个部门排名前N的员工)

2.偏移函数

 1--基本语法
 2
 3lag(exp_str,offset,defval) over()
 4
 5Lead(exp_str,offset,defval) over()
 6
 7--exp_str要取的列
 8
 9--offset取偏移后的第几行数据
10
11--defval:没有符合条件的默认值

lag() lag是用于统计窗口内往上(向前偏移)第n行值
lead() lead是用于统计窗口内往下（向后偏移）第n行值

1select *,
2    lag(要偏移的列) over(partition by department order by 要排序的列) as next_lag
3from table
4---------------------------------------------------------------------------------------
5select *,
6    lead(要偏移的列) over(partition by department order by 要排序的列) as next_lead
7from table

常用场景：多用于解决用户连续登录问题，如连续7天都登陆的用户数

拓展：
偏移函数还有first_value()、last_value()，感兴趣的同学可以自行学习，本文不作介绍。

3.聚合函数

sum()
avg()
count()
max()
min()

1select * ,sum(需要计算的列名) over (order by 需要计算的列名) as sum,
2avg(需要计算的列名) over (order by 需要计算的列名) as avg,
3count(需要计算的列名) over (order by 需要计算的列名) as cot,
4max(需要计算的列名) over (order by 需要计算的列名) as max,
5min(需要计算的列名) over (order by 需要计算的列名) as min
6from table

聚合函数在窗口函数中是针对自身记录、以及自身记录之上的所有数据进行计算。
聚合函数作为窗口函数，可以直观的看到，截止到本行数据，统计数据是多少（最大值、最小值等）；同时可以看出每一行数据，对整体统计数据的影响。

三、代码实操（经典问题）

~~本文代码只展示基本框架，如遇同类问题，直接代入使用即可~~

1.排序

topN问题 (找出每个部门排名前N的员工)

1select *
2from (
3   select *, 
4          row_number() over (partition by 要分组的列名
5                       order by 要排序的列名 desc) as ranking
6   from 表名) as a
7where ranking <= N

2.偏移

user_name 用户名（连续7天都登陆的用户数）
思路：
1.首先利用偏移窗口函数lead()求得每个用户在每个登陆时间向后偏移7行的登陆时间;（需要注意：date 按倒序排序）
2.再计算每个用户在每个登陆时间滞后7天的登陆时间;
3.如果每个用户向后偏移7行的登陆时间等于滞后7天的时间，说明该用户连续登陆了7天

1select b.user_name
2from
3(select user_name,
4date,lead(date,7,-1) 
5over(partition by user_name order by date desc) as date_7
6from user_login_table) b 
7where 
8date_sub(cast(b.date as date,7)) = cast(b.date_7 as date)