前言

我們通過 OLTP(聯(lián)機(jī)事務(wù)處理)系統(tǒng)實(shí)時(shí)處理用戶數(shù)據(jù)，還需要在 OLAP(聯(lián)機(jī)分析處理)系統(tǒng)中對它們進(jìn)行分析,今天我們來看下如何使用 SQL 分析數(shù)據(jù)。

使用 SQL 進(jìn)行數(shù)據(jù)分析的幾種方式

在 DBMS(數(shù)據(jù)庫管理系統(tǒng)) 中，有些數(shù)據(jù)庫很好地集成了 BI 工具，可以方便我們對收集的數(shù)據(jù)進(jìn)行商業(yè)分析。 比如在SQL Server 中提供了 BI 分析工具，我們可以通過使用 SQL Server中的 Analysis Services 完成數(shù)據(jù)挖掘任務(wù)。SQL Server 內(nèi)置了多種數(shù)據(jù)挖掘算法，比如常用的 EM、K-Means 聚類算法、決策樹、樸素貝葉斯和邏輯回歸等分類算法，以及神經(jīng)網(wǎng)絡(luò)等模型。我們還可以對這些算法模型進(jìn)行可視化效果呈現(xiàn)，幫我們優(yōu)化和評估算法模型的好壞。

另外 PostgreSQL 是一個(gè)免費(fèi)開源的關(guān)系數(shù)據(jù)庫(ORDBMS)，它的穩(wěn)定性非常強(qiáng)，功能強(qiáng)大，在 OLTP 和 OLAP 系統(tǒng)上表現(xiàn)都非常出色。同時(shí)在機(jī)器學(xué)習(xí)上，配合 Madlib 項(xiàng)目可以讓 PostgreSQL 如虎添翼。Madlib 包括了多種機(jī)器學(xué)習(xí)算法，比如分類、聚類、文本分析、回歸分析、關(guān)聯(lián)規(guī)則挖掘和驗(yàn)證分析等功能。這樣我們可以通過使用 SQL，在 PostgreSQL 中使用各種機(jī)器學(xué)習(xí)算法模型，幫我們進(jìn)行數(shù)據(jù)挖掘和分析。

2018 年 Google 將機(jī)器學(xué)習(xí)(Machine Learning)工具集成到了 BigQuery 中，發(fā)布了 BigQuery ML，這樣開發(fā)者就可以在大型的結(jié)構(gòu)化或半結(jié)構(gòu)化的數(shù)據(jù)集上構(gòu)建和使用機(jī)器學(xué)習(xí)模型。通過 BigQuery 控制臺(tái)，開發(fā)者可以像使用 SQL 語句一樣來完成機(jī)器學(xué)習(xí)模型的訓(xùn)練和預(yù)測。

SQLFlow 是螞蟻金服于 2019 年開源的機(jī)器學(xué)習(xí)工具，我們可以通過使用 SQL 就可以完成機(jī)器學(xué)習(xí)算法的調(diào)用，你可以將 SQLFlow 理解為機(jī)器學(xué)習(xí)的翻譯器。我們在 SELECT 之后加上 TRAIN 從句就可以完成機(jī)器學(xué)習(xí)模型的訓(xùn)練，在 SELECT 語句之后加上 PREDICT 就可以使用模型來進(jìn)行預(yù)測。這些算法模型既包括了傳統(tǒng)的機(jī)器學(xué)習(xí)模型，也包括了基于 Tensorflow、PyTorch 等框架的深度學(xué)習(xí)模型。

從上圖中你能看出 SQLFlow 的使用過程，首先我們可以通過 Jupyter notebook 來完成 SQL 語句的交互。SQLFlow 支持了多種 SQL 引擎，包括 MySQL、Oracle、Hive、SparkSQL 和 Flink 等，這樣我們就可以通過 SQL 語句從這些 DBMS 數(shù)據(jù)庫中抽取數(shù)據(jù)，然后選擇想要進(jìn)行的機(jī)器學(xué)習(xí)算法(包括傳統(tǒng)機(jī)器學(xué)習(xí)和深度學(xué)習(xí)模型)進(jìn)行訓(xùn)練和預(yù)測。不過這個(gè)工具剛剛上線，工具、文檔、社區(qū)還有很多需要完善的地方。

