數據分析面試必考—SQL 快速入門寶典

1SQL 簡介

SQL 全稱 Structured Query Language，說人話就是結構化查詢語言。毫不誇張地說，它是數據分析必會技能 Top1，因爲沒有哪個初級數據分析師的面試能跨過 SQL 技能考覈這一項的。

那麼爲了更好的理解 SQL 是什麼，與其說它是結構化查詢語言，不如稱之爲查詢結構化數據的機器語言。雖然隨着發展，SQL 的功能已經不限於查詢，但是查詢語句永遠是它的核心。本篇 ZZ 和大家分享一下他的查詢語句 SELECT，供大家快速入門 SQL。本篇內容框架如下：

2 查詢框架

數據分析的第一步是獲取數據，在成熟的公司體系中，數據的採集和儲存一般有專門的部門來負責，他們可能有不同的名字，例如數據中臺，數據倉庫等等。數據分析作爲數據的消費者，屬於數據採集和存儲部門的下游。

所以數據分析師主要需要從儲存數據的結構化的數據庫中提取數據進行分析挖掘，那麼工作中使用最多的語句就是 SELECT。舉個例子，"SELECT" 於分析師而言就像 "答" 對於數學考場上的考生一樣，管他什麼需求，上來就是一個 SELECT 準沒錯。

對於 SQL 快速入門而言，初學者主要關注三方面的 SQL 操作即可應付 90% 的工作，即聚合函數、表連接和窗口函數。

在介紹這些之前，ZZ 首先給出 SQL 操作的一般框架，應用此框架可以應對 100% 的查詢任務，所謂框架已有，胸有成竹。

select
    [colName_1],
    [colName_2],
    ...
    [colName_n]
from
    [tableName]
where
    [condition]
group by 
    [colName]
order by 
    [colName]
limit [N]

對於本框架的作用，舉個例子，相當於面臨語文閱讀題時，回答某個修辭方法的作用：應用了 XX 的修辭方法，生動形象的描述了 XX 的場景，抒發了作者 XX 的思想感情。

簡單來說，就是萬能的模板，上來就把這個框架 CV(Ctrl+C，Ctrl+V) 上，再修飾 "[]" 內的內容即可。

簡單解釋一下這個框架，紫色部分爲關鍵字，分別代表:

select-查詢
from-從
where-哪裏(滿足XX條件的)
group by-依據XX分組
order by-依據XX排序
limit-限制N條

連起來讀就是從 XX 表中查詢滿足 XX 條件的 XX 列，結果依據 XX 分組，依據 XX 排序，限制返回 N 條。

當然使用框架時依據實際情況靈活將 XX 換成實際需求的字段，並且這些關鍵字不是必須都存在的，如只有 select 和 from，讓機器知道從哪查詢 XX 也是可以的。下面基於此框架進行進階學習 SQL 查詢三劍客：聚合函數、表連接和窗口函數。

2.1 聚合函數

聚合函數指依據某個規則做合併運算的一系列操作，通俗來講就是把一列數聚合爲一個數的操作，包括求和運算，平均值運算，最大最小值運算，分位點計算等等；

爲什麼聚合函數比較常用呢？就像講 EXCEL 的文章一定要講數據透視表一樣，數據的基本統計數據 (例如統計學的五數) 是分析師最先關注也是最容易獲取的有價值的數據。學習 SQL 的聚合函數，我們可以通過對比 EXCEL 的數據透視表來學習。因爲 ZZ 看來 SQL 的聚合函數類似 EXCEL 的數據透視表。

那麼使用聚合函數需要注意三要素：聚合函數 + group by 關鍵字 + having 關鍵字。

（1）聚合函數

聚合函數就是我們希望聚合的方式，例如求和 sum()、求平均值 avg()，計數 count()等等，括號裏面的參數就是我們希望計算的目標列，聚合函數具體都有什麼，ZZ 這裏不一一列舉，需要時查一下即可，ZZ 只談漁不授魚。聚合函數類似 EXCEL 數據透視表中 “值” 的部分。

（2）group by 關鍵字

group by 關鍵字用於指定依據哪些列計算聚合值，爲什麼要存在 group by 關鍵字呢？是爲了更方便的從多維度來呈現數據，以一個學校的成績單爲例，大家不會只關注這個學校的平均成績，我們可能關注的是該學校不同年級，不同科目，不同班級的平均成績，那麼在這個例子中，年紀，科目，班級字段將在 group by 關鍵字後設置。group by 關鍵字類似於 EXCEL 透視表中的 “行” 和“列”的部分。

（3）having 關鍵字

having 關鍵字用於篩選出聚合值滿足一定條件的數據項，例如我們計算每個同學的語數外三科成績的平均值，同時想限制平均成績 >= 60 分，即可使用 having 關鍵字篩選：

having avg(score) >= 60

注意，這裏的 having 篩選與 EXCEL 透視表的篩選並不是一個功能，having 是對聚合值的篩選，EXCEL 透視表的篩選是對字段的值的篩選，這與 SQL 中的 where 關鍵字實現了一樣的功能。總的來說，使用聚合函數的三要素是聚合函數 + group by 關鍵字 + having 關鍵字。

