總第145篇/張俊紅

01|什么是窗口函數

我們都知道 SQL 中的聚合函數，聚合函數顧名思義就是聚集合并的意思，是對某個范圍內的數值進行聚合，聚合后的結果是一個值或是各個類別對應的值。如下所示：

直接聚合得到的結果是所有店鋪在這段時間內的所有銷量之和，分組聚合(group by)得到的結果是每個店鋪在這段時間內的銷量之和。

這種聚合函數得到的數據行數是小于基礎數據行數的，但是我們經常會有這樣的需求，就是既希望看基礎數據同時也希望查看聚合后的數據，這個時候聚合函數就滿足不了我們了，窗口函數就派上用場了。窗口函數就是既可以顯示原始基礎數據也可以顯示聚合數據。可能你還是不太理解，沒關系，我也剛開始不太理解，我們看幾個關于窗口函數的具體的應用就理解了。

02|聚合函數+over()

現在有如下的一張表 t 存儲了每個店鋪每天的銷量:

shopname??sales??date?????? A?????????1??????2018/4/1?? B?????????3??????2018/4/1?? C?????????5??????2018/4/1?? A?????????7??????2018/4/2?? B?????????9??????2018/4/2?? C?????????2??????2018/4/2?? A?????????4??????2018/4/3?? B?????????6??????2018/4/3?? C?????????8??????2018/4/3?

如果我們想看一下每個店鋪每天的銷量和一段時間內所有店鋪銷量的平均值的話該怎么做呢？答案就是可以用窗口函數來實現。只需要除了基礎的查詢代碼以外，還需要在你要聚合的字段后面加一個 over()，語句如下所示：

selectshopname,sales,date,avg(sales)?over() fromt

最后結果如下所示：

shopname??sales??date??????avg_window_0 A?????????1??????2018/4/1??5 B?????????3??????2018/4/1??5 C?????????5??????2018/4/1??5 A?????????7??????2018/4/2??5 B?????????9??????2018/4/2??5 C?????????2??????2018/4/2??5 A?????????4??????2018/4/3??5 B?????????6??????2018/4/3??5 C?????????8??????2018/4/3??5

02|partition by子句

再想象一下，上面我們是拿每個店鋪每天的銷量和全部店鋪全部天數的平均銷量作比較，如果我們現在想讓每個店鋪每天的銷量與自身全部天數的平均值作比較，該怎么實現呢？答案就是使用 partition by ,partition by的作用和 group by 是類似的，是進行分組聚合的,需要與 over() 搭配使用。

selectshopname,sales,date,avg(sales)?over(partition?by?shopname) fromt

最后結果如下所示：

shopname??sales??date??????avg_window_0 A?????????1??????2018/4/1??4 B?????????3??????2018/4/1??6 C?????????5??????2018/4/1??5 A?????????7??????2018/4/2??4 B?????????9??????2018/4/2??6 C?????????2??????2018/4/2??5 A?????????4??????2018/4/3??4 B?????????6??????2018/4/3??6 C?????????8??????2018/4/3??5

03|order by子句

order by 就是按照某一列數值進行排序，主要與接下來的序列函數結合使用，當 order by 與聚合函數一起使用時，是順序聚合的。什么叫順序聚合呢？給大家舉一個求和的聚合與 order by 結合使用的例子，就是類似于累計和的效果，具體代碼如下：

selectshopname,sales,date,sum(sales)?over(partition?by?shopname?order?by?date) fromt

最后運行結果如下：

shopname??sales??date??????sum_window_0 A?????????1??????2018/4/1??1 A?????????7??????2018/4/2??8 A?????????4??????2018/4/3??12 B?????????3??????2018/4/1??3 B?????????9??????2018/4/2??12 B?????????6??????2018/4/3??18 C?????????5??????2018/4/1??5 C?????????2??????2018/4/2??7 C?????????8??????2018/4/3??15

當 order by 與序列函數一起使用時就是用于排序。

04|序列函數

什么是序列函數，就是可以將數據整理成一個有序的序列，然后我們可以在這個序列里面挑選我們想要的序列對應的數據。

4.1 ntile

ntile 函數主要是用于將整表數據進行切片分組，默認是對表在不做任何操作之前進行切片分組的，比如現在整個表有9行數據，你要切片分成3組，那么就是第 1-3 行為一組，4-6 行為一組，7-9 行為一組。我們將店鋪銷量表切分成3組，代碼如下：

selectshopname,date,sales,ntile(3)?over() fromt

最后結果如下：

shopname??sales??date??????ntile_window_0 A?????????1??????2018/4/1??1 B?????????3??????2018/4/1??1 C?????????5??????2018/4/1??1 A?????????7??????2018/4/2??2 B?????????9??????2018/4/2??2 C?????????2??????2018/4/2??2 A?????????4??????2018/4/3??3 B?????????6??????2018/4/3??3 C?????????8??????2018/4/3??3

上面是把銷量表切分成3組了，但是對我們實際應用中沒什么實際作用啊，你想一下，你拿一個亂序分組有什么用？如果我們和 order by結合使用就有用了，比如我先按照 sales 升序排列，然后再進行切片分組，這個時候的切片就有意義了。也可以在分組內(partition by)近行切片分組，示例如下：

