PostgreSQL 实现列转行问题

1 测试表数据

SELECT
  relative_label_content
FROM
  frk_s.label_cor_gene
relative_label_content
------
AA
BB
CC

2 列转行写法

写法1:

string_agg

SELECT
  frwybs,
  string_agg (relative_label_content, ',') as relative_label_content
FROM
  frk_s.label_cor_gene
GROUP BY
  frwybs
relative_label_content
------------
AA,BB,CC

写法2:

array_to_string(ARRAY_AGG (text),',')

SELECT
  frwybs,
  array_to_string(
    ARRAY_AGG (DISTINCT relative_label_content),
    ','
  ) as labels_content
FROM
  frk_s.label_cor_gene
GROUP BY
  frwybs
labels_content
------------
AA,BB,CC

补充:PostgreSQL行列转换(兼容oracle pivot unpivot)

oracle11g开始内置了数据透视表pivot table这一功能,可以用来实现行列转换的功能,但是在数据量较大的时候使用性能就会较差。

pivot语法为:

SELECT ...
FROM  ...
PIVOT [XML]
  (pivot_clause
  pivot_for_clause
  pivot_in_clause )
WHERE ... 

oracle pivot使用例子:

–创建测试表并插入数据

create table usr
(name varchar2(20),
 score int,
 class varchar2(20)
);
insert into usr values('a',20,'math');
insert into usr values('a',22,'phy');
insert into usr values('b',23,'phy');
insert into usr values('b',21,'math');
insert into usr values('c',22,'phy');
insert into usr values('c',24,'math');
insert into usr values('d',25,'math');
insert into usr values('d',23,'phy');

–使用pivot进行行列转换

SQL> select * from usr
 2 pivot(
sum(score)
for class in ('math','phy')
 3  4  5 );
NAME           'math'   'phy'
-------------------- ---------- ----------
d              25     23
a              20     22
b              21     23
c              24     22

我们还可以使用unpivot来实现列转行。

unpivot语法为:

SELECT ...
FROM ...
UNPIVOT [INCLUDE|EXCLUDE NULLS]
  (unpivot_clause
  unpivot_for_clause
  unpivot_in_clause )
WHERE ... 

oracle unpivot使用例子:

–创建测试表并插入数据

CREATE TABLE t1
(
  VendorID int,
  Emp1 int,
  Emp2 int,
  Emp3 int,
  Emp4 int,
  Emp5 int
);
INSERT INTO t1 VALUES (1,4,3,5,4,4);
INSERT INTO t1 VALUES (2,4,1,5,5,5);
INSERT INTO t1 VALUES (3,4,3,5,4,4);
INSERT INTO t1 VALUES (4,4,2,5,5,4);
INSERT INTO t1 VALUES (5,5,1,5,5,5);

–使用unpivot进行列转行

SQL> select * from t1
 2 UNPIVOT(
orders for Employee in(emp1,emp2,emp3,emp4,emp5)
); 3  4
 VENDORID EMPL   ORDERS
---------- ---- ----------
     1 EMP1     4
     1 EMP2     3
     1 EMP3     5
     1 EMP4     4
     1 EMP5     4
     2 EMP1     4
     2 EMP2     1
     2 EMP3     5
     2 EMP4     5
     2 EMP5     5
     3 EMP1     4
 VENDORID EMPL   ORDERS
---------- ---- ----------
     3 EMP2     3
     3 EMP3     5
     3 EMP4     4
     3 EMP5     4
     4 EMP1     4
     4 EMP2     2
     4 EMP3     5
     4 EMP4     5
     4 EMP5     4
     5 EMP1     5
     5 EMP2     1
 VENDORID EMPL   ORDERS
---------- ---- ----------
     5 EMP3     5
     5 EMP4     5
     5 EMP5     5
25 rows selected.

那么在pg中该如何实现oracle的pivot/unpivot的行列转行功能呢?pg中自带的tablefunc插件可以实现,我们可以使用该插件中的crosstab函数接口进行行列转换。

pg行转列例子:

–建表插入测试数据

