Erlo

前沿科技探究DeepSQL:库内AI算法

2022-03-25 11:30:25 发布   227 浏览  
页面报错/反馈
收藏 点赞

数据库DeepSQL特性实现DB4AI功能,即在数据库内实现AI算法,以更好的支撑大数据的快速分析和计算。这里提供了一整套基于SQL的机器学习、数据挖掘以及统计学的算法,用户可以直接使用SQL进行机器学习工作。Deep SQL能够抽象出端到端从数据到模型的研发过程,配合底层的引擎及自动优化,具备基础SQL知识的技术人员即可完成大部分的机器学习模型训练及预测任务。整个分析和处理都运行在数据库引擎中,用户可以直接分析和处理数据库内的数据,不需要在数据库和其它平台之间进行数据传递,避免在多个环境之间进行不必要地数据移动。

  • 概述

  • 环境部署

  • 使用指导

  • 最佳实践

  • 常见问题处理

一、概述

DeepSQL是对openGauss DB4AI能力的增强,让对MADLib比较熟悉的数据分析师或开发者可以轻松迁移到openGauss上进行工作。DeepSQL将常用的机器学习算法封装为SQL语句,支持60多个常用算法。其中包括回归算法(例如线性回归,逻辑回归,随机森林等)、分类算法(比如KNN等)、聚类算法(比如K-means)等。除了基础的机器学习算法之外,还包括图相关的算法,比如最短路径,图形直径等等算法;此外还支持数据处理(比如PCA),稀疏向量,统计学常用算法(比如协方差,Pearson系数计算等),训练集测试集分割方法,交叉验证方法等。

表 1 支持的机器学习算法 - 回归类算法

算法中文名称

算法英文名称

应用场景

逻辑回归

Logistic Regression

例如寻找某疾病的危险因素,金融商业机构需要对企业进行评估等。

预测:根据模型预测不同的自变量情况下某病或某情况的发生概率。

判别:实际上跟预测类似,也是根据模型判断某人属于某病或属于某种情况的概率有多大,即判断某人有多大可能是属于某病。

Cox比例风险回归

Cox Proportional Hazards Regression

该模型以生存结局和生存时间为因变量,可同时分析众多因素对生存期的影响,能分析带有截尾生存时间的资料,且不要求估计资料的生存分布类型。由于上述优良性质,该模型自问世以来,在医学类研究中得到广泛的应用,是迄今生存分析中应用最多的多因素分析方法。

弹性网络回归

Elastic Net Regularization

弹性回归是岭回归和套索回归的混合技术,它同时使用 L2 和 L1 正则化。当有多个相关的特征时,套索回归很可能随机选择其中一个,而弹性回归很可能都会选择。

广义线性模型

Generalized Linear Models

在一些实际问题中,变量间的关系并不都是线性的,这种情况就应该用曲线去进行拟合。

边际效应

Marginal Effects

提供边际效应的计算。

多类回归

Multinomial Regression

如果目标类别数超过两个,这时就需要使用多类回归,如疗效可能是“无效”,“显效”,“痊愈”三类。

序数回归

Ordinal Regression

在统计学中,序数回归是一种用于预测序数变量的回归分析,即其值存在于任意范围内的变量,不同值之间的度量距离也不同。它可以被认为是介于回归和分类之间的一类问题。例如,病情的分级(1、2、3、4级),症状的感觉分级(不痛、微痛、较痛和剧痛),对药物剂量反应的分级(无效、微效、中效和高效)等等。不同级别之间的差异不一定相等,如不痛与微痛的差值不一定等于较痛与剧痛的差值。

聚类方差

Clustered Variance

Clustered Variance模块调整聚类的标准误差。例如,将一个数据集合复制100次,不应该增加参数估计的精度,但是在符合独立同分布假设(Independent Identically Distributed,IID)下执行这个过程实际上会提高精度。

稳健方差

Robust Variance

Robust Variance模块中的函数用于计算线性回归、逻辑回归、多类逻辑回归和Cox比例风险回归的稳健方差(Huber-White估计)。它们可用于计算具有潜在噪声异常值的数据集中数据的差异。

支持向量机

Support Vector Machines(SVM)

用于文本和超文本的分类、图像分类,比起传统的查询优化方案,支持向量机能够获取明显更高的搜索准确度。这同样也适用于图像分割系统。

线性回归

Linear Regression

应用广泛,例如经济学、金融学等。

表 2 支持的机器学习算法 - 其他监督学习

算法名称(中文)

算法名称(英文)

应用场景

决策树

Decision Tree

最为广泛的归纳推理算法之一,处理类别型或连续型变量的分类预测问题,可以用图形和if-then的规则表示模型,可读性较高。

随机森林

Random Forest

随机森林是一类专门为决策树分类器设计的组合方法。它组合多棵决策树作出的预测。

条件随机场

Conditional Random Field (CRF)

条件随机场(CRF)是一种判别的,无向概率的图形模型。线性链CRF是一种特殊类型的CRF,它假定当前状态仅取决于先前的状态。在分词、词性标注和命名实体识别等序列标注任务中取得了很好的效果。

