数据分析是近年来非常热门的话题，在金融、电商、制造等很多行业里面都需要用到数据分析。数据分析的工具多种多样，比如有些同学会用Excel做数据分析，在银行里面可能用SAS软件做数据分析，还有通过Python或R等编程语言来做数据分析。
本文想向大家展示一下在PolarDB里如何用纯SQL做数据分析，下文提到的PolarDB特指PolarDB for PG。
SQL是一门非常强大的数据分析语言，它围绕着二维表数据结构，除了提供增删改查这些基础功能之外，还提供了非常完整的数据操作、数据查询和数据处理的功能。此外，在本文里也会向大家展示通过PolarDB的插件，可以实现用SQL做数据可视化展示。
本文不是专业的数据分析入门教程，所以不需要大家有数据分析的背景；并且为了方便演示，数据分析的过程也会简化；过程中还会介绍一些SQL的简单语法，所以不需要有太多的背景知识。
一、AGENDA
本文由三个部分组成：
1. 首先介绍如何搭建PolarDB的数据分析环境，包括安装PolarDB和相关插件
2. 然后介绍简单的数据分析知识，并通过两个案例展示如何用SQL做数据分析，过程中会穿插一些SQL的基础知识。
3. 最后总结本文涉及到的数据库相关的知识点。
二、环境搭建
我们就先来看一下怎么去搭建一个数据分析的环境。
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图片，那就说明已经安装成功了。
三、数据分析简介
环境搭建好之后，我们就可以开始动手做数据分析了。一般情况下做数据分析要有很多的工作要做，整个过程比较繁琐并且冗长的，但是我们可以大致把它分成以下四个步骤。
1. 获取数据：把数据加载到数据分析工具里，即先获得要分析的数据，再将数据导入到工具中。这些待分析的数据可能是散落在互联网上，也可能是在某个文件里面，总而言之我们得先获得想要分析的数据。但是，刚拿到手的数据，格式通常是五花八门的，比如下载的网页格式通常是HTML，有些数据是XML格式，也有些是JSON格式，甚至还有一些数据格式是自定义的，不是通用的格式。为了能分析这些数据，得先把这些数据做格式转换，转换成方便处理的结构化格式（例如CSV、JSON等），然后导入到分析工具中。
2. 处理数据：提取特征数据。在真是的工作场景中，除了数据格式不统一，数据的内容往往也参差不齐，很少能直接拿到一份干净又完整的数据。比如有些样本数据会缺失部分特征、有些数据是明显的异常数据。所以在开始分析之前，还需要对数据做进一步的处理，比如用默认值填充缺失的值、过滤掉明显重复的或者异常的数据，这个步骤一般也叫“特征工程”，需要耗费比较长的时间才能把数据整理好。
3. 分析数据：有了干净整齐的数据，才能真正开始分析数据。到目前为止，数据分析的工作还是非常依赖人工的，需要大家根据自己的经验来观察数据。此时，通常会把数据以各种图表的形式展示出来，人工观察并总结一些特征，再根据这些特征选择一些复杂度合适的模型，然后开始训练并不断调优。
4. 预测数据：最后，等模型训练出来后，就可以拿它来预测新的数据，然后根据结果不断优化和升级模型。
看到这里大家可能会有个疑问，这好像是做机器学习的模型训练。没错，因为机器学习的本质其实就是通过数学建模来理解数据和分析数据，机器学习就是一种数据分析的方法。在后文的案例演示中，也会用到一些机器学习的算法，来帮助我们更好地理解和分析数据。
四、鸢尾花聚类分析案例
鸢尾花数据集是一个非常经典的数据集，通常是会用来做聚类或分类问题的入门数据。它包含150个样本，包括了三种鸢尾花，分别是山鸢尾、变色鸢尾和维吉尼亚鸢尾。每种花分别有50个样本，并且每个样本都包含花萼的长度与宽度，还有花瓣的长度与宽度，总共有四个特征，以及它属于哪种花。
如上图所示，第一个表格的第一行山鸢尾的花萼长度是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标准的支持还是比较好的。
如上图所示，创建一张叫flowers的表，包含5个字段，前4个字段的类型是float8，就是double类型的浮点数，分别是花萼与花瓣的长度与宽度；最后一个字段类型是text，就是任意长度的字符串，用来保存花的品种。
上图中右侧的代码是给数据库表和字段添加注释，用来说明表和字段的用途。给数据库的表和字段添加注释是一个非常好的习惯，可以帮助理解数据，以及每个字段的含义。推荐大家在创建数据库表或新增字段时，及时地给表或字段加上注释。
4.1.2 导入数据到数据库表
创建了表之后，如何将数据导进来呢？在数据量小的时候，可以通过手工逐行insert数据，但是如果数据量大，手工insert就几乎不可能。比如鸢尾花的数据一共有150行，手工insert 150次是一件很费劲的事情。好在PolarDB提供了一个COPY的命令，可以批量地导入或者批量导出数据。如下图所示：
其中左边的代码是从CSV文件里导入数据到flowers表中；右边的代码是把flowers表的数据导出到test.csv这个文件里。with子句指定数据的格式信息，比如上图中指定格式是CSV格式、包含表头、分隔符是逗号。
安装完插件后，在Docker容器内/home/postgres/datasets目录下，已经预置了一些CSV文件，其中iris.csv就是本案例中用到的鸢尾花数据，如下图所示：
接着，这个数据导入到PolarDB里面来。先通过PSQL连接到PolarDB数据库，当前数据库是空的，里面是没有任何的一张表，所以通过建表语句，先创建一张数据库表。如下图所示：
然后再添加注释，通过\d+ flowers可以看到数据库表的定义。如下图所示：
最后，通过copy命令把CSV的数据导进来：其中格式是CSV格式、header为true（即包含了标题），以及分隔符是逗号。如下图所示：
提示导进来了150行数据，通过select * from flowers可查看数据：
再试试把表中的数据导出到一个新的文件里面。如下图所示：
导出成功后，切换到Shell里，可以看到datasets目录里多出了一个叫test.csv的文件，并且有151行内容（包含标题）。
4.1.3 其他预置数据
如前文演示中看到的， dataset目录下面还有其他预先准备好的CSV数据，都可以手工创建表，然后用copy命令把数据导进来。为了方便使用，PolarDB的插件其实还额外提供了一批以load开头的函数，执行这些函数就能自动创建数据库的表，并把CSV的数据导入到表中。如下图所示：
目前插件里包含了这5份数据，分别是波士顿房价数据、鸢尾花数据、糖尿病数据、手写数字数据，以及苹果公司股价数据。其中房价数据、糖尿病数据和股价数据可以用来做回归分析，另外两份数据可以用来做分类分析。
4.2 处理数据
4.2.1 定义缺失值
鸢尾花这150条数据是非常完整并且干净的数据，如前文所说，现实工作中的数据往往会有许多缺失值或者异常值，需要先对数据做清洗。比方说，如果我们现在插入一条新的鸢尾花的样本，但部分的数据缺失了，希望分析工具在数据缺失的时候自动填充上默认值（例如0）。PolarDB里面有一个数据库字段默认值的功能，在数据库字段缺失的时候，就会自动使用默认值来填充。
如上图所示，给字段加默认值有两种方法：第一种是在表还不存在的时候，创建表的时候直接定义字段的默认值，就像图中左边的代码一样，也用到了CREATE TABLE；第二种是表已经创建好了，想给已经存在的字段添加默认值或者修改默认值，这时候就需要用到另一个命令，ALTER TABLE，代码就像图中右边一样，给刚刚创建的表前4个字段全部加上默认值。
4.2.2 使用缺失值
定义好了默认值，接下来就可以通过INSERT命令来插入默认值了。
如上图所示，PolarDB中有两种INSERT方式可以用到默认值：第一种就像左边的代码，在INSERT的时候，不指定有默认值的列；第二种，指定了列，但values里面用default关键字。这两种方式都能用上默认值，但用COPY导入数据的话，能不能用上默认值呢？我们来测试一下。
再看一下flower表的定义：
其中每个字段的默认值是空（即null）。尝试插入一行脏数据，除了品类，其他的字段都不指定：
可以看到现在插进来的这行数据里面，其他字段的值都是空的。接着用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表的定义：
现在字段的默认值都已经有了，全部都是0。然后再试试重新insert一条bad2的脏数据：
此时，bad2的其他字段都是0。可以再试试另外一种用default关键词的insert的方式：
可以看到bad2与bad3都使用了默认值0。最后再测试一下copy命令是否可以用到默认值。
首先清空数据：
然后编辑test.csv，手工加入3行脏数据。最后通过copy命令，把test.csv的数据导进来：
一共导入了153行，但脏数据的字段依然为空：
说明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，详细用法大家可以参考文档。
上图中用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命令，细节大家可以查看文档。
如上图所示，一个UDF要能被触发器调用，需要做一些简单的调整：1）返回结果不能是void，而需要改成TRIGGER；2）在end之前，return一个null。然后用create trigger命令创建后置触发器，在每条插入flowers表的语句执行完之后，自动调用update_null_to_zero函数。
接下来我们演示一下。首先创建UDF：update_null_to_zero()。
创建好了之后，先手工调用一下，测试函数功能正常：
此时，原先是null的字段已经被更新成了0。接着，修改函数定义，变成触发器可调用的函数。
即返回类型是trigger，并且添加了return null语句，然后将其绑定到一个后置触发器上。
如上图所示，在清空表之后，用COPY重新导入，原先哪些导进来是空的数据自动变成了0，就说明触发器已经生效了！
4.2.5 过滤异常数据
除了前文提到的后置触发器（after trigger），触发器还有另外一种用法——前置触发器（before trigger）。顾名思义，后置触发器是在数据插入成功后执行，前置触发器是在数据插入之前执行。在前置触发器中，如果最终return null可以直接忽略该行数据；如果return new，则能插入该行数据。因此，利用前置触发器，可以在数据被真正插入到数据库之前，过滤掉异常的数据。
例如上图所示，当鸢尾花的样本数据里四个特征全部是null，则认为是异常数据，可以直接忽略。看一下执行效果。
与后置触发器一样，也要先创建一个UDF作为触发器的handler：
接着将其绑定到前置触发器，然后再次清空表，并重新导入输入。
此时，只copy了150条数据，因为异常数据已经被自动被忽略。
4.3 分析数据
前文演示了在PolarDB里做数据准备与处理数据的过程，现在正式进入第三步——分析数据。
4.3.1 降维
如前文所说，当前分析数据还非常依赖人工，因此需要通过数据可视化的方式分析数据的分布，即将表格形式转换成图片形式。但原始数据包含花萼、花瓣的长度与宽度，就是4维数据，而图片一般只能展示2维数据。为了能在图片上展示高维数据，需要先对数据降维，把4维的数据降低到2维数据。
如上图所示，使用降维方法之前，需要先修改一下flowers表：第一个ID是自增的唯一编号，因为降维算法运算结果，需要通过ID保存回来；后面还有feature1和feature2两个字段，用来保存降维后的两维数据。
有了保存的字段之后，就可以从插件里挑一个降维算法，把四维数据压缩成二维数据，然后再以散点图的方式呈现出来。
这里选择了一个比较常用的PCA降维算法，它包含四个参数：
1. 第一个参数是一个字符串形式的表名，告诉这个算法数据是存在哪张表。
2. 第二个字段是一个唯一字段名。
3. 第三个参数是一个字符串数组，告诉算法，原始的高维数据保存在哪些字段里。
4. 第四个参数也是一个数组，告诉算法结果保存在哪些字段里。
上图的例子就是把flowers表里的数据，根据ID字段，把花瓣花萼的长度和宽度这四个字字段作为输入，降维完之后存到feature1和feature2二维的数据里面。降维完成之后再通过散点图的函数展示出来，告诉散点图从哪个表里去拿x轴的数据和y轴的数据以及目标值的数据。如果一切顺利的话，大家能够看到上图右边这样一个散点图展示在终端。
4.3.2 可视化展示
同样的，咱们来实操一下。首先新增三个字段，一个是ID自增的，然后一个是feature1和feature2：
然后用 PCA降维算法把这四个特征变量降维成两个特征变量：
此时，feature1与feature2已经有降维后的值了：
最后再用散点图去展示数据：
可以看到三个花分别用不同的颜色标记出来，并且大体上能够被区分开来。
4.3.3 预置算法与绘图函数
同样的，插件中已经预置了一些算法函数以及绘图函数，包括降维、分类、回归、聚类、绘图等。
这些函数都是封装了第三方的Python函数：算法函数就是封装了Scikit Learn这个Python机器学习库；函数函数则是封装了Matplotlib。这就是PolarDB的UDF最强大的地方，它海纳百川，可以直接把其他编程语言的生态封装到自己的生态里，并统一用SQL函数的方式提供给使用者。大家可以参考插件的文档，查看有哪些已经封装好的函数，或者也可以自己来尝试封装。
4.4 模型训练及预测
通过可视化展示，我们已经知道数据大概是什么样的分布情况，最后我们就可以挑一个聚类算法函数来训练模型，并且用这个模型来预测新数据。
如上图所示，我选择了高斯混合模型，因为它用起来最简单，用法和前面的降维函数有点类似。如果训练成功，最后得到类似上图右侧的分布图，可以把三个数据进行聚类，简单的高斯混合模型聚类后效果看起来也还不错。
五、苹果公司股价回归分析案例
鸢尾花的分析案例中，使用的是非监督学习的聚类方法，插件中还包含一些监督学习算法。可以试试用线性回归模方法分析苹果股价的走势。过程与鸢尾花的分析一样，也分成四步：
其中，在数据准备环节，直接调用预置的load函数加载苹果公司自2015年以来每天的股价收盘价格，并散点图把它展示出来；接着再选择线性回归模型进行训练；最后以折性图的方式把它呈现出来。如果一切顺利，你将能在终端看到如下的折线图：
上图是用自增的id作为特征变量，与股价做拟合。真正的股价走势分析是非常严谨和复杂的，本示例只作为演示用途，切勿当真！
六、总结
总结前文中涉及到的PolarDB数据库功能：
本文用纯粹的SQL完成了鸢尾花聚类与苹果股价分析。其中，在准备数据时，用到了CREATE TABLE创建数据库表，COPY命令导入数据，也学会了如何调用UDF；在处理数据阶段，学会了添加新的列、给列添加默认值、INSERT语句、触发器等。
除了本文罗列的功能，还有许多今天没涉及到的、但非常有用的功能，例如，像面向对象一样的继承表，还有条件索引、表达式索引、倒排索引、全文检索等等。通过这些特性，大家应该能感受到PolarDB是一个非常开放的平台，几乎允许你以任何你喜欢的方式来管理数据。大家可以下载PolarDB，探索一下更多更好玩的功能。