最后一個(gè)最常用方法是 SQL+Python，也是我們今天要重點(diǎn)講解的內(nèi)容。上面介紹的工具可以說既是 SQL 查詢數(shù)據(jù)的入口，也是數(shù)據(jù)分析、機(jī)器學(xué)習(xí)的入口。不過這些模塊耦合度高，也可能存在使用的問題。一方面工具會(huì)很大，比如在安裝 SQLFlow 的時(shí)候，采用 Docker 方式進(jìn)行安裝，整體需要下載的文件會(huì)超過 2G。同時(shí)，在進(jìn)行算法調(diào)參、優(yōu)化的時(shí)候也存在靈活度差的情況。因此最直接的方式，還是將 SQL 與數(shù)據(jù)分析模塊分開，采用 SQL 讀取數(shù)據(jù)，然后通過 Python 來進(jìn)行數(shù)據(jù)分析的處理。

案例：挖掘購物數(shù)據(jù)中的頻繁項(xiàng)集與關(guān)聯(lián)規(guī)則

下面我們通過一個(gè)案例來進(jìn)行具體的講解。

我們要分析的是購物問題，采用的技術(shù)為關(guān)聯(lián)分析。它可以幫我們在大量的數(shù)據(jù)集中找到商品之間的關(guān)聯(lián)關(guān)系，從而挖掘出經(jīng)常被人們購買的商品組合，一個(gè)經(jīng)典的例子就是“啤酒和尿布”的例子。

今天我們的數(shù)據(jù)集來自于一個(gè)購物樣本數(shù)據(jù)，字段包括了 trans_id(交易 ID)以及 product(商品名稱)，具體的數(shù)據(jù)集參考下面的初始化 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');  

這里我們采用的關(guān)聯(lián)分析算法是 Apriori 算法，它幫我們查找頻繁項(xiàng)集，首先我們需要先明白什么是頻繁項(xiàng)集。

頻繁項(xiàng)集就是支持度大于等于最小支持度閾值的項(xiàng)集，小于這個(gè)最小值支持度的項(xiàng)目就是非頻繁項(xiàng)集，而大于等于最小支持度的項(xiàng)集就是頻繁項(xiàng)集。支持度是個(gè)百分比，指的是某個(gè)商品組合出現(xiàn)的次數(shù)與總次數(shù)之間的比例。支持度越高，代表這個(gè)組合出現(xiàn)的頻率越大。

我們再來看下 Apriori 算法的基本原理。

Apriori 算法其實(shí)就是查找頻繁項(xiàng)集 (frequent itemset) 的過程： 0.設(shè)置一個(gè)最小支持度， 1.從K=1開始，篩選頻繁項(xiàng)集。 2.在結(jié)果中，組合K+1項(xiàng)集，再次篩選 3.循環(huán)1、2步。直到找不到結(jié)果為止，K-1項(xiàng)集的結(jié)果就是最終結(jié)果。

我們來看下數(shù)據(jù)理解一下，下面是所有的訂單，以及每筆訂單購買的商品：

在這個(gè)例子中，“啤酒”出現(xiàn)了 7 次，那么這 7 筆訂單中“牛奶”的支持度就是 7/7=1。同樣“啤酒 + 尿布”出現(xiàn)了 5 次，那么這 7 筆訂單中的支持度就是 5/7=0.71。