朴素贝叶斯

Naive Bayes

通过计算概率来进行分类,可以用来处理多分类问题,比如:垃圾邮件过滤器。

神经网络

Neural Networks

拥有广泛的应用场景,譬如语音识别、图像识别、机器翻译等等。在模式识别的领域中算是标准监督学习算法,并在计算神经学中,持续成为被研究的课题。MLP已被证明是一种通用的函数近似方法,可以被用来拟合复杂的函数或解决分类问题。

k临近算法

k-Nearest Neighbors

K近邻分类方法通过计算每个训练样例到待分类样品的距离,取和待分类样品距离最近的K个训练样例,K个样品中哪个类别的训练样例占多数,则待分类元组就属于哪个类别。

可用于:文字识别,面部识别,基因模式识别,客户流失预测、欺诈侦测。

表 3 支持的机器学习算法 - 数据处理类算法

算法名称(中文)

算法名称(英文)

应用场景

数组操作

Array Operations

数组、向量操作运算,包括基础的加减乘除、幂运算、开方、cos、sin、绝对值、方差等。

主成成分分析

Dimensionality Reduction (PCA)

降维,计算主成分。

变量编码

Encoding Categorical Variables

当前支持one-hot和dummy编码技术。

当需要用一组特定的预测变量与其它预测变量组作比较时,通常使用哑编码(dummy coding),与之比较的变量组称为参照组。One-hot编码与哑编码类似,两者的区别是前者为每种分类值建立数字类型的0/1指示列。在每行数据中(对应一个数据点),只有一个分类编码列的值可以为1。

矩阵操作

Matrix Operations

运用矩阵分解,将大型矩阵分解成简单矩阵的乘积形式,则可大大降低计算的难度以及计算量。

矩阵加减乘除、最值、均值、求秩、求逆、矩阵分解(QR,LU,Cholesky),特征提取。

规范化和距离函数

Norms and Distance Functions

求范数,余弦相似度,向量间距离。

稀疏向量

Sparse Vectors

实现稀疏向量类型,如果向量中重复值较多,可以用来压缩储存节省空间。

透视图

Pivot

透视表或枢轴表,通常用来实现OLAP或报表系统中一类常见的行列转置需求。pivot函数能够对一个表中存储的数据执行基本行转列操作,并将汇总后的结果输出到另一个表中。使行列转置操作变得更为简单与灵活。

模式匹配

Path

是在一系列行上执行常规模式匹配,并提取有关模式匹配的有用信息。有用的信息可以是简单的匹配计数或更多涉及的内容,如聚合或窗口函数。

会话

Sessionize

会话化功能对包括事件序列的数据集执行面向时间的会话重建。定义的不活动时段表示一个会话的结束和下一个会话的开始。

可以用于:网络分析,网络安全,制造,财务和运营分析。

共轭梯度法

Conjugate gradient

求解系数矩阵为对称正定矩阵的线性方程组的数值解的方法。

词干提取

Stemming

词干提取简单说就是找出单词中的词干部分,场景比如:搜索引擎建立网页主题概念。

在英文网站优化作用明显,对其他语言有借鉴意义。

训练集测试集分割

Train-Test Split

分割数据集,把一份数据集划分成训练集和测试集,train的部分用于训练,test部分用于验证。

交叉验证

Cross Validation

交叉验证。

预测指标

Prediction Metrics

用于评估模型预测的质量,包括均方误差,AUC值、混淆矩阵、修正R方等用于评价模型的函数。

小批量预处理

Mini-Batch Preprocessor

把数据打包成小份进行训练,优点是它可以比随机梯度下降(默认MADlib优化器)表现更好,会更快更平滑的收敛。

表 4 支持的机器学习算法 - 图类

算法名称(中文)

算法名称(英文)

应用场景

所有对间最短路径

All Pairs Shortest Path (APSP)

所有对最短路径(APSP)算法找到所有顶点对之间的最短路径的长度(总和权重),使得路径边缘的权重之和最小化。

广度优先算法

Breadth-First Search

广度优先算法遍历路径。

超链接诱导主题搜索

Hyperlink-Induced Topic Search (HITS)

HITS算法输出每个节点的authority评分和hub评分,其中authority评分给出页面内容的分数,hub评估出连接到其他页面的分数。

平均路径长度

Average Path Length

此函数计算每对顶点之间的最短路径的平均值。平均路径长度基于“可到达的目标顶点”,因此它忽略了未连接的顶点之间的无限长度路径。

中心性

Closeness Centrality

接近度度量是和的倒数,平均值的倒数,以及到所有可到达目标顶点(不包括源顶点)的最短距离的倒数之和。

图表直径

Graph Diameter

直径被定义为图中所有最短路径中最长的。

入度出度

In-Out Degree

计算图中每个点的入度出度,入度指指向此点的边的数量,出度指此点指向其他点的边的数量。

网页排名

PageRank

给定图形,给定图形,PageRank算法输出概率分布,该概率分布表示随机遍历图形的人将到达任何特定顶点的可能性。