create table tbl (seller text,se_year int,se_month int,se_amount int);
insert into tbl values ('test1',2020,01,123456);
insert into tbl values ('test1',2020,02,234567);
insert into tbl values ('test1',2020,03,345678);
insert into tbl values ('test1',2020,04,345678);
insert into tbl values ('test1',2020,05,567890);
insert into tbl values ('test2',2020,01,12);
insert into tbl values ('test2',2020,02,23);
insert into tbl values ('test2',2020,03,34);
insert into tbl values ('test2',2020,04,45);
insert into tbl values ('test2',2020,05,56);
insert into tbl values ('test3',2020,03,12);
insert into tbl values ('test3',2020,04,45);
insert into tbl values ('test3',2020,05,56);
insert into tbl values ('test4',2020,02,20);
insert into tbl values ('test4',2020,03,30);
insert into tbl values ('test4',2020,04,40);
insert into tbl values ('test4',2020,05,50);
insert into tbl values ('test1',2019,01,123456);
insert into tbl values ('test1',2019,02,234567);
insert into tbl values ('test1',2019,03,345678);
insert into tbl values ('test1',2019,04,345678);
insert into tbl values ('test1',2019,05,567890);
insert into tbl values ('test1',2019,06,123456);
insert into tbl values ('test1',2019,07,234567);
insert into tbl values ('test1',2019,08,345678);
insert into tbl values ('test1',2019,09,345678);
insert into tbl values ('test1',2019,10,567890);
insert into tbl values ('test1',2019,11,123456);
insert into tbl values ('test1',2019,12,234567);
insert into tbl values ('test2',2019,11,12);
insert into tbl values ('test2',2019,12,23);
insert into tbl select * from tbl;

–行转列

