思維導(dǎo)圖

Q1：SQL中有哪些常用的聚合函數(shù)尉咕？

• 常用的聚合函數(shù)

• 注：對表中所有記錄進行聚合計算時叠蝇，無需使用group by語句，可以在select后直接寫聚合函數(shù)年缎，但是不能出現(xiàn)非聚合字段悔捶。

• 代碼示例：統(tǒng)計表中2019-08-21的所有記錄的數(shù)量

Q2：SQL中distinct有什么作用？如何使用distinct单芜？

• distinct用于去重蜕该，它有兩種使用場景

1.在select后直接使用

• 對select后的所有字段進行去重

• 可以理解為在所有的語句執(zhí)行結(jié)束之后，對所有的記錄整體去重

• 只有在所有字段值都相同的情況下洲鸠，才會進行去重堂淡，不能做到對部分字段進行去重

• 雖然在Hive中只能使用union all，但是如果想達到union的效果扒腕，則可以使用distinct

• 注：distinct和group by語句不能在同一個SQL查詢中出現(xiàn)（不包含子查詢的情況）

• 代碼示例：

2.distinct在聚合函數(shù)中使用

• 實現(xiàn)分組后去重淤齐，然后再進行聚合計算

• 代碼示例：（統(tǒng)計每個學(xué)生參加考試的次數(shù)，以及參加過考試的學(xué)科數(shù)）

• count(1)統(tǒng)計的是2019年該學(xué)生的考試記錄數(shù)

• count(distinct subject)則會基于學(xué)號和姓名分組后袜匿，對同一個學(xué)生所有的學(xué)科記錄去重后統(tǒng)計記錄數(shù)更啄，從而計算出參加過考試的學(xué)科數(shù)

Q3：SQL中case when有什么作用？如何使用居灯？

1.利用現(xiàn)有的字段祭务，結(jié)合條件語句，生成新的字段

• 代碼示例1：（根據(jù)城市名city怪嫌，生成一個新的字段province义锥，并將除"青島""濟南""南京"之外的值統(tǒng)一命名為“其他”）

• 代碼示例2：（另一種實現(xiàn)方式）

• in表示如果是兩者其中之一，則賦值“山東”

• 注：不要漏掉end關(guān)鍵字岩灭，實際應(yīng)用中會直接報錯拌倍，面試中會大大減分

2.case when可以被使用在分組語句和選擇語句中，寫在group by之后，提供新的分組字段

• case when也可以被寫在select后柱恤，基于現(xiàn)有的字段生成新的字段

• 注：如果將case when寫在group by之后数初，則不可以使用字段別名

• 代碼示例：（統(tǒng)計各個省的數(shù)據(jù)量）

3.case when還被被使用在聚合函數(shù)中

• 代碼示例：（統(tǒng)計學(xué)生參加考試的次數(shù)，學(xué)生考試通過[>60]的次數(shù)以及考試通過的學(xué)科數(shù)）

• count(1)：統(tǒng)計學(xué)生考試總數(shù)

• count(case when score >= 60 then 1 end)：篩選出成績大于60分的考試記錄

• count(distinct subject)：統(tǒng)計參加過考試的學(xué)科數(shù)

• count(distinct case when score >= 60 then subject end)：統(tǒng)計考試通過的學(xué)科數(shù)

聚合函數(shù)+distinct+case when梗顺，基本可以完成SQL分組計算

Q4：什么是窗口函數(shù)泡孩，如何在SQL中使用窗口函數(shù)？

1. 窗口函數(shù)

• 窗口函數(shù)與聚合函數(shù)類似寺谤，它也會在記錄分組之后進行聚合計算

• 它不會為每組只返回一個值仑鸥，而是可以為每組返回多個值

• 準確地說，它為分組中的每條記錄都會返回特定值

• 窗口函數(shù)既可以計算出整體的統(tǒng)計值（平均分变屁、總次數(shù)等）眼俊，也可以計算出每條記錄在分組中基于時間或者其他維度的排名或者分位數(shù)

• 窗口函數(shù)不會出現(xiàn)在group by語句中，也不會出現(xiàn)在聚合函數(shù)中粟关，它只能出現(xiàn)在select語句后疮胖。并且使用窗口函數(shù)后，不會再使用group by語句

