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

环境清单:

  1. 主机1: 内网: 125.10.1.166

系统配置清单:

IP HOSTNAME CPU MEM DISK RELEASE
125.10.1.166 demo166 2核 2G 5G CentOS release 6.8 (Final)

GPDB配置清单:

IP HOSTNAME GPDB RELEASE
125.10.1.166 demo166 PostgreSQL 8.2.15 (Greenplum Database 4.3.25.1 build 1)

★下载对应的postgres版本

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

★make

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

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

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

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

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

★确认刷tablefunc是否成功

  1. testdb=# \df tablefunc.*
  2. List of functions
  3. Schema | Name | Result data type | Argument data types | Type
  4. -----------+-------------+----------------------------+---------------------------------------------+--------
  5. tablefunc | connectby | SETOF record | text, text, text, text, integer | normal
  6. tablefunc | connectby | SETOF record | text, text, text, text, integer, text | normal
  7. tablefunc | connectby | SETOF record | text, text, text, text, text, integer | normal
  8. tablefunc | connectby | SETOF record | text, text, text, text, text, integer, text | normal
  9. tablefunc | crosstab | SETOF record | text | normal
  10. tablefunc | crosstab | SETOF record | text, integer | normal
  11. tablefunc | crosstab | SETOF record | text, text | normal
  12. tablefunc | crosstab2 | SETOF tablefunc_crosstab_2 | text | normal
  13. tablefunc | crosstab3 | SETOF tablefunc_crosstab_3 | text | normal
  14. tablefunc | crosstab4 | SETOF tablefunc_crosstab_4 | text | normal
  15. tablefunc | normal_rand | SETOF double precision | integer, double precision, double precision | normal
  16. (11 rows)

★★使用测试

★刷入测试表和数据

  1. --测试表
  2. create table score(
  3. name varchar,
  4. subject varchar,
  5. score bigint
  6. );
  7. --测试数据
  8. insert into score values
  9. ('Lucy','English',100),
  10. ('Lucy','Physics',90),
  11. ('Lucy','Math',85),
  12. ('Lily','English',76),
  13. ('Lily','Physics',57),
  14. ('Lily','Math',86),
  15. ('David','English',57),
  16. ('David','Physics',86),
  17. ('David','Math',100),
  18. ('Simon','English',88),
  19. ('Simon','Physics',99),
  20. ('Simon','Math',65);
  21. --原数据查询
  22. select * from score order by 1,2,3;

★sql标准实现

  1. select name,
  2. sum(case when subject='English' then score else 0 end) as "English",
  3. sum(case when subject='Physics' then score else 0 end) as "Physics",
  4. sum(case when subject='Math' then score else 0 end) as "Math"
  5. from score
  6. group by name order by name desc;
  7. name | English | Physics | Math
  8. -------+---------+---------+------
  9. Simon | 88 | 99 | 65
  10. Lucy | 100 | 90 | 85
  11. Lily | 76 | 57 | 86
  12. David | 57 | 86 | 100
  13. (4 rows)

★tablefunc实现

  1. select * from
  2. crosstab('select name,subject,score from score order by name desc',
  3. /* name:分组标准,subject:聚合标准,score:聚合标准下经过计算的值 */
  4. $$values('English'::text),('Physics'::text),('Math'::text)$$
  5. )
  6. as score(name text,English bigint,Physics bigint,Math bigint);
  7. /*显示字段name,English,Physics,Math
  8. [name是分组标准;English,Physics,Math是聚合标准产生的字段名]
  9. */
  10. name | english | physics | math
  11. -------+---------+---------+------
  12. Simon | 88 | 99 | 65
  13. Lucy | 100 | 90 | 85
  14. Lily | 76 | 57 | 86
  15. David | 57 | 86 | 100
  16. (4 rows)

参考:

  1. http://www.bubuko.com/infodetail-2159755.html
0 评论  
添加一条新评论