腳本之家服務(wù)器常用軟件

快捷導(dǎo)航

MySQL實戰(zhàn)窗口函數(shù)SQL分析班級學(xué)生考試成績及生活消費

更新時間：2021年10月26日 09:21:36 作者：數(shù)據(jù)分析與統(tǒng)計學(xué)之美

這篇文章主要為大家介紹了MySQL實戰(zhàn)，利用窗口函數(shù)SQL來分析班級學(xué)生的考試成績及生活消費的示例過程，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步

一、背景介紹

今天，野雞大學(xué)高（三）班的月考成績出來了，這里先給大家公布一下各位同學(xué)的考試成績。

在這里插入圖片描述

接著，在給大家公布一下各位同學(xué)的生活消費情況。

在這里插入圖片描述

下面我們利用上述考試成績和生活消費記錄，利用mysql做一個簡單的分析。

當(dāng)然，從本文標(biāo)題就可以看出來。本文就是要結(jié)合這份數(shù)據(jù)，為大家講述SQL “窗口函數(shù)” 應(yīng)該怎么用？

包括你以后學(xué)習(xí)hive或者oracle數(shù)據(jù)庫，或者說數(shù)據(jù)分析面試，這都將是一個很重要的知識點。

二、建表語句和插入數(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, '語文' , 90),
('張三' , 18, '數(shù)學(xué)' , 80),
('張三' , 18, '英語' , 70),
('李四' , 21, '語文' , 88),
('李四' , 21, '數(shù)學(xué)' , 78),
('李四' , 21, '英語' , 71),
('王五' , 18, '語文' , 95),
('王五' , 18, '數(shù)學(xué)' , 83),
('王五' , 18, '英語' , 71),
('趙六' , 19, '語文' , 98),
('趙六' , 19, '數(shù)學(xué)' , 90),
('趙六' , 19, '英語' , 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)圖。

在這里插入圖片描述

對于over()里面，這里還有兩個常用的關(guān)鍵字，必須要講述。如下：

partition by + 字段：你可以想象成group by關(guān)鍵字，就是用于 “分組” 的關(guān)鍵字；

order by + 字段：這個更容易理解，就是用于 “排序” 的關(guān)鍵字；

四、窗口函數(shù)應(yīng)用

上面給大家介紹了若干常用的 “窗口函數(shù)”，這里利用文首創(chuàng)建的數(shù)據(jù)，講講 “窗口函數(shù)” 的應(yīng)用。

希望大家通過每個案例，來總結(jié)一下每個函數(shù)的含義，這里就不詳細(xì)寫了。

1. 聚合函數(shù) + over()搭配

① 計算每位同學(xué)的得分與平均值的情況

select 
	sname
    ,subject
    ,score
    ,avg(score) over(partition by sname) as avg_score
from
	exam_score

結(jié)果如下：

在這里插入圖片描述

② 計算每位同學(xué)1-3月消費情況和消費總額

select
	sname
    ,buydate
    ,buycost
    ,sum(buycost) over(partition by sname) as sum_cost
from
	cost_fee

結(jié)果如下：

在這里插入圖片描述

③ 計算每位同學(xué)1-3月消費情況和累計消費總額

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é)果。一個是分組求總和（不加order by）；一個是分組求累計和（加order by）。

2. 排序函數(shù) + over()搭配

① 計算每個科目的排名，相同的分?jǐn)?shù)排名不同，順序依次增加

select
	sname
	,subject
	,score
    ,row_number() over(partition by subject order by score) rank1
from
	exam_score

結(jié)果如下：

在這里插入圖片描述

② 計算每個科目的排名，相同的分?jǐn)?shù)排名相同，余下排名跳躍增加

select
	sname
	,subject
	,score
    ,rank() over(partition by subject order by score) rank1
from
	exam_score

結(jié)果如下：

在這里插入圖片描述

③ 計算每個科目的排名，相同的分?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ù)有點亂入的感覺，你不知道給它分哪一類。該函數(shù)主要用 “數(shù)據(jù)切分”。如果說這個函數(shù)還有點用的話，就是他也可以對數(shù)據(jù)進(jìn)行排序，類似于上面提到的row_number()函數(shù)。

① 對exam_score表，進(jìn)行整張表切分

select
	sname
	,subject
	,score
    ,ntile(4) over() rank1
from
	exam_score

結(jié)果如下：

在這里插入圖片描述

不信你下去試一下，ntile()里面不管寫哪個數(shù)字，好像都可以。

② 對exam_score表，按照subject分組切分

select
	sname
	,subject
    ,score
    ,ntile(4) over(partition by subject) rank1
from
	exam_score

結(jié)果如下：

在這里插入圖片描述

即使是分組切分，你也會發(fā)現(xiàn)，這樣毫無意義，因為score并沒有排序。

③ 對exam_score表，對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é)的“上次購買時間”和“下次購買時間”

注：對于第一天，顯示 “first buy”；對于最后一天，顯示 “l(fā)ast buy”；

select
	sname
	,buydate
    ,lag(buydate,1,'first day') over(partition by sname order by buydate) as 上次購買時間
    ,lead(buydate,1,'last day') over(partition by sname order by buydate) as 下次購買時間
from
	cost_fee

結(jié)果如下：

在這里插入圖片描述

② 截止到當(dāng)前日期，每位同學(xué)的“首次購買時間”和“最后一次購買時間”

select
	sname
	,buydate
    ,first_value(buydate) over(partition by sname order by buydate) as 首次購買時間
    ,last_value(buydate) over(partition by sname order by buydate) as 最后一次購買時間
from
	cost_fee

結(jié)果如下：

在這里插入圖片描述

③ 展示每位同學(xué)的“首次購買時間”和“最后一次購買時間”

注意：這里并沒有說 “截止到當(dāng)前日期”，請注意②③之間的區(qū)別呀。需求不同，結(jié)果就不同。

select
	sname
	,buydate
    ,first_value(buydate) over(partition by sname order by buydate) as 首次購買時間
    ,last_value(buydate) over(partition by sname ) as 最后一次購買時間
from
	cost_fee

結(jié)果如下：

在這里插入圖片描述