行转列插件tablefunc加载

行转列插件tablefunc加载

注意: 此插件只是在测试环境论证过，如若在生产环境使用，请自行评估风险

★下载对应的postgres版本

注意查看查看greenplum的postgresql内核版本，需要完全对应
然后到https://ftp.postgresql.org/pub/source 去下载对应的版本

--gpadmin用户下操作
$> mkdir /home/demo/tablefunc
$> cd /home/demo/tablefunc
$> wget https://ftp.postgresql.org/pub/source/v8.2.15/postgresql-8.2.15.tar.gz

★make 安装对应的插件

注意：需要在子节点上安装插件，否则对应的文件将不会生成

--gpadmin用户下操作
$> cd /home/demo/tablefunc/postgresql-8.2.15/contrib/tablefunc
$> make USE_PGXS=1 install

★发送tablefunc.so到gpdb其他机器上

gpscp -f all_nomaster /usr/local/greenplum-db-6.20.5/lib/postgresql/tablefunc.so =:/usr/local/greenplum-db-6.20.5/lib/postgresql/
gpssh -f all_nomaster "chmod 755 /usr/local/greenplum-db-6.20.5/lib/postgresql/tablefunc.so"

★刷tablefunc插件到指定的数据库

psql -f /usr/local/greenplum-db-6.20.5/share/postgresql/contrib/tablefunc.sql datawarehouse_test

★确认刷tablefunc是否成功

testdb=# \df tablefunc.*                                                                                                                                 
                                              List of functions
  Schema   |    Name     |      Result data type      |             Argument data types             |  Type  
-----------+-------------+----------------------------+---------------------------------------------+--------
 tablefunc | connectby   | SETOF record               | text, text, text, text, integer             | normal
 tablefunc | connectby   | SETOF record               | text, text, text, text, integer, text       | normal
 tablefunc | connectby   | SETOF record               | text, text, text, text, text, integer       | normal
 tablefunc | connectby   | SETOF record               | text, text, text, text, text, integer, text | normal
 tablefunc | crosstab    | SETOF record               | text                                        | normal
 tablefunc | crosstab    | SETOF record               | text, integer                               | normal
 tablefunc | crosstab    | SETOF record               | text, text                                  | normal
 tablefunc | crosstab2   | SETOF tablefunc_crosstab_2 | text                                        | normal
 tablefunc | crosstab3   | SETOF tablefunc_crosstab_3 | text                                        | normal
 tablefunc | crosstab4   | SETOF tablefunc_crosstab_4 | text                                        | normal
 tablefunc | normal_rand | SETOF double precision     | integer, double precision, double precision | normal
(11 rows)

testdb=#

★★使用测试

★刷入测试表和数据

--测试表
create table score(
name varchar,
subject varchar,
score bigint
);
--测试数据
insert into  score values
('Lucy','English',100),
('Lucy','Physics',90),
('Lucy','Math',85),
('Lily','English',76),
('Lily','Physics',57),
('Lily','Math',86),
('David','English',57),
('David','Physics',86),
('David','Math',100),
('Simon','English',88),
('Simon','Physics',99),
('Simon','Math',65);
--原数据查询
select * from score order by 1,2,3;

★sql标准实现

select name,
  sum(case when subject='English' then score else 0 end) as "English",
  sum(case when subject='Physics' then score else 0 end) as "Physics",
  sum(case when subject='Math' then score else 0 end) as "Math"
  from score
  group by name order by name desc;

 name  | English | Physics | Math 
-------+---------+---------+------
 Simon |      88 |      99 |   65
 Lucy  |     100 |      90 |   85
 Lily  |      76 |      57 |   86
 David |      57 |      86 |  100
(4 rows)

★tablefunc实现

select * from 
    crosstab('select name,subject,score from score order by name desc',   
    /* name:分组标准,subject:聚合标准,score:聚合标准下经过计算的值 */
    $$values('English'::text),('Physics'::text),('Math'::text)$$           
    )
    as score(name text,English bigint,Physics bigint,Math bigint);        
    /*显示字段name,English,Physics,Math   
      [name是分组标准;English,Physics,Math是聚合标准产生的字段名]
    */


 name  | english | physics | math 
-------+---------+---------+------
 Simon |      88 |      99 |   65
 Lucy  |     100 |      90 |   85
 Lily  |      76 |      57 |   86
 David |      57 |      86 |  100
(4 rows)