《PolarDB forPG:用 SQL 做数据分析》|学习笔记

开发者学堂课程【PolarDB for PostgreSQL 入门：《PolarDB forPG:用 SQL 做数据分析》】学习笔记，与课程紧密联系，让用户快速学习知识。

课程地址：https://developer.aliyun.com/learning/course/813/detail/13916

《PolarDB forPG:用 SQL 做数据分析》

 

内容介绍

一．环境搭建

二．案例演示

三．功能总结

 

数据分析是近几年非常热门的话题，在金融、电商、制造等众多行业都需要用到数据分析。数据分析工具多种多样，这节课用 PolarDB 展示怎么用纯 SQL 做数据分析.后面提到 PolarDB 即使 PolarDB  forPG（不再赘述）。

SQL 是一门非常强大的数据分析，围绕二维表数据结构除了提供增删改查基础功能之外，还提供了非常完整的数据操作，数据查询和数据处理功能，此外在今天的课程里面也会展示通过 PolarDB 的插件是可以实现用 SQL 做数据可视化展示，今天的课程不是专业的数据分析入门课程，所以不需要大家有数据分析的背景，并且为方便演示整个数据分析过程，我是刻意演化过程，整个过程会介绍一些 SQL 简单语法，所以不需要有太多的背景知识，大家可以在自己的电脑上安装一个 PolarDB 进行练习。 

首先来看一下今天的课程内容今天的课程内容有三个部分组成，首先我会介绍一下如何搭建 Pro 的数据分析环境，包括安装和相关的插件，再介绍简单的数据分析的一些知识，并且通过两个案例介绍怎么用 SQL 去做数据分析，介绍的过程里面我会穿插的讲一些收获的基本基础知识，最后总结今天学到的一些关于 polarDB 的知识点。

 

一．环境搭建

1.安装 polardDB

搭建环境完成之后，可以通过 polarDB 的数据库的命令行客户端，命令行的工具运行数据分析的 sql，前面我们已经提到用 SQL 去做数据可视化的展示，所以在今天去运行 sql 的时候，需要在命令行下面去展示图片，就需要大家使用的终端软件里面能够支持这种图片格式，罗列了几款长期的操作系统，上面能够支持显示图片的终端，比如说使用苹果操作系统，推荐用 iQOO，如果使用的是linux操作系统，可以有自带的终端桥，如果用的是 Windows，可以 meanty。

2.环境搭建方法

搭建的方法有两种。

第一种，使用源码搭建，从源码开始一步一步进行手工编译。使用此方式可以通过图片上的链接进行软件安装。

第二种方法比较简单，推荐如果电脑上存在 docker 可以尝试在本地进行搭建 polarDB，边看视频边进行练习，环境搭建好后。启动 SQL 进入运行环境。可以尝试使用 skleam 上的 sql。若在屏幕上查看到安装图片，即为安装成功。

因为下载和安装比较耗时，在这里不进行操作。

 

 

二．案例演示

有很多工作要做掌护，比较繁琐并且融残的，但是我们大致可以把它分成以下四个步骤，第一步要获取数据，第二步是处理数据，第三步是分析数据以及第四步是预测数据。获取数据其实就是把数据加载到数据分析的工具里面，所以先找到要分析的数据，然后将数据导入到你的工具里面，数据可能是散落在互联网上，也可能是在某个文件里面，总而言之，要先在电脑上拿到你的数据时候要去分析的数据，这些刚拿到手的数据通常格式数不正确，比如说如果你是下载的网页，格式可能是很少没有格式，然后有一些是没有格式，有些是呃基层格式等等，甚至还有一些数据格式自定义的，就不是一种通用的格式，为了分析这些数据，我们得先把这些数据做一些格式转换，把它转换成类似于像sv或者局层的各方面处理的一些结构化格式，最后把这些数据导入到分析工具里面在现实的工作里面除了数据格式的不统一之外，数据的往往是的内容往往也是参差不齐的，很少直接拿到手就是一份非常干净，非常完整的数据。