這裏聚合函數是必須存在的。另外還有一點需要注意的是：當計算聚合值時，與聚合值無關的字段不可以出現在 SElECT 關鍵字下。因爲這會導致一對多，SQL 邏輯混亂的情況；我們應用上面說的萬能框架 + 聚合三要素給出一段聚合函數的使用案例：

需求：使用聚合函數實現提取在 XX 學校 XX 年紀 XX 班同學中，語數外三門成績最小值大於等於 60 分所有同學姓名

一、萬能框架：

select
    studentName
from
    scoreTable
where
    school = 'XX學校'
    grade = 'XX班'
group by 
    studentName

二、聚合三要素：

1. 聚合函數：min(score)
2.group by 關鍵字：group by studentName
3.having 關鍵字：having min(score) >= 60

三、組合最終 SQL

select
    studentName
from
    scoreTable
where
    school = 'XX學校'
    grade = 'XX班'
group by 
    studentName
having min(score) >= 60

2.2 表連接

對於單表簡單操作，上面的萬能框架 + 聚合函數基本可以涵蓋，但是在實際工作中，由於業務複雜性，不可能所有數據都放在一張數據表中，這樣會造成資源的浪費。所以我們必須掌握的第二個 SQL 操作就會聯表查詢。

記得我們分享 EXCEL 應用時，第二項技術是 VLOOKUP 和 MATCH(INDEX)，這項實用的 EXCEL 技巧映射到 SQL 操作就是表的連接。

聯表查詢結合萬能框架就特別容易學習，因爲聯表查詢就是兩個萬能框架中間加一行連接語句以及末尾加一個連接條件。

連接條件較爲簡單，這裏首先說明，即兩個表連接在一起時需要滿足的條件，一般爲兩個表中對應字段的值相等；

對於表的連接語句有四種：內連接 inner join、全連接 full join、左連接 left join、右連接 right join。我們來簡單理解一下：

內連接：基於連接條件，兩表都存在的數據將被提取出來顯示在同一行；
全連接：基於連接條件，兩表都存在的數據將被提取出來顯示在同一行，其他數據也會被提取出來，缺失部分使用空值填充；
左連接：基於連接條件，左表數據作爲標準，右表也存在的數據將與左表顯示在同一行，否則將使用空值填充；
右連接：基於連接條件，右表數據作爲標準，左表也存在的數據將與右表顯示在同一行，否則將使用空值填充；

內連接和全連接是兩個極端，內連接是兩者均有才會返回，全連接是不管兩者有沒有，所有數據都要返回，存在匹配成功就放在同一行的形式；

左連接和右連接看起來有一者是多餘的，因爲我們可以使用左連接和右連接中的任意一個，同時通過調整表查詢的順序來實現左、右連接的功能，那麼爲什麼還會存在這樣多餘的函數呢？因爲在 SQL 的查詢機制中，前面的表 (左表) 較小時，查詢的效率更高；由於這個潛規則，我們放置數據表的順序被限制後，才需要這兩個不同的連接來實現不同的左、右連接的功能。

下面舉個例子來一網打盡表的連接操作：

兩張數據表：

1、某班同學信息表：studentInfo
字段：name、studentID、sex
2、該班同學期末成績表：studentScore
字段：studentID、Math、Chinese、English

需求：該班男生的數學平均值

一、萬能框架 * 2

(
select
    studentID
from
    studentInfo
where
    sex = '男'
group by 
    studentID
) boyID

(
select
    studentID,
    Math
from
    studentScore
group by 
    studentID,
    Math
) mathScore

二、表的連接

(
select
    studentID
from
    studentInfo
where
    sex = '男'
group by 
    studentID
) boyID
left join --連接語句，使用左連接，左表是我們關心的中心對象：男同學們
(
select
    studentID,
    Math
from
    studentScore
group by 
    studentID,
    Math
) mathScore
on boyID.studentID = mathScore.studentID -- 連接條件

三、聚合求解

select
    avg(mathScore.Math) as avgBoyMathScore
from
(
select
    studentID
from
    studentInfo
where
    sex = '男'
group by 
    studentID
) boyID
left join --連接語句，使用左連接，左表是我們關心的中心對象：男同學們
(
select
    studentID,
    Math
from
    studentScore
group by 
    studentID,
    Math
) mathScore
on boyID.studentID = mathScore.studentID -- 連接條件

2.3 窗口函數

以上我們學習了 SQL 的兩個基本操作，聚合函數和表的連接。面對基礎的任務，我們已經可以滿足。但是爲了展示出我們的專業素質，有必要學習一下面對一些複雜的統計任務時比較常用的窗口函數。

理解窗口函數，我們首先從字面上理解，顧名思義，這是一個實現在滑動窗口上統計值的操作。何爲一個滑動的窗口，即一個小區間，這個小區間可以是固定長度，也可以是可變長度的。

爲什麼會有窗口函數呢？回憶我們之前介紹的聚合函數，它實現了依據某些維度計算某列聚合值的需求，但是如果想更具體的，需要統計某些維度上某些小區間上的聚合值時，聚合函數顯得無能爲力.

另外，我們介紹了在使用聚合函數時，與聚合列無關的列不可以出現在 SELECT 關鍵字下，如果想要除了聚合列之外的其他明細數據和聚合值同時提取時，聚合函數又不太行了。那麼基於以上的原因呢，以更靈活的設置小區間的方式來計算統計值的窗口函數應運而生，ZZ 總結窗口函數主要有以下兩個方面的應用，（首先統一說明，over 關鍵字是窗口函數的標誌），在某個小區間上：

（1）滑動窗口分組

在已有維度不能滿足分析需求時，設置一個滑動窗口，來靈活設置統計區間。設置一個滑動窗口來實現統計值的跨度，即設置當前統計值是從第幾行計算到第幾行，例如計算移動平均值，累計值等等；

滑動窗口設置方式十分簡單，關鍵字 OVER + 關鍵字 ROWS + 計算區間：

OVER(ROWS BETWEEN "起始行" AND "結束行")

其中起始行和結束行主要有以下的表示方式：

CURRENT ROW --當前行
UNBOUNDED PRECEDING --窗口內第一行
UNBOUNDED FOLLOWING --窗口內最後一行
[N] PRECEDING --當前行向前N行
[N] FOLLOWING --當前行向後N行

有了以上 5 種表示方式，那麼任意的窗口都可以設置了。默認情況是第一行到當前行，主要應用於計算累積值：

-- 月份month從小到大排序，計算第一個月到當前月的累積銷售額
sum(sales) over(order by month) 
-- 月份month從小到大排序，計算第一個月到當前月的累積平均銷售額
avg(sales) over(order by month)

通過設置窗口計算移動平均值

-- 計算股票的250日均線 
-- IOPV:單位淨值
avg(IOPV) over(order by day ROWS BETWEEN 249 PRECEDING AND CURRENT ROW)

（2）明細 & 聚合

已有維度不能滿足分析需求時，設置滑動窗口可以靈活的開發出一些額外的維度。但是當已有維度已經能滿足需求時，窗口函數就沒有用武之地了嗎？恰恰相反，其中一種情況就是當前維度足夠時，我們同時想輸出明細值和聚合值時，窗口函數就又派上用場：同時輸出明細值和聚合值。

# 統計各年級同學每個人的數學成績和各年級的數學平均分
select 
    grade,
    studentName,
    Math,
    avg(Math) over(partition by grade) as gradeAvgMath
from
    [tableName]
where
    [condition]
group by 
    [colName]
order by 
    [colName]

（3）分區排序

上面介紹在當前維度足夠時，窗口函數的一種應用是明細 & 聚合值一起出來。但這個聚合值可以發生一些變化，演變爲排序值，即窗口函數另一應用：分區排序 (明細 & 次序)。類似於明細 & 聚合操作，我們直接來看一個例子

統計各年級同學每個人的數學成績並根據成績由大到小排序，注意，各年級分開排序

select 
    grade,
    studentName,
    Math,
    RANK() over(partition by grade order by Math desc) as gradeMathOrder
from
    [tableName]
where
    [condition]

分區排序函數詳細區分：

RANK() Over(partition by order by )  -- 非稠密排序：1、1、3、3、5
DENSE_RANK() Over(partition by order by )  --稠密排序：1、1、2、2、3
ROW_NUMBER() Over(partition by order by )  --純數排序：1、2、3、4、5

（4）其他

窗口函數還有一些輸出窗口內第一個值和最後一個值的操作，雖然 ZZ 感覺這個挺多餘的，但是還是列一下，省的大家以爲我不知道似的。

# FIRST_VALUE() 和LAST_VALUE()，返回窗口的第一個和最後一個值:
FIRST_VALUE(SUM(amount)) OVER (ORDER BY month) 
LAST_VALUE(SUM(amount)) OVER (ORDER BY month)

3 小結

ok，最後我們來總結一下。

當我們拿到一個取數的需求時，首先列出我們的萬能框架，有了框架之後，就有了思路。

如果需要在某個或者多個維度進行聚合（例如求和，平均值，最大最小值，分位點），這時候需要使用我們的聚合函數，然後注意將這些維度放進 group by 關鍵字之後即可；

如果業務比較複雜，單從一個表中無法提供所有的字段，這個時候需要進行表連接，根據之前介紹的不同連接方式的區別即可選擇對應錶鏈接方式；

如果業務更復雜一些，比如需要計算移動平均值，分組排序，以及同時想看明細和聚合值得情況下，就需要用到強大的窗口函數了。

掌握以上內容，足以應對數據分析師在日常工作中所面臨的所有取數的需求，更小的一些細節比如字符串和日期格式的處理，需要用的時候直接百度就可以了。

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/P2hXgh5NYPmEupD-uHES5Q

猜你喜歡