摘要：採用 SQL 作為資料查詢和分析的入口是一種資料全棧的思路。
本文分享自華為雲社群《如何使用 SQL 對資料進行分析？》，作者：zuozewei 。
前言
我們通過 OLTP（聯機事務處理）系統實時處理使用者資料，還需要在 OLAP（聯機分析處理）系統中對它們進行分析,今天我們來看下如何使用 SQL 分析資料。
使用 SQL 進行資料分析的幾種方式
在 DBMS(資料庫管理系統) 中，有些資料庫很好地整合了 BI 工具，可以方便我們對收集的資料進行商業分析。
比如在SQL Server 中提供了 BI 分析工具，我們可以通過使用 SQL Server中的 Analysis Services 完成資料探勘任務。SQL Server 內建了多種資料探勘演算法，比如常用的 EM、K-Means 聚類演算法、決策樹、樸素貝葉斯和邏輯迴歸等分類演算法，以及神經網路等模型。我們還可以對這些演算法模型進行視覺化效果呈現，幫我們優化和評估演算法模型的好壞。
圖片來源：：https://docs.microsoft.com/en-us/analysis-services/analysis-services-features-supported-by-the-editions-of-sql-server-2016
另外 PostgreSQL 是一個免費開源的關聯式資料庫（ORDBMS），它的穩定性非常強，功能強大，在 OLTP 和 OLAP 系統上表現都非常出色。同時在機器學習上，配合 Madlib 專案可以讓 PostgreSQL 如虎添翼。Madlib 包括了多種機器學習演算法，比如分類、聚類、文字分析、迴歸分析、關聯規則挖掘和驗證分析等功能。這樣我們可以通過使用 SQL，在 PostgreSQL 中使用各種機器學習演算法模型，幫我們進行資料探勘和分析。
圖片來源：https://cwiki.apache.org/confluence/display/MADLIB/Architecture
2018 年 Google 將機器學習（Machine Learning）工具整合到了 BigQuery 中，釋出了 BigQuery ML，這樣開發者就可以在大型的結構化或半結構化的資料集上構建和使用機器學習模型。通過 BigQuery 控制檯，開發者可以像使用 SQL 語句一樣來完成機器學習模型的訓練和預測。
SQLFlow 是螞蟻金服於 2019 年開源的機器學習工具，我們可以通過使用 SQL 就可以完成機器學習演算法的呼叫，你可以將 SQLFlow 理解為機器學習的翻譯器。我們在 SELECT 之後加上 TRAIN 從句就可以完成機器學習模型的訓練，在 SELECT 語句之後加上 PREDICT 就可以使用模型來進行預測。這些演算法模型既包括了傳統的機器學習模型，也包括了基於 Tensorflow、PyTorch 等框架的深度學習模型。
從上圖中你能看出 SQLFlow 的使用過程，首先我們可以通過 Jupyter notebook 來完成 SQL 語句的互動。SQLFlow 支援了多種 SQL 引擎，包括 MySQL、Oracle、Hive、SparkSQL 和 Flink 等，這樣我們就可以通過 SQL 語句從這些 DBMS 資料庫中抽取資料，然後選擇想要進行的機器學習演算法（包括傳統機器學習和深度學習模型）進行訓練和預測。不過這個工具剛剛上線，工具、文件、社群還有很多需要完善的地方。
最後一個最常用方法是 SQL+Python，也是我們今天要重點講解的內容。上面介紹的工具可以說既是 SQL 查詢資料的入口，也是資料分析、機器學習的入口。不過這些模組耦合度高，也可能存在使用的問題。一方面工具會很大，比如在安裝 SQLFlow 的時候，採用 Docker 方式進行安裝，整體需要下載的檔案會超過 2G。同時，在進行演算法調參、優化的時候也存在靈活度差的情況。因此最直接的方式，還是將 SQL 與資料分析模組分開，採用 SQL 讀取資料，然後通過 Python 來進行資料分析的處理。
案例：挖掘購物資料中的頻繁項集與關聯規則
下面我們通過一個案例來進行具體的講解。
我們要分析的是購物問題，採用的技術為關聯分析。它可以幫我們在大量的資料集中找到商品之間的關聯關係，從而挖掘出經常被人們購買的商品組合，一個經典的例子就是“啤酒和尿布”的例子。
今天我們的資料集來自於一個購物樣本資料，欄位包括了 trans_id（交易 ID）以及 product(商品名稱)，具體的資料集參考下面的初始化 sql：
DROP TABLE IF EXISTS test_data; CREATE TABLE test_data ( trans_id INT, product TEXT ); INSERT INTO test_data VALUES (1, 'beer'); INSERT INTO test_data VALUES (1, 'diapers'); INSERT INTO test_data VALUES (1, 'chips'); INSERT INTO test_data VALUES (2, 'beer'); INSERT INTO test_data VALUES (2, 'diapers'); INSERT INTO test_data VALUES (3, 'beer'); INSERT INTO test_data VALUES (3, 'diapers'); INSERT INTO test_data VALUES (4, 'beer'); INSERT INTO test_data VALUES (4, 'chips'); INSERT INTO test_data VALUES (5, 'beer'); INSERT INTO test_data VALUES (6, 'beer'); INSERT INTO test_data VALUES (6, 'diapers'); INSERT INTO test_data VALUES (6, 'chips'); INSERT INTO test_data VALUES (7, 'beer'); INSERT INTO test_data VALUES (7, 'diapers');
這裡我們採用的關聯分析演算法是 Apriori 演算法，它幫我們查詢頻繁項集，首先我們需要先明白什麼是頻繁項集。
頻繁項集就是支援度大於等於最小支援度閾值的項集，小於這個最小值支援度的專案就是非頻繁項集，而大於等於最小支援度的項集就是頻繁項集。支援度是個百分比，指的是某個商品組合出現的次數與總次數之間的比例。支援度越高，代表這個組合出現的頻率越大。
我們再來看下 Apriori 演算法的基本原理。
Apriori 演算法其實就是查詢頻繁項集 (frequent itemset) 的過程：
0.設定一個最小支援度，
1.從K=1開始，篩選頻繁項集。
2.在結果中，組合K+1項集，再次篩選
3.迴圈1、2步。直到找不到結果為止，K-1項集的結果就是最終結果。
我們來看下資料理解一下，下面是所有的訂單，以及每筆訂單購買的商品：
在這個例子中，“啤酒”出現了 7 次，那麼這 7 筆訂單中“牛奶”的支援度就是 7/7=1。同樣“啤酒 + 尿布”出現了 5 次，那麼這 7 筆訂單中的支援度就是 5/7=0.71。
同時，我們還需要理解一個概念叫做“置信度”，它表示的是當你購買了商品 A，會有多大的概率購買商品 B，在這個例子中，置信度（啤酒→尿布）=5/7=0.71，代表如果你購買了啤酒，會有 71% 的概率會購買尿布；置信度（啤酒→薯條）=3/7=0.43，代表如果你購買了啤酒，有 43% 的概率會購買薯條。
所以說置信度是個條件概念，指的是在 A 發生的情況下，B 發生的概率是多少。
我們在計算關聯關係的時候，往往需要規定最小支援度和最小置信度，這樣才可以尋找大於等於最小支援度的頻繁項集，以及在頻繁項集的基礎上，大於等於最小置信度的關聯規則。
使用 MADlib+PostgreSQL 完成購物資料的關聯分析
針對上面的購物資料關聯分析的案例我們可以使用工具自帶的關聯規則進行分析，下面我們演示使用 PostgreSQL 資料庫在 Madlib 工具中都可以找到相應的關聯規則，通過寫 SQL 的方式就可以完成關聯規則的呼叫分析。
開發環境
- Windows/MacOS
- Navicat Premium 11.2.7及以上
伺服器環境
- Centos 7.6
- Docker
- PostgreSQL 9.6
- MADlib 1.4及以上
使用 Docker 安裝 MADlib+PostgreSQL
拉取 docker 映象(這個映象提供了需要的 postgres 等環境,並沒有安裝 madlib) ：
docker pull madlib/postgres_9.6:latest
下載 MADlib github 原始碼. 假定下載的原始碼位置為 /home/git-repo/github/madlib：
cd /home/git-repo/github && git clone git@github.com:apache/madlib.git
啟動容器，並建立本機目錄與容器中系統的路徑對映，共享的目錄在容器和本機之間是讀寫共享的。
docker run -d -it --name madlib -v /home/git-repo/github/madlib:/incubator-madlib/ madlib/postgres_9.6
啟動容器後,連線容器編譯 MADlib 元件，編譯用時約 30 分鐘：
docker exec -it madlib bash mkdir /incubator-madlib/build-docker cd /incubator-madlib/build-docker cmake .. make make doc make install
在容器中安裝 MADlib：
src/bin/madpack -p postgres -c postgres/postgres@localhost:5432/postgres install
執行 MADlib 測試:
# Run install check, on all modules: src/bin/madpack -p postgres -c postgres/postgres@localhost:5432/postgres install-check # Run install check, on a specific module, say svm: src/bin/madpack -p postgres -c postgres/postgres@localhost:5432/postgres install-check -t svm # Run dev check, on all modules (more comprehensive than install check): src/bin/madpack -p postgres -c postgres/postgres@localhost:5432/postgres dev-check # Run dev check, on a specific module, say svm: src/bin/madpack -p postgres -c postgres/postgres@localhost:5432/postgres dev-check -t svm # 如果需要,重新安裝 Reinstall MADlib: src/bin/madpack -p postgres -c postgres/postgres@localhost:5432/postgres reinstall
如果需要，先關掉並刪除容器，刪完再起新容器需要重新安裝:
docker kill madlib
docker rm madlib
用配置好的容器製作新映象，先檢視容器 ID, 在用容器 ID 建立新映象：
docker ps -a
docker commit <container id> my/madlib_pg9.6_dev
用新映象建立新容器：
docker run -d -it -p 5432:5432 --name madlib_dev -v /home/my/git-repo/github/madlib:/incubator-madlib/ madlib/postgres_9.6
連線容器進行互動(發現新容器還是沒有安裝，但是不用編譯了，安裝也很快，裝完測試一下)
docker exec -it madlib_dev bash cd /incubator-madlib/build-docker src/bin/madpack -p postgres -c postgres/postgres@localhost:5432/postgres install src/bin/madpack -p postgres -c postgres/postgres@localhost:5432/postgres install-check
使用 Navicat 遠端連線 PostgreSQL(假定沒有修改登入使用者和密碼,預設沒有密碼)
最後，新建表並初始化資料：
使用 SQL 完成關聯規則的呼叫分析
最後使用 SQL + MADlib 進行關聯分析，這裡我們設定了引數最小支援度為 0.25，最小置信度為 0.5。根據條件生成 transactions 中的關聯規則，如下所示：
SELECT * FROM madlib.assoc_rules( .25, -- 支援度 .5, -- 置信度 'trans_id', -- Transaction id 欄位 'product', -- Product 欄位 'test_data', -- 輸入資料 NULL, -- 輸出模式 TRUE -- 詳細輸出 );
查詢結果：
關聯規則儲存在 assoc_rules 表中:
SELECT * FROM assoc_rules
ORDER BY support DESC, confidence DESC;
注意：
關聯規則會始終建立一個名為的表 assoc_rules。如果要保留多個關聯規則表，請在再次執行之前複製該表。
使用 SQL+Python 完成購物資料的關聯分析
除此以外，我們還可以直接使用 SQL 完成資料的查詢，然後通過 Python 的機器學習工具包完成關聯分析。
開發環境
- Windows/MacOS
- Navicat Premium 11.2.7及以上
- Python 3.6
伺服器環境
- Centos 7.6
- Docker
- MySQL 5.7
使用 Docker 安裝 MySQL
拉取官方映象（我們這裡選擇5.7，如果不寫後面的版本號則會自動拉取最新版）：
docker pull mysql:5.7
檢查是否拉取成功：
docker images REPOSITORY TAG IMAGE ID CREATED SIZE docker.io/mysql 5.7 db39680b63ac 2 days ago 437 MB
啟動容器：
docker run -p 3306:3306 --name mymysql -v $PWD/conf:/etc/mysql/conf.d -v $PWD/logs:/logs -v $PWD/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7
- –name：容器名，此處命名為 mymysql；
- -e：配置資訊，此處配置 mysql 的 root 使用者的登陸密碼；
- -p：埠對映，此處對映 主機 3306 埠到容器的 3306 埠；
- -d：源映象名，此處為 mysql:5.7；
- -v：主機和容器的目錄對映關係，":"前為主機目錄，之後為容器目錄。
檢查容器是否正常執行：
[root@VM_0_10_centos ~]# docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES d1e682cfdf76 mysql:5.7 "docker-entrypoint..." 14 seconds ago Up 13 seconds 0.0.0.0:3306->3306/tcp, 33060/tcp mymysql
可以看到容器 ID、容器的源映象、啟動命令、建立時間、狀態、埠對映資訊、容器名字。
進入 docker 本地連線 MySQL 客戶端：
sudo docker exec -it mymysql bash
mysql -u root -p
設定遠端訪問賬號，並授權遠端連線：
CREATE USER 'zuozewei'@'%' IDENTIFIED WITH mysql_native_password BY 'zuozewei'; GRANT ALL PRIVILEGES ON *.* TO 'zuozewei'@'%';
使用 Navicat 遠端連線 MySQL，新建資料庫並初始化資料。
編寫 Python 指令碼完成資料分析
首先我們通過 SQLAlchemy 來完成 SQL 查詢，使用 efficient_apriori 工具包的 Apriori 演算法。
整個工程一共包括 3 個部分：
- 第一個部分為資料載入，首先我們通過 sql.create_engine 建立 SQL 連線，然後從資料集表中讀取全部的資料載入到 data 中。這裡需要配置 MySQL 賬戶名和密碼；
- 第二步為資料預處理。我們還需要得到一個 transactions 陣列，裡面包括了每筆訂單的資訊，其中每筆訂單是以集合的形式進行儲存的，這樣相同的訂單中 item 就不存在重複的情況，同時也可以使用 Apriori 工具包直接進行計算；
- 最後一步，使用 Apriori 工具包進行關聯分析，這裡我們設定了引數 min_support=0.25，min_confidence=0.5，也就是最小支援度為 0.25，最小置信度為 0.5。根據條件找出 transactions 中的頻繁項集 itemsets 和關聯規則 rules。
下載依賴庫：
#pip3 install 包名 -i 源的url 臨時換源 #清華大學源：https://pypi.tuna.tsinghua.edu.cn/simple/ # 強大的資料結構庫，用於資料分析，時間序列和統計等 pip3 install pandas -i https://pypi.tuna.tsinghua.edu.cn/simple/ # python的orm程式 pip3 install SQLAlchemy -i https://pypi.tuna.tsinghua.edu.cn/simple/ # Apriori演算法的高效純Python實現 pip3 install efficient-apriori -i https://pypi.tuna.tsinghua.edu.cn/simple/ # MySQL驅動 pip3 install mysql-connector -i https://pypi.tuna.tsinghua.edu.cn/simple/
具體的程式碼如下：
from efficient_apriori import apriori import sqlalchemy as sql import pandas as pd ''' 資料載入 ''' # 建立資料庫連線 engine = sql.create_engine('mysql+mysqlconnector://zuozewei:zuozewei@server_ip/SQLApriori') # 查詢資料 query = 'SELECT * FROM test_data' # 載入到 data 中 data = pd.read_sql_query(query, engine) ''' 資料預處理 ''' # 得到一維陣列 orders_series，並且將 Transaction 作為 index, value 為 Item 取值 orders_series = data.set_index('trans_id')['product'] # 將資料集進行格式轉換 transactions = [] temp_index = 0 for i, v in orders_series.items(): if i != temp_index: temp_set = set() temp_index = i temp_set.add(v) transactions.append(temp_set) else: temp_set.add(v) ''' 資料分析 ''' # 挖掘頻繁項集和頻繁規則 itemsets, rules = apriori(transactions, min_support=0.25, min_confidence=0.5) print('頻繁項集：', itemsets) print('關聯規則：', rules)
執行結果：
頻繁項集： { 1: {('beer',): 7, ('chips',): 3, ('diapers',): 5}, 2: {('beer', 'chips'): 3, ('beer', 'diapers'): 5, ('chips', 'diapers'): 2}, 3: {('beer', 'chips', 'diapers'): 2} } 關聯規則： [ {chips} -> {beer}, {diapers} -> {beer}, {beer} -> {diapers}, {chips} -> {diapers}, {chips, diapers} -> {beer}, {beer, chips} -> {diapers}, {chips} -> {beer, diapers} ]
從結果中我們能看到購物組合中：
- 商品個數為 1 的頻繁項集有 3 種，分別為 beer(啤酒)、chips（薯條）、diapers（尿布） 等；
- 商品個數為 2 的頻繁項集有 3 種，包括{beer(啤酒), chips（薯條）}，{beer(啤酒), diapers（尿布）}，{chips（薯條）, diapers（尿布）}等；
- 其中關聯規則有 7 種，包括了購買 chips（薯條) 的人也會購買 beer(啤酒)，購買 diapers（尿布）的同時也會 beer(啤酒) 等。
總結
通過 SQL 完成資料分析、機器學習還是推薦使用到 Python，因為這是 Python 所擅長的。通過今天的例子我們應該能看到採用 SQL 作為資料查詢和分析的入口是一種資料全棧的思路，對於資料開發人員來說降低了資料分析的技術門檻。相信在當今的 DT 時代，我們的業務增長會越來越依靠於 SQL 引擎 + AI 引擎。
參考文獻：
- [1]：http://madlib.apache.org/docs/latest/group__grp__assoc__rules.html
- [2]：https://sql-machine-learning.github.io/
- [3]：https://www.jianshu.com/p/8e1e64c08cb7
- [4]：《資料分析實戰45講》陳暘 清華大學計算機博士