单源最短路径

Single Source Shortest Path (SSSP)

给定图形和源顶点,单源最短路径(SSSP)算法找到从源顶点到图中的每个其他顶点的路径,使得路径边缘的权重之和最小化(每条边权值非负)。

弱连通分量

Weakly Connected Component

给定有向图,弱连通分量(WCC)是原始图的子图,其中所有顶点通过某个路径彼此连接,忽略边的方向。在无向图的情况下,弱连通分量也是强连通分量。该模块还包括许多在WCC输出上运行的辅助函数。

表 5 支持的机器学习算法 - 时间序列

算法名称(中文)

算法名称(英文)

应用场景

差分整合移动平均自回归模型

Autoregressive Integrated Moving Average model(ARIMA)

时间序列预测,用于理解和预测一系列数据的未来值。

比如:国际航空旅客数据,预测旅客人数。

表 6 支持的机器学习算法 - 采样

算法名称(中文)

算法名称(英文)

应用场景

采样函数

sample

抽样。

分层抽样

Stratified Sampling

分层随机抽样,又称类型随机抽样,它是先将总体各单位按一定标准分成各种类型(或层);然后根据各类型单位数与总体单位数的比例,确定从各类型中抽取样本单位的数量;最后,按照随机原则从各类型中抽取样本。

对称抽样

Balanced Sampling

一些分类算法仅在每个类中的样本数大致相同时才最佳地执行。高度偏斜的数据集在许多领域中是常见的(例如,欺诈检测),因此重新采样以抵消这种不平衡可以产生更好的决策边界。

表 7 支持的机器学习算法 - 统计学

算法名称(中文)

算法名称(英文)

应用场景

汇总统计函数

Summary

生成任何数据表的摘要统计信息。

协方差和相关系数

Correlation and Covariance

描述性统计,求Pearson系数,相关系数,另一个输出协方差。了解数据从统计学上反映的量的特征,以便我们更好地认识这些将要被挖掘的数据。

统计频率算法

CountMin (Cormode-Muthukrishnan)

统计一个实时的数据流中元素出现的频率,并且准备随时回答某个元素出现的频率,不需要的精确的计数。

基数估计算法

FM (Flajolet-Martin)

获取指定列中的不同值的数量。 找出这个数字集合中不重复的数字的个数。

最频繁值

MFV (Most Frequent Values)

计算频繁值的场景。

假设检验

Hypothesis Tests

包含F-test,chi2-test等。

概率函数

Probability Functions

概率函数模块为各种概率分布提供累积分布,密度、质量和分位数函数。

表 8 支持的机器学习算法 - 其他算法

算法名称(中文)

算法名称(英文)

应用场景

k-聚类算法

K-means

聚类场景。

隐含狄利克雷分布

Latent Dirichlet Allocation (LDA)

LDA 在主题模型中占有非常重要的地位,常用来文本分类。

关联规则算法

Apriori Algorithm

关联规则算法,关联规则挖掘的目标是发现数据项集之间的关联关系。比如经典的“啤酒和尿布”。

 

二、环境部署

DeepSQL环境包括编译数据库和安装算法库两个部分。

前提条件

  • 环境中安装python2.7.12以上版本Python。
  • 数据库需要开启对PL/Python存储过程的支持。
  • 安装算法库需要拥有管理员权限的用户。

