導讀
視窗函式是資料庫查詢中的一個經典場景，在解決某些特定問題時甚至是必須的。個人認為，在單純的資料庫查詢語句層面【即不考慮DML、SQL調優、索引等進階】，視窗函式可看作是考察求職者SQL功底的一個重要方面。
前期個人以求職者身份參加面試時被問及視窗函式的問題，近期在作為面試官也提問過這一問題，但回答較為理想者居少。所以本文首先視窗函式進行講解，然後分別從SQL、Pandas和Spark三種工具平臺展開實現。
模擬問題描述：
給定一組中學生的歷次語文月考成績表（每名學生含有4次成績），需要實現以下3個需求：
對每名學生的4次成績表分別進行排序，排序後每人的成績排名1-2-3-4
求每名學生歷次月考成績的變化幅度，即本月較上個月的成績差值
求每名學生歷次月考成績中近3次平均分
資料表樣例如下：
01 視窗函式介紹
在分析上述需求之前，首先對視窗函式進行介紹。何為視窗函式呢？既然視窗函式這個名字源於資料庫，那麼我們就援引其在資料庫中的定義。下圖源於MySQL8。0的官方文件，從標黃高亮的一句介紹可知：視窗函式是用與當前行有關的資料行參與計算。這個翻譯可能有些蹩腳，但若能感性理解視窗函式的話，其實反而會覺得其概括的比較傳神。
MySQL8。0官方手冊中關於視窗函式的介紹
當然，為了形象表達上述定義所言何物，這裡還是進一步給出一些配套插圖以便於理解。在給出具體配圖之前，首先要介紹與視窗函式相關的3個關鍵詞：
partition by：用於對全量資料表進行切分（與SQL中的groupby功能類似，但功能完全不同），直接體現的是前面視窗函式定義中的“有關”，即切分到同一組的即為有關，否則就是無關；
order by：用於指定對partition後各組內的資料進行排序；
rows between：用於對切分後的資料進一步限定“有關”行的數量，此種情景下即使partition後分到一組，也可能是跟當前行的計算無關。
相應的，這3個關鍵字在前面的資料樣表中可作如下配套解釋：
當然，到這裡還不是很理解視窗函式以及相應的3個關鍵字也問題不大，後續結合前述的三個實際需求再返過來看此圖多半會豁然開朗。
上面是視窗函式的邏輯解釋，那麼具體能用於實現什麼功能呢？其實，視窗函式能實現什麼功能則要取決於能搭配什麼函式。仍然引用MySQL8。0官方文件中的一幅圖例：
其中，上表所述的視窗函式主要分為兩大類：
排序類，包括row_number、rank、dense_rank等，也包括percent_rank、cume_dist等分佈排序類
相對引用類，如lag、lead、first_value、last_value、nth_value等
除了這兩類專用視窗函式之外，還有廣義的聚合函式也可配套視窗函式使用，例如sum、avg、max、min等。
所以，現在來看前面提到的三個需求，就剛好是分別應用這三類視窗函式的例子。【哪有什麼剛好，不過是特意設計而已】
圍繞這三個需求，下面分別應用SQL、Pandas和Spark三個工具予以實現。
02 SQL實現
既然視窗函式起源於資料庫，那麼下面就首先應用SQL予以實現。
注：以下所有SQL查詢語句實現均基於MySQL8。0。
Q1：求解每名同學歷次成績的排名。
A1：由於是區分每名同學進行排序，所以需要依據uid欄位進行partition；進一步地，按照成績進行排序，所以order by欄位即為score；最後，由於是要進行排名，所以配套函式選擇row_number即可。注：row_number、rank和dense_rank的具體區別可參考歷史文章：一文解決所有MySQL分類排名問題。
查詢語句及查詢結果如下：
Q2：求解每名同學歷次月考成績的差值，即本月成績-上月成績。
A2：首先，仍然是依據uid欄位進行partition；而後由於是要計算本月成績與上月成績的差值，所以此次的排序依據應該是date；進一步地，由於要計算差值，所以需要對每次月考成績計算其前一行的成績（在按照uid進行切分並按照date排序後，上月成績即為當前行的前一條記錄），所以配套函式即為lag。
給出具體實現SQL語句及查詢結果如下：
Q3：求解每名學生近3次月考成績的平均分。
A3：在前兩個需求的基礎上，易見，仍然是依據uid進行partition、依據date進行排序，並選用avg聚合函式作為配套視窗函式。進一步地，由於此處限定計算近3次成績的平均分，所以除了partition和order by 兩個關鍵字外，還需增加rows between的限定。
具體SQL語句和查詢結果如下：
值得指出的是，對於每名學生，當切分視窗不足指定視窗大小（即目標行數）時會按實際的資料進行聚合，例如學生A，1月31日對應的近3次平均分即為本月成績自身；2月28日對應近3次平均分即為本月成績和上月成績的平均分，而3月31日和4月30日計算的近3次平均分則為真正意義上的3次成績均值。
03 Pandas實現
Pandas作為Python資料分析與處理的主力工具，自然也是支援視窗函式的，而且花樣只會比SQL更多。對於上述三個需求，Pandas分別實現如下：
Q1：求解每名同學歷次成績的排名。
A1：雖然Pandas介面非常豐富，但用其實現分組排名貌似卻並不方便。不過也是可以的。基本思路如下：首先仍然分別用uid和score欄位進行分組和排序，而後透過對取值=1的常數列num進行cumsum，即累加，即可獲取分組排名結果。其中，還可進一步應用assign函式實現鏈式呼叫，最終整個需求實現下來也是一行程式碼即可！
具體Pandas實現程式碼即結果如下：
Q2：求解每名同學歷次月考成績的差值，即本月成績-上月成績。
A2：對於這一特定需求，Pandas中實際上是內建了偏移函式shift，專門用於求解當前行的相對引用值。進一步地，對於求解差分結果，還可直接用diff實現，其中diff就相當於當前行-shift（1）。
兩種API實現程式碼即執行結果分別如下：
Q3：求解每名學生近3次月考成績的平均分。
A3：如果說前兩個需求用Pandas實現都沒有很好體現視窗函式的話，那麼這個需求可能才更貼近Pandas中視窗函式的標準用法——那就是用關鍵字rolling。rolling原義即有滾動的意思，用在這裡即表達滑動視窗的意思，所以自然也就可以設定滑動視窗的大小。至於SQL中視窗函式的另外兩個關鍵字partition和order則仍然需要藉助Pandas的sort_values和gropupby來實現。另外，與SQL中類似，這裡仍然是要用求均值函式來做為配套。
具體Pandas實現程式碼如下：
注：上述實現中用到了reset_index和set_index，其目的是為了保證滑窗聚合後保證順序不變。為了追求單行程式碼實現，這裡的寫法不再優雅，並不提倡。
04 Spark實現
最後，選用Spark予以實現。應該講，Spark。sql元件幾乎是完全對標SQL語法的實現，這在視窗函式中也例外，包括over以及paritionBy、orderBy和rowsbetween等關鍵字的使用上。
注：在使用Spark視窗函式前，首先需要求引入視窗函式類Window。即
Q1：求解每名同學歷次成績的排名。
A1：直接沿用SQL思路即可，需要注意Spark中的相應表達。
程式碼實現及相應執行結果如下：
Q2：求解每名同學歷次月考成績的差值，即本月成績-上月成績。
A2：首先應用lag運算元求出上月成績，而後直接相減即可。
程式碼及執行結果如下：
Q3：求解每名學生近3次月考成績的平均分。
A3：仍然沿用SQL中思路即可，只需增加rowsBetween函式。
程式碼實現及執行結果如下：
05 小節
本文首先對視窗函式進行了介紹，透過模擬設定3個實際需求問題，分別基於SQL、Pandas和Spark三個工具平臺予以分析和實現。總體來看，SQL和Spark實現視窗函式的方式和語法更為接近，而Pandas雖然擁有豐富的API，但對於具體視窗函式功能的實現上卻不盡統一，而需靈活呼叫相應的函式。當然，視窗函式的功能還有很多，三個工具平臺的使用也遠不止這些，但其核心原理則是大體相通的。
Python
秋招資料領取！
給大家分享一份資料分析筆試面試題集合，祝各位還在秋招的小夥伴早日拿下BAT offer。
當然也歡迎各位小夥伴進入
筆試題
，第一時間查收資料分析相關資料！
獲取方式：
1、點選卡片，關注【資料百曉生】公眾號
如果您覺得我們的文章還不錯，請分享，點贊，再看，一鍵三連！！！
END
加入資料萬花筒的交流群，每日分享資料分析筆試面試題
資料分析師必會A/B 試驗設計及其容易忽略的誤區
2021-04-12
Python實戰：利用Uplift模型識別營銷敏感使用者提升轉化率（一）
2021-08-06
Python實戰：利用Uplift模型識別營銷敏感使用者提升市場策略（二）
2021-08-11
Python實戰|利用生存分析預測使用者流失週期（一）
2021-07-12
Python實戰|利用生存分析預測使用者流失週期（二）
2021-07-23