SQL初級數據分析（基於Microsoft Access）

一、資料庫與SQL

1、資料庫介紹

資料庫（Database）是按照數據介面來組織、存儲和管理數據的倉庫。通過資料庫中的各種對象，進行記錄、處理、分析各種數據。

常用資料庫：

• Oracle
• SQL Server
• MySQL
• Access

2、Access資料庫

2.1、Microsoft Office辦公軟體重要組成部分，一種關係數據管理系統軟體。

存儲、處理、分析數據，數據處理功能更強大，數據量較大（大於幾十萬）時使用。

2.2、優缺點

• 優點：操作界面熟悉且友好，容易操作；查詢處理可以直接生成相應的SQL語句；適合資料庫入門
• 缺點：資料庫過大（Access文件大於100M）性能會變差；每個資料庫文件最大上限為2G

2.3、六個對象：

• 表：存儲數據
• 查詢：查找數據
• 窗體：獲取數據
• 報表：獲取數據
• 宏：自動化操作
• 模塊：自動化操作

3、SQL簡介

3.1、什麼是SQL

SQL（Structured Query Language）結構化查詢語言，一種通用的關係型資料庫操作語言，用於存取數據、查詢、更新和管理資料庫。

SQL的本質是讓資料庫根據用戶要求實現查詢操作，數據查詢是資料庫的核心操作。

SQL是各類資料庫語言的基礎。

3.2、SQL常用語句

3.3、SQL語法

1）SELECT語句是SQL查詢語言中的核心語言，能根據指定的條件規則從資料庫中查詢所要的數據。

2）基本語法：

SELECT 欄位1,欄位2,欄位3,......FROM 表WHERE 條件

條件是非必須的語句，但一般情況下都會進行具有某種條件的查詢。

3）SQL語句示例：

SELECT 姓名,性別,年齡,身高,月收入 #選擇5個欄位 FROM 會員表 #數據表名稱 WHERE 性別=男 AND 年齡 BETWEEN(26,30)AND 身高BETWEEN(170,180) AND 月收入>=8000 ORDER BY 月收入DESC; #降序排序

4）SQL語句注意事項

• SQL語句中，英文字母大寫或小寫均可；
• 每個SQL語句的關鍵字用空格符號分隔，例：SELECT 欄位 FROM 表；
• 欄位或參數之間用英文逗號分隔，例：SELECT 姓名,性別,年齡,身高 FROM 會員表
• SQL語句中如參數為字元型，需要使用英文單引號，數值型則不使用，例：SELECT 姓名,性別,年齡,身高,月收入 FROM 會員表 WHERE 性別=男;
• SQL語句結束時，在語句結尾處添加英文分號（雖然在Access資料庫中無強制要求，但為了養成良好編程習慣建議一定添加，避免出錯）
• Access資料庫SQL語句中，如表名、欄位名中出現空格、"/"、""等特殊字元時，需要用方括弧"[]"將還有特殊字元的表名或欄位名括起來，以免得到不正確的結果或SQL語句無法運行；
• SQL語句中，"*"代表選定數據表中的所有欄位，並且按照其在資料庫中的固定順序來顯示，例：SELECT *
  FROM 表;
• Access資料庫，在函數參數中或條件查詢中，若參數或查詢條件為日期和時間類型數據，需要在數據值兩端加上井字元號（#），以表示數據類型為日期型；
• SQL語句中使用的逗號、分號、單引號、括弧等符號均為英文符號；
• 應盡量避免在資料庫中進行全表掃描：首先應考慮用WHERE子句篩選出需要的數據；其次在WHERE子句中，應盡量避免使用"!="或"<>"、"OR"等；最後應盡量避免在WHERE子句中對欄位進行函數操作，否則將進行全表掃描；

二、數據處理

1、數據導入

創建Access資料庫

• Excel數據導入
• 文本數據導入

2、創建查詢

• 查詢設計：點擊右下角SQL按鈕可以得到SQL語句
• SQL視圖

數據表表名有特殊字元需要用方括弧括起來，還可以用"as"重新命名，如：

3、數據抽取

3.1、欄位拆分

截取某一欄位的部分信息形成一個新欄位，比如提取身份證號碼的不同欄位。

提取方式同Excel，使用函數LEFT、RIGHT、MID，公式如下：

• LEFT(字元串,提取的位數)
• RIGHT(字元串,提取的位數)
• MID(字元串,提取的起始位置,提取的位數)