2.窗口函數(shù)的使用

• 窗口函數(shù)的基本結(jié)構(gòu)：函數(shù)名() over (partition by c1,c2 order by c3 asc,c4desc)

• 代碼示例：（統(tǒng)計每個學(xué)生各個學(xué)科2019年最新的一次考試記錄）

• 子查詢中誊役，使用了row_number()窗口函數(shù)获列，其中"partition by"表示對所有的記錄按照id、name和subject進行分組

• 具有相同id蛔垢、name和subject的記錄按照pt降序排列击孩，最新的記錄回排在最前面。同一分組的所有記錄返回row_number()對應(yīng)的值鹏漆，最新的記錄返回1巩梢，次新的記錄返回2。最后篩選出rank=1的列艺玲，即為最新一次的考試記錄括蝠。

3.常用的窗口函數(shù)

窗口函數(shù)一定要掌握，通過窗口函數(shù)可以減少表與表之間的連接饭聚，同時也可以實現(xiàn)很多功能

Q5：Hive中如何實現(xiàn)動態(tài)更新忌警？

• Hive本身不支持對記錄進行insert、update秒梳、delete等操作法绵，因此無法直接修改記錄

即使在Hive的最新版本中可以通過一些方法來實現(xiàn)，但是在實際工作中也很少這樣做

• 動態(tài)分區(qū)可以解決這個問題酪碘，通過對分區(qū)的全量更新朋譬，實現(xiàn)對數(shù)據(jù)的修改

1.建立分區(qū)表

• 建表語句：

• 分區(qū)表按照pt進行分區(qū)，col1兴垦、col2徙赢、col3是非分區(qū)字段

2.設(shè)置參數(shù)

• 如果要對其中的一些分區(qū)進行更新字柠，需要設(shè)置一些參數(shù)

• 代碼示例：

• 第一條語句表示開啟動態(tài)分區(qū)；第二條語句表示在動態(tài)分區(qū)的過程中狡赐，可以不用指定任何分區(qū)

3.動態(tài)更新

• 更新語句：

• insert overwrite意思是覆蓋窑业，在接下來的查詢語句中，所有出現(xiàn)的pt對應(yīng)的分區(qū)都會進行全量更新阴汇，更新后的數(shù)據(jù)就是SQL語句查詢的結(jié)果

• 注：select后字段的順序需要與建表時字段的順序一致数冬，并且將分區(qū)字段放到最后节槐。如果有多個分區(qū)字段搀庶，那么也需要按照建表時字段的順序進行排序

• 常出現(xiàn)的問題：查詢語句中字段的順序和建表時字段的順序不一致導(dǎo)致數(shù)據(jù)錯位

Q6：SQL中如何實現(xiàn)一行變多行？

案例：比如在數(shù)據(jù)庫中以列表的形式存儲了某個學(xué)生一段時間的成績

現(xiàn)在想計算出成績的平均值

1.字段拆分

• Hive中并沒有處理這種列表數(shù)據(jù)的函數(shù)铜异，因此需要先將grades字段拆分成多行哥倔，每行代表一個分數(shù)

• 代碼示例：

• regexp_replace()函數(shù)：將grades字段中所有的“{”和“}”替換為' '，也就是刪除

• split()函數(shù)：將grades字段按照逗號進行分割

• explode()函數(shù)：將grades字段變成多行揍庄，再使用“l(fā)ateral view”將變成多行后的grades字段與原表進行笛卡爾積計算

• t可以理解為只有一個字段的虛擬表咆蒿，該字段為grades字段分成多行后的結(jié)果，并且將該字段重命名為"grade"蚂子，最后將虛擬表t與原表進行笛卡爾積運算

• 最終輸出結(jié)果：

Q7：SQL中常用的調(diào)優(yōu)方法有哪些沃测？

1.當需要對大表和小表進行join操作時，可以使用MAPJOIN將小表加載到內(nèi)存中

