3.3 透视表

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. 参数说明

pivot函数中的参数如表3-5所示。

表3-5 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;

结果：

可以看到，pivset表的三列中各有一行的值为NULL。

（2）执行聚合操作并生成透视表

drop table if exists pivout;
select madlib.pivot('pivset', 'pivout', 'id', 'piv', 'val');
select * from pivout order by id;

结果：

id是分组列，piv是需要按值做行转列操作的列，val是需要执行AVG聚合的列，其他参数并未显式赋值，均使用默认值。可以看到，这里的输出中将NULL显示为空串。单从这个简单的例子看，pivot与下面的查询语句结果是一样的：

显然pivot函数更简洁也更灵活，因为实际上我们一般事先不会知道结果有多少列。pivot为用户提供了一种非常简单的方式，实现了所谓的动态行转列功能。

（3）增加源表列并创建相应的视图

结果：

视图定义查询中的coalesce函数将NULL值替换为0，功能类似于Oracle的NVL函数或SQL Server的ISNULL函数。

（4）在视图上应用聚合函数

drop table if exists pivout;
select madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val', 'sum');
select * from pivout order by id;

结果：

和（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;

结果：

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;

结果：

这次我们在调用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;

结果：

从查询结果看到，我们用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两列，从结果可以看出，结果数据由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;

结果：

我们将根据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;

结果：

按照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;

结果：

可以对同一列执行不同的聚合函数，按参数给出的聚合函数顺序，pivot函数为每个聚合函数生成由行转成的列。本例中piv有3个不同值，分别执行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;

结果：

将（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;

结果：

这个例子更复杂些，对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;

结果：

本例结合了前面介绍的所有参数选项，结果行数为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;

结果：

这里生成的字典表包含6列，分别是行转列后生成的数字列名、聚合列名、聚合函数名、原表中需要转置的列名（本例有两列）、行转列后生成的惯用列名。此时查询输出的结果表，生成的列名是以数字ID表示的：

\x on
select * from pivout order by id,id2;

结果：

上一章目录下一章