MADlib——基于SQL的数据挖掘解决方案（7）——数据转换之其它转换

用户1148526

发布于 2019-05-25 19:40:13

2.9K0

发布于 2019-05-25 19:40:13

    本篇介绍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’参数中给出的每个不同的值也在输出表中用一个字段表示。输出表的列名是自动生成的。惯用命名方式为下划线‘_’连接的以下各字符串： pivot_values参数中的列名，代表需要执行聚合的数据列。聚合函数名称。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字节的限制时，将创建一个字典输出文件，并向用户发送一个消息。

pivot_values参数中的列名，代表需要执行聚合的数据列。
聚合函数名称。
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，参见[https://archive.ics.uci.edu/ml/datasets/Abalone](https://archive.ics.uci.edu/ml/datasets/Abalone)）是一个机器学习应用标准数据集，涉及根据鲍鱼个体的测量数据来预测鲍鱼的年龄（环的数量）。这是一个多分类（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’的值指定top50%。

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}

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2018年01月09日，如有侵权请联系 cloudcommunity@tencent.com 删除

编程算法

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体分享计划，欢迎热爱写作的你一起参与！

编程算法