玖叶教程网

前端编程开发入门

一文讲懂SQL窗口函数 大厂必考知识点

大家好,我是宁一。


今天是我们的第24课:窗口函数。


窗口函数,也叫OLAP(Online Anallytical Processing,联机分析处理),可以对数据库数据进行实时分析处理。


窗口函数是数据分析师常用的语法函数,面试时几乎是大厂的必考题。


这一节属于高阶内容了,比较难以理解,大家一定要自己在电脑上敲打,看看效果。


这样才能加深理解。之前的课程讲过如何安装数据库,大家可以到我主页查看哦~


基本语法


<窗口函数> OVER (
[PARTITION BY <用于分组的列名>] -- 可选
[ORDER BY <用于排序的列名>] -- 可选
)


上面 <窗口函数> 的位置,可以放下面两种函数:


(1)聚合函数:如sum.avg,count,max,min等;

(2)专用窗口函数:比如rank, dense_rank, row_number,下面会详细讲到。


我们一般将窗口函数放在select语句中。


1、聚合函数


窗口函数也是用来分组排序的,与聚合函数+GROUP BY效果类似。但是窗口函数产生的记录不会聚合到一起,每一行数据都生成一条记录。


实例:在Scores成绩表中,找到Sid为7-10的学生,并计算每个学生的总分显示出来。



SELECT
  Sid,
  SUM(score) AS "总分"
FROM Scores
WHERE Sid BETWEEN 7 AND 10
GROUP BY Sid;



但是问题来了,我们知道学生总分的同时,还想知道各科具体分数是多少,这个用上面语句是做不到的。


为啥呢?


因为SELECT后面的字段如果是表中现有的列,则GROUP BY子句中也必须有这个列。


所以如果要想知道各科具体分数,在SELECT后面就要添加Cid、score两列,在GROUP BY子句中也添加这两列的话,就会跟我们题目相悖。


这个知识点我们在第15讲和第16讲都强调过,大家可以点击主页再详细了解一下,我们现在就不展开说了。


我们要想同事知道学生总分、课程编号、分数,就需要使用窗口函数。


SELECT
  Sid,Cid,score,
  SUM(score) OVER (PARTITION BY Sid) AS "总分"
FROM Scores
WHERE Sid BETWEEN 7 AND 10;



补充知识:PARTITION BY的含义


PARTITION BY就是分区的意思,跟GROUP BY分组意思差不多。


如果不写PARTITION BY,就代表整个数据集属于一个分区。


比如我们上面SQL语句,如果省略掉PARTITION BY


SELECT
  Sid,Cid,score,
  SUM(score) OVER() AS "总分"
FROM Scores
WHERE Sid BETWEEN 7 AND 10;


结果是下面这样的,会计算所有学生的总分。



2、专用窗口函数


常用的专用窗口函数有:


(1)获取数据排名:


ROW_NUMBER():不考虑并列名次的情况,比如前三名分数都是88,88,77,排名是1,2,3。


RANK():如果有并列名次的行,会占用下一名次的位置。比如前三名分数都是88,88,77,排名是1,1,3。


DEBSE_RANK():如果并列名次的行,不占用下一名次的位置。比如前三名分数都是88,88,77,排名是1,1,2。


实例:在Scores成绩表中,找到Sid为7-10的学生,并计算成绩从高到低排名。


SELECT *,
  ROW_NUMBER() OVER(
    ORDER BY score DESC
  ) AS "排名"
FROM Scores
WHERE Sid BETWEEN 7 AND 10;



上方使用了ROW_NUMBER()窗口函数,可以看到增加了“排名”这一列。如果将ROW_NUMBER()替换为RANK(),返回结果是下面这样的:



(2)获取第一名或最后一名:


FIRST_VALUE(<列名>):获取第一名。


LAST_VALUE(<列名>):获取最后一名。


实例:在Scores成绩表中,找到Sid为7-10的学生,获取每个学生的最高成绩。


SELECT *,
  FIRST_VALUE(score) OVER(
    PARTITION BY Sid
    ORDER BY score DESC
  ) AS "最高成绩"
FROM Scores
WHERE Sid BETWEEN 7 AND 10;



(3)偏移函数:


LEAD(<列名>,<数值n>):从当前行访问向下偏移n行的数据。


LAG(<列名>,<数值n>):从当前行访问上偏移n行的数据。


NTH_VALUE(<列名>,<数值n>):从结果集中的第N行获取数据。


(4)分布函数:


CUME_DIST():分组内小于、等于当前rank值的行数 / 分组内总行数。


PERCENT_RANK():返回某列每行的百分比排序,每行按照公式(rank-1) / (rows-1)进行计算。


NTILE(<数值n>):将结果集整体分为n组,并展现出某一条数据被分配在哪个组中。


作业:在Scores中验证偏移函数和分布函数,看看能返回什么结果。


比如:获取下面第2行偏移分数。


SELECT *,
  LEAD(score,2) OVER(
    ORDER BY score DESC
  ) AS "获取下面第2行score值"
FROM Scores
WHERE Sid BETWEEN 7 AND 10;



我们SQL入门到进阶课程已经完结了。后面我还会继续出SQL高阶课程,包括视图、索引、并发、死锁、触发器、事件、事务、存储过程等。


录播课和直播课也会陆续跟上的~

发表评论:

控制面板
您好,欢迎到访网站!
  查看权限
网站分类
最新留言