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