以提取身份證號碼為例：

SELECT 身份證號碼,LEFT(身份證號碼,2) as 省份編碼,MID(身份證號碼,7,4) as 年,MID(身份證號碼,11,2) as 月,MID(身份證號碼,13,2) as 日,MID(身份證號碼,17,1) as 性別FROM 用戶明細;

3.2、記錄抽取

根據指定的條件對數據表中的數據記錄進行篩選，抽取出符合條件的數據記錄。

使用篩選功能，在SQL中主要使用WHERE子句，舉例見第一部分：資料庫與SQL-3.3-3。

WHERE 條件表達式

以下是相關案例：

案例1

SELECT 用戶ID,年齡,性別 FROM 用戶明細 WHERE 年齡>=30 AND 年齡<=32 AND 性別=『男;

案例2

SELECT 用戶ID,年齡,性別 FROM 用戶明細 WHERE 年齡 BETWEEN 30 AND32 AND 性別=男;

案例3

SELECT 用戶ID,年齡,性別 FROM 用戶明細 WHERE 年齡 BETWEEN 30 AND32 AND 性別 IN (男』);

案例4

SELECT 用戶ID,年齡,性別 FROM 用戶明細 WHERE 年齡 BETWEEN 30 AND32 AND 性別 NOT IN (女);

案例5

SELECT 用戶ID,身份證號碼 FROM 用戶明細 WHERE 身份證號碼 LIKE *A;

案例6

SELECT 用戶ID,身份證號碼 FROM 用戶明細 WHERE 身份證號碼 IS NULL;

3.3、隨機抽樣

按照隨機的原則，即保證總體中的每個單位都有同等機會被抽中，進行抽取樣本的一種方法。

應用：調查用戶行為異常；數據挖掘建模

函數RND進行隨機抽樣，該函數只有一個參數，參數必須是數值型數據；如果參數是字元型，可以用函數LEN計算字元型長度，轉換為數值型數據。

例如，隨機抽取用戶明細表中20個用戶

SELECT TOP 20* #提取20條記錄，包含數據表中的所有欄位 FROM 用戶明細 ORDER BY RND(用戶ID); #對用戶ID進行排列，ASC為升序，DESC為降序排列，不寫默認為升序排列

4、數據合併

綜合不同的原數據表中某幾個欄位的信息或不同記錄數據，組合成一個新欄位或新記錄數據，包括欄位合併、欄位匹配、記錄匹配。

4.1、欄位合併

將某幾個欄位合併成一個新欄位，比如將之前身份證號碼中提取的年、月、日合併成出生日期欄位。

連接符：&、+

SELECT 身份證號碼, (MID(身份證號碼,7,4)&-&MID(身份證號碼,11,2)&-&MID(身份證號碼,13,2)) AS 出生日期 FROM 用戶明細;

以上代碼生成的欄位是字元型，不是日期型；可以使用函數DateSerial直接生成日期型欄位，與Excel中函數Day一致。

函數DateSerial，返回包含指定的年、月、日的日期；公式：DateSerial(year,month,day)

SELECT 身份證號碼, DateSerial(MID(身份證號碼,7,4),MID(身份證號碼,11,2),MID(身份證號碼,13,2)) AS 出生日期 FROM 用戶明細;

4.2、欄位匹配

根據各表共有的關鍵欄位把各表所需的記錄一一對應，相當於Excel中的函數VLOOKUP的精確匹配功能。

資料庫連接關係

• 內連接（INNER JOIN）：選擇兩個表中關鍵欄位相匹配的記錄
• 左連接（LEFT JOIN）：選擇第一個表中的所有記錄以及第二個表中關鍵欄位相匹配的記錄
• 右連接（RIGHT JOIN）：選擇第二個表中的所有記錄以及第一個表中關鍵欄位相匹配的記錄

在Access的資料庫工具中創建關係，找到關鍵欄位，選擇需要的聯接類型。

左連接與右連接的原理一致，左連接經常用來計算用戶留存率、再購買率等。

4.3、記錄合併

將具有共同的數據欄位、結構，但記錄信息不同的數據表合併到一個新的數據表。

在SQL中使用UNION或UNION ALL合併兩表或多表。

*UNION會刪除各表中的重複記錄，並進行排序，使用時要慎重；UNION ALL則不做重複數據處理。

SELECT * FROM (SELECT * FROM 訂購明細20110901 UNION ALL SELECT * FROM 訂購明細20110902);

如果需要保存新合併的數據表，將合併查詢結果插入一個新建相同欄位、結構的空表中。

SELECT * INTO 訂購明細201109 FROM 訂購明細20110901 WHERE 1=2;

*上面WHERE條件1=2永遠不成立，因此插入0條記錄，就相當於新建了一個相同欄位、結構的空表。

將合併查詢的記錄插入至剛才新建的空表中。

INSERT INTO 訂購明細201109 SELECT * FROM (SELECT * FROM 訂購明細20110901 UNION ALL SELECT * FROM 訂購明細20110902);

5、數據去重

保留唯一的數據記錄，刪除其他多餘的重複記錄。

在SQL中常用的兩種數據去重方式為：GROUP BY子句和DISTINCT

5.1、GROUP BY子句

對數據按指定的分組欄位進行分組，相當於EXCEL透視表中的行標籤分組功能。分組欄位可以有多個，即多條件去重，同時滿足才去重，用英文逗號分隔。

SELECT 用戶ID FROM 用戶明細重複 GROUP BY 用戶ID;

5.2、DISTINCT

忽略所選欄位中包含重複數據的記錄，數據去重。同理，欄位可以有多個，即多條件去重，同時滿足才去重，用英文逗號分隔。

SELECT DISTINCT 用戶ID,註冊日期,身份證號碼,性別,年齡 FROM 用戶明細;

6、數據分組

重要的數據分析方法，根據數據分析對象特徵，按照一定的指標，如業務、用戶屬性、時間等維度，把數據分析對象劃分為不同的部分和類型進行研究，以揭示其內在的聯繫和規律性。

常用的數據分組方式包括數值分組和日期分組。

6.1、數值分組

函數IIF，與Excel中的函數IF用法一致；在Access資料庫中，函數IIF最多進行13層嵌套。

IIF(條件表達式,表達式成立返回的值,表達式不成立返回的值)

SELECT 用戶ID,年齡 IIF(年齡<=20,"20歲及其以下", IIF(年齡<=30,"30歲及其以下", IIF(年齡<=40,"31-40歲」,"40歲以上"))) AS 年齡分組 FROM 用戶明細;

6.2、日期分組

6.2.1、函數YEAR、MONTH、DAY

與Excel中的同名函數用法一致，分成年、月、日三個分組，前提是欄位必須為日期型數據。

SELECT 訂單編號,訂購日期, YEAR(訂購日期) AS 年, MONTH(訂購日期) AS 月, DAY(訂購日期) AS 日 FROM 訂購明細;

6.2.2、函數FORMAT

公式：FORMAT(日期/時間,日期/時間格式參數)

參數表如下

舉例如下：

SELECT 訂單編號,訂購日期, FORMAT(訂購日期,"yyyy")AS 年, FORMAT(訂購日期,"q")AS 季, FORMAT(訂購日期,"m")AS 月, FORMAT(訂購日期,"d")AS 日, FORMAT(訂購日期,"dddd")AS 星期, FORMAT(訂購日期,"h")AS 小時, FORMAT(訂購日期,"n")AS 分, FORMAT(訂購日期,"s")AS 秒 FROM 訂購明細;

7、數據計算

通過對原有欄位進行相應計算後得到新的欄位，以滿足數據分析需求，主要包括簡單計算和函數計算。

7.1、簡單計算

通過「加、減、乘、除」的方法得到新的欄位

SELECT 訂單編號, 產品, [單價(元)], 數量, 訂購金額, [數量]*[單價(元)] AS 訂單金額 FROM 訂購明細;

7.2、函數計算

通過內置的函數進行計算，比如求和、平均、最大值、最小值等。

以介紹函數DATEDIFF為例

公式：DATEDIFF("參數",起始日期,結束日期)

與Excel中的函數DATEDIFF一致，但用法略有不同，不同之處：日期間隔的參數在SQL中為公式中的第一個變數。

參數表如下

舉例如下：

SELECT 用戶ID,註冊日期, DATEDIFF("D",註冊日期,#2015-2-14#)AS 註冊天數 FROM 用戶明細;

*在SQL中，如果函數參數或條件查詢為日期或時間類型數據，需要在數據兩端加上#符號以表示該數據欄位為日期型。

三、數據分析

數據處理就是給數據分析做好準備工作，數據處理的目的將採集到的數據用適當的處理方法進行整理加工，形成適合數據分析的要求樣式（即一維表）。

數據分析就是通過對比與細分進行現狀分析和原因分析，可通過數據分組了解其數據構成，通過不同時間維度的對比查找數據變化的原因。

數據分析通常包含簡單統計、分組統計、交叉表統計等常用方法。

3.1、簡單統計

指計數、求和、平均等常用簡單統計分析，用於反映描述事物的整體情況。

舉例，SQL代碼如下：

SELECTCOUNT(訂單編號) AS 訂單總數,SUM(訂購金額) AS 訂購金額總額,AVG(訂購金額) AS 平均訂單金額FROM 訂購明細;

3.2、分組分析

根據分析對象的某種特徵，把分析對象劃分為不同的部分以進行對比分析研究，揭示其內在的聯繫和規律性。

分組的目的是進行各組之間對比分析，研究數據分析對象的結構構成和分布特徵。

分組類型主要有定量和定性兩大類。定量分組就是數據分組（數值分組和日期分組）；定性分組就是按照事物的已有類別、屬性劃分，如性別、學歷、地區等。

前面數據去重小節中的GROUP BY子句的作用就是對數據按指定的分組欄位進行分組，相當於Excel透視表中的行標籤。編寫SQL語句時將GROUP BY放置在FROM語句之後，添加分組欄位。

定性分組舉例：

SELECT 產品,COUNT(訂單編號) AS 訂單總數,SUM(訂購金額) AS 訂購金額總額,AVG(訂購金額) AS 平均訂單金額FROM 訂購明細GROUP BY 產品;

定量分組-數值分組舉例：

SELECT IIF(年齡<=20,"20歲及其以下",IIF(年齡<=30,"30歲及其以下",IIF(年齡<=40,"31-40歲」,"40歲以上"))) AS 年齡分組,COUNT(用戶ID) AS 用戶數FROM 用戶明細GROUP BY IIF(年齡<=20,"20歲及其以下",IIF(年齡<=30,"30歲及其以下",IIF(年齡<=40,"31-40歲」,"40歲以上")));

定量分組-日期分組舉例：

SELECT FORMAT(註冊日期,"m")AS 月,COUNT(用戶ID) AS 用戶數FROM 用戶明細GROUP BY FORMAT(註冊日期,"m");

3.3、排序分析

根據分析對象，按數值大小（分組統計的基礎上）進行升序、降序排列。排序的目的是更方便的進行對比分析，以便重點突出前幾名和後幾名。雖然簡單基礎，但非常實用，且容易被忽視。主要用於用戶偏好分析，如銷售排行榜、下載排行榜。

*註：如果數據沒有數值分布順序、時間順序，建議按數值大小排序。

二八分析法就是排序分析的進一步擴展，以帕累托圖的方式呈現。帕累托圖是按照問題發生頻率的高低順序繪製的直方圖，可用來分析質量問題，尋找影響質量問題的主要因素。

舉例：

SELECT 產品,COUNT(訂單編號) AS 訂單總數FROM 訂購明細GROUP BY 產品ORDER BY COUNT(訂單編號) DESC;

3.4、結構分析

結構分析法是在分組的基礎上，計算各組成部分所佔比重，進而分析總體的內部結構特徵的分析方法。該方法應用廣泛，比如市場佔有率。分組主要為定性分組，定性分組一般看結構，重點在於佔比。

舉例：

SELECT 產品,COUNT(訂單編號)/(SELECTCOUNT(訂單編號) FROM 訂購明細) AS 佔比FROM 訂購明細GROUP BY 產品;

其中SELECT COUNT(訂單編號) FROM 訂購明細為嵌套子查詢，計算總銷量數，用於分母。

以上代碼得出的結果為小數而非百分比，如果讓結果為百分比，需使用FORMAT函數轉換，代碼如下

SELECT 產品,FORMAT(COUNT(訂單編號)/(SELECTCOUNT(訂單編號) FROM 訂購明細),"0.0%") AS 佔比FROM 訂購明細GROUP BY 產品;

3.5、分布分析

在定量分組的基礎上查看數據的分布情況，其橫坐標軸不能改變順序，即不能按數值的大小排序，否則無法分析研究分布規律。應用廣泛，用戶消費分布、收入分布、年齡分布等。

下面對用戶年齡進行分布分析的例子中還綜合運用了SQL的分組、嵌套、計數、內連接（欄位匹配）、去重等五個功能，代碼如下：

SELECT IIF(B.年齡<=20,"20歲及其以下",IIF(B.年齡<=30,"30歲及其以下",IIF(B.年齡<=40,"31-40歲」,"40歲以上"))) AS 年齡分組,COUNT(A.用戶ID) AS 購買用戶數FROM(SELECT 用戶ID FROM 訂購明細 GROUP BY 用戶ID) AS A,用戶明細 AS BWHERE A.用戶ID=B.用戶IDGROUP BY IIF(B.年齡<=20,"20歲及其以下",IIF(B.年齡<=30,"30歲及其以下",IIF(B.年齡<=40,"31-40歲」,"40歲以上")));

因為訂購明細表中存在大量同一用戶ID重複購買的情況，所以將訂購明細表重命名為A表之前對用戶ID數據去重；將用戶明細重命名為B表，並與A表通過欄位用戶ID建立內連接；最後根據用戶年齡進行分組分析。

*註：涉及到用戶、產品、渠道等相關統計時，要特別注意考慮數據重複的問題；掌握好SQL基礎功能語句，靈活地組合運用。

3.6、交叉分析

用於兩個或兩個以上分組變數之間的關係，以交叉表形式對比分析變數間關係。交叉分析的原理是從數據的不同維度綜合分組細分，以進一步了解數據的構成分布特徵。

• 定量、定量分組交叉
• 定量、定性分組交叉
• 定性、定性分組交叉

建議最多兩個維度，維度越多越沒有重點，越難發現規律。在Access中，可先選擇交叉表查詢嚮導創建交叉表，再點擊SQL視圖查看SQL語句：

TRANSFORM COUNT(用戶ID) AS 用戶數SELECT 年齡FROM 用戶明細GROUP BY 年齡PIVOT 性別;

其中TRANSFORM COUNT(用戶ID) AS 用戶數這行代碼是增加交叉表每個行和列的交叉點統計函數及欄位，GROUP BY 年齡設置行標籤，PIVOT 性別設置列標籤。

3.7、留存分析

以留存率衡量分析用戶的質量，留存率可理解為留存下來的老用戶與總用戶訪問量之間的比率。主要應用於網站分析、電商分析、遊戲分析等，一般分為次日、3日、7日、14日、30日留存率，次日留存率最常用。與留存率類似的指標還有再付費率、再充值率等。

次日留存率的示意圖如下

在Access中，首先以欄位用戶ID創建兩個表的關係，在選擇兩個用戶ID欄位，運行後查詢SQL語句，最後添加COUNT計數函數修改SQL，並進行留存率計算。

3.8、矩陣分析

根據事物的兩個重要屬性作為分析依據進行關聯分析，找出解決問題辦法的分析方法。

舉例，根據年齡、消費兩個維度，分析各省份購買用戶質量。

首先，統計各省份購買用戶平均訂購金額

SELECT LEFT(B.身份證號碼,2) AS 省份編號,AVG(A.訂購金額) AS 平均訂購金額FROM 訂購明細 AS A, 用戶明細 AS BWHERE A.用戶ID = B.用戶IDGROUP BY LEFT(B.身份證號碼,2);

運行後得到的數據是為省份編碼，導入省份編碼表後（導入時數據類型選擇短文本，需要與LEFT函數得到的省份編碼數據類型一致），與另外兩個表進行關聯，修改SQL語句，運行後可以得到省份名稱。

SELECT C.省份,AVG(A.訂購金額) AS 平均訂購金額FROM 訂購明細 AS A, 用戶明細 AS B, 省份 AS CWHERE A.用戶ID = B.用戶IDAND C.省份編碼 = LEFT(B.身份證號碼,2)GROUP BY C;

接下來，統計各省份購買用戶平均年齡，注意考慮數據重複的問題

SELECT C.省份, AVG(B.年齡) AS 平均年齡FROM(SELECT 用戶ID FROM 訂購明細 GROUP BY 用戶ID) AS A,用戶明細 AS B, 省份 AS CWHERE A.用戶ID = B.用戶IDAND C.省份編碼 = LEFT(B.身份證號碼,2)GROUP BY C;

最後根據得到的各省份購買用戶平均訂購金額、各省份購買用戶平均年齡兩個數據表，使用Excel便可製作矩陣圖。