獲取學科最高分

SELECT  DISTINCT name,subject,MAX(score) OVER (PARTITION by subject) as '此學科最高分數' from scores;

獲取學科的報名人數

select DISTINCT subject,count(name) over (partition by subject) as '報名此學科的人數' from scores; 

求學科總分

SELECT DISTINCT subject,SUM(score) over (partition by subject) as '此學科總分' from scores;

求每次累加分數

select score,sum(score) over (order by score) as '累加分數' from scores; 

當前分數+前一個分數+后一個分數

select score,sum(score) over (ORDER BY score rows between 1 preceding and 1 following) as '累加分數' from scores;

ROW_NUMBER() 對成績進行排序（如果兩個分數一樣，那仍然是一個第一，一個第二）

select name,subject,score,ROW_NUMBER() over (order by score DESC) as '排名' from scores;

rank() 排序，并列時下面的排名間隔一個開始排即1，1，3

select name,subject,score,RANK() over(order by score desc) as '排名' from scores;

DENSE_RANK() 并列時不出現跳過排名的情況即1，1，2

select name,subject,score,DENSE_RANK() over(ORDER BY score desc) as '排名' from scores;

NTILE(N) 將結果集劃分為指定數量的組，并為每個組分配一個編號，例如將下列數分成四組

select name,subject,score,NTILE(4) over(order by score desc) as '組' from scores;

LAG(expr[,N[,default]]) 處理時間序列數據或相鄰行的值，它允許檢索前一行的值，并將其與當前行的值進行比較或計算差異三個參數的意義：column 列名、offset 向前的偏移量、 default_value如果向前偏移的行不存在，就取這個默認值 例比較相鄰兩個排名的分數差。

select name,subject,score,abs(score-LAG(score,1,score) over(order by score desc)) as '分值差' from scores; 

LEAD(expr[,N[,default]]) 與LAG() 相反相后偏移。

select name,subject,score,abs(score-LEAD(score,1,score) over(order by score desc)) as '分值差' from scores;