操作步骤

  1. 检查部署Python环境。

    安装前,请查看系统安装的python版本,当前DeepSQL需要python2.7.12以上版本的环境。

    • 如果当前系统python2版本高于2.7.12,可以直接安装python-devel包。
    • 如果版本过低,或者无法安装python-devel包,可以下载最新python2源码,手动配置编译python2,并配置环境变量。

    算法库中,部分算法调用了python包,如numpy,pandas等。用户可以安装以下python库:

    pip install numpy
    pip install pandas
    pip install scipy
    

     须知:  

    • 如果自行编译python,需要在configure脚本执行时加入–enable-shared参数。

    • 如果系统中的python2使用的是UCS4编码,自行编译python2时,还需要加入–enable-unicode=ucs4参数。

    • 可以在系统中自带的python2下执行:“import sys;print sys.maxunicode”并查看结果,如果结果是65535,说明系统默认的是ucs2;如果结果是1114111,说明用的ucs4编码。

    • 如果系统中内置的python2使用的ucs4,说明系统中的gdb,gstack等也会依赖ucs4。因此自行编译的python2在configure时,需要添加–enable-unicode=ucs4,否则后续使用gdb,gstack时,会遇到报错。

  2. 编译部署数据库。

    数据库需要开启对PL/Python存储过程的支持。默认编译数据库,不包含此模块。因此需要编译数据库时,在configure阶段,加入--with-python参数;

    其他编译保持步骤不变;

    编译完成后,需要重新gs_initdb;

    默认PL/Python存储过程模块不被加载,请执行“CREATE EXTENSION plpythonu”来加载模块。

  3. 算法库编译和安装。

    算法库使用开源的MADlib机器学习框架。源码包和相应patch可以从第三方库的代码仓库里获取。安装命令如下:

    tar -zxf apache-madlib-1.17.0-src.tar.gz
    cp madlib.patch apache-madlib-1.17.0-src           
    cd apache-madlib-1.17.0-src/
    patch -p1 

    编译命令如下:
    ./configure -DCMAKE_INSTALL_PREFIX={YOUR_MADLIB_INSTALL_FOLDER} -DPOSTGRESQL_EXECUTABLE=$GAUSSHOME/bin/ -DPOSTGRESQL_9_2_EXECUTABLE=$GAUSSHOME/bin/ -DPOSTGRESQL_9_2_CLIENT_INCLUDE_DIR=$GAUSSHOME/bin/ -DPOSTGRESQL_9_2_SERVER_INCLUDE_DIR=$GAUSSHOME/bin/ # 以上均为configure命令。 make make install

    其中, {YOUR_MADLIB_INSTALL_FOLDER}需要改为用户的实际安装路径。

    须知: 编译MADlib时,会联网下载依赖软件。无法联网时,需要手动下载依赖包“PyXB-1.2.6.tar.gz”,“eigen-branches-3.2.tar.gz”和“boost_1_61_0.tar.gz”放在本地。使用的configure命令如下: 

    复制代码./configure -DCMAKE_INSTALL_PREFIX={YOUR_MADLIB_INSTALL_FOLDER}                  # your install folder
    -DPYXB_TAR_SOURCE={YOUR_DEPENDENCY_FOLDER}/PyXB-1.2.6.tar.gz                     # change to your local folder
    -DEIGEN_TAR_SOURCE={YOUR_DEPENDENCY_FOLDER}/eigen-branches-3.2.tar.gz      # change to your local folder
    -DBOOST_TAR_SOURCE={YOUR_DEPENDENCY_FOLDER}/boost_1_61_0.tar.gz              # change to your local folder
    -DPOSTGRESQL_EXECUTABLE=$GAUSSHOME/bin/
    -DPOSTGRESQL_9_2_EXECUTABLE=$GAUSSHOME/bin/
    -DPOSTGRESQL_9_2_CLIENT_INCLUDE_DIR=$GAUSSHOME/bin/
    -DPOSTGRESQL_9_2_SERVER_INCLUDE_DIR=$GAUSSHOME/bin/
    
  4. 将算法库安装到数据库中。

a.进入{YOUR_MADLIB_INSTALL_FOLDER}路径。

b.进入bin文件夹。

c.执行如下命令。

   ./madpack -s  -p opengauss -c @127.0.0.1:/ install

命令中参数说明如下:

  • -s:schema的名称。
  • -p:数据库平台,使用opengauss即可。
  • -c:连接数据库的参数。包括用户名、‘@’、IP地址、端口号和目标数据库名称。

install为安装的命令,除此之外,还有reinstall(重新安装),uninstall(卸载)等命令可用。

 说明:
- 目标数据库必须存在。
- IP请使用127.0.0.1,不要使用localhost。
- 涉及到大量PL/Python存储过程的安装、卸载等操作,需要数据库管理员权限用户来进行,普通用户没有权限创建和修改PL/Python存储过程,只能调用。
- 数据库兼容性,推荐兼容性为B。不同的数据库兼容性下,对空值,NULL等处理有较大差异。建议使用B兼容性。例如,CREATE DATABASE dbcompatibility='B'。 

 

三、使用指导

PL/Python存储过程

当前PL/Python存储过程优先支持python2;默认版本也是python2。

PL/Python中的函数通过标准的CREATE FUNCTION声明:

CREATE FUNCTION funcname (argument-list)
RETURNS return-type
AS $$
# PL/Python function body
$$ LANGUAGE plpythonu;

函数体是一个简单的Python脚本,当函数被调用的时候,它的参数作为列表args的元素传递;命名参数也会被当做普通的变量传递到Python脚本中。命名参数的使用通常更易读。 结果将使用return或yield(结果集语句的情况) 照常从Python代码中返回。如果没有提供返回值,Python返回缺省的None。 PL/Python将Python中的None认为SQL空值。

例如,返回两个整数中较大者的函数定义如下。

CREATE FUNCTION pymax(a integer, b integer) RETURNS integer AS $$   
if a > b:     
    return a
return b 
$$ LANGUAGE plpythonu;

 注意: 

  • PL/Python函数中,后缀为plpythonu。‘u’说明是untrusted类型的存储过程。

  • Trusted:这个语言不能访问越权的数据。例如,数据库服务器的文件、数据库内部(包括直接访问共享内存)。

  • Untrusted:这个语言没有任何限制,允许访问任何数据(包括文件,网络,共享LIB库等,危害性较大),但是功能更加强大。

  • PL/Python属于untrusted类型的存储过程语言,当前仅允许管理员权限的用户创建和修改,普通用户仅支持使用。

  • 定义PL/Python存储过程时,注意不要定义执行诸如import os;os.system(“rm -rf /”) 等危险语句。管理员权限的用户需要小心创建此类PL/Python存储过程。

数据库Null, None和空串处理