同時(shí)，我們還需要理解一個(gè)概念叫做“置信度”，它表示的是當(dāng)你購買了商品 A，會(huì)有多大的概率購買商品 B，在這個(gè)例子中，置信度(啤酒→尿布)=5/7=0.71，代表如果你購買了啤酒，會(huì)有 71% 的概率會(huì)購買尿布;置信度(啤酒→薯?xiàng)l)=3/7=0.43，代表如果你購買了啤酒，有 43% 的概率會(huì)購買薯?xiàng)l。

所以說置信度是個(gè)條件概念，指的是在 A 發(fā)生的情況下，B 發(fā)生的概率是多少。

我們在計(jì)算關(guān)聯(lián)關(guān)系的時(shí)候，往往需要規(guī)定最小支持度和最小置信度，這樣才可以尋找大于等于最小支持度的頻繁項(xiàng)集，以及在頻繁項(xiàng)集的基礎(chǔ)上，大于等于最小置信度的關(guān)聯(lián)規(guī)則。

使用 MADlib+PostgreSQL 完成購物數(shù)據(jù)的關(guān)聯(lián)分析

針對上面的購物數(shù)據(jù)關(guān)聯(lián)分析的案例我們可以使用工具自帶的關(guān)聯(lián)規(guī)則進(jìn)行分析，下面我們演示使用 PostgreSQL 數(shù)據(jù)庫在 Madlib 工具中都可以找到相應(yīng)的關(guān)聯(lián)規(guī)則，通過寫 SQL 的方式就可以完成關(guān)聯(lián)規(guī)則的調(diào)用分析。

開發(fā)環(huán)境

• Windows/MacOS
• Navicat Premium 11.2.7及以上

服務(wù)器環(huán)境

• Centos 7.6
• Docker
• PostgreSQL 9.6
• MADlib 1.4及以上

使用 Docker 安裝 MADlib+PostgreSQL

拉取 docker 鏡像(這個(gè)鏡像提供了需要的 postgres 等環(huán)境,并沒有安裝 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  

啟動(dòng)容器，并建立本機(jī)目錄與容器中系統(tǒng)的路徑映射，共享的目錄在容器和本機(jī)之間是讀寫共享的。

docker run -d -it --name madlib -v /home/git-repo/github/madlib:/incubator-madlib/ madlib/postgres_9.6  

啟動(dòng)容器后，連接容器編譯 MADlib 組件，編譯用時(shí)約 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  

運(yùn)行 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  

如果需要，先關(guān)掉并刪除容器，刪完再起新容器需要重新安裝:

docker kill madlib  
docker rm madlib  

用配置好的容器制作新鏡像，先查看容器 ID, 在用容器 ID 創(chuàng)建新鏡像：

docker ps -a  
docker commit <container id> my/madlib_pg9.6_dev  

用新鏡像創(chuàng)建新容器：

docker run -d -it -p 5432:5432 --name madlib_dev -v /home/my/git-repo/github/madlib:/incubator-madlib/ madlib/postgres_9.6   

連接容器進(jìn)行交互(發(fā)現(xiàn)新容器還是沒有安裝，但是不用編譯了，安裝也很快，裝完測試一下)

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 遠(yuǎn)程連接 PostgreSQL(假定沒有修改登錄用戶和密碼,默認(rèn)沒有密碼)

最后，新建表并初始化數(shù)據(jù)：

使用 SQL 完成關(guān)聯(lián)規(guī)則的調(diào)用分析

最后使用 SQL + MADlib 進(jìn)行關(guān)聯(lián)分析，這里我們設(shè)定了參數(shù)最小支持度為 0.25，最小置信度為 0.5。根據(jù)條件生成 transactions 中的關(guān)聯(lián)規(guī)則，如下所示：

SELECT * FROM madlib.assoc_rules( .25,            -- 支持度  
                                  .5,             -- 置信度  
                                  'trans_id',     -- Transaction id 字段  
                                  'product',      -- Product 字段  
                                  'test_data',    -- 輸入數(shù)據(jù)  
                                  NULL,           -- 輸出模式  
                                  TRUE            -- 詳細(xì)輸出  
                                );  

查詢結(jié)果：

關(guān)聯(lián)規(guī)則存儲(chǔ)在 assoc_rules 表中:

SELECT * FROM assoc_rules  
ORDER BY support DESC, confidence DESC;  

注意： 關(guān)聯(lián)規(guī)則會(huì)始終創(chuàng)建一個(gè)名為的表 assoc_rules。如果要保留多個(gè)關(guān)聯(lián)規(guī)則表，請?jiān)谠俅芜\(yùn)行之前復(fù)制該表。

