很可惜 T 。T 您現(xiàn)在還不是作者身份，不能自主發(fā)稿哦～
如有投稿需求，請把文章發(fā)送到郵箱tougao@appcpx.com，一經(jīng)錄用會有專人和您聯(lián)系
咨詢如何成為春羽作者請聯(lián)系：鳥哥筆記小羽毛（ngbjxym）
這是我的第30篇原創(chuàng)
其實很討厭有人問窗口函數(shù)，因為窗口函數(shù)解決的是我在剛開始工作時遇到的問題。因為是BI工程師出身，從業(yè)開始就在做各種排名、同比、環(huán)比、帕累托占比、當前最大等各種亂七八糟的表格需求。
什么是窗口函數(shù)？網(wǎng)上很多數(shù)據(jù)都寫的亂七八糟，搞得好像你越看不懂就顯得他越厲害一樣。來吧，保證你只要會select，就能看的懂這篇文章！
OK，Let's GO！
一句話解釋：
窗口是什么？窗口就是在完整的房子邊上開一個大洞，用來通風、觀察。
窗口函數(shù)是什么？窗口函數(shù)就是在完整的表上開一個大洞，在大洞中再開若干小洞，用來觀察、計算表中的數(shù)據(jù)的一類函數(shù)。
窗口函數(shù)又叫OLAP（OnlineAnalytical Processing）函數(shù)，其實就是用來畫表格的函數(shù)。特別擅長處理各種分組排序、同比環(huán)比、累計占比、TOP等復雜分析操作。
你現(xiàn)在收到一個人事部門的需求：統(tǒng)計每個業(yè)務員的每個月的業(yè)績，包括當月業(yè)績、當年最大單月業(yè)績、當年累計業(yè)績。簡單設計了一張表，表頭如下：
你迅速整理了思路，準備這么做：
1、先把每個業(yè)務員每個月的業(yè)績統(tǒng)計出來，存成中間表A表
2、然后根據(jù)中間表，統(tǒng)計出當月業(yè)績、當年累計業(yè)績、當年最大單月業(yè)績。
但是你在做的時候發(fā)現(xiàn)，當年累計和當年最大單月業(yè)績，每個月的計算范圍不一樣，截止時間應該是1月到當前月份。所以你想了兩種方法：
寫一個簡單腳本，每次都max和sum，但是執(zhí)行12遍（月份為參數(shù)），當月的數(shù)據(jù)用join、子查詢或者其他方式并進去。
select 月份，業(yè)務員, max（當月業(yè)績），sum（當月業(yè)績） from a where a.月份<=‘2月’
寫一個比較復雜的腳本，用中間表A表join自己，然后join的時候設置月份要小于當前月份，但是得捋很多遍的邏輯，一不小心就會出錯。代碼我寫了一下，頭大
，如果寫錯了，私信我，給你發(fā)紅包~~~
select aa.b業(yè)務員, aa.b月份, aa.b當月業(yè)績, sum(aa.a當月業(yè)績) as 當年累計業(yè)績, max(aa.a當月業(yè)績) as 當年最大單月業(yè)績 from
(select a.業(yè)務員 as a業(yè)務員, a.月份 as a月份, a.當月業(yè)績 as a當月業(yè)績,b.業(yè)務員 as b業(yè)務員, b.月份 as b月份, b.當月業(yè)績 as b當月業(yè)績
from 中間表 A join 中間表 B on a.業(yè)務員 = b.業(yè)務員) aa
where aa.a月份 <= aa.b月份 group by aa.b業(yè)務員, aa.b月份, aa.b當月業(yè)績 order by aa.b業(yè)務員, aa.b月份;
你們知道我想說什么。上述的sql都是通過聚合函數(shù)（avg、sum、max、min等）+子查詢+join來實現(xiàn)的。
實際上大部分數(shù)據(jù)庫除了聚合函數(shù)之外，還提供了窗口函數(shù)、分析函數(shù)、排序函數(shù)等各種函數(shù)。這些函數(shù)結合起來非常好用。
剛才的需求為例，實際上這個需求理解起來非常簡單，就是按照每個業(yè)務員進行分組，然后在每個分組中，取每個月份的當前值、最大值和累計值。
那么我們只需要一個sql就能搞定三個指標的計算：
select 業(yè)務員,月份,商品金額,
sum(商品金額) over (partition by 業(yè)務員 order by 月份 asc rows between unbounded preceding and current row) as 當年累計業(yè)績,
max(商品金額) over (partition by 業(yè)務員 order by 月份 asc rows between unbounded preceding and current row) as 當年最大單月業(yè)績
from 原始業(yè)績表
解釋一下：
partition就是對每個業(yè)務員的數(shù)據(jù)單獨分組，然后進行內部計算；
order by就是你計算的時候是按什么順序進行計算的；
rows between ... and 就是在計算的時候，窗口的區(qū)域是從哪里到哪里
unbounded preceding就是當前分組第一行，
current row就是當前行
組合起來就是：
sum(商品金額) over (partition by 業(yè)務員 order by 月份 asc rows between unbounded preceding and current row) as 當年累計業(yè)績,
按照業(yè)務員進行分組，月份從小到大排序之后，累計賈總第一個月到當前月的商品金額。翻譯成人話就是計算每個業(yè)務員當年累計業(yè)績。over。
這就是窗口函數(shù)的作用。每個業(yè)務員的每份數(shù)據(jù)都開一個窗口，在這個窗**單獨計算。
partition就是以業(yè)務員進行開窗（分區(qū)），between 和 and就是你在計算這個數(shù)值的時候，是否有區(qū)域條件（窗**的窗口），and 之前的“unbounded preceding”就是第一條數(shù)據(jù)開始，and 之后的“current row”就是當前這條數(shù)據(jù)。
同樣，你可以一條語句算出帕累托值（累計占比），不用加輔助字段，不用中間表，不用給自己關聯(lián)自己，一個窗口函數(shù)就搞定。下面兩個語句的結果一除就好，語句太長，所以只是計算出來兩個字段，沒有除出結果。
sum(商品金額) over (partition by 業(yè)務員 order by 月份 asc rows between unbounded preceding and current row) as 當年累計到當月業(yè)績,
sum(商品金額) over (partition by 業(yè)務員 order by 月份 asc rows between unbounded preceding and unbounded following) as 全年累計業(yè)績,
同樣，同比環(huán)比也都可以用類似函數(shù)直接搞定。怎么樣？厲害吧？
窗口函數(shù)基本語法：
操作函數(shù)（窗口、聚合、排序等）+窗口函數(shù)基本內容【 over + partition分區(qū)+排序+窗口內區(qū)域】
窗口操作函數(shù)：
1.LEAD(字段,位移數(shù),默認值) ：向下位移N行取值
2.LAG(字段,位移數(shù),默認值) ：向上位移N行取值
3.FIRST_VALUE：當前分組第一個值
4.LAST_VALUE：當前分組最后一個值
示例：
,lead(當月業(yè)績,1,-9999) over (partition by 業(yè)務員 order by 月份) as 下月業(yè)績
,lag(當月業(yè)績,1,-9999) over (partition by 業(yè)務員 order by 月份) as 上月業(yè)績
窗口函數(shù)基本內容：
over (partition by 字段 order by 字段 asc rows between ** and** )
聚合函數(shù)
這個就不用解釋了吧？聚合就是“多行變一行”的操作。有：
sum、max、min、avg、count
與窗口函數(shù)一起用的示例其實上面已經(jīng)寫了幾個了，這里就不再復述了
排序函數(shù)（分析函數(shù)）
1.ROW_NUMBER：從1開始，按照順序編號
2.RANK：生成排名，相同得分排名相同，并留空位。
3.DENSE_RANK：生成排名，相同得分排名相同，并不留空位。
4.CUME_DIST：小于等于當前值的行數(shù)/分組內總行數(shù)
5.PERCENT_RANK：分組內當前排名占總排名的百分比
6.NTILE：分桶，將分組內的數(shù)據(jù)均勻分N桶
示例：
,ROW_NUMBER() over (partition by 業(yè)務員 order by 當月業(yè)績) as ROW_NUMBER1
,RANK() over (partition by 業(yè)務員 order by 當月業(yè)績) as RANK1
,DENSE_RANK() over (partition by 業(yè)務員 order by 當月業(yè)績) as DENSE_RANK1
,CUME_DIST() over (partition by 業(yè)務員 order by 當月業(yè)績) as CUME_DIST1
這些在計算績效的時候非常好用，尤其是業(yè)務員的業(yè)績計算，其中有大量排名，業(yè)務員之間全國排名、大區(qū)排名、省級排名什么的，應用場景非常多。
至于窗口內排名占比和分桶，比較少用，可以存著備用。
寫累了...洗洗睡了，希望能幫到你，晚安，好夢~~~
本文為作者獨立觀點，不代表鳥哥筆記立場，未經(jīng)允許不得轉載。
《鳥哥筆記版權及免責申明》 如對文章、圖片、字體等版權有疑問，請點擊 反饋舉報
我們致力于提供一個高質量內容的交流平臺。為落實國家互聯(lián)網(wǎng)信息辦公室“依法管網(wǎng)、依法辦網(wǎng)、依法上網(wǎng)”的要求，為完善跟帖評論自律管理，為了保護用戶創(chuàng)造的內容、維護開放、真實、專業(yè)的平臺氛圍，我們團隊將依據(jù)本公約中的條款對注冊用戶和發(fā)布在本平臺的內容進行管理。平臺鼓勵用戶創(chuàng)作、發(fā)布優(yōu)質內容，同時也將采取必要措施管理違法、侵權或有其他不良影響的網(wǎng)絡信息。
一、根據(jù)《網(wǎng)絡信息內容生態(tài)治理規(guī)定》《中華人民共和國未成年人保護法》等法律法規(guī)，對以下違法、不良信息或存在危害的行為進行處理。
1. 違反法律法規(guī)的信息，主要表現(xiàn)為：
1)反對憲法所確定的基本原則；
2)危害國家安全，泄露國家秘密，顛覆國家政權，破壞國家統(tǒng)一，損害國家榮譽和利益；
3)侮辱、濫用英烈形象，歪曲、丑化、褻瀆、否定英雄烈士事跡和精神，以侮辱、誹謗或者其他方式侵害英雄烈士的姓名、肖像、名譽、榮譽；
4)宣揚恐怖主義、極端主義或者煽動實施恐怖活動、極端主義活動；
5)煽動民族仇恨、民族歧視，破壞民族團結；
6)破壞國家宗教政策，宣揚邪教和封建迷信；
7)散布謠言，擾亂社會秩序，破壞社會穩(wěn)定；
8)宣揚淫穢、色情、賭博、暴力、兇殺、恐怖或者教唆犯罪；
9)煽動非法集會、結社、游行、示威、聚眾擾亂社會秩序；
10)侮辱或者誹謗他人，侵害他人名譽、隱私和其他合法權益；
11)通過網(wǎng)絡以文字、圖片、音視頻等形式，對未成年人實施侮辱、誹謗、威脅或者惡意損害未成年人形象進行網(wǎng)絡欺凌的；
12)危害未成年人身心健康的；
13)含有法律、行政法規(guī)禁止的其他內容；
2. 不友善：不尊重用戶及其所貢獻內容的信息或行為。主要表現(xiàn)為：
1)輕蔑：貶低、輕視他人及其勞動成果；
2)誹謗：捏造、散布虛假事實，損害他人名譽；
3)嘲諷：以比喻、夸張、侮辱性的手法對他人或其行為進行揭露或描述，以此來激怒他人；
4)挑釁：以不友好的方式激怒他人，意圖使對方對自己的言論作出回應，蓄意制造事端；
5)羞辱：貶低他人的能力、行為、生理或身份特征，讓對方難堪；
6)謾罵：以不文明的語言對他人進行負面評價；
7)歧視：煽動人群歧視、地域歧視等，針對他人的民族、種族、宗教、性取向、性別、年齡、地域、生理特征等身份或者歸類的攻擊；
8)威脅：許諾以不良的后果來迫使他人服從自己的意志；
3. 發(fā)布垃圾廣告信息：以推廣曝光為目的，發(fā)布影響用戶體驗、擾亂本網(wǎng)站秩序的內容，或進行相關行為。主要表現(xiàn)為：
1)多次發(fā)布包含售賣產品、提供服務、宣傳推廣內容的垃圾廣告。包括但不限于以下幾種形式：
2)單個帳號多次發(fā)布包含垃圾廣告的內容；
3)多個廣告帳號互相配合發(fā)布、傳播包含垃圾廣告的內容；
4)多次發(fā)布包含欺騙性外鏈的內容，如未注明的淘寶客鏈接、跳轉網(wǎng)站等，誘騙用戶點擊鏈接
5)發(fā)布大量包含推廣鏈接、產品、品牌等內容獲取搜索引擎中的不正當曝光；
6)購買或出售帳號之間虛假地互動，發(fā)布干擾網(wǎng)站秩序的推廣內容及相關交易。
7)發(fā)布包含欺騙性的惡意營銷內容，如通過偽造經(jīng)歷、冒充他人等方式進行惡意營銷；
8)使用特殊符號、圖片等方式規(guī)避垃圾廣告內容審核的廣告內容。
4. 色情低俗信息，主要表現(xiàn)為：
1)包含自己或他人性經(jīng)驗的細節(jié)描述或露骨的感受描述；
2)涉及色情段子、兩性笑話的低俗內容；
3)配圖、頭圖中包含庸俗或挑逗性圖片的內容；
4)帶有性暗示、性挑逗等易使人產生性聯(lián)想；
5)展現(xiàn)血腥、驚悚、殘忍等致人身心不適；
6)炒作緋聞、丑聞、劣跡等；
7)宣揚低俗、庸俗、媚俗內容。
5. 不實信息，主要表現(xiàn)為：
1)可能存在事實性錯誤或者造謠等內容；
2)存在事實夸大、偽造虛假經(jīng)歷等誤導他人的內容；
3)偽造身份、冒充他人，通過頭像、用戶名等個人信息暗示自己具有特定身份，或與特定機構或個人存在關聯(lián)。
6. 傳播封建迷信，主要表現(xiàn)為：
1)找人算命、測字、占卜、解夢、化解厄運、使用迷信方式治??；
2)求推薦算命看相大師；
3)針對具體風水等問題進行求助或咨詢；
4)問自己或他人的八字、六爻、星盤、手相、面相、五行缺失，包括通過占卜方法問婚姻、前程、運勢，東西寵物丟了能不能找回、取名改名等；
7. 文章標題黨，主要表現(xiàn)為：
1)以各種夸張、獵奇、不合常理的表現(xiàn)手法等行為來誘導用戶；
2)內容與標題之間存在嚴重不實或者原意扭曲；
3)使用夸張標題，內容與標題嚴重不符的。
8.「飯圈」亂象行為，主要表現(xiàn)為：
1)誘導未成年人應援集資、高額消費、投票打榜
2)粉絲互撕謾罵、拉踩引戰(zhàn)、造謠攻擊、人肉搜索、侵犯隱私
3)鼓動「飯圈」粉絲攀比炫富、奢靡享樂等行為
4)以號召粉絲、雇用網(wǎng)絡水軍、「養(yǎng)號」形式刷量控評等行為
5)通過「蹭熱點」、制造話題等形式干擾輿論，影響傳播秩序
9. 其他危害行為或內容，主要表現(xiàn)為：
1)可能引發(fā)未成年人模仿不安全行為和違反社會公德行為、誘導未成年人不良嗜好影響未成年人身心健康的；
2)不當評述自然災害、重大事故等災難的；
3)美化、粉飾侵略戰(zhàn)爭行為的；
4)法律、行政法規(guī)禁止，或可能對網(wǎng)絡生態(tài)造成不良影響的其他內容。
二、違規(guī)處罰
本網(wǎng)站通過主動發(fā)現(xiàn)和接受用戶舉報兩種方式收集違規(guī)行為信息。所有有意的降低內容質量、傷害平臺氛圍及欺凌未成年人或危害未成年人身心健康的行為都是不能容忍的。
當一個用戶發(fā)布違規(guī)內容時，本網(wǎng)站將依據(jù)相關用戶違規(guī)情節(jié)嚴重程度，對帳號進行禁言 1 天、7 天、15 天直至永久禁言或封停賬號的處罰。當涉及欺凌未成年人、危害未成年人身心健康、通過作弊手段注冊、使用帳號，或者濫用多個帳號發(fā)布違規(guī)內容時，本網(wǎng)站將加重處罰。
三、申訴
隨著平臺管理經(jīng)驗的不斷豐富，本網(wǎng)站出于維護本網(wǎng)站氛圍和秩序的目的，將不斷完善本公約。
如果本網(wǎng)站用戶對本網(wǎng)站基于本公約規(guī)定做出的處理有異議，可以通過「建議反饋」功能向本網(wǎng)站進行反饋。
(規(guī)則的最終解釋權歸屬本網(wǎng)站所有)