比如有些数据是会缺失一些特征，有些数据是明显能看出来是重复的或者是错误的或者异常的。

 

1. 获取数据

获取数据其实就是把数据加载到数据分析的工具里面

2. 处理数据

3. 分析数据

产生的数据，所以我们还需要对数据做一些进一步的处理，比如说我们如果遇到缺失的数据，就可以提出默认值，然后有些明显是重复的或者是异常的数据，那我们就把它过滤掉，这个过程一般也称为特征工程，是耗时非常长的一个过程，大家在做数据分析的时候，这个过程可能是占用了你特别长的一个时间。

然后终于我们把数据整理的干干净净，非常整齐，这时候才能真正开始去做数据分析，但是数据分析这个过程目前为止还是非常依赖人工的，然后需要大家凭借自己的经验来观察数据，到这个时候一般我们会考虑说先把数据以一种可视化的方式展示出来，通过人工去观察，不断的去寻找其特征，最后用一些复杂度合适的一个模型去训练出来，并且不断的去调参数去调用，然后有了模型之后

4. 预测数据

然后有了模型之后，可以预测新的数据，并且根据结果不断的去优化升级模型，讲到这里的时候，大家可能会有一个疑问，这好像是做机器学习的模型训练没错，因为机器学习的本质其实就是通过数学建模来理解数据和分析数据，所以如果机器学习是一种大家可以把机器学习当成一种数据分析的方法，并且在接下来的案例里面，也会用到一些机器学习的算法来帮助我们更好的理解和分析数据。

（1）鸢尾花分类案例

数据集介绍

鸢尾花卉数据集（英文：Iris flower data set)是一类多重变量分析的数据集.最初由埃德加安德森从加拿大加斯帕半岛上的鸢尾属花朵中提取的形态学变异数据，后由罗纳德费雪作为判别分析的一个例子，运用到统计学中，该致据失包含了150个样本，都属于鸢尾属下的三个亚属，分别足山这尾、变色这尾和维吉尼亚这尾.样本包含花碧与花的长度和宽度四个特征,基于这四个特征的集合，可分析以确定其属种，是非常经典的分类问题数据集。

下载地址

中文维基百科：http://archive ics uci.edu/ml/datasets/Iris

插件预置数据：home/postgres/datasets/ns.csv

山鸢尾数据

变色鸷尾数据

 

维吉尼亚鸿尾数据

分析的工具是多种多样的，每种工具的数据保存是不同的，比如如果是用 excel 来做数据分析，用到的就是 excel

的表格，如果是其他用做数据分析比较常见的有二维表，在 sql 所有的数据都是以一种数据库表格的形式保存和记录的，所以要先创建出一张数据库的表来保存数据，大家可以打开链接看语法。

建表语句参考

(PostgreSQL 13中文文档》-SQl 命令

-CREATE TABLE:http://www.postgres.cn//docs/13sal-createtable.html

创建数据统一的表示形式：二维表

create table flowers

sepal_length float8

sepal width float8,

patal_length float8,

patal width float8,

species text

);

polarDB 是基于开源的数据库。对SQL支持比较好。创建一个 flowers 的表包括五个字段,前面四个类型是 double的浮点数。分别是雨花般的长度和宽度，最后一个字段的类型是 text，可以保存任意长度的字段并且性能较好。用此来保存花的品种属性。

添加表与字段的注释

comment on table f1 owers is'鸢尾花数据'；

comment on column flowers.sepal_length is'花萼长度'；

comment on column f1 owers.sepal_width is'花萼宽度'；

comment on column f1 owers.patal_1 ength is'花长度'；

comment on column f1 owers.patal_width is'花宽度'；

comment on column f1 owers.species is'属种'；

