為什麼考察SQL？
大數據分析工程師80%的時間都在與SQL打交道，通過SQL完成業務方的各種臨時性需求分析和常規性報表統計。熟練的SQL技能能夠大大提高工作效率。本文將SQL/SparkSql/HiveQL放在一起來梳理一份常見題型的面試題庫。
面試題庫
01
SQL基礎知識考察
對於面試初級數據分析師來說，SQL的面試重點會放在基礎知識的考察，如果最基本的基礎概念和語法都不能熟練回答出來的話，通過面試的機率就會很低。下面兩張圖是SQL基礎概念和基礎語法的考題大綱圖，接下來圍繞圖中提到的概念來列舉幾個常見面試題。
圖1 基礎概念
圖2 基礎語法
考題模擬
題1：你覺得SQL是一種什麼樣的語言，說說你對它的認識。
答：SQL是Structured Query Language（結構化查詢語言）的縮寫。是一種專門用來與資料庫溝通的語言，與大多數程式語言不同，SQL中只有很少的關鍵字，SQL語言的設計只為了達到一個目的---提供一種從資料庫中讀寫數據的簡單有效的方法。
SQL有如下優點：
（1）SQL不是某個特定資料庫提供商專有的語言。幾乎所有重要的DBMS都支持SQL，所以學習此語言使你幾乎能與所有資料庫打交道。
（2）SQL簡單易學。它的語句全都是由有很強描述性的英語單詞組成，而且這些單詞的數目不多。
（3）SQL雖然看上去很簡單，但實際上是一種強有力的語言，靈活使用其語言元素，可以進行非常複雜和高級的資料庫操作。
題2：你是怎麼理解資料庫和表的？
答：資料庫是一個以某種有組織的方式存儲的數據集合。可以將資料庫想像為一個文件櫃，這個文件櫃是一個存放數據的物理位置，不管數據是什麼，也不管數據是如何組織的。通常容易將資料庫和資料庫軟體的概念相混淆，資料庫軟體應稱為資料庫管理系統（DBMS）。資料庫是通過DBMS創建和操縱的容器，而具體它究竟是什麼，形式如何，各種資料庫都不一樣。
往文件櫃裡放資料時，並不是隨便將它們扔進某個抽屜就完事了，而是在文件櫃中創建文件，將相關的資料放入特定的文件中。在資料庫領域中，這種文件稱為表。是一種結構化的文件，可用來存放某種特定類型的數據。關於表的概念有以下注意事項：
資料庫中的每個表都有一個名字來標識自己。這個名字是唯一的，即資料庫中沒有其他表具有相同的名字。
PS：每次回答面試官所提問的概念性問題時，最好在答出概念後，總結性的闡述一下相關注意事項，這樣能夠很快體現出你對這個概念的認知是很清楚的，且給面試官思路清晰，總結和表述能力不錯的印象。
題3：手寫一下如何創建一張表Products，該表有5個欄位，產品id，供應商id，產品名稱，產品價格，產品描述。
答：
CREATE TABLE Products
（
prod_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '產品id',
vend_id INT NOT NULL COMMENT '供應商id',
prod_category VARCHAR(254) COMMENT '產品類別',
prod_name VARCHAR(254) COMMENT '產品名稱',
prod_price DECIMAL(8,2) COMMENT '產品價格',
prod_desc VARCHAR(1000) COMMENT '產品描述',
create_time datetime DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',
update_time datetime COMMENT '更新時間'
）;
本題看似簡單，卻可考察出面試者的三個方面：
1.平時創建表時考慮是否周全，比如對各個欄位該定義為什麼類型，可否為空是否都有思量；
2.建表是否有新建主鍵意識；
3.如果最後能自己主動補上create_time和update_time兩欄位，可說明面試者的確是有實際工作經驗的。
題4：給剛剛定義的表Products增加一列，表示供應商的手機號碼。
答：
ALTER TABLE Products add vend_phone VARCHAR(20);
題5：把剛新加的列刪除掉。
答：ALTER TABLE Products DROP COLUMN vend_phone;
PS：諸如以上題4題5題6，面試官要麼會直接問你怎麼新加一個欄位，怎麼刪除一個欄位，這種只需你口頭回答的題型，要麼就會給個示例，讓你實際書寫一下。總之，無論會怎麼面，面試者需要對基本的DDL語言十分熟悉這是肯定的，類似考題比如怎麼給表重命名呀，怎麼刪除一張表呀，怎麼給表插入數據呀，等等，這裡就不一一列舉了，如果真的忘了，可即刻上網百度複習一下。
題6：假設以上所建表中已插入實際數據，請你檢索一下產品價格大於20，供應商id為28，產品名稱裡包含餅乾的數據，結果按產品id排序，只需顯示10行。
答：當我們看到這樣一個典型的數據檢索麵試題時，首先我們將面試官所描述的所有內容快速記錄下來，如果面試官沒有給你準備面試草稿紙，那麼，你最好事先自己準備幾張，一個事前能夠做足準備的面試者，給面試官的印象分只會增不會減。將內容記錄下來以後，接下來，你先花點時間觀察一下這些內容，迅速對應一下自己所學知識結構，想想，每一個具體的內容描述，面試官具體是想考察什麼。以本題為例，首先說的是檢索一下，那麼也就是select，產品價格大於20，供應商id為28，顯然這是限制條件，且它們之間是組合關係（且），產品名稱裡包含餅乾，包含，什麼意思呢，就是模糊查詢的概念，那麼自然想到like，結果排序，也就是order by，且並沒有說按升序還是降序，那就只要使用默認排序即可，顯示10行，limit。
SELECT *
FROM Products
WHERE prod_price > 20 and vend_id = '28'
and prod_name like '%餅乾%'
ORDER BY prod_id
LIMIT 10；
題7：以上明細結果基礎上按產品類別分組，統計每組記錄數，結果按照記錄數進行降序。
答：
SELECT prod_category，count(*) as cnt
FROM Products
WHERE prod_price > 20 and vend_id = '28'
and prod_name like '%餅乾%'
GROUP BY prod_category
ORDER BY prod_category DESC;
題8：將產品名稱和價格以空格分隔，合併為一個欄位。
答：使用CONCAT函數。
CONCAT(prod_name,' ',prod_price)
題9：將產品價格按照1-10元，11-100元，100元以上分為三組，進行分組統計記錄數。
答：
SELECT
case when prod_price between 1 and 10 then '1~10'
when prod_price between 11 and 100 then '11~100'
when prod_price > 100 then '大於100' end as price_group,
count(*) as cnt
FROM Products
group by price_group;
PS：在題7考查了基本語法結構以後，會再進一步考查分組，匯總，使用函數，創建計算欄位等更為常見使用更為頻繁的SQL進階知識點，如題8/9/10。其他常見的聚集函數，文本處理函數，日期、時間處理函數和數值處理函數等，考察情況類似，大家可自行前去總結了解一下，也可直接閱讀本公眾號SQL相關文章，裡面都有羅列總結。
02
SQL實操考察
SQL基礎知識考察結束後，如果面試者整體表現尚佳，面試官就會加大考題難度，以實際工作中的SQL應用程度來考察大家，具體體現為對多張表之間的檢索考察，相關知識點涉及：表聯結、子查詢、組合查詢。
圖3 實操考察
考題模擬
題10：什麼叫左外連接、什麼叫右外連接、什麼叫內外連接 或 什麼叫全外連接？
答：
內連接(INNER JOIN)，通常可以省略掉INNER不寫，它的含義是左右兩個集合相乘後，只保留滿足ON後面關聯條件的記錄。所以，可以利用內連接計算兩個集合的交集，只需要把集合元素的欄位都寫在ON後面的關聯條件裡即可。
左外連接(LEFT OUTER JOIN)，OUTER通常可以省略不寫，它的含義是，左右兩個集合相乘後，保留滿足ON後面關聯條件的記錄加上左表中原有的但未關聯成功的記錄。因此，左外連接，可以用來計算集合的差集，只需要過濾掉關聯成功的記錄，留下左表中原有的但未關聯成功的記錄，就是我們要的差集。
右外連接(RIGHT OUTER JOIN)，與左外連接含義相同，只是方向不同而已，通常也是省略OUTER不寫。
全外連接(FULL OUTER JOIN)，含義是，左右兩個集合相乘後，保留滿足ON後面關聯條件的記錄加上左表和右表中原有的但未關聯成功的記錄。
題11：假設除了以上Products表，還有一張存儲銷售產品供應商的表Vendors，表中欄位信息如下，如何得到表Products和Vendors兩表能關聯上的部分中Products的數據。
答：
//方式一：
SELECT Products.*
FROM Vendors，Products
WHERE Vendors.vend_id = Products.vend_id;
//方式二：
SELECT Products.*
FROM Products
WHERE vend_id IN (SELECT DISTINCT vend_id
FROM Vendors);
//方式三：
SELECT a.*
FROM Products a
(INNER) JOIN Vendors b
ON a.vend_id = b.vend_id;
題12：檢索供應商id不在Products中的Vendors表中的數據。
答：
SELECT a.*
FROM Vendors a
LEFT JOIN Products b
ON a.vend_id = b.vend_id
WHERE b.vend_id is null
題13：組合查詢有哪兩種？組合查詢有什麼特點？
答：組合查詢有兩種，一種是相同記錄去重組合查詢，一種是相同記錄不去重組合查詢。對應語法是UNION和UNION ALL。
UNION使用注意事項：
（1）使用UNION必須有兩條或者兩條以上的SELECT語句組成，語句之間用UNION關鍵字分割；
（2）使用UNION關聯的每個子查詢必須包含相同數量的欄位；
（3）列數據類型必須兼容;類型不必完全相同，但必須是DBMS可以隱含轉換的類型(不同的數值類型或者不同的日期類型)；
（4）使用組合查詢,當需要對結果進行排序時，只能指定一條order By語句，這條語句只能放在最後一條SELECT語句的後面。
以上所列是SQL中的UNION使用注意事項，HiveSql和SparkSql中的使用規則如何讀者最好再自行總結對比一下。
PS：以上幾個示例分別演示了表聯結、子查詢、組合查詢的重要知識點，實際考題可能千變萬化，本小結相當於給大家將相關知識點總結了一下，大家可多刷刷題加強認知和使用的熟練程度。
03
SparkSql/HiveSql專業知識考查
實際面試中，前兩節SQL知識點的面試範圍和語法題型同樣適用於SparkSql/HiveSql，至於SparkSql和HiveSql獨有的特性考察，在面試一個初級數據分析師的時候，會體現在以下知識點。
圖4 SparkSql專業考點
圖5 HiveSql專業考點
考題模擬
題14：SparkSql支持讀入的數據類型有哪些？
答：SparkSql支持讀入的數據類型有parquet、csv、json、jdbc、table和text等。
題15：具體闡述一下SparkSql想做到跟SQL一樣查看目標文件裡的3條記錄，需要怎麼做？
答：
（1）構建入口
import org.apache.spark.sql.SparkSession
val spark = SparkSession
.builder()
.appName("Spark SQL basic example")
.config("spark.some.config.option", "some-value")
.getOrCreate()
（2）創建DataFrame，例如從一個json文件創建一個DataFrame。
val df = spark.read.json("examples/src/main/resources/people.json")
// 顯示出DataFrame的內容
df.show()
// +----+-------+
// | age| name|
// +----+-------+
// |null|Michael|
// | 30| Andy|
// | 19| Justin|
// +----+-------
（3）執行SQL查詢。
// 將DataFrame註冊成一個臨時視圖
df.createOrReplaceTempView("people")
val sqlDF = spark.sql("SELECT * FROM people LIMIT 3")
sqlDF.show()
// +----+-------+
// | age| name|
// +----+-------+
// |null|Michael|
// | 30| Andy|
// | 19| Justin|
// +----+-------+
其他使用語法示例。
import spark.implicits._ //導入隱式轉換的包
//列印schema
df.printSchema()
// root
// |-- age: long (nullable = true)
// |-- name: string (nullable = true)
//選擇一列進行列印
df.select("name").show()
//選取年齡大於20的
df.filter($"age" > 20).show()
//聚合操作
df.groupBy("age").count().show
題16：SparkSql的數據保存語法是什麼樣的？
答：
//例如選取DataFrame中的兩列保存到json文件中，可指定保存文件的格式
df.select("name", "favorite_color").write.format("json").save("namesAndFavColors.json")
//選取DataFrame中的兩列保並追加到parquet文件中，可指定模式為追加，另還有覆蓋模式
df.select("name", "favorite_color").write.mode(SaveMode.append)
.save("namesAndFavColors.parquet")
題17：談談Hive裡分區的概念，它的作用是什麼，如何添加和刪除分區？
答：在Hive中，表中的一個分區（Partition）對應於表下的一個目錄，分區的作用就是輔助查詢，縮小查詢範圍，在HiveSql中限制分區條件可加快數據的檢索速度。
//添加分區
ALTER TABLE day_table ADD PARTITION (dt='2008-08-08', hour='08') location '/path/pv1.txt' PARTITION (dt='2008-08-08', hour='09') location '/path/pv2.txt';
//刪除分區
ALTER TABLE day_hour_table DROP PARTITION (dt='2008-08-08', hour='09');
題18：如何自定義一個函數供HiveSql使用，具體步驟分為哪幾步？
答：Hive自定義臨時通用函數的步驟如下，以一個簡單示例我們一起來操作一下：
1. 在類中創建自定義函數。自定義UDF需要繼承org.apache.hadoop.hive.ql.exec.UDF，實現evaluate函數，evaluate函數支持重載。
package com.yqz.udf;
import org.apache.hadoop.hive.ql.exec.UDF;
import org.apache.hadoop.io.Text;
public class ConcatString extends UDF {
// string can not translation in hadoop
public Text evaluate(Text a, Text b) {
return new Text(a.toString() + "*******" + b.toString());
}
}
查詢執行過程中，查詢中對應的每個應用到這個函數的地方都會對這個類進行實例化。對於每行輸入都會調用evaluate()函數。
2.將該類所在的包導出成jar包，放入linux目錄下。
//先刪除舊包
delete jar /data/yqz/hive/contactString.jar;
//添加新的jar包
add jar /data/yqz/hive/contactString.jar;
需要注意的是，jar文件路徑是不需要用引號括起來的。這個路徑需要是當前文件系統的全路徑。Hive不僅僅將這個jar文件放入到classpath中，同時還將其加入到分布式緩存中，這樣，整個集群的機器都是可以獲得該jar文件的。
3.創建臨時函數，指向jar包中的類。
//語法：create temporary function <函數名> as 'java類名';
//示例
create temporary function myconcat as 'com.yqz.udf.ConcatString';
需要注意的是，create temporary function中的temporary 關鍵字表示的是當前會話中聲明的函數只會在當前會話中有效。因此用戶需要在每個會話中都添加jar，然後再創建函數。如果用戶需要長期頻繁的使用同一個jar和函數的話，可以將相關語句增加到$HOME/.hiverc文件中去。
4.使用臨時函數.
//語法：
使用：select <函數名> (參數);
刪除：drop temporary function if exists <函數名>;
//示例
select myconcat('HELLO','world');
刪除自定義臨時函數時，加上if exists，這樣即使該函數不存在，也不會報錯。
題19：SQL和HiveSql中都有窗口函數，能講講窗口函數的基本語法嗎？
答：
<窗口函數>()
OVER
(
[PARTITION BY <列清單>]
[ORDER BY <排序用清單列>] [ASC/DESC]
(ROWS | RANGE) <範圍條件>
)
題20：HiveSql使用到的數據跟傳統SQL資料庫中的數據存儲方式不同，你能講解下HiveSql中使用到的數據源的數據組織方式嗎？
答：
（1）Hive的數據組織包括資料庫、表、視圖、分區、分桶和表數據等。資料庫，表，分區等等都對應 HDFS上的一個目錄。分桶和表數據對應 HDFS 對應目錄下的文件。
（2）Hive 中所有的數據都存儲在 HDFS 中，沒有專門的數據存儲格式，因為 Hive 是讀模式 （Schema On Read），可支持 TextFile，SequenceFile，RCFile 或者自定義格式等。
（3）只需要在創建表的時候告訴 Hive 數據中的列分隔符和行分隔符，Hive 就可以解析數據。
（4）Hive 中包含以下數據模型：
database：在 HDFS 中表現為${hive.metastore.warehouse.dir}目錄下一個文件夾；
table：在 HDFS 中表現所屬 database 目錄下一個文件夾；
partition：在 HDFS 中表現為 table 目錄下的子目錄；
bucket：在 HDFS 中表現為同一個表目錄或者分區目錄下根據某個欄位的值進行 hash 散列之後的多個文件；
view：與傳統資料庫類似，只讀，基於基本表創建；
（5）Hive 的元數據存儲在 RDBMS中，除元數據外的其它所有數據都基於 HDFS 存儲。
PS：需要重點強調的是，雖然以上示例16-22中，窗口函數只是通過示例給出了語法結構，但在實際面試中窗口函數卻是被考察最多的，其考點內容不一，沒有統一的面試模板，都是面試官隨機出題考察，通常會以實際案例形式給出。窗口函數在HiveSql的實際使用中能夠實現各種複雜查詢且語法結構簡潔高效。所以強烈建議大家一定要多加練習，本公眾號之前有一篇專門寫Hive窗口函數的文章《Hive窗口函數使用指南》，講解細緻且示例很多，大家可自行查閱一下。
小結
本文將SQL/SparkSql/HiveSql放在一起梳理了一份常見題型的面試題庫。每一小節前都給出了考點大綱，依據大綱，下面都會出幾道模擬考題，每一個考題的講解都儘量全面和詳細，以帶大家將相關知識點溫習一遍，所選考題只是作為典型樣例，其他類推知識考點或周邊知識細節還需要大家再多加練習和總結，唯有充足的準備才能如願找到理想的工作，希望本文對你有一定的幫助哦。以上就是今天的內容了，希望你能夠關注、點讚、轉發一鍵三連支持一下。
需要完整學習線路和配套課堂筆記，請回復111。