一，開窗函數(shù)的語法

開窗函數(shù)的語法為：over(partition by 列名1 order by 列名2 )，括號中的兩個關(guān)鍵詞partition by 和order by 可以只出現(xiàn)一個。over() 前面是一個函數(shù)，如果是聚合函數(shù)，那么order by 不能一起使用。

二，從聚合開窗函數(shù)sum(score) over(partition by name )講起

實不相瞞我看一眼就會了（假的，其實這種又臭又長的字實在懶得看）

sum(score) over(partition by name )
sum()是聚合函數(shù)，其實我聚合函數(shù)還沒學(xué)明白，當(dāng) sum()函數(shù) 后面跟上 over()以后，由sum聚合函數(shù)就成為了開窗函數(shù)。
over() 括號里面就是定義窗口的內(nèi)容了，partition 是分區(qū),分組的意思。partition by 就是根據(jù)某個字段分組。
所以sum(score) over(partition by name ) ，就是先根據(jù) name 分組(如圖)，當(dāng)前面加了sum（score）后就把根據(jù)name分組后的，每個（組）窗口里面的字段 score進(jìn)行求和操作。

select *,sum(score)  over(partition by name) sum窗口函數(shù)舉例
from kchs
-- 為了簡單就只有兩個字段，name和score

聚合函數(shù)同樣需要對數(shù)據(jù)進(jìn)行排序，但不會顯示排名結(jié)果。會將當(dāng)前名次的數(shù)據(jù) 與 排在這之前的所有數(shù)據(jù) 依次做相應(yīng)的計算。

執(zhí)行語句：

select *,
sum(score) over (order by id) as 累加求和
from kchs

拓展一下：
一，很多聚合函數(shù)都可以用作窗口函數(shù)的運算，如SUM、AVG、MAX、MIN、COUNT。
二，和gropu by 不同的是窗口函數(shù)會生成多行，而不是想group by 一樣只有一行

三，開窗函數(shù)之first_value，last_value，lead，lag

first_value：是在窗口里面取到第一個值

first_value(score) over( partition by name)as first_score ,
根據(jù)name分區(qū)（組），取score列的第一個值

last_value：是在窗口里面取到最后一個值

last_value(score) over(partition by name) as last_score
--根據(jù)name分區(qū)（組），取score列的最后一個值

lead 是取當(dāng)前行的上 N 條數(shù)據(jù)，并且可以設(shè)置默認(rèn)值

lead(score,1,0) over(partition by name ) as lead_score
--根據(jù)name分區(qū)（組），score列當(dāng)前行的上面N行，，如果沒有就為默認(rèn)值0

lag 是取當(dāng)前行的下 N 條數(shù)據(jù)，并且可以設(shè)置默認(rèn)值

lag(score,1,0) over(partition by name ) as lag_score 
--根據(jù)name分區(qū)（組），score列當(dāng)前行的下面N行，如果沒有就為默認(rèn)值0

四，排名開窗函數(shù)ROW_NUMBER、DENSE_RANK、RANK

row_number ()是為每組的行設(shè)置一個連續(xù)的遞增的數(shù)字（123456）

ROW_NUMBER() over( partition by name order by score asc)as ROW_NUMBER_score 

rank()是排名，也為每一組的行生成一個序號,如果有相同的值會生成相同的序號，并且接下來的序號是不連序的。例如：有三個人并列第一名，第四名序號為四（111456）

rank() over(partition by name order by score asc) as RANK_score

DENSE_RANK（）和RANK（）類似，不同的是如果有相同的序號，那么接下來的序號不會間斷。例如：有三個人并列第一，第四名序號為2（111234）

DENSE_RANK() over(partition by name order by score asc) as DENSE_RANK_score

注意：
一，排名開窗函數(shù)可以單獨使用ORDER BY 語句，也可以和PARTITION BY同時使用。
二，ORDER BY 指定排名開窗函數(shù)的順序，在排名開窗函數(shù)中必須使用ORDER BY語句。
三，PARTITION BY用于將結(jié)果集進(jìn)行分組，開窗函數(shù)應(yīng)用于每一組。

到此這篇關(guān)于SQLServer三種開窗函數(shù)詳細(xì)用法的文章就介紹到這了,更多相關(guān)SQLServer 開窗函數(shù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論