selectshopname,date,sales,ntile(3)?over(partition?by?shopname?order?by?sales) fromt

最后結果如下：

shopname??sales??date??????ntile_window_0 A?????????1??????2018/4/1??1 A?????????4??????2018/4/3??2 A?????????7??????2018/4/2??3 B?????????3??????2018/4/1??1 B?????????6??????2018/4/3??2 B?????????9??????2018/4/2??3 C?????????2??????2018/4/2??1 C?????????5??????2018/4/1??2 C?????????8??????2018/4/3??3

4.2 row_number

row_number() 從 1 開始，按照順序(注意這里是順序不是排序)生成該條數據在分組內的對應的序列數,row_number() 的值不會存在重復,當排序的值相同時,按照表中記錄的順序進行排列。

因為 row_number() 是按照順序生成對應的序列，而不是按照排序來生成序列的，所以 row_number() 一般需要與 order by 進行結合使用。

你現在想看一下，在一段時間內每個店鋪 sales 對應最早的一次 date 是什么時候？該怎么看呢？這個時候就可用 row_number() 與 order by 相結合，代碼如下：

selectshopname,date,sales,row_number()?over(partition?by?shopname?order?by?date) fromt

因為我們是要查看每個店鋪最早的一次 date，所以需要對 date 進行升序排列。最后結果如下：

shopname??sales??date??????row_number_window_0 A?????????1??????2018/4/1??1 A?????????7??????2018/4/2??2 A?????????4??????2018/4/3??3 B?????????3??????2018/4/1??1 B?????????9??????2018/4/2??2 B?????????6??????2018/4/3??3 C?????????5??????2018/4/1??1 C?????????2??????2018/4/2??2 C?????????8??????2018/4/3??3

我們只需要把 num = 1 的部分取出來就是我們想要的結果。

4.3 lag和lead

lag 的英文意思是滯后，而 lead 的英文意思是超前。對應的 lag 是讓數據向后移動，而 lead 是讓數據向前移動。你可能不太理解，無所謂，直接來看實例。

現在你想看一下每個店鋪這一次和上一次 date 的時間差，你該怎么看呢？可以借助 lag，代碼如下：

selectshopname,date,sales,lag(date,1)?over(partition?by?shopname?order?by?date) fromt

最后結果如下：

shopname??sales??date??????lag_window_0 A?????????1??????2018/4/1??NULL A?????????7??????2018/4/2??2018/4/1 A?????????4??????2018/4/3??2018/4/2 B?????????3??????2018/4/1??NULL B?????????9??????2018/4/2??2018/4/1 B?????????6??????2018/4/3??2018/4/2 C?????????5??????2018/4/1??NULL C?????????2??????2018/4/2??2018/4/1 C?????????8??????2018/4/3??2018/4/2

現在你想看一下每個店鋪這次和下一次 date 之間的時間差，你又該怎么看呢？可以借助 lead，代碼如下：

selectshopname,date,sales,lead(date,1)?over(partition?by?shopname?order?by?date) fromt

最后結果如下：

shopname??sales??date??????lead_window_0 A?????????1??????2018/4/1??2018/4/2 A?????????7??????2018/4/2??2018/4/3 A?????????4??????2018/4/3??NULL B?????????3??????2018/4/1??2018/4/2 B?????????9??????2018/4/2??2018/4/3 B?????????6??????2018/4/3??NULL C?????????5??????2018/4/1??2018/4/2 C?????????2??????2018/4/2??2018/4/1 C?????????8??????2018/4/3??2018/4/3

4.4 first_value和last_value

first_value 和 last_value 都是顧名思義，就是獲取第一個值和最后一個值。但是不是真正意義上的第一個或最后一個，而是截至到當前行的第一個或最后一個。

現在你想看一下每個店鋪的首次 date 和最后一次 date，你會怎么看呢？就可以直接借助first_value 和 last_value，代碼如下：

selectshopname,date,sales,first_value(date)?over(partition?by?shopname?order?by?date),last_value(date)?over(partition?by?shopname?order?by?date) fromt

最后結果如下：

shopname??sales??date??????first_value_window_0???last_value_window_0 A?????????1??????2018/4/1??2018/4/1???????????????2018/4/1 A?????????7??????2018/4/2??2018/4/1???????????????2018/4/2 A?????????4??????2018/4/3??2018/4/1???????????????2018/4/3 B?????????3??????2018/4/1??2018/4/1???????????????2018/4/1 B?????????9??????2018/4/2??2018/4/1???????????????2018/4/2 B?????????6??????2018/4/3??2018/4/1???????????????2018/4/3 C?????????5??????2018/4/1??2018/4/1???????????????2018/4/1 C?????????2??????2018/4/2??2018/4/1???????????????2018/4/2 C?????????8??????2018/4/3??2018/4/1???????????????2018/4/3

---

上次在公眾號活動，隨機選取3名讀者送我簽名書一本，獲獎者為：幻滅、順勢而為、為人民服務。請這三位讀者添加我微信 YHTZJH 發我地址。

總結

以上是生活随笔為你收集整理的讲讲你不知道的窗口函数的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。