1. 窗口函數(shù)概述

1.1 介紹

Mysql8.0新增窗口函數(shù)，窗口函數(shù)又被稱為開(kāi)窗函數(shù)，與Oracle窗口函數(shù)類似，屬于Mysql的一大特點(diǎn)。非聚合窗口函數(shù)是相對(duì)于聚合函數(shù)來(lái)說(shuō)的。聚合函數(shù)是對(duì)一組數(shù)據(jù)計(jì)算后返回單個(gè)值（即分組），非聚合函數(shù)一次只會(huì)處理一行數(shù)據(jù)。窗口聚合函數(shù)在行記錄上計(jì)算某個(gè)字段的結(jié)果時(shí)，可將窗口范圍內(nèi)的數(shù)據(jù)輸入到聚合函數(shù)中，并不改變函數(shù)。

1.2 作用

• 查詢每一行數(shù)據(jù)時(shí)，使用指定的窗口函數(shù)對(duì)每行關(guān)聯(lián)的一組數(shù)據(jù)進(jìn)行處理。
  
• 簡(jiǎn)單
  • 窗口函數(shù)更易于使用。
  • 之前需要通過(guò)定義臨時(shí)變量和大量的子查詢或關(guān)聯(lián)才能完成的工作，使用窗口函數(shù)實(shí)現(xiàn)起來(lái)更加簡(jiǎn)潔高效。窗口函數(shù)也是面試及實(shí)際工作的高頻點(diǎn)。
• 快速
  • 使用窗口函數(shù)比使用替代方法要快得多。當(dāng)你處理成百上千個(gè)千兆字節(jié)的數(shù)據(jù)時(shí)，這非常有用。
• 多功能性
  • 最重要的是，窗口函數(shù)具有多種功能，比如：求差值、求占比、求排名、累計(jì)值計(jì)算等等。

2. 場(chǎng)景說(shuō)明

2.1 準(zhǔn)備工作

-- 創(chuàng)建表格
CREATE TABLE score (id INT PRIMARY KEY,name VARCHAR(50),gender CHAR(1),score INT
);-- 插入數(shù)據(jù)
INSERT INTO score (id, name, gender, score) VALUES
(1, '賈寶玉', '男', 85),
(2, '林黛玉', '女', 90),
(3, '薛寶釵', '女', 78),
(4, '王熙鳳', '女', 92),
(5, '史湘云', '女', 88),
(6, '賈璉', '男', 86),
(7, '賈環(huán)', '男', 87);

2.2 場(chǎng)景說(shuō)明

需求：計(jì)算每個(gè)學(xué)生的分?jǐn)?shù)和整體平均值的差值。

2.3 分析

第一步：求出平均分
第二步：差值=成績(jī)-平均分

2.4 實(shí)現(xiàn)

2.4.1 非窗口函數(shù)方式實(shí)現(xiàn)

select id, name, gender, score,round((select avg(score) from score),1) as `平均分`,score - (round((select avg(score) from score),1)) as `差值`
from score;

2.4.2 窗口函數(shù)方式實(shí)現(xiàn)

select id, name, gender, score,round(avg(score) over(),1) as `平均分`,round(score - (avg(score) over()),1) as `差值`
from score;

3. 窗口函數(shù)分類

另外還有開(kāi)窗聚合函數(shù)：SUM，AVG，MIN，MAX。

4. 窗口函數(shù)基礎(chǔ)用法：OVER關(guān)鍵字

4.1 語(yǔ)法

select字段,... ...,<window_function> over(... ...)
from 表;

• OVER(...)的作用就是設(shè)置每一行數(shù)據(jù)關(guān)聯(lián)的一組數(shù)據(jù)范圍，OVER()時(shí)，每行關(guān)聯(lián)的數(shù)據(jù)范圍都是整張表的數(shù)據(jù)。
• <window function>表示使用的窗口函數(shù)，窗口函數(shù)可以使用之前已經(jīng)學(xué)過(guò)的聚合函數(shù)，比如COUNT、SUM、AVG等，也可以是其他函數(shù)，比如 ranking 排序函數(shù)等。

4.2 場(chǎng)景一 :計(jì)算每個(gè)值和整體平均值的差值

4.2.1 需求

4.2.2 分析

4.2.3 實(shí)現(xiàn)

select id, name, gender, score,round(avg(score) over(),1) as `平均分`,round(score - (avg(score) over()),1) as `差值`
from score;

