greenplum插件tablefunc安装和使用(行列转换)

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

环境清单：

主机1: 内网: 125.10.1.166

系统配置清单：

GPDB配置清单：

★下载对应的postgres版本

--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-4.3.25.1/lib/postgresql/tablefunc.so =:/usr/local/greenplum-db-4.3.25.1/lib/postgresql/
$> gpssh -f all_nomaster "chmod 755 /usr/local/greenplum-db-4.3.25.1/lib/postgresql/tablefunc.so"

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

$> psql -f  /usr/local/greenplum-db-4.3.25.1/share/postgresql/contrib/tablefunc.sql

★确认刷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)

★★使用测试

★刷入测试表和数据

--测试表
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)

参考:

   http://www.bubuko.com/infodetail-2159755.html