• 此時將a放到內(nèi)存中食茎，由b到內(nèi)存中循環(huán)讀取a蒂破，由于讀取內(nèi)存中數(shù)據(jù)的速度要遠快于讀取磁盤中數(shù)據(jù)的速度，因此效率得到大大提高别渔。

• 通常小表的大小應(yīng)小于25MB附迷，否則達不到應(yīng)有的效果

2.當需要對大表和大表進行join操作時

• 可以考慮是否能夠?qū)⑵渲幸粋€大表轉(zhuǎn)換成小表，例如只需要一個表中一段時間的數(shù)據(jù)時哎媚，就可以將這段時間的數(shù)據(jù)取岀喇伯，建立一個小的臨時表，然后將其與另一個表進行連接

• 即使按照上述操作進行計算拨与，計算速度仍然不快稻据，這時需要考慮數(shù)據(jù)傾斜的問題

3.數(shù)據(jù)傾斜

• 理論上，正常的數(shù)據(jù)分布都有可能是不平衡的买喧。正是由于數(shù)據(jù)分布的不平衡捻悯，導(dǎo)致Hive在計算過程中出現(xiàn)數(shù)據(jù)傾斜的問題

• 數(shù)據(jù)傾斜問題涉及到Map&Reduce過程

• Map過程會將原始數(shù)據(jù)轉(zhuǎn)換成<key,value>鍵值對，然后Reduce過程會對相同key的數(shù)據(jù)進行合并計算

• 在默認情況下岗喉，具有相同key的數(shù)據(jù)會被放在同一個Reduce任務(wù)中秋度，因此會出現(xiàn)“一個人累死，其他人閑死”的情況钱床，即數(shù)據(jù)傾斜問題（執(zhí)行Hive SQL語句或MapReduce作業(yè)時荚斯，一直卡在Map100%、Reduce99%）

4.數(shù)據(jù)傾斜常見問題的解決方法

（1）當使用group by分組時，如果某些key占比非常大事期，由于相同key的數(shù)據(jù)會被拉取到相同節(jié)點中執(zhí)行Reduce操作滥壕，因此會出現(xiàn)某些節(jié)點需要計算的數(shù)據(jù)量遠大于其他節(jié)點的情況，造成數(shù)據(jù)傾斜兽泣。

• 明顯特征：在Reduce任務(wù)執(zhí)行時绎橘，進度停留在99%的時間非常長，此時1%的節(jié)點計算量可能超過其余99%節(jié)點計算量的總和

• 解決方法：設(shè)置“set hive.map.aggr=true”和“set hive.groupby.skewindata=true”參數(shù)

• 設(shè)置參數(shù)后唠倦，生成的查詢會將此前的一個MapReduce作業(yè)拆分成兩個任務(wù)：

  • 第一個任務(wù)：Map任務(wù)的輸出結(jié)果集合會隨機分布到Reduce任務(wù)中称鳞，每個Reduce任務(wù)進行部分聚合操作，并輸出結(jié)果稠鼻，這樣相同key的數(shù)據(jù)會被拉取到不同的節(jié)點中冈止，從而達到負載均衡的目的

  • 第二個任務(wù)：根據(jù)第一個任務(wù)預(yù)處理的數(shù)據(jù)結(jié)果將相同key的數(shù)據(jù)分發(fā)到同一個Reduce任務(wù)中，完成最終的聚合操作

（2）當Map任務(wù)的計算量非常大時（執(zhí)行count(*),sum(case when)這些語句)

• 解決方法：設(shè)置Map任務(wù)數(shù)量的上限

• “set mapred.map.tasks”可以設(shè)置合理的Map任務(wù)數(shù)量

（3）如果Hive SQL語句中計算的數(shù)據(jù)量非常大

• 例如:

• 此時就會因為count(distinct b)函數(shù)而出現(xiàn)數(shù)據(jù)傾斜的問題候齿，可以使用“sum...group by”代替該函數(shù)：

（4）當需要執(zhí)行join操作但是關(guān)聯(lián)字段存在大量空值時

• 解決方法：可以在join操作過程中忽略空值熙暴，然后再通過union操作加上空值

參考文獻

1.《拿下Offer 數(shù)據(jù)分析師求職面試指南》徐麟 著