如果向函数传递了一个SQL null值,参数值在Python中将会显示为None。在数据库中,不同的兼容性下,空串的行为会被当做NULL处理。

同一个函数,在不同的兼容性下表现不同。

CREATE FUNCTION quote(t text, how text) RETURNS text AS $$
if how == "literal":
    return plpy.quote_literal(t)    
elif how == "nullable":
    return plpy.quote_nullable(t)    
elif how == "ident":
    return plpy.quote_ident(t)
else:        
    raise plpy.Error("unrecognized quote type %s" % how)
$$ LANGUAGE plpythonu;

示例1:

SELECT quote(t, 'literal') FROM (VALUES ('abc'),('a''bc'),('''abc'''),(''),(''''),('xyzv')) AS v(t);

数据库不同兼容性下的结果为:

  • 兼容性为A时,返回结果如下:

    ERROR:  TypeError: argument 1 must be string, not None
    CONTEXT:  Traceback (most recent call last):
    PL/Python function "quote", line 3, in 
    return plpy.quote_literal(t)
    referenced column: quote
    
  • 兼容性为B时,返回结果如下:

    quote
    -----------
    'abc'
    'a''bc'
    '''abc'''
    ''
    ''''
    'xyzv'
    (6 rows)
    

示例2:

SELECT quote(t, 'nullable') FROM (VALUES ('abc'),('a''bc'),('''abc'''),(''),(''''),(NULL)) AS v(t);

数据库不同兼容性下的结果为:

  • 兼容性为A时,返回结果如下:

    quote
    -----------
    'abc'
    'a''bc'
    '''abc'''
    NULL
    ''''
    NULL
    (6 rows)
    
  • 兼容性为B时,返回结果如下:

    quote
    -----------
    'abc'
    'a''bc'
    '''abc'''
    ''
    ''''
    NULL
    (6 rows)
    

可以看到,在兼容性“A”中,空串被当为NULL了。

触发器

当前PL/Python存储过程中,不支持触发器功能。

匿名代码块

PL/Python也支持DO声明的匿名代码块:

DO $$
# PL/Python code
$$ LANGUAGE plpythonu;

一个匿名代码块不接受参数,并且丢弃它可能返回的值。

共享数据

每个函数都在Python解释器里获得自己的执行环境。

全局字典SD在函数调用之间用于存储数据。这些变量是私有静态数据。每一个函数都有自己的SD数据空间,函数A的全局数据和函数参数是函数B不可用的。

全局字典GD是公共数据,在一个gsql会话中,所有python函数都可访问和改变,使用时需要小心。

当gsql断开或退出,共享数据就被释放。

 注意: 

  • 运行DeepSQL或者PL/Python存储过程时,需要关闭线程池相关参数。否则PL/Python存储过程中的Sharing Data(“GD”、“SD”)等功能会失效。

  • 在数据库中,当线程池功能关闭,每一个连入的gsql,数据库内会起一个新的线程去处理。在gsql中,如果调用到PL/Python存储过程,会在本线程中完成python解析器模块的初始化,其中包括初始化“GD”,“SD”等共享空间。

  • 在线程池功能开启的状态下,一个gsql执行时,由当前空闲线程执行,每次执行可能分配到不同的线程上。导致共享数据紊乱。

数据库访问

PL/Python语言模块自动import一个叫plpy的Python模块。

plpy模块提供几个函数执行数据库命令:比如plpy.execute,plpy.prepare等。

plpy模块也提供了函数plpy.debug(msg)、 plpy.log(msg)、plpy.info(msg)、 plpy.notice(msg)、plpy.warning(msg)、 plpy.error(msg)和plpy.fatal(msg)。 plpy.error和 plpy.fatal实际上抛出了一个Python异常,会导致当前事务或者子事务退出。

另一个实用函数集是plpy.quote_literal(string)、 plpy.quote_nullable(string)和 plpy.quote_ident(string)。

关于审计

PL/Python存储过程支持审计功能。具体设置可以参考审计

关于并发执行

当前PL/Python存储过程对并发执行不友好,建议串行执行。

说明: 由于openGauss是多线程架构,C-python中,由于GIL锁(Global Interpreter Lock)的限制,多线程在Python中只能交替执行,无法做到真正的并发。 

库内算法

具体库内算法介绍和使用,可参考MADlib官方网站(MADlib文档)。

 须知:
- 当前仅支持机器学习算法,不支持深度学习(deep learning)模块。
- 当前数据库不支持xml,所以pmml模块和相关功能不支持。
- 数据库不支持jsonb模块,json格式的模型导出功能也不支持。

其他算法支持

除了MADlib提供的算法外,openGauss又额外提供了以下三个算法。

表 1 额外增加的模块列表

算法名称(中文)

算法名称(英文)

梯度提升树

gbdt

梯度提升

xgboost

时间序列预测的算法

facebook_prophet

使用时,需要安装依赖的python库:

  • 如果使用prophet算法:

    pip install pystan
    pip install holidays==0.9.8
    pip install fbprophet==0.3.post2
    
  • 如果使用xgboost算法:

    pip install xgboost
    pip install scikit-learn
    
  • gbdt不需要额外安装其他库。

详细操作请参考最佳实践。

四、最佳实践

本章节介绍部分算法的使用,主要包含分类、回归、聚类、gbdt算法、xgboost算法和prohpet算法。

首先需要创建一个数据库,并安装算法。

create database test1 dbcompatibility='B';
./madpack -s madlib -p opengauss -c opg@127.0.0.1:7651/test1 install

分类算法

以svm分类房价为例子:

  1. 数据集准备。

    DROP TABLE IF EXISTS houses;
    CREATE TABLE houses (id INT, tax INT, bedroom INT, bath FLOAT, price INT,  size INT, lot INT);
    INSERT INTO houses VALUES
    (1 ,  590 ,       2 ,    1 ,  50000 ,  770 , 22100),
    (2 , 1050 ,       3 ,    2 ,  85000 , 1410 , 12000),
    (3 ,   20 ,       3 ,    1 ,  22500 , 1060 ,  3500),
    (4 ,  870 ,       2 ,    2 ,  90000 , 1300 , 17500),
    (5 , 1320 ,       3 ,    2 , 133000 , 1500 , 30000),
    (6 , 1350 ,       2 ,    1 ,  90500 ,  820 , 25700),
    (7 , 2790 ,       3 ,  2.5 , 260000 , 2130 , 25000),
    (8 ,  680 ,       2 ,    1 , 142500 , 1170 , 22000),
    (9 , 1840 ,       3 ,    2 , 160000 , 1500 , 19000),
    (10 , 3680 ,       4 ,    2 , 240000 , 2790 , 20000),
    (11 , 1660 ,       3 ,    1 ,  87000 , 1030 , 17500),
    (12 , 1620 ,       3 ,    2 , 118600 , 1250 , 20000),
    (13 , 3100 ,       3 ,    2 , 140000 , 1760 , 38000),
    (14 , 2070 ,       2 ,    3 , 148000 , 1550 , 14000),
    (15 ,  650 ,       3 ,  1.5 ,  65000 , 1450 , 12000);
    
  2. 模型训练。

    训练前配置相应schema和兼容性参数:

    SET search_path="$user",public,madlib;
    SET behavior_compat_options = 'bind_procedure_searchpath';
    

    使用默认的参数进行训练,分类的条件为‘price

    DROP TABLE IF EXISTS houses_svm, houses_svm_summary; 
    SELECT madlib.svm_classification('public.houses','public.houses_svm','price 
  3. 查看模型。

    x on
    SELECT * FROM houses_svm;
    x off
    

    结果如下:

    -[ RECORD 1 ]------+-----------------------------------------------------------------
    coef               | {.113989576847,-.00226133300602,-.0676303607996,.00179440841072}
    loss               | .614496714256667
    norm_of_gradient   | 108.171180769224
    num_iterations     | 100
    num_rows_processed | 15
    num_rows_skipped   | 0
    dep_var_mapping    | {f,t}
    
  4. 进行预测。

    DROP TABLE IF EXISTS houses_pred; 
    SELECT madlib.svm_predict('public.houses_svm','public.houses','id','public.houses_pred');
    
    • 查看预测结果

      SELECT *, price 

      结果如下:

       id | tax  | bedroom | bath | price  | size |  lot  | prediction | decision_function | actual
      ----+------+---------+------+--------+------+-------+------------+-------------------+--------
        1 |  590 |       2 |    1 |  50000 |  770 | 22100 | t          |      .09386721875 | t
        2 | 1050 |       3 |    2 |  85000 | 1410 | 12000 | t          |     .134445058042 | t
        3 |   20 |       3 |    1 |  22500 | 1060 |  3500 | t          |   1.9032054712902 | t
        4 |  870 |       2 |    2 |  90000 | 1300 | 17500 | t          |    .3441000739464 | t
        5 | 1320 |       3 |    2 | 133000 | 1500 | 30000 | f          |   -.3146180966186 | f
        6 | 1350 |       2 |    1 |  90500 |  820 | 25700 | f          |  -1.5350254452892 | t
        7 | 2790 |       3 |  2.5 | 260000 | 2130 | 25000 | f          |  -2.5421154971142 | f
        8 |  680 |       2 |    1 | 142500 | 1170 | 22000 | t          |    .6081106124962 | f
        9 | 1840 |       3 |    2 | 160000 | 1500 | 19000 | f          |   -1.490511259749 | f
       10 | 3680 |       4 |    2 | 240000 | 2790 | 20000 | f          |   -3.336577140997 | f
       11 | 1660 |       3 |    1 |  87000 | 1030 | 17500 | f          |  -1.8592129109042 | t
       12 | 1620 |       3 |    2 | 118600 | 1250 | 20000 | f          |  -1.4416201011046 | f
       13 | 3100 |       3 |    2 | 140000 | 1760 | 38000 | f          |   -3.873244660547 | f
       14 | 2070 |       2 |    3 | 148000 | 1550 | 14000 | f          |  -1.9885277913972 | f
       15 |  650 |       3 |  1.5 |  65000 | 1450 | 12000 | t          |   1.1445697772786 | t
      (15 rows)
      
    • 查看误分率

      SELECT COUNT(*) FROM houses_pred JOIN houses USING (id) WHERE houses_pred.prediction != (houses.price 

      结果如下:

      count
      -------
           3
      (1 row)
      
  5. 使用svm其他核进行训练。

    DROP TABLE IF EXISTS houses_svm_gaussian, houses_svm_gaussian_summary, houses_svm_gaussian_random; 
    SELECT madlib.svm_classification( 'public.houses','public.houses_svm_gaussian','price 

    进行预测,并查看训练结果。

    DROP TABLE IF EXISTS houses_pred_gaussian; 
    SELECT madlib.svm_predict('public.houses_svm_gaussian','public.houses','id', 'public.houses_pred_gaussian');
    SELECT COUNT(*) FROM houses_pred_gaussian JOIN houses USING (id) WHERE houses_pred_gaussian.prediction != (houses.price 

    结果如下。

     count 
    -------+    
    0 
    (1 row)
    
  6. 其他参数

    除了指定不同的核方法外,还可以指定迭代次数,初始参数,比如init_stepsize, max_iter, class_weight等。

 须知: 

复制代码
SET search_path="$user",public,madlib;
SET behavior_compat_options = 'bind_procedure_searchpath';

执行算法前,需要设置search_path中schema,另外需要bind_procedure_searchpath,否则出现表找不到的情况。因为机器学习所有的方法安装在一个schema中,用户的表安装在用户的schema中,在这个例子中,算法安装在madlib中,用户表放置在public中。如果不设置,可能会出现执行算法时,找不到表的情况。执行算法时,建议把输入表的schema也加进去。

回归算法

我们以线性回归预测波士顿房价为例:

  1. 数据集准备。

    同svm的数据集,请参见1

  2. 训练模型。

    SET search_path="$user",public,madlib;
    SET behavior_compat_options = 'bind_procedure_searchpath';
        
    DROP TABLE IF EXISTS houses_linregr, houses_linregr_summary;
    SELECT madlib.linregr_train( 'public.houses', 'public.houses_linregr',  'price', 'ARRAY[1, tax, bath, size]');
    
  3. 查看模型内容。

    x ON
    SELECT * FROM houses_linregr;
    x OFF
    

    返回结果如下。

    -[ RECORD 1 ]------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    coef                     | {-12849.4168959872,28.9613922651775,10181.6290712649,50.516894915353}
    r2                       | .768577580597462
    std_err                  | {33453.0344331377,15.8992104963991,19437.7710925915,32.9280231740856}
    t_stats                  | {-.384103179688204,1.82156166004197,.523806408809163,1.53416118083608}
    p_values                 | {.708223134615411,.0958005827189556,.610804093526516,.153235085548177}
    condition_no             | 9002.50457069858
    num_rows_processed       | 15
    num_missing_rows_skipped | 0
    variance_covariance      | {
        {1119105512.7847,217782.067878005,-283344228.394538,-616679.693190829},{217782.067878005,252.784894408806,-46373.1796964038,-369.864520095145},{-283344228.394538,-46373.1796964038,377826945.047986,-209088.217319699},{-616679.693190829,-369.864520095145,-209088.217319699,1084.25471015312}}
    
  4. 预测,并对比结果。

    SELECT houses.*,
        madlib.linregr_predict( m.coef, ARRAY[1,tax,bath,size]) as predict,
        price - madlib.linregr_predict( m.coef, ARRAY[1,tax,bath,size]) as residual
    FROM public.houses, public.houses_linregr AS m 
    ORDER BY id;
    

    返回结果如下。

     id | tax  | bedroom | bath | price  | size |  lot  |     predict      |     residual
    ----+------+---------+------+--------+------+-------+------------------+-------------------
      1 |  590 |       2 |    1 |  50000 |  770 | 22100 | 53317.4426965543 | -3317.44269655428
      2 | 1050 |       3 |    2 |  85000 | 1410 | 12000 | 109152.124955627 | -24152.1249556268
      3 |   20 |       3 |    1 |  22500 | 1060 |  3500 | 51459.3486308555 | -28959.3486308555
      4 |  870 |       2 |    2 |  90000 | 1300 | 17500 |  98382.215907206 | -8382.21590720599
      5 | 1320 |       3 |    2 | 133000 | 1500 | 30000 | 121518.221409606 |  11481.7785903935
      6 | 1350 |       2 |    1 |  90500 |  820 | 25700 | 77853.9455638568 |  12646.0544361432
      7 | 2790 |       3 |  2.5 | 260000 | 2130 | 25000 | 201007.926371722 |  58992.0736282778
      8 |  680 |       2 |    1 | 142500 | 1170 | 22000 | 76130.7259665615 |  66369.2740334385
      9 | 1840 |       3 |    2 | 160000 | 1500 | 19000 | 136578.145387499 |  23421.8546125013
     10 | 3680 |       4 |    2 | 240000 | 2790 | 20000 | 255033.901596231 | -15033.9015962306
     11 | 1660 |       3 |    1 |  87000 | 1030 | 17500 | 97440.5250982859 | -10440.5250982859
     12 | 1620 |       3 |    2 | 118600 | 1250 | 20000 | 117577.415360321 |  1022.58463967856
     13 | 3100 |       3 |    2 | 140000 | 1760 | 38000 | 186203.892319614 | -46203.8923196141
     14 | 2070 |       2 |    3 | 148000 | 1550 | 14000 | 155946.739425522 | -7946.73942552213
     15 |  650 |       3 |  1.5 |  65000 | 1450 | 12000 | 94497.4293105374 | -29497.4293105374
    (15 rows)
    

聚类算法

以kmeans为例:

  1. 准备数据。

    DROP TABLE IF EXISTS km_sample; 
    CREATE TABLE km_sample(pid int, points double precision[]); 
    INSERT INTO km_sample VALUES 
    (1,  '{14.23, 1.71, 2.43, 15.6, 127, 2.8, 3.0600, 0.2800, 2.29, 5.64, 1.04, 3.92, 1065}'), 
    (2,  '{13.2, 1.78, 2.14, 11.2, 1, 2.65, 2.76, 0.26, 1.28, 4.38, 1.05, 3.49, 1050}'), 
    (3,  '{13.16, 2.36,  2.67, 18.6, 101, 2.8,  3.24, 0.3, 2.81, 5.6799, 1.03, 3.17, 1185}'), 
    (4,  '{14.37, 1.95, 2.5, 16.8, 113, 3.85, 3.49, 0.24, 2.18, 7.8, 0.86, 3.45, 1480}'), 
    (5,  '{13.24, 2.59, 2.87, 21, 118, 2.8, 2.69, 0.39, 1.82, 4.32, 1.04, 2.93, 735}'), 
    (6,  '{14.2, 1.76, 2.45, 15.2, 112, 3.27, 3.39, 0.34, 1.97, 6.75, 1.05, 2.85, 1450}'), 
    (7,  '{14.39, 1.87, 2.45, 14.6, 96, 2.5, 2.52, 0.3, 1.98, 5.25, 1.02, 3.58, 1290}'), 
    (8,  '{14.06, 2.15, 2.61, 17.6, 121, 2.6, 2.51, 0.31, 1.25, 5.05, 1.06, 3.58, 1295}'), 
    (9,  '{14.83, 1.64, 2.17, 14, 97, 2.8, 2.98, 0.29, 1.98, 5.2, 1.08, 2.85, 1045}'), 
    (10, '{13.86, 1.35, 2.27, 16, 98, 2.98, 3.15, 0.22, 1.8500, 7.2199, 1.01, 3.55, 1045}');
    
  2. 运行kmeans算法。

    使用kmeans++进行计算,距离函数使用欧几里得距离。

    SET search_path="$user",public,madlib;
    SET behavior_compat_options = 'bind_procedure_searchpath';
        
    DROP TABLE IF EXISTS km_result; 
    CREATE TABLE km_result AS SELECT * FROM madlib.kmeanspp( 'public.km_sample',   -- Table of source data                              
                                                             'points',      -- Column containing point co-ordinates                              
                                                             2,             -- Number of centroids to calculate                              
                                                             'madlib.squared_dist_norm2',   -- Distance function                              
                                                             'madlib.avg',  -- Aggregate function                              
                                                             20,            -- Number of iterations                             
                                                             0.001          -- Fraction of centroids reassigned to keep iterating
    );
    

    kmeans执行完后,不会自动创建表保存内容,所以需要用户自行创建table。

    x on
     select * from km_result;
    x off
    

    返回结果如下。

    -[ RECORD 1 ]----+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    centroids        | {
        {14.0333333333333,1.84111111111111,2.41,15.5111111111111,96.2222222222222,2.91666666666667,3.01111111111111,.282222222222222,1.95444444444444,5.88553333333333,1.02222222222222,3.38222222222222,1211.66666666667},{13.24,2.59,2.87,21,118,2.8,2.69,.39,1.82,4.32,1.04,2.93,735}}
    cluster_variance | {257041.999707571,0}
    objective_fn     | 257041.999707571
    frac_reassigned  | 0
    num_iterations   | 2
    
  3. 应用聚类结果。

    执行以下函数,计算每个节点的临近节点和相应距离。

    DROP TABLE IF EXISTS km_points_silh; 
    SELECT * FROM madlib.simple_silhouette_points('public.km_sample',          -- Input points table
                                                  'public.km_points_silh',      -- Output table
                                                  'pid',                 -- Point ID column in input table
                                                  'points',              -- Points column in input table
                                                  'public.km_result',           -- Centroids table
                                                  'centroids',           -- Column in centroids table containing centroids
                                          
登录查看全部

参与评论

评论留言

还没有评论留言,赶紧来抢楼吧~~

手机查看

返回顶部

给这篇文章打个标签吧~

棒极了 糟糕透顶 好文章 PHP JAVA JS 小程序 Python SEO MySql 确认