視窗函式的基本語法如下：

<視窗函式> over (partition by <用於分組的列名>
                order by <用於排序的列名>)

<視窗函式>的位置，可以放以下兩種函式：

1. 專用視窗函式
   • 序號函式：row_number() / rank() / dense_rank()
   • 分佈函式：percent_rank() / cume_dist()
   • 前後函式：lag() / lead()
   • 頭尾函式：first_val() / last_val()
   • 其他函式：nth_value() (用途：返回視窗中第N個expr的值，expr可以是表示式，也可以是列名)/nfile()（用途：將分割槽中的有序資料分為n個桶，記錄桶號）/nfile()
2. 聚合函式，如sum()， avg()， count()， max()， min()等

注意事項

• 視窗函式對where和group by子句處理後的結果進行操作，所以原則上視窗函式只能寫在select子句中
• 聚合函式是將多條記錄聚合為一條；而視窗函式是每條記錄都會執行，查詢結果並不會改變記錄條數，有幾條記錄執行完還是幾條。
• 普通聚合函式也可以用於視窗函式中，賦予它視窗函式的功能。
• 視窗函式的執行順序（邏輯上的）是在FROM，JOIN，WHERE，GROUP BY，HAVING之後，在ORDER　BY，LIMIT，SELECT，DISTINCT之前。它執行時GROUP BY的聚合過程已經完成了，所以不會再產生資料聚合。

建表

create table student (sid char(2), sname char(5), sclass char(2));
create table course (cid char(2), cname char(10));
create table score (sid char(2), cid char(2), score int);

insert into student values('01', '崔健', '01');
insert into student values('02', '李健', '01');
insert into student values('03', '高虎', '01');
insert into student values('04', '子健', '01');
insert into student values('05', '石璐', '01');
insert into student values('06', '亞千', '01');
insert into student values('07', '史立', '01');
insert into student values('08', '竇唯', '01');
insert into student values('09', '華東', '01');

insert into course values('01', '金屬');
insert into course values('02', '迷幻');
insert into course values('03', '朋克');
insert into course values('04', '後搖');

insert into score values('01', '01', 60);
insert into score values('02', '01', 85);
insert into score values('03', '01', 57);
insert into score values('04', '01', 34);
insert into score values('05', '01', 78);
insert into score values('06', '01', 90);
insert into score values('07', '01', 76);
insert into score values('08', '01', 90);
insert into score values('09', '01', 85);
insert into score values('01', '02', 78);
insert into score values('02', '02', 59);
insert into score values('03', '02', 59);
insert into score values('04', '02', 79);
insert into score values('05', '02', 88);
insert into score values('01', '03', 65);
insert into score values('03', '03', 89);
insert into score values('05', '03', 46);
insert into score values('06', '03', 85);
insert into score values('07', '03', 89);
insert into score values('08', '03', 79);
insert into score values('03', '04', 99);
insert into score values('04', '04', 95);
insert into score values('07', '04', 68);
insert into score values('08', '04', 59);
insert into score values('09', '04', 80);

1.專用視窗函式

1.1序號函式

• row_number() 、 rank() 、 dense_rank()都是序號函式，一個例子說明三者的區別，對每門課程的成績排序：

SELECT s.sname, c.cname, sc.score,
	ROW_NUMBER() OVER (PARTITION BY c.cname
			   ORDER BY sc.score DESC) AS row_num, 
        RANK() OVER (PARTITION BY c.cname
		     ORDER BY sc.score DESC) AS ranking,
        DENSE_RANK() OVER(PARTITION BY c.cname
		          ORDER BY sc.score DESC) AS dense_ranking
FROM student s INNER JOIN score sc ON s.sid = sc.sid
	       INNER JOIN course c ON sc.cid = c.cid	

• row_number 相同成績不會並列，按出現順序排名
• rank 相同成績會並列，且下一名為並列排名+並列人數
• dense_rank 相同成績會並列，並且下一名為並列排名+1

