开发者社区> xn3dxx4ljvk44> 正文

PolarDB for PostgreSQL 用SQL做数据分析

简介: 作者:技师
+关注继续查看

数据分析是近年来非常热门的话题,在金融、电商、制造等很多行业里面都需要用到数据分析。数据分析的工具多种多样,比如有些同学会用Excel做数据分析,在银行里面可能用SAS软件做数据分析,还有通过Python或R等编程语言来做数据分析。


本文想向大家展示一下在PolarDB里如何用纯SQL做数据分析,下文提到的PolarDB特指PolarDB for PG。


SQL是一门非常强大的数据分析语言,它围绕着二维表数据结构,除了提供增删改查这些基础功能之外,还提供了非常完整的数据操作、数据查询和数据处理的功能。此外,在本文里也会向大家展示通过PolarDB的插件,可以实现用SQL做数据可视化展示。


本文不是专业的数据分析入门教程,所以不需要大家有数据分析的背景;并且为了方便演示,数据分析的过程也会简化;过程中还会介绍一些SQL的简单语法,所以不需要有太多的背景知识。

 

一、AGENDA

image001.png

本文由三个部分组成:

1. 首先介绍如何搭建PolarDB的数据分析环境,包括安装PolarDB和相关插件

2. 然后介绍简单的数据分析知识,并通过两个案例展示如何用SQL做数据分析,过程中会穿插一些SQL的基础知识。

3. 最后总结本文涉及到的数据库相关的知识点。

 

二、环境搭建


我们就先来看一下怎么去搭建一个数据分析的环境。

image002.png

2.1 选择一款支持sixel图片格式的终端软件


我们最终是在命令行环境下,通过PSQL命令行来运行分数据分析的SQL,以及做数据可视化的展示,因此需要终端软件能支持sixel这种图片格式。上图中罗列了几款常见操作系统中能支持显示图片的终端软件:


  • 如果用的是MacOS操作系统,推荐使用iTerm2;
  • 如果用的是Linux操作系统,可以使用xTerm;
  • 如果用的是Windows,可以试一下Mintty。


2.2 安装PolarDB及插件


环境搭建的方法有两种,第一种是从源码开始一步一步的手工编译,选择这种方式的同学你可以上图中的链接,分别去编译安装PolarDB和编译安装插件。第二种办法比较简单也比较推荐,如果电脑上面有安装Docker的话,可以直接使用预先编译好的Docker容器。


2.3 测试运行


环境搭建好了之后,启动PSQL进入到运行SQL的环境。大家可以试一试运行上图的SQL:`select show­_logo()`,如果能在屏幕上面看到PolarDB的logo图片,那就说明已经安装成功了。

 

三、数据分析简介


环境搭建好之后,我们就可以开始动手做数据分析了。一般情况下做数据分析要有很多的工作要做,整个过程比较繁琐并且冗长的,但是我们可以大致把它分成以下四个步骤。

image003.png


1. 获取数据:把数据加载到数据分析工具里,即先获得要分析的数据,再将数据导入到工具中。这些待分析的数据可能是散落在互联网上,也可能是在某个文件里面,总而言之我们得先获得想要分析的数据。但是,刚拿到手的数据,格式通常是五花八门的,比如下载的网页格式通常是HTML,有些数据是XML格式,也有些是JSON格式,甚至还有一些数据格式是自定义的,不是通用的格式。为了能分析这些数据,得先把这些数据做格式转换,转换成方便处理的结构化格式(例如CSV、JSON等),然后导入到分析工具中。


2. 处理数据:提取特征数据。在真是的工作场景中,除了数据格式不统一,数据的内容往往也参差不齐,很少能直接拿到一份干净又完整的数据。比如有些样本数据会缺失部分特征、有些数据是明显的异常数据。所以在开始分析之前,还需要对数据做进一步的处理,比如用默认值填充缺失的值、过滤掉明显重复的或者异常的数据,这个步骤一般也叫“特征工程”,需要耗费比较长的时间才能把数据整理好。


3. 分析数据:有了干净整齐的数据,才能真正开始分析数据。到目前为止,数据分析的工作还是非常依赖人工的,需要大家根据自己的经验来观察数据。此时,通常会把数据以各种图表的形式展示出来,人工观察并总结一些特征,再根据这些特征选择一些复杂度合适的模型,然后开始训练并不断调优。


4. 预测数据:最后,等模型训练出来后,就可以拿它来预测新的数据,然后根据结果不断优化和升级模型。