4.3 場(chǎng)景二: 計(jì)算每個(gè)值占整體之和的占比

4.3.1 需求

4.3.2 分析

4.3.3 非窗口函數(shù)實(shí)現(xiàn)

selectid, name, gender, score,(select sum(score) from score) as sum_score,round(100 * score / (select sum(score) from score),1) as rate
from score;

4.3.4 窗口函數(shù)實(shí)現(xiàn)

selectid, name,gender, score,sum(score) over() as sum_score,round(100 * score / (sum(score) over()),1) as rate
from score;

5. PARTITION BY分區(qū)

5.1 場(chǎng)景說(shuō)明

• 如何計(jì)算每個(gè)學(xué)生的Score 分?jǐn)?shù)和同性別學(xué)生平均分的差值？
  

5.2 語(yǔ)法

• partition by 作用：用于對(duì)整張表的數(shù)據(jù)進(jìn)行分區(qū)(分組)操作。

select字段,... ...,<window_function> over(partition by 字段 ...)
from 表;

• PARTITION BY 列名, ... 的作用是按照指定列的值對(duì)整張表的數(shù)據(jù)進(jìn)行分區(qū)，OVER()中沒(méi)有PARTITION BY時(shí)，整張表就是一個(gè)分區(qū)。
• 分區(qū)之后，在處理每行數(shù)據(jù)時(shí)，<window function>是作用在該行數(shù)據(jù)關(guān)聯(lián)的分區(qū)上，不再是整張表。

5.3 分析

第一步：求出平均分（按性別分組求）

第二步：求出每個(gè)人的分?jǐn)?shù)與平均分的差值

5.4 非窗口函數(shù)實(shí)現(xiàn)

selectid, name,gender, score,round((select avg(b.score) from score b where b.gender=a.gender),1) as avg_score,round(score - (select avg(b.score) from score b where b.gender=a.gender),1) as diff
from score a;

5.5 窗口函數(shù)實(shí)現(xiàn)

selectid, name,gender, score,round(avg(score) over(partition by gender),1) as avg_score,round(score-(avg(score) over(partition by gender)),1) as rate
from score ;

5.6 GROUP BY 與 PARTITION BY的區(qū)別

• 使用場(chǎng)景不同
  • GROUP BY分組是為了聚合，分組聚合屬于：多進(jìn)一出。
  • PARTITION BY分區(qū)是為了配合窗口函數(shù)做運(yùn)算，窗口函數(shù)屬于：一進(jìn)一出。
    
    
    

6. 排名函數(shù)：產(chǎn)生排名

6.1 場(chǎng)景說(shuō)明

6.2 準(zhǔn)備工作

-- 創(chuàng)建學(xué)生成績(jī)表
create table student_scores (studentname varchar(50) not null,subject varchar(50) not null,score int not null,primary key (studentname, subject)
);-- 插入張三的成績(jī)數(shù)據(jù)
insert into student_scores (studentname, subject, score) values ('張三', '語(yǔ)文', 81);
insert into student_scores (studentname, subject, score) values ('張三', '數(shù)學(xué)', 75);-- 插入李四的成績(jī)數(shù)據(jù)
insert into student_scores (studentname, subject, score) values ('李四', '語(yǔ)文', 76);
insert into student_scores (studentname, subject, score) values ('李四', '數(shù)學(xué)', 90);-- 插入王五的成績(jī)數(shù)據(jù)
insert into student_scores (studentname, subject, score) values ('王五', '語(yǔ)文', 81);
insert into student_scores (studentname, subject, score) values ('王五', '數(shù)學(xué)', 100);

6.3 語(yǔ)法

• 排名函數(shù)作用：用于按照指定列對(duì)每一行產(chǎn)生一個(gè)所在分區(qū)內(nèi)的排名序號(hào)。

select字段,... ...,<排名函數(shù)> over(order by 字段 ...)
from 表;

• OVER() 中可以指定 ORDER BY 按照指定列對(duì)每個(gè)分區(qū)內(nèi)的數(shù)據(jù)進(jìn)行排序。
• 排名函數(shù)用于對(duì)分區(qū)內(nèi)的每行數(shù)據(jù)產(chǎn)生一個(gè)排名序號(hào)。
  • RANK、DENSE_RANK、ROW_NUMBER。

6.3 分析

6.4 實(shí)現(xiàn)

6.4.1 非窗口函數(shù)方式實(shí)現(xiàn)

-- 傳統(tǒng)方式 排名 并列 且 排名連續(xù)
selectstudentname, subject, score,(select count(distinct score) from student_scores b where b.score>a.score) + 1 as `排名`
from student_scores a
order by score desc;

6.4.2 窗口函數(shù)方式實(shí)現(xiàn)

6.4.2.1 rank

-- 排名并列 但 排名不連續(xù)
select studentname,subject,score,rank() over (order by score desc) as `排名`
from student_scores;

6.4.2.2 dense_rank

-- 排名并列 且 排名連續(xù)
select studentname,subject,score,dense_rank() over (order by score desc) as `排名`
from student_scores;

6.4.2.3 row_number

-- 排名連續(xù) 但 不考慮排名并列
select studentname,subject,score,row_number() over (order by score desc) as `排名`
from student_scores;

6.4.2.4 總結(jié)

• RANK()：有并列的情況出現(xiàn)時(shí)序號(hào)會(huì)重復(fù)但不連續(xù)。
• DENSE_RANK()：有并列的情況時(shí)序號(hào)會(huì)重復(fù)但連續(xù)。
• ROW_NUMBER()：返回連續(xù)唯一的行號(hào)，序號(hào)不會(huì)重復(fù)且連續(xù)。
  

7. PARTITION BY和排名函數(shù)

• 規(guī)律: 只要碰到每個(gè)每種等類似詞匯, 肯定分組
  • group by : 多進(jìn)一出
  • partition by : 一進(jìn)一出

  7.1 場(chǎng)景說(shuō)明

  • 先分組, 再排名
    

7.2 分析

7.3 實(shí)現(xiàn)

7.3.1 非窗口函數(shù)實(shí)現(xiàn)

selectstudentname,subject, score,(select count(*) + 1 from student_scores b where b.subject=a.subject and b.score>a.score) as `排名`
from student_scores a
order by subject,`排名`;

7.3.2 窗口函數(shù)實(shí)現(xiàn)

selectstudentname, subject, score,rank() over (partition by subject order by score desc) as `排名`
from student_scores;

8. 排名 練習(xí)一

8.1 準(zhǔn)備工作

-- 創(chuàng)建一個(gè)名為 Students 的表，增加區(qū)域列
create table Students (id int primary key auto_increment,  -- 學(xué)生ID，自動(dòng)遞增name varchar(50),                    -- 學(xué)生姓名score int,                           -- 學(xué)生成績(jī)region varchar(10)                   -- 學(xué)生區(qū)域（魏、蜀、吳）
);-- 向 Students 表中插入數(shù)據(jù)，使用三國(guó)人物作為姓名及區(qū)域
insert into Students (name, score, region) values
-- 魏國(guó)人物
('曹操', 95, '魏'),
('司馬懿', 89, '魏'),
('荀彧', 84, '魏'),
('甄氏', 91, '魏'),
('夏侯惇', 88, '魏'),
-- 蜀國(guó)人物
('劉備', 85, '蜀'),
('關(guān)羽', 92, '蜀'),
('張飛', 78, '蜀'),
('諸葛亮', 88, '蜀'),
('黃承兒', 80, '蜀'),
-- 吳國(guó)人物
('孫權(quán)', 76, '吳'),
('周瑜', 90, '吳'),
('魯肅', 83, '吳'),
('陸遜', 86, '吳'),
('小喬', 87, '吳');

8.2 需求: 去掉最高分和去掉最低分 求平均分

8.3 分析

8.4 實(shí)現(xiàn)

with t1 as (select*,row_number() over (order by score asc) rn1,row_number() over (order by score desc) rn2from Students
)
selectround(avg(score), 2) as avg_score
from t1
where t1.rn1>1 and t1.rn2>1

9. 排名 練習(xí)二

9.1 需求:

求每個(gè)部門 去掉最高分和去掉最低分 求平均分

9.2 分析

9.3 實(shí)現(xiàn)

-- 需求2: 求每個(gè)部門 去掉最高分和去掉最低分 求平均分
with t1 as (select*,row_number() over (partition by region order by score asc) rn1,row_number() over (partition by region order by score desc) rn2from Students
)
selectround(avg(score), 2) as avg_score
from t1
where t1.rn1>1 and t1.rn2>1
group by region;

---

感謝觀看，未完待續(xù)…