使用 SQL+Python 完成購物數(shù)據(jù)的關(guān)聯(lián)分析

除此以外，我們還可以直接使用 SQL 完成數(shù)據(jù)的查詢，然后通過 Python 的機(jī)器學(xué)習(xí)工具包完成關(guān)聯(lián)分析。

開發(fā)環(huán)境

• Windows/MacOS
• Navicat Premium 11.2.7及以上
• Python 3.6

服務(wù)器環(huán)境

• Centos 7.6
• Docker
• MySQL 5.7

使用 Docker 安裝 MySQL

拉取官方鏡像(我們這里選擇5.7，如果不寫后面的版本號則會(huì)自動(dòng)拉取最新版)：

docker pull mysql:5.7  

檢查是否拉取成功：

docker images  
REPOSITORY                        TAG                 IMAGE ID            CREATED             SIZE  
docker.io/mysql                   5.7                 db39680b63ac        2 days ago          437 MB  

啟動(dòng)容器：

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：端口映射，此處映射 主機(jī) 3306 端口到容器的 3306 端口;
• -d：源鏡像名，此處為 mysql:5.7;
• -v：主機(jī)和容器的目錄映射關(guān)系，":"前為主機(jī)目錄，之后為容器目錄。

檢查容器是否正常運(yùn)行：

[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、容器的源鏡像、啟動(dòng)命令、創(chuàng)建時(shí)間、狀態(tài)、端口映射信息、容器名字。

進(jìn)入 Docker 本地連接 MySQL 客戶端：

sudo docker exec -it mymysql bash  
mysql -u root -p   

設(shè)置遠(yuǎn)程訪問賬號，并授權(quán)遠(yuǎn)程連接：

CREATE USER 'zuozewei'@'%' IDENTIFIED WITH mysql_native_password BY 'zuozewei';  
GRANT ALL PRIVILEGES ON *.* TO 'zuozewei'@'%';     

使用 Navicat 遠(yuǎn)程連接 MySQL，新建數(shù)據(jù)庫并初始化數(shù)據(jù)。

編寫 Python 腳本完成數(shù)據(jù)分析

首先我們通過 SQLAlchemy 來完成 SQL 查詢，使用 efficient_apriori 工具包的 Apriori 算法。 整個(gè)工程一共包括 3 個(gè)部分：

• 第一個(gè)部分為數(shù)據(jù)加載，首先我們通過 sql.create_engine 創(chuàng)建 SQL 連接，然后從數(shù)據(jù)集表中讀取全部的數(shù)據(jù)加載到 data 中。這里需要配置 MySQL 賬戶名和密碼;
• 第二步為數(shù)據(jù)預(yù)處理。我們還需要得到一個(gè) transactions 數(shù)組，里面包括了每筆訂單的信息，其中每筆訂單是以集合的形式進(jìn)行存儲(chǔ)的，這樣相同的訂單中 item 就不存在重復(fù)的情況，同時(shí)也可以使用 Apriori 工具包直接進(jìn)行計(jì)算;
• 最后一步，使用 Apriori 工具包進(jìn)行關(guān)聯(lián)分析，這里我們設(shè)定了參數(shù) min_support=0.25，min_confidence=0.5，也就是最小支持度為 0.25，最小置信度為 0.5。根據(jù)條件找出 transactions 中的頻繁項(xiàng)集 itemsets 和關(guān)聯(lián)規(guī)則 rules。

下載依賴庫：

#pip3 install 包名 -i 源的url 臨時(shí)換源  
#清華大學(xué)源：https://pypi.tuna.tsinghua.edu.cn/simple/  
  
# 強(qiáng)大的數(shù)據(jù)結(jié)構(gòu)庫，用于數(shù)據(jù)分析，時(shí)間序列和統(tǒng)計(jì)等  
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實(shí)現(xiàn)  
pip3 install efficient-apriori -i https://pypi.tuna.tsinghua.edu.cn/simple/   
  
# MySQL驅(qū)動(dòng)  
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  
  
'''  
數(shù)據(jù)加載  
'''  
  
# 創(chuàng)建數(shù)據(jù)庫連接  
engine = sql.create_engine('mysql+mysqlconnector://zuozewei:zuozewei@server_ip/SQLApriori')  
# 查詢數(shù)據(jù)  
query = 'SELECT * FROM test_data'  
# 加載到 data 中  
data = pd.read_sql_query(query, engine)  
  
'''  
數(shù)據(jù)預(yù)處理  
'''  
  
# 得到一維數(shù)組 orders_series，并且將 Transaction 作為 index, value 為 Item 取值  
orders_series = data.set_index('trans_id')['product']  
# 將數(shù)據(jù)集進(jìn)行格式轉(zhuǎn)換  
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)  
  
