盤點一道窗口函數(shù)的數(shù)據(jù)分析面試題
回復(fù)“書籍”即可獲贈Python從入門到進階共10本電子書
今
日
雞
湯
云母屏風(fēng)燭影深，長河漸落曉星沉。大家好，我是熱心讀者。前幾天在群里看到有人問了這樣一道題，我覺得對一些新手了解窗口函數(shù)很有裨益，因此拿出來以饗讀者。
至于為什么要拿窗口函數(shù)來說事兒呢？因為目前的數(shù)分面試，只要考sql，窗口函數(shù)是100%會問的。從另一個側(cè)面來講，窗口函數(shù)是檢驗?zāi)愕腟QL的試金石，一驗一個準，比目前的核酸檢測水平都高。
好了，閑言少敘，我們來解題。
窗口函數(shù)定義
按照分類來講，一類是專門的窗口函數(shù)：row_number(),rank(),dense_rank()等;一類是聚合函數(shù)：sum(),avg()等。
按照功能來講，窗口函數(shù)是在不損失行數(shù)的背景下，按照指定維度進行分組，按照指定維度進行排序的一種排序函數(shù)，聚合等作用的函數(shù)，窗口函數(shù)的熟練程度決定了你SQL的熟練程度，而在面試中是一道必考題，在業(yè)務(wù)實踐中也是一道邁不過去的坎兒。
窗口函數(shù)表達式
這里以row_number()為例，來說明一下表達式的含義，因為萬變不離其宗，對于基礎(chǔ)我們要了然于心。
row_number() over([parition by 維度] order by 維度 asc [desc])
[partition by 維度]? 該部分可以省略，表按照某指定維度進行分組
order by 維度?該部分不允許為空，表按照某維度進行升序(或降序)排序
row_number()函數(shù)是用來分組排序的，排序不重復(fù)，此處大家可以百度一下跟rank和dense_rank排序的區(qū)別。
我們先“由儉入奢”，從最常規(guī)的開始處理。題目被我稍作修改
窗口函數(shù)實戰(zhàn)——基礎(chǔ)版
如圖：
根據(jù)題意我們會發(fā)現(xiàn)，這里是完成的排序是按照id和cat字段分組，按照time字段進行排序，發(fā)現(xiàn)了這個規(guī)律我們就套用窗口函數(shù)的基本語法，即可完成題目的要求，下面是腳本：
#第一步?構(gòu)造數(shù)據(jù)
insert?into??test?values('2020-10-02?12:30:45','A','AAA');
insert?into??test?values('2020-10-02?12:30:55','A','AAA');
insert?into??test?values('2020-10-02?14:39:45','A','BBB');
insert?into??test?values('2020-10-02?14:40:55','A','BBB');
insert?into??test?values('2020-10-02?15:30:05','A','AAA');
insert?into??test?values('2020-10-02?16:30:45','B','AAA');
insert?into??test?values('2020-10-02?17:04:45','B','BBB');
#?腳本
select?
time,
id,
cat,
row_number()?over(partition?by?id,cat?order?by?time?asc)?as?rnk
from?test
order?by?time?asc
#?加入order?by?語句為了讓數(shù)據(jù)展示的更清楚，別無他用
看下效果：
窗口函數(shù)實戰(zhàn)——進階版
具體的題目是這樣的：
具體的題目描述如下：
第一列是事件發(fā)生的時間，第二列是用戶id，第三列是事件分組，第四列是我想要打的排序，如果事件分組和上一個事件不一致，或者用戶id不一致的話，就要重新計數(shù)
根據(jù)圖片和描述的情景，我們發(fā)現(xiàn)跟上一道題有一點點差別，就是數(shù)據(jù)順序已經(jīng)按照時間排好了序，如果id和cat相同，則進行順序排序；如果id和cat不同，則要重新從1進行排序。
思路：
1、要想實現(xiàn)上述效果，也就是需要第三列輔助列跟id和cat一起進行分組，而第三列輔助列應(yīng)該滿足以下：
按照id和cat分組連續(xù)，則第三列輔助列應(yīng)該為相同的值；如果id和cat發(fā)生了改變，則第三列輔助列應(yīng)該隨之改變，并且需要保證第三列輔助列在每個分組內(nèi)的值唯一。
2、如果有了第三列輔助列，那么我們套用窗口函數(shù)公式即可將題意中順序排出來。
以下為腳本：
#?1?構(gòu)造數(shù)據(jù)?以題一中數(shù)據(jù)為例
#?2?腳本?a?構(gòu)造連續(xù)排序
#?備注?原題中時間標記為1?2?3?,而實際中時間肯定為標準的時間戳形式，因此需要通過連續(xù)數(shù)來構(gòu)造
#?加with?temp1?as這個偽腳本為了下面腳本引用方便而已，實際運行中請忽略
with?temp1?as?
select?
time,
id,
category,
concat_ws('-',id,category)?as?add_col?,
row_number()?over(order?by?time?asc)?as?order_rnk
--?按時間順序計算連續(xù)排序，構(gòu)造連續(xù)數(shù)序列
--?此處省略partition?by?說明實際中partition可以省略
from?test
示意如圖：
而我們通過這個連續(xù)數(shù)序列想要判斷哪些數(shù)是連續(xù)的，哪些數(shù)又是不連續(xù)的，接著看腳本
with?temp2?as?
select
time,
id,
category,
add_col,
order_rnk,
order_rnk-lag(order_rnk,1,order_rnk-1)?over(partition?by?add_col?order?by?time?asc)?as?order_rnk_lag1
from?temp1
#?這里需要解釋下幾個知識點：
#?窗口函數(shù)lag可以理解為拖后的意思，即按照指定維度分組，指定維度排序，將某列向下平移n行，空值用第三個參數(shù)默認
#?因而本文的意思就是將order_rnk這個連續(xù)數(shù)序列按照add_col這個輔助列，組內(nèi)向下平移1行，如果是空值，
#?用?order_rnk-1設(shè)置為默認值
#?為什么要這樣處理：因為我們第一步配置的連續(xù)數(shù)序列，相鄰兩行作差，如果差值為1，則連續(xù)，否則說明組內(nèi)出現(xiàn)了不連續(xù)的情況
示意如圖：
通過紅色框，我們可以看出來，我們把同一個add_col內(nèi)的不連續(xù)區(qū)分了開來，此時，我們就可以有“山重水復(fù)疑無路，柳暗花明又一村”的感覺了，我們把add_col 和order_cnk_lag1再作為一個分組的依據(jù)，再排序，題意可解，腳本如下：
with?temp3?as?
select?
time,
id,
catgory,
row_number()?over(parition?by?concat(add_col,order_cnk_lag1)?order?by?time?asc)?as?rnk
from?temp2
以上。
小伙伴們，快快用實踐一下吧！如果在學(xué)習(xí)過程中，有遇到任何問題，歡迎加我好友，我拉你進Python學(xué)習(xí)交流群共同探討學(xué)習(xí)。
-------------------?End?-------------------
往期精彩文章推薦：
歡迎大家點贊，留言，轉(zhuǎn)發(fā)，轉(zhuǎn)載，感謝大家的相伴與支持
想加入Python學(xué)習(xí)群請在后臺回復(fù)【入群】
萬水千山總是情，點個【在看】行不行
/今日留言主題/
隨便說一兩句吧~~