内置机器学习库（madlib）
内置机器学习库（madlib)¶
MADlib简介¶
概述¶
MADlib是面向DBA（DatabaseAdministrator，数据库管理员）的SQL中的大数据机器学习库。 是 Pivotal 公司与伯克利大学合作开发的一个开源机器学习库，提供了多种数据转换、数据探索、 统计、数据挖掘和机器学习方法，使用它能够简易的对结构化数据进行分析和挖掘。用户可以方便的 将MADlib加载到数据库中，扩展数据库的分析功能。 2015 年 7 月MADlib成为Apache软件基金 会的孵化器项目，经过两年的发展，于 2017 年 8 月毕业成为 Apache顶级项目。可以与 PostgreSQL等数据库系统无缝集成， 我们积极跟踪madlib的开源项目， 并集成到我们的分布式数据库产品seaboxmpp中。为客户提供基于SQL的高性能机器学习工具。
术语¶
数据挖掘（Data mining）：是指从大量的、不完全的、模糊的、有噪声的、随机的实际应用数据中，自动提取隐含的、人们事先不知道的、又潜在有用的信息.
OLAP（On-Line Analytical Processing，联机分析处理）：基于数据仓库的信息分析处理过程， 是数据仓库的用户接口部分。
安装MADlib¶
在数据库的 master节点使用数据库管理员用户登录，例如
su - seabox
安装MADlib到数据库¶
${SDHOME}/madlib/bin/madpack install -s madlib -p seaboxsql -c "seabox@localhost:${SDPORT}/testdb"
-
s：schema名称，安装后的madlib函数在这个 schema下。
-
p：平台名称,这里指定seaboxsql
-
c：master节点信息：用户@节点名：端口号/数据库名。
SDHOME: seaboxmpp安装位置的环境变量
SDPORT: seaboxmpp的监听端口
验证¶
${SDHOME}/madlib/bin/madpack install-check -s madlib -p seaboxsql -c "seabox@localhost:${SDPORT}/seaboxsql"
- s：schema名称，安装后的madlib函数在这个 schema下。
- p：平台名称,这里指定seaboxsql
- c：master节点信息：用户@节点名：端口号/数据库名。
卸载¶
${SDHOME}/madlib/bin/madpack uninstall -s madlib -p seaboxsql -c "seabox@localhost:${SDPORT}/seaboxsql"
使用指南¶
MADlib安装完成之后，就可以在所安装的数据库中对应的 schema下使用所有的 madlib函数。 MADlib库涵盖数据挖掘的各个方面，下面会分类进行举例说明函数的用法。
数据类型¶
向量数组¶
MADlib 库提供了丰富的向量运算函数，涵盖了向量的所有基本运算。
1. 向量运算符合
| 函数 | 描述 |
|---|---|
| array_add() | 两个数组相加，需要所有值非空，返回与输入相同的数据类型。 |
| sum() | 数组元素求和，需要所有值非空，返回与输入相同的数据类型。 |
| array_sub() | 两个数组相减，需要所有值非空，返回与输入相同的数据类型。 |
| array_mult() | 两个数组相乘，需要所有值非空，返回与输入相同的数据类型。 |
| array_div() | 两个数组相除，需要所有值非空，返回与输入相同的数据类型。 |
| array_dot() | 两个数组点积，需要所有值非空，返回与输入相同的数据类型。 |
| array_contains() | 检查一个数组是否包含另一个数组。如果右边数组中的每个非零元素都等于左边数组中相同下标的元素，函数返回TRUE。 |
| array_max() | 返回数组中的最大值，忽略空值，返回数组元素的相同类型。 |
| array_max_index() | 返素回类数型组与中输的入最类大型值相及同其。对应的下标，忽略空值，返回类型的格式为[max,index] |
| array_min() | 返回数组中的最小值，忽略空值，返回数组元素的相同类型。 |
| array_min_index() | 返回数组中的最小值及其对应的下标，忽略空值，返回类型的格式为[min,index]，其元素类型与输入类型相同。 |
| array_sum() | 返回数组中值的和，忽略空值，返回与输入相同的数据类型。 |
| array_sum_big() | 返素回类数型组范中围值时的，和替，换忽arr略ay空_s值um，(返)。回FLOAT^8 类型。该函数的意思是当汇总值可能超出元 |
| array_abs_sum() | 返回数组中绝对值的和，忽略空值，返回与输入相同的数据类型。 |
| array_abs() | 返回由数组元素的绝对值组成的新数组，需要所有值非空。 |
| array_mean() | 返回数组的均值，忽略空值。 |
| array_stddev() | 返回数组的标准差，忽略空值。 |
| array_of_float() | 创建元素个数为参数值的FLOAT8数组，初始值为0.0。 |
| array_of_bigint() | 创建元素个数为参数值的BIGINT数组，初始值为0。 |
| array_fill() | 将数组每个元素设置为参数值。 |
| array_filter() | 过滤掉数组中的指定元素，要求所有值非空。返回与输入相同的数据类型。不指定被过滤元素时，该函数移除数组中的所有0值。 |
| array_scalar_mult() | 数组与标量相乘，返回结果数组。需要所有值非空，返回与输入相同的数据类型。 |
| array_scalar_add() | 数组与标量相加，返回结果数组。需要所有值非空，返回与输入相同的数据类型。 |
| array_sqrt() | 返回由数组元素的平方根组成的数组，需要所有值非空。 |
| array_pow() | 以数组和一个float8为输入，返回每个元素的乘幂（由第二个参数指定）组成的数组, 需要所有值非空。 |
| array_square() | 返回由数组元素的平方组成的数组，需要所有值非空。 |
| normalize() | 该函数规范化一个数组，使它的元素平方和为1。要求所有值非空。 |
2. 示例
- 创建向量表，并插入数据
drop table array_tbl;
create table array_tbl(idinteger,array 1 integer[],array 2 integer[]);
insert into array_tbl values
( 1 ,'{ 1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 }','{ 9 , 8 , 7 , 6 , 5 , 4 , 3 , 2 , 1 }'),
( 2 ,'{ 1 , 3 , 5 , 7 , 9 , 11 , 13 , 15 }','{ 2 , 4 , 6 , 8 , 10 , 12 , 14 , 16 }');
select* from array_tbl;
seaboxsql=# select * from array_tbl;
id | array1 | array2
----+----------------------+-----------------------
1 | {1,2,3,4,5,6,7,8,9} | {9,8,7,6,5,4,3,2,1}
2 | {1,3,5,7,9,11,13,15} | {2,4,6,8,10,12,14,16}
(2 rows)
- 向量加减(arrray_sub,array_add)
seaboxsql=# select id, madlib.array_add(array1,array2),madlib.array_sub(array1,array2) from array_tbl;
id | array_add | array_sub
----+------------------------------+---------------------------
1 | {10,10,10,10,10,10,10,10,10} | {-8,-6,-4,-2,0,2,4,6,8}
2 | {3,7,11,15,19,23,27,31} | {-1,-1,-1,-1,-1,-1,-1,-1}
- *向量乘除
array_mult,array_div)
seaboxsql=# select id, madlib.array_mult(array1,array2),madlib.array_div(array1,array2) from array_tbl;
id | array_mult | array_div
----+-----------------------------+---------------------
2 | {2,12,30,56,90,132,182,240} | {0,0,0,0,0,0,0,0}
1 | {9,16,21,24,25,24,21,16,9} | {0,0,0,0,1,1,2,4,9}
(2 rows)
- 数组与标量相乘
(array_scalar_mult(array1, scalar k))
seaboxsql=# select
id,
madlib.array_scalar_mult(array1, 3),
madlib.array_scalar_mult(array1, -3)
from
array_tbl;
id | array_scalar_mult | array_scalar_mult
----+---------------------------+------------------------------------
2 | {3,9,15,21,27,33,39,45} | {-3,-9,-15,-21,-27,-33,-39,-45}
1 | {3,6,9,12,15,18,21,24,27} | {-3,-6,-9,-12,-15,-18,-21,-24,-27}
(2 rows)
- 数组求点积(array_dot(array1, array2))
seaboxsql=# select id, madlib.array_dot(array1,array2) from array_tbl;
id | array_dot
----+-----------
1 | 165
2 | 744
(2 rows)
-
其他
-
array_min(array1) array_max(array1) 返回 array 中的最大值/最小值
-
array_max_index(array1)/array_min_index(array1) 返回 array 中的最大值/最小值以及位置
-
array_mean(array1)/array_stddev(array1) 返回 array 中的平均值/方差
seaboxsql=# select
id,
madlib.array_min(array1),
madlib.array_max(array1),
madlib.array_min_index(array1),
madlib.array_max_index(array1),
madlib.array_mean(array1),
madlib.array_stddev(array1)
from
array_tbl;
id | array_min | array_max | array_min_index | array_max_index | array_mean | array_stddev
----+-----------+-----------+-----------------+-----------------+------------+--------------------
2 | 1 | 15 | {1,1} | {15,8} | 8 | 4.898979485566356
1 | 1 | 9 | {1,1} | {9,9} | 5 | 2.7386127875258306
(2 rows)
矩阵¶
矩阵（matrix）是把集合汇聚成行和列的一种表示。术语“m×n 矩阵”，通常用来说明矩阵具有 m 行和 n 列。
MADlib 支持稠密和稀疏两种矩阵表示形式，所有的矩阵运算都以一种表示形式工作。
-
稠密矩阵：矩阵被表示成一堆数组的行集合。
-
稀疏矩阵：使用行列下标指示矩阵中的每一个非零项。
1. 矩阵的函数分类
- 表示函数
转为稀疏矩阵
matrix_sparsify(matrix_in, in_args, matrix_out, out_args)
转为稠密矩阵
matrix_densify(matrix_in, in_args, matrix_out, out_args)
获取矩阵的维度
matrix_ndims(matrix_in, in_args)
- 计算函数
矩阵转置 matrix_trans(matrix_in, in_args, matrix_out, out_args)
矩阵相加 matrix_add(matrix_a, a_args, matrix_b, b_args,matrix_out, out_args)
矩阵相减 matrix_sub(matrix_a, a_args, matrix_b, b_args, matrix_out,out_args)
矩阵相乘
matrix_mult(matrix_a, a_args, matrix_b, b_args, matrix_out,out_args)
矩阵元素相乘
matrix_elem_mult(matrix_a, a_args, matrix_b, b_args, matrix_out,out_args)
标量乘矩阵 matrix_scalar_mult(matrix_in, in_args, scalar, matrix_out, out_args)
向量乘矩阵 matrix_vec_mult(matrix_in, in_args, vector)
- 提取函数
从行下标提取行 matrix_extract_row(matrix_in, in_args, index)
从列下标提取列 matrix_extract_col(matrix_in, in_args, index)
提取主对角线元素 matrix_extract_diag(matrix_in, in_args)
归约函数 获取指定维度的最大值 matrix_max(matrix_in, in_args, dim, fetch_index)
获取指定维度的最小值 matrix_min(matrix_in, in_args, dim, fetch_index)
获取指定维度的和 matrix_sum(matrix_in, in_args, dim)
获取指定维度的均值 matrix_mean(matrix_in, in_args, dim)
获取矩阵范数 matrix_norm(matrix_in, in_args, norm_types)
- 创建函数
创建指定行列维度的矩阵，用 1 初始化元素值 matrix_ones(row_dim, col_dim, matrix_out, out_args)
创建指定行列维度的矩阵，用 0 初始化元素值 matrix_zeros(row_dim, col_dim, matrix_out, out_args)
创建单位矩阵 matrix_identity(dim, matrix_out, out_args)
用给定对角元素初始化矩阵 matrix_diag(diag_elements, matrix_out, out_args)
- 转换函数
矩阵求逆 matrix_inverse(matrix_in, in_args, matrix_out, out_args)
广义逆矩阵 matrix_pinv(matrix_in, in_args, matrix_out, out_args)
矩阵特征提取 matrix_eigen(matrix_in, in_args, matrix_out, out_args)
cholesky 分解 matrix_closesky(matrix_in, in_args, matrix_out_prefix, out_args)
QR 分解 matrix_qr(matrix_in, in_args, matrix_out)
LU 分解 matrix_lu(matrix_in, in_args, matrix_out_prefix, out_args)
求矩阵的核范数 matrix_nuclear_norm(matrix_in, in_args)
求矩阵的秩 matrix_rank(matrix_in, in_args)
2. 示例
- 创建两个矩阵
DROP TABLE if EXISTS mat_a;
CREATE TABLE mat_a(row_id integer, row_vec integer[]);
INSERT INTO mat_a VALUES
(1, '{9,6,5,8}'), (2, '{8,2,2,6}'), (3, '{3,9,9,9}'), (4, '{6,4,2,2}');
SELECT * FROM mat_a;
seaboxsql=# SELECT * FROM mat_a;
row_id | row_vec
--------+-----------
2 | {8,2,2,6}
3 | {3,9,9,9}
4 | {6,4,2,2}
1 | {9,6,5,8}
(4 rows)
DROP TABLE if EXISTS mat_b;
CREATE TABLE mat_b(row_id integer, row_vec integer[]);
INSERT INTO mat_b VALUES
(1, '{4,5,2,7}'), (2, '{9,6,3,1}'), (3, '{6,4,2,1}'), (4, '{7,2,1,6}');
SELECT * FROM mat_b;
seaboxsql=# SELECT * FROM mat_b;
row_id | row_vec
--------+-----------
1 | {4,5,2,7}
2 | {9,6,3,1}
3 | {6,4,2,1}
4 | {7,2,1,6}
(4 rows)
- 由稠密矩阵表生成稀疏表示的表
seaboxsql=# SELECT
madlib.matrix_sparsify(
'mat_a',
'row=row_id, val=row_vec',
'mat_a_sparse',
'col=col_id, val=val'
);
matrix_sparsify
-----------------
(mat_a_sparse)
(1 row)
seaboxsql=# SELECT * from mat_a_sparse;
row_id | col_id | val
--------+--------+-----
2 | 1 | 8
2 | 2 | 2
2 | 3 | 2
2 | 4 | 6
3 | 1 | 3
3 | 2 | 9
3 | 3 | 9
3 | 4 | 9
4 | 1 | 6
4 | 2 | 4
4 | 3 | 2
4 | 4 | 2
1 | 1 | 9
1 | 2 | 6
1 | 3 | 5
1 | 4 | 8
(16 rows)
seaboxsql=# SELECT
madlib.matrix_sparsify(
'mat_b',
'row=row_id, val=row_vec',
'mat_b_sparse',
'col=col_id, val=val'
);
matrix_sparsify
-----------------
(mat_b_sparse)
(1 row)
seaboxsql=# SELECT * FROM mat_b_sparse;
row_id | col_id | val
--------+--------+-----
2 | 1 | 9
2 | 2 | 6
2 | 3 | 3
2 | 4 | 1
3 | 1 | 6
3 | 2 | 4
3 | 3 | 2
3 | 4 | 1
4 | 1 | 7
4 | 2 | 2
4 | 3 | 1
4 | 4 | 6
1 | 1 | 4
1 | 2 | 5
1 | 3 | 2
1 | 4 | 7
(16 rows)
- 矩阵转置
drop table if exists mat_a_r;
seaboxsql=# select madlib.matrix_trans('mat_a','row=row_id,val=row_vec','mat_a_r');
matrix_trans
--------------
(mat_a_r)
(1 row)
seaboxsql=# select * from mat_a_r order by row_id;
row_id | row_vec
--------+-----------
1 | {9,8,3,6}
2 | {6,2,9,4}
3 | {5,2,9,2}
4 | {8,6,9,2}
(4 rows)
- 矩阵相加
seaboxsql=# select
madlib.matrix_add(
'mat_a',
'row=row_id,val=row_vec',
'mat_b',
'row=row_id,val=row_vec',
'mat_add',
'val=row_vec,fmt=dense'
);
matrix_add
------------
(mat_add)
(1 row)
seaboxsql=# select * from mat_add order by row_id;
row_id | row_vec
--------+--------------
1 | {13,11,7,15}
2 | {17,8,5,7}
3 | {9,13,11,10}
4 | {13,6,3,8}
(4 rows)
seaboxsql=#
- 提取主对角线元素
seaboxsql=#SELECT
madlib.matrix_extract_diag('mat_a', 'row=row_id, val=row_vec'),
madlib.matrix_extract_diag('mat_b', 'row=row_id, val=row_vec');
matrix_extract_diag | matrix_extract_diag
---------------------+---------------------
{9,2,9,2} | {4,6,2,6}
(1 row)
seaboxsql=#
- 获取指定维度的和
seaboxsql=#SELECT
madlib.matrix_sum('mat_a', 'row=row_id, val=row_vec', 1),
madlib.matrix_sum('mat_a', 'row=row_id, val=row_vec', 2);
matrix_sum | matrix_sum
---------------+---------------
{26,21,18,25} | {28,18,30,14}
(1 row)
matrix_sum 函数按照指定维度求和，第三个参数的值只能是 1 或者 2，1 代表按照行求和，2 代表按照列求和。
- 创建单位矩阵
seaboxsql=# SELECT madlib.matrix_identity(4, 'mat_identity');
SELECT * from mat_identity;
matrix_identity
-----------------
(mat_identity)
(1 row)
row | col | val
-----+-----+-----
2 | 2 | 1
3 | 3 | 1
4 | 4 | 1
1 | 1 | 1
(4 rows)
matrix_identity 函数创建一个稀疏表示的单位矩阵，主对角线上的元素都为 1，其余元素都为0 的方阵成为单位矩阵。
- 求逆矩阵
seaboxsql=#SELECT
madlib.matrix_inverse('mat_a', 'row=row_id, val=row_vec', 'mat_inverse');
SELECT * from mat_inverse;
seaboxsql=# SELECT * from mat_inverse;
-[ RECORD 1 ]----------------------------------------------------------------------------
row_id | 1
row_vec | {-1.2000000000000008,0.9000000000000009,0.3333333333333336,0.6000000000000004}
-[ RECORD 2 ]----------------------------------------------------------------------------
row_id | 2
row_vec | {3.200000000000003,-2.4000000000000017,-1.0000000000000009,-1.1000000000000014}
-[ RECORD 3 ]----------------------------------------------------------------------------
row_id | 3
row_vec | {-5.0000000000000036,3.5000000000000018,1.6666666666666676,2.0000000000000018}
-[ RECORD 4 ]----------------------------------------------------------------------------
row_id | 4
row_vec | {2.200000000000001,-1.4000000000000006,-0.6666666666666669,-1.1000000000000005}
数据变换¶
临近度¶
相似度（similarity）：是指这两个对象相似程度的数值度量，两个对象越相似，他们的相似度就越高，取值通常是非负的，范围在 0-1 之间。 相异度（dissmilarity）：是指两个对象差异程度的数值度量，对象越相似，他们的相异度就越低，取值通常在 0-1 之间，但有时也在 0 和无穷大之间取值。
1. 临近度相关函数
2. 示例
- 范数
seaboxsql=# SELECT madlib.norm1('{1,-2,3}'),madlib.norm2('{1,-2,3}');
norm1 | norm2
-------+--------------------
6 | 3.7416573867739413
(1 row)
norm1 定义为向量各元素绝对值的和，norm2 定义为向量各元素平方和的平方根。
- 欧几里得距离（L2 范数）
seaboxsql=# SELECT madlib.dist_norm2('{1,-2,3}', '{4,-5,6}');
dist_norm2
-------------------
5.196152422706632
(1 row)
seaboxsql=# SELECT madlib.dist_pnorm('{1,-2,3}', '{4,-5,6}',5);
dist_pnorm
-------------------
3.737192818846552
(1 row)
- 曼哈顿函数
seaboxsql=# SELECT madlib.dist_norm1('{1,-2,3}', '{4,-5,6}');
dist_norm1
------------
9
(1 row)
seaboxsql=#
- 上确界距离函数
seaboxsql=# SELECT madlib.dist_inf_norm('{1,-2,3}', '{4,-5,6}');
dist_inf_norm
---------------
3
(1 row)
- 平方欧几里得函数
seaboxsql=# SELECT madlib.squared_dist_norm2('{1,-2,3}', '{4,-5,6}');
squared_dist_norm2
--------------------
27
(1 row)
- Jaccard 距离
seaboxsql=# SELECT madlib.dist_jaccard('{1,-2,3}', '{4,-5,6}');
dist_jaccard
--------------
1
(1 row)
- tanimoto 函数
seaboxsql=# SELECT madlib.dist_tanimoto('{1,-2,3}', '{4,-5,6}');
dist_tanimoto
--------------------
0.4576271186440678
(1 row)
- (8)余弦相似度
seaboxsql=# SELECT madlib.cosine_similarity('{1,-2,3}', '{4,-5,6}');
cosine_similarity
--------------------
0.9746318461970762
(1 row)
- (9)角距离
seaboxsql=# SELECT madlib.dist_angle('{1,-2,3}', '{4,-5,6}');
dist_angle
--------------------
0.2257261285527342
(1 row)
- 取矩阵行列
DROP TABLE matrix;
create table matrix (id integer, m integer[]); insert into matrix values
(1, '{{4,5},{3,5},{9,0}}');
seaboxsql=# SELECT
madlib.get_row(m, 1) as row_1,
madlib.get_row(m, 2) as row_2,
madlib.get_row(m, 3) as row_3,
madlib.get_row(m, 1) as row_1,
madlib.get_row(m, 2) as row_2
FROM
matrix;
row_1 | row_2 | row_3 | row_1 | row_2
-------+-------+-------+-------+-------
{4,5} | {3,5} | {9,0} | {4,5} | {3,5}
(1 row)
seaboxsql=#
- (11)求向量平均值
CREATE TABLE vector(id integer, v float8[]);
INSERT INTO vector VALUES (1, '{4,1}'),
(2, '{8,-6}'),
(3, '{5,9}');
seaboxsql=# SELECT madlib.avg(v) from vector;
avg
----------------------------------------
{5.666666666666667,1.3333333333333333}
(1 row)
- (12)求归一化平均值
seaboxsql=# SELECT madlib.normalized_avg(v) from vector;
normalized_avg
------------------------------------------
{0.9747565798343222,0.22327026239446793}
(1 row)
- (13)合并向量
seaboxsql=# SELECT madlib.matrix_agg(v) from vector;
matrix_agg
----------------------
{{4,1},{8,-6},{5,9}}
(1 row)
矩阵分解¶
1. 低秩矩阵分解
矩阵中的最大不相关向量的个数，叫做矩阵的秩。秩可以独享相关性，而向量的相关性实际上又带有了矩阵的结构信息。如果矩阵表达的是结构性信息，例如图像、用户推荐表等，那么这个矩阵之间存在一定的相关性，这个矩阵一般就是低秩的。
lmf_igd_run(
rel_output,
rel_source,
col_row,
col_column,
col_value,
row_dim,
column_dim,
max_rank,
stepsize,
scale_factor,
num_iterations,
tolerance
)
参数说明：
rel_output：输出表名。
rel_source：输入表名。
col_row：包含行号的列名。
col_column：包含列号的列名。
col_value：（row，col）位置对应的值。
row_dim：指示矩阵中的行数。
column_dim：指示矩阵中的列数。
max_rank：期望逼近的秩数。
stepsize：缺省值 0.01，超参数，决定梯度下降法的步长。
scale_factor：缺省值 0.1，超参数，决定初始缩放因子。
num_iterations：缺省值 10，不考虑收敛情况下的最大迭代次数。
tolerance：缺省值 0.0001，收敛误差，小于该误差时停止迭代。
2. 奇异值分解
低秩矩阵分解是用两个矩阵的成绩近似还原一个低秩矩阵，MADlib 提供了另一种矩阵分解方法， 即奇异值分解。奇异值分解简称 SVD，可以理解成将一个比较复杂的矩阵用更小更简单的三个子矩阵的相乘来表示，这三个矩阵描述了原矩阵重要的特性。SVD 用处很多，如推荐系统，数据降维等。
- (1)稠密矩阵的 SVD 函数
svd(
source_table,
output_table_prefix,
row_id,
k,
n_interations,
result_summary_table
);
参数说明：
source_table：源表名。
output_table_prefix：指定输出表名的前缀。
row_id：代表行 ID 的列名。
k：计算的奇异值个数。
n_iterations：运行的迭代次数。
result_summary_table：存储结果摘要的表的名称。
- (2)稀疏矩阵的 SVD 函数
svd_sparse(
source_table ， output_table_prefix ， row_id ， row_id ，
col_id ， value ， row_dim ， col_dim ， k,
n_iterations,
result_summary_table
);
参数说明：
source_table：源表名。
output_table_prefix：指定输出表名的前缀。
row_id：包含行下标的列名。
col_id：包含列下标的列名。
value：包含值得列名。
row_dim：矩阵的行数。
col_dim：矩阵的列数。
k：计算的奇异值个数。
n_iterations：运行的迭代次数。
result_summary_table：存储结果摘要的表的名称。
- (3)稀疏矩阵的本地实现 SVD 函数
svd_sparse_native(
source_table ， output_table_prefix ， row_id ，
col_id ， value ， row_dim ， col_dim ， k,
n_iterations ， result_summary_table
)
参数说明：
source_table：源表名。
output_table_prefix：指定输出表名的前缀。
row_id：包含行下标的列名
col_id：包含列下标的列名。
value：包含值得列名。
row_dim：矩阵的行数。
col_dim：矩阵的列数。
k：计算的奇异值个数。
n_iterations：运行的迭代次数。
result_summary_table：存储结果摘要的表的名称。
- (4)输出表
三个 SVD 函数的输出都是以下三个表
左奇异表矩阵表：表名为<output_table_prefix>_u。
右奇异表矩阵表：表名为<output_table_prefix>_v。
奇异值矩阵表：表名为<output_table_prefix>_s。
- (5)查看帮助信息
select madlib.svd();
select madlib.svd('usage');
select madlib.svd('example');
3. 函数示例，低秩矩阵分解
- (1)建立输入表并输入数据
CREATE TABLE tbl_idx_user (user_idx bigserial, userid varchar(10));
CREATE TABLE tbl_idx_music (music_idx bigserial, musicid varchar(10));
CREATE TABLE lmf_data (row int, col int, val float8);
INSERT INTO tbl_idx_user (userid)
values ('u1'),('u2'),('u3'),('u4'),('u5'),('u6'),('u7'),('u8'),('u9'),('u10');
INSERT INTO tbl_idx_music (musicid) values
('m1'),('m2'),('m3'),('m4'),('m5'),('m6'),
('m7'),('m8'),('m9'),('m10'),('m11'),('m12'),('m13'),('m14'),('m15');
INSERT INTO lmf_data values
(1,1,5),(1,6,-5),(1,9,5),(1,11,3),(1,12,1),
(1,13,5),(2,4,3),(2,9,3),(2,13,4),(3,3,1),
(3,5,2),(3,6,-5),(3,7,4),(3,11,-2),(3,12,-2),
(1,13,-2),(4,2,4),(4,3,4),(4,4,3),(4,7,-2),
(4,9,-5),(4,12,3),(6,2,5),(6,5,-5);
- (2)调用 lmf_igd_run 函数分解矩阵
seaboxsql=# SELECT
madlib.lmf_igd_run(
'lmf_mode1',
'lmf_data',
'row',
'col',
'val',
11,
16,
7,
0.1,
1,
10,
1e -9
);
lmf_igd_run
-------------
1
(1 row)
- (3)检查结果
seaboxsql=#SELECT
array_dims(matrix_u) as u_dims,
array_dims(matrix_v) as v_dim
from
lmf_mode1;
u_dims | v_dim
-------------+-------------
[1:11][1:7] | [1:16][1:7]
(1 row)
- (4)生成推荐矩阵
a. 建表
create table mat_a_sparse as
select
d1,
d2,
matrix_u [d1] [d2] val
from
(
select
matrix_u,
generate_series(1, array_upper(matrix_u, 1)) d1,
generate_series(1, array_upper(matrix_u, 2)) d2
from
lmf_mode1
) t
where
d1 is not NULL
and d2 is not NULL
and matrix_u [d1] [d2] is not NULL;
create table mat_b_sparse as
select
d1,
d2,
matrix_v [d1] [d2] val
from
(
select
matrix_v,
generate_series(1, array_upper(matrix_v, 1)) d1,
generate_series(1, array_upper(matrix_v, 2)) d2
from
lmf_mode1
) t
where
d1 is not NULL
and d2 is not NULL
and matrix_v [d1] [d2] is not NULL;
select
madlib.matrix_mult(
'mat_a_sparse',
'row=d1,col=d2,val=val',
'mat_b_sparse',
'row=d1,col=d2,val=val, trans=true',
'matrix_r'
);
select
madlib.matrix_sparsify(
'matrix_r',
'row=d1, val=val',
'matrix_r_sparse',
'col=d2, val=val'
);
seaboxsql=# drop table if exists matrix_r_sparse;
DROP TABLE
seaboxsql=# select madlib.matrix_sparsify('matrix_r', 'row=d1, val=val',
seaboxsql(# 'matrix_r_sparse', 'col=d2, val=val');
seaboxsql=# select * from matrix_r_sparse;
d1 | d2 | val
----+----+----------------------
4 | 4 | 1.3992247346247098
6 | 6 | -0.23073951419166963
2 | 2 | 0.6570604217878426
7 | 7 | -0.01702389907860038
3 | 3 | -0.2256712107242783
1 | 1 | 0.011853368147079827
5 | 5 | -0.12107407149859037
(7 rows)
b.执行命令
select t2.userid,
t3.musicid,
t1.val
from
(
select
d1,
d2,
val,
row_number() over (
partition by d1
order by
val desc
) rn
from
matrix_r_sparse t1
where
not exists (
select
1
from
lmf_data t2
where
t1.d1 = t2.row
and t1.d2 = t2.col
)
) t1,
tbl_idx_user t2,
tbl_idx_music t3
where
t1.rn = 1
and t2.user_idx = t1.d1
and t3.music_idx = t1.d2
order by
t2.user_idx;
c.执行结果
userid | musicid | val
--------+---------+----------------------
u2 | m2 | 0.6570604217878426
u5 | m5 | -0.12107407149859037
u6 | m6 | -0.23073951419166963
u7 | m7 | -0.01702389907860038
(4 rows)
其他转换¶
1. 透视表
pivot(
source_table ， output_table ， index ， pivot_cols ，
pivot_values ， aggregate_func ， fill_value ， keep_null ，
output_col_dictionary
)
参数说明：
source_table：源数据表。
output_table：包含转置后数据的输出表名。
index：逗号分隔的列名，构成输出透视表的分组列，分组汇总后的数据行存储在输出的透视表中。
pivot_cols：逗号分隔的列名，指示需要按值转成多了的数据列。
pivot_values：逗号分隔的列名，指示需要执行聚合的数据列。
aggregate_func：缺省值为“AVG”，逗号分隔的聚合函数列表。
fill_value：缺省为 null，用于每个聚合函数，在聚合之后替换输出表中的 null 值。
keep_null：缺省值为 false，若设置为 true，行转列后将建立 null 值对应的列。
output_col_dictionary：缺省值为 false，用于处理自动生成的列名长度超过 PostgreSQL
限制的 63 个字节。
2. 分类变量编码
encode_categorical_variable(
source_table,
output_table,
categorical_cols,
categorical_cols_to_exclude,
row_id top,
value_to_drop,
encode_null,
output_type,
output_dictionary,
distributed_by
)
参数说明：
source_table：源表名。
output_table：结果表名。
categorical_cols：逗号分隔的字符串，由需要编码的分类列名组成。
categorical_cols_to_exclude：指定不编码的列。
row_id：对应源表中的主键。
top：如果参数是整数，按分类值出现的比例对前 n 个值进行编码，如果是浮点型的范围值，按照分类值出现的比例，编码指定的部分值。
value_to_drop：该参数只针对哑编码方法，对于哑编码方式，指示每个分类变量，期望在结果表中取出的参考值，可以作为一个全局参数，作用于所有分类列。
encode_null：指定是否将 null 视为一个分类变量值，如果该参数值为 true，
对空值创建编码列，如果为 false，不对空值进行编码。
output_type：控制指示符变量的输出格式。
output_dictionary：缺省值为 false，用于处理自动生成的列名长度超过 PostgreSQL 限制的 63 个字节。
distributed_by：用于输出表的数据分布策略的列，支持两种数据分布策略，随机和哈希。
3. 词干提取
text_stem_token(text token)
text_stem_token_arr(text token_arr)
4. 函数示例(词干提取)
- (1)建表
create table token_tbl(id integer,word text);
- (2)插入数据
insert into
token_tbl
values
(1, 'kneel'),
(2, 'kneeled'),
(3, 'kneeling'),
(4, 'kneels'),
(5, 'knees'),
(6, 'knell'),
(7, 'knelt'),
(8, 'knew'),
(9, 'knick'),
(10, 'knif'),
(11, 'knife'),
(12, 'knight'),
(13, 'knightly'),
(14, 'knight s'),
(15, 'knit'),
(16, 'knits'),
(17, 'knitted'),
(18, 'knitting'),
(19, 'knives'),
(20, 'knob'),
(21, 'knobs'),
(22, 'knock'),
(23, 'knocked'),
(24, 'knocker'),
(25, 'knockers'),
(26, 'knocking'),
(27, 'knocks'),
(28, 'knopp'),
(29, 'knot'),
(30, 'knots');
- (3)查询结果
seaboxsql=# select id,madlib.stem_token(word) from token_tbl ;
id | stem_token
----+------------
1 | kneel
5 | knee
11 | knife
12 | knight
14 | knight
15 | knit
17 | knit
20 | knob
23 | knock
25 | knocker
26 | knock
30 | knot
2 | kneel
3 | kneel
4 | kneel
6 | knell
7 | knelt
8 | knew
9 | knick
10 | knif
13 | knight
16 | knit
18 | knit
19 | knive
21 | knob
22 | knock
24 | knocker
27 | knock
28 | knopp
29 | knot
(30 rows)
数据探索¶
对数据进行统计是从定量的角度去探索数据，是最基本的数据探索方式，其主要目的是了解数据从统计学上反应的量的特征，以便我们更好的去认识这些将要被挖掘的数据。
MADlib 提供了描述性统计、概率统计、推论统计三类数据统计模块。
描述性统计¶
1. 皮尔森相关函数
相关系数函数
orrelation(source_table, output_table, target_cols, verbose)
协方差函数
ovariance(source_table, output_table, target_cols, verbose)
参数说明：
source_table：包含输入数据的源表名。
output_table：保存相关矩阵的输出表名。
target_cols：缺省为*，需要计算相关性的列组成的逗号分隔符字符串，如果为 NULL 或*， 结果产生源表所有数字列相关系数或协方差。
verbose：缺省为 false，若为 true，将在控制台打印出详细信息。
- 汇总统计
summary(
source_table,
output_table,
target_cols,
grouping_cols,
get_distinct,
get_quartiles,
ntile_array,
how_many_mfv,
get_estimates
)
返回值：
output_table：TEXT 类型，输出表的名称。
row_count：INTEGER 类型，输出表的行数。
duration：FLOAT8 类型，计算汇总值所用的秒数。
参数说明：
source_table：包含输入数据的源表名。
output_table：包含汇总值得输出表名。
target_cols：缺省为 null，请求汇总列组成的逗号分隔字符串，若为 null，为所有列生成汇总统计。
grouping_cols：若为 null，为全表生成汇总统计。
get_distinct：缺省为 TRUE，表示计算不同值。
get_quartiles：缺省为 TRUE，表示计算四分函数。
ntile_array：缺省为 NULL，要计算的百分位数组。
how_many_mfv：缺省为 10，指定需要计算的最频繁的数目。
get_estimates：缺省为 TRUE，表示为不同值和最频繁值产生估算。
3. 查看帮助信息
select * from madlib.summary();
select madlib.summary('usage');
select madlib.summary('example');
4. 函数示例(汇总统计函数)
- (1)创建输入数据集
a.建表
create table houses (
id int,
tax int,
bedroom int,
bath float,
price int,
size int,
lot int
);
b.导入数据
copy houses from stdin with delimiter '|';
1|156|1|1|56789|1134|231
2|256|1|1|55789|1135|231
3|356|1|1|53859|1136|231
4|456|1|1|12859|1137|231
5|556|1|1|57489|1138|231
6|656|1|1|56589|1139|231
7|756|1|1|57489|1124|231
8|856|1|1|56779|1144|231
9|956|1|1|33789|1154|231
10|312|1|1|52789|1634|231
11|111|1|1|56989|1174|231
12|221|1|1|56749|1164|231
13|188|1|1|56589|1184|231
14|56|1|1|52589|1139|231
15|146|1|1|46789|1184|231
\.
c.查询结果
seaboxsql=# select * from houses;
id | tax | bedroom | bath | price | size | lot
----+-----+---------+------+-------+------+-----
1 | 156 | 1 | 1 | 56789 | 1134 | 231
5 | 556 | 1 | 1 | 57489 | 1138 | 231
11 | 111 | 1 | 1 | 56989 | 1174 | 231
12 | 221 | 1 | 1 | 56749 | 1164 | 231
14 | 56 | 1 | 1 | 52589 | 1139 | 231
15 | 146 | 1 | 1 | 46789 | 1184 | 231
2 | 256 | 1 | 1 | 55789 | 1135 | 231
3 | 356 | 1 | 1 | 53859 | 1136 | 231
4 | 456 | 1 | 1 | 12859 | 1137 | 231
6 | 656 | 1 | 1 | 56589 | 1139 | 231
7 | 756 | 1 | 1 | 57489 | 1124 | 231
8 | 856 | 1 | 1 | 56779 | 1144 | 231
9 | 956 | 1 | 1 | 33789 | 1154 | 231
10 | 312 | 1 | 1 | 52789 | 1634 | 231
13 | 188 | 1 | 1 | 56589 | 1184 | 231
(15 rows)
- (2)运行 summary 函数运行结果：
seaboxsql=#select
*
from
madlib.summary(
'houses',
'houses_summary',
'tax,bedroom,bath,price,size,lot',
'bedroom',
true,
true,
array [0.1,0.2,0.3],
5,
false
);
output_table | num_col_summarized | duration
----------------+--------------------+--------------------
houses_summary | 11 | 0.1230154037475586
(1 row)
seaboxsql=#
- (3)查询结果
seaboxsql=# select * from houses_summary;
-[ RECORD 1 ]--------+----------------------------------------
group_by |
group_by_value |
target_column | tax
column_number | 2
data_type | int4
row_count | 15
distinct_values | 15
missing_values | 0
blank_values |
fraction_missing | 0
fraction_blank |
positive_values | 15
negative_values | 0
zero_values | 0
mean | 402.53333333333336
variance | 83573.55238095239
confidence_interval | {256.2331490484946,548.8335176181721}
min | 56
max | 956
first_quartile | 172
median | 312
third_quartile | 606
quantile_array | {125,154,194.6}
most_frequent_values | {256,356,456,656,756}
mfv_frequencies | {1,1,1,1,1}
-[ RECORD 2 ]--------+----------------------------------------
group_by |
group_by_value |
target_column | bedroom
column_number | 3
data_type | int4
row_count | 15
distinct_values | 1
missing_values | 0
blank_values |
fraction_missing | 0
fraction_blank |
positive_values | 15
negative_values | 0
zero_values | 0
mean | 1
variance | 0
confidence_interval | {1,1}
min | 1
max | 1
first_quartile | 1
median | 1
third_quartile | 1
quantile_array | {1,1,1}
most_frequent_values | {1}
mfv_frequencies | {15}
-[ RECORD 3 ]--------+----------------------------------------
group_by |
group_by_value |
target_column | bath
column_number | 4
data_type | float8
row_count | 15
distinct_values | 1
missing_values | 0
blank_values |
fraction_missing | 0
fraction_blank |
positive_values | 15
negative_values | 0
zero_values | 0
mean | 1
variance | 0
confidence_interval | {1,1}
min | 1
max | 1
first_quartile | 1
median | 1
third_quartile | 1
quantile_array | {1,1,1}
most_frequent_values | {1}
mfv_frequencies | {15}
-[ RECORD 4 ]--------+----------------------------------------
group_by |
group_by_value |
target_column | price
column_number | 5
data_type | int4
row_count | 15
distinct_values | 13
missing_values | 0
blank_values |
fraction_missing | 0
fraction_blank |
positive_values | 15
negative_values | 0
zero_values | 0
mean | 50928.333333333336
variance | 149140020.95238096
confidence_interval | {44748.06200978199,57108.60465688468}
min | 12859
max | 57489
first_quartile | 52689
median | 56589
third_quartile | 56784
quantile_array | {38989,51429,53003}
most_frequent_values | {56589,57489,55789,53859,12859}
mfv_frequencies | {2,2,1,1,1}
-[ RECORD 5 ]--------+----------------------------------------
group_by |
group_by_value |
target_column | size
column_number | 6
data_type | int4
row_count | 15
distinct_values | 13
missing_values | 0
blank_values |
fraction_missing | 0
fraction_blank |
positive_values | 15
negative_values | 0
zero_values | 0
mean | 1181.3333333333333
variance | 16040.952380952382
confidence_interval | {1117.2381319182161,1245.4285347484504}
min | 1124
max | 1634
first_quartile | 1136.5
median | 1139
third_quartile | 1169
quantile_array | {1134.4,1135.8,1137.2}
most_frequent_values | {1184,1139,1136,1137,1124}
mfv_frequencies | {2,2,1,1,1}
-[ RECORD 6 ]--------+----------------------------------------
group_by |
group_by_value |
target_column | lot
column_number | 7
data_type | int4
row_count | 15
distinct_values | 1
missing_values | 0
blank_values |
fraction_missing | 0
fraction_blank |
positive_values | 15
negative_values | 0
zero_values | 0
mean | 231
variance | 0
confidence_interval | {231,231}
min | 231
max | 231
first_quartile | 231
median | 231
third_quartile | 231
quantile_array | {231,231,231}
most_frequent_values | {231}
mfv_frequencies | {15}
-[ RECORD 7 ]--------+----------------------------------------
group_by | bedroom
group_by_value | 1
target_column | tax
column_number | 2
data_type | int4
row_count | 15
distinct_values | 15
missing_values | 0
blank_values |
fraction_missing | 0
fraction_blank |
positive_values | 15
negative_values | 0
zero_values | 0
mean | 402.53333333333336
variance | 83573.55238095239
confidence_interval | {256.2331490484946,548.8335176181721}
min | 56
max | 956
first_quartile | 172
median | 312
third_quartile | 606
quantile_array | {125,154,194.6}
most_frequent_values | {256,356,456,656,756}
mfv_frequencies | {1,1,1,1,1}
-[ RECORD 8 ]--------+----------------------------------------
group_by | bedroom
group_by_value | 1
target_column | bath
column_number | 4
data_type | float8
row_count | 15
distinct_values | 1
missing_values | 0
blank_values |
fraction_missing | 0
fraction_blank |
positive_values | 15
negative_values | 0
zero_values | 0
mean | 1
variance | 0
confidence_interval | {1,1}
min | 1
max | 1
first_quartile | 1
median | 1
third_quartile | 1
quantile_array | {1,1,1}
most_frequent_values | {1}
mfv_frequencies | {15}
-[ RECORD 9 ]--------+----------------------------------------
group_by | bedroom
group_by_value | 1
target_column | price
column_number | 5
data_type | int4
row_count | 15
distinct_values | 13
missing_values | 0
blank_values |
fraction_missing | 0
fraction_blank |
positive_values | 15
negative_values | 0
zero_values | 0
mean | 50928.333333333336
variance | 149140020.95238096
confidence_interval | {44748.06200978199,57108.60465688468}
min | 12859
max | 57489
first_quartile | 52689
median | 56589
third_quartile | 56784
quantile_array | {38989,51429,53003}
most_frequent_values | {56589,57489,55789,53859,12859}
mfv_frequencies | {2,2,1,1,1}
-[ RECORD 10 ]-------+----------------------------------------
group_by | bedroom
group_by_value | 1
target_column | size
column_number | 6
data_type | int4
row_count | 15
distinct_values | 13
missing_values | 0
blank_values |
fraction_missing | 0
fraction_blank |
positive_values | 15
negative_values | 0
zero_values | 0
mean | 1181.3333333333333
variance | 16040.952380952382
confidence_interval | {1117.2381319182161,1245.4285347484504}
min | 1124
max | 1634
first_quartile | 1136.5
median | 1139
third_quartile | 1169
quantile_array | {1134.4,1135.8,1137.2}
most_frequent_values | {1184,1139,1136,1137,1124}
mfv_frequencies | {2,2,1,1,1}
-[ RECORD 11 ]-------+----------------------------------------
group_by | bedroom
group_by_value | 1
target_column | lot
column_number | 7
data_type | int4
row_count | 15
distinct_values | 1
missing_values | 0
blank_values |
fraction_missing | 0
fraction_blank |
positive_values | 15
negative_values | 0
zero_values | 0
mean | 231
variance | 0
confidence_interval | {231,231}
min | 231
max | 231
first_quartile | 231
median | 231
third_quartile | 231
quantile_array | {231,231,231}
most_frequent_values | {231}
mfv_frequencies | {15}
概率统计¶
- 累计分布函数
distribution_cdf(random variate[, parameter1 [, parameter2 [, parameter3] ] ]) - 概率密度函数
distribution_pdf(random variate[, parameter1 [, parameter2 [, parameter3 ] ] ]) - 概率质量函数
distribution_pmf(random variate[, parameter1 [, parameter2 [, parameter3 ] ] ]) - 分位函数
distribution_quantile(probability[[, parameter1 [, parameter2 [, parameter3 ] ] ]) - 伯努利分布
float8 bernoulli_cdf(float8 x, flaot8 sp) float8 bernoulli_pmf(float8 x, flaot8 sp) float8 bernoulli_quantile(float8 x, flaot8 sp) - 贝塔分布
float8 beta_cdf (float8 x, float8 alpha, float8 beta) float8 beta_pdf (float8 x, float8 alpha, float8 beta) float8 beta_quantile (float8 x, float8 alpha, float8 beta) - 二项分布
float8 binomial_cdf(float8 x, int4 n, float8 sp)
float8 binomial_pmf(float8 x, int4 n, float8 sp) float8 binomial_quantile(float8 x, int4 n, float8 sp) - 柯西分布 float8 cauchy_cdf(float8 x, float8 location, float8 scale) float8 cauchy_pdf(float8 x, float8 location, float8 scale) float8 cauchy_quantile(float8 x, float8 location, float8 scale)
-
卡方分布
float8 chi_squared_cdf(float8 x, float8 df)
float8 chi_squared_pdf(float8 x, float8 df)
float8 chi_squared_quantile(float8 x, float8 df) -
指数分布
float8 exponential_cdf(float8 x, float8 lambda)
float8 exponential_pdf(float8 x, float8 lambda) float8 exponential_quantile(float8 x, float8 lambda) - 极值分布
float8 extreme_value_cdf(float8 x, float8 location, float8 scale)
float8 extreme_value_pdf(float8 x, float8 location, float8 scale)
float8 extreme_value_quantile(float8 x, float8 location, float8 scale) - 费舍尔分布
float8 finisher_f_cdf(float8 x, float8 df1, float8 df2)
float8 finisher_f_pdf(float8 x, float8 df1, float8 df2)
float8 finisher_f_quantile(float8 x, float8 df1, float8 df2) - 伽玛分布
float8 gamma_cdf(float8 x, float8 shape, floa8t scale)
float8 gamma_pdf(float8 x, float8 shape, float8 scale)
float8 gamma_quantile(float8 x, float8 shape, float8 scale) - 几何分布
float8 geometric_cdf(float8 x, float8 sp) float8 geometric_pmf(float8 x, float8 sp) float8 geometric_quantile(float8 x, float8 sp) - 超几何分布
float8 hapergeometric_cdf(float8 x, int4 r, int4n, int4 N) float8 hapergeometric_pmf(float8 x, int4 r, int4n, int4 N)
float8 hapergeometric_quantile(float8 x, int4 r, int4n, int4 N) - 逆伽玛分布
float8 inverse_gamma_cdf(float8 x, float8 shape, float8 scale) float8 inverse_gamma_pmf(float8 x, float8 shape, float8 scale)
float8 inverse_gamma_quantile(float8 x, float8 shape, float8 scale) - Kolmogorov分布
float8 kolmogorov_cdf(float8 x) - 拉普拉斯分布
float8 laplace_cdf(float8 x, float8 mean, float8 scale) float8 laplace_pdf(float8 x, float8 mean, float8 scale) float8 laplace_quantile(float8 x, float8 mean, float8 scale) -
逻辑斯谛分布
float8 logistic_cdf(float8 x, float8 mean, float8 scale) float8 logistic_pdf(float8 x, float8 mean, float8 scale) float8 logistic_quantile(float8 x, float8 mean, float8 scale) -
对数正态分布
float8 lognormal_cdf(float8 x, float8 location, float8 scale) float8 lognormal_pdf(float8 x, float8 location, float8 scale) float8 lognormal_quantile(float8 x, float8 location, float8 scale) - 负二项分布
float8 negative_binomial_cdf (float8 x, float8 r, float8 sp) float8 negative_binomial_pdf (float8 x, float8 r, float8 sp) float8 negative_binomial_quantile (float8 x, float8 r, float8 sp) - 非中心贝塔分布
float8 non_central_beta_cdf (float8 x, float8 alpha, float8 beta, float8 ncp) float8 non_central_beta_pdf (float8 x, float8 alpha, float8 beta, float8 ncp) float8 non_central_beta_quantile x, float8 alpha, float8 beta, float8 ncp) - 非中心卡方分布
float8 non_central_chi_squared_cdf(float8 x, float8 df, float8 ncp) float8 non_central_chi_squared_pdf(float8 x, float8 df, float8 ncp) float8 non_central_chi_squared_quantile(float8 x, float8 df, float8 ncp) - 非中心F分布
float8 non_central_f_cdf(float8 x, float8 df1, float8 df2, float8 ncp)
float8 non_central_f_pdf(float8 x, float8 df1, float8 df2, float8 ncp)
float8 non_central_f_quantile(float8 x, float8 df1, float8 df2, float8 ncp) - 非中心T分布
float8 non_central_t_cdf(float8 x, float8 df, float8 ncp) float8 non_central_t_pdf(float8 x, float8 df, float8 ncp) float8 non_central_t_quantile(float8 x, float8 df, float8 ncp) - 正态分布
float8 normal_cdf(float8 x, float8 mean=0, float8 sd=1) float8 normal_cdf(float8 x, float8 mean)
float8 normal_cdf(float8 x)
float8 normal_pdf(float8 x, float8 mean=0, float8 sd=1)
float8 normal_pdf(float8 x, float8 mean)
float8 normal_pdf(float8 x)
float8 normal_quantile(float8 p, float8 mean=0, float8 sd=1) float8 normal_quantile(float8 p, float8 mean)
float8 normal_quantile(float8 p) - 帕累托分布
float8 pareto_cdf(float8 x, float8 scale, float8 shape) float8 pareto_pdf(float8 x, float8 scale, float8 shape) float8 pareto_quantile(float8 x, float8 scale, float8 shape) - 泊松分布
float8 poisson_cdf(float8 x, float8 mean)
float8 poisson_pdf(float8 x, float8 mean)
float8 poisson_quantile(float8 x, float8 mean) - 瑞利分布
float8 rayleigh_cdf(float8 x, float8 scale)
float8 rayleigh_pdf(float8 x, float8 scale) float8 rayleigh_quantile(float8 x, float8 scale) - 学生t分布
float8 student_t_cdf(float8 x, float8 df)
float8 student_t_pdf(float8 x, float8 df)
float8 student_t_quantile(float8 x, float8 df) - 三角分布
float8 triangular_cdf(float8 x, float8 lower, float8 mode, float8 upper)
float8 triangular_pdf(float8 x, float8 lower, float8 mode, float8 upper) float8 triangular_quantile(float8 x, float8 lower, float8 mode, float8 upper) - 均匀分布
float8 uniform_cdf(float8 x, float8 lower, float8 mode, float8 upper) float8 uniform_pdf(float8 x, float8 lower, float8 mode, float8 upper) float8 uniform_quantile(float8 x, float8 lower, float8 mode, float8 upper) - 韦布尔分布
float8 weibull_cdf(float8 x, float8 shape, float8 scale)
float8 weibull_pdf(float8 x, float8 shape, float8 scale)
float8 weibull_quantile(float8 x, float8 shape, float8 scale)
2. 函数示例
- 求标准正态分布下，1 的概率密度函数
seaboxsql=# select madlib.normal_pdf(1,0,1),exp(-0.5)/sqrt(2*pi());
normal_pdf | ?column?
---------------------+---------------------
0.24197072451914337 | 0.24197072451914337
(1 row)
- 求标准正态分布下，1 的累计分布函数。
seaboxsql=# select madlib.normal_cdf(1,0,1);
normal_cdf
--------------------
0.8413447460685429
(1 row)
- 求标准正态分布下，概率为 0、0.25、0.5、0.75、1 的分位数值
seaboxsql=# Select
seaboxsql-# madlib.normal_quantile(0, 0, 1),
seaboxsql-# madlib.normal_quantile(0.25, 0, 1),
seaboxsql-# madlib.normal_quantile(0.5, 0, 1),
seaboxsql-# madlib.normal_quantile(0.75, 0, 1),
seaboxsql-# madlib.normal_quantile(1, 0, 1);
-[ RECORD 1 ]---+--------------------
normal_quantile | -Infinity
normal_quantile | -0.6744897501960818
normal_quantile | 0
normal_quantile | 0.6744897501960818
normal_quantile | Infinity
主成分分析¶
主成分分析（PCA），采取一种数学降维的方法，其所要做的就是设法将原来众多具有一定相关性的变量，重新组合成一组新的相互无关的综合变量来代替原来的变量。
1. 训练函数
- (1)稠密/稀疏矩阵的训练函数
pca_train (
source_table,
out_table,
row_id,
components_param,
grouping_cols,
lanczos_iter,
use_correlation,
result_summary_table
)
pca_sparse_train (
source_table,
out_table,
row_id,
col_id,
val_id,
row_dim,
col_dim,
components_param,
grouping_cols,
lanczos_iter,
use_correlation,
result_summary_table
)
参数说明：
source_table：输入表名。
out_table：输出表名。
row_id：输入表中表示行 ID 的列名。
col_id：稀疏矩阵中表示列 ID 的列名。
val_id：稀疏矩阵中表示非零元素值得列名。
row_dim：稀疏矩阵中矩阵的实际行数。
col_dim：稀疏矩阵中矩阵的实际列数。
components_param：控制如何从输入数据确定主成分的数量。
grouping_cols：指定逗号分隔的列名。
lanczos_iter：计算 SVD 时的 Lanczos 迭代次数，迭代次数越大精度越高。
use_correlation：在计算主成分时，是否使用相关矩阵代替协方差矩阵。
result_summary_table：指定概要表的名称。
- (2)查看帮助信息
select madlib.pca_train('usage');
2. 投影函数
- (1)稠密/稀疏矩阵的投影函数
pca_project(
source_table ， pc_table ， out_table ， row_id ， residual_table ， result_summary_table
)
pca_sparse_project(
source_table ， pc_table ， out_table ， row_id ， val_id ， r ow_dim ，
col_dim ， residual_table ， result_summary_table
)
参数说明：
source_table：输入源表。
pc_table：主成分表名。
out_table：输入数据降维后的输出表名。
row_id：同 PCA 训练函数。
val_id：同 PCA 训练函数。
row_dim：同 PCA 训练函数。
col_dim：同 PCA 训练函数。
residual_table：残余表的名称。
result_summary_table：结果概要表的名称。
- (2)查看帮助信息
select madlib.pca_project('usage');
select madlib.pca_sparse_project('usage');
3. 函数示例
我们用一个企业综合实力排序的例子说明 MADlib PCA 的用法。为了系统的分析某 IT 类企业的经济效益，选择了 8 个不同的利润指标，对 15 家企业进行调研，得到下表数据，现在需要根据这些 数据对 15 家企业进行综合实力排序。
- (1)创建原始稠密矩阵表并添加数据
drop table if exists mat; create table mat (id integer, row_vec double precision[]); insert into mat values (1, '{40.4, 24.7, 7.2, 6.1, 8.3, 8.7, 2.442, 20}'), (2, '{ 25, 12.7, 11.2, 11, 12.9, 20.2, 3.542, 9.1}'), (3, '{13.2, 3.3, 3.9, 4.3, 4.4, 5.5, 0.578, 3.6}'), (4, '{22.3, 6.7, 5.6, 3.7, 6, 7.4, 0.176, 7.3}'), (5, '{34.3, 11.8, 7.1, 7.1, 8, 8.9, 1.726, 27.5}'), (6, '{35.6, 12.5, 16.4, 16.7, 22.8, 29.3, 3.017, 26.6}'), (7, '{ 22, 7.8, 9.9, 10.2, 12.6, 17.6, 0.847, 10.6}'), (8, '{48.4, 13.4, 10.9, 9.9, 10.9, 13.9, 1.772, 17.8}'), (9, '{40.6, 19.1, 19.8, 19, 29.7, 39.6, 2.449, 35.8}'), (10, '{24.8, 8, 9.8, 8.9, 11.9, 16.2, 0.789, 13.7}'), (11, '{12.5, 9.7, 4.2, 4.2, 4.6, 6.5, 0.874, 3.9}'), (12, '{ 1.8, 0.6, 0.7, 0.7, 0.8, 1.1, 0.056, 1}'), (13, '{32.3, 13.9, 9.4, 8.3, 9.8, 13.3, 2.126, 17.1}'), (14, '{38.5, 9.1, 11.3, 9.5, 12.2, 16.4, 1.327, 11.6}'), (15, '{26.2, 10.1, 5.6, 15.6, 7.7, 30.1, 0.126, 25.9}'); - (2)调用 PCA 训练函数生成特征向量矩阵
select madlib.pca_train('mat','result_table','id',4);
- (3)查看输出表
select
row_id id,
std,
prop,
p1 :: text || ',' || p2 :: text || ',' || p3 :: text || ',' || p4 :: text || ','
|| p5 :: text || ',' || p6 :: text || ',' || p7 :: text || ',' || p8 :: text principal_components
from
(
select
row_id,
round(p [1] :: numeric, 3) p1,
round(p [2] :: numeric, 3) p2,
round(p [3] :: numeric, 3) p3,
round(p [4] :: numeric, 3) p4,
round(p [5] :: numeric, 3) p5,
round(p [6] :: numeric, 3) p6,
round(p [7] :: numeric, 3) p7,
round(p [8] :: numeric, 3) p8,
round(std_dev :: numeric, 3) std,
round(proportion :: numeric, 3) prop
from
(
select
row_id,
principal_components p,
std_dev,
proportion
from
result_table
) t
) t
order by
row_id;
seaboxsql=# select row_id id, std, prop,
seaboxsql-# p1::text||','||p2::text||','||
seaboxsql-# p3::text||','||p4::text||','||
seaboxsql-# p5::text||','||p6::text||','||
seaboxsql-# p7::text||','||p8::text principal_components from (select row_id,
seaboxsql(# round(p[1]::numeric,3) p1,
seaboxsql(# round(p[2]::numeric,3) p2,
seaboxsql(# round(p[3]::numeric,3) p3,
seaboxsql(# round(p[4]::numeric,3) p4,
seaboxsql(# round(p[5]::numeric,3) p5,
seaboxsql(# round(p[6]::numeric,3) p6,
seaboxsql(# round(p[7]::numeric,3) p7,
seaboxsql(# round(p[8]::numeric,3) p8, round(std_dev::numeric,3) std, round(proportion::numeric,3) prop
seaboxsql(# from (select row_id, principal_components p, std_dev, proportion from result_table) t) t
seaboxsql-# order by row_id;
-[ RECORD 1 ]--------+----------------------------------------------------
id | 1
std | 19.487
prop | 0.744
principal_components | 0.550,0.221,0.222,0.234,0.324,0.460,0.035,0.477
-[ RECORD 2 ]--------+----------------------------------------------------
id | 2
std | 9.067
prop | 0.161
principal_components | -0.679,-0.258,0.092,0.224,0.255,0.590,-0.019,0.009
-[ RECORD 3 ]--------+----------------------------------------------------
id | 3
std | 5.063
prop | 0.050
principal_components | 0.293,-0.117,0.358,0.036,0.371,0.070,0.056,-0.791
-[ RECORD 4 ]--------+----------------------------------------------------
id | 4
std | 3.580
prop | 0.025
principal_components | 0.357,-0.823,-0.140,0.148,-0.331,0.155,-0.135,0.046
可以看到，主成分数量为 3 时，累计标准差比例为 95.5，即反映了 95.5%的原始信息，而维度已经从 8 个降低到了 3 个。
- (4)调用 PCA 投影函数生成降维后的数据表
select
madlib.pca_project(
'mat',
'result_table',
'out_table',
'id',
'residual_table',
'result_summary_table'
);
- (5)查看投影函数输出表
select * from out_table order by row_id;
-[ RECORD 1 ]-----------------------------------------------------------------------------
row_id | 1
row_vec | {7.080211736652196,-17.611340838009287,-3.625049288738968,-7.236643163274299}
-[ RECORD 2 ]-----------------------------------------------------------------------------
row_id | 2
row_vec | {-0.3774990740942843,5.250839113183314,6.066672643953421,-3.0891018146953098}
-[ RECORD 3 ]-----------------------------------------------------------------------------
row_id | 3
row_vec | {-24.365951692627746,2.6929455204927426,0.8546804873083715,1.1344957687657058}
-[ RECORD 4 ]-----------------------------------------------------------------------------
row_id | 4
row_vec | {-15.235298685289816,-2.775692353196144,1.4878903263125434,1.2516922073981522}
-[ RECORD 5 ]-----------------------------------------------------------------------------
row_id | 5
row_vec | {4.642648294782504,-9.801922141553115,-9.974411664381785,1.933141707723937}
-[ RECORD 6 ]-----------------------------------------------------------------------------
row_id | 6
row_vec | {23.614659861209716,7.912771871975425,1.7012544671941363,-0.006446945513589153}
-[ RECORD 7 ]-----------------------------------------------------------------------------
row_id | 7
row_vec | {-4.25445515317285,6.710531071166752,3.6348957444047985,0.061922553031729}
-[ RECORD 8 ]-----------------------------------------------------------------------------
row_id | 8
row_vec | {12.854730331749828,-15.21512767242868,4.5320206278191275,4.897316636014207}
-[ RECORD 9 ]-----------------------------------------------------------------------------
row_id | 9
row_vec | {40.453111473201,11.566606363448447,-0.3335140897319313,-3.968431378510502}
-[ RECORD 10 ]----------------------------------------------------------------------------
row_id | 10
row_vec | {-2.391872102585449,3.480639228228872,1.5363367879213132,0.8846089291035552}
-[ RECORD 11 ]----------------------------------------------------------------------------
row_id | 11
row_vec | {-22.617367443032023,2.1597095588416058,-0.07113929246538753,-4.373975914842054}
-[ RECORD 12 ]----------------------------------------------------------------------------
row_id | 12
row_vec | {-37.22731028009528,6.507780453673364,-3.0621610870869773,-0.3430558126420974}
-[ RECORD 13 ]----------------------------------------------------------------------------
row_id | 13
row_vec | {2.4567683795893993,-5.55018275234772,-0.7158631460159338,-1.100615956491987}
-[ RECORD 14 ]----------------------------------------------------------------------------
row_id | 14
row_vec | {5.058286737893314,-5.67262157438274,7.797627161187961,4.5139805482628175}
-[ RECORD 15 ]----------------------------------------------------------------------------
row_id | 15
row_vec | {10.309337615819462,10.345064150907232,-9.82923967768072,5.441112635669691}
- (6)按照主成分总得分降序排列得出综合实力排序
select
row_id id,
row_vec,
round(madlib.array_sum(row_vec) :: numeric, 4) r
from
out_table
order by
r desc;
seaboxsql=# select
row_id id,
row_vec,
round(madlib.array_sum(row_vec) :: numeric, 4) r
from
out_table
order by
r desc;
-[ RECORD 1 ]-----------------------------------------------------------------------------
id | 9
row_vec | {40.453111473201,11.566606363448447,-0.3335140897319313,-3.968431378510502}
r | 47.7178
-[ RECORD 2 ]-----------------------------------------------------------------------------
id | 6
row_vec | {23.614659861209716,7.912771871975425,1.7012544671941363,-0.006446945513589153}
r | 33.2222
-[ RECORD 3 ]-----------------------------------------------------------------------------
id | 15
row_vec | {10.309337615819462,10.345064150907232,-9.82923967768072,5.441112635669691}
r | 16.2663
-[ RECORD 4 ]-----------------------------------------------------------------------------
id | 14
row_vec | {5.058286737893314,-5.67262157438274,7.797627161187961,4.5139805482628175}
r | 11.6973
-[ RECORD 5 ]-----------------------------------------------------------------------------
id | 2
row_vec | {-0.3774990740942843,5.250839113183314,6.066672643953421,-3.0891018146953098}
r | 7.8509
-[ RECORD 6 ]-----------------------------------------------------------------------------
id | 8
row_vec | {12.854730331749828,-15.21512767242868,4.5320206278191275,4.897316636014207}
r | 7.0689
-[ RECORD 7 ]-----------------------------------------------------------------------------
id | 7
row_vec | {-4.25445515317285,6.710531071166752,3.6348957444047985,0.061922553031729}
r | 6.1529
-[ RECORD 8 ]-----------------------------------------------------------------------------
id | 10
row_vec | {-2.391872102585449,3.480639228228872,1.5363367879213132,0.8846089291035552}
r | 3.5097
-[ RECORD 9 ]-----------------------------------------------------------------------------
id | 13
row_vec | {2.4567683795893993,-5.55018275234772,-0.7158631460159338,-1.100615956491987}
r | -4.9099
-[ RECORD 10 ]----------------------------------------------------------------------------
id | 5
row_vec | {4.642648294782504,-9.801922141553115,-9.974411664381785,1.933141707723937}
r | -13.2005
-[ RECORD 11 ]----------------------------------------------------------------------------
id | 4
row_vec | {-15.235298685289816,-2.775692353196144,1.4878903263125434,1.2516922073981522}
r | -15.2714
-[ RECORD 12 ]----------------------------------------------------------------------------
id | 3
row_vec | {-24.365951692627746,2.6929455204927426,0.8546804873083715,1.1344957687657058}
r | -19.6838
-[ RECORD 13 ]----------------------------------------------------------------------------
id | 1
row_vec | {7.080211736652196,-17.611340838009287,-3.625049288738968,-7.236643163274299}
r | -21.3928
-[ RECORD 14 ]----------------------------------------------------------------------------
id | 11
row_vec | {-22.617367443032023,2.1597095588416058,-0.07113929246538753,-4.373975914842054}
r | -24.9028
-[ RECORD 15 ]----------------------------------------------------------------------------
id | 12
row_vec | {-37.22731028009528,6.507780453673364,-3.0621610870869773,-0.3430558126420974}
r | -34.1247
最终得出第 11 家企业综合实力最强，第 9 家企业综合实力最弱。
回归¶
线性回归¶
1. 训练函数
linregr_train(
source_table ， out_table ， dependent_varname ， independent_varname,
grouping_cols,
heteroskedaticity_option
)
参数说明：
source_table：源表名。
out_table：输出表名。
dependent_varname：因变量列的名称。
independent_varname：评估使用的自变量的表达式列表。
grouping_cols：将输入数据集分成离散组的表达式，每个组运行一个回归。
heteroskedaticity_option：缺省 False，为 True 时会计算并返回模型的异方差。
2. 预测函数
linregr_predict(coef,col_ind)
参数说明：
coef：float8 类型，回归系数向量。
col_ind：float8 类型，包含自变量列名索引的数组。
3. 函数示例
- (1)调用训练函数建立模型
drop table if exists t1;
create table t1 (a float, b float);
insert into t1 values
(23.8,41.4),(27.6,51.8),(31.6,61.7),(32.4,67.9),(33.7,68.7),
(34.9,77.5),(43.2,95.9),(52.8,137.4),(63.8,155.0),(73.4,175.0);
drop table if exists t1_linregr, t1_linregr_summary;
select madlib.linregr_train('t1','t1_linregr','b','array[1, a]');
select * from t1_linregr;
\x on
seaboxsql=# select * from t1_linregr;
-[ RECORD 1 ]------------+------------------------------------------------------------------------------------
coef | {-23.5493464974993,2.799121440496144}
r2 | 0.9867780454319058
std_err | {5.102826992725827,0.11455520804997091}
t_stats | {-4.614960791551296,24.43469387507131}
p_values | {0.0017214811063985813,8.401379814060306e-09}
condition_no | 127.16907667009139
num_rows_processed | 10
num_missing_rows_skipped | 0
variance_covariance | {{26.03884331769131,-0.5474872082440451},{-0.5474872082440451,0.01312289569137212}}
- (2)利用预测函数估计残差
select
a,
b,
predict,
b - predict residual
from
(
select
t1.*,
madlib.linregr_predict(m.coef, array [1, a]) as predict
from
t1,
t1_linregr m
) t;
seaboxsql=# select
a,
b,
predict,
b - predict residual
from
(
select
t1.*,
madlib.linregr_predict(m.coef, array [1, a]) as predict
from
t1,
t1_linregr m
) t;
a | b | predict | residual
------+-------+--------------------+-----------------------
27.6 | 51.8 | 53.70640526019427 | -1.9064052601942763
31.6 | 61.7 | 64.90289102217885 | -3.2028910221788465
43.2 | 95.9 | 97.37269973193413 | -1.472699731934128
23.8 | 41.4 | 43.06974378630893 | -1.6697437863089348
32.4 | 67.9 | 67.14218817457576 | 0.7578118254242412
33.7 | 68.7 | 70.78104604722076 | -2.0810460472207524
34.9 | 77.5 | 74.13999177581611 | 3.3600082241838862
52.8 | 137.4 | 124.2442655606971 | 13.155734439302904
63.8 | 155 | 155.03460140615468 | -0.034601406154678216
73.4 | 175 | 181.9061672349177 | -6.906167234917689
(10 rows)
广义线性模型¶
1. 培训函数
glm(
source_table,
model_table,
dependent_varname,
independent_varname,
family_params,
grouping_col,
optim_params,
verbose
)
参数说明：
source_table：源表名。
model_table：输出表名。
dependent_varname：因变量列的名称。
independent_varname：评估使用的自变量的表达式的列表。
family_params：分布族参数。
grouping_col：将输入数据集分成离散组的表达式。
optim_params：优化相关参数。
verbose：指定是否输出训练结果的详细信息。
2. 预测函数
glm_predict(coef， col_ind_var link)
参数说明：
coef：float8 类型，训练模型获得的回归系数向量。
col_ind_var：float8 类型，包含自变量列名索引的数组。
link：text 类型，连接函数字符串，应该与训练函数使用相同的连接函数。
3. 示例函数
- (1)创建表并添加数据
create table t1 (x float, y float);
insert into t1 values
(1.5,7.0), (4.5,4.8), (7.5,3.6), (10.5,3.1), (13.5,2.7),
(16.5,2.5), (19.5,2.4), (22.5,2.3), (25.5,2.2);
- (2)使用 identity 连接函数
drop table if exists t1_glm, t1_glm_summary;
select madlib.glm( 't1','t1_glm','y','array[1, x]','family=gamma, link=identity');
seaboxsql=# select * from t1_glm;
-[ RECORD 1 ]------+---------------------------------------------
coef | {5.032482978892998,-0.12429121017080266}
log_likelihood | -8.093849757758298
std_err | {0.5943361501291856,0.030461570989451937}
t_stats | {8.46740178567151,-4.080262643507176}
p_values | {6.331950308857692e-05,0.004688132433599113}
dispersion | 0.05003214766191885
num_rows_processed | 9
num_rows_skipped | 0
num_iterations | 11
逻辑回归¶
1. 训练函数
logregr_train (
source_table,
out_table,
dependent_varname,
independent_varname,
grouping_cols,
max_iter,
optimizer,
tolerance,
verbose
)
参数说明：
source_table：包含训练数据的源表名。
out_table：包含输出模型的表名。主输出表列和概要输出表列如表 12-1、表 12-3 所示。
dependent_varname：训练数据中因变量列的名称（BOOLEAN 兼容类型），或者一个布尔表达式。
independent_varname：评估使用的自变量的表达式列表，一般显式地由包括一个常数 1项的自变量列表提供。
grouping_cols（可选）：缺省值为 NULL。和 SQL 中的“GROUP BY”类似，是一个将输入数据集分成离散组的表达式，每个组运行一个回归。此值为 NULL 时，将不使用分组， 并产生一个单一的结果模型。
max_iter（可选）：缺省值 20，指定允许的最大迭代次数。
optimizer（可选）：缺省值为‘irls’，指定所使用的优化器的名称：
‘newton’或‘irls’：加权迭代最小二乘。
‘cg’：共轭梯度法。
‘igd’：梯度下降法。
tolerance（可选）：缺省值为 0.0001，连续的迭代次数的对数似然值之间的差异。零不能作为收敛准则，因此当连续两次的迭代差异小于此值时停止执行。
verbose（可选）：缺省值为 FALSE，指定是否提供训练的详细输出结果。
表12-1 Logregr_train 函数主输出表列说明
| 列名 | 数据类型 | 描述 |
|---|---|---|
| <…> | TEXT | 分组列，取决于grouping_col输入，可能是多个列。 |
| coef | FLOAT8[] | 回归系数向量。 |
| log_likelihood | FLOAT8 | 对数似然值。 |
| std_err | FLOAT8[] | 系数的标准方差向量。 |
| z_stat | FLOAT8[] | 系数的z统计量向量。 |
| p_values | FLOAT8[] | 系数的P值向量。 |
| odds_ratios | FLOAT8[] | 让步比exp(ci)。 |
| condition_no | FLOAT8[] | X*X矩阵的条件数。高条件数说明结果中的一些数值不稳定， 产生的模型不可靠。这通常是由于底层设计矩阵中有相当多的共线性造成的，在这种情况下可能更合适使用其他回归技术。* |
| num_iterations | INTEGER | 实际迭代次数。如果提供了tolerance参数，并且算法在所有迭代完成之前收敛，此列的值将会与max_iter参数的值不同。 |
| num_rows_processed | INTEGER | 实际处理的行数，等于源表中的行数减去跳过的行数。 |
| num_missing_rows_skipped | INTEGER | 训练时跳过的行数。如果自变量名是NULL或者包含NULL 值，则该行被跳过。 |
表12-2 Logregr_train 函数概要输出表说明
| 列名 | 数据类型 | 描述 |
|---|---|---|
| source_table | TEXT | 源数据表名称。 |
| out_table | TEXT | 输出表名。 |
| dependent_varname | TEXT | 因变量名。 |
| independent_varname | TEXT | 自变量名。 |
| optimizer_params | TEXT | 包含所有优化参数的字符串，形式是 'optimizer=…,max_iter=…,tolerance=…'。 |
| num_all_groups | INTEGER | 用逻辑回归模型拟合了多少组数据。 |
| num_failed_groups | INTEGER | 有多少组拟合过程失败。 |
| num_rows_processed | INTEGER | 用于计算的总行数。 |
| num_missing_rows_skiped | INTEGER | 跳过的总行数。 |
2. 预测函数
- 预测因变量的布尔值的函数
logregr_predict(coefficients, ind_var)
- 预测因变量是“真”的概率值的函数：
logregr_predict_prob(coefficients,ind_var)
参数说明：
coefficients：[FLOAT8]类型，来自 logregr_train()的模型系数。
ind_var：自变量构成的 DOUBLE 数组，其长度应该与调用 logregr_train()函数时，
由independent_varname 参数所赋值的数组相同。
3. 函数示例
- (1)建立数据表并装载数据
drop table if exists source_data;
create table source_data
(id integer not null, x1 float8, x2 float8, x3 float8, y int);
insert into source_data values
( 1, -62.8,89.5,1.7,0),
( 2, 3.3,-3.5,1.1,0),
( 3,-120.8,03.2,2.5,0),
( 4, -18.1,28.8,1.1,0),
( 5, -3.8,50.6,0.9,0),
( 6, -61.2,56.2,1.7,0),
( 7, -20.3,17.4,1 ,0),
( 8,-194.5,25.8,0.5,0),
( 9, 20.8,-4.3,1 ,0),
(10,-106.1,22.9,1.5,0),
(11, 43 ,16.4,1.3,1),
(12, 47 ,16 ,1.9,1),
(13, -3.3, 4 ,2.7,1),
(14, 35 ,20.8,1.9,1),
(15, 46.7,12.6,0.9,1),
(16, 20.8,12.5,2.4,1),
(17, 33 ,23.6,1.5,1),
(18, 26.1,10.4,2.1,1),
(19, 68.6,13.8,1.6,1),
(20, 37.3,33.4,3.5,1);
drop table if exists source_data_predict;
create table source_data_predict
(id integer not null, x1 float8, x2 float8, x3 float8, y int);
insert into source_data_predict values (21,-49.2,-17.2,0.3),
(22,-19.2,-36.7,0.8),
(23, 40.6, 5.8,1.8),
(24, 34.6, 26.4,1.8),
(25, 19.9, 26.7,2.3);
- (2)训练回归模型
drop table if exists loan_logregr, loan_logregr_summary;
select madlib.logregr_train ('source_data','loan_logregr','y','array[1, x1, x2, x3]',null,20,'irls');
- (3)查看回归结果
select
round(unnest(coef) :: numeric, 4) as coefficient,
round(unnest(std_err) :: numeric, 4) as standard_error,
round(unnest(z_stats) :: numeric, 4) as z_stat,
round(unnest(p_values) :: numeric, 4) as pvalue,
round(unnest(odds_ratios) :: numeric, 4) as odds_ratio
from
loan_logregr;
seaboxsql=# select
round(unnest(coef) :: numeric, 4) as coefficient,
round(unnest(std_err) :: numeric, 4) as standard_error,
round(unnest(z_stats) :: numeric, 4) as z_stat,
round(unnest(p_values) :: numeric, 4) as pvalue,
round(unnest(odds_ratios) :: numeric, 4) as odds_ratio
from
loan_logregr;
coefficient | standard_error | z_stat | pvalue | odds_ratio
-------------+----------------+---------+--------+------------------
-50.8355 | 719.4014 | -0.0707 | 0.9437 | 0.0000
0.7691 | 9.4506 | 0.0814 | 0.9351 | 2.1579
0.3782 | 9.4351 | 0.0401 | 0.9680 | 1.4597
24.3905 | 453.3033 | 0.0538 | 0.9571 | 39143137952.8503
(4 rows)
- (4)使用 Logistic 回归预测因变量
select
p.id,
madlib.logregr_predict(coef, array [1, x1, x2, x3])
from
source_data_predict p,
loan_logregr m
order by
p.id;
seaboxsql=# select
p.id,
madlib.logregr_predict(coef, array [1, x1, x2, x3])
from
source_data_predict p,
loan_logregr m
order by
p.id;
id | logregr_predict
----+-----------------
21 | f
22 | f
23 | t
24 | t
25 | t
(5 rows)
- (5)预测因变量为“真”的概率
select
p.id,
madlib.logregr_predict_prob(coef, array [1, x1, x2, x3])
from
source_data_predict p,
loan_logregr m
order by
p.id;
seaboxsql=# select
p.id,
madlib.logregr_predict_prob(coef, array [1, x1, x2, x3])
from
source_data_predict p,
loan_logregr m
order by
p.id;
id | logregr_predict_prob
----+-----------------------
21 | 6.925373923141066e-39
22 | 9.007972637600254e-27
23 | 0.9999999999968645
24 | 0.9999999999998692
25 | 0.999999999999952
(5 rows)
- (6)在训练数据上执行预测函数
select
p.id,
madlib.logregr_predict(coef, array [1, x1, x2, x3]),
p.y
from
source_data p,
loan_logregr m
order by
p.id;
seaboxsql=# select
p.id,
madlib.logregr_predict(coef, array [1, x1, x2, x3]),
p.y
from
source_data p,
loan_logregr m
order by
p.id;
id | logregr_predict | y
----+-----------------+---
1 | f | 0
2 | f | 0
3 | f | 0
4 | f | 0
5 | f | 0
6 | f | 0
7 | f | 0
8 | f | 0
9 | f | 0
10 | f | 0
11 | t | 1
12 | t | 1
13 | t | 1
14 | t | 1
15 | t | 1
16 | t | 1
17 | t | 1
18 | t | 1
19 | t | 1
20 | t | 1
(20 rows)
多类回归¶
1. 训练函数
multinom(
source_table,
model_table,
dependent_varname,
independent_varname,
ref_category,
link_func,
grouping_col,
optim_params,
verbose
)
参数说明：
source_table：包含训练数据的表名。
model_table：包含输出模型的表名。主输出表列和概要输出表列如表 12-3、表 12-4 所示
dependent_varname：因变量列名。
independent_varname：评估使用的自变量的表达式列表，一般显式地由包括一个常数 1项的自变量列表提供。
link_function（可选）：缺省值为'logit'。连接函数参数，当前仅支持 logit。
ref_category（可选）：缺省值为'0'，该参数指定参考类别。在做多类回归时，如果因变量 Y 有 n 个值，以其中一个类别作为参考类别，其它类别都同它相比较生成 n-1 个非冗余的logit 变量模型。对于参考类别，其模型中所有系数均为 0。
grouping_col（可选）：缺省值为 NULL。和 SQL 中的“GROUP BY”类似，是一个将输入数据集分成离散组的表达式，每个组运行一个回归。此值为 NULL 时，将不使用分组， 并产生一个单一的结果模型。
optim_params（可选）：缺省值为'max_iter=100,optimizer=irls,tolerance=1e-6'，指定优化参数。
verbose（可选）：缺省值为 FALSE，指定是否提供训练的详细输出结果。
表12-3 multinom 函数主输出表列说明
| 列名 | 数据类型 | 描述 |
|---|---|---|
| <…> | TEXT | 分组列，取决于grouping_col输入，可能是多个列。 |
| category | VARCHAR | 标识分类值的字符串。 |
| coef | FLOAT8[] | 回归系数向量。 |
| log_likelihood | FLOAT8 | 对数似然值。 |
| std_err | FLOAT8[] | 系数的标准方差向量。 |
| z_stat | FLOAT8[] | 系数的z统计量向量。 |
| p_values | FLOAT8[] | 系数的P值向量。 |
| num_iterations | INTEGER | 实际迭代次数。 |
| num_rows_processed | INTEGER | 实际处理的行数。 |
| num_missing_rows_skipped | INTEGER | 训练时跳过的行数。 |
表12-4 multinom 函数概要输出表列说明
| 列名 | 数据类型 | 描述 |
|---|---|---|
| method | VARCHAR | 'multinom',描述模型的字符串。 |
| source_table | VARCHAR | 源表名。 |
| model_table | VARCHAR | 模型表名。 |
| dependent_varname | VARCHAR | 因变量表达式。 |
| independent_varname | VARCHAR | 自变量表达式。 |
| ref_category | VARCHAR | 参考类别的字符串表示。 |
| link_func | VARCHAR | 连接函数参考，当前只实现了'logit'。 |
| grouping_col | VARCHAR | 分组列。 |
| optimizer_params | VARCHAR | 包含所有优化参数的字符串，形式是 'optimizer=…,max_iter=…,tolerance=…'。 |
| num_all_groups | INTEGER | 分组数。 |
| num_failed_groups | INTEGER | 失败分组数。 |
| total_rows_processed | BIGINT | 所有分组处理的总行数。 |
2. 预测函数
multinom_predict(
model_table,
predict_table_input,
output_table,
predict_type,
verbose,
id_column
)
参数说明：
model_table：训练函数生成的模型表名，是 multinom()函数的输出表。
predict_table_input：包含被预测数据的表名。表中必须有作为主键的 ID 列。
output_table：包含预测结果的输出表名。输出表的列根据 predict_type 参数而有所不同。当predict_type = response 时，输出表中包含两列：SERIAL 类型的id，表示主键，TEXT 类型的 category 列，包含预测的类别。predict_type = probability 时，除 id 列外，每个类 别输出一列，列名就是类别值，列值数据类型为 FLOAT8，表示预测为该类别的概率。
predict_type：'response'或'probability'。使用前者将输出预测最大概率的类别值，使用后者将输出每种类别的预测概率。
verbose：控制是否显示详细信息，缺省值为 FALSE。
id_column：输入表中的 ID 列名。
3. 函数示例
- (1)建立测试数据表并装载原始数据
drop table if exists t1;
create table t1 (id int, y int, x1 int, x2 int); insert into t1 values
(1, 1, 1, 15), (2, 1, 1, 15), (3, 2, 1, 14), (4, 2, 2, 16),
(5, 3, 2, 16), (6, 3, 2, 17), (7, 2, 2, 17), (8, 2, 1, 18),
(9, 1, 1, 14), (10, 3, 2, 18), (11, 1, 1, 17), (12, 1, 2, 17),
(13, 1, 1, 15), (14, 2, 1, 18), (15, 1, 2, 15), (16, 1, 2, 15),
(17, 3, 2, 17), (18, 1, 1, 15), (19, 1, 1, 15), (20, 2, 2, 16);
- (2)调用训练函数
drop table if exists t1_output, t1_output_summary;
select madlib.multinom('t1','t1_output','y','array[1, x1, x2]','1','logit');
- (3)查看回归结果
seaboxsql=# select * from t1_output;
-[ RECORD 1 ]------+---------------------------------------------------------------
category | 2
coef | {-14.82903037287406,0.7324519659176458,0.8355983495805379}
log_likelihood | -13.678884731228997
std_err | {8.211125202794266,1.1834311245650144,0.49823078159139483}
z_stats | {-1.8059681233246456,0.6189223442867181,1.6771311216692797}
p_values | {0.07092331889044116,0.5359675178284234,0.09351684425076973}
num_rows_processed | 20
num_rows_skipped | 0
num_iterations | 14
-[ RECORD 2 ]------+---------------------------------------------------------------
category | 3
coef | {-66.50086328782767,16.081183860575262,2.1124007986729048}
log_likelihood | -13.678884731228997
std_err | {755.0744384575338,377.3756486529358,1.1813345463192737}
z_stats | {-0.08807193026382358,0.042613199653920374,1.7881478242167619}
p_values | {0.9298195060699723,0.9660098732465803,0.07375216154524175}
num_rows_processed | 20
num_rows_skipped | 0
num_iterations | 14
- (4)使用模型进行预测
drop table if exists t1_prd;
select madlib.multinom_predict('t1_output', 't1', 't1_prd', 'probability');
select * from t1_prd order by id;
seaboxsql=# select madlib.multinom_predict('t1_output', 't1', 't1_prd', 'probability');
seaboxsql=#select * from t1_prd order by id;
multinom_predict
------------------
(1 row)
id | 1 | 2 | 3
----+----------------------+---------------------+------------------------
1 | 0.8267265676799954 | 0.1732734262744869 | 6.0455176888097536e-09
2 | 0.8267265676799954 | 0.1732734262744869 | 6.0455176888097536e-09
3 | 0.9166900562743273 | 0.083309942914917 | 8.107558889282188e-10
4 | 0.38637457408413023 | 0.3884849577871214 | 0.22514046812874838
5 | 0.38637457408413023 | 0.3884849577871214 | 0.22514046812874838
6 | 0.12290156351699044 | 0.2849829175880308 | 0.5921155188949788
7 | 0.12290156351699044 | 0.2849829175880308 | 0.5921155188949788
8 | 0.2800540552870704 | 0.7199447872009379 | 1.1575119917587083e-06
9 | 0.9166900562743273 | 0.083309942914917 | 8.107558889282188e-10
10 | 0.021653693196984226 | 0.11579482531911252 | 0.8625514814839031
11 | 0.47287815188419824 | 0.5271216117259263 | 2.3638987542105146e-07
12 | 0.12290156351699044 | 0.2849829175880308 | 0.5921155188949788
13 | 0.8267265676799954 | 0.1732734262744869 | 6.0455176888097536e-09
14 | 0.2800540552870704 | 0.7199447872009379 | 1.1575119917587083e-06
15 | 0.6638081652413356 | 0.2894093154837047 | 0.046782519274959584
16 | 0.6638081652413356 | 0.2894093154837047 | 0.046782519274959584
17 | 0.12290156351699044 | 0.2849829175880308 | 0.5921155188949788
18 | 0.8267265676799954 | 0.1732734262744869 | 6.0455176888097536e-09
19 | 0.8267265676799954 | 0.1732734262744869 | 6.0455176888097536e-09
20 | 0.38637457408413023 | 0.3884849577871214 | 0.22514046812874838
(20 rows)
- (5)返回预测类别值
drop table if exists t1_prd;
select madlib.multinom_predict('t1_output', 't1', 't1_prd', 'response');
select t0.id, t1.y realvalue, t0.category predict,
case when t1.y = t0.category::int4 then 'T' else 'F' end istrue from t1_prd t0, t1
where t0.id = t1.id order by id;
seaboxsql=# select t0.id, t1.y realvalue, t0.category predict,
seaboxsql-# case when t1.y = t0.category::int4 then 'T' else 'F' end istrue from t1_prd t0, t1
seaboxsql-# where t0.id = t1.id order by id;
id | realvalue | predict | istrue
----+-----------+---------+--------
1 | 1 | 1 | T
2 | 1 | 1 | T
3 | 2 | 1 | F
4 | 2 | 2 | T
5 | 3 | 2 | F
6 | 3 | 3 | T
7 | 2 | 3 | F
8 | 2 | 2 | T
9 | 1 | 1 | T
10 | 3 | 3 | T
11 | 1 | 2 | F
12 | 1 | 3 | F
13 | 1 | 1 | T
14 | 2 | 2 | T
15 | 1 | 1 | T
16 | 1 | 1 | T
17 | 3 | 3 | T
18 | 1 | 1 | T
19 | 1 | 1 | T
20 | 2 | 2 | T
(20 rows)
序数回归¶
1. 训练函数
ordinal(
source_table,
model_table,
dependent_varname,
independent_varname,
cat_order,
link_func,
grouping_col,
optim_params,
verbose
)
参数说明：
source_table：包含训练数据的表名。
model_table：包含输出模型的表名。主输出表列和概要输出表列如表 12-5、表 12-6 所示。
dependent_varname：因变量列名。
independent_varname：评估使用的自变量的表达式列表。此处不应包含截距，累积概率里包含了每个类别的截距。
cat_order：表示类别顺序的字符串，顺序由'<'字符指示。
link_function（可选）：缺省为'logit'。连接函数参数，当前支持 logit 和 probit。
grouping_col（可选）：缺省值为 NULL。和 SQL 中的“GROUP BY”类似，是一个将输入数据集分成离散组的表达式，每个组运行一个回归。此值为 NULL 时，将不使用分组， 并产生一个单一的结果模型。
optim_params（可选）：缺省值为'max_iter=100,optimizer=irls,tolerance=1e-6'，指定优化参数。
verbose（可选）：缺省值为 FALSE，指定是否提供训练的详细输出结果。
表12-5 ordinal 函数主输出表列说明
| 列名 | 数据类型 | 描述 |
|---|---|---|
| <…> | TEXT | 分组列，取决于grouping_col输入，可能是多个列。 |
| coef_threshold | FLOAT8[] | 线性预测中阈值系数向量。 阈值系数是每个特定级别的截距。 |
| std_err_threshold | FLOAT8[] | 阈值系数的标准差向量。 |
| z_stats_threshold | FLOAT8[] | 阈值系数z统计向量。 |
| p_values_threshold | FLOAT8[] | 阈值系数P值向量。 |
| log_likelihood | FLOAT8 | 对数似然比。 |
| coef_feature | FLOAT8[] | 线性预测中特征系数向量。 |
| std_err_feature | FLOAT8[] | 特征系数标准差向量。 |
| z_stats_feature | FLOAT8[] | 特征系数z统计向量。 |
| p_values_feature | FLOAT8[] | 特征系数P值向量。 |
| num_rows_processed | BIGINT | 实际处理的行数。 |
| num_missing_rows_skipped | BIGINT | 训练时因为缺失值或错误跳过的行数。 |
| num_iteration | INTEGER | 实际迭代次数。 |
表12-6 ordinal 函数概要输出表列说明
| 列名 | 数据类型 | 描述 |
|---|---|---|
| <…> | TEXT | 分组列，取决于grouping_col输入，可能是多个列。 |
| method | VARCHAR | 'ordinal',描述模型的字符串。 |
| source_table | VARCHAR | 源表名。 |
| model_table | VARCHAR | 模型表名。 |
| dependent_varname | VARCHAR | 因变量表达式。 |
| independent_varname | VARCHAR | 自变量表达式。 |
| cat_order | VARCHAR | 标识类别顺序的字符串，模式是使用python排序的类别。 |
| link_func | VARCHAR | 连接函数参数，当前实现了'logit'和'probit'。 |
| grouping_col | VARCHAR | 分组列。 |
| optimizer_params | VARCHAR | 包含所有优化参数的字符串，形式是 'optimizer=…,max_iter=…,tolerance=…'。 |
| num_all_groups | VARCHAR | 分组数。 |
| num_failed_groups | VARCHAR | 失败分组数。 |
| total_rows_processed | BIGINT | 所有分组处理的总行数。 |
| total_rows_skipped | BIGINT | 所有组由于缺少值或失败跳过的总行数。 |
2. 预测函数
ordinal_predict(
model_table,
predict_table_input,
output_table,
predict_type,
verbose
)
参数说明：
model_table：训练函数生成的模型表名，是 ordinal()函数的输出表。
predict_table_input：包含被预测数据的表名。表中必须有作为主键的 ID 列。
output_table：包含预测结果的输出表名。输出表的列根据 predict_type 参数而有所不同。当predict_type = response 时，输出表中包含两列：SERIAL 类型的id，表示主键，TEXT 类型的 category 列，包含预测的类别。predict_type = probability 时，除 id 列外，每个类 别输出一列，列名就是类别值，列值数据类型为 FLOAT8，表示预测为该类别的概率。
predict_type：'response'或'probability'。使用前者将输出预测最大概率的类别值，使用后者将输出每种类别的预测概率。
verbose：控制是否显示详细信息，缺省值为 FALSE。
3. 函数实例
- (1)建立测试数据表并装载原始数据
drop table if exists t1;
create table t1 (id serial, y int, x1 int, x2 int);
insert into t1 (y, x1, x2) values
(1, 1, 1), (1, 1, 1), (1, 1, 1), (1, 1, 1),
(1, 1, 1), (1, 1, 1), (1, 1, 1), (1, 1, 1),
(1, 1, 1), (1, 1, 1), (1, 1, 1), (1, 1, 1),
(1, 1, 1), (1, 1, 1), (1, 1, 1), (1, 1, 1),
(2, 1, 1), (2, 1, 1), (2, 1, 1), (2, 1, 1), (2, 1, 1),
(3, 1, 1), (3, 1, 1), (3, 1, 1), (3, 1, 1), (3, 1, 1), (3, 1, 1),
(1, 1, 0), (1, 1, 0), (1, 1, 0), (1, 1, 0), (1, 1, 0), (1, 1, 0),
(2, 1, 0), (2, 1, 0), (2, 1, 0), (2, 1, 0), (2, 1, 0), (2, 1, 0), (2, 1, 0),
(3, 1, 0), (3, 1, 0), (3, 1, 0), (3, 1, 0), (3, 1, 0), (3, 1, 0),
(3, 1, 0), (3, 1, 0), (3, 1, 0), (3, 1, 0), (3, 1, 0), (3, 1, 0),
(3, 1, 0), (3, 1, 0), (3, 1, 0), (3, 1, 0), (3, 1, 0), (3, 1, 0), (3, 1, 0),
(1, 0, 1), (1, 0, 1), (1, 0, 1), (1, 0, 1), (1, 0, 1),
(2, 0, 1), (2, 0, 1),
(3, 0, 1), (3, 0, 1), (3, 0, 1), (3, 0, 1), (3, 0, 1), (3, 0, 1), (3, 0, 1),
(1, 0, 0),
(3, 0, 0), (3, 0, 0), (3, 0, 0), (3, 0, 0), (3, 0, 0),
(3, 0, 0), (3, 0, 0), (3, 0, 0), (3, 0, 0), (3, 0, 0);
- (2)用 logit 连接函数训练
drop table if exists t1_logit;
drop table if exists t1_logit_summary;
select madlib.ordinal('t1','t1_logit','y','array[x1, x2]','1<2<3','logit');
- (3)查看回归结果
select * from t1_logit;
seaboxsql=# select * from t1_logit;
-[ RECORD 1 ]------+----------------------------------------------
coef_threshold | {-2.6671947518065817,-1.812801418864191}
std_err_threshold | {0.5997020426219571,0.5566137285465618}
z_stats_threshold | {-4.447533211901931,-3.256839215227058}
p_values_threshold | {8.686203224452921e-06,0.0011266022845629244}
log_likelihood | -75.01470654489133
coef_feature | {-1.3187515986463052,-1.7973033841289638}
std_err_feature | {0.5291914019068253,0.4728237477089269}
z_stats_feature | {-2.492012519278417,-3.801212170153929}
p_values_feature | {0.012702156013472864,0.00014398993692462655}
num_rows_processed | 84
num_rows_skipped | 0
num_iterations | 6
- (4)使用模型进行预测源表数据
drop table if exists t1_prd_logit;
select
madlib.ordinal_predict('t1_logit', 't1', 't1_prd_logit', 'response');
select
istrue,
count(*)
from
(
select
t0.id,
t1.y realvalue,
t0.category predict,
case
when t1.y = t0.category :: int then 'T'
else 'F'
end istrue
from
t1_prd_logit t0,
t1
where
t0.id = t1.id
) t
group by
istrue;
seaboxsql=# select
madlib.ordinal_predict('t1_logit', 't1', 't1_prd_logit', 'response');
select
istrue,
count(*)
from
(
select
t0.id,
t1.y realvalue,
t0.category predict,
case
when t1.y = t0.category :: int then 'T'
else 'F'
end istrue
from
t1_prd_logit t0,
t1
where
t0.id = t1.id
) t
group by
istrue
istrue | count
--------+-------
F | 32
T | 52
(2 rows)
弹性网络回归¶
1. 训练函数
elastic_net_train(
tbl_source,
tbl_result,
col_dep_var,
col_ind_var,
regress_family,
alpha,
lambda_value,
standardize,
grouping_col,
optimizer,
optimizer_params,
excluded,
max_iter,
tolerance
)
参数说明：
tbl_source：包含训练数据的源表名。
tbl_result：包含模型的输出表名。
col_dep_var：因变量表达式。col_dep_var 和 col_ind_var 可以是有效的 PostgreSQL 表达式。例如，col_dep_var = 'log(y+1)'、 col_ind_var = 'array[exp(x[1]), x[2], 1/(1+x[3])]'。在二项回归情况下，可以使用布尔表达式，如 col_dep_var = 'y < 0'。
col_ind_var：自变量表达式。使用‘*’指定 tbl_source 中除以下描述中的列以外的所有列。如果 col_dep_var 是列名，它自动排除在自变量之外。如果 col_dep_var 是一个PostgreSQL 表达式，表达式中用到的列名，只有显式出现在 excluded 参数中才被排除。因此，比较好的做法是将因变量表达式中所含的列名都添加到 excluded 参数的字符串中。
regress_family：指定回归类型，有效值为‘gaussian’（‘linear’）线性回归或‘binomial’（‘logistic’）二项回归。
alpha：弹性网络控制参数，取值范围是[0, 1]。1 表示 L1 正则化，0 表示 L2 正则化。该参数是超参，其值并不是自动从模型中学习得到而是手动设置。
lambda_value：指定正则化参数，必须是正数。该参数也是超参数。
standardize（可选）：指定是否规范化数据，缺省值为 TRUE。设置为 TRUE 通常能产生更好的结果和更快的收敛速度。
grouping_col（可选）：缺省值为 NULL。可以指定单列或逗号分隔的多列，将输入数据按列划分为离散组，每组执行一个回归。缺省不使用分组，全部数据生成单一模型。该参数当前不支持表达式。
optimizer（可选）：优化器名称，可指定为‘fista’或‘igd’，缺省为‘fista’。
optimizer_params（可选）：指定逗号分隔的优化参数，缺省为 NULL。这些参数根据优化器参数的值而有所不同，后面将详细描述。
excluded（可选）：逗号分隔的列名，缺省值为 NULL。当 col_ind_var 入参为‘*’时， 可从特征列中排除此参数中的列。
max_iter（可选）：缺省值为 1000，指定允许的最大迭代次数。
tolerance：缺省值为 1e-6，指定停止迭代的条件。无论是‘fista’还是‘igd’优化器，如果两次连续迭代的对数似然值之差小于此参数值，或者迭代次数超过max_iter 参数的限制， 计算结束。
2. 预测函数
- (1)元组预测
elastic_net_<family>_predict(
coefficients, intercept, ind_var
)
参数说明：
coefficients：FLOAT8[]类型，拟合系数，通常指定为模型表的 coef_all 或
coef_nonzero 列。
intercept：FLOAT8[]类型，模型截距。
ind_var：FLOAT8[]类型，系数对应的自变量。对于 coef_all，使用模型结果表中的features 列。
对于 coef_nonzero 使用模型结果表中的 features_selected 中的列。
- (2)表预测
elastic_net_predict( tbl_model,
tbl_new_sourcedata, col_id,
tbl_predict
)
参数说明：
tbl_model：TEXT 类型，包含模型的训练函数输出表名。
tbl_new_sourcedata：TEXT 类型，包含测试数据的表名。
col_id：TEXT 类型，行 ID 列。
tbl_predict：TEXT 类型，存储预测结果的表名。
3. 函数示例
- (1)查看帮助信息
select madlib.elastic_net_train();
- (2)创建测试表并生成数据
drop table if exists houses;
create table houses ( id int,
tax int, bedroom int, bath float, price int,
size int, lot int,
zipcode int
);
insert into houses (id, tax, bedroom, bath, price, size, lot, zipcode) values
(1 , 590 , 2 , 1 , 50000 , 770 , 22100 , 94301),
(2 , 1050 , 3 , 2 , 85000 , 1410 , 12000 , 94301),
(3 , 20 , 3 , 1 , 22500 , 1060 , 3500 , 94301),
(4 , 870 , 2 , 2 , 90000 , 1300 , 17500 , 94301),
(5 , 1320 , 3 , 2 , 133000 , 1500 , 30000 , 94301),
(6 , 1350 , 2 , 1 , 90500 , 820 , 25700 , 94301),
(7 , 2790 , 3 , 2.5 , 260000 , 2130 , 25000 , 94301),
(8 , 680 , 2 , 1 , 142500 , 1170 , 22000 , 94301),
(9 , 1840 , 3 , 2 , 160000 , 1500 , 19000 , 94301),
(10 , 3680 , 4 , 2 , 240000 , 2790 , 20000 , 94301),
(11 , 1660 , 3 , 1 , 87000 , 1030 , 17500 , 94301),
(12 , 1620 , 3 , 2 , 118600 , 1250 , 20000 , 94301),
(13 , 3100 , 3 , 2 , 140000 , 1760 , 38000 , 94301),
(14 , 2070 , 2 , 3 , 148000 , 1550 , 14000 , 94301),
(15 , 650 , 3 , 1.5 , 65000 , 1450 , 12000 , 94301),
(16 , 770 , 2 , 2 , 91000 , 1300 , 17500 , 76010),
(17 , 1220 , 3 , 2 , 132300 , 1500 , 30000 , 76010),
(18 , 1150 , 2 , 1 , 91100 , 820 , 25700 , 76010),
(19 , 2690 , 3 , 2.5 , 260011 , 2130 , 25000 , 76010),
(20 , 780 , 2 , 1 , 141800 , 1170 , 22000 , 76010),
(21 , 1910 , 3 , 2 , 160900 , 1500 , 19000 , 76010),
(22 , 3600 , 4 , 2 , 239000 , 2790 , 20000 , 76010),
(23 , 1600 , 3 , 1 , 81010 , 1030 , 17500 , 76010),
(24 , 1590 , 3 , 2 , 117910 , 1250 , 20000 , 76010),
(25 , 3200 , 3 , 2 , 141100 , 1760 , 38000 , 76010),
(26 , 2270 , 2 , 3 , 148011 , 1550 , 14000 , 76010),
(27 , 750 ,3 , 1.5 , 66000 , 1450 , 12000 , 76010);
- (3) 训练模型
drop table if exists houses_en, houses_en_summary;
select madlib.elastic_net_train( 'houses',
'houses_en', 'price',
'array[tax, bath, size]', 'gaussian',
0.5,
1,
true, null, 'fista',
'',
null, 1000,
1e-6);
- (4)查看结果
select * from houses_en;
seaboxsql=# select * from houses_en;
-[ RECORD 1 ]-----+-----------------------------------------------------------
family | gaussian
features | {tax,bath,size}
features_selected | {tax,bath,size}
coef_nonzero | {19.130773041833475,14331.306868334272,40.447814959158585}
coef_all | {19.130773041833475,14331.306868334272,40.447814959158585}
intercept | 12944.513189185796
log_likelihood | -741004029.7738273
standardize | t
iteration_run | 1000
- (5)使用预测函数评估残差
select
id,
price,
predict,
price - predict as residual,
round(abs((price - predict) / price * 100) :: numeric, 4) as residual_pct
from
(
select
houses.*,
madlib.elastic_net_gaussian_predict(m.coef_all, m.intercept, array [tax,bath,size]) as predict
from
houses,
houses_en m
) s
order by
id;
seaboxsql=# select
id,
price,
predict,
price - predict as residual,
round(abs((price - predict) / price * 100) :: numeric, 4) as residual_pct
from
(
select
houses.*,
madlib.elastic_net_gaussian_predict(m.coef_all, m.intercept, array [tax,bath,size]) as predict
from
houses,
houses_en m
) s
order by
id;
id | price | predict | residual | residual_pct
----+--------+--------------------+---------------------+--------------
1 | 50000 | 69707.79367075392 | -19707.79367075392 | 39.4156
2 | 85000 | 118725.8577121931 | -33725.8577121931 | 39.6775
3 | 22500 | 70533.11937506484 | -48033.11937506484 | 213.4805
4 | 90000 | 110833.05891915562 | -20833.05891915562 | 23.1478
5 | 133000 | 127531.46977981241 | 5468.530220187589 | 4.1117
6 | 90500 | 86269.5719305053 | 4230.428069494694 | 4.6745
7 | 260000 | 188301.48300974467 | 71698.51699025533 | 27.5764
8 | 142500 | 87608.68922818237 | 54891.31077181763 | 38.5202
9 | 160000 | 137479.4717615658 | 22520.5282384342 | 14.0753
10 | 240000 | 224857.77545585396 | 15142.22454414604 | 6.3093
11 | 87000 | 100694.15271489698 | -13694.152714896976 | 15.7404
12 | 118600 | 123158.7479525728 | -4558.747952572798 | 3.8438
13 | 140000 | 172100.6776836572 | -32100.67768365721 | 22.9291
14 | 148000 | 158233.2471774797 | -10233.247177479701 | 6.9144
15 | 65000 | 105525.80765965892 | -40525.80765965892 | 62.3474
16 | 91000 | 108919.98161497226 | -17919.98161497226 | 19.6923
17 | 132300 | 125618.39247562905 | 6681.607524370949 | 5.0503
18 | 91100 | 82443.41732213862 | 8656.582677861385 | 9.5023
19 | 260011 | 186388.4057055613 | 73622.59429443869 | 28.3152
20 | 141800 | 89521.76653236573 | 52278.23346763427 | 36.8676
21 | 160900 | 138818.62587449414 | 22081.374125505856 | 13.7237
22 | 239000 | 223327.31361250728 | 15672.686387492722 | 6.5576
23 | 81010 | 99546.30633238697 | -18536.306332386972 | 22.8815
24 | 117910 | 122584.82476131778 | -4674.824761317781 | 3.9647
25 | 141100 | 174013.75498784054 | -32913.75498784054 | 23.3265
26 | 148011 | 162059.4017858464 | -14048.401785846392 | 9.4915
27 | 66000 | 107438.88496384225 | -41438.884963842254 | 62.7862
(27 rows)
Cox 比例风险回归¶
1. 训练函数
coxph_train( source_table,
output_table, dependent_variable, independent_variable, right_censoring_status,
strata, optimizer_params
)
参数说明：
source_table：包含训练数据的源表名。
output_table：保存模型的输出表名，主输出表列和概要输出表列分别如表 12-7、表 12-8 所示。
dependent_variable：因变量名称，指死亡时间，不需要对数据进行预排序。
independent_variable：自变量名称数组。
right_censoring_status（可选）：缺省值为 TRUE。表示右删失状态的字符串，如果无删失则为TRUE，否则为FALSE。该参数可以包含是右删失状态的列名，或者是一个可以对每个观察值进行评估的布尔表达式，如‘column_name < 10’。
strata（可选）：缺省值为 NULL，不做任何分层，可以是逗号分隔的列名。
optimizer_params（可选）：缺省值为 NULL，此时使用缺省的优化参数：max_iter=100, optimizer=newton, tolerance=1e-8, array_agg_size=10000000, sample_size=1000000。应该是一个逗号分隔的‘key=value’字符串，参数含义如下：
max_iter：最大迭代数。如果迭代次数达到此数值则停止计算，这通常意味着无法收敛。
optimizer：优化方法，当前仅支持‘newton’。
tolerance：停止条件。当连续两次迭代的对数似然值之差小于此参数，计算已经收敛并停止。
array_agg_size：为了加速计算，将原始数据表切分成多个数据片，每片数据聚合成一个大行。计算处理时，整个大行被导入内存以提高运算速度。此参数控制一个大行中包含多少数据，参数值越大速度越快，但由于 PostgreSQL 数据库的限制，一个大行的大小不能超过 1G。
sample_size：为了将数据切分成大致相等的片，先进行数据采样，然后利用采样数据找出断点。该参数值越大，产生的断点越精确。
表12-7 coxph_train 函数主输出表列说明
| 列名 | 数据类型 | 描述 |
|---|---|---|
| coef | FLOAT8[] | 回归系数向量。 |
| loglikelihood | FLOAT8 | 极大似然估计的对数似然值。 |
| std_err | FLOAT8[] | 回归系数标准差向量。 |
| stats | FLOAT8[] | 回归系数统计向量。 |
| p_values | FLOAT8[] | 回归系数P值向量。 |
| hessian | FLOAT8[] | hessian矩阵。 |
| num_iterations | INTEGER | 优化器执行的迭代次数。 |
表12-8 coxph_train 函数概要输出表列说明
| 列名 | 数据类型 | 描述 |
|---|---|---|
| method | VARCHAR | 'coxph',描述模型的字符串。 |
| source_table | VARCHAR | 源表名。 |
| out_table | VARCHAR | 模型表名。 |
| dependent_varname | VARCHAR | 因变量表达式。 |
| independent_varname | VARCHAR | 自变量表达式。 |
| right_censoring_status | VARCHAR | 右删失状态。 |
| strata | VARCHAR | 分层列。 |
| num_processed | INTEGER | 处理行数。 |
| num_missing_rows_skipped | INTEGER | 跳过行数。 |
2. 风险比例假设校验函数
cox_zph(cox_model_table, output_table)
参数说明：
cox_model_table：TEXT 类型，包含 Cox 模型的表名，应该是 coxph_train()函数的输出
表。
output_table：TEXT 类型，保存测试结果的表名。
3. 预测函数
coxph_predict(model_table,
source_table, id_col_name, output_table, pred_type, reference)
参数说明：
model_table：包含 cox 模型的表名。
source_table：包含预测数据的表名。
id_col_name：id 列名。
output_table：存储预测结果的输出表名，输出表具有以下列。
id：TEXT 类型，id 列。
predicted_result：FLOAT8 类型，基于预测类型参数值的预测结果。
pred_type（可选）：预测类型。缺省为‘linear_predictors’，可以是‘linear_predictors’、‘risk’或‘terms’。
linear_predictors：计算自变量和系数的点积，即预后指数。
risk：预后指数预测结果的指数值。
terms：每个协变量与它们相应的系数值的乘积，不求和。
reference（可选）：用于中心预测。可以为‘strata’、‘overall’，缺省值为‘strata’。
4. 函数示例
- (1)查看帮助信息
select madlib.coxph_train();
select madlib.coxph_train('usage');
select madlib.coxph_train('example');
- (2) 建立测试数据表并装载原始数据
drop table if exists sample_data;
create table sample_data (
id integer not null, grp double precision,
wbc double precision, timedeath integer, status boolean
);
insert into sample_data values
( 1,0,1.45,5,'t'),
( 2,0,1.47,4,'t'),
( 3,0, 2.2,2,'t'),
( 4,0,1.78,5,'t'),
( 5,0,2.57,3,'t'),
( 6,0,2.32,2,'t'),
( 7,0,2.01,0,'t'),
( 8,0,2.05,9,'t'),
( 9,0,2.16,7,'t'),
(10,0, 3.6,6,'t'),
(11,1, 2.3,5,'t'),
(12,0,2.88,3,'t'),
(13,1, 1.5,2,'t'),
(14,0, 2.6,1,'t'),
(15,0, 2.7,0,'t'),
(16,0, 2.8,9,'t'),
(17,1,2.32,8,'t'),
(18,0,4.43,7,'t'),
(19,0,2.31,6,'t'),
(20,1,3.49,5,'t'),
(21,1,2.42,4,'t'),
(22,1,4.01,3,'t'),
(23,1,4.91,2,'t'),
(24,1,5,1,'t');
- (3)训练模型
drop table if exists sample_cox, sample_cox_summary;
select madlib.coxph_train( 'sample_data','sample_cox','timedeath','array[grp,wbc]','status');
- (4)查看回归结果
select * from sample_cox;
seaboxsql=# select * from sample_cox;
-[ RECORD 1 ]--+--------------------------------------------------------------------------------
coef | {0.3009649489258887,0.09107213451002935}
loglikelihood | -56.82564220918377
std_err | {0.4545114067651304,0.2338674652282597}
z_stats | {0.6621724877444338,0.38941771751423815}
p_values | {0.5078606840899934,0.6969671677066437}
hessian | {{4.942114466571948,1.3757117559795402},{1.3757117559795402,18.66649408748197}}
num_iterations | 4
- (5)检验所得模型的 PHA
drop table if exists sample_zph_output, sample_zph_output_residual;
select madlib.cox_zph( 'sample_cox',
'sample_zph_output'
);
select * from sample_zph_output;
seaboxsql=# select * from sample_zph_output;
-[ RECORD 1 ]------------------------------------------
covariate | array[grp,wbc]
rho | {0.13979462052319086,-0.03080155670695556}
chi_square | {0.40616689410371076,0.025104874481573614}
p_value | {0.5239214782780656,0.8741060163453928}
- (6)使用模型进行预测
a.条目预测
drop table if exists sample_pred_terms;
select madlib.coxph_predict('sample_cox',
'sample_data', 'id',
'sample_pred_terms', 'terms');
select
id,
terms,
sum(c) linear_predictors
from
(
select
id,
terms,
unnest(terms) c
from
sample_pred_terms
) t
group by
id,
terms
order by
linear_predictors;
seaboxsql=# select
id,
terms,
sum(c) linear_predictors
from
(
select
id,
terms,
unnest(terms) c
from
sample_pred_terms
) t
group by
id,
terms
order by
linear_predictors;
id | terms | linear_predictors
----+----------------------------------------------+-----------------------
1 | {-0.1003216496419629,-0.1156616108277373} | -0.21598326046970018
2 | {-0.1003216496419629,-0.11384016813753671} | -0.2141618177794996
4 | {-0.1003216496419629,-0.0856078064394276} | -0.1859294560813905
7 | {-0.1003216496419629,-0.06466121550212088} | -0.16498286514408378
8 | {-0.1003216496419629,-0.0610183301217197} | -0.16133997976368258
9 | {-0.1003216496419629,-0.05100039532561644} | -0.15132204496757934
3 | {-0.1003216496419629,-0.047357509945215266} | -0.14767915958717817
19 | {-0.1003216496419629,-0.03733957514911205} | -0.13766122479107495
6 | {-0.1003216496419629,-0.03642885380401177} | -0.13675050344597467
5 | {-0.1003216496419629,-0.013660820176504435} | -0.11398246981846734
14 | {-0.1003216496419629,-0.010928656141203532} | -0.11125030578316643
15 | {-0.1003216496419629,-0.0018214426902005887} | -0.10214309233216348
16 | {-0.1003216496419629,0.007285770760802314} | -0.09303587888116058
12 | {-0.1003216496419629,0.01457154152160467} | -0.08575010812035823
10 | {-0.1003216496419629,0.08014347836882582} | -0.020178171273137077
18 | {-0.1003216496419629,0.15573335001215013} | 0.055411700370187236
13 | {0.20064329928392582,-0.11110800410223583} | 0.08953529518169
11 | {0.20064329928392582,-0.03825029649421236} | 0.16239300278971347
17 | {0.20064329928392582,-0.03642885380401177} | 0.16421444547991404
21 | {0.20064329928392582,-0.027321640353008828} | 0.173321658930917
20 | {0.20064329928392582,0.0701255435727226} | 0.27076884285664843
22 | {0.20064329928392582,0.11748305351793782} | 0.31812635280186363
23 | {0.20064329928392582,0.19944797457696428} | 0.4000912738608901
24 | {0.20064329928392582,0.2076444666828669} | 0.4082877659667927
(24 rows)
b.预后指数预测
drop table if exists sample_pred;
select madlib.coxph_predict('sample_cox',
'sample_data', 'id', 'sample_pred');
select id, linear_predictors, exp(linear_predictors) risk
from sample_pred order by linear_predictors;
seaboxsql=# select id, linear_predictors, exp(linear_predictors) risk
from sample_pred order by linear_predictors;
id | linear_predictors | risk
----+----------------------+--------------------
1 | -0.2159832604697002 | 0.8057487896168679
2 | -0.21416181777949964 | 0.8072177522693705
4 | -0.18592945608139053 | 0.8303321678007944
7 | -0.16498286514408378 | 0.8479082327488405
8 | -0.1613399797636826 | 0.8510026982232409
9 | -0.15132204496757937 | 0.8595708336188569
3 | -0.14767915958717817 | 0.8627078620904545
19 | -0.13766122479107495 | 0.8713938483801412
6 | -0.1367505034459747 | 0.8721878068404318
5 | -0.11398246981846735 | 0.8922735974634484
14 | -0.11125030578316644 | 0.8947147686175474
15 | -0.10214309233216351 | 0.9029003443088277
16 | -0.0930358788811606 | 0.9111608082793091
12 | -0.08575010812035827 | 0.917823559219748
10 | -0.02017817127313709 | 0.9800240456184857
18 | 0.05541170037018722 | 1.0569756823902985
13 | 0.08953529518168996 | 1.0936659337689822
11 | 0.16239300278971341 | 1.1763224484945571
17 | 0.16421444547991398 | 1.178467004920091
21 | 0.17332165893091694 | 1.1892485759936913
20 | 0.2707688428566483 | 1.3109719948155605
22 | 0.3181263528018636 | 1.3745499284904237
23 | 0.4000912738608901 | 1.4919608684555188
24 | 0.40828776596679267 | 1.5042399679622616
(24 rows)
c.风险预测
drop table if exists sample_pred_risk;
select madlib.coxph_predict('sample_cox',
'sample_data', 'id',
'sample_pred_risk', 'risk');
select * from sample_pred_risk order by risk;
seaboxsql=# select * from sample_pred_risk order by risk;
id | risk
----+--------------------
1 | 0.8057487896168679
2 | 0.8072177522693705
4 | 0.8303321678007944
7 | 0.8479082327488405
8 | 0.8510026982232409
9 | 0.8595708336188569
3 | 0.8627078620904545
19 | 0.8713938483801412
6 | 0.8721878068404318
5 | 0.8922735974634484
14 | 0.8947147686175474
15 | 0.9029003443088277
16 | 0.9111608082793091
12 | 0.917823559219748
10 | 0.9800240456184857
18 | 1.0569756823902985
13 | 1.0936659337689822
11 | 1.1763224484945571
17 | 1.178467004920091
21 | 1.1892485759936913
20 | 1.3109719948155605
22 | 1.3745499284904237
23 | 1.4919608684555188
24 | 1.5042399679622616
(24 rows)
稳健方差¶
1. 稳健线性回归训练函数
robust_variance_linregr(
source_table,
out_table,
dependent_varname,
independent_varname,
grouping_cols
)
参数说明：
source_table：VARCHAR 类型，包含训练数据的表的名称。
out_table：VARCHAR 类型，包含输出模型生成表的名称。输出表包含以下列：
coef：DOUBLE PRECISION[]类型，回归系数向量。
std_err：DOUBLE PRECISION[]类型，系数标准误向量。
t_stats：DOUBLE PRECISION[]类型，系数的 t 统计向量。
p_values：DOUBLE PRECISION[]类型，系数的 p 值向量。
2. 稳健逻辑回归训练函数
robust_variance_logregr(
source_table,
out_table,
dependent_varname,
independent_varname,
grouping_cols,
max_iter,
optimizer,
tolerance,
verbose_mode
)
参数说明：
source_table：VARCHAR 类型，包含训练数据的表的名称。
out_table：VARCHAR 类型，包含输出模型生成表的名称。输出表包含以下列：
coef：DOUBLE PRECISION[]类型，回归系数向量。
std_err：DOUBLE PRECISION[]类型，系数标准误向量。
z_stats：DOUBLE PRECISION[]类型，系数的 z 统计向量。
p_values：DOUBLE PRECISION[]类型，系数的 p 值向量。
3. 稳健多类逻辑回归函数
robust_variance_mlogregr(
source_table,
out_table,
dependent_varname,
independent_varname,
ref_category,
grouping_cols,
optimizer_params,
verbose_mode
)
参数说明：
source_table：VARCHAR 类型，包含培训数据的表格的名称。
out_table：VARCHAR 类型，存储回归模型的表的名称，具有以下列：
category：类别。
ref_category：用于建模的参考类别。
coef：回归系数向量。
std_err：系数标准误向量。
z_stats：系数的 z 统计向量。
p_values：系数的 p 值向量。
4. Cox 比例风险稳健方差函数
robust_variance_coxph(model_table, output_table)
参数说明：
model_table：TEXT 类型，模型表的名称，与 coxph_train()函数的“output_table”参数
同。
output_table：TEXT 类型，保存输出的表的名称，有以下几列：
coef：FLOAT8[]类型，回归系数向量。
loglikelihood：FLOAT8 类型，极大似然估计（Maximum Likelihood Estimate，MLE） 的对数似然值。
std_err：FLOAT8[]类型，系数标准误向量。
robust_se：FLOAT8[]类型，系数的稳健标准误向量。
robust_z：FLOAT8[]类型，系数的稳健 z 统计向量。
robust_p：FLOAT8[]类型，系数的稳健 p 值向量。
hessian：FLOAT8[][]类型，黑塞矩阵（Hessian Matrix）。
5. 函数示例
- (1)查看帮助信息
SELECT madlib.robust_variance_logregr();
- (2)创建训练数据表
DROP TABLE IF EXISTS patients;
CREATE TABLE patients (id INTEGER NOT NULL, second_attack INTEGER, treatment INTEGER, trait_anxiety INTEGER);
insert into patients values ( 1,1,1,70),
( 3,1,1,50),
( 5,1,0,40),
( 7,1,0,75),
( 9,1,0,70),
(11,0,1,65),
(13,0,1,45),
(15,0,1,40),
(17,0,0,55),
(19,0,0,50),
( 2,1,1,80),
( 4,1,0,60),
( 6,1,0,65),
( 8,1,0,80),
(10,1,0,60),
(12,0,1,50),
(14,0,1,35),
(16,0,1,50),
(18,0,0,45),
(20,0,0,60);
- (3)运行逻辑回归训练函数并计算回归的文件逻辑方差
DROP TABLE IF EXISTS patients_logregr;
SELECT madlib.robust_variance_logregr( 'patients',
'patients_logregr', 'second_attack',
'ARRAY[1, treatment, trait_anxiety]'
);
- (4)查看回归结果
SELECT * FROM patients_logregr;
seaboxsql=# SELECT * FROM patients_logregr;
-[ RECORD 1 ]----------------------------------------------------------
coef | {-6.363469941781797,-1.0241060523932684,0.119044916668605}
std_err | {3.4587206233364483,1.1716192578233986,0.05343288641850151}
z_stats | {-1.8398334629419382,-0.8740945879430352,2.2279334815681016}
p_values | {0.0657926909738862,0.3820667445855415,0.02588495107573297}
聚类方差¶
1. 聚类方差线性回归训练函数
clustered_variance_linregr (
source_table,
out_table,
dependent_varname,
independent_varname,
clustervar,
grouping_cols ）
参数说明：
source_table：TEXT 类型，包含训练数据的表的名称。
out_table：VARCHAR 类型，包含输出模型的生成表的名称。输出表包含以下列：
coef：DOUBLE PRECISION[]类型，回归系数向量。
std_err：DOUBLE PRECISION[]类型，系数标准误向量。
t_stats：DOUBLE PRECISION[]类型，系数的 t 统计向量。
p_values：DOUBLE PRECISION[]类型，系数的 p 值向量。
2. 聚类方差逻辑回归训练函数
clustered_variance_logregr(
source_table,
out_table,
dependent_varname,
independent_varname,
clustervar,
grouping_cols,
max_iter,
optimizer,
tolerance,
verbose_mode
)
参数说明：
source_table：TEXT 类型，包含训练数据的表的名称。
out_table：VARCHAR 类型，包含输出模型的生成表的名称。输出表包含以下列：
coef：回归系数向量。
std_err：系数标准误向量。
z_stats：DOUBLE PRECISION[]类型，系数的 z 统计向量。
p_values：DOUBLE PRECISION[]类型，系数的 p 值向量。
3. 聚类方差多类逻辑回归训练函数
clustered_variance_mlogregr(
source_table,
out_table,
dependent_varname,
independent_varname,
cluster_varname,
ref_category,
grouping_cols,
optimizer_params,
verbose_mode
)
参数说明：
source_table：TEXT 类型，包含输入数据的表的名称。
out_table：TEXT 类型，存储回归模型的表的名称。输出表包含以下列：
category：类别。
ref_category：用于建模的参考类别。
coef：回归系数向量。
std_err：系数标准误向量。
z_stats：系数 z 统计向量。
p_values：系数 p 值向量。
4. Cox 比例风险模型聚类方差
clustered_variance_coxph(model_table,output_table, clustervar)
参数说明：
model_table：TEXT 类型。模型表的名称，与 coxph_train()函数的‘output_table’参数
完全相同。
output_table：TEXT 类型。保存输出表的名称，有以下几列：
coef：FLOAT8[]类型，系数向量。
loglikelihood：FLOAT8 类型，极大似然估计（Maximum Likelihood Estimate，MLE） 的对数似然值。
std_err：FLOAT8[]类型，系数标准误向量。
clustervar：TEXT 类型，用作聚类变量列的逗号分隔列表。
clustered_se：FLOAT8[]类型，系数的稳健标准误向量。
clustered_z：FLOAT8[]类型，系数的稳健 z 统计向量。
clustered_p：FLOAT8[]类型，系数的稳健 p 值向量。
hessian：FLOAT8[][]类型，黑塞矩阵（Hessian Matrix）。
5. 函数示例
- (1)查看帮助信息
SELECT madlib.clustered_variance_linregr();
- (2)运行线性回归函数并查看结果
CREATE TABLE abalone (
id INT,
sex TEXT,
length FLOAT,
diameter FLOAT,
height FLOAT,
rings INT
);
INSERT INTO abalone VALUES
(1,'M',0.455,0.365,0.095,15),
(2,'M',0.35,0.265,0.09,7),
(3,'F',0.53,0.42,0.135,9),
(4,'M',0.44,0.365,0.125,10),
(5,'I',0.33,0.255,0.08,7),
(6,'I',0.425,0.3,0.095,8),
(7,'F',0.53,0.415,0.15,20),
(8,'F',0.545,0.425,0.125,16),
(9,'M',0.475,0.37,0.125,9),
(10,'F',0.55,0.44,0.15,19),
(11,'F',0.525,0.38,0.14,14),
(12,'M',0.43,0.35,0.11,10),
(13,'M',0.49,0.38,0.135,11),
(14,'F',0.535,0.405,0.145,10),
(15,'F',0.47,0.355,0.1,10),
(16,'M',0.5,0.4,0.13,12),
(17,'I',0.355,0.28,0.085,7),
(18,'F',0.44,0.34,0.1,10),
(19,'M',0.365,0.295,0.08,7),
(20,'M',0.45,0.32,0.1,9);
DROP TABLE IF EXISTS out_table,out_table_summary;
SELECT madlib.clustered_variance_linregr( 'abalone','out_table', 'rings','ARRAY[1, diameter, length, height]', 'sex',NULL);
SELECT * FROM out_table;
seaboxsql=# select * from out_table;
-[ RECORD 1 ]---------------------------------------------------------------------------------
coef | {-8.031648677301348,50.83737511631716,2.9867272604624304,-3.93522774788471}
std_err | {2.004844605744247,5.8480079843889,20.351095847882743,93.01213125398951}
t_stats | {-4.006120301937219,8.693109731044514,0.14676002131714,-0.04230875795264522}
p_values | {0.001018831486856997,1.8567211457209964e-07,0.8851547287742467,0.9667760517958583}
- (3)查看聚类方差逻辑回归函数的帮助信息
SELECT madlib.clustered_variance_logregr();
- (4)运行逻辑回归函数并查看结果
DROP TABLE IF EXISTS out_table;
DROP TABLE IF EXISTS out_table_summary;
SELECT madlib.clustered_variance_logregr( 'abalone',
'out_table',
'rings < 10',
'ARRAY[1, diameter, length, height]', 'sex'
);
SELECT * FROM out_table;
seaboxsql=# SELECT * FROM out_table;
-[ RECORD 1 ]-----------------------------------------------------------------------------
coef | {12.80056202976047,-58.257924141243734,10.993327307178788,20.303730480805484}
std_err | {6.648386172962767,69.48041917775092,37.53082735337523,14.301017774062178}
z_stats | {1.925363794572731,-0.8384797448069965,0.2929146006740012,1.4197402451754486}
p_values | {0.054183861745972936,0.40176131894853767,0.769587428373138,0.1556833169707266}
- (5)运行多类逻辑回归并查看结果
DROP TABLE IF EXISTS out_table;
DROP TABLE IF EXISTS out_table_summary;
SELECT madlib.clustered_variance_mlogregr( 'abalone',
'out_table',
'CASE WHEN rings < 10 THEN 1 ELSE 0 END',
'ARRAY[1, diameter, length, height]', 'sex',
0
);
seaboxsql=# SELECT * FROM out_table;
-[ RECORD 1 ]+---------------------------------------------------------------------------------
category | 1
ref_category | 0
coef | {12.800562029760618,-58.25792414124712,10.993327307181618,20.303730480803125}
std_err | {6.648386172964194,69.48041917775926,37.53082735337576,14.301017774071196}
z_stats | {1.9253637945723399,-0.8384797448069446,0.2929146006740725,1.4197402451743883}
p_values | {0.05418386174602185,0.40176131894856687,0.7695874283730835,0.15568331697103535}
时间序列分析¶
ARIMA¶
- 训练函数
arima_train(
input_table,
output_table,
timestamp_column,
timeseries_column,
grouping_columns,
include_mean,
non_seasonal_orders,
optimizer_params
)
参数说明：
input_table：包含时间序列数据的表的名称。
output_table：用于存储 ARIMA 模型的表的名称。会创建三个表，名称基于训练函数中
output_table 参数的值。三个输出表列分别如表 12-9、表 12-10、表 12-11 所示。
timestamp_column：包含时间戳（或索引）数据的列的名称。可以一个序列索引INTEGER）或日期/时间值（TIMESTAMP）。
timeseries_column：包含时间序列数据的列的名称。这些数据目前仅限于 DOUBLE PRECISION 类型。
grouping_columns（可选）：缺省值为 NULL，当前未实现，任何非 NULL 值都会被忽略。逗号分隔的列名，与 SQL 中的 GROUP BY 子句类似，用于将输入数据集划分为离散组， 每组训练一个 ARIMA 模型。当此值为空时，不使用分组，并生成单个结果模型。
include_mean（可选）：缺省值为 FALSE。如果此变量为 True，则数据序列的平均值将添加到 ARIMA 模型中。
non_seasonal_orders（可选）：缺省值为‘ARRAY[1,1,1]’。ARIMA 模型中按[p, d, q] 顺序的值。其中参数 p、d 和 q 是非负整数，分别表示模型的自回归、差分和移动平均部分的参数值。
optimizer_params（可选）：name = value’形式的优化器特定参数的逗号分隔值列表， 参数无顺序。识别以下参数：
max_iter：缺省值为 100。运行学习算法的最大迭代次数。
tau：缺省值为 0.001。计算梯度算法的初始步长。
e1：缺省值为 1e-15。算法特定的收敛阈值。
e2：缺省值为 1e-15。算法特定的收敛阈值。
e3：缺省值为 1e-15。算法特定的收敛阈值。
hessian_delta：缺省值为 1e-6。用于计算黑塞矩阵近似值的 Delta 参数。
表12-9 arima_train 函数主输出表列说明
| 列名 | 数据类型 | 描述 |
|---|---|---|
| mean | FLOAT8[] | 模型均值。 |
| mean_std_error | FLOAT8[] | 均值的标准误差。 |
| ar_params | FLOAT8[] | ARIMA模型的自回归参数。 |
| ar_std_errors | FLOAT8[] | AR参数的标准误差。 |
| ma_params | FLOAT8[] | ARIMA模型的移动平均参数。 |
| ma_std_error | FLOAT8[] | MA参数的标准误差。 |
表12-10 arima_train 函数概要输出表列说明
| 列名 | 数据类型 | 描述 |
|---|---|---|
| input_table | TEXT | 源数据表名。 |
| timestamp_col | TEXT | 源表中包含数据的时间戳索引的列名。 |
| timeseries_col | TEXT | 包含数据值的源表中的列名称。 |
| non_seasonal_orders | INTEGER[] | 非季节性ARIMA模型的[p,d,q]参数数组。 |
| include_mean | BOOLEAN | ARIMA模型中是否包含均值。 |
| residual_variance | FLOAT8 | 残差的方差。 |
| log_likelihood | FLOAT8 | 使用极大似然估计方法时的对数似然值。 |
| iter_num | INTEGER | 执行的迭代次数。 |
| exec_time | FLOAT8 | 训练模型所用的总时间。 |
表12-11 arima_train 函数残差输出表列说明
| 列名 | 数据类型 | 描述 |
|---|---|---|
| timestamp_col | INTEGER | 与'timestamp_col'参数相同（除了第一个d元素外，源表中的所有索引都包含在其中，d是来自'non_seasonal_orders'的差异顺序值）。 |
| residual | FLOAT8 | 每个数据表的残差值。 |
2. 预测函数
arima_forecast(model_table,
output_table, steps_ahead
)
参数说明：
model_table：包含在时间序列数据集上训练出来的 ARIMA 模型的表名。
output_table：用于存储预测值的表的名称。预测函数生成的输出表包含以下列。
group_by_cols：分组列的值（如果提供了分组参数）。
step_ahead：预测的时间步长。
forecast_value：对当前时间步长的预测。
steps_ahead：在时间序列末尾预测的步长数。
3. 函数示例
- (1)查看帮助信息
select madlib.arima_train();
select madlib.arima_train('usage');
- (2)创建源表并加载数据
drop table if exists arima_skirts;
create table arima_skirts (time_id integer not null, value double precision not null );
insert into
arima_skirts
values
(1866, 608),
(1867, 617),
(1868, 625),
(1869, 636),
(1870, 657),
(1871, 691),
(1872, 728),
(1873, 784),
(1874, 816),
(1875, 876),
(1876, 949),
(1877, 997),
(1878, 1027),
(1879, 1047),
(1880, 1049),
(1881, 1018),
(1882, 1021),
(1883, 1012),
(1884, 1018),
(1885, 991),
(1886, 962),
(1887, 921),
(1888, 871),
(1889, 829),
(1890, 822),
(1891, 820),
(1892, 802),
(1893, 821),
(1894, 819),
(1895, 791),
(1896, 746),
(1897, 726),
(1898, 661),
(1899, 620),
(1900, 588),
(1901, 568),
(1902, 542),
(1903, 551),
(1904, 541),
(1905, 557),
(1906, 556),
(1907, 534),
(1908, 528),
(1909, 529),
(1910, 523),
(1911, 531);
- (3)训练 ARIMA 模型
drop table if exists arima_skirts_output,
arima_skirts_output_summary, arima_skirts_output_residual;
select madlib.arima_train( 'arima_skirts',
'arima_skirts_output', 'time_id',
'value', null, true,
array[1, 2, 5]
);
4. 查看输出表
- (1)查看 ARIMA 模型输出表
select * from arima_skirts_output;
seaboxsql=# select * from arima_skirts_output;
-[ RECORD 1 ]-+--------------------------------------------------------------------------------------------------------
ar_params | {-0.1846528715125243}
ar_std_errors | {0.15601611556947773}
ma_params | {-0.12300760374201583,0.059651023123397595,-0.20781163434890507,0.06340924583011018,-1.058863260166408}
ma_std_errors | {0.05120008059417309,0.045903631804671115,0.05451979715301572,0.05533685229541148,0.059235143149885504}
- (2)查看概要输出表
select * from arima_skirts_output_summary;
seaboxsql=# select * from arima_skirts_output_summary;
-[ RECORD 1 ]-------+--------------------
input_table | arima_skirts
timestamp_col | time_id
timeseries_col | value
non_seasonal_orders | {1,2,5}
include_mean | f
residual_variance | 218.52060214672002
log_likelihood | -180.94466204102352
iter_num | 74
exec_time (s) | 1.51
- (3)查看残差输出表
select * from arima_skirts_output_residual order by time_id;
seaboxsql=# select * from arima_skirts_output_residual order by time_id;
time_id | residual
---------+---------------------
1866 | 0
1867 | 0
1868 | 0
1869 | 2.815347128487476
1870 | 10.900267718514781
1871 | 16.019406190664398
1872 | 7.305845864848587
1873 | 21.583742649836502
1874 | -12.653523293367362
1875 | 32.76871348998602
1876 | 43.94033226099209
1877 | -15.41147140615678
1878 | 3.3331666524165295
1879 | -18.33931488246348
1880 | 6.407449712491927
1881 | 13.755076303388705
1882 | 8.875105845188976
1883 | 0.5731911905167558
1884 | -4.6413901397387365
1885 | -23.078560319496596
1886 | 3.4655774761538307
1887 | -2.169710608966432
1888 | -15.584205800499856
1889 | 1.8195577312701963
1890 | 12.522975847367924
1891 | 13.46330637405197
1892 | -15.098773755195202
1893 | 17.37069041014193
1894 | -7.200037370239293
1895 | -22.5308290224145
1896 | -5.319914451507748
1897 | 3.9652475438209214
1898 | -25.411017857380152
1899 | 5.027609010030773
1900 | -7.129814470019638
1901 | 1.3197519615930593
1902 | 3.6582187772671313
1903 | 5.556098436016052
1904 | -6.022017197436191
1905 | 14.546451932065226
1906 | -7.73038779096157
1907 | -23.68790582100166
1908 | 18.957544729011587
1909 | 4.79403908205941
1910 | 4.721695574798656
1911 | 10.258471999623794
(46 rows)
5. 预测 5 年裙子的边缘直径
drop table if exists arima_skirts_forecast_output;
select madlib.arima_forecast( 'arima_skirts_output',
'arima_skirts_forecast_output', 5
);
select * from arima_skirts_forecast_output order by steps_ahead;
seaboxsql=# select * from arima_skirts_forecast_output order by steps_ahead;
steps_ahead | forecast_value
-------------+-------------------
1 | 560.7227234134328
2 | 566.2955274338282
3 | 569.4190208884223
4 | 568.6456385752384
5 | 557.7295064566549
(5 rows)
分类¶
KNN¶
knn(
point_source,
point_column_name,
label_column_name,
test_source,
test_column_name,
id_column_name,
output_table,
operation,
k
)
参数说明：
point_source：包含训练数据点的表的名称。训练数据点应该按行存储在类型为 DOUBLE PRECISION[]的列中。
point_column_name：包含训练数据点的列名。
label_column_name：具有训练数据点类标签的列名。
id_column_name：包含训练数据点中具有数据点 ID 的列的名称。
test_source：包含测试数据点的表的名称。测试数据点应该按行存储在类型为 DOUBLE PRECISION[]的列中。
test_column_name：包含训练数据点的列名。
id_column_name：测试数据表中具有数据点 ID 的列的名称。
output_table：存储输出结果的表名。
K（可选）：缺省值为 1，最近邻数量。对于分类，应该是一个奇数。
output_neighbors：输出投票/平均中使用的 k-最近邻列表。
fn_dist：计算数据点到中心的距离的函数。
1. 函数示例
- (1)创建数据表并生成数据
drop table if exists source_data;
create table source_data (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int); insert into source_data values
(1, 1,85,85,0, 0),
(2, 1,80,90,1, 0),
(3, 2,83,78,0, 1),
(4, 3,70,96,0, 1),
(5, 3,68,80,0, 1),
(6, 3,65,70,1, 0),
(7, 2,64,65,1, 1),
(8, 1,72,95,0, 0),
(9, 1,69,70,0, 1),
(10,3,75,80,0, 1),
(11,1,75,70,1, 1),
(12,2,72,90,1, 1),
(13,2,81,75,0, 1),
(14,3,71,80,1, 0);
drop table if exists knn_train_data;
create table knn_train_data (
id integer, data float8[], label integer
);
insert into
knn_train_data
select
c1,
array [round((c2 - min_c2)/cast((max_c2 - min_c2) as numeric),4)::float8,
round((c3 - min_c3)/cast((max_c3 - min_c3) as numeric),4)::float8,
round((c4 - min_c4)/cast((max_c4 - min_c4) as numeric),4)::float8, c5],
c6
from
source_data,
(
select
min(c2) min_c2,
max(c2) max_c2,
min(c3) min_c3,
max(c3) max_c3,
min(c4) min_c4,
max(c4) max_c4
from
source_data
) t;
- (2)生成测试数据
select
round((avg(c3) - min(c3)) /(max(c3) - min(c3)), 4),
round((avg(c4) - min(c4)) /(max(c4) - min(c4)), 4)
from
source_data;
drop table if exists knn_test_data;
create table knn_test_data
( id integer, data float8[]
);
insert into knn_test_data values (1, '{0,0.4558,0.4931,0}'),
(2, '{0.5,0.4558,0.4931,0}'),
(3, '{1,0.4558,0.4931,0}'),
(4, '{0,0.4558,0.4931,1}'),
(5, '{0.5,0.4558,0.4931,1}'),
(6, '{1,0.4558,0.4931,1}');
- (3)运行 KNN 分类
drop table if exists knn_result_regression;
select * from madlib.knn(
'knn_train_data', 'data',
'label',
'id', 'knn_test_data', 'data',
'id', 'knn_result_regression', 1,
True, 'madlib.dist_norm2'
);
- (4)查看输出结果
select * from knn_result_regression order by id;
seaboxsql=# select * from knn_result_regression order by id;
id | data | prediction | k_nearest_neighbours | distance
----+-----------------------+------------+----------------------+-----------------------
1 | {0,0.4558,0.4931,0} | 9 | {1} | {0.39684320581307675}
2 | {0.5,0.4558,0.4931,0} | 13 | {1} | {0.3926498949445931}
3 | {1,0.4558,0.4931,0} | 10 | {1} | {0.06861953074744834}
4 | {0,0.4558,0.4931,1} | 11 | {1} | {0.3386963832106862}
5 | {0.5,0.4558,0.4931,1} | 12 | {1} | {0.3222027312112671}
6 | {1,0.4558,0.4931,1} | 14 | {0} | {0.12284498361756575}
forest_train 朴素贝叶斯¶
MADlib 提供的朴素贝叶斯分类（Naive Bayes Classification）模块仍然处于早期开发阶段。将来的版本会解决一些问题，并且接口和实现可能会发生变化。MADlib 的朴素贝叶斯分类使用最大似然或拉普拉斯平滑估计先验概率。对于数字属性，可以使用高斯平滑来估计先验概率，然后使用这些参数对新数据进行分类。
1. 训练函数
对于仅具有分类属性的数据，使用以下函数预计算先验概率。
create_nb_prepared_data_tables (
trainingSource,
trainingClassColumn,
trainingAttrColumn,
numAttrs,
featureProbsName,
classPriorsName
)
2. 分类函数
如果数据只包含分类属性，以下函数执行朴素贝叶斯分类。
create_nb_classify_view (
featureProbsName,
classPriorsName,
classifySource,
classifyKeyColumn,
classifyAttrColumn,
numAttrs,
destName
)
3. 概率函数
如果数据只包含分类属性，以下函数计算朴素贝叶斯概率：
create_nb_probs_view(
featureProbsName,
classPriorsName,
classifySource,
classifyKeyColumn,
classifyAttrColumn,
numAttrs,
destName
)
4. 函数示例
- (1)准备训练数据
drop table if exists nb_train_data;
create table nb_train_data (
id integer, data float8[], label integer
);
insert into nb_train_data values
(1, '{1,85,85,0}', 0), (2, '{1,80,90,1}', 0), (3, '{2,83,78,0}', 1),
(4, '{3,70,96,0}', 1), (5, '{3,68,80,0}', 1), (6, '{3,65,70,1}', 0),
(7, '{2,64,65,1}', 1), (8, '{1,72,95,0}', 0), (9, '{1,69,70,0}', 1),
(10,'{3,75,80,0}', 1), (11,'{1,75,70,1}', 1), (12,'{2,72,90,1}', 1),
(13,'{2,81,75,0}', 1), (14,'{3,71,80,1}', 0);
- (2)准备测试数据
drop table if exists nb_test_data;
create table nb_test_data
( id integer, data float8[]
);
insert into nb_test_data values (1, '{1,73.57,80.29,0}'),
(2, '{2,73.57,80.29,0}'),
(3, '{3,73.57,80.29,0}'),
(4, '{1,73.57,80.29,1}'),
(5, '{2,73.57,80.29,1}'),
(6, '{3,73.57,80.29,1}');
- (3)预计算先验概率
select
madlib.create_nb_prepared_data_tables (
'nb_train_data',
'label',
'data',
'array[2,3]',
4,
'categ_feature_probs',
'numeric_attr_params',
'class_priors'
);
- (4)查询预计算输出表
select * from categ_feature_probs;
select * from numeric_attr_params;
select * from class_priors;
seaboxsql=# select * from categ_feature_probs;
class | attr | value | cnt | attr_cnt
-------+------+-------+-----+----------
1 | 4 | 1 | 3 | 2
0 | 4 | 0 | 2 | 2
0 | 1 | 1 | 3 | 3
0 | 1 | 3 | 2 | 3
1 | 1 | 2 | 4 | 3
1 | 1 | 1 | 2 | 3
0 | 4 | 1 | 3 | 2
1 | 1 | 3 | 3 | 3
1 | 4 | 0 | 6 | 2
0 | 1 | 2 | 0 | 3
(10 rows)
seaboxsql=# select * from numeric_attr_params;
class | attr | attr_mean | attr_var
-------+------+-------------------+-------------------
0 | 2 | 74.6 | 62.3
1 | 2 | 73 | 38.00000000000001
1 | 3 | 78.22222222222223 | 97.69444444444443
0 | 3 | 84 | 92.5
(4 rows)
seaboxsql=# select * from class_priors;
class | class_cnt | all_cnt
-------+-----------+---------
0 | 5 | 14
1 | 9 | 14
(2 rows)
- (5)建立朴素贝叶斯分类视图并检查结果
drop view if exists classify_view;
select madlib.create_nb_classify_view
( 'categ_feature_probs', 'class_priors', 'nb_test_data',
'id',
'data', 4,
'numeric_attr_params', 'classify_view');
select * from classify_view order by key;
seaboxsql=# select * from classify_view order by key;
key | nb_classification
-----+-------------------
1 | {1}
2 | {1}
3 | {1}
4 | {0}
5 | {1}
6 | {1}
(6 rows)
- (6)查看每个分类的概率
drop view if exists probs_view;
select madlib.create_nb_probs_view
( 'categ_feature_probs', 'class_priors', 'nb_test_data',
'id',
'data', 4,
'numeric_attr_params', 'probs_view' );
select * from probs_view;
seaboxsql=# select * from probs_view;
key | class | nb_prob
-----+-------+---------------------
2 | 0 | 0.07844253981461236
2 | 1 | 0.9215574601853876
3 | 0 | 0.24196381892957872
3 | 1 | 0.7580361810704213
4 | 0 | 0.5697225108174688
4 | 1 | 0.4302774891825312
6 | 0 | 0.42686706414724934
6 | 1 | 0.5731329358527506
1 | 0 | 0.36202663105016714
1 | 1 | 0.6379733689498328
5 | 0 | 0.16570182973256137
5 | 1 | 0.8342981702674387
(12 rows)
- (7)执行即席计算
drop view if exists classify_view;
select madlib.create_nb_classify_view
( 'nb_train_data', 'label',
'data', 'array[2,3]', 'nb_test_data', 'id',
'data', 4,
'classify_view');
select * from classify_view order by key;
seaboxsql=# select * from classify_view order by key;
key | nb_classification
-----+-------------------
1 | {1}
2 | {1}
3 | {1}
4 | {0}
5 | {1}
6 | {1}
(6 rows)
drop view if exists probs_view;
select madlib.create_nb_probs_view
( 'nb_train_data', 'label',
'data', 'array[2,3]', 'nb_test_data', 'id',
'data', 4,
'probs_view' );
select * from probs_view;
seaboxsql=# select * from probs_view;
key | class | nb_prob
-----+-------+---------------------
1 | 0 | 0.36202663105016714
1 | 1 | 0.6379733689498328
5 | 0 | 0.16570182973256137
5 | 1 | 0.8342981702674387
2 | 0 | 0.07844253981461236
2 | 1 | 0.9215574601853876
3 | 0 | 0.24196381892957852
3 | 1 | 0.7580361810704216
4 | 0 | 0.5697225108174688
4 | 1 | 0.4302774891825312
6 | 0 | 0.4268670641472491
6 | 1 | 0.5731329358527509
(12 rows)
SVM¶
1. 分类训练函数
svm_classification(
source_table,
model_table,
dependent_varname,
independent_varname,
kernel_func,
kernel_params,
grouping_col,
params,
verbose
)
参数说明：
source_table：包含训练数据的表的名称。
model_table：包含模型的输出表名称，输出表列如表 12-12，表 12-13 所示。
dependent_varname：因变量列的名称。对于分类，此列可以包含任何类型的值，但必须假定两个不同的值。否则，将会抛出错误。
independent_varname：自变量表达式列表。截取变量不应该包含在这个表达式中。有关截取信息，参阅内核参数中的‘fit_intercept’。【注意】表达式应该能够转换为 DOUBLE PRECISION[]类型。
kernel_func（可选）：核函数类型，缺省值为‘linear’。当前支持三种核类型，‘linear’、 ‘gaussian’和‘polynomial’。文本可以是三个字符串的任何子集，例如，kernel_func='ga'将创建一个高斯核函数。
kernel_params（可选）：缺省值为 NULL。用逗号分隔的键值对中的非线性内核的参数。
grouping_col（可选）：缺省值为 NULL。与 SQL 中的“GROUP BY”类似，是一个表达式列表，用于将输入数据集分组为离散组，每个组训练一个模型。【注意】如果使用分组，则不支持交叉验证。
params（可选）：缺省值为 NULL。用逗号分隔的键值对中的优化和正则化参数。
verbose（可选）：缺省值为 FALSE。是否详细输出训练结果。
表12-12 svm_classification 函数输出表列说明
| 列名 | 数据类型 | 描述 |
|---|---|---|
| coef | FLOAT8 | 系数向量。 |
| grouping_key | TEXT | 标识数据所属的组。 |
| num_rows_processed | BIGINT | 处理的行数。 |
| num_rows_skipped | BIGINT | 由于缺失值或失败而跳过的行数。 |
| num_iteratoins | INTEGER | 随机梯度下降算法完成的迭代次数。 |
| loss | FLOAT8 | SVM的目标函数值。 |
| norm_of_gradient | FLOAT8 | 目标函数梯度的L2范数的值。 |
| dep_var_mapping | TEXT[] | 因变量标签的向量。 |
表12-13 svm_classification 函数概要输出表列说明
| 列名 | 数据类型 | 描述 |
|---|---|---|
| method | TEXT | 'svm'。 |
| version_number | TEXT | 用于生成模型的madlib版本。 |
| source_table | TEXT | 元数据表名。 |
| model_table | TEXT | 模型表名。 |
| dependent_varname | TEXT | 因变量。 |
| independent_varname | TEXT | 自变量。 |
| kernel_func | TEXT | 核函数。 |
| kernel_parameters | TEXT | 核参数以及随机特征映射数据。 |
| grouping_col | TEXT | 分组列。 |
| optim_params | TEXT | 优化参数字符串。 |
| reg_params | TEXT | 正则化参数字符串。 |
| num_all_groups | INTEGER | SVM训练的组数。 |
| num_failed_groups | INTEGER | SVM训练失败的组数。 |
| total_rows_processed | BIGINT | 所有组处理的总行数。 |
| total_rows_skipped | BIGINT | 由于缺少值或失败，所有组跳过的总行数。 |
2. 回归训练函数
svm_regression(
source_table,
model_table,
dependent_varname,
independent_varname,
kernel_func,
kernel_params,
grouping_col,
params,
verbose
)
回归训练函数与分类函数基本相同，以下的参数不同：
dependent_varname：TEXT 类型，因变量列的名称。对于回归，该列只能包含可以转换为 DOUBLEPRECISION 的值或表达式，否则将会抛出错误。
params（可选）：TEXT，缺省值为 NULL。参数 epsilon 和 eps_table 仅对回归有意义。
3. 新颖性检测训练函数
svm_one_class(
source_table,
model_table,
independent_varname,
kernel_func,
kernel_params,
grouping_col,
params,
verbose
)
新颖性检测函数的格式与分类函数基本相同，但未指定因变量名称。
4. 预测函数
svm_predict(model_table,
new_data_table, id_col_name, output_table)
参数说明：
model_table：由训练函数产生的模型表。
new_data_table：包含预测数据的表的名称。应该包含与训练期间使用的相同的特征。
该表还应包含用于标识每一行的 id_col_name 列。
id_col_name：输入表中 id 列的名称。
output_table：输出预测写入的表的名称。如果该表名已被使用，则返回错误。
5. 函数示例
- (1)准备训练数据
drop table if exists dt_golf;
create table dt_golf
( id integer not null, "outlook" int,
temperature double precision, humidity double precision, windy int,
class int );
insert into dt_golf values
(1, 1,85,85,0, 0), (2, 1,80,90,1, 0), (3, 2,83,78,0, 1),
(4, 3,70,96,0, 1), (5, 3,68,80,0, 1), (6, 3,65,70,1, 0),
(7, 2,64,65,1, 1), (8, 1,72,95,0, 0), (9, 1,69,70,0, 1),
(10,3,75,80,0, 1), (11,1,75,70,1, 1), (12,2,72,90,1, 1),
(13,2,81,75,0, 1), (14,3,71,80,1, 0);
- (2)使用线性模型训练分类器
drop table if exists dt_golf_svm, dt_golf_svm_summary;
select madlib.svm_classification
( 'dt_golf', 'dt_golf_svm', 'class',
'array[1, "outlook", temperature, humidity, windy]' );
- (3)查看线性分类模型结果
select * from dt_golf_svm;
seaboxsql=# select * from dt_golf_svm;
-[ RECORD 1 ]------+----------------------------------------------------------------------------------------------------------
coef | {-5.777024104114425e-17,0.05307241219855752,0.18451648484234764,-0.16247231175967197,-0.0643509666622372}
loss | 1.0389051901573687
norm_of_gradient | 108.7848835705276
num_iterations | 100
num_rows_processed | 14
num_rows_skipped | 0
dep_var_mapping | {0,1}
- (4)用线性模型进行预测
drop table if exists dt_golf_pred;
select
t1.id,
t1.class,
t2.prediction,
t2.decision_function,
case
when class = t2.prediction then 'T'
else 'F'
end
from
dt_golf t1
join dt_golf_pred t2 using (id)
order by
id;
seaboxsql=# select
t1.id,
t1.class,
t2.prediction,
t2.decision_function,
case
when class = t2.prediction then 'T'
else 'F'
end
from
dt_golf t1
join dt_golf_pred t2 using (id)
order by
id;
id | class | prediction | decision_function | case
----+-------+------------+----------------------+------
1 | 0 | 1 | 1.926827124225989 | F
2 | 0 | 1 | 0.127532174553654 | F
3 | 1 | 1 | 2.748172749057556 | T
4 | 1 | 0 | -2.5219707533685014 | F
5 | 1 | 0 | -0.29144673489844664 | F
6 | 0 | 1 | 0.7153759615089953 | F
7 | 1 | 1 | 1.2901486232664487 | T
8 | 0 | 0 | -2.0966102963212485 | T
9 | 1 | 1 | 1.411648043143508 | T
10 | 1 | 1 | 1.0001686589979872 | T
11 | 1 | 1 | 2.4543959855353568 | T
12 | 1 | 0 | -1.2955272919865697 | F
13 | 1 | 1 | 2.8665567146518764 | T
14 | 0 | 1 | 0.19775175296636 | F
(14 rows)
- (5)使用高斯核函数训练非线性分类器
drop table if exists dt_golf_svm_gaussian,
dt_golf_svm_gaussian_summary, dt_golf_svm_gaussian_random;
select madlib.svm_classification
( 'dt_golf', 'dt_golf_svm_gaussian', 'class',
'array[1, "outlook", temperature, humidity, windy]', 'gaussian',
'n_components=10', '',
'init_stepsize=1, max_iter=200'
);
- (6)查看高斯核函数分类模型结果
select * from dt_golf_svm_gaussian;
seaboxsql=# select * from dt_golf_svm_gaussian;
-[ RECORD 1 ]------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
coef | {-3.3514926718010775e-09,1.427025432418159,-1.1331792574018841e-06,1.4404451096942013,-1.795775631674154e-09,-0.26723671131478016,2.459957592700661,1.3864784883347527e-09,2.368836859259108e-09,-1.0703172425008545}
loss | 0.8194570814860818
norm_of_gradient | 1.6377927665077026
num_iterations | 182
num_rows_processed | 14
num_rows_skipped | 0
dep_var_mapping | {0,1}
- (7)用高斯非线性模型进行预测
drop table if exists dt_golf_pred_gaussian;
select madlib.svm_predict('dt_golf_svm_gaussian', 'dt_golf', 'id', 'dt_golf_pred_gaussian');
select
t1.id,
t1.class,
t2.prediction,
t2.decision_function,
case
when class = t2.prediction then 'T'
else 'F'
end
from
dt_golf t1
join dt_golf_pred_gaussian t2 using (id)
order by
id;
seaboxsql=# select
t1.id,
t1.class,
t2.prediction,
t2.decision_function,
case
when class = t2.prediction then 'T'
else 'F'
end
from
dt_golf t1
join dt_golf_pred_gaussian t2 using (id)
order by
id;
id | class | prediction | decision_function | case
----+-------+------------+----------------------+------
1 | 0 | 1 | 0.30752573119791793 | F
2 | 0 | 0 | -1.000000009946038 | T
3 | 1 | 1 | 0.4488907480652198 | T
4 | 1 | 1 | 0.589007771730887 | T
5 | 1 | 1 | 1.0131532803926158 | T
6 | 0 | 0 | -1.0000000052545135 | T
7 | 1 | 0 | -0.4187591276352226 | F
8 | 0 | 0 | -0.9999999997491626 | T
9 | 1 | 1 | 0.4505424992661082 | T
10 | 1 | 1 | 0.9805993300951882 | T
11 | 1 | 1 | 1.5494948189780724 | T
12 | 1 | 1 | 0.6730906614843679 | T
13 | 1 | 1 | 0.8960373498378693 | T
14 | 0 | 0 | -0.27680170332295423 | T
(14 rows)
- (8)使用‘balanced’参数为不平衡数据集训练模型
drop table if exists dt_golf_svm_gaussian,
dt_golf_svm_gaussian_summary, dt_golf_svm_gaussian_random;
select madlib.svm_classification ( 'dt_golf',
'dt_golf_svm_gaussian', 'class',
'array[1, "outlook", temperature, humidity, windy]', 'gaussian',
'n_components=10', '',
'init_stepsize=1, max_iter=200, class_weight=balanced'
);
select * from dt_golf_svm_gaussian;
seaboxsql=# select * from dt_golf_svm_gaussian;
-[ RECORD 1 ]------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
coef | {-3.3514926718010775e-09,1.427025432418159,-1.1331792574018841e-06,1.4404451096942013,-1.795775631674154e-09,-0.26723671131478016,2.459957592700661,1.3864784883347527e-09,2.368836859259108e-09,-1.0703172425008545}
loss | 0.8194570814860818
norm_of_gradient | 1.6377927665077026
num_iterations | 182
num_rows_processed | 14
num_rows_skipped | 0
dep_var_mapping | {0,1}
- (9)用不平衡模型预测
drop table if exists dt_golf_pred_gaussian;
select madlib.svm_predict('dt_golf_svm_gaussian', 'dt_golf', 'id', 'dt_golf_pred_gaussian');
;
select
t1.id,
t1.class,
t2.prediction,
t2.decision_function,
case
when class = t2.prediction then 'T'
else 'F'
end
from
dt_golf t1
join dt_golf_pred_gaussian t2 using (id)
order by
id
seaboxsql=# select
t1.id,
t1.class,
t2.prediction,
t2.decision_function,
case
when class = t2.prediction then 'T'
else 'F'
end
from
dt_golf t1
join dt_golf_pred_gaussian t2 using (id)
order by
id;
id | class | prediction | decision_function | case
----+-------+------------+---------------------+------
1 | 0 | 0 | -0.8003850079834606 | T
2 | 0 | 0 | -0.9999999995755703 | T
3 | 1 | 1 | 0.6234002940581886 | T
4 | 1 | 1 | 0.55957054464483 | T
5 | 1 | 1 | 0.8930508281405103 | T
6 | 0 | 0 | -1.0000000026757792 | T
7 | 1 | 1 | 0.3334529872075315 | T
8 | 0 | 0 | -1.0507931190346165 | T
9 | 1 | 1 | 0.2431506207263964 | T
10 | 1 | 1 | 0.2491368098079885 | T
11 | 1 | 1 | 1.0898965387308879 | T
12 | 1 | 0 | -0.3808316517993938 | F
13 | 1 | 1 | 0.9281669984439374 | T
14 | 0 | 0 | -1.0000000015820427 | T
(14 rows)
决策树¶
- 训练函数
tree_train (
training_table_name,
output_table_name,
id_col_name,
dependent_variable,
list_of_features,
list_of_features_to_exclude,
split_criterion,
grouping_cols,
weights,
max_depth,
min_split,
min_bucket,
num_splits,
pruning_params,
surrogate_params,
verbosity
)
参数说明：
training_table_name：训练数据输入表名。
output_table_name：包含决策树模型的输出表名，如果表已经存在则报错。输出表列如表 12-14，表 12-15 所示。
id_col_name：训练数据中，含有 ID 信息的列名。这是一个强制参数，用于预测和交叉验证。每行的 ID 值应该是唯一的。
dependent_variable：包含用于训练的输出列名。分类的输出列是 boolean、integer 或text 类型，回归的输出列是 double precision 类型。
决策树的因变量可以为多个，训练函数的时间和空间复杂度，会随着因变量数量的增加呈线性增长。
list_of_features：逗号分隔字符串，用于预测的特征列名，也可以用‘*’表示所有列都用于预测（除下一个参数中的列名外）。
特征列的类型可以是 boolean、integer、text 或double precision。
list_of_features_to_exclude：逗号分隔字符串，不用于预测的列名。
如果自变量是一个 表达式（包括列的类型转换），
那么这个列表中应该包括用于自变量表达式的所有列名， 否则那些列将被包含在特征中。
split_criterion：缺省值为‘gini’，用于分类，而‘mse’用于回归。不纯度函数计算用于分裂的特征值。
分类树支持的标准有‘gini’、‘entropy’或‘misclassification’，回归树的分裂标准总是使用‘mse’。
grouping_cols（可选）：缺省值为 NULL，逗号分隔字符串，分组的列名。将为每个分组产生一棵决策树。
weights（可选）：权重列名。
max_depth（可选）：缺省是 10。最终决策树的最大深度，根的深度为 0。
min_split（可选）：缺省值为 20。一个试图被分裂的节点中，必须存在的元组的最小数量。此参数的最佳值取决于数据集的元组数目。
min_bucket（可选）：缺省值为 min_split/3。任何叶节点对应的最小元组数量。
如果min_split 和 min_bucket 只指定一个，那么 min_split 设置成 min_bucket*3，或者min_bucket 设置成 min_split/3。
num_splits（可选）：缺省值为 100。为计算分割边界，需要将连续特征值分成离散型分位点。
此全局参数用于计算连续特征的分割点，值越大预测越准，处理时间也越长。
pruning_params（可选）：逗号分隔的键-值对，用于决策树剪枝，当前接受的值为：
cp：缺省值为 0。
cp 全称为 complexity parameter，指某个点的复杂度，对每一步拆分， 模型的拟合优度必须提高的程度。
试图分裂一个节点时，分裂增加的精确度必须提高 cp， 才进行分裂，否则剪枝该节点。该参数值用于在运行检查验证前，创建一棵初始树。
n_folds：缺省值为 0。用于计算 cp 最佳值的交叉验证褶皱数。为执行交叉验证，n_folds 的值应该大于 2。
执行交叉验证时，会产生一个名为<model_table>_cv 的输出表，其中包含估计的 cp 值和交叉验证错误。输出表中返回的决策树对应具有最少交叉错误的 cp
（如果多个 cp 值有相同的错误数，取最大的 cp）。
surrogate_params：逗号分隔的键值对，控制替代分裂点的行为。
替代变量是与主预测变量相关的另一种预测变量，当主预测变量的值为 NULL 时使用替代变量。此参数当前接受的值为：
max_surrogates：缺省值为 0，每个节点的替代变量数。
verbosity：是否提供训练结果的详细输出，缺省值为 FALSE。
表12-14 tree_train 函数输出模型列表
| 列名 | 数据类型 | 描述 |
|---|---|---|
| <…> | TEXT | 当提供了grouping_col入参时，该列存储分组列， 依赖于grouping_cols入参的值，可能有多列，类型与训练表相同。 |
| tree | BYTEA8 | 二进制格式存储的决策树模型。 |
| cat_levels_in_text | TEXT[] | 分类变量的层次。 |
| cat_n_levels | INTEGER[] | 每个分类变量的层号。 |
| tree_depth | INTEGER[] | 剪枝前的决策树最大深度。 |
| pruning_cp | FLOAT8[] | 用于剪枝决策树的复杂性成本参数。 |
表12-15 tree_train 函数输出概要表列
| 列名 | 数据类型 | 描述 |
|---|---|---|
| method | TEXT | 值为'tree_train'。 |
| is_classification | TEXT | 用于分类时为Tree，用于回归时为FALSE。 |
| source_table | TEXT | 源表名。 |
| model_table | TEXT | 模型表名。 |
| id_col_name | TEXT | ID列名。 |
| dependent_varname | TEXT | 因变量。 |
| independent_varname | TEXT | 自变量。 |
| cat_feature | TEXT | 逗号分隔字符串，分类特征名称列表。 |
| con_feature | TEXT | 逗号分隔字符串，连续特征名称列表。 |
| grouping_col | TEXT | 分组列名。 |
| num_all_groups | INTEGER | 训练决策树时的总分组数。 |
| num_failed_groups | INTEGER | 训练决策树时失败的分组数。 |
| total_rows_processed | BIGINT | 所有分组处理的总行数。 |
| total_rows_skipped | BIGINT | 所有分组中因为缺少值或者失败而跳过的总行数。 |
| dependent_var_level | TEXT | 对于分类，因变量的不同取值。 |
| dependent_var_type | TEXT | 因变量类型。 |
| input_cp | FLOAT8[] | 交叉验证前，用于剪枝决策树的复杂度参数。 |
| independent_var_types | TEXT | 逗号分隔字符串，自变量类型。 |
2. 预测函数
tree_predict(tree_model,
new_data_table, output_table, type)
参数说明：
tree_model：包含决策树模型的表名，应该是决策树训练函数的输出表。
new_data_table：包含被预测数据的表名。该表应该和训练表具有相同的特征，也应该包含用于标识每行的 id_col_name。
output_table：预测结果的输出表名，如果表已经存在则报错。
表中包含标识每个预测的id_col_name 列，以及每个因变量的预测列。
如果 type = 'response'，表有单一预测列。此列的类型依赖于训练时使用的因变量的类型。
如果 type = 'prob'，每个因变量对应多列，每列表示因变量的一个可能值。
列标识为‘estimated_prob_dep_value’，其中 dep_value 表示对应的因变量值。
type（可选）：缺省值为‘response’。
对于回归树，输出总是因变量的预测值。对于分类树，变量类型可以是‘response’或‘prob’。
3. 显示函数
tree_display(tree_model,dot_format, verbosity)
参数说明：
tree_model：含有决策树模型的表名。
dot_format：缺省值为 TRUE，使用 dot 格式，否则输出文本格式。
verbosity：缺省值为 FALSE。如果设置为 TRUE，dot 格式输出中包含附加信息，
如不纯度、样本大小、每个因变量的权重行数、被剪枝的分类或预测等。
输出总是返回文本形式， 对于 dot 格式，可以重定向输出到客户端文件，然后使用可视化程序处理。
4. 函数示例
- (1)准备输入数据
drop table if exists dt_golf; create table dt_golf
( id integer not null, "outlook" text,
temperature double precision, humidity double precision,
windy text,
class text );
insert into dt_golf values (1,'sunny',85,85,'false','Dont Play'),
(2,'sunny',80,90,'true','Dont Play'),
(3,'overcast',83,78,'false','Play'),
(4,'rain',70,96,'false','Play'),
(5,'rain',68,80,'false','Play'),
(6,'rain',65,70,'true','Dont Play'),
(7,'overcast',64,65,'true','Play'),
(8,'sunny',72,95,'false','Dont Play'),
(9,'sunny',69,70,'false','Play'),
(10,'rain',75,80,'false','Play'),
(11,'sunny',75,70,'true','Play'),
(12,'overcast',72,90,'true','Play'),
(13,'overcast',81,75,'false','Play'),
(14,'rain',71,80,'true','Dont Play');
- (2)运行决策树训练函数
drop table if exists train_output, train_output_summary, train_output_cv;
select madlib.tree_train(
'dt_golf', 'train_output', 'id',
'class',
'"outlook", temperature, humidity, windy',
null::text, 'gini', null::text, null::text, 5,
3,
1,
10,
'cp=0,n_folds=6'
);
- (3)查看结果
seaboxsql=# select * from train_output_summary;
-[ RECORD 1 ]---------+-----------------------------------------------
method | forest_train
is_classification | t
source_table | dt_golf
model_table | train_output
id_col_name | id
dependent_varname | class
independent_varnames | "outlook",windy,temperature,humidity
cat_features | "outlook",windy
con_features | temperature,humidity
grouping_cols |
num_trees | 20
num_random_features | 2
max_tree_depth | 8
min_split | 3
min_bucket | 1
num_splits | 10
verbose | f
importance | t
num_permutations | 1
num_all_groups | 1
num_failed_groups | 0
total_rows_processed | 14
total_rows_skipped | 0
dependent_var_levels | don't play,play
dependent_var_type | text
independent_var_types | text, text, double precision, double precision
null_proxy | None
- (4)用决策树模型进行预测
drop table if exists prediction_results;
select madlib.tree_predict ('train_output',
'dt_golf', 'prediction_results', 'response');
select
t1.*,
t2.class
from
prediction_results t1,
dt_golf t2
where
t1.id = t2.id
order by
id;
seaboxsql=# select
t1.*,
t2.class
from
prediction_results t1,
dt_golf t2
where
t1.id = t2.id
order by
id;
id | estimated_class | class
----+-----------------+-----------
1 | Play | Dont Play
2 | Play | Dont Play
3 | Play | Play
4 | Play | Play
5 | Play | Play
6 | Play | Dont Play
7 | Play | Play
8 | Play | Dont Play
9 | Play | Play
10 | Play | Play
11 | Play | Play
12 | Play | Play
13 | Play | Play
14 | Play | Dont Play
(14 rows)
随机森林¶
forest_train(
training_table_name,
output_table_name,
id_col_name,
dependent_variable,
list_of_features,
list_of_features_to_exclude,
grouping_cols,
num_trees,
num_random_features,
importance,
num_permutations,
max_tree_depth,
min_split,
min_bucket,
num_splits,
surrogate_params,
verbose,
sample_ratio
)
参数说明：
training_table_name：包含培训数据的表的名称。
output_table_name：包含生成模型的表的名称。会创建三个表，名称基于训练函数中output_table_name 参数的值。三个输出表列分别如表 12-16，表 12-17，表 12-18 所示。
id_col_name：包含训练数据中 id 信息的列名。
dependent_variable：包含用于训练输出的列名。分类输出 Boolean、integer 或 text 类型， 而回归输出 float 类型的值。
list_of_features：用作预测的逗号分隔的特征列名。可以是“*”，说明将所有列用作预 测的特征列（除在下一个参数中包含的列外）。
类别列可以是 Boolean、integer 或 text 类型。
list_of_features_to_exclude：排除在预测列表以外的逗号分隔的列名字符串。
如果dependent_variable 参数是一个表达式（包括列名的转换），那么这个列表中应该包含dependent_variable 表达式中的列，
否则那些列将包含在特征中，结果将生成无意义的树。
grouping_cols（可选）：缺省值为 NULL，逗号分隔的分组列名，每个分组会建立一个随机森林。
num_trees（可选）：缺省值为 100，随机森林模型中产生的决策树的最大数目。实际产生的决策树数量可能稍有不同。
num_random_feature（可选）：每次分裂随机选择的特征数。分类树的缺省值为 sqrt(n)，否则为 n/3。
importance（可选）：缺省值为 true，是否计算变量的重要性。如果设置为 true，将在分组模型表（<model_table>_group）中输出分类特征和连续特征的变量重要性。计算变量重要性将增加函数的运行时间。
num_permutations（可选）：缺省值为 1。计算变量重要性时，每个特征值的重排次数。一个特征变量的重要性是通过重排变量的随机值计算的，计算预测精度的下降（使用 OOB 采样）。设置大于 1 的值将计算多个重要性的平均值，这会增加总体运行时间。大多数情况下，缺省值 1 对计算重要性已经足够。
max_depth（可选）：缺省值为 10。一棵树中任意节点的最大深度，根节点的深度为 0。
min_split（可选）：缺省值为 20。试图分裂的节点中必须存在的最小观测数。
min_bucket（可选）：缺省值为 min_split/3。任何终端节点的最小观测数。如果只指定min_bucket 和 min_split 之一，则 min_split 被设置成 min_bucket * 3，或者 min_bucket 被设置成 min_split / 3。
num_splits（可选）：缺省值为 100。连续特征值被离散时，计算分裂边界的个数。这个全局参数用于计算连续特征的拆分的结果。较大值会导致更好的预测，但也会增加处理时间。
surrogate_params（可选）：逗号分隔的键值对字符串，用于控制树中每个节点的代理拆分行为。可指定 max_surrogates，缺省值为 0，指定每个节点存储的代理数量。
verbose（可选）：缺省值为 false。是否提供训练结果的详细输出。
sample_ratio（可选）：缺省值为 1，范围是(0,1]。如果 sample_ratio 小于 1，bootstrap
样本小于用于在森林中每棵树训练的预期值。一个接近 0 的比率可能导致只有根节点的树。这允许用户快速对该功能进行实验。
表12-16 forest_train 函数模型输出表列说明
| 列名 | 数据类型 | 描述 |
|---|---|---|
| gid | INTEGER | 分组ID。 |
| sample_id | INTEGER | bootstrap样本id。 |
| tree | BYTEA8 | 以二进制格式存储的训练树模型。 |
表12-17 forest_train 函数概要输出表列说明
| 列名 | 数据类型 | 描述 |
|---|---|---|
| method | TEXT | 'forest_train'。 |
| is_classification | BOOLEAN | 分类模型为True。 |
| source_table | TEXT | 源表名。 |
| model_table | TEXT | 模型表名。 |
| id_col_name | TEXT | ID列名。 |
| dependent_varname | TEXT | 因变量。 |
| independent_varname | TEXT | 自变量。 |
| cat_features | TEXT | 类别特征名。 |
| con_features | TEXT | 连续特征名。 |
| grouping_col | INT | 分组列名。 |
| num_trees | INT | 模型产生的决策树数量。 |
| num_random_features | INT | 每次分裂随机选择的特征数。 |
| max_tree_depth | INT | 随机森林中任意树的最大深度。 |
| min_split | INT | 要分割的节点中的最小观测数。 |
| min_bucket | INT | 任何终端节点的最小观测数。 |
| num_splits | INT | 连续变量的桶数。 |
| verbose | BOOLEAN | 是否显示debug信息。 |
| importance | BOOLEAN | 是否计算变量重要性。 |
| num_permutations | INT | 计算变量重要性时，每个特征值的重排次数，缺省值为1。 |
| num_all_groups | INT | 分组数。 |
| num_failed_groups | INT | 失败的分组数。 |
| total_rows_processed | BIGINT | 所有分组处理的总行数。 |
| total_rows_skipped | BIGINT | 由于缺少或失败，所有组中跳过的行总数。 |
| dependent_var_level | TEXT | 对于分类，因变量的不同值。 |
| dependent_var_type | TEXT | 因变量的数据类型。 |
表12-18 forest_train 函数分组输出表列说明
| 列名 | 数据类型 | 描述 |
|---|---|---|
| gid | INTEGER | 唯一标识一组分组列值的组ID。 |
| <…> | TEXT | 分组列，取决于grouping_cols输入可能有多个列。 |
| success | BOOLEAN | 标识分组是否成功。 |
| cat_level_in_text | TEXT[] | 分类变量的序号。 |
| cat_n_level | INTEGER[] | 每个分类变量的级别。 |
| oob_error | DOUBLE PRECISION | 随机森林模型的无袋误差。 |
| cat_var_importance | DOUBLE PRECISION[] | 分类特征变量的重要性，顺序与<model_table_summary表中 cat_features列的顺序对应。 |
| con_var_importance | DOUBLE PRECISION[] | 连续特征变量的重要性，顺序与<model_table_summary表中 cat_features列的顺序对应。 |
2. 预测函数
forest_predict(random_forest_model,
new_data_table, output_table, type)
参数说明：
forest_model：模型表名。
new_data_table：包含被预测数据的表名。
output_table：预测结果输出表。
Type（可选）：缺省值为'response'。对于回归模型，输出总是依赖变量的预测值。对于分类模型，类型变量可以是“response”，将分类预测作为输出，或者是“概率”，给出类概率作为输出。对于因变量的每个值，在输出表中添加一个有概率的列。
3. 显示函数
get_tree(forest_model_table,
gid, sample_id, dot_format, verbose)；
get_tree_surr(forest_model_table,
gid, sample_id)
参数说明：
forest_model_table：模型表名。
gid：分组 ID。
sample_id：Bootstrap 样本 ID。
dot_format（可选）：缺省值为 TRUE，使用 dot 格式，否则输出文本格式。
Verbose（可选）：缺省值为 FALSE。如果设置为 TRUE，dot 格式输出中包含附加信息，如不纯度、样本大小、每个因变量的权重行数、被剪枝的分类或预测等。输出总是返回文本形式，对于 dot 格式，可以重定向输出到客户端文件，然后使用可视化程序处理。
4. 函数示例
- (1)准备数据
drop table if exists dt_golf; create table dt_golf (
id integer not null, "outlook" text,
temperature double precision, humidity double precision, windy text,
class text
);
insert into dt_golf (id,"outlook",temperature,humidity,windy,class) values
(1, 'sunny', 85, 85, 'false', 'don''t play'),
(2, 'sunny', 80, 90, 'true', 'don''t play'),
(3, 'overcast', 83, 78, 'false', 'play'),
(4, 'rain', 70, 96, 'false', 'play'),
(5, 'rain', 68, 80, 'false', 'play'),
(6, 'rain', 65, 70, 'true', 'don''t play'),
(7, 'overcast', 64, 65, 'true', 'play'),
(8, 'sunny', 72, 95, 'false', 'don''t play'),
(9, 'sunny', 69, 70, 'false', 'play'),
(10, 'rain', 75, 80, 'false', 'play'),
(11, 'sunny', 75, 70, 'true', 'play'),
(12, 'overcast', 72, 90, 'true', 'play'),
(13, 'overcast', 81, 75, 'false', 'play'),
(14, 'rain', 71, 80, 'true', 'don''t play');
- (2)运行随机森林训练函数
drop table if exists train_output, train_output_group, train_output_summary;
select
madlib.forest_train(
'dt_golf',
'train_output',
'id',
'class',
'"outlook", temperature, humidity, windy',
null,
null,
20 :: integer,
2 :: integer,
true :: boolean,
1 :: integer,
8 :: integer,
3 :: integer,
1 :: integer,
10 :: integer
);
- (3)查看输出
seaboxsql=# select * from train_output_summary;
-[ RECORD 1 ]---------+-----------------------------------------------
method | forest_train
is_classification | t
source_table | dt_golf
model_table | train_output
id_col_name | id
dependent_varname | class
independent_varnames | "outlook",windy,temperature,humidity
cat_features | "outlook",windy
con_features | temperature,humidity
grouping_cols |
num_trees | 20
num_random_features | 2
max_tree_depth | 8
min_split | 3
min_bucket | 1
num_splits | 10
verbose | f
importance | t
num_permutations | 1
num_all_groups | 1
num_failed_groups | 0
total_rows_processed | 14
total_rows_skipped | 0
dependent_var_levels | don't play,play
dependent_var_type | text
independent_var_types | text, text, double precision, double precision
null_proxy | None
- (4)获取森林中单个树的 dot 格式显示
select madlib.get_tree('train_output',1,2);
seaboxsql=#
seaboxsql=# select madlib.get_tree('train_output',1,2);
get_tree
---------------------------------------------------
digraph "Classification tree for dt_golf" { +
"0" [label="humidity <= 75", shape=ellipse]; +
"0" -> "1"[label="yes"]; +
"1" [label="play",shape=box]; +
"0" -> "2"[label="no"]; +
"2" [label="\"outlook\" <= rain", shape=ellipse];+
"2" -> "5"[label="yes"]; +
"5" [label="play",shape=box]; +
"2" -> "6"[label="no"]; +
"6" [label="don't play",shape=box]; +
+
} //---end of digraph---------
(1 row)
- (5)获取树的文本格式显示
select madlib.get_tree('train_output',1,2,false);
seaboxsql=# select madlib.get_tree('train_output',1,2,false);
get_tree
---------------------------------------------------------------------------
------------------------------------- +
- Each node represented by 'id' inside (). +
- Each internal nodes has the split condition at the end, while each +
leaf node has a * at the end. +
- For each internal node (i), its child nodes are indented by 1 level+
with ids (2i+1) for True node and (2i+2) for False node. +
- Number of (weighted) rows for each response variable inside [].' +
The response label order is given as ["don't play", 'play']. +
For each leaf, the prediction is given after the '-->' +
+
------------------------------------- +
(0)[2 7] humidity <= 75 +
(1)[0 6] * --> play +
(2)[2 1] "outlook" in {overcast,rain} +
(5)[0 1] * --> play +
(6)[2 0] * --> don't play +
(1 row)
- (6)为相同的输入数据预测输出类别
drop table if exists prediction_results;
select madlib.forest_predict('train_output',
'dt_golf', 'prediction_results', 'response');
- (7)预测的数据输出类别概率
select id, estimated_class, class
from prediction_results join dt_golf using (id) order by id;
seaboxsql=# select id, estimated_class, class
seaboxsql-# from prediction_results join dt_golf using (id) order by id;
id | estimated_class | class
----+-----------------+------------
1 | don't play | don't play
2 | don't play | don't play
3 | play | play
4 | play | play
5 | play | play
6 | don't play | don't play
7 | play | play
8 | don't play | don't play
9 | play | play
10 | play | play
11 | play | play
12 | play | play
13 | play | play
14 | don't play | don't play
(14 rows)
聚类¶
k-means 方法¶
1. 训练函数
kmeans_random (
rel_source,
expr_point,
k,
fn_dist,
agg_centroid,
max_num_iterations,
min_frac_reassigned
) kmeans(
rel_source,
expr_point,
rel_initial_centroids,
expr_centroid,
fn_dist,
agg_centroid,
max_num_iterations,
min_frac_reassigned
) kmeans(
rel_source,
expr_point,
rel_initial_centroids,
expr_centroid,
fn_dist,
agg_centroid,
max_num_iterations,
min_frac_reassigned
) kmeans(
rel_source,
expr_point,
initial_centroids,
fn_dist,
agg_centroid,
max_num_iterations,
min_frac_reassigned
)
参数说明：
rel_source：含有输入数据对象的表名。数据对象和预定义中心点（如果使用的话）应该使用一个数组类型的列存储，如 FLOAT[]或 INTEGER[]。调用任何以上四种函数进行数据分析时，都会跳过具有 non-finite 值的数据对象，non-finite 值包括 NULL、NaN、infinity 等。
expr_point：包含数据对象的列名。
k：指定要计算的中心点的个数。
fn_dist（可选）：缺省值为‘squared_dist_norm2’，指定计算数据对象与中心点距离的函数名称。可以使用以下距离函数，括号内为均值计算方法。
dist_norm1：1 范数/曼哈顿距离（元素中位数）。
dist_norm2: 2 范式/欧氏距离（元素平均数）。
squared_dist_norm2：欧氏平方距离（元素平均数）。
dist_angle：角距离（归一化数据的元素平均数）。
dist_tanimoto：谷本距离（归一化数据的元素平均数）。
具有 DOUBLE PRECISION[] x, DOUBLE PRECISION[] y -> DOUBLE PRECISION 参数形式的用户自定义函数。
agg_centroid（可选）：缺省值为‘avg’。确定中心点使用的聚合函数名，可以使用以下聚合函数：
avg：平均值（缺省）。
normalized_avg：归一化平均值。
max_num_iterations（可选）：缺省值为 20，指定执行的最大迭代次数。
min_frac_reassigned（可选）：缺省值为 0.001。相邻两次迭代所有中心点相差小于该值时计算完成。
seeding_sample_ratio（可选）：缺省值为 1.0。kmeans++将扫描数据‘k’次，对大数据集会很慢。此参数指定用于确定初始中心点所使用的原始数据集样本比例。当此参数大于0 时（最大值为 1.0），初始中心点在数据均匀分布的随机样本上。注意，k-means 算法最终会在全部数据集上执行。此参数只是为确定初始中心点建立一个子样本，并且只对kmeans++有效。
rel_initial_centroids：包含初始中心点的表名。
expr_centroid：rel_initial_centroids 指定的表中包含中心点的列名。
initial_centroids：包含初始中心点的 DOUBLE PRECISION 数组表达式的字符串。
2. 簇分配函数
closest_column( m, x )
参数说明：
m：DOUBLEPRECISION[][]类型，训练函数返回的中心点。
x：DOUBLEPRECISION[]类型，输入数据。
3. 轮廓系数函数
simple_silhouette( rel_source,
expr_point, centroids, fn_dist
)
参数说明：
rel_source：含有输入数据对象的表名。
expr_point：数据对象列名。
centroids：中心点表达式。
fn_dist：（可选）计算数据点到中心点距离的函数名，缺省值为‘dist_norm2’。
4. 函数示例
- (1)建立测试数据表并装载原始数据
a. 创建原始数据表
drop table if exists t_source;
create table t_source (cust_id int,
amount decimal(10 , 2 ), quantity int,
dt date);
b. 添加 20 条数据
insert into t_source (cust_id,amount,quantity,dt) values
(567,1100.51,2,'2017-07-20'),(568,2003.47,2,'2017-07-20'),
(569,297.91,2,'2017-07-12'),(590,34,10,'2017-07-22'),
(652,1023,5,'2017-07-30'),(653,1211,5,'2017-07-20'),
(654,480,6,'2017-07-23'),(655,100,2,'2017-07-08'),
(656,300,6,'2017-07-24'),(656,46,2,'2017-06-20'),
(657,1000,5,'2017-07-13'),(658,100.02,2,'2017-06-26'),
(659,4497.91,5,'2017-07-01'),(660,45,6,'2017-06-12'),
(661,301.91,10,'2017-07-24'),(662,2031.62,2,'2017-06-12'),
(663,954.77,2,'2017-06-27'),(664,6006.78,3,'2017-06-22'),
(665,25755.7,2,'2017-06-06'),(666,60201.48,2,'2017-07-11');
c.数据预处理
drop table if exists t_source_change;
create table t_source_change (row_id serial,
cust_id int,
amount decimal(10 , 2 ), quantity int,
dt int);
insert into
t_source_change (cust_id, amount, quantity, dt)
select
cust_id,
amount,
quantity,
current_date - dt dt
from
t_source
where
amount < (
select
percentile_cont (0.99) within group (
order by
amount
)
from
t_source
);
select * from t_source_change order by cust_id;
seaboxsql=# select * from t_source_change order by cust_id;
row_id | cust_id | amount | quantity | dt
--------+---------+----------+----------+------
1 | 567 | 1100.51 | 2 | 1909
3 | 568 | 2003.47 | 2 | 1909
5 | 569 | 297.91 | 2 | 1917
2 | 590 | 34.00 | 10 | 1907
4 | 652 | 1023.00 | 5 | 1899
7 | 653 | 1211.00 | 5 | 1909
9 | 654 | 480.00 | 6 | 1906
6 | 655 | 100.00 | 2 | 1921
8 | 656 | 300.00 | 6 | 1905
10 | 656 | 46.00 | 2 | 1939
12 | 657 | 1000.00 | 5 | 1916
14 | 658 | 100.02 | 2 | 1933
11 | 659 | 4497.91 | 5 | 1928
13 | 660 | 45.00 | 6 | 1947
15 | 661 | 301.91 | 10 | 1905
16 | 662 | 2031.62 | 2 | 1947
18 | 663 | 954.77 | 2 | 1932
19 | 664 | 6006.78 | 3 | 1937
17 | 665 | 25755.70 | 2 | 1953
(19 rows)
d.PCA 去相关性
drop table if exists mat;
create table mat (id integer,
row_vec double precision[] );
insert into
mat
select
row_id,
string_to_array(amount || ',' || quantity || ',' || dt, ',') :: double precision [] row_vec
from
t_source_change;
select * from mat;
seaboxsql=# select * from mat;
id | row_vec
----+------------------
3 | {2003.47,2,1909}
7 | {1211,5,1909}
9 | {480,6,1906}
13 | {45,6,1947}
2 | {34,10,1907}
4 | {1023,5,1899}
6 | {100,2,1921}
8 | {300,6,1905}
10 | {46,2,1939}
16 | {2031.62,2,1947}
18 | {954.77,2,1932}
19 | {6006.78,3,1937}
1 | {1100.51,2,1909}
5 | {297.91,2,1917}
11 | {4497.91,5,1928}
15 | {301.91,10,1905}
17 | {25755.7,2,1953}
12 | {1000,5,1916}
14 | {100.02,2,1933}
e.PCA 培训
drop table if exists result_table, result_table_mean;
select madlib.pca_train('mat', 'result_table',
'id',
3
);
seaboxsql=# select * from result_table;
-[ RECORD 1 ]--------+-------------------------------------------------------------------
row_id | 1
principal_components | {-0.9999989959445386,0.0001084856067425366,-0.0014129192432166815}
std_dev | 5851.269194480173
proportion | 0.999993467315375
-[ RECORD 2 ]--------+-------------------------------------------------------------------
row_id | 2
principal_components | {-0.001417077143557377,-0.07549239191217608,0.9971453708741506}
std_dev | 14.777088767817489
proportion | 6.3778568841834456e-06
-[ RECORD 3 ]--------+-------------------------------------------------------------------
row_id | 3
principal_components | {-1.5112673205641691e-06,-0.9971463719004605,-0.07549246984610153}
std_dev | 2.3023740683420866
proportion | 1.548277403953901e-07
seaboxsql=# select * from result_table_mean;
column_mean
----------------------------------------------------------
{2488.926315789474,4.157894736842105,1922.0526315789473}
(1 row)
f. PCA 投影
drop table if exists residual_table, result_summary_table, out_table;
select madlib.pca_project( 'mat',
'result_table', 'out_table', 'id',
'residual_table', 'result_summary_table'
);
seaboxsql=# select * from residual_table;
row_id | row_vec
--------+--------------------------------------------------------------------------
5 | {0,-1.6964207816272392e-13,4.884981308350689e-13}
11 | {0,1.5554224574998443e-13,-4.46753745109163e-13}
15 | {4.547473508864641e-13,-1.6786572132332367e-13,4.831690603168681e-13}
12 | {-2.2737367544323206e-13,-1.1501910535116622e-13,3.304023721284466e-13}
14 | {0,-1.8740564655672642e-13,5.400124791776761e-13}
1 | {2.2737367544323206e-13,-1.0613732115416497e-13,3.0375701953744283e-13}
17 | {3.637978807091713e-12,1.8069989948799048e-12,-5.17630383001233e-12}
3 | {-5.684341886080802e-14,-3.597122599785507e-14,1.0302869668521453e-13}
7 | {0,-9.769962616701378e-14,2.8066438062523957e-13}
9 | {0,-1.5432100042289676e-13,4.440892098500626e-13}
4 | {-2.2737367544323206e-13,-1.1068923555512811e-13,3.161915174132446e-13}
10 | {-4.547473508864641e-13,-1.9273471707492718e-13,5.506706202140776e-13}
18 | {-2.2737367544323206e-13,-1.2079226507921703e-13,3.4638958368304884e-13}
19 | {-4.547473508864641e-13,2.717825964282383e-13,-7.815970093361102e-13}
13 | {-4.547473508864641e-13,-1.9340085088970227e-13,5.577760475716786e-13}
2 | {0,-1.8918200339612667e-13,5.417888360170764e-13}
6 | {4.547473508864641e-13,-1.8562928971732617e-13,5.337952302397753e-13}
8 | {0,-1.6830981053317373e-13,4.831690603168681e-13}
16 | {0,-3.863576125695545e-14,1.1013412404281553e-13}
(19 rows)
seaboxsql=# select * from result_summary_table;
exec_time | residual_norm | relative_residual_norm
------------------+-----------------------+------------------------
428.997278213501 | 6.979225758902398e-12 | 2.461125344369036e-16
(1 row)
seaboxsql=# select * from out_table;
row_id | row_vec
--------+---------------------------------------------------------------
3 | {485.47403657822133,-12.164537472193297,3.137845957936769}
7 | {1277.9435663512102,-11.268023523974911,0.14760447624891482}
9 | {2008.9471796300043,-13.299068636569098,-0.6219597497019089}
4 | {1465.9575067812154,-20.973066729727627,0.9028132929661962}
10 | {2442.899683592481,20.523709650230476,0.8760301229957079}
18 | {1534.140486482665,12.255894858360787,1.4031040175155096}
19 | {-3517.8713971297125,10.00704142079606,0.020860358768377774}
13 | {2443.8888131769063,28.200320126718534,-3.7164936121076257}
2 | {2454.945752844452,-11.97187642731706,-4.685363681924881}
6 | {2388.925170357854,2.4985708087436693,2.234812971790225}
8 | {2188.9484118192645,-14.041140121602922,-0.5461952517381057}
16 | {457.2703739111405,25.687095899433285,0.269089561609837}
5 | {2191.021020747443,-1.7704644122343738,2.5364837562592184}
11 | {-2008.9899788744742,3.019933488703771,-1.2917198504956497}
15 | {2187.038847679438,-14.34581630659582,-4.53478362586053}
12 | {1488.9334640608051,-3.989002650565251,-0.3805239352691568}
14 | {2388.8882153470163,14.464286917690604,1.3289033034116602}
1 | {1388.433129956302,-10.884973494646728,3.139210571876546}
17 | {-23266.794283312225,-1.9488833952485116,-0.2197186842812222}
(19 rows)
g. 归一化
drop table if exists t_source_change_nor;
create table t_source_change_nor as
select
row_id,
string_to_array(amount_nor || ',' || quantity_nor || ',' || dt_nor, ',') :: double precision [] row_vec
from
(
select
row_id,
(row_vec [1] - min_amount) /(max_amount - min_amount) amount_nor,
(row_vec [2] - min_quantity) /(max_quantity - min_quantity) quantity_nor,
(max_dt - row_vec [3]) /(max_dt - min_dt) dt_nor
from
out_table,
(
select
max(row_vec [1]) max_amount,
min(row_vec [1]) min_amount,
max(row_vec [2]) max_quantity,
min(row_vec [2]) min_quantity,
max(row_vec [3]) max_dt,
min(row_vec [3]) min_dt
from
out_table
) t
) t;
select * from t_source_change_nor order by row_id;
seaboxsql=# select * from t_source_change_nor order by row_id;
row_id | row_vec
--------+----------------------------------------------------------------
1 | {0.9585365289677539,0.20515351656641986,0}
2 | {1,0.1830500373848176,1}
3 | {0.9234316296837705,0.1791320431771239,0.00017440104669132187}
4 | {0.9615504921256093,0,0.2858171200591313}
5 | {0.989739234914667,0.3905080277173541,0.07702998221590188}
6 | {0.9974332769713948,0.4773239965551486,0.11558425682354997}
7 | {0.9542409578497121,0.1973637332340975,0.3823346802765932}
8 | {0.989658656815158,0.1409690698824825,0.47100400661725006}
9 | {0.9826606375545546,0.15605998658505138,0.4806868974054606}
10 | {0.9995316775134561,0.8438868874560402,0.28924007562217374}
11 | {0.8264528089684432,0.48792653409201053,0.5662838997558889}
12 | {0.9624437426307189,0.34539138271570835,0.4498308013928942}
13 | {0.9995701325162294,1,0.8761760016084521}
14 | {0.9974318402485765,0.7206612339083318,0.23136175972582548}
15 | {0.9895844175087525,0.13477311299461647,0.9807554952921311}
16 | {0.9223351384422203,0.9488905607697471,0.3668085850003038}
17 | {0,0.3868796629773983,0.429279491408225}
18 | {0.9642012840084916,0.6757509236672075,0.2218787243941842}
19 | {0.7677910926096656,0.6300177826059685,0.39853289290381194}
(19 rows)
- (2)k-means 聚类
a.调用 kmeanspp 函数进行聚类
drop table if exists km_result;
create table km_result (centroids float8[][],cluster_variance float8[],
objective_fn float8,frac_reassigned int8,num_iterations int8 );
insert into km_result;
select * from madlib.kmeanspp
( 't_source_change_nor',
'row_vec',
3,
'madlib.squared_dist_norm2','madlib.avg',20,
0.001
);
\x on;
select
centroids [1] [1] || ', ' || centroids [1] [2] || ', ' || centroids [1] [3] cent1,
centroids [2] [1] || ', ' || centroids [2] [2] || ', ' || centroids [2] [3] cent2,
centroids [3] [1] || ', ' || centroids [3] [2] || ', ' || centroids [3] [3] cent3,
cluster_variance,
objective_fn,
frac_reassigned,
num_iterations
from
km_result;
seaboxsql=# select
centroids [1] [1] || ', ' || centroids [1] [2] || ', ' || centroids [1] [3] cent1,
centroids [2] [1] || ', ' || centroids [2] [2] || ', ' || centroids [2] [3] cent2,
centroids [3] [1] || ', ' || centroids [3] [2] || ', ' || centroids [3] [3] cent3,
cluster_variance,
objective_fn,
frac_reassigned,
num_iterations
from
km_result;
-[ RECORD 1 ]----+-----------------------------------------------------------
cent1 | 0.9613110319992112, 0.2414709534087359, 0.400791795073791
cent2 | 0.9418101942231064, 0.8032012314012159, 0.3973330065424585
cent3 | 0, 0.3868796629773983, 0.429279491408225
cluster_variance | {1.5297466429534106,0.4558229672944433,0}
objective_fn | 1.985569610247854
frac_reassigned | 0
num_iterations | 5
b.调用 simple_silhouette 函数评价聚类质量
select * from madlib.simple_silhouette ( 't_source_change_nor',
'row_vec', (select centroids
from madlib.kmeanspp('t_source_change_nor',
'row_vec', 3,
'madlib.squared_dist_norm2', 'madlib.avg',
20,
0.001)),
'madlib.dist_norm2' );
seaboxsql=# select * from madlib.simple_silhouette ( 't_source_change_nor',
seaboxsql(# 'row_vec', (select centroids
seaboxsql(# from madlib.kmeanspp('t_source_change_nor',
seaboxsql(# 'row_vec', 3,
seaboxsql(# 'madlib.squared_dist_norm2', 'madlib.avg',
seaboxsql(# 20,
seaboxsql(# 0.001)),
seaboxsql(# 'madlib.dist_norm2' );
-[ RECORD 1 ]-----+-------------------
simple_silhouette | 0.5466650767044673
关联规则¶
Aproiri 算法¶
assoc_rules (
support,
confidence,
tid_col,
item_col,
input_table,
output_schema,
verbose,
max_itemset_size
);
参数说明：
support：最小支持度。
confidence：最小置信度。
tid_col：事务 ID 列名。
item_col：项目对应的列名。
input_table：包含输入数据的表名。
output_schema：存储最终结果的模式名称，调用函数前，模式必须已创建。如果此参数为 NULL，则输出到当前模式。
verbose：缺省为 false，指示是否详细打印算法过程中每次迭代的结果。
max_itemset_size：该参数值必须大于等于 2，指定用于产生关联规则的频繁项集的大小， 缺省值是产生全部项集。当项集太大时，可用此参数限制数据集的大小，以减少运行时长。
1. 函数示例
- (1)创建输入数据集
drop table if exists test_data; create table test_data (
trans_id int,
product text
);
insert into test_data values
(1, 'beer'), (1, 'diapers'), (1, 'chips'),
(2, 'beer'), (2, 'diapers'),
(3, 'beer'), (3, 'diapers'),
(4, 'beer'), (4, 'chips'),
(5, 'beer'),
(6, 'beer'), (6, 'diapers'), (6, 'chips'),
(7, 'beer'), (7, 'diapers');
- (2)生成关联规则
select * from madlib.assoc_rules
( .25,.5,'trans_id','product','test_data',null,
true
);
seaboxsql=# select * from madlib.assoc_rules
seaboxsql-# ( .25,.5,'trans_id','product','test_data',null,
seaboxsql(# true
seaboxsql(# );
INFO: finished checking parameters
INFO: finished removing duplicates
INFO: finished encoding items
INFO: finished encoding input table: 0.13438105583190918
INFO: Beginning iteration #1
INFO: 3 Frequent itemsets found in this iteration
INFO: Completed iteration # 1. Time: 0.08190178871154785
INFO: Beginning iteration # 2
INFO: time of preparing data: 0.003918647766113281
INFO: 3 Frequent itemsets found in this iteration
INFO: Completed iteration # 2. Time: 0.045502424240112305
INFO: Beginning iteration # 3
INFO: time of preparing data: 0.003026247024536133
INFO: 1 Frequent itemsets found in this iteration
INFO: Completed iteration # 3. Time: 0.03300905227661133
INFO: Beginning iteration # 4
INFO: time of preparing data: 0.002967357635498047
INFO: 0 Frequent itemsets found in this iteration
INFO: Completed iteration # 4. Time: 0.038477420806884766
INFO: begin to generate the final rules
INFO: 7 Total association rules found. Time: 0.0602717399597168
-[ RECORD 1 ]-+----------------
output_schema | public
output_table | assoc_rules
total_rules | 7
total_time | 00:00:00.395448
- (3)查看规则
select
ruleid id,
pre,
post,
count c,
round(support :: numeric, 4) sup,
round(confidence :: numeric, 4) conf,
round(lift :: numeric, 4) lift,
round(conviction :: numeric, 4) conv
from
assoc_rules
order by
support desc,
confidence desc;
seaboxsql=#select
ruleid id,
pre,
post,
count c,
round(support :: numeric, 4) sup,
round(confidence :: numeric, 4) conf,
round(lift :: numeric, 4) lift,
round(conviction :: numeric, 4) conv
from
assoc_rules
order by
support desc,
confidence desc;
id | pre | post | c | sup | conf | lift | conv
----+-----------------+----------------+---+--------+--------+--------+--------
2 | {diapers} | {beer} | 5 | 0.7143 | 1.0000 | 1.0000 | 0.0000
6 | {beer} | {diapers} | 5 | 0.7143 | 0.7143 | 1.0000 | 1.0000
1 | {chips} | {beer} | 3 | 0.4286 | 1.0000 | 1.0000 | 0.0000
4 | {diapers,chips} | {beer} | 2 | 0.2857 | 1.0000 | 1.0000 | 0.0000
3 | {chips} | {diapers} | 2 | 0.2857 | 0.6667 | 0.9333 | 0.8571
7 | {chips,beer} | {diapers} | 2 | 0.2857 | 0.6667 | 0.9333 | 0.8571
5 | {chips} | {diapers,beer} | 2 | 0.2857 | 0.6667 | 0.9333 | 0.8571
(7 rows)
图算法¶
单源最短路径函数¶
graph_sssp(
vertex_table,
vertex_id,
edge_table,
edge_args,
source_vertex,
out_table
)
参数说明：
vertex_table：包含图中顶点数据的表名。
vertex_id：缺省值为‘id’，vertex_table 表中包含顶点的列名。顶点列必须是 INTEGER
类型，并且数据不能重复，但不要求连续。
edge_table：包含边数据的表名。边表必须包含源顶点、目标顶点和边长三列。边表中允许出现回路，并且构成回路的权重可以不同。
edge_args：是一个逗号分隔字符串，包含多个“name=value”形式的参数，支持的参数如下：src：INTEGER 类型，边表中包含源顶点的列名，缺省值为‘src’。
dest：INTEGER 类型，边表中包含目标顶点的列名，缺省值为‘dest’。
weight：FLOAT8 类型，边表中包含边长的列名，缺省值为‘weight’。
source_vertex：算法的起始顶点。此顶点必须在 vertex_table 表的 vertex_id 列中存在。
out_table：存储单源最短路径的表名，表中的每一行对应一个 vertex_table 表中的顶点， 具有以下列：
vertex_id：目标顶点 ID，使用 vertex_id 入参的值作为列名。
weight：从源顶点到目标顶点最短路径边长合计，使用 weight 入参的值作为列名。
parent：在最短路径上，本顶点的上一节点，列名为‘parent’。
1. 函数示例
- (1)建立表示图的顶点表和边表
drop table if exists vertex, edge;
create table vertex( id integer );
create table edge( src integer, dest integer, weight float8 ); insert into vertex values
(0), (1), (2), (3), (4), (5), (6), (7);
insert into edge values
(0, 1, 1.0), (0, 2, 1.0), (0, 4, 10.0), (1, 2, 2.0),
(1, 3, 10.0), (2, 3, 1.0), (2, 5, 1.0), (2, 6, 3.0),
(3, 0, 1.0), (4, 0, -2.0), (5, 6, 1.0), (6, 7, 1.0);
- (2)计算从 0 顶点到各顶点的最短路径
drop table if exists out;
select madlib.graph_sssp ( 'vertex',
null, 'edge', null, 0,
'out');
select * from out order by id;
seaboxsql=# select * from out order by id;
id | weight | parent
----+--------+--------
0 | 0 | 0
1 | 1 | 0
2 | 1 | 0
3 | 2 | 2
4 | 10 | 0
5 | 2 | 2
6 | 3 | 5
7 | 4 | 6
(8 rows)
- (3)使用非缺省列名
drop table if exists vertex_alt, edge_alt;
create table vertex_alt as select id as v_id from vertex;
create table edge_alt as select src as e_src, dest, weight as e_weight from edge;
- (4)计算从 1 顶点到各顶点的最短路径
drop table if exists out_alt;
select madlib.graph_sssp
( 'vertex_alt', 'v_id', 'edge_alt',
'src=e_src, weight=e_weight', 1,
'out_alt');
select * from out_alt order by v_id;
seaboxsql=# select * from out_alt order by v_id;
v_id | e_weight | parent
------+----------+--------
0 | 4 | 3
1 | 0 | 1
2 | 2 | 1
3 | 3 | 2
4 | 14 | 0
5 | 3 | 2
6 | 4 | 5
7 | 5 | 6
(8 rows)
模型评估¶
交叉验证¶
cross_validation_general(
modelling_func,
modelling_params,
modelling_params_type,
param_explored,
explore_values,
predict_func,
predict_params,
predict_params_type,
metric_func,
metric_params,
metric_params_type,
data_tbl,
data_id,
id_is_random,
validation_result,
data_cols,
fold_num
)
参数说明：
modelling_func：模型训练函数名称。
modelling_params：训练函数参数数组。
modelling_params_type：训练函数参数对应的数据类型名称数组。
param_explored：被寻找最佳值的参数名称，必须是 modelling_params 数组中的元素。
explore_values：候选的参数值。如果为 NULL，只运行一轮交叉验证。
predict_func：预测函数名称。
predict_params：提供给预测函数的参数数组。
predict_params_type：预测函数参数对应的数据类型名称数组。
metric_func：误差度量函数名称。
metric_params：提供给误差度量函数的参数数组。
metric_params_type：误差度量函数参数对应的数据类型名称数组。
data_tbl：包含原始输入数据表名，表中数据将被分成训练集和测试集。
data_id：表示每一行唯一 ID 的列名，可以为空。理想情况下，数据集中的每行数据都包含一个唯一 ID，这样便于将数据集分成训练部分与验证部分。id_is_random 参数值告诉交叉验证函数 ID 值是否是随机赋值。如果原始数据不是随机赋的 ID 值，验证函数为每行生成一个随机 ID。
id_is_random：为 TRUE 时表示提供的 ID 是随机分配的。
validation_result：存储交叉验证函数输出结果的表名，具有以下列：
param_explored：被寻找最佳值的参数名称。与 cross_validation_general()函数的param_explored 入参相同。
average error：误差度量函数计算出的平均误差。
standard deviation of error：标准差。
data_cols：逗号分隔的用于计算的数据列名。为 NULL 时，函数自动计算数据表中的所有列。只有当 data_id 参数为 NULL 时才会用到此参数，否则忽略。如果数据集没有唯一 ID，交叉验证函数为每行生成一个随机 ID，并将带有随机 ID 的数据集复制到一个临时表。设置此参数为自变量和因变量列表，通过只复制计算需要的数据，最小化复制工作量。计算完成后临时表被自动删除。
fold_num：K 值，缺省值为 10，指定验证轮数，每轮验证使用 1/fold_num 数据做验证。
1. 函数示例
- (1)准备输入数据
DROP TABLE houses;
CREATE TABLE houses( id serial not null,
tax integer, bedroom real, bath real,
price integer, size integer, lot INTEGER);
INSERT INTO houses(tax, bedroom, bath, price,size,lot) values
(590,2,1,50000,770,22100),
(1050,3,2,85000,1410,12000),
(20,3,1,22500,1060,3500),
(870,2,2,90000,1300,175000),
(1320,3,2,13300,1500,30000),
(1350,2,1,90500,820,25700),
(2790,3,2.5,260000,2130,25000),
(680,2,1,142500,1170,22000),
(1840,3,2,160000,1500,19000),
(3680,4,2,240000,2790,20000),
(1660,3,1,87000,1030,17500),
(1620,3,2,118600,1250,20000),
(3100,3,2,140000,1760,38000),
(2070,2,3,140000,1550,14000),
(650,3,1.5,65000,1450,12000);
- (2)创建函数并交叉验证
create
or replace function check_cv() returns void as $$ begin execute 'drop table if exists valid_rst_houses';
perform madlib.cross_validation_general(
'madlib.elastic_net_train',
'{%data%, %model%, (price>100000), "array[tax, bath, size, lot]", binomial, 1, lambda, true, null, fista,
"{eta = 2, max_stepsize = 2, use_active_set = t}", null, 2000, 1e-6}' :: varchar [],
'{varchar, varchar, varchar, varchar, varchar, double precision,
double precision, boolean, varchar, varchar, varchar, varchar, integer, double precision}' :: varchar [],
'lambda',
'{0.04, 0.08, 0.12, 0.16, 0.20, 0.24, 0.28, 0.32, 0.36}' :: varchar [],
'madlib.elastic_net_predict',
'{%model%, %data%, %id%, %prediction%}' :: varchar [],
'{text, text, text, text}' :: varchar [],
'madlib.misclassification_avg',
'{%prediction%, %data%, %id%, (price>100000), %error%}' :: varchar [],
'{varchar, varchar, varchar, varchar, varchar}' :: varchar [],
'houses',
'id',
false,
'valid_rst_houses',
'{tax,bath,size,lot, price}' :: varchar [],
3
);
end;
$$ language plpgsql volatile;
- (3)执行函数并查询结果
SELECT check_cv();
SELECT * FROM valid_rst_houses order by lambda;
seaboxsql=# SELECT * FROM valid_rst_houses order by lambda;
lambda | error_rate_avg | error_rate_stddev
--------+------------------------+--------------------------------------------
0.04 | 0.26666666666666666667 | 0.1154700538379251529018297561003914911294
0.08 | 0.33333333333333333333 | 0.2309401076758503058036595122007829822590
0.12 | 0.40000000000000000000 | 0.2000000000000000000000000000000000000000
0.16 | 0.40000000000000000000 | 0.2000000000000000000000000000000000000000
0.2 | 0.40000000000000000000 | 0.2000000000000000000000000000000000000000
0.24 | 0.46666666666666666667 | 0.3055050463303893337725364795818672325989
0.28 | 0.53333333333333333333 | 0.3055050463303893337725364795818672325989
0.32 | 0.60000000000000000000 | 0.3464101615137754587054892683011744733886
0.36 | 0.66666666666666666667 | 0.2309401076758503058036595122007829822590
(9 rows)
预测度量¶
平均绝对误差
mean_abs_error(table_in, table_out,prediction_col, observed_col, grouping_cols)
平均绝对百分误差
mean_abs_perc_error(table_in,table_out, prediction_col, observed_col, grouping_cols)
平均百分比误差
mean_perc_error(table_in, table_out,prediction_col, observed_col, grouping_cols)
均方误差
mean_squared_error(table_in, table_out,prediction_col, observed_col, grouping_cols)
R2 评分
r2_score(table_in, table_out,prediction_col, observed_col, grouping_cols)
整后的 R2 评分
adjusted_r2_score(table_in,table_out, prediction_col, observed_col, num_predictors, training_size,grouping_cols)
与二元分类相关的预测度量
binary_classifier(table_in,table_out, prediction_col, observed_col, grouping_cols)
ROC 曲线下的面积（二元分类）
area_under_roc(table_in,table_out, prediction_col, observed_col, grouping_cols)
多类分类器的混淆矩阵
confusion_matrix(table_in,table_out, prediction_col, observed_col, grouping_cols)
参数说明：
table_in：TEXT，输入表的名称。
table_out：TEXT，输出表的名称。出于一致性，即使在不使用分组的情况下，也会为所有度量输出创建一个表，这可能意味着在某些情况下输出表中只有一个值。
prediction_col：TEXT，输入表中预测值列的名称。
observed_col：TEXT，输入表中观察值列的名称。
num_predictors（只对 adjusted_r2_score）：INTEGER，不含常数项的预测模型中的参数个数。
training_size（只对 adjusted_r2_score）：INTEGER，用于训练的行数，不包括任何空行。
grouping_cols（可选）：TEXT，缺省值为 NULL。输入表中分组列的名称。
1. 函数示例
- (1)创建示例数据
drop table if exists test_set; create table test_set(
pred float8, obs float8
);
insert into test_set values
(37.5,53.1), (12.3,34.2), (74.2,65.4), (91.1,82.1);
- (2)运行平均绝对误差函数
drop table if exists table_out;
select madlib.mean_abs_error( 'test_set', 'table_out', 'pred', 'obs');
select * from table_out;
seaboxsql=# select * from table_out;
mean_abs_error
----------------
13.825
(1 row)
- (3)运行平均绝对百分误差函数
drop table if exists table_out;
select madlib.mean_abs_perc_error( 'test_set', 'table_out', 'pred', 'obs');
select * from table_out;
seaboxsql=# select * from table_out;
mean_abs_perc_error
---------------------
0.2945787936360126
(1 row)
- (4)运行均方误差函数
drop table if exists table_out;
select madlib.mean_squared_error( 'test_set', 'table_out', 'pred', 'obs');
select * from table_out;
seaboxsql=# select * from table_out;
mean_squared_error
--------------------
220.35250000000002
(1 row)
- (5)运行 R2 评分函数
drop table if exists table_out;
select madlib.r2_score( 'test_set', 'table_out', 'pred', 'obs');
select * from table_out;
seaboxsql=# select * from table_out;
r2_score
--------------------
0.2799290884433767
(1 row)
- (6)运行调整后的 R2 评分函数
drop table if exists table_out;
select madlib.adjusted_r2_score( 'test_set', 'table_out', 'pred', 'obs', 3, 100);
select * from table_out;
seaboxsql=# select * from table_out;
r2_score | adjusted_r2_score
--------------------+--------------------
0.2799290884433767 | 0.2574268724572323
(1 row)
- (7)运行 ROC 曲线下的面积函数
drop table if exists table_out;
select madlib.area_under_roc( 'test_set', 'table_out', 'pred', 'obs');
select * from table_out;
seaboxsql=# select * from table_out;
area_under_roc
---------------------------------------------
0.49855072463768115942034671125975473801560
(1 row)