人妖在线一区,国产日韩欧美一区二区综合在线,国产啪精品视频网站免费,欧美内射深插日本少妇

新聞動態(tài)

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

發(fā)布日期:2022-02-05 17:58 | 文章來源:腳本之家

一、背景介紹

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

接著,在給大家公布一下各位同學的生活消費情況。

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

當然,從本文標題就可以看出來。本文就是要結合這份數據,為大家講述SQL “窗口函數” 應該怎么用?

包括你以后學習hive或者oracle數據庫,或者說數據分析面試,這都將是一個很重要的知識點。

二、建表語句和插入數據

創(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;

插入數據

insert into exam_score values
('張三' , 18, '語文' , 90),
('張三' , 18, '數學' , 80),
('張三' , 18, '英語' , 70),
('李四' , 21, '語文' , 88),
('李四' , 21, '數學' , 78),
('李四' , 21, '英語' , 71),
('王五' , 18, '語文' , 95),
('王五' , 18, '數學' , 83),
('王五' , 18, '英語' , 71),
('趙六' , 19, '語文' , 98),
('趙六' , 19, '數學' , 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);

三、窗口函數分類介紹

在正式講述 “窗口函數” 應用之前,我這里先帶著大家梳理一遍 “窗口函數” 的基礎。我們可以將窗口函數分為如下幾類:

聚合函數 + over()搭配;

排序函數 + over()搭配;

ntile()函數 + over()搭配;

偏移函數 + over()搭配;

具體每一類,有哪些函數呢?觀察下面的思維導圖。

對于over()里面,這里還有兩個常用的關鍵字,必須要講述。如下:

partition by + 字段:你可以想象成group by關鍵字,就是用于 分組” 的關鍵字;

order by + 字段:這個更容易理解,就是用于 “排序” 的關鍵字;

四、窗口函數應用

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

希望大家通過每個案例,來總結一下每個函數的含義,這里就不詳細寫了。

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

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

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

結果如下:

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

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

結果如下:

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

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

結果如下:

注意: 結合②③,大家可以發(fā)現partition by結合order by,與不結合order by,得到的完全是不同的結果。一個是分組求總和(不加order by);一個是分組求累計和(加order by)。

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

① 計算每個科目的排名,相同的分數排名不同,順序依次增加

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

結果如下:

② 計算每個科目的排名,相同的分數排名相同,余下排名跳躍增加

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

結果如下:

③ 計算每個科目的排名,相同的分數排名相同,余下排名順序增加

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

結果如下:

3. ntile()函數 + over()搭配

ntile()函數有點亂入的感覺,你不知道給它分哪一類。該函數主要用 數據切分”。如果說這個函數還有點用的話,就是他也可以對數據進行排序,類似于上面提到的row_number()函數。

① 對exam_score表,進行整張表切分

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

結果如下:

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

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

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

結果如下:

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

③ 對exam_score表,對score排序后,按照subject分組切分(最有用)

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

結果如下:

注意: 仔細觀察這種用法,基本可以等效row_number()函數,效果是一樣的。

4. 偏移函數 + over()搭配

① 展示各位同學的“上次購買時間”和“下次購買時間”

注:對于第一天,顯示 “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

結果如下:

② 截止到當前日期,每位同學的“首次購買時間”和“最后一次購買時間”

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

結果如下:

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

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

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

結果如下:

以上就是MySQL實戰(zhàn)窗口函數SQL分析班級學生考試成績及生活消費的詳細內容,更多關于SQL窗口函數分析成績及消費的資料請關注本站其它相關文章!

國外服務器租用

版權聲明:本站文章來源標注為YINGSOO的內容版權均為本站所有,歡迎引用、轉載,請保持原文完整并注明來源及原文鏈接。禁止復制或仿造本網站,禁止在非www.sddonglingsh.com所屬的服務器上建立鏡像,否則將依法追究法律責任。本站部分內容來源于網友推薦、互聯網收集整理而來,僅供學習參考,不代表本站立場,如有內容涉嫌侵權,請聯系alex-e#qq.com處理。

實時開通

自選配置、實時開通

免備案

全球線路精選!

全天候客戶服務

7x24全年不間斷在線

專屬顧問服務

1對1客戶咨詢顧問

在線
客服

在線客服:7*24小時在線

客服
熱線

400-630-3752
7*24小時客服服務熱線

關注
微信

關注官方微信
頂部