本文介绍了MADlib中的三种核心数据转换方法：透视表、分类变量编码和词干提取。透视表主要用于行列转置，方便报表制作。分类变量编码通过生成指示列，将分类变量转换为适合回归分析的形式。词干提取则简化英文单词至基本形式。
目录
本篇介绍 MADlib 提供的的另外三个常用数据转换方法，即透视表、分类变量编码和词干提取。透视表最主要的用途是行列转置，常被用于报表需求。MADlib 的分类变量编码可以理解为一种特殊的单列变多列的数据转换，对每个类别值新增为一列，列的取值是 0 或 1，表示行对象是否属于该类别。词干提取则用于提取英文单词的词干。
一、透视表
MADlib 提供了一个名为 pivot 的函数，作为一个基础的数据汇总工具。熟悉 SQL 的用户肯定对 pivot 一词不会陌生，它的中文译作透视表或枢轴表，通常用来实现 OLAP 或报表系统中一类常见的行列转置需求。pivot 函数能够对一个表中存储的数据执行基本行转列操作，并将汇总后的结果输出到另一个表中。严格说 pivot 函数并不是一个数据挖掘模型或算法，但它确实使行列转置操作变得更为简单与灵活。
1. 函数语法
pivot(
source_table,
output_table,
index,
pivot_cols,
pivot_values,
aggregate_func,
fill_value,
keep_null,
output_col_dictionary
)2. 参数说明
| 参数名称 | 数据类型 | 描述 |
| source_table | TEXT | 源数据表（或视图）名称。 |
| output_table | TEXT | 包含转置后数据的输出表名。输出表包括在‘index’参数列出的全部字段，‘pivot_cols’参数中给出的每个不同的值也在输出表中用一个字段表示。输出表的列名是自动生成的。惯用命名方式为下划线‘_’连接的以下各字符串：
|
| index | TEXT | 逗号分隔的列名，构成输出透视表的分组列（group by 的列），分组汇总后的数据行存储在输出的透视表中。 |
| pivot_cols | TEXT | 逗号分隔的列名，指示需要按值转成多列的数据列。 |
| pivot_values | TEXT | 逗号分隔的列名，指示需要执行聚合的数据列。 |
| aggregate_func（可选） | TEXT | 缺省值为‘AVG’。逗号分隔的聚合函数列表，可以是 PostgreSQL 内建的聚合函数或者是用户自定义的聚合函数（UDAs）。有可能为每个列值赋予多个聚合函数。这里只允许使用所谓的“严格转换函数”。在 PostgreSQL 数据库中，声明为严格的转换函数将忽略具有 NULL 值的数据行，也就是说聚合操作将不会被应用到含有 NULL 值的行。如果希望对 NULL 输入采取其他行为，应该在调用透视表函数之前进行处理。 |
| fill_value（可选） | TEXT | 缺省值为 NULL。如果指定该值，它将决定如何填充转置操作结果中的 NULL 值。该参数是全局的，将应用于每个聚合函数，在聚合后替换输出表中的 NULL 值。 |
| keep_null（可选） | BOOLEAN | 缺省值为 FALSE。如果设置为 TRUE，行转列后将建立 NULL 值对应的列。 |
| output_col_dictionary（可选） | BOOLEAN | 缺省值为 FALSE。此参数用于处理自动生成的列名长度超过 PostgreSQL 所限制的 63 个字节（经常碰到）的情况。如果为 TRUE，列名将被设置成数字 ID 号，并会创建一个名为输出表名加上 _dictionary 的字典表。如果该参数为 FALSE，将自动生成列名小于 63 字节的列，当列名长度超过 63 字节的限制时，将创建一个字典输出文件，并向用户发送一个消息。 |
表1 pivot 函数参数说明
3. 示例
（1）建立示例数据表并添加数据。
drop table if exists pivset cascade;
create table pivset
( id integer, piv integer, val float8 );
insert into pivset values
(0, 10, 1), (0, 10, 2), (0, 20, 3), (1, 20, 4), (1, 30, 5),
(1, 30, 6), (1, 10, 7), (null, 10, 8), (1, null, 9), (1, 10, null);
select * from pivset order by id, piv; 结果：
id | piv | val
----+-----+-----
0 | 10 | 1
0 | 10 | 2
0 | 20 | 3
1 | 10 |
1 | 10 | 7
1 | 20 | 4
1 | 30 | 6
1 | 30 | 5
1 | | 9
| 10 | 8
(10 rows)可以看到，pivset 表的三列中各有一行的值为 NULL。
（2）执行聚合操作，并生成透视表。
drop table if exists pivout;
select madlib.pivot('pivset', 'pivout', 'id', 'piv', 'val');
select * from pivout order by id; 结果：
id | val_avg_piv_10 | val_avg_piv_20 | val_avg_piv_30
----+----------------+----------------+----------------
0 | 1.5 | 3 |
1 | 7 | 4 | 5.5
| 8 | |
(3 rows)id 作为分组列，piv 作为需要按值做行转列操作的列，val 是需要执行 AVG 聚合的列，其他参数并未显式赋值，均使用缺省值。可以看到，这里的输出中将 NULL 显示为空串。单从这个简单的例子看，pivot 与下面的查询语句结果是一样的：
select id,
sum(case when piv = 10 then val else 0 end)
/sum(case when piv = 10 and val is not null then 1 else null end) as val_avg_piv_10,
sum(case when piv = 20 then val else 0 end)
/sum(case when piv = 20 and val is not null then 1 else null end) as val_avg_piv_20,
sum(case when piv = 30 then val else 0 end)
/sum(case when piv = 30 and val is not null then 1 else null end) as val_avg_piv_30
from pivset
group by id order by id;显然 pivot 函数更简洁也更灵活，因为实际上我们一般事先不会知道结果有多少列。pivot 为用户提供了一种非常简单的方式，实现了所谓的动态行转列功能。
（3）增加源表列并创建相应的视图。
drop view if exists pivset_ext;
create view pivset_ext as
select *,
coalesce(id + (val / 3)::integer, 0) as id2,
coalesce(100*(val / 3)::integer, 0) as piv2,
coalesce(val + 10, 0) as val2
from pivset;
select id,id2,piv,piv2,val,val2 from pivset_ext
order by id,id2,piv,piv2,val,val2;结果：
id | id2 | piv | piv2 | val | val2
----+-----+-----+------+-----+------
0 | 0 | 10 | 0 | 1 | 11
0 | 1 | 10 | 100 | 2 | 12
0 | 1 | 20 | 100 | 3 | 13
1 | 0 | 10 | 0 | | 0
1 | 2 | 20 | 100 | 4 | 14
1 | 3 | 10 | 200 | 7 | 17
1 | 3 | 30 | 200 | 5 | 15
1 | 3 | 30 | 200 | 6 | 16
1 | 4 | | 300 | 9 | 19
| 0 | 10 | 300 | 8 | 18
(10 rows)视图定义查询中的 coalesce 函数将 NULL 值替换为 0，功能类似于 Oracle 的 NVL 函数或 SQLServer 的 ISNULL 函数。
（4）在视图上应用聚合函数。
drop table if exists pivout;
select madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val', 'sum');
select * from pivout order by id; 结果：
id | val_sum_piv_10 | val_sum_piv_20 | val_sum_piv_30
----+----------------+----------------+----------------
0 | 3 | 3 |
1 | 7 | 4 | 11
| 8 | |
(3 rows)和（2）的查询类似，只不过这次我们把集合函数换成了‘sum’。
（5）创建一个用户自定义聚合函数，注意函数必须定义为严格的。
drop function if exists array_add1 (anyarray, anyelement) cascade;
create function array_add1(anyarray, anyelement) returns anyarray as $$
select $1 || $2
$$ language sql strict;
drop aggregate if exists array_accum1 (anyelement);
create aggregate array_accum1 (anyelement) (
sfunc = array_add1,
stype = anyarray,
initcond = '{}'
);
drop table if exists pivout;
select madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val', 'array_accum1');
select * from pivout order by id; 结果：
id | val_array_accum1_piv_10 | val_array_accum1_piv_20 | val_array_accum1_piv_30
----+-------------------------+-------------------------+-------------------------
0 | {1,2} | {3} | {}
1 | {7} | {4} | {5,6}
| {8} | {} | {}
(3 rows)array_add1 函数被声明为严格的，将一个元素添加到一个数组中。array_accum1 以‘val’的值为参数，调用 array_add1 函数生成相应的数组，并忽略 val 列中的 NULL 值。
（6）在转置列中保持 NULL 值。
drop table if exists pivout;
select madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val', 'sum', true);
select * from pivout order by id; 结果：
id | val_sum_piv_null | val_sum_piv_10 | val_sum_piv_20 | val_sum_piv_30
----+------------------+----------------+----------------+----------------
0 | | 3 | 3 |
1 | 9 | 7 | 4 | 11
| | 8 | |
(3 rows)这次我们在调用 pivot 函数时，将 keep_null 参数设置‘true’。从结果中可以看到，比前面的查询多了 val_sum_piv_null 一列，表示 piv 为 NULL 时，val 的聚合值。
（7）替换结果中的 NULL 值。
drop table if exists pivout;
select madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val', 'sum', '999');
select * from pivout order by id; 结果：
id | val_sum_piv_10 | val_sum_piv_20 | val_sum_piv_30
----+----------------+----------------+----------------
0 | 3 | 3 | 999
1 | 7 | 4 | 11
| 8 | 999 | 999
(3 rows)从查询结果看到，我们用 999 替换了聚合结果中的 NULL 值。
（8）使用多个分组列。
drop table if exists pivout;
select madlib.pivot('pivset_ext', 'pivout', 'id,id2', 'piv', 'val');
select * from pivout order by id,id2; 结果：
id | id2 | val_avg_piv_10 | val_avg_piv_20 | val_avg_piv_30
----+-----+----------------+----------------+----------------
0 | 0 | 1 | |
0 | 1 | 2 | 3 |
1 | 0 | | |
1 | 2 | | 4 |
1 | 3 | 7 | | 5.5
1 | 4 | | |
| 0 | 8 | |
(7 rows)我们将分组列定义为 id 和 id2 两列，从结果可以看到，结果数据由 3 行变为 7 行，与下面的查询逻辑上等价，结果相同。
select id, id2,
sum(case when piv = 10 then val else 0 end)
/sum(case when piv = 10 and val is not null then 1 else null end) as val_avg_piv_10,
sum(case when piv = 20 then val else 0 end)
/sum(case when piv = 20 and val is not null then 1 else null end) as val_avg_piv_20,
sum(case when piv = 30 then val else 0 end)
/sum(case when piv = 30 and val is not null then 1 else null end) as val_avg_piv_30
from pivset_ext
group by id, id2 order by id, id2; （9）将多个列进行行转列。
drop table if exists pivout;
select madlib.pivot('pivset_ext', 'pivout', 'id', 'piv, piv2', 'val');
\x on
select * from pivout order by id; 结果：
-[ RECORD 1 ]-----------+----
id | 0
val_avg_piv_10_piv2_0 | 1
val_avg_piv_10_piv2_100 | 2
val_avg_piv_10_piv2_200 |
val_avg_piv_10_piv2_300 |
val_avg_piv_20_piv2_0 |
val_avg_piv_20_piv2_100 | 3
val_avg_piv_20_piv2_200 |
val_avg_piv_20_piv2_300 |
val_avg_piv_30_piv2_0 |
val_avg_piv_30_piv2_100 |
val_avg_piv_30_piv2_200 |
val_avg_piv_30_piv2_300 |
-[ RECORD 2 ]-----------+----
... 我们将根据 piv 和 piv2 两列的值进行行转列，piv 有 3 个不同值，piv2 有 4 个不同值，因此结果中将包含 12 个由行转成的列，共 3 行。
（10）聚合多列。
drop table if exists pivout;
select madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val, val2');
select * from pivout order by id; 结果：
-[ RECORD 1 ]---+-----
id | 0
val_avg_piv_10 | 1.5
val_avg_piv_20 | 3
val_avg_piv_30 |
val2_avg_piv_10 | 11.5
val2_avg_piv_20 | 13
val2_avg_piv_30 |
-[ RECORD 2 ]---+-----
...将按照 id 分组，每个 piv 值（不含 NULL）对应两列，分别代表对 val 与 val2 的聚合值，结果中共有 6 个由行转成的列，共 3 行。
（11）同一列使用多个聚合函数。
drop table if exists pivout;
select madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val', 'avg, sum');
select * from pivout order by id; 结果：
-[ RECORD 1 ]--+----
id | 0
val_avg_piv_10 | 1.5
val_avg_piv_20 | 3
val_avg_piv_30 |
val_sum_piv_10 | 3
val_sum_piv_20 | 3
val_sum_piv_30 |
-[ RECORD 2 ]--+----
... 可以对同一列执行不同的聚合函数，按参数给出的聚合函数顺序，pivot 函数为每个聚合函数生成由行转成的列。本例中 piv 有三个不同值，分别执行 avg 与 sum 两种聚合操作，因此结果中有 6 个由行转成的列，共 3 行。
（12）对不同列使用不同的单一聚合函数。
drop table if exists pivout;
select madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val, val2', 'val=avg, val2=sum');
select * from pivout order by id; 结果：
-[ RECORD 1 ]---+----
id | 0
val_avg_piv_10 | 1.5
val_avg_piv_20 | 3
val_avg_piv_30 |
val2_sum_piv_10 | 23
val2_sum_piv_20 | 13
val2_sum_piv_30 |
-[ RECORD 2 ]---+----
... 将第（10）和第（11）两个例子相结合，这次对两列分别执行不同的聚合操作，结果也是 3 行 6 列。pivot 函数能够对不同的数据列执行不同的聚合操作，为获取用户关心的汇总数据提供了最大的灵活性。
（13）为不同列使用多个聚合函数。
drop table if exists pivout;
select madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val, val2',
'val=avg, val2=[avg,sum]');
select * from pivout order by id; 结果：
-[ RECORD 1 ]---+-----
id | 0
val_avg_piv_10 | 1.5
val_avg_piv_20 | 3
val_avg_piv_30 |
val2_avg_piv_10 | 11.5
val2_avg_piv_20 | 13
val2_avg_piv_30 |
val2_sum_piv_10 | 23
val2_sum_piv_20 | 13
val2_sum_piv_30 |
-[ RECORD 2 ]---+-----
... 这个例子更复杂些，它对 val 列执行单一 avg 聚合，而对 val2 列执行 avg 与 sum 两种聚合操作，与前面的例子同理，本次结果中将包含 9 个由行转成的列，共 3 行。
（14） 使用所有选项。
drop table if exists pivout;
select madlib.pivot('pivset_ext', 'pivout', 'id, id2', 'piv, piv2', 'val, val2',
'val=avg, val2=[avg,sum]', '999', true);
select * from pivout order by id,id2; 结果：
-[ RECORD 1 ]--------------+-----
id | 0
id2 | 0
val_avg_piv_null_piv2_0 | 999
val_avg_piv_null_piv2_100 | 999
val_avg_piv_null_piv2_200 | 999
val_avg_piv_null_piv2_300 | 999
...
val2_avg_piv_null_piv2_0 | 999
val2_avg_piv_null_piv2_100 | 999
val2_avg_piv_null_piv2_200 | 999
val2_avg_piv_null_piv2_300 | 999
...
val2_sum_piv_null_piv2_0 | 999
val2_sum_piv_null_piv2_100 | 999
val2_sum_piv_null_piv2_200 | 999
val2_sum_piv_null_piv2_300 | 999
...
-[ RECORD 2 ]--------------+-----
... 本例结合了前面介绍的所有参数选项，结果行数为 7（按 id、id2 两列分组），列数为 48，等于 piv 的不同值个数 4（包括一个 NULL 列）* piv2 的不同值个数 4 * 聚合操作列数 3（val 列 avg 聚合，val2 列 avg 和 sum 聚合）。
（15） 创建一个输出列名字典表。
drop table if exists pivout, pivout_dictionary;
select madlib.pivot('pivset_ext', 'pivout', 'id, id2', 'piv, piv2', 'val, val2',
'val=avg, val2=[avg,sum]', '999', true, true);
\x off
select * from pivout_dictionary; 结果：
__pivot_cid__ | pval | agg | piv | piv2 | col_name
---------------+------+-----+-----+------+------------------------------
__p_0__ | val | avg | | 0 | "val_avg_piv_null_piv2_0"
__p_1__ | val | avg | | 100 | "val_avg_piv_null_piv2_100"
__p_2__ | val | avg | | 200 | "val_avg_piv_null_piv2_200"
__p_3__ | val | avg | | 300 | "val_avg_piv_null_piv2_300"
...
__p_47__ | val2 | sum | 30 | 300 | "val2_sum_piv_30_piv2_300"
(48 rows) 这里生成的字典表包含 6 列，分别是行转列后生成的数字列名、聚合列名、聚合函数名、原表中需要转置的列名（本例有两列）、行转列后生成的惯用列名。此时查询输出的结果表，生成的列名是以数字 ID 表示的：
dm=# \x on
Expanded display is on.
dm=# select * from pivout order by id,id2;
-[ RECORD 1 ]--
id | 0
id2 | 0
__p_0__ | 999
__p_1__ | 999
__p_2__ | 999
__p_3__ | 999
...
__p_47__ | 999
-[ RECORD 2 ]--
...
dm=#二、分类变量编码
在统计学中，分类变量是指具有有限的、通常是固定数量值的变量，其变量值是定性的，表现为互不相容的类别或属性。若按每个分类变量值分组，每个被观察的数据点或记录属于某一特定组。在计算机科学和一些数学分支中，分类变量被称为枚举类型。有时将分类变量的每一个可能值称为级别，与随机分类变量相关的概率分布称为分类分布。
回归分析时需要特别注意分类变量，与双值或连续变量不同，分类变量不能直接代入回归方程。例如，假设有一个名为 race 的变量，其编码为 1=Hispanic，2=Asian，3=Black，4=White，那么在回归中直接代入 race 变量的数字值，将会得出线性效果，但这并不是分类本来的意义。因此，像这样的分类变量需要被编码成一系列指示变量，然后就可以将指示值输入到回归模型中。从数据库的角度来看，所谓分类变量编码，简单说就是为分类列的每个值创建一个新的分类编码列，然后将数据的类别指示值（通常就是 0 或 1）赋给各个分类编码列。分类变量编码是一种将分类属性二元化的技术。有多种编码系统用于分类编码，常见的包括 one-hot、dummy、effects、orthogonal 和 Helmert 等，MADlib 当前支持 one-hot 和 dummy 编码技术。
- 当需要用一组特定的预测变量与其他预测变量组作比较时，通常使用哑编码（dummy coding），与之比较的变量组称为参照组。
- One-hot 编码与哑编码类似，两者的区别是前者为每种分类值建立数字类型的 0/1 指示列。在每行数据中（对应一个数据点），只有一个分类编码列的值可以为 1。One-hot 编码中没有参考类别。
1. 函数语法
encode_categorical_variables (
source_table,
output_table,
categorical_cols,
categorical_cols_to_exclude, -- 可选
row_id, -- 可选
top, -- 可选
value_to_drop, -- 可选
encode_null, -- 可选
output_type, -- 可选
output_dictionary, -- 可选
distributed_by -- 可选
)2. 参数说明
| 参数名称 | 数据类型 | 描述 |
| source_table | VARCHAR | 包含分类数据的源表名，要对该表中的分类进行编码。 |
| output_table | VARCHAR | 结果表名。如果在‘row_id’参数指定了索引（主键）列，则输出表中只有‘row_id’索引列和分类编码列。如果没有指定‘row_id’，那么除分类编码列外，‘output_table’结果表中还包含‘source_table’表中的所有其他列。 |
| categorical_cols | VARCHAR | 逗号分隔的字符串，由需要编码的分类列名组成。可以使用通配符‘*’，表示除‘categorical_cols_to_exclude’和‘row_id’参数指定的列以外，其他列都编码。注意，当给此参数赋予‘*’时，所有但也只有布尔、整数和文本列都被视为分类列而被编码。 |
| categorical_cols_to_exclude（可选） | VARCHAR | 逗号分隔的字符串，指定不编码的列。只适用于当‘categorical_cols’ = ‘*’时。 |
| row_id（可选） | VARCHAR | 逗号分隔列名，对应于源表的主键（HAWQ 中只代表逻辑主键）。该参数决定‘output_table’输出表中的列。row_id 指定的列不会被编码，但会出现在结果表中。如果想在结果表中既想看到原始分类变量，又想看到编码后的分类变量，那么可以在‘row_id’参数中包含这些分类变量，这有点像在表的主键中增加冗余的分类列。需要注意的是，如果‘categorical_cols’参数值为‘*’，这种方法不能正常工作，因为此情况下‘row_id’参数中的列根本就不会被编码。 |
| top（可选） | VARCHAR | 缺省值为 NULL。如果参数值是整数，按分类值出现的比例对前 n 个值进行编码。如果参数值浮点型的范围（0.0, 1.0），按分类值出现的比例，编码指定的部分值，例如，0.1 表示编码前 10% 的分类。该参数可以作为所有分类列的全局参数，也可作为一个字典，为每个分类变量指定单独的值。设置为 NULL 时，为所有分类列编码所有的值。 |
| value_to_drop（可选） | VARCHAR | 缺省值为 NULL。该参数只针对哑编码方法。对于哑编码方式，指示每个分类变量，期望在结果表中去除的参考值。可以作为一个全局参数，作用于所有分类列，也可以在一个逗号分隔字符串中，包含‘name=value’形式的条目，其中‘name’是列名，‘value’是要被删除的参考值。对于 one-hot 编码方式，设置为缺省值 NULL。 注意，如果同时指定了‘value_to_drop’和‘top’参数，‘value_to_drop’的优先级高于‘top’，‘top’应用于删除后剩余的分类值。 |
| encode_null（可选） | BOOLEAN | 缺省值为 FALSE。指定是否将 NULL 视为一个分类变量值。如果该参数值为 TRUE，对空值创建编码列。如果为 FALSE，不对空值进行编码。 |
| output_type（可选） | VARCHAR | 缺省值为‘column’。该参数控制指示符变量的输出格式。设置为‘column’时，为每个指示符变量创建一列。PostgreSQL 限制一个表中的列数最多为 1600。如果总的指示符列数超过这一限制，可以将该参数指定为‘array’或‘svec’，‘array’将指示符列联合成数组，‘svec’将输出的编码数据转化为 madlib.svec 类型。由于任何单个元组的数组输出都是稀疏的（每个分类列只有一个非零元素），‘svec’输出类型的存储效率最高。如果后续处理需要使用数组，如与其他非分类的特征相关联时，可以使用‘array’输出类型。数组中元素的顺序与‘categorical_cols’参数中列的顺序相同。当‘output_type’为‘array’或‘svec’时，会创建一个字典表，在字典表中定义数组元素索引。字典表的名称为‘output_table’参数值后加上‘_dictionary’。 |
| output_dictionary（可选） | BOOLEAN | 缺省值为 FALSE。该参数用于处理自动生成的列名长度超过 PostgreSQL 限制的 63 字节的情况。如果设置为 TRUE，列名将包含数字 ID，同时创建一个字典表，名称为‘output_table’参数值后加上‘_dictionary’。如果该参数值为 FALSE，将自动生成列名小于 63 字节的列，当列名长度超过 63 字节的限制时，会创建一个字典输出文件，并向用户发送一个消息。 |
| distributed_by（可选） | VARCHAR | 缺省值为 NULL，用于输出表的数据分布策略的列。支持两种数据分布策略，随机与哈希。该参数不应用于 PostgreSQL 平台。 l NULL：缺省值，如果提供了‘row_id’参数，将参数值中的列作为数据分布的哈希键，否则使用源表分配政策。 l 逗号分隔的列名：这些列被用于数据分布的哈希键。 l RANDOMLY：使用随机分布策略，要求输出表中不能有名为‘randomly’的列。 |
表2 encode_categorical_variables 函数参数说明
3. 示例
（1）使用鲍鱼数据集的子集
鲍鱼数据集（Abalone Dataset，参见“UCI Machine Learning Repository”）是一个机器学习应用标准数据集，涉及根据鲍鱼个体的测量数据来预测鲍鱼的年龄（环的数量）。这是一个多分类（multi-class classification）问题，但也可以抽象为回归问题。该数据集有 4177 个观察值，8 个输入变量和 1 个输出变量，每个类的观察值数量不均等。变量名包括：性别（M，F，I）、长度、直径、高度、总重量、剥壳重量、内脏重量、壳重、环的数量。
drop table if exists abalone;
create table abalone (
id serial,
sex character varying,
length double precision,
diameter double precision,
height double precision,
rings int
);
insert into abalone (sex, length, diameter, height, rings) values
('m', 0.455, 0.365, 0.095, 15), ('m', 0.35, 0.265, 0.09, 7),
('f', 0.53, 0.42, 0.135, 9), ('m', 0.44, 0.365, 0.125, 10),
('i', 0.33, 0.255, 0.08, 7), ('i', 0.425, 0.3, 0.095, 8),
('f', 0.53, 0.415, 0.15, 20), ('f', 0.545, 0.425, 0.125, 16),
('m', 0.475, 0.37, 0.125, 9), (null, 0.55, 0.44, 0.15, 19),
('f', 0.525, 0.38, 0.14, 14), ('m', 0.43, 0.35, 0.11, 10),
('m', 0.49, 0.38, 0.135, 11), ('f', 0.535, 0.405, 0.145, 10),
('f', 0.47, 0.355, 0.1, 10), ('m', 0.5, 0.4, 0.13, 12),
('i', 0.355, 0.28, 0.085, 7), ('f', 0.44, 0.34, 0.1, 10),
('m', 0.365, 0.295, 0.08, 7), (null, 0.45, 0.32, 0.1, 9); （2）使用 one-hot 编码创建新表，通过对‘sex’列编码，替换‘f’、‘m’和‘i’三个值，缺省不对 NULL 编码。
drop table if exists abalone_out, abalone_out_dictionary;
select madlib.encode_categorical_variables (
'abalone', -- 源表
'abalone_out', -- 输出表
'sex' -- 编码列
);
select * from abalone_out order by id; 结果：
id | length | diameter | height | rings | sex_f | sex_i | sex_m
----+--------+----------+--------+-------+-------+-------+-------
1 | 0.455 | 0.365 | 0.095 | 15 | 0 | 0 | 1
2 | 0.35 | 0.265 | 0.09 | 7 | 0 | 0 | 1
...
20 | 0.45 | 0.32 | 0.1 | 9 | 0 | 0 | 0
(20 rows)（3）包含 NULL 值的 one-hot 编码，注意新增的‘sex_NULL’列。
drop table if exists abalone_out, abalone_out_dictionary;
select madlib.encode_categorical_variables (
'abalone', -- 源表
'abalone_out', -- 输出表
'sex', -- 编码列
null, -- 不编码列
null, -- 主键列
null, -- top
null, -- 哑编码的删除值
true -- 是否对空值编码
);
select * from abalone_out order by id; 结果：
id | length | diameter | height | rings | sex_f | sex_i | sex_m | sex_NULL
----+--------+----------+--------+-------+-------+-------+-------+----------
1 | 0.455 | 0.365 | 0.095 | 15 | 0 | 0 | 1 | 0
2 | 0.35 | 0.265 | 0.09 | 7 | 0 | 0 | 1 | 0
...
20 | 0.45 | 0.32 | 0.1 | 9 | 0 | 0 | 0 | 1
(20 rows) （4）对源表中的所有列编码。并且指定‘id’列作为主键列，使得输出表中只包含主键和分类编码列。
drop table if exists abalone_out, abalone_out_dictionary;
select madlib.encode_categorical_variables (
'abalone', -- 源表
'abalone_out', -- 输出表
'*', -- 编码列
null, -- 不编码列
'id' -- 主键列
);
\x on
select * from abalone_out order by id; 结果：
-[ RECORD 1 ]
id | 1
sex_f | 0
sex_i | 0
sex_m | 1
rings_7 | 0
rings_8 | 0
rings_9 | 0
rings_10 | 0
rings_11 | 0
rings_12 | 0
rings_14 | 0
rings_15 | 1
rings_16 | 0
rings_19 | 0
rings_20 | 0
-[ RECORD 2 ]
...
-[ RECORD 20 ]
id | 20
sex_f | 0
sex_i | 0
sex_m | 0
rings_7 | 0
rings_8 | 0
rings_9 | 1
rings_10 | 0
rings_11 | 0
rings_12 | 0
rings_14 | 0
rings_15 | 0
rings_16 | 0
rings_19 | 0
rings_20 | 0 源表有 id、sex、length、diameter、height、rings 六列，由于指定了 id 列为索引列，它不被编码；length、diameter、height 这三列是 doubleprecision 类型，而编码只针对布尔、整数和文本数据类型，因此这三列也不被编码，对剩下的 sex 列的 3 个值，rings 列的 11 个值进行编码，结果表共有包含 id 在内的 15 列。
（5）只对 top n 的分类值编码，把其他值分组到另一个杂项列中。Top 值或针对所有编码列，或按列指定。作为后者的一个例子，这里对‘sex’的值指定 top 2，而对‘rings’的值指定 top 50%。
drop table if exists abalone_out, abalone_out_dictionary;
select madlib.encode_categorical_variables (
'abalone', -- 源表
'abalone_out', -- 输出表
'*', -- 分类列
null, -- 不分类列
'id', -- 主键列
'sex=2, rings=0.5' -- top值
);
\x off
select * from abalone_out order by id; 结果：
id | sex_m | sex_f | sex__MISC__ | rings_10 | rings_7 | rings_9 | rings__MISC__
----+-------+-------+-------------+----------+---------+---------+---------------
1 | 1 | 0 | 0 | 0 | 0 | 0 | 1
2 | 1 | 0 | 0 | 0 | 1 | 0 | 0
...
20 | 0 | 0 | 0 | 0 | 0 | 1 | 0
(20 rows) 可以看到，结果表中对 sex 列的‘m’和‘f’两个值创建了编码列，其他值创建了一个杂项编码列；同样对 rings 列的 10、7、9 三个值创建了编码列，其他值创建了一个杂项编码列。
下面验证一下占前两位的 sex 值是‘m’和‘f’：
dm=# select sex, count(*) c from abalone group by sex order by count(*) desc limit 2;
sex | c
-----+---
m | 8
f | 7
(2 rows)再验证一下 rings 取值占总记录数 50% 的值为 10、7、9：
dm=# select rings
dm-# from (with t1 as
dm(# (select row_number() over (order by c desc) row_id,rings,c
dm(# from (select rings,count(*) c from abalone group by rings) t)
dm(# select rings,(select sum(c) from t1 where row_id<t2.row_id)/cc s from t1 as t2,
dm(# (select count(*) cc from abalone) t3) t4
dm-# where coalesce(s,0.0) <=0.5;
rings
-------
10
7
9
(3 rows)（6）如希望在输出结果表里同时看到原始分类和及其编码，可以在 index 参数中包含分类变量列。正如前面所讲，如果‘categorical_cols’参数值为‘*’，这种方法不可行，因为此情况下‘row_id’参数中指定的列根本就不会被编码。
drop table if exists abalone_out, abalone_out_dictionary;
select madlib.encode_categorical_variables (
'abalone', -- 源表
'abalone_out', -- 输出表
'sex, rings', -- 编码列
null, -- 不编码列
'id, sex, rings' -- 主键列
);
\x on
select * from abalone_out order by id; 结果：
-[ RECORD 1 ]
id | 1
sex | m
rings | 15
sex_f | 0
sex_i | 0
sex_m | 1
rings_7 | 0
rings_8 | 0
rings_9 | 0
rings_10 | 0
rings_11 | 0
rings_12 | 0
rings_14 | 0
rings_15 | 1
rings_16 | 0
rings_19 | 0
rings_20 | 0
-[ RECORD 2 ]
...
-[ RECORD 20 ]
id | 20
sex |
rings | 9
sex_f | 0
sex_i | 0
sex_m | 0
rings_7 | 0
rings_8 | 0
rings_9 | 1
rings_10 | 0
rings_11 | 0
rings_12 | 0
rings_14 | 0
rings_15 | 0
rings_16 | 0
rings_19 | 0
rings_20 | 0（7）对于哑编码方式，我们将‘sex’中的‘i’值作为参考值，这里使用了‘value_to_drop’参数。
drop table if exists abalone_out, abalone_out_dictionary;
select madlib.encode_categorical_variables (
'abalone', -- 源表
'abalone_out', -- 输出表
'*', -- 编码列
'rings', -- 不编码列
'id', -- 主键列
null, -- top
'sex=i' -- 哑编码的删除值
);
\x off
select * from abalone_out order by id; 结果：
id | sex_f | sex_m
----+-------+-------
1 | 0 | 1
2 | 0 | 1
...
20 | 0 | 0
(20 rows) 结果表中只有三列，分别是 id 列和 sex 值为‘m’、‘f’的两个编码列。id 为 5、6、17 的三行，编码的两列值都是 0，因为这三行的sex值为‘i’，id 为 10、20 的两行，编码的两列值也都是 0，因为这两行的 sex 值为空。
（8）为源表中的两个分类变量创建输出数组。
drop table if exists abalone_out, abalone_out_dictionary;
select madlib.encode_categorical_variables (
'abalone', -- 源表
'abalone_out', -- 输出表
'*', -- 编码列
null, -- 不编码列
'id', -- 主键列
null, -- top
null, -- 哑编码的删除值
null, -- 是否对空值编码
'array' -- 输出类型
);
select * from abalone_out order by id; 结果：
id | __encoded_variables__
----+-------------------------------
1 | {0,0,1,0,0,0,0,0,0,0,1,0,0,0}
2 | {0,0,1,1,0,0,0,0,0,0,0,0,0,0}
...
20 | {0,0,0,0,0,1,0,0,0,0,0,0,0,0}
(20 rows) 对比（4）的结果，这里用（4）的结果列构成了结果数组，注意结果数组元素的排列顺序，先按分类列的出现顺序 sex、rings，再按 sex、rings 两列分类值的升序顺序。
查看带有数组元素索引的字典表。
dm=# select * from abalone_out_dictionary;
encoded_column_name | index | variable | value
-----------------------+-------+----------+-------
__encoded_variables__ | 1 | sex | f
__encoded_variables__ | 2 | sex | i
__encoded_variables__ | 3 | sex | m
__encoded_variables__ | 4 | rings | 7
__encoded_variables__ | 5 | rings | 8
__encoded_variables__ | 6 | rings | 9
__encoded_variables__ | 7 | rings | 10
__encoded_variables__ | 8 | rings | 11
__encoded_variables__ | 9 | rings | 12
__encoded_variables__ | 10 | rings | 14
__encoded_variables__ | 11 | rings | 15
__encoded_variables__ | 12 | rings | 16
__encoded_variables__ | 13 | rings | 19
__encoded_variables__ | 14 | rings | 20
(14 rows)（9）创建输出字典表。
drop table if exists abalone_out, abalone_out_dictionary;
select madlib.encode_categorical_variables (
'abalone', -- 源表
'abalone_out', -- 输出表
'*', -- 编码列
null, -- 不编码列
'id', -- 主键列
null, -- top
null, -- 哑编码的删除值
null, -- 是否对空值编码
null, -- 输出类型
true -- 是否创建字典表
);
\x on
select * from abalone_out order by id; 结果：
-[ RECORD 1 ]
id | 1
sex_1 | 0
sex_2 | 0
sex_3 | 1
rings_1 | 0
rings_2 | 0
rings_3 | 0
rings_4 | 0
rings_5 | 0
rings_6 | 0
rings_7 | 0
rings_8 | 1
rings_9 | 0
rings_10 | 0
rings_11 | 0
-[ RECORD 2 ]
...
-[ RECORD 20 ]
id | 20
sex_1 | 0
sex_2 | 0
sex_3 | 0
rings_1 | 0
rings_2 | 0
rings_3 | 1
rings_4 | 0
rings_5 | 0
rings_6 | 0
rings_7 | 0
rings_8 | 0
rings_9 | 0
rings_10 | 0
rings_11 | 0查看字典表，该表定义输出表中的数字编码列。
dm=# \x off
Expanded display is off.
dm=# select * from abalone_out_dictionary order by encoded_column_name;
encoded_column_name | index | variable | value
---------------------+-------+----------+-------
"rings_1" | 1 | rings | 7
"rings_10" | 10 | rings | 19
"rings_11" | 11 | rings | 20
"rings_2" | 2 | rings | 8
"rings_3" | 3 | rings | 9
"rings_4" | 4 | rings | 10
"rings_5" | 5 | rings | 11
"rings_6" | 6 | rings | 12
"rings_7" | 7 | rings | 14
"rings_8" | 8 | rings | 15
"rings_9" | 9 | rings | 16
"sex_1" | 1 | sex | f
"sex_2" | 2 | sex | i
"sex_3" | 3 | sex | m
(14 rows)（10）选择数据分布策略，例如随机分布。
drop table if exists abalone_out, abalone_out_dictionary;
select madlib.encode_categorical_variables (
'abalone', -- 源表
'abalone_out', -- 输出表
'*', -- 编码列
null, -- 不编码列
'id', -- 主键列
null, -- top
null, -- 哑编码的删除值
null, -- 是否对空值编码
null, -- 输出类型
null, -- 是否创建字典表
'randomly' -- 数据分布策略
); 查看输出表的数据分布策略。
dm=# \d abalone_out
Append-Only Table "public.abalone_out"
Column | Type | Modifiers
----------+---------+-----------
id | integer |
sex_f | integer |
...
rings_20 | integer |
Compression Type: None
Compression Level: 0
Block Size: 32768
Checksum: f
Distributed randomly（11）如果需要对 FLOAT 数据类型的变量进行编码，可以在函数调用中按以下方式进行显式数据类型转换。
drop table if exists abalone_out, abalone_out_dictionary;
select madlib.encode_categorical_variables (
'abalone', -- 源表
'abalone_out', -- 输出表
'height::text' -- 编码列
);
\x on
select * from abalone_out; 结果：
-[ RECORD 1 ]------+------
id | 1
sex | m
length | 0.455
diameter | 0.365
height | 0.095
rings | 15
height::text_0.08 | 0
height::text_0.085 | 0
height::text_0.09 | 0
height::text_0.095 | 1
height::text_0.1 | 0
height::text_0.11 | 0
height::text_0.125 | 0
height::text_0.13 | 0
height::text_0.135 | 0
height::text_0.14 | 0
height::text_0.145 | 0
height::text_0.15 | 0
-[ RECORD 2 ]------+------
...
-[ RECORD 20 ]-----+------
id | 20
sex |
length | 0.45
diameter | 0.32
height | 0.1
rings | 9
height::text_0.08 | 0
height::text_0.085 | 0
height::text_0.09 | 0
height::text_0.095 | 0
height::text_0.1 | 1
height::text_0.11 | 0
height::text_0.125 | 0
height::text_0.13 | 0
height::text_0.135 | 0
height::text_0.14 | 0
height::text_0.145 | 0
height::text_0.15 | 0三、词干提取
1. 什么是词干
词干（Stem）是由词根和词缀构成的，是一个词除去词尾的部分。词干可能是由词根构成的，也可以是词根加上词缀构成的。例如：workers，其中 s 是词尾，work 是词根，er 是词缀，这个词除去词尾，剩下的 worker 就是词干。而 worker 的词干还是 worker，works 的词干去掉词尾s后的 work。
词干提取简单说就是找出单词中的词干部分。该技术在英文网站优化中的作用比较明显，对中文网站优化也有一定的借鉴意义。在网页内容的写作中，可尽量把同一词干衍生出的不同形式自然融合在句子当中，而不要仅使用单一形式。也就是说，如果目标关键词是 work，最好 working、worked、worker、works、workers 等这些词也要适当出现。
这样做至少有两个好处，一是有助于搜索引擎建立网页主题概念。因为这些相同词干的词其实意义极为相近。二是用户在搜索的时候，有可能使用各种各样的变体，不一定都是作者最喜欢用的那个词。如果在网页中出现各种变体，网页有更高的可能性在不同搜索词结果中出现。
2. 波特词干算法
应用最为广泛的、中等复杂程度的、基于后缀剥离的词干提取算法是波特词干算法，也叫波特词干器（Porter Stemmer）。最初的词干提取算法由 Martin Porter 编写和维护。比较热门的检索系统包括 Lucene、Whoosh 等中的词干过滤器就是采用的波特词干算法。MADlib 中的词干提取函数采用的也是这个算法。
在实际处理中，波特词干算法需要分六步走：
- 处理复数，以及 ed 和 ing 结束的单词。
- 如果单词中包含元音，并且以 y 结尾，将 y 改为 i。
- 将双后缀的单词映射为单后缀。
- 处理 -ic-，-full，-ness 等等后缀。和步骤 3 有着类似的处理。
- 在 <c>vcvc<v> 情形下，去除 -ant，-ence 等后缀。其中 c 代表辅音序列，v 代表元音序列，<..> 表示任意存在。
- 也就是最后一步，在 m()>1 的情况下，移除末尾的“e”。
最后要注意的是，传入的单词必须是小写。波特词干算法的官方网站上，有各个语言的实现版本（其实都是 C 标准的各个翻译形式），要应用到实际生产中可以直接下载对应的版本。
3. MADlib 中的词干提取函数
MADlib 的 porter_stemmer 模块对输入文本执行基本的词干提取操作，它是某些需要词干分析器的机器学习算法的支持模块。该模块当前仅支持英文单词。词干提取函数提供了一个 SQL 接口，实现波特词干提取算法（Porter Stemming Algorithm）。
（1）语法
porter_stemmer 模块中有以下两个函数，语法与参数都非常简单：
- text stem_token (text token)
- text[] stem_token_arr (text[]token_arr)
stem_token 返回输入单词的词干，输入为 NULL 时返回 NULL。stem_token_arr 返回输入单词数组对应的词干数组，空数组返回 NULL。
（2）联机帮助
可以执行下面的查询获得词干提取函数的联机帮助。
select madlib.stem_token();
select madlib.stem_token_arr(); 4. 词干提取函数示例
（1）创建示例表并添加数据
drop table if exists token_tbl;
create table token_tbl ( id integer, word text );
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, 'knights'), (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'); （2）返回单词的词干
\x off
select word, madlib.stem_token(word) from token_tbl; 结果：
word | stem_token
----------+------------
kneel | kneel
kneeled | kneel
kneeling | kneel
...
(30 rows)（3）处理输入的单词数组
select madlib.stem_token_arr(array_agg(word order by word)) from token_tbl; 结果：
stem_token_arr
-------------------------------------------------------------------------------
{kneel,kneel,kneel,kneel,knee,knell,knelt,knew,knick,knif,knife,knight,knight,knight,knit,knit,knit,knit,knive,knob,knob,knock,knock,knocker,knocker,knock,knock,knopp,knot,knot}
扫一扫
964
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