bill=# select
bill-#  js->>'seller' as seller,
bill-#  js->>'se_year' as se_year,
bill-#  jan ,
bill-#  feb ,
bill-#  mar ,
bill-#  apr ,
bill-#  may ,
bill-#  jun ,
bill-#  jul ,
bill-#  aug ,
bill-#  sep ,
bill-#  oct ,
bill-#  nov ,
bill-#  dec
bill-# from crosstab(
bill(#  -- 这个是需要进行行列变换的源SQL , 数据源。
bill(#  -- 排序字段为group by字段 ,最后一个字段为转换后的内容字段,导数第二个字段为行列变换的字段(内容为枚举,比如月份)
bill(#  -- (必须在下一个参数中提取出对应的所有枚举值)
bill(#  $$select jsonb_build_object('seller', seller, 'se_year', se_year) as js, se_month, sum(se_amount) from tbl group by 1,2 order by 1$$,
bill(#  -- 行列转换的行,有哪些值被提取出来作为列。 这个在这里代表的是月份,也就是se_month的值
bill(#  -- 或(select * from (values('jan'),...('dec')) t(se_month))
bill(#  'select distinct se_month from tbl order by 1'
bill(# )
bill-# as  -- crosstab 输出格式
bill-# ( js jsonb, -- 第一个参数SQL内对应的order by对应的字段(1个或多个)
bill(#  Jan numeric, -- 第一个参数SQL内对应导数第二个字段的枚举值,(行转列)
bill(#  feb numeric, -- ...同上
bill(#  mar numeric,
bill(#  apr numeric,
bill(#  may numeric,
bill(#  jun numeric,
bill(#  jul numeric,
bill(#  aug numeric,
bill(#  sep numeric,
bill(#  oct numeric,
bill(#  nov numeric,
bill(#  dec numeric
bill(# )
bill-# order by 1,2;
 seller | se_year | jan  | feb  | mar  | apr  |  may  | jun  | jul  | aug  | sep  |  oct  | nov  | dec
--------+---------+--------+--------+--------+--------+---------+--------+--------+--------+--------+---------+--------+--------
 test1 | 2019  | 246912 | 469134 | 691356 | 691356 | 1135780 | 246912 | 469134 | 691356 | 691356 | 1135780 | 246912 | 469134
 test1 | 2020  | 246912 | 469134 | 691356 | 691356 | 1135780 |    |    |    |    |     |    |
 test2 | 2019  |    |    |    |    |     |    |    |    |    |     |   24 |   46
 test2 | 2020  |   24 |   46 |   68 |   90 |   112 |    |    |    |    |     |    |
 test3 | 2020  |    |    |   24 |   90 |   112 |    |    |    |    |     |    |
 test4 | 2020  |    |   40 |   60 |   80 |   100 |    |    |    |    |     |    |
(6 rows)

–列转行

bill=# with a as ( -- A对应原始数据(即需要列转行的数据)
bill(# select
bill(#  js->>'seller' as seller,
bill(#  js->>'se_year' as se_year,
bill(#  jan ,
bill(#  feb ,
bill(#  mar ,
bill(#  apr ,
bill(#  may ,
bill(#  jun ,
bill(#  jul ,
bill(#  aug ,
bill(#  sep ,
bill(#  oct ,
bill(#  nov ,
bill(#  dec
bill(# from crosstab(
bill(#  -- 这个是需要进行行列变换的源SQL , 数据源。
bill(#  -- 排序字段为group by字段 ,最后一个字段为转换后的内容字段,导数第二个字段为行列变换的字段(内容为枚举,比如月份)
bill(#  -- (必须在下一个参数中提取出对应的所有枚举值)
bill(#  $$select jsonb_build_object('seller', seller, 'se_year', se_year) as js, se_month, sum(se_amount) from tbl group by 1,2 order by 1$$,
bill(#  -- 行列转换的行,有哪些值被提取出来作为列。 这个在这里代表的是月份,也就是se_month的值
bill(#  -- 或(select * from (values('jan'),...('dec')) t(se_month))
bill(#  'select distinct se_month from tbl order by 1'
bill(# )
bill(# as  -- crosstab 输出格式
bill(# ( js jsonb, -- 第一个参数SQL内对应的order by对应的字段(1个或多个)
bill(#  Jan numeric, -- 第一个参数SQL内对应导数第二个字段的枚举值,(行转列)
bill(#  feb numeric, -- ...同上
bill(#  mar numeric,
bill(#  apr numeric,
bill(#  may numeric,
bill(#  jun numeric,
bill(#  jul numeric,
bill(#  aug numeric,
bill(#  sep numeric,
bill(#  oct numeric,
bill(#  nov numeric,
bill(#  dec numeric
bill(# )
bill(# order by 1,2
bill(# )
bill-# ,
bill-# -- b , 用jsonb把多列合并为一列,并使用jsonb_each展开。
bill-# b as (select seller, se_year, jsonb_each(row_to_json(a)::jsonb-'seller'::text-'se_year'::text) as rec from a)
bill-# select seller, se_year, (b.rec).key as month, (b.rec).value as sum from b;
 seller | se_year | month |  sum
--------+---------+-------+---------
 test1 | 2019  | apr  | 691356
 test1 | 2019  | aug  | 691356
 test1 | 2019  | dec  | 469134
 test1 | 2019  | feb  | 469134
 test1 | 2019  | jan  | 246912
 test1 | 2019  | jul  | 469134
 test1 | 2019  | jun  | 246912
 test1 | 2019  | mar  | 691356
 test1 | 2019  | may  | 1135780
 test1 | 2019  | nov  | 246912
 test1 | 2019  | oct  | 1135780
 test1 | 2019  | sep  | 691356
 test1 | 2020  | apr  | 691356
 test1 | 2020  | aug  | null
 test1 | 2020  | dec  | null
 test1 | 2020  | feb  | 469134
 test1 | 2020  | jan  | 246912
 test1 | 2020  | jul  | null
 test1 | 2020  | jun  | null
 test1 | 2020  | mar  | 691356
 test1 | 2020  | may  | 1135780
 test1 | 2020  | nov  | null
 test1 | 2020  | oct  | null
 test1 | 2020  | sep  | null
 test2 | 2019  | apr  | null
 test2 | 2019  | aug  | null
 test2 | 2019  | dec  | 46
 test2 | 2019  | feb  | null
 test2 | 2019  | jan  | null
 test2 | 2019  | jul  | null
 test2 | 2019  | jun  | null
 test2 | 2019  | mar  | null
 test2 | 2019  | may  | null
 test2 | 2019  | nov  | 24
 test2 | 2019  | oct  | null
 test2 | 2019  | sep  | null
 test2 | 2020  | apr  | 90
 test2 | 2020  | aug  | null
 test2 | 2020  | dec  | null
 test2 | 2020  | feb  | 46
 test2 | 2020  | jan  | 24
 test2 | 2020  | jul  | null
 test2 | 2020  | jun  | null
 test2 | 2020  | mar  | 68
 test2 | 2020  | may  | 112
 test2 | 2020  | nov  | null
 test2 | 2020  | oct  | null
 test2 | 2020  | sep  | null
 test3 | 2020  | apr  | 90
 test3 | 2020  | aug  | null
 test3 | 2020  | dec  | null
 test3 | 2020  | feb  | null
 test3 | 2020  | jan  | null
 test3 | 2020  | jul  | null
 test3 | 2020  | jun  | null
 test3 | 2020  | mar  | 24
 test3 | 2020  | may  | 112
 test3 | 2020  | nov  | null
 test3 | 2020  | oct  | null
 test3 | 2020  | sep  | null
 test4 | 2020  | apr  | 80
 test4 | 2020  | aug  | null
 test4 | 2020  | dec  | null
 test4 | 2020  | feb  | 40
 test4 | 2020  | jan  | null
 test4 | 2020  | jul  | null
 test4 | 2020  | jun  | null
 test4 | 2020  | mar  | 60
 test4 | 2020  | may  | 100
 test4 | 2020  | nov  | null
 test4 | 2020  | oct  | null
 test4 | 2020  | sep  | null
(72 rows)

以上为个人经验,希望能给大家一个参考,也希望大家多多支持我们。如有错误或未考虑完全的地方,望不吝赐教。

(0)

相关推荐

  • PostgreSQL归档配置及自动清理归档日志的操作

    在一般的生产环境中,数据库都需要开启归档模式,那么在pg中如何开启归档模式呢? pg中的归档配置涉及几个参数如下: # - Archiving - 是否开启归档 #archive_mode = off # enables archiving; off, on, or always # (change requires restart) 归档命令,注意 %p %f %% 格式化的含义. %p 是被归档的redo文件的路径, %f 是被归档的redo文档的文件名 %% 是百分号 #archive_c

  • postgresql 实现启动、状态查看、关闭

    利用psql启动数据库 [postgres@highgo ~]$ pg_ctl start 查看系统中运行的postgres进程 #ps -ef | grep postgres 连接postgresql数据库 #psql -h 127.0.0.1 -d postgres -U postgres 停止postgresql数据库实例 #pg_ctl stop #ps -ef | grep postgres 启动服务器最简单的方法是像下面这样: $ postgres -D /usr/local/pgs

  • Postgresql 如何清理WAL日志

    WAL是Write Ahead Log的简写,和oracle的redo日志类似,存放在$PGDATA/pg_xlog中,10版本以后在$PGDATA/pg_wal目录. 如果开启了归档,在目录archive_status下会有一些文件,以ready结尾的,表示可以归档但还没有归档,done结尾的表示已经归档. 和WAL日志数量相关的几个参数: wal_keep_segments = 300 # in logfile segments, 16MB each; 0 disables checkpoi

  • PostgreSQL 实现distinct关键字给单独的几列去重

    PostgreSQL去重问题一直困扰着我,distinct和group by远不如MySQL用起来随便,但是如果掌握了规律,还是和MySQL差不多的 主要介绍的是distinct关键字 select distinct id,name,sex,age from student 假如有一张student表,字段如上图,我查询student表中所有信息用distinct去重(上面的SQL语句),pgsql就会根据所有的字段通过算法取得重复行的第一行,但是很明显,ID这个字段我在设计的时候不会让它重复,

  • PostgreSQL LIKE 大小写实例

    PostgreSQL 数据库 函数upper("字符串"):转成大写字符串 WHERE UPPER("User_Name") LIKE upper(username) 此句查询"User_Name" 中值大小写不区分. SELECT "User_Id","User_Image","User_Name","User_Birthday","User_Sex&qu

  • 解决PostgreSQL日志信息占用磁盘过大的问题

    当PostgreSQL启用日志时,若postgresql.conf日志的相关参数还使用默认值的话磁盘很容易被撑爆.因此在启用了logging_collector参数时,需要对其它相关的参数进行调整. 系统默认参数如下 #log_destination = 'stderr' #日志格式,值为stderr, csvlog, syslog, and eventlog之一. logging_collector = on #启用日志 #log_directory = 'log' #日志文件存储目录 #lo

  • Postgresql中LIKE和ILIKE操作符的用法详解

    LIKE和ILIKE操作符可以模糊匹配字符串,LIKE是一般用法,ILIKE匹配时则不区分字符串的大小写. 它们需要结合通配符使用,下面介绍两种常用的通配符. %:百分号用于匹配字符串序列,可匹配任意组合: _:下划线用于匹配任何单一字符. 举例来说明LIKE和ILIKE操作符的区别. 先创建一张数据表table1,包含两列:id列和name列,代码如下: create table table1(id int, name varchar); insert into table1 values(1

  • PostgreSQL pg_archivecleanup与清理archivelog的操作

    pg_archivecleanup 和 pg_rewind 是PG 中两个重要的功能,一个是为了清理过期的 archive log 使用的命令,另一个是你可以理解为物理级别的 wal log的搬运工. 我们先说第一个 pg_archivecleanup 命令,这个命令主要是用于使用了archive log 功能的 postgresql 但在 archive log 堆积如山的情况下,你怎么来根据某些规则,清理这些日志呢? 这里面就要使用 pg_archivecleanup 这个命令了,可以定时的

  • postgresql连续归档及时间点恢复的操作

    简介 前面我们介绍了通过pgsql的流复制在生产环境中搭建高可用环境来保证服务的可持续性:我们也要对数据库进行周期备份,来防止数据的丢失,这就需要连续归档,它不仅可以用于大型数据库的增量备份和恢复,也可以用于搭建standby镜像备份.    PostgreSQL默认处于非归档模式.开启归档模式,主要涉及到三个参数:wal_level,archive_mode和archive_commandwal_level参数默认为mininal,设置此参数为archive或者之上的级别都可以打开归档.当po

  • 浅谈PostgreSQL中大小写不敏感问题

    本文主要讨论PostgreSQL中大小写不敏感存在的问题. 默认情况下,PostgreSQL会将列名和表名全部转换为小写状态. 图1 Person与person 如图1所示,我们创建表person,其中包含name列.然后插入一条记录.执行SELECT查询时,使用列名Name和表名Person而不是name和person,发现仍然可以正常获取刚刚插入表person中的记录. 图2 创建表Person? 此时如果我们再想创建表Person,会得到一个错误,因为此时PostgreSQL实际上把表名从

随机推荐