一、背景介紹
今天，野雞大學(xué)高（三）班的月考成績(jī)出來(lái)了，這里先給大家公布一下各位同學(xué)的考試成績(jī)。
接著，在給大家公布一下各位同學(xué)的生活消費(fèi)情況。
下面我們利用上述考試成績(jī)和生活消費(fèi)記錄，利用mysql做一個(gè)簡(jiǎn)單的分析。
當(dāng)然，從本文標(biāo)題就可以看出來(lái)。本文就是要結(jié)合這份數(shù)據(jù)，為大家講述SQL “窗口函數(shù)” 應(yīng)該怎么用？
包括你以后學(xué)習(xí)hive或者oracle數(shù)據(jù)庫(kù)，或者說(shuō)數(shù)據(jù)分析面試，這都將是一個(gè)很重要的知識(shí)點(diǎn)。
二、建表語(yǔ)句和插入數(shù)據(jù)
創(chuàng)建表格
create table exam_score(
sname varchar(20),
age int,
subject varchar(20),
score varchar(20)
)charset=utf8;
# ----------------------- #
create table cost_fee(
sname varchar(20),
buydate varchar(20),
buycost int
)charset=utf8;
插入數(shù)據(jù)
insert into exam_score values
("張三" , 18, "語(yǔ)文" , 90),
("張三" , 18, "數(shù)學(xué)" , 80),
("張三" , 18, "英語(yǔ)" , 70),
("李四" , 21, "語(yǔ)文" , 88),
("李四" , 21, "數(shù)學(xué)" , 78),
("李四" , 21, "英語(yǔ)" , 71),
("王五" , 18, "語(yǔ)文" , 95),
("王五" , 18, "數(shù)學(xué)" , 83),
("王五" , 18, "英語(yǔ)" , 71),
("趙六" , 19, "語(yǔ)文" , 98),
("趙六" , 19, "數(shù)學(xué)" , 90),
("趙六" , 19, "英語(yǔ)" , 80);
# ----------------------- #
insert into cost_fee values
("張三","2019-01-01",10),
("張三","2019-03-03",23),
("張三","2019-02-05",46),
("李四","2019-02-02",15),
("李四","2019-01-07",50),
("李四","2019-03-04",29),
("王五","2019-03-08",62),
("王五","2019-02-09",68),
("王五","2019-01-11",75),
("趙六","2019-02-08",55),
("趙六","2019-03-10",12),
("趙六","2019-01-12",80);
三、窗口函數(shù)分類介紹
在正式講述 “窗口函數(shù)” 應(yīng)用之前，我這里先帶著大家梳理一遍 “窗口函數(shù)” 的基礎(chǔ)。我們可以將窗口函數(shù)分為如下幾類：
聚合函數(shù) + over()搭配；
排序函數(shù) + over()搭配；
ntile()函數(shù) + over()搭配；
偏移函數(shù) + over()搭配；
具體每一類，有哪些函數(shù)呢？觀察下面的思維導(dǎo)圖。
對(duì)于over()里面，這里還有兩個(gè)常用的關(guān)鍵字，必須要講述。如下：
partition by + 字段：你可以想象成group by關(guān)鍵字，就是用于 “分組” 的關(guān)鍵字；
order by + 字段：這個(gè)更容易理解，就是用于 “排序” 的關(guān)鍵字；
四、窗口函數(shù)應(yīng)用
上面給大家介紹了若干常用的 “窗口函數(shù)”，這里利用文首創(chuàng)建的數(shù)據(jù)，講講 “窗口函數(shù)” 的應(yīng)用。
希望大家通過(guò)每個(gè)案例，來(lái)總結(jié)一下每個(gè)函數(shù)的含義，這里就不詳細(xì)寫(xiě)了。
1. 聚合函數(shù) + over()搭配
① 計(jì)算每位同學(xué)的得分與平均值的情況
select
sname
,subject
,score
,avg(score) over(partition by sname) as avg_score
from
exam_score
結(jié)果如下：
② 計(jì)算每位同學(xué)1-3月消費(fèi)情況和消費(fèi)總額
select
sname
,buydate
,buycost
,sum(buycost) over(partition by sname) as sum_cost
from
cost_fee
結(jié)果如下：
③ 計(jì)算每位同學(xué)1-3月消費(fèi)情況和累計(jì)消費(fèi)總額
select
sname
,buydate
,buycost
,sum(buycost) over(partition by sname order by buydate) as sum_cost
from
cost_fee
結(jié)果如下：
注意： 結(jié)合②③，大家可以發(fā)現(xiàn)partition by結(jié)合order by，與不結(jié)合order by，得到的完全是不同的結(jié)果。一個(gè)是分組求總和（不加order by）；一個(gè)是分組求累計(jì)和（加order by）。
2. 排序函數(shù) + over()搭配
① 計(jì)算每個(gè)科目的排名，相同的分?jǐn)?shù)排名不同，順序依次增加
select
sname
,subject
,score
,row_number() over(partition by subject order by score) rank1
from
exam_score
結(jié)果如下：
② 計(jì)算每個(gè)科目的排名，相同的分?jǐn)?shù)排名相同，余下排名跳躍增加
select
sname
,subject
,score
,rank() over(partition by subject order by score) rank1
from
exam_score
結(jié)果如下：
③ 計(jì)算每個(gè)科目的排名，相同的分?jǐn)?shù)排名相同，余下排名順序增加
select
sname
,subject
,score
,dense_rank() over(partition by subject order by score) rank1
from
exam_score
結(jié)果如下：
3. ntile()函數(shù) + over()搭配
ntile()函數(shù)有點(diǎn)亂入的感覺(jué)，你不知道給它分哪一類。該函數(shù)主要用 “數(shù)據(jù)切分”。如果說(shuō)這個(gè)函數(shù)還有點(diǎn)用的話，就是他也可以對(duì)數(shù)據(jù)進(jìn)行排序，類似于上面提到的row_number()函數(shù)。
① 對(duì)exam_score表，進(jìn)行整張表切分
select
sname
,subject
,score
,ntile(4) over() rank1
from
exam_score
結(jié)果如下：
不信你下去試一下，ntile()里面不管寫(xiě)哪個(gè)數(shù)字，好像都可以。
② 對(duì)exam_score表，按照subject分組切分
select
sname
,subject
,score
,ntile(4) over(partition by subject) rank1
from
exam_score
結(jié)果如下：
即使是分組切分，你也會(huì)發(fā)現(xiàn)，這樣毫無(wú)意義，因?yàn)閟core并沒(méi)有排序。
③ 對(duì)exam_score表，對(duì)score排序后，按照subject分組切分（最有用）
select
sname
,subject
,score
,ntile(4) over(partition by subject order by score) rank1
from
exam_score
結(jié)果如下：
注意： 仔細(xì)觀察這種用法，基本可以等效row_number()函數(shù)，效果是一樣的。
4. 偏移函數(shù) + over()搭配
① 展示各位同學(xué)的“上次購(gòu)買(mǎi)時(shí)間”和“下次購(gòu)買(mǎi)時(shí)間”
注：對(duì)于第一天，顯示 “first buy”；對(duì)于最后一天，顯示 “last buy”；
select
sname
,buydate
,lag(buydate,1,"first day") over(partition by sname order by buydate) as 上次購(gòu)買(mǎi)時(shí)間
,lead(buydate,1,"last day") over(partition by sname order by buydate) as 下次購(gòu)買(mǎi)時(shí)間
from
cost_fee
結(jié)果如下：
② 截止到當(dāng)前日期，每位同學(xué)的“首次購(gòu)買(mǎi)時(shí)間”和“最后一次購(gòu)買(mǎi)時(shí)間”
select
sname
,buydate
,first_value(buydate) over(partition by sname order by buydate) as 首次購(gòu)買(mǎi)時(shí)間
,last_value(buydate) over(partition by sname order by buydate) as 最后一次購(gòu)買(mǎi)時(shí)間
from
cost_fee
結(jié)果如下：
③ 展示每位同學(xué)的“首次購(gòu)買(mǎi)時(shí)間”和“最后一次購(gòu)買(mǎi)時(shí)間”
注意： 這里并沒(méi)有說(shuō) “截止到當(dāng)前日期”，請(qǐng)注意②③之間的區(qū)別呀。需求不同，結(jié)果就不同。
select
sname
,buydate
,first_value(buydate) over(partition by sname order by buydate) as 首次購(gòu)買(mǎi)時(shí)間
,last_value(buydate) over(partition by sname ) as 最后一次購(gòu)買(mǎi)時(shí)間
from
cost_fee
結(jié)果如下：
以上就是MySQL實(shí)戰(zhàn)窗口函數(shù)SQL分析班級(jí)學(xué)生考試成績(jī)及生活消費(fèi)的詳細(xì)內(nèi)容，更多關(guān)于SQL窗口函數(shù)分析成績(jī)及消費(fèi)的資料請(qǐng)關(guān)注服務(wù)器之家其它相關(guān)文章！
原文鏈接：https://huang-tong-xue.blog.csdn.net/article/details/115908762