'''  
數(shù)據(jù)分析  
'''  
  
# 挖掘頻繁項(xiàng)集和頻繁規(guī)則  
itemsets, rules = apriori(transactions, min_support=0.25,  min_confidence=0.5)  
  
print('頻繁項(xiàng)集：', itemsets)  
print('關(guān)聯(lián)規(guī)則：', rules)  

運(yùn)行結(jié)果：

頻繁項(xiàng)集： {  
1: {('beer',): 7, ('chips',): 3, ('diapers',): 5},   
2: {('beer', 'chips'): 3, ('beer', 'diapers'): 5, ('chips', 'diapers'): 2},   
3: {('beer', 'chips', 'diapers'): 2}  
}  
  
關(guān)聯(lián)規(guī)則： [  
{chips} -> {beer},   
{diapers} -> {beer},   
{beer} -> {diapers},   
{chips} -> {diapers},   
{chips, diapers} -> {beer},   
{beer, chips} -> {diapers},   
{chips} -> {beer, diapers}  
]  

從結(jié)果中我們能看到購物組合中：

• 商品個(gè)數(shù)為 1 的頻繁項(xiàng)集有 3 種，分別為 beer(啤酒)、chips(薯?xiàng)l)、diapers(尿布) 等;
• 商品個(gè)數(shù)為 2 的頻繁項(xiàng)集有 3 種，包括{beer(啤酒), chips(薯?xiàng)l)}，{beer(啤酒), diapers(尿布)}，{chips(薯?xiàng)l), diapers(尿布)}等;
• 其中關(guān)聯(lián)規(guī)則有 7 種，包括了購買 chips(薯?xiàng)l) 的人也會(huì)購買 beer(啤酒)，購買 diapers(尿布)的同時(shí)也會(huì) beer(啤酒) 等。

總結(jié)

通過 SQL 完成數(shù)據(jù)分析、機(jī)器學(xué)習(xí)還是推薦使用到 Python，因?yàn)檫@是 Python 所擅長的。通過今天的例子我們應(yīng)該能看到采用 SQL 作為數(shù)據(jù)查詢和分析的入口是一種數(shù)據(jù)全棧的思路，對于數(shù)據(jù)開發(fā)人員來說降低了數(shù)據(jù)分析的技術(shù)門檻。相信在當(dāng)今的 DT 時(shí)代，我們的業(yè)務(wù)增長會(huì)越來越依靠于 SQL 引擎 + AI 引擎。