面試時，你應該知道的5個 SQL 窗口函數(shù)
關(guān)注"Python學習與數(shù)據(jù)挖掘"，
設(shè)為“置頂或星標”，第一時間送達干貨
SQL是數(shù)據(jù)世界中的通用語言，是數(shù)據(jù)從業(yè)人員最重要的技能之一。許多數(shù)據(jù)探索、數(shù)據(jù)操作、管道開發(fā)和儀表板創(chuàng)建都可以通過 SQL 完成的。
偉大的數(shù)據(jù)科學家與優(yōu)秀的數(shù)據(jù)科學家的區(qū)別在于，偉大的數(shù)據(jù)科學家可以盡可能多地利用 SQL 的功能來處理數(shù)據(jù)。要充分利用 SQL 提供的所有功能，很大一部分是要知道如何使用窗口函數(shù)。
1、帶有 LEAD() 和 LAG() 的增量
LEAD()和 LAG()主要用于將某個時間段與給定指標的前一個時間段進行比較，比如：
獲得每年銷售額與上一年銷售額之間的差值 獲得每月注冊/轉(zhuǎn)換/網(wǎng)站訪問次數(shù)的增量 按月比較用戶流失率
以如何查詢成本的每月百分比變化為例
with?monthly_costs?as?(
????SELECT
????????date
??????,?monthlycosts
??????,?LEAD(monthlycosts)?OVER?(ORDER?BY?date)?as
????????previousCosts
????FROM
????????costs
)SELECT
????date
??,?(monthlycosts?-?previousCosts)?/?previousCosts?*?100?AS
????costPercentChange
FROM?monthly_costs
2、使用 SUM() 或 COUNT() 求和
以 SUM() 或 COUNT() 開頭的窗口函數(shù)簡單地計算運行總計。當你想要顯示特定指標隨時間的增長時，這是一個必備的工具，它在以下情況下很有用：
獲得一段時間內(nèi)的總收入和成本 獲取每個用戶在應用程序上花費的總時間 獲取一段時間內(nèi)的總轉(zhuǎn)化次數(shù)
以如何包含每月費用的累積總和列：
SELECT
????date
??,?monthlycosts
??,?SUM(monthlycosts)?OVER?(ORDER?BY?date)?as?cumCosts
FROM
????cost_table
3、使用 AVG() 的移動平均線
AVG() 在窗口函數(shù)中非常強大，因為它可以計算一段時間內(nèi)的移動平均值。移動平均線是一種簡單但有效的短期預測值的方法。比如
獲取每周銷售額的總體趨勢 獲取每周轉(zhuǎn)換或網(wǎng)站訪問的總體趨勢。例子：
以查詢是獲取轉(zhuǎn)化次數(shù)的 10 天移動平均值的示例
SELECT
????Date
??,?dailyConversions
??,?AVG(dailyConversions)?OVER?(ORDER?BY?Date?ROWS?10?PRECEDING)?AS
????10_dayMovingAverage
FROM
????conversions
4、ROW_NUMBER()
當想要獲取第一條或最后一條記錄時，ROW_NUMBER() 特別有用。
如何使用 ROW_NUMBER() 獲取每個用戶訪問的第一個日期。
with?numbered_visits?as?(
????SELECT
????????memberId
??????,?visitDate
??????,?ROW_NUMBER()?OVER?(PARTITION?BY?customerId?ORDER?BY
????????purchaseDate)?as?rowNumber
????FROM
????????gym_visits
)SELECT
????*
FROM
????numbered_visits
WHERE?
????rowNumber?=?1
5、使用 DENSE_RANK() 進行記錄排名
DENSE_RANK() 與 ROW_NUMBER() 類似，只是它為相等的值返回相同的排名。例如：
想拉出本周觀看次數(shù)最多的 10 部 Netflix 節(jié)目 想根據(jù)花費的金額獲得前 100 名用戶 想查看 1000 個最不活躍用戶的行為 例子：
如果你想按總銷售額對頂級客戶進行排名，則 DENSE_RANK() 將是一個合適的函數(shù)
SELECT
????customerId
??,?totalSales
??,?DENSE_RANK()?OVER?(ORDER?BY?totalSales?DESC)?as?rank
FROM
????customers
總結(jié)
如果你對這5個概念了如指掌，那么當涉及到大多數(shù)SQL窗口函數(shù)問題時，你會做得很好。有所收獲，點贊支持。
長按或掃描下方二維碼，后臺回復：加群，即可申請入群。一定要備注：來源+研究方向+學校/公司，否則不拉入群中，見諒！
（長按三秒，進入后臺）
推薦閱讀
