01、寫(xiě)在前面
秋高氣爽，金桂飄香，十月份即將結(jié)束，對(duì)于求職的小伙伴們來(lái)說(shuō)，“金九銀十”已經(jīng)接近尾聲，不知道小伙伴們有沒(méi)有找到自己心儀的工作呢？相信大多數(shù)的小伙伴都經(jīng)歷過(guò)數(shù)據(jù)分析的面試流程了，在數(shù)據(jù)分析的面試過(guò)程中，除了常規(guī)的業(yè)務(wù)問(wèn)題外，還會(huì)重點(diǎn)考查數(shù)據(jù)分析的技術(shù)能力，這里就不得不提SQL了，SQL作為數(shù)據(jù)分析的筆試必考題，常常被用來(lái)檢驗(yàn)候選人的技術(shù)水平，而且有些題目也是非常的刁鉆，這對(duì)于初入職場(chǎng)的“小白”也是非常不友好。不過(guò)不用擔(dān)心，結(jié)合自己多年的數(shù)據(jù)分析的求職和工作經(jīng)驗(yàn)，我們接下來(lái)會(huì)有一系列文章，詳細(xì)講解數(shù)據(jù)分析面試中那些高頻出現(xiàn)的SQL筆試題以及各大廠的SQL筆試題，學(xué)習(xí)了這些筆試題的常見(jiàn)套路和解法，把這些題目都刷一遍，在接下來(lái)的筆試中應(yīng)該可以一往無(wú)前，勢(shì)如破竹!，收割offer啦！?
數(shù)據(jù)分析筆試中比較?？疾榈囊粋€(gè)知識(shí)點(diǎn)就是行/列轉(zhuǎn)換，以及一行/多行轉(zhuǎn)換，因?yàn)樵趯?shí)際的業(yè)務(wù)中數(shù)據(jù)原本的存儲(chǔ)方式可能不便于我們進(jìn)行分析，所以我們需要進(jìn)行行/列的轉(zhuǎn)換，以及一行/多行的轉(zhuǎn)換，這個(gè)轉(zhuǎn)換過(guò)程非常常見(jiàn)也比較的tricky，所以也是數(shù)據(jù)分析面試中經(jīng)?？疾榈膬?nèi)容！下面我們就通過(guò)實(shí)際的案例來(lái)進(jìn)行講解。
02、行/列轉(zhuǎn)換
行/列轉(zhuǎn)換在做數(shù)據(jù)分析時(shí)會(huì)經(jīng)常遇到，所謂的行/列轉(zhuǎn)換就是如下圖所示兩種展示形式的互相轉(zhuǎn)換。
我們假設(shè)有4款產(chǎn)品ABCD，分別在三個(gè)電商平臺(tái)天貓、淘寶和京東上進(jìn)行銷售，下表分別以兩種形式記錄了某個(gè)月各產(chǎn)品（Product）在各個(gè)平臺(tái)（Platform）的銷售數(shù)量（Quqntity）。我們?yōu)榱朔治龅姆奖悖枰獙?duì)兩種形式進(jìn)行轉(zhuǎn)換，也就是我們常說(shuō)的行/列轉(zhuǎn)換。
行轉(zhuǎn)列
假如我們有下表，由Product、Platform、Quantity三個(gè)字段組成，但是為了方便展示，我們需要將Platform中的每一平臺(tái)展示為一列，也就是行轉(zhuǎn)列。
方法一：PIVOT
在SQL SERVER中，提供了專門(mén)進(jìn)行行列轉(zhuǎn)換的函數(shù)：PIVOT，PIVOT的一般語(yǔ)法是：
FROM table_source? ? -- 表名稱，即數(shù)據(jù)源
? ? PIVOT(
? ? 聚合函數(shù)（value_column）? ? -- value_column 要轉(zhuǎn)換為 列值 的列名
? ? FOR pivot_column? ? ? ? -- pivot_column 指定要轉(zhuǎn)換的列
? ? IN(column_list)? ? ? ? -- column_list 自定義的目標(biāo)列名
)
使用PIVOT進(jìn)行行轉(zhuǎn)列的代碼如下：
SELECT *
FROM table
PIVOT (
? ? SUM(quantity) FOR platform IN ('天貓','淘寶','京東')
)
通過(guò)上面 SQL 語(yǔ)句即可得到下面的結(jié)果：
PIVOT 后跟一個(gè)聚合函數(shù)SUM來(lái)拿到結(jié)果，F(xiàn)OR 后面跟的平臺(tái)Platform是我們要轉(zhuǎn)換的列，這樣的話平臺(tái)中的天貓、淘寶、京東就就被轉(zhuǎn)換為列。IN 后面跟的就是具體的平臺(tái)名稱。
方法二：聚合函數(shù)+CASE WHEN
需要注意的是PIVOT是SQL SERVER的內(nèi)置函數(shù)，對(duì)于行列轉(zhuǎn)換非常方便，但Oracle、MySQL是不支持PIVOT函數(shù)的。我們可以用聚合函數(shù)配合CASE語(yǔ)句實(shí)現(xiàn)。使用 CASE WHEN 可以得到和 PIVOT 同樣的結(jié)果，但是沒(méi)有 PIVOT 簡(jiǎn)單直觀。
SELECT Product,
? MAX(CASE WHEN Platform='天貓' THEN quantity ELSE 0 END) AS "天貓",
? MAX(CASE WHEN Platform='淘寶' THEN quantity ELSE 0 END) AS "淘寶",
? MAX(CASE WHEN Platform='京東' THEN quantity ELSE 0 END) AS "京東"
FROM table
GROUP BY Product
列轉(zhuǎn)行
反過(guò)來(lái)，假設(shè)我們有下表 table，各個(gè)平臺(tái)已經(jīng)作為一列出現(xiàn)，我們需要將各個(gè)平臺(tái)匯總為一列，也就是列轉(zhuǎn)行。
方法一：UNPIVOT
與行轉(zhuǎn)列類似，在SQL SERVER中，提供了列轉(zhuǎn)行的函數(shù)：UNPIVOT，UNPIVOT的一般語(yǔ)法是：
FROM table_source? ? -- 表名稱，即數(shù)據(jù)源
? ? UNPIVOT(
? ? value_column? ? -- value_column 要轉(zhuǎn)換為 行值 的列名
? ? FOR pivot_column? ? -- pivot_column 指定要轉(zhuǎn)換為指定的列
? ? IN(column_list )? ? -- column_list 目標(biāo)列名
)
使用UNPIVOT進(jìn)行列轉(zhuǎn)行的代碼如下：
SELECT *
FROM table
UNPIVOT (
? ? quantity FOR Platform IN ("天貓","淘寶","京東")
)
通過(guò) UNPIVOT 可得到如下結(jié)果：
方法二：聚合函數(shù)+UNION
同理，Oracle，MySQL也是不支持UNPIVOT函數(shù)的，對(duì)于這種情況我們可以通過(guò)聚合函數(shù)+UNION的方式將數(shù)據(jù)組合起來(lái)。
SELECT
? ? Product,
? ? '天貓' AS Platform ,
? ? MAX("天貓") AS Quantity
FROM table GROUP BY Product
UNION
SELECT
? ? Product,
? ? '淘寶' AS Platform ,
? ? MAX("淘寶") AS Quantity
FROM table GROUP BY Product
UNION
SELECT
? ? Product,
? ? '京東' AS Platform ,
? ? MAX("京東") AS Quantity
FROM table GROUP BY Product
03、一行/多行轉(zhuǎn)換
在數(shù)據(jù)分析中，還有一個(gè)非常常見(jiàn)的場(chǎng)景，就是需要將一行數(shù)據(jù)拓展為多行數(shù)據(jù)，或者將多行數(shù)據(jù)匯總為一行數(shù)據(jù)，這種一行/多行的數(shù)據(jù)轉(zhuǎn)換問(wèn)題也是面試中頻繁考查的知識(shí)點(diǎn)之一，舉例如下圖所示。
一行轉(zhuǎn)多行
原數(shù)據(jù)如下：table表中有兩個(gè)字段，產(chǎn)品（Product）和供應(yīng)商（Supplier），每個(gè)產(chǎn)品有多個(gè)供應(yīng)商，各個(gè)供應(yīng)商以逗號(hào)分割匯總為一行?，F(xiàn)在需要將各個(gè)供應(yīng)商分別展開(kāi)為一行。
針對(duì)這種情況，Hive中提供了一個(gè)很高效的方法：lateral view和explode等函數(shù)一起使用，能將一行數(shù)據(jù)拆分成多行數(shù)據(jù)，在此基礎(chǔ)上可以對(duì)拆分的數(shù)據(jù)進(jìn)行聚合，使用的時(shí)候需要注意：
1、explode接受一個(gè)數(shù)組（或一個(gè)map）作為輸入，并將數(shù)組元素（map）作為單獨(dú)的行輸出。UDTF可以在SELECT表達(dá)式列表中使用，也可以作為L(zhǎng)ATERAL VIEW的一部分使用。
2、LATERAL VIEW是配合explode（或者其他的UDTF），把單行數(shù)據(jù)拆解成多行后的數(shù)據(jù)結(jié)果集。
3、LATERAL VIEW explode要放置于from之后，where之前
SELECT DISTINCT Product,Supplier
FROM table
LATERAL VIEW explode (split(Supplier,',')) as t -- t為新表別名
拆成后結(jié)果：
多行轉(zhuǎn)一行
反過(guò)來(lái)，我們想把多行再變回一行，需要怎么處理呢？我們可以使用：collect_set +concat_ws來(lái)實(shí)現(xiàn)。代碼如下：
SELECT Product,concat_ws(',',collect_set(Supplier)) Product_Supplier
FROM table
GROUP BY Product
最終還原為一行數(shù)據(jù)如下：
注意：上述用的到的collect_set函數(shù)，有兩個(gè)作用，第一個(gè)是去重，去除group by后的重復(fù)元素，
第二個(gè)是形成一個(gè)集合，將group by后屬于同一組的集合起來(lái)成為一個(gè)集合。與contact_ws結(jié)合使用就是將這些元素以逗號(hào)分隔的形式形成字符串。
以上就是數(shù)據(jù)分析面試—SQL高頻面試題部分的內(nèi)容，部分?jǐn)?shù)據(jù)分析文章請(qǐng)翻看歷史文章，更多數(shù)據(jù)分析面試筆試的文章請(qǐng)移步共粽號(hào)：【數(shù)據(jù)分析星球】，敬請(qǐng)期待，如果覺(jué)得不錯(cuò)，也歡迎分享、點(diǎn)贊和點(diǎn)收藏哈~