看到这里大家可能会有个疑问,这好像是做机器学习的模型训练。没错,因为机器学习的本质其实就是通过数学建模来理解数据和分析数据,机器学习就是一种数据分析的方法。在后文的案例演示中,也会用到一些机器学习的算法,来帮助我们更好地理解和分析数据。

 

四、鸢尾花聚类分析案例


鸢尾花数据集是一个非常经典的数据集,通常是会用来做聚类或分类问题的入门数据。它包含150个样本,包括了三种鸢尾花,分别是山鸢尾、变色鸢尾和维吉尼亚鸢尾。每种花分别有50个样本,并且每个样本都包含花萼的长度与宽度,还有花瓣的长度与宽度,总共有四个特征,以及它属于哪种花。

image004.png

如上图所示,第一个表格的第一行山鸢尾的花萼长度是5.1、花萼宽度是3.5、花瓣长度是1.4、花瓣宽度是0.2,这些信息我们拿到手之后,就可以基于它们去做一些分析,总结出这四个特征与鸢尾花品种的关系。


鸢尾花的数据大家可以在互联网上下载到,如果已经根据前面的安装指南成功安装了PolarDB以及相关插件,插件里面已经包含这份数据。

 

4.1 获取数据

4.1.1 创建数据库表


正如前文所述,数据分析的工具多种多样,并且每种工具内部保存数据的结构也不一样。比如Excel就是表格、Python里比较常见的有Pandas的DataFrame,或者Numpy的数组等。在SQL的世界里,所有的数据都是以数据库表的形式保存,所以我们要先创建一张数据库的表,用来保存鸢尾花的数据。SQL里创建数据库表,需要用到CREATE TABLE这个命令(语法规则可参考下图中的链接)。PolarDB for PG数据库因为是基于开源的PG数据库,对SQL标准的支持还是比较好的。

image006.png

如上图所示,创建一张叫flowers的表,包含5个字段,前4个字段的类型是float8,就是double类型的浮点数,分别是花萼与花瓣的长度与宽度;最后一个字段类型是text,就是任意长度的字符串,用来保存花的品种。


上图中右侧的代码是给数据库表和字段添加注释,用来说明表和字段的用途。给数据库的表和字段添加注释是一个非常好的习惯,可以帮助理解数据,以及每个字段的含义。推荐大家在创建数据库表或新增字段时,及时地给表或字段加上注释。

 

4.1.2 导入数据到数据库表


创建了表之后,如何将数据导进来呢?在数据量小的时候,可以通过手工逐行insert数据,但是如果数据量大,手工insert就几乎不可能。比如鸢尾花的数据一共有150行,手工insert 150次是一件很费劲的事情。好在PolarDB提供了一个COPY的命令,可以批量地导入或者批量导出数据。如下图所示:

image007.png

其中左边的代码是从CSV文件里导入数据到flowers表中;右边的代码是把flowers表的数据导出到test.csv这个文件里。with子句指定数据的格式信息,比如上图中指定格式是CSV格式、包含表头、分隔符是逗号。


安装完插件后,在Docker容器内/home/postgres/datasets目录下,已经预置了一些CSV文件,其中iris.csv就是本案例中用到的鸢尾花数据,如下图所示:

image009.png

接着,这个数据导入到PolarDB里面来。先通过PSQL连接到PolarDB数据库,当前数据库是空的,里面是没有任何的一张表,所以通过建表语句,先创建一张数据库表。如下图所示:

image010.png

然后再添加注释,通过\d+ flowers可以看到数据库表的定义。如下图所示:

image011.png

最后,通过copy命令把CSV的数据导进来:其中格式是CSV格式、header为true(即包含了标题),以及分隔符是逗号。如下图所示:

image012.png

提示导进来了150行数据,通过select * from flowers可查看数据:

image013.png

再试试把表中的数据导出到一个新的文件里面。如下图所示:

image014.png

导出成功后,切换到Shell里,可以看到datasets目录里多出了一个叫test.csv的文件,并且有151行内容(包含标题)。

image015.png

 

4.1.3 其他预置数据


如前文演示中看到的, dataset目录下面还有其他预先准备好的CSV数据,都可以手工创建表,然后用copy命令把数据导进来。为了方便使用,PolarDB的插件其实还额外提供了一批以load开头的函数,执行这些函数就能自动创建数据库的表,并把CSV的数据导入到表中。如下图所示:

image016.png

