目錄
一、小案例：
1.1測試資料如下：
1.2使用over(partition by)函數實作小案例中的SQL
二、知識擴充
2.1 over(partition by)函數的寫法
2.2 與over(partition by)函數結合的函數的介紹
2.2.1 rank() 與over(partition by ... order by ...)組合函數
2.2.2 row_number() 與over(partition by ... order by ...)組合函數
2.2.3 關于rank() 和row_number()的總結
一、小案例：
school表中有①id 序号②class 班級 ③score成績 三個字段， 使用oracle實作按照班級分區，然後取班級中的第一名。
1.1測試資料如下：
--建立學校表school
create table school(
id varchar2(10) primary key, --序号
class varchar2(10), --班級
score NUMBER --分數
);
--插入幾條資料
insert into school (id, CLASS, SROCE) values ('1', '一班', 93);
insert into school (id, CLASS, SROCE) values ('2', '一班', 93);
insert into school (id, CLASS, SROCE) values ('3', '一班', 92);
insert into school (id, CLASS, SROCE) values ('4', '一班', 81);
insert into school (id, CLASS, SROCE) values ('5', '二班', 99);
insert into school (id, CLASS, SROCE) values ('6', '二班', 99);
insert into school (id, CLASS, SROCE) values ('7', '二班', 92);
insert into school (id, CLASS, SROCE) values ('8', '二班', 83);
1.2使用over(partition by)函數實作小案例中的SQL
select *
from (select t.id,
t.class,
t.sroce,
rank() over(partition by t.class order by t.sroce desc) n
from school t)
where n = 1;
二、知識擴充
簡介：
Oracle從8.1.6開始提供分析函數，分析函數用于計算基于組的某種聚合值，它和聚合函數的不同之處是對于每個組傳回多行，而聚合函數對于每個組隻傳回一行。
2.1 over(partition by)函數的寫法
over（partition by class order by sroce） 先按照class分區,再按照sroce排序，order by是個預設的開窗函數。
2.2 與over(partition by)函數結合的函數的介紹
row_number() over(partition by ... order by ...)：傳回分組排序後的順序
rank() over(partition by ... order by ...)：傳回分組排序後的排名(并列第一名的情況下傳回：第一名，第一名，第三名)
dense_rank() over(partition by ... order by ...)：傳回分組排序後的排名（并列第一名的情況下傳回：第一名，第一名，第二名)
count(A) over(partition by ... order by ...)：傳回分組排序後的總數。
max(A) over(partition by ... order by ...)：傳回分組排序後的最大值。
min(A) over(partition by ... order by ...)：傳回分組排序後的最小值。
avg(A) over(partition by ... order by ...)：傳回分組排序後的平均值。
sum(A) over(partition by ... order by ...)：傳回分組排序後的累加求和。
lag(A,1) over(partition by ... order by ...)：取出上一列的A的值放到本列中。
lead(A,1) over(partition by ... order by ...)：取出下一列的A的值放到本列中。
ratio_to_report(A) over(partition by B) 傳回分組後的A在其分區B内的占比，A就是分子，B分的組就是分母
2.2.1 rank() 與over(partition by ... order by ...)組合函數
公式：
rank() over(partition by A order by B) --按照A分區，按照B排序
根據小案例中資料，使用rank()函數 按照班級分區，然後取班級中的第一名
select *
from (select t.id 序号,
t.class 班級,
t.sroce 成績,
rank() over(partition by t.class order by t.sroce desc) n傳回值
from school t)
--where n = 1
;
結果：對分區間内的查詢的記錄排名，如下圖，有兩個并列第一的情況，則有兩個第一名，然後是第三名，是以二班為一個區間 傳回1,1,3,4 ；一班為一個區間傳回1,1,3,4
2.2.2 row_number() 與over(partition by ... order by ...)組合函數
公式
row_number() over(partition by A order by B) --按照A分區，按照B排序
根據小案例中資料，使用row_number()函數 按照班級分區，然後取班級中的第一名
select *
from (select t.id 序号,
t.class 班級,
t.sroce 成績,
row_number() over(partition by t.class order by t.sroce desc) n傳回值
from school t)
--where n = 1
;
結果：簡單的說row_number()從1開始，為每一條分區中的記錄傳回一個數字，如下圖中二班為一個區間 傳回1,2,3,4 ；一班為一個區間傳回1,2,3,4
2.2.3 關于rank() 和row_number()的總結
綜合上述案例，row_number()：适用于将select查詢到的資料進行排序，每一條資料加一個序号，他不能用做于學生成績的排名，一般多用于分頁查詢，
比如查詢前10個 查詢10-100個學生。
rank() ：可以了解為 排名函數，在求第一名成績的時候，如果同班有兩個并列第一，rank()傳回兩個結果。