1.2分佈函式

• percent_rank()
  用途：和之前的RANK()函式相關，每行按照如下公式進行計算：

(rank - 1) / (rows - 1)

其中，rank為RANK()函式產生的序號，rows為當前視窗的記錄總行數。

SELECT s.sname, c.cname, sc.score, RANK() OVER(PARTITION BY c.cname ORDER BY sc.score DESC) as ranking,
         PERCENT_RANK() OVER (PARTITION BY c.cname
			      ORDER BY sc.score DESC) as percent																									
FROM student s INNER JOIN score sc ON s.sid = sc.sid
	       INNER JOIN course c ON sc.cid = c.cid

• cume_dist()
  用途：分組內大於等於當前rank值的行數/分組內總行數，這個函式比percen_rank使用場景更多。
  應用場景：某門課程各同學排名在前百分之幾

SELECT s.sname, c.cname, sc.score, RANK() OVER(PARTITION BY c.cname ORDER BY sc.score DESC) as ranking,
       CUME_DIST() OVER (PARTITION BY c.cname
                         ORDER BY sc.score DESC) as cumdist																									
FROM student s INNER JOIN score sc ON s.sid = sc.sid
	       INNER JOIN course c ON sc.cid = c.cid;

亞千和竇唯的金屬成績並列第一，在班級排名前22.22%

1.3前後函式

lag和lead函式可以在同一次查詢中取出同一欄位的前N行資料（lag）和後N行資料（lead）
語法：

LAG(EXP_STR,OFFSET,DEFVAL)OVER()

LEAD(EXP_STR,OFFSET,DEFVAL)OVER()

EXP_STR：要取的列
OFFSET： 取偏移後的第幾行資料
DEFVAL：沒有符合條件的預設值

應用場景：求每個使用者相鄰兩次瀏覽的時間差；求每個同學相鄰兩門考試的成績差

SELECT s.sname, c.cname, sc.score,
       lead(sc.score,1) OVER (PARTITION BY s.sname
                              ORDER BY sc.score DESC) as leadVal,
       lag(sc.score,1) OVER (PARTITION BY s.sname
                             ORDER BY sc.score DESC) as lagVal,
       score - leadVal as diff1,
       score - lagVal as diff2
FROM student s INNER JOIN score sc ON s.sid = sc.sid
	       INNER JOIN course c ON sc.cid = c.cid;

1.4頭尾函式

• first_val()/last_val()
  用途：得到分割槽中的第一個/最後一個指定引數的值。

SELECT s.sname, c.cname, sc.score,
       FIRST_VALUE(sc.score) OVER (PARTITION BY s.sname
                                   ORDER BY sc.score DESC) as firstVal,
       LAST_VALUE(sc.score) OVER (PARTITION BY s.sname
                                  ORDER BY sc.score DESC) as lastVal
FROM student s INNER JOIN score sc ON s.sid = sc.sid
	       INNER JOIN course c ON sc.cid = c.cid

1.5其他函式

• nth_value()

SELECT s.sname, s.sclass, c.cname, sc.score,
       nth_value(sc.score,1) OVER (PARTITION BY s.sname
                                   ORDER BY sc.score DESC) as 1th,
       nth_value(sc.score,2) OVER (PARTITION BY s.sname
                                   ORDER BY sc.score DESC) as 2th
FROM student s INNER JOIN score sc ON s.sid = sc.sid
               INNER JOIN course c ON sc.cid = c.cid

• nfile()
  • 用途：將分割槽中的有序資料分為n個桶，記錄桶號。
  • 此函式在資料分析中應用較多，比如由於資料量大，需要將資料平均分配到N個並行的程式分別計算，此時就可以用NFILE(N)對資料進行分組，由於記錄數不一定被N整除，所以資料不一定完全平均，多出來的部分則依次加給第一組、第二組···直到分配完。