一、背景介紹
今天，野雞大學高（三）班的月考成績出來了，這裡先給大家公布一下各位同學的考試成績。
接着，在給大家公布一下各位同學的生活消費情況。
下面我們利用上述考試成績和生活消費記錄，利用mysql做一個簡單的分析。當然，從本文标題就可以看出來。本文就是要結合這份資料，為大家講述SQL “視窗函數” 應該怎麼用？
從上面這張圖，應該知道這個知識點的重要性了。包括你以後學習hive或者oracle資料庫，或者說資料分析面試，這都将是一個很重要的知識點。
二、建表語句和插入資料
建立表格
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 + 字段：這個更容易了解，就是用于“排序”的關鍵字；
四、視窗函數應用
上面給大家介紹了若幹常用的 “視窗函數”，這裡利用文首建立的資料，講講 “視窗函數” 的應用。
希望大家通過每個案例，來總結一下每個函數的含義，這裡就不詳細寫了。
1. 聚合函數 + over()搭配
① 計算每位同學的得分與平均值的情況
select
sname
,subject
,score
,avg(score) over(partition by sname) as avg_score
from 結果如下：
② 計算每位同學1-3月消費情況和消費總額
select
sname
,buydate
,buycost
,sum(buycost) over(partition by sname) as sum_cost
from 結果如下：
③ 計算每位同學1-3月消費情況和累計消費總額
select
sname
,buydate
,buycost
,sum(buycost) over(partition by sname order by buydate) as sum_cost
from 結果如下：
注意： 結合②③，大家可以發現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 結果如下：
② 計算每個科目的排名，相同的分數排名相同，餘下排名跳躍增加
select
sname
,subject
,score
,rank() over(partition by subject order by score) rank1
from 結果如下：
③ 計算每個科目的排名，相同的分數排名相同，餘下排名順序增加
select
sname
,subject
,score
,dense_rank() over(partition by subject order by score) rank1
from 結果如下：
3. ntile()函數 + over()搭配
ntile()函數有點亂入的感覺，你不知道給它分哪一類。該函數主要用 “資料切分”。如果說這個函數還有點用的話，就是他也可以對資料進行排序，類似于上面提到的row_number()函數。
① 對exam_score表，進行整張表切分
select
sname
,subject
,score
,ntile(4) over() rank1
from 結果如下：
不信你下去試一下，ntile()裡面不管寫哪個數字，好像都可以。
② 對exam_score表，按照subject分組切分
select
sname
,subject
,score
,ntile(4) over(partition by subject) rank1
from 結果如下：
即使是分組切分，你也會發現，這樣毫無意義，因為score并沒有排序。
② 對exam_score表，對score排序後，按照subject分組切分（最有用）
select
sname
,subject
,score
,ntile(4) over(partition by subject order by score) rank1
from 結果如下：
注意： 仔細觀察這種用法，基本可以等效row_number()函數，效果是一樣的。
4. 偏移函數 + over()搭配
① 展示各位同學的“上次購買時間”和“下次購買時間”
注：對于第一天，顯示 “first buy”；對于最後一天，顯示 “last 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 結果如下：
② 截止到目前日期，每位同學的“首次購買時間”和“最後一次購買時間”
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 結果如下：
③ 展示每位同學的“首次購買時間”和“最後一次購買時間”
注意： 這裡并沒有說 “截止到目前日期”，請注意②③之間的差別呀。需求不同，結果就不同。
select
sname
,buydate
,first_value(buydate) over(partition by sname order by buydate) as 首次購買時間
,last_value(buydate) over(partition by sname ) as 最後一次購買時間
from