目前插件里包含了这5份数据,分别是波士顿房价数据、鸢尾花数据、糖尿病数据、手写数字数据,以及苹果公司股价数据。其中房价数据、糖尿病数据和股价数据可以用来做回归分析,另外两份数据可以用来做分类分析。

 

4.2 处理数据

4.2.1 定义缺失值


鸢尾花这150条数据是非常完整并且干净的数据,如前文所说,现实工作中的数据往往会有许多缺失值或者异常值,需要先对数据做清洗。比方说,如果我们现在插入一条新的鸢尾花的样本,但部分的数据缺失了,希望分析工具在数据缺失的时候自动填充上默认值(例如0)。PolarDB里面有一个数据库字段默认值的功能,在数据库字段缺失的时候,就会自动使用默认值来填充。

image018.png

如上图所示,给字段加默认值有两种方法:第一种是在表还不存在的时候,创建表的时候直接定义字段的默认值,就像图中左边的代码一样,也用到了CREATE TABLE;第二种是表已经创建好了,想给已经存在的字段添加默认值或者修改默认值,这时候就需要用到另一个命令,ALTER TABLE,代码就像图中右边一样,给刚刚创建的表前4个字段全部加上默认值。

 

4.2.2 使用缺失值


定义好了默认值,接下来就可以通过INSERT命令来插入默认值了。

image019.png

如上图所示,PolarDB中有两种INSERT方式可以用到默认值:第一种就像左边的代码,在INSERT的时候,不指定有默认值的列;第二种,指定了列,但values里面用default关键字。这两种方式都能用上默认值,但用COPY导入数据的话,能不能用上默认值呢?我们来测试一下。


再看一下flower表的定义:

image021.png

其中每个字段的默认值是空(即null)。尝试插入一行脏数据,除了品类,其他的字段都不指定:

image022.png

可以看到现在插进来的这行数据里面,其他字段的值都是空的。接着用Alter Table去添加数据库字段的默认值:`alter table flowers alter column sepal_length set default 0, alter column sepal_width set default 0, alter column patal_length set default 0, alter column patal_width set default 0`。再次查看flowers表的定义:

image023.png

现在字段的默认值都已经有了,全部都是0。然后再试试重新insert一条bad2的脏数据:

image024.png

此时,bad2的其他字段都是0。可以再试试另外一种用default关键词的insert的方式:

image025.png

可以看到bad2与bad3都使用了默认值0。最后再测试一下copy命令是否可以用到默认值。


首先清空数据:

image026.png

然后编辑test.csv,手工加入3行脏数据。最后通过copy命令,把test.csv的数据导进来:

image027.png

一共导入了153行,但脏数据的字段依然为空:

image028.png

说明COPY命令没有用上默认值!因为COPY命令等价于在insert的时候是直接指定了字段的值是null。

 

4.2.3 用户定义函数


我们知道,数据导进来之后,我们是可以通过update更新值的,所以在COPY命令导入数据后,我们可以执行4次UPDATE命令,把4个特征中的NULL值依次更新成0。但每次导数据都要执行4条语句很麻烦,有没有办法可以一次性执行4条UPDATE呢?


这里就用到了PolarDB一个非常重要的功能——用户定义的函数(UDF,User Defined Function)。PolarDB的UDF非常强大,大家甚至可以把他看成是一种FaaS(Function as a Service),它允许我们用很多种不同的编程语言来写函数,在无须重启数据库、更不用重新编译数据库内核的情况下,能直接使用动态创建的函数。函数的定义用CREATE FUNCTION,详细用法大家可以参考文档。

image029.png

上图中用PLPGSQL这门语言创建了一个叫update_null_to_zero的函数。其中PLPGSQL是PolarDB自带的一种编程语言,此外还支持用SQL、Python等其他主流编程语言。


update_null_to_zero这个函数没有返回值,所以returns是void,函数体里除了开头的begin和结尾的end,就是4句更新语句。

 

4.2.4 后置触发器


函数定义完之后,调用函数的方法也非常简单,就是函数前面加上select就行。但又有另一个问题,每次导入数据后,都要记得手工执行一下这个填充缺失值的函数,有没有办法在每次数据导入后,都能自动地执行这个函数呢?


答案肯定的!PolarDB支持触发器功能,能在数据发生变更前或者变更后,自动运行某个函数。这正好符合我们期望的场景。触发器的定义用CREATE TRIGGER命令,细节大家可以查看文档。

image030.png

