如何使用SQL分析数据

前言我们通过OLTP（OnlineTransactionProcessing）系统对用户数据进行实时处理，同时也需要在OLAP（OnlineAnalyticalProcessing）系统中进行分析。今天我们就来看看如何使用SQL分析数据。使用SQL进行数据分析的几种方式在DBMS（数据库管理系统）中，一些数据库很好地集成了BI工具，可以方便的对收集到的数据进行业务分析。例如，SQLServer中提供了BI分析工具，我们可以使用SQLServer中的AnalysisServices完成数据挖掘任务。SQLServer内置了多种数据挖掘算法，如常用的EM、K-Means聚类算法、决策树、朴素贝叶斯和逻辑回归等分类算法，以及神经网络等模型。我们还可以将这些算法模型可视化，帮助我们优化和评估算法模型的好坏。此外，PostgreSQL是一个免费开源的关系数据库（ORDBMS），非常稳定和强大，在OLTP和OLAP系统上表现非常好。同时，在机器学习方面，与Madlib项目的合作可以让PostgreSQL如虎添翼。Madlib包括多种机器学习算法，如分类、聚类、文本分析、回归分析、关联规则挖掘和验证分析。这样我们就可以通过SQL来使用PostgreSQL中的各种机器学习算法模型来帮助我们进行数据挖掘和分析。2018年，谷歌将机器学习（MachineLearning）工具集成到BigQuery中，发布了BigQueryML，让开发者可以在大型结构化或半结构化数据集上构建和使用机器学习模型。通过BigQuery控制台，开发者可以使用SQL语句完成机器学习模型的训练和预测。SQLFlow是蚂蚁金服2019年开源的机器学习工具，我们可以通过SQL来调用机器学习算法。您可以将SQLFlow理解为机器学习翻译器。我们可以通过在SELECT语句后添加TRAIN子句来完成机器学习模型的训练，在SELECT语句后添加PREDICT可以将模型用于预测。这些算法模型既包括传统的机器学习模型，也包括基于Tensorflow、PyTorch等框架的深度学习模型。从上图中可以看出使用SQLFlow的过程。首先，我们可以使用Jupyternotebook来完成SQL语句的交互。SQLFlow支持多种SQL引擎，包括MySQL、Oracle、Hive、SparkSQL、Flink，这样我们就可以通过SQL语句从这些DBMS数据库中提取数据，然后选择需要的机器学习算法（包括传统机器学习和深度学习）模型）进行训练和预测。不过这个工具刚刚上线，在工具、文档、社区等方面还有很多需要完善的地方。最后一种也是最常用的方法就是SQL+Python，这也是我们今天要重点介绍的。上面介绍的这些工具可以说不仅仅是SQL查询数据的入口，更是数据分析和机器学习的入口。但是，这些模块是高度耦合的，使用起来可能会出现问题。一方面，工具将非常庞大。比如安装SQLFlow时，使用Docker安装，整体下载的文件会超过2G。同时，在进行算法调优时也存在灵活性较差的情况。因此，最直接的方式就是将SQL从数据分析模块中分离出来，使用SQL读取数据，然后使用Python处理数据分析。案例：挖掘购物数据中的频繁项集和关联规则下面用一个案例来详细说明。我们要分析的是购物问题，用到的技术是关联分析。它可以帮助我们在大量的数据集中找到商品之间的关系，从而挖掘出人们经常购买的商品组合。一个经典的例子就是“啤酒和尿布”的例子。今天我们的数据集来自一个购物样本数据。字段包括trans_id（交易ID）和product（产品名称）。具体的数据集可以参考下面的初始化sql：DROPTABLEIFEXISTStest_data;CREATETABLEtest_data(trans_idINT,productTEXT);INSERTINTOtest_dataVALUES(1,'beer');INSERTINTOtest_dataVALUES(1,'diapers');INSERTINTOtest_dataVALUES(1,'chips');INSERTINTTOtest_dataVALUES(2,'啤酒');INSERTINTOtest_dataVALUES(2,'尿布');INSERTINTOtest_dataVALUES(3,'啤酒');插入测试数据值（3，“尿布”）；插入测试数据值（4，“啤酒”）；插入测试数据值（4，“筹码”）；插入测试数据值（5，“啤酒”）；插入测试数据值（6，“啤酒”）；插入测试数据值（6，'尿布');INSERTINTOtest_dataVALUES(6,'薯片');INSERTINTOtest_dataVALUES(7,'啤酒');INSERTINTOtest_dataVALUES(7,'尿布');这里我们使用的关联分析算法是Ap??riori算法，它可以帮助我们找到频繁项集，首先我们要了解什么是频繁项集。频繁项集是支持度大于或等于最小支持度阈值的项集，支持度小于该最小值的项集为不频繁项集，支持度大于或等于最小支持度的项集为频繁项集。支持度是一个百分比，指的是产品组合出现的次数与总次数的比值。支持度越高，这种组合出现的频率就越大。让我们看一下Apriori算法的基本原理。Apriori算法其实就是寻找频繁项集的过程：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%的概率会买尿布；Confidence(beer→chips)=3/7=0.43，也就是说如果你买啤酒，你有43%的概率会买chipsstrip。所以置信度是一个条件概念，指的是当A发生时B发生的概率。我们在计算关联关系时，往往需要指定最小支持度和最小置信度，这样才能找到大于或等于最小支持度的频繁项集，并在频繁项集的基础上，关联规则大于或等于最低置信度。使用MADlib+PostgreSQL完成购物数据的关联分析对于上面购物数据关联分析的案例，我们可以使用工具自带的关联规则进行分析。下面我们演示使用PostgreSQL数据库在Madlib工具中可以找到对应的关联规则。关联规则的调用分析可以通过编写SQL来完成。开发环境Windows/MacOSNavicatPremium11.2.7及以上服务器环境Centos7.6DockerPostgreSQL9.6MADlib1.4及以上使用Docker安装MADlib+PostgreSQL拉取docker镜像（本镜像提供所需的postgres等环境，madlib为未安装）：dockerpullmadlib/postgres_9.6:latest下载MADlibgithub源代码。假设下载的源码位置是/home/git-repo/github/madlib:cd/home/git-repo/github&&gitclonegit@github.com:apache/madlib.git启动容器，并建立本地目录之间的路径映射和容器中的系统，共享目录在容器和本机之间读写共享。dockerrun-d-it--namemadlib-v/home/git-repo/github/madlib:/incubator-madlib/madlib/postgres_9.6启动容器后，连接容器编译MADlib组件，编译大约需要30分钟：dockerexec-itmadlibbashmkdir/incubator-madlib/build-dockercd/incubator-madlib/build-dockercmake..makemakedocmakeinstall在容器中安装MADlib：src/bin/madpack-ppostgres-cpostgres/postgres@localhost:5432/postgresinstall运行MADlib测试:#Runinstallcheck,onallmodules:src/bin/madpack-ppostgres-cpostgres/postgres@localhost:5432/postgresinstall-check#Runinstallcheck,onaspecificmodule,saysvm:src/bin/madpack-ppostgres-cpostgres/postgres@localhost:5432/postgresinstall-check-tsvm#Rundevcheck，在所有模块上（比安装检查更全面）：src/bin/madpack-ppostgres-cpostgres/postgres@localhost:5432/postgresdev-check#Rundevcheck,onaspecificmodule,saysvm:src/bin/madpack-ppostgres-cpostgres/postgres@localhost:5432/postgresdev-check-tsvm#如有必要，重新安装ReinstallMADlib:src/bin/madpack-ppostgres-cpostgres/postgres@localhost:5432/postgresreinstall需要的话关闭删除删除容器，然后重启新容器重新安装：dockerkillmadlibdockerrmmadlib用配置好的容器创建新镜像，先查看容器ID，创建新镜像容器ID：dockerps-adockercommitmy/madlib_pg9.6_dev使用新镜像创建新容器：dockerrun-d-it-p5432:5432--namemadlib_dev-v/home/my/git-repo/github/madlib:/incubator-madlib/madlib/postgres_9.6连接容器进行交互（foundnewcontainerornotInstall,但不需要编译，安装很快，安装好后测试）dockerexec-itmadlib_devbashcd/incubator-madlib/build-dockersrc/bin/madpack-ppostgres-cpostgres/postgres@localhost:5432/postgresinstallsrc/bin/madpack-ppostgres-cpostgres/postgres@localhost:5432/postgresinstall-check使用Navicat远程连接PostgreSQL（假设没有修改登录用户和密码，也没有密码）默认情况下）最后，创建一个新表和ini数据初始化：使用SQL完成关联规则的调用分析，最后使用SQL+MADlib进行关联分析。这里我们设置参数minimumsupport为0.25，minimumconfidence为0.5，根据条件在交易中生成关联规则，如下图：SELECT*FROMmadlib.assoc_rules(.25,--support.5,--confidencedegree'trans_id',--Transactionid字段'product',--产品字段'test_data',--输入数据NULL,--输出方式TRUE--详细输出);查询结果：关联规则存放在assoc_rules表中：SELECT*FROMassoc_rulesORDERBYsupportDESC,confidenceDESC;注意：关联规则总是创建一个名为assoc_rules的表。如果要保留多个关联规则表，请在再次运行之前复制该表。使用SQL+Python完成购物数据的关联分析另外，我们也可以直接使用SQL完成数据查询，再通过Python机器学习工具包完成关联分析。开发环境Windows/MacOSNavicatPremium11.2.7及以上Python3.6服务器环境Centos7.6DockerMySQL5.7使用Docker安装MySQL拉取官方镜像（我们这里选择5.7，如果后面不写版本号，它会自动拉取最新版本）：dockerpullmysql:5.7查看是否拉取成功：dockerimagesREPOSITORYTAGIMAGEIDCREATEDSIZEdocker.io/mysql5.7db39680b63ac2daysago437MB启动容器：dockerrun-p3306:3306--namemysql-v$PWD/conf:/etc/mysql/conf.d-v$PWD/logs:/logs-v$PWD/data:/var/lib/mysql-eMYSQL_ROOT_PASSWORD=123456-dmysql:5.7–name：容器名，这里命名为mymysql；-e：配置信息，这里配置mysql的root用户的登录密码；-p：端口映射，将宿主机的3306端口映射到容器的3306端口；-d：源镜像名称，这里是mysql:5.7；-v：宿主机与容器的目录映射关系，“：”在宿主机目录前，后跟容器目录。查看容器是否正常运行：[root@VM_0_10_centos~]#dockerpsCONTAINERIDIMAGECOMMANDCREATEDSTATUSPORTSNAMESd1e682cfdf76mysql:5.7"docker-entrypoint..."14secondsagoUp13seconds0.0.0.0:3306->3306/tcp,33060/tcpmysql可以看到源镜像容器ID，33060/tcpmysql，启动命令，创建时间，状态，端口映射信息，容器名称。本地输入Docker连接MySQL客户端：sudodockerexec-itmymysqlbashmysql-uroot-p设置远程访问账号，授权远程连接：CREATEUSER'zuozewei'@'%'IDENTIFIEDWITHmysql_native_passwordBY'zuozewei';GRANTALLPRIVILEGESON*.*TO'zuozewei'@'%';使用Navicat远程连接MySQL，新建数据库，初始化数据。编写Python脚本完成数据分析首先我们使用SQLAlchemy完成SQL查询，使用efficient_apriori工具包的Apriori算法。整个项目由3部分组成：第一部分是数据加载。首先，我们通过sql.create_engine创建一个SQL连接，然后从dataset表中读取所有数据，加载到data中。这里需要配置MySQL的账户名和密码；第二步是数据预处理。我们还需要得到一个transactions数组，里面包含了每一个订单的信息，这里每一个订单都是以集合的形式存储的，这样就不会出现同一个订单的物品重复，也可以使用Apriori工具包直接计算；最后一步是使用Apriori工具包进行关联分析，这里我们设置参数min_support=0.25，min_confidence=0.5，即最小支持度为0.25，最小置信度为0.5。根据条件查找交易中的频繁项集和关联规则。下载依赖库：#pip3install包名-i源url临时改源#清华大学源：https://pypi.tuna.tsinghua.edu.cn/simple/#强大的数据结构库，用于数据分析、时序和统计，等pip3installpandas-ihttps://pypi.tuna.tsinghua.edu.cn/simple/#Python的orm程序pip3installSQLAlchemy-ihttps://pypi.tuna.tsinghua.edu.cn/simple/#Apriori算法高效纯Python实现pip3installerefficient-apriori-ihttps://pypi.tuna.tsinghua.edu.cn/simple/#MySQL驱动pip3installmysql-connector-ihttps://pypi.tuna.tsinghua.edu.cn/simple/具体代码如下：fromefficient_aprioriimportaprioriimportsqlalchemyassqlimportpandasasspd'''数据加载'''#创建数据库连接engine=sql.create_engine('mysql+mysqlconnector://zuozewei:zuozewei@server_ip/SQLApriori')#查询数据query='SELECT*FROMtest_data'#加载到数据indata=pd.read_sql_query(query,engine)'''数据预处理'''#得到一维数组orders_series，并使用Transaction作为索引，value为Itemvalueorders_series=data.set_index('trans_id')['product']#转换数据集的格式transactions=[]temp_index=0fori,vinorders_series.items():ifi!=temp_index:temp_set=set()temp_index=itemp_set.add(v)transactions.append(temp_set)else:temp_set。add(v)'''数据分析'''#挖掘频繁项集和频繁规则项集,rules=apriori(transactions,min_support=0.25,min_confidence=0.5)print('频繁项集:',itemsets)print('关联规则:',rules)运行结果：频繁项集：{1:{('beer',):7,('chips',):3,('diapers',):5},2:{('beer','筹码'):3,('啤酒','尿布'):5,('筹码','尿布'):2},3:{('啤酒','筹码','尿布'):2}}关联规则：[{chips}->{beer},{diapers}->{beer},{beer}->{diapers},{chips}->{diapers},{chips,diapers}->{beer},{beer,chips}->{diapers},{chips}->{beer,diapers}]从结果可以看出，购物组合中：商品个数为1的频繁项集有3种，分别是啤酒（beer）、薯条（frenchfries）、尿布（diapers）等；有2种商品的3种频繁项集，分别是{啤酒（啤酒），薯条（炸薯条）}，{啤酒（啤酒），尿布（尿布）}，{薯条（炸薯条），尿布（尿布）}，ETC。;关联规则有7种，包括买薯片（frenchfries）的人也会买啤酒（beer），买尿布（diapers）的人也会买啤酒（beer）等。推荐使用Python来通过SQL完成数据分析和机器学习，因为这正是Python所擅长的。通过今天的例子，我们应该可以看出，以SQL作为数据查询分析的入口，是一种全栈的数据方式，降低了数据开发人员进行数据分析的技术门槛。我相信在今天的DT时代，我们的业务增长会越来越依赖SQL引擎+AI引擎。