一，開(kāi)窗函數(shù)的語(yǔ)法

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

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

實(shí)不相瞞我看一眼就會(huì)了（假的，其實(shí)這種又臭又長(zhǎng)的字實(shí)在懶得看）

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

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

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

執(zhí)行語(yǔ)句：

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

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

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

first_value：是在窗口里面取到第一個(gè)值

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

last_value：是在窗口里面取到最后一個(gè)值

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

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行，，如果沒(méi)有就為默認(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行，如果沒(méi)有就為默認(rèn)值0

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

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

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

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

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

DENSE_RANK（）和RANK（）類似，不同的是如果有相同的序號(hào)，那么接下來(lái)的序號(hào)不會(huì)間斷。例如：有三個(gè)人并列第一，第四名序號(hào)為2（111234）

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

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

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

最新評(píng)論