如上图所示,一个UDF要能被触发器调用,需要做一些简单的调整:1)返回结果不能是void,而需要改成TRIGGER;2)在end之前,return一个null。然后用create trigger命令创建后置触发器,在每条插入flowers表的语句执行完之后,自动调用update_null_to_zero函数。


接下来我们演示一下。首先创建UDF:update_null_to_zero()。


创建好了之后,先手工调用一下,测试函数功能正常:

image031.png

image032.png

此时,原先是null的字段已经被更新成了0。接着,修改函数定义,变成触发器可调用的函数。

image033.png

即返回类型是trigger,并且添加了return null语句,然后将其绑定到一个后置触发器上。

image034.png

image035.png

如上图所示,在清空表之后,用COPY重新导入,原先哪些导进来是空的数据自动变成了0,就说明触发器已经生效了!

 

4.2.5 过滤异常数据


除了前文提到的后置触发器(after trigger),触发器还有另外一种用法——前置触发器(before trigger)。顾名思义,后置触发器是在数据插入成功后执行,前置触发器是在数据插入之前执行。在前置触发器中,如果最终return null可以直接忽略该行数据;如果return new,则能插入该行数据。因此,利用前置触发器,可以在数据被真正插入到数据库之前,过滤掉异常的数据。

image037.png

例如上图所示,当鸢尾花的样本数据里四个特征全部是null,则认为是异常数据,可以直接忽略。看一下执行效果。

与后置触发器一样,也要先创建一个UDF作为触发器的handler:

image038.png

接着将其绑定到前置触发器,然后再次清空表,并重新导入输入。

image039.png

此时,只copy了150条数据,因为异常数据已经被自动被忽略。

image040.png

 

4.3 分析数据


前文演示了在PolarDB里做数据准备与处理数据的过程,现在正式进入第三步——分析数据。


4.3.1 降维


如前文所说,当前分析数据还非常依赖人工,因此需要通过数据可视化的方式分析数据的分布,即将表格形式转换成图片形式。但原始数据包含花萼、花瓣的长度与宽度,就是4维数据,而图片一般只能展示2维数据。为了能在图片上展示高维数据,需要先对数据降维,把4维的数据降低到2维数据。

image041.png

如上图所示,使用降维方法之前,需要先修改一下flowers表:第一个ID是自增的唯一编号,因为降维算法运算结果,需要通过ID保存回来;后面还有feature1和feature2两个字段,用来保存降维后的两维数据。


有了保存的字段之后,就可以从插件里挑一个降维算法,把四维数据压缩成二维数据,然后再以散点图的方式呈现出来。

image042.png

这里选择了一个比较常用的PCA降维算法,它包含四个参数:


1. 第一个参数是一个字符串形式的表名,告诉这个算法数据是存在哪张表。

2. 第二个字段是一个唯一字段名。

3. 第三个参数是一个字符串数组,告诉算法,原始的高维数据保存在哪些字段里。

4. 第四个参数也是一个数组,告诉算法结果保存在哪些字段里。


上图的例子就是把flowers表里的数据,根据ID字段,把花瓣花萼的长度和宽度这四个字字段作为输入,降维完之后存到feature1和feature2二维的数据里面。降维完成之后再通过散点图的函数展示出来,告诉散点图从哪个表里去拿x轴的数据和y轴的数据以及目标值的数据。如果一切顺利的话,大家能够看到上图右边这样一个散点图展示在终端。

 

4.3.2 可视化展示


同样的,咱们来实操一下。首先新增三个字段,一个是ID自增的,然后一个是feature1和feature2:

image043.png

image044.png

然后用 PCA降维算法把这四个特征变量降维成两个特征变量:

image045.png

此时,feature1与feature2已经有降维后的值了:

image046.png

最后再用散点图去展示数据:

image047.png

可以看到三个花分别用不同的颜色标记出来,并且大体上能够被区分开来。

 

4.3.3 预置算法与绘图函数


同样的,插件中已经预置了一些算法函数以及绘图函数,包括降维、分类、回归、聚类、绘图等。

image048.png

这些函数都是封装了第三方的Python函数:算法函数就是封装了Scikit Learn这个Python机器学习库;函数函数则是封装了Matplotlib。这就是PolarDB的UDF最强大的地方,它海纳百川,可以直接把其他编程语言的生态封装到自己的生态里,并统一用SQL函数的方式提供给使用者。大家可以参考插件的文档,查看有哪些已经封装好的函数,或者也可以自己来尝试封装。

 

4.4 模型训练及预测


通过可视化展示,我们已经知道数据大概是什么样的分布情况,最后我们就可以挑一个聚类算法函数来训练模型,并且用这个模型来预测新数据。

image050.png

如上图所示,我选择了高斯混合模型,因为它用起来最简单,用法和前面的降维函数有点类似。如果训练成功,最后得到类似上图右侧的分布图,可以把三个数据进行聚类,简单的高斯混合模型聚类后效果看起来也还不错。

 

五、苹果公司股价回归分析案例


鸢尾花的分析案例中,使用的是非监督学习的聚类方法,插件中还包含一些监督学习算法。可以试试用线性回归模方法分析苹果股价的走势。过程与鸢尾花的分析一样,也分成四步:

image051.png

其中,在数据准备环节,直接调用预置的load函数加载苹果公司自2015年以来每天的股价收盘价格,并散点图把它展示出来;接着再选择线性回归模型进行训练;最后以折性图的方式把它呈现出来。如果一切顺利,你将能在终端看到如下的折线图:

image053.png

上图是用自增的id作为特征变量,与股价做拟合。真正的股价走势分析是非常严谨和复杂的,本示例只作为演示用途,切勿当真!

 

六、总结


总结前文中涉及到的PolarDB数据库功能:

image054.png

本文用纯粹的SQL完成了鸢尾花聚类与苹果股价分析。其中,在准备数据时,用到了CREATE TABLE创建数据库表,COPY命令导入数据,也学会了如何调用UDF;在处理数据阶段,学会了添加新的列、给列添加默认值、INSERT语句、触发器等。


除了本文罗列的功能,还有许多今天没涉及到的、但非常有用的功能,例如,像面向对象一样的继承表,还有条件索引、表达式索引、倒排索引、全文检索等等。通过这些特性,大家应该能感受到PolarDB是一个非常开放的平台,几乎允许你以任何你喜欢的方式来管理数据。大家可以下载PolarDB,探索一下更多更好玩的功能。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
OLAP on TableStore:基于Data Lake Analytics的Serverless SQL大数据分析
TableStore(简称OTS)是阿里云的一款分布式表格系统,为用户提供schema-free的分布式表格服务。随着越来越多用户对OLAP有强烈的需求,我们提供在表格存储上接入Data Lake Analytics(简称DLA)服务的方式,提供一种快速的OLAP解决方案。
6713 0
MySQL · 特性分析 · 数据一样checksum不一样
背景 有一个特殊环境需要进行人肉迁移数据,对比了表里的数据一模一样,但是无论如何checksum就是不一致,那么问题出在哪里呢? 问题排查 数据是否一致 眼睛都把屏幕盯穿了,也没发现不一致的数据。 导出数据的方式 checksum还是不一致,所以这个原因排除。 MySQL版本 嗯,这个确实不一样,源端是5.5,目的端是5.6,但是这个是checksum函数不一样吗?还是表的结构变了
1431 0
Forrester区块链分析报告:区块链热潮背后的五大技术隐忧
前瞻2018年,区块链离“成熟”还有很长一段距离,既有技术上的不稳定等五大问题,又需要参与者秉持契约精神,达成共识从而切实推动区块链的发展。
4159 0
使用OpenApi弹性释放和设置云服务器ECS释放
云服务器ECS的一个重要特性就是按需创建资源。您可以在业务高峰期按需弹性的自定义规则进行资源创建,在完成业务计算的时候释放资源。本篇将提供几个Tips帮助您更加容易和自动化的完成云服务器的释放和弹性设置。
18703 0
怎么设置阿里云服务器安全组?阿里云安全组规则详细解说
阿里云服务器安全组设置规则分享,阿里云服务器安全组如何放行端口设置教程
10161 0
59
文章
0
问答
来源圈子
更多
阿里云数据库:帮用户承担一切数据库风险,给您何止是安心!支持关系型数据库:MySQL、SQL Server、PostgreSQL、PPAS(完美兼容Oracle)、自研PB级数据存储的分布式数据库Petadata、自研金融级云数据库OceanBase支持NoSQL数据库:MongoDB、Redis、Memcache更有褚霸、丁奇、德哥、彭立勋、玄惭、叶翔等顶尖数据库专家服务。
+ 订阅
相关文档: 云数据库 OceanBase 版 可信账本数据库 云原生关系型数据库 PolarDB PostgreSQL引擎
文章排行榜
最热
最新
相关电子书
更多
JS零基础入门教程(上册)
立即下载
性能优化方法论
立即下载
手把手学习日志服务SLS,云启实验室实战指南
立即下载