greenplum行转列-摩杜云开发者社区

项目中需要将150w的数据转为1500列的大宽表数据。
最开始尝试了网上提供的两种方法：

SELECT 'Tim' name, '数学' subject, 'A' grade
UNION
SELECT 'Tim' name, '英语' subject, 'B' grade
UNION
SELECT 'Tim' name, '语文' subject, null grade
UNION
SELECT 'Tom' name, '数学' subject, 'B' grade
UNION
SELECT 'Tom' name, '英语' subject, 'D' grade
UNION
SELECT 'Tom' name, '语文' subject, 'B' grade

max + case when

实际业务中10行*1500列数据查询并插入需要25秒左右（测试服务器性能比较差）

-- insert into ....
select name,
	max(case subject when '数学' then grade end) 数学,
	max(case subject when '英语' then grade end) 英语,
	max(case subject when '语文' then grade end) 语文
FROM (
    SELECT 'Tim' name, '数学' subject, 'A' grade
    UNION
    SELECT 'Tim' name, '英语' subject, 'B' grade
    UNION
    SELECT 'Tim' name, '语文' subject, null grade
    UNION
    SELECT 'Tom' name, '数学' subject, 'B' grade
    UNION
    SELECT 'Tom' name, '英语' subject, 'D' grade
    UNION
    SELECT 'Tom' name, '语文' subject, 'B' grade
) t
GROUP BY t.name

case when的问题是每个case when的语句都会把分组后的数据判断一下，时间复杂度为O²（O为行转列后的列数），非常的慢。case when中注意源数据中不需要计算的数据一定要过滤掉，否则时间会更长（业务系统中忘记过滤数据导致时间膨胀了一倍多）。

tablefunc插件

实际业务中10行*1500列数据只查询需要7-8秒左右（测试服务器性能比较差）

select * from crosstab('select * from(
    SELECT ''Tim'' name, ''数学'' subject, ''A'' grade
    UNION
    SELECT ''Tim'' name, ''英语'' subject, ''B'' grade
    UNION
    SELECT ''Tim'' name, ''语文'' subject, null grade
    UNION
    SELECT ''Tom'' name, ''数学'' subject, ''B'' grade
    UNION
    SELECT ''Tom'' name, ''英语'' subject, ''D'' grade
    UNION
    SELECT ''Tom'' name, ''语文'' subject, ''B'' grade
) t order by name','select unnest( array[''数学'', ''语文'', ''英语'']) ')
AS sales_pivot(name varchar,数学 varchar,英语 varchar,语文 varchar)

crosstab的底层原理不知道是什么，但是不支持直接直接插入只能查询，直接插入不支持分布式运算。如果想配合insert直接插入，查询的数据表必须是复制表，这就需要把需要计算的数据复制到一张DISTRIBUTED REPLICATED的表中才可以。这样就会导致数据都是在一个节点上计算的，greenplum的分布式优势就没有了，同时复制数据也需要消耗时间。也可以把数据查出来再插入到数据库，需要一次服务器和数据库的数据流转。

array_agg

上面的方法都有弊端，官方资料中也没有相关的资料。有一天突然灵光一闪发现可以使用数组的方式，因为数组的定位效率高，性能会提升很多。

实际业务中10行*1500列数据查询并插入需要3秒左右（测试服务器性能比较差）

-- insert into ....
select t.name,t.grades[1] 数学,t.grades[2] 英语,t.grades[3] 语文 from(
	SELECT
	    t.name,
	    array_agg(t.grade order by t.subject) grades
	FROM (
	    SELECT 'Tim' name, '数学' subject, 'A' grade
	    UNION
	    SELECT 'Tim' name, '英语' subject, 'B' grade
	    UNION
	    SELECT 'Tim' name, '语文' subject, null grade
	    UNION
	    SELECT 'Tom' name, '数学' subject, 'B' grade
	    UNION
	    SELECT 'Tom' name, '英语' subject, 'D' grade
	    UNION
	    SELECT 'Tom' name, '语文' subject, 'B' grade
	) t
	GROUP BY t.name
)t;

array_agg的原理是把分组后的数据转换为数组，然后在使用数组的地址取出数据，时间复杂度为O。这种方法需要注意的是需要对列进行排序，转换的时候注意列的顺序。
同理也可以使用split_part+string_agg操作，但是split_part需要切割字符串效率并不会比array_agg高。