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