给数据库字段和表添加注释是一种非常好的习惯,它可以方便我们理解数据的含义内容.所以若大家创建表语字段，可以尽早将其进行注释。

COPY 语句参考

·《PostgreSQL13中文文档》-SQL 命令

-COPY:http://www.postgres..cn/docs/13 ql-copy.html

数据导入：从 CSV 到数据库表

数据导出：从数据库表到 CSV

\copy flowers

from datasets/iris.csv'

with (format 'csv',

header true,

delimiter ',')

\copy flowers

to 'datasets/test.csv'

with (format 'csv',

header true,

delimiter ',')

当数据量小时可以通过手工这样数据导入，数据量大时手工导入不太可能，比如原尾花数据150行，比较难以手工导入。polarDB 提供 copy 命令，可以批量导入或数据导出从而简便，比如大家看那个 PPT 上的代码。从 CSV 导入或导出数据,规定了表的格式分割符是逗号.

演示：

在苹果上进行演示此时 polarDB 已经被打开

[root@ea81c9721241 datasets]#ls

boston.csv diabetes.csv

digits.csv iris.csv shares.csv

[root@ea81c9721241 datasets]#pwd

/home/postgres/datasets

[root@ea81c9721241 datasets]#head iris.csv

sepal_length,sepal_width,patal_length,patal_width,species

5.1,3.5,1.4,0.2,setosa

4.9,3.0,1.4,0.2,setosa

4.7,3.2,1.3,0.2,setosa

4.6,3.1,1.5,0.2,setosa

5.0,3.6,1.4,0.2,setosa

5.4,3.9,1.7,0.4,set0sa

4.6,3.4,1.4,0.3,setosa

5.0,3.4,1.5,0.2,setosa

4.4,2.9,1.4,.2,setosa

[root@ea81c9721241 datasets]#wc -l iris.csv

151 iris.csv

[root@ea81c9721241 datasets]#

额外包含标题，现在将此数据导入 polarDB 中

查看数据库可以正常使用  

展现出一张 polarDB 图片，即可以使用。

此时是一张空表，添加数据此时可以观察到有5个字段，浮点型（前四个）和字符型。 

\copy flowers

from datasets/iris.csv'

with (format 'csv',

header true,

delimiter ',')

进行数据导入

此时导入150行

导出

预置数据集介绍

·插件已预置了一些常见的数据集，其中包括高尾花数据集，

·预置数据集文件放置在容器的/home/postgres/datasets 目录下。

·插件同时提供了一些以 Ioad”开头的函数，可自动创建数据库表，并导入数据

·执行门 df load“查看预置的导入函数.

预置数据集清单

·load_boston:导入波士倾房价数据集，可用于回归分析.数据位于 home/postgres/datasets/boston.csv.

·load iris:导入鸢尾花数据集，可用于分类分析.数据位于/home/postgres/datasets/.is.csv.

·load diabetes:导入穗尿病数据集，可用于回归分析.数据位于 home/postgres/datasets/,diabetes.csv.

·load_digits:导入手写数字数据集，可用于分类分析.数据位于/home/postgres/datasets/digits.csv.

·load_shares:导入苹果公司股价数据集，可用于回归分析.数据位于/home/postgres/,datasets/shares.csv,

1.修改字段默认值语句参考

·(PostgreSQL13中文文档》-SQL 命令

-ALTER TABLE:http://www.postgres.(n/dos/13/sql-altertable.html

新建表时添加字段默认值

create table flowers

sepal_length float8 default 0,

sepal_width float8 default 0,

patal_length float8 default 0,

patal_width float8 default 0,

species text

鸢尾花150条是非常干净，非常整齐的数据，在现实的工作当中，数据往往会有很多缺失值，或者说有很多异常值需要对数据新作一本清晰，比如刚刚插入的一条新的文化的样本，但是部分的数据缺失，在时希望分析工具能自动的帮我们在数据缺失的时填充上一些默认值，比如我们填充已经提供了一种叫数据库表字段默认值的功能，在数据库知道缺失的时候，就会自动使默认值，补充给这个制造加上问题，然后给出库的表示添加后的方法能用第一种就是在表不存在的时候，创建表的时候直接指定字段的默认值，就像 PPT 上面左边的代码。

另外一种是已经创建好，制作也已经存在，这时候想要给字段去添加上新的一个默认值，或者说修改相关，同右侧一样，然后给创建的表四个字段加上默认值的具体的用法，可以根据 PPT 上的文档链接去查看。

已存在的表字段添加默认值

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;

定义完成命令符号可以通过 insert 插入命令值,Insert 命令具体用法可以通过文档进行查看 insert 的两种命令方法。比如第一种同代码左侧，不去指定需要用到的列。第二种方法如同右侧代码指定需要的列。

问 copy 到如数据时会使用到默认值吗？

此时没有默认值，添加一段默认值

此时默认值为0.

另一种加入默认值：

用 copy 导入不存在默认值

但是每次导入数据之后都要执行这四条语句，其实很麻烦，有没有办法一次性有4次行的领域呢？这里就要介绍一个非常强大的功能，叫用 UDF 户定义的函数体积非常强大，甚至可以把它看成是一种发平台就放起来的位置。

允许我们用多种不同的编程语言来写函数，并且呢是不用重启数据库，更不用重新逼你数据库内核就可以直接动态的去创建和使用不同的状态函数，使用函数。插入数据语句参考

·(PostgreSQL13中文文档》-SQL 命令-CREATE FUNCTION:http://www.postgres.<n/docs/13/sqL(reatefunction html

创建 UDF(User Defined Function)

create function update_null_to_zero()returns void as $begin

update flowers set sepal_length 0 where sepal_length is null;

update flowers set sepal width =0 where sepal width is null;

update frowers set patal_length =0 where patal_length is null;

update flowers set patal_width =0 where patal_width is null;

end;$language plpgsql;

调用 UDF:像用 FaaS 一样用 UDF

select update null to zero();

大家可以参考一下 PPT，在 PPT 上面大家可以看到我用了 user 中的语言来创建了一个 q 语言，自带的计算器扩展，除此之外你还可以用原生的，或者其他语言写 UDF。

提到函数，必须有返回值，里面除了开始和结尾的总共也就是四句更新语句，函数定义完了之后调用函数的方法，之前大家也看到了 select，就在函数名和名字名前加个 select 就可以。

出现问题：能否在插入数据后自动调用 UDF?

可以的。

数据发生变更之前或者变更之后自动运行，正好符合我们的产品，表里的数据并更完之后去执行，触发器的定义是x，可以看文档，如果一个 udf 想要对触发器能够调用，还需要调整，比如返回结果不能是 void，并且至少要返回一个结果出来，然后用命令创建一个触发器，在每条插入表的语句执行完之后自动的去调用，可以很大负责任函数。

插入数据语句参考

(PostgreSQL13中文文档》-SQL 命令-CREATE TRIGGER:http://www.postgres.cn/docs/13 ql-createtriggerhtml

修改 UDF 的返回值为 TRIGGER

drop function if exists update_null_to_zero();

create function update null_to zero()returns TRIGGER as$

begin ..RETURN NULL;end;$language plpgsql;

后置触发器：每句插入语句执行后都填充缺失值

create trigger flowers_after_insert_trigger

after insert on flowers

for each statement

execute procedre update null to zero();

返回结果，return

创建并调用

被改成触发器

插入前置触发器过滤异常数据

创建函数：排除所有值都为空的样本

create function ignore_empty_sample()returns trigger as $begin

if new.sepal_length is null

and new.sepal width is null

and new.patal length is null

and new.patal_width is null then

return null;

else

return new;

end if;

end;$language plpgsql;

前置触发器：每行记录插入前都检查是否是异常值

create trigger flowers_before_insert_trigger before insert on flowers

for each row execute procedure ignore_empty_sample();

注意：

所以和这个相比 before 还有另外一种更好的，用法就是在数据真正被插入之前，我们可以判断一下数据是不是异常数据，如果异常数据，直接忽略这条记录，就让其不要插进来。

Copy150条

将4维数据变成二维数据，需要用降维方法

在降维前可以先修改此表

添加字段语句参考

·(PostgreSQL13中文文档》-SQL 命令-ALTER TABLE:http://www.postgres.cn/dos/13/sql-altertablehtml

已存在的表添加新的字段

alter table flowers

add column id bigserial primary key,

add column featurel float8,

add column feature2 float8;

comment on column f1 owers.id is'唯-编号'；

comment on column f1 owers.feature1is‘降维后的第一个特征'；

comment on column f1 owers.feature2is'降维后的第二个特征'；

通过 id 可以标记

选择比较常用的 PC 的网算法，它包含四个参数，第一个参数是一个字符，表明告诉的方法数据是存在条例的结果。

第二个参数是刚刚提到的用来更新数据时用哪个文件。

第三个参数是一个字符串形式的数组，告诉创法原始的那些高位数据是存在哪些字段里面的。

第四个参数就是一个是一个数组，告诉降维完成数据是存在哪些里面去。

降维完成后通过散点图表示出，告知 x，y 走的数据，若一切顺利可观察到如下图：

降维演示：

4个特色字段加一个目标字段

多出3个字段

用降维算法将4个变量变成2个

多出两列已经被填充完成值

三种花用不同的颜色标记出来，大体上是能够被区分出来的

实际上插件已经预置了非常多的函数，包括降为分类，回归，聚类，绘图等等，具体函数用法参考插件文档，如果学会 udf 可以自己尝试安装分装类。

鸢尾花分类案例：分析数据 ：预置函数

插件已经包装了一些 Scikit Learn 的算法以及 Matplotlib 绘图函数

预置降维算法

·插件已封装了一些 Scikit Learng 的算法，包括降维、聚类、分类和回归.

·其中，降维算法是“decomposition°开头的函数

·执行门 df decomposition_”查看预置的降维函数.

·法函放的调用方式都是指定来源表与字段，以及目标表与字段，

预置分类算法

·分类算法是“classification”开头的函数

·执行门 df classification_“直看预置的分类函数，

预置回归算法

·回归算法是“regression”开头的函数.

·执行 df regression_”查看预置的回归函数，

预置绘图函数

·绘图函改足“show”开头的函数，

·执行门 df show_“查看预置的绘图函数。

鸢尾花分类案例一预测数据——模型训练及预测

将多维数据降维成二维数据

1.使用高斯混合分类算法

select classification_gaussian_mixture(

'flowers',‘id'

ARRAY['sepal length',

'sepal_width',

'patal_length',

patal_width'],

公

‘species',-训练标签

'predict');-保存预测结果

简单的高速混合模型看起来效果也还不错.

(2) 苹果公司股价分析案例

分成4部用散点图展现，折线图绘画。

准备数据

select load_shares();-~加载预置的股价数据

分析数据

select show scatter('shares','id','prices');

训练模型并预测结果

select regression_linear('shares','id',ARRAY['id'],'price','predict');

展示拟合结果

select show plot('shares','id','price','predict');

 

三．功能总结

我们鸢尾花完成分类和苹果股价分析，其中在数据准备环节的时候，我们用到了 create table 来创建是表，用命令来导入数据，也学会了怎么留点符，在数据处理的环节，又学会怎么去添加新的页比例去添加默认值，怎么通过一次的语句去用到监管值，还有触发器，一些udf的定义等等，除了这些功能以外，还有许多今天是没有时间涉及到的，但是可以自行做。

通过这些特性可知 polarDB 是一个非常开放性的平台。可以通过任何自己所喜欢的方式来展示使用数据。