• 窗口函數(shù)簡(jiǎn)介
• 數(shù)據(jù)準(zhǔn)備
• 問(wèn)題描述
• 解答

窗口函數(shù)簡(jiǎn)介

窗口函數(shù)是 SQL 中一類(lèi)特別的函數(shù)。

• 和聚合函數(shù)相似船庇，窗口函數(shù)的輸入也是多行記錄
• 不同的是灰署，聚合函數(shù)對(duì)其所作用的每一組記錄輸出一條結(jié)果腺怯，而窗口函數(shù)對(duì)其所作用的窗口中的每一行記錄輸出一條結(jié)果
• OVER 子句定義查詢(xún)結(jié)果集內(nèi)的窗口泳赋。 然后，開(kāi)窗函數(shù)（sum汇跨、max务荆、min、avg穷遂、count函匕、rank等）將計(jì)算窗口中每一行的值，以便取得各種聚合值蚪黑，例如 移動(dòng)平均值盅惜、累積聚合吸耿、運(yùn)行總計(jì)、每組結(jié)果的前 N 個(gè)結(jié)果

窗口函數(shù)定義

開(kāi)窗函數(shù) OVER (   
       [ <PARTITION BY clause> ]  
       [ <ORDER BY clause> ]   
       [ <ROW or RANGE clause> ]  
)  

1. partition by

指定分區(qū)的列 酷窥，將查詢(xún)結(jié)果集分為多個(gè)分區(qū)。 開(kāi)窗函數(shù)分別應(yīng)用于每個(gè)分區(qū)伴网，并為每個(gè)分區(qū)重新啟動(dòng)計(jì)算蓬推。
只能引用可供 Select 子句使用的列。不能引用選擇列表中的表達(dá)式或別名澡腾。 可以是列表達(dá)式沸伏、標(biāo)量子查詢(xún)、標(biāo)量函數(shù)或用戶(hù)定義的變量动分。

2. order by

定義結(jié)果集的每個(gè)分區(qū)中行的 邏輯順序
指定用于進(jìn)行排序的列或表達(dá)式毅糟。 只能引用可供 Select 子句使用的列。 不能將整數(shù)指定為表示列名或別名澜公。
如果使用rank類(lèi)函數(shù)姆另，必須有order by；

asc | desc：指定按升序或降序排列坟乾。

3. ROWS | RANGE

通過(guò)指定分區(qū)中的起點(diǎn)和終點(diǎn)迹辐，進(jìn)一步限制分區(qū)中的行數(shù)。

• 必須和order by 子句同時(shí)使用甚侣；
• 如果指定了order by 子句未指定窗口子句明吩，則默認(rèn)為RANGE BETWEEN unbounded preceding AND CURRENT ROW；
• ROWS 子句通過(guò)指定 固定數(shù)目的行殷费；
• RANGE 子句通過(guò)指定 針對(duì)當(dāng)前行中的值的某一范圍的值印荔；
• BETWEEN ··· AND ···
  與 ROWS 或 RANGE 一起使用，以便指定窗口的下（開(kāi)始）邊界和上（結(jié)束）邊界點(diǎn)

窗口范圍

數(shù)據(jù)準(zhǔn)備：全校的成績(jī)數(shù)據(jù)

#--創(chuàng)建表
CREATE TABLE StudentScore (
    Id int(10) primary key NOT NULL auto_increment,
    StudentId int(10) NOT NULL DEFAULT 0,
    ClassId int(10) NOT NULL DEFAULT 0,
    CourseId int(10) NOT NULL DEFAULT 0,
    Score float NOT NULL DEFAULT 0,
    CreateDate datetime NOT NULL DEFAULT now()
) ;
#--插入數(shù)據(jù)
#--CourseId 2:語(yǔ)文 4：數(shù)學(xué) 8：英語(yǔ)

#--1班學(xué)生成績(jī)
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (1,1,2,85);
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (2,1,2,95.5);
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (3,1,2,90);
 
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (1,1,4,90);
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (2,1,4,98);
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (3,1,4,89);
 
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (1,1,8,80);
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (2,1,8,75.5);
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (3,1,8,77);
 
 
#--2班學(xué)生成績(jī)
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (1,2,2,90);
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (2,2,2,77);
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (3,2,2,78);
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (4,2,2,83);
 
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (1,2,4,98);
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (2,2,4,95);
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (3,2,4,78);
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (4,2,4,100);
 
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (1,2,8,85);
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (2,2,8,90);
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (3,2,8,86);
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (4,2,8,78.5);
 
#studentscore--3班學(xué)生成績(jī)
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (1,3,2,82);
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (2,3,2,78);
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (3,3,2,91);
 
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (1,3,4,83);
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (2,3,4,78);
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (3,3,4,99);
 
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (1,3,8,86);
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (2,3,8,78);
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (3,3,8,97);

實(shí)操

問(wèn)答一

前幾名學(xué)生的成績(jī)合計(jì)數(shù)
解題：

SELECT studentId,ClassId,CourseId,Score,
    sum(score) over(partition by CourseId order by score rows between unbounded preceding and current row) as '移動(dòng)合計(jì)',
    sum(score) over(partition by CourseId order by score range between unbounded preceding and current row) as '移動(dòng)合計(jì)',
    sum(score) over(partition by CourseId order by score rows between 1 preceding and 1 following) as '移動(dòng)合計(jì)',
    sum(score) over(partition by CourseId order by score range between 1 preceding and 1 following) as '移動(dòng)合計(jì)'
FROM test_schema.studentscore
where courseId = 2;

查詢(xún)結(jié)果

問(wèn)答二

所有班級(jí)所有學(xué)生的語(yǔ)文平均分
每一個(gè)班級(jí)的語(yǔ)文平均分，可根據(jù)PARTION BY來(lái)進(jìn)行分組

SELECT studentId,ClassId,CourseId,Score,
    avg(score) over() as '語(yǔ)文平均分 全局',
    cast(avg(score) over(partition by classId) as decimal(5,2)) as '語(yǔ)文平均分 分班級(jí)'
FROM test_schema.studentscore
where courseId = 2;

查詢(xún)結(jié)果

問(wèn)答三

按照數(shù)學(xué)成績(jī)逆序排列：

SELECT studentId,ClassId,CourseId,Score,
    row_number() over(order by Score desc) as '數(shù)學(xué)成績(jī)排名',
    rank() over(order by Score desc) as '數(shù)學(xué)成績(jī)排名',
    dense_rank() over(order by Score desc) as '數(shù)學(xué)成績(jī)排名',
    ntile(3) OVER(ORDER BY score DESC) AS '組編號(hào)'
FROM test_schema.studentscore
where courseId = 8;

查詢(xún)結(jié)果

排名函數(shù)對(duì)比：