PostgreSQL Oracle 兼容性 - 行列变换(pivot unpivot) (tablefunc, crosstab)
背景
行列转换是OLAP应用场景中，对数据透视常用的SQL之一。
Oracle pivot 行转列
语法如下：
SELECT ...
FROM ...
PIVOT [XML]
(pivot_clause
pivot_for_clause
pivot_in_clause )
WHERE ...
In addition tothe new PIVOT keyword, we can see three new pivot clauses, described below.
（1）pivot_clause: definesthe columns to be aggregated (pivot is an aggregate operation);
（2）pivot_for_clause: definesthe columns to be grouped and pivoted;
（3）pivot_in_clause: definesthe filter for the column(s) in the pivot_for_clause (i.e. the range of valuesto limit the results to). The aggregations for each value in thepivot_in_clause will be transposed into a separate column (where appropriate).
对pivot_for_clause 指定的列进行过滤，只将指定的行转成列。
如：
SQL> WITH pivot_data AS ( -- 使用CTE语法，行转列的记录集
2 SELECT deptno, job, sal -- 原始表，注意出现过的列，看PIVOT里面是如何处理的
3 FROM emp
4 )
5 SELECT *
6 FROM pivot_data
7 PIVOT (
8 SUM(sal) --<-- pivot_clause ， 作为聚合项
9 FOR deptno --<-- pivot_for_clause 行转列字段（聚合）
10 IN (10,20,30,40) --<-- pivot_in_clause 行转列的行取值，最后会生成这些行（聚合）
11 );
未写入pivot内的列(JOB)，被作为group by的字段。sal作为聚合列，deptno的内容10,20,30,40作为行列转换（GROUP BY）字段。
JOB 10 20 30 40
--------- ---------- ---------- --------- -----------
CLERK 1430 2090 1045
SALESMAN 6160
PRESIDENT 5500
MANAGER 2695 3272.5 3135
ANALYST 6600
5 rows selected.
Oracle unpivot 列转行
语法：
SELECT ...
FROM ...
UNPIVOT [INCLUDE|EXCLUDE NULLS]
(unpivot_clause
unpivot_for_clause
unpivot_in_clause )
WHERE ...
The unpivot_clause rotatescolumns into rows.
| （1）The INCLUDE | EXCLUDE NULLS clausegives you the option of including or excluding null-valued rows. INCLUDE NULLS causesthe unpivot operation to include null-valued rows; EXCLUDE NULLS eliminatesnull-values rows from the return set. If you omit this clause, then the unpivotoperation excludes nulls. |
这个选项用来控制unpivot 是否包含null 的记录，默认是不包含nulls的。
（2）unpivot_clause: this clause specifies a name for a column to represent the unpivotedmeasure values.
对应的具体值
（3）Inthe pivot_for_clause, specify a name for eachoutput column that will hold descriptor values, such as quarter or product.
对应转换后列的名称
（4）Inthe unpivot_in_clause, specify the input datacolumns whose names will become values in the output columns of the pivot_for_clause.These input data columns have names specifying a category value, such as Q1,Q2, Q3, Q4. The optional AS clause lets you map the input data columnnames to the specified literal values in the output columns.
具体列到行的列名
如：
SQL> SELECT *
2 FROM pivoted_data
3 UNPIVOT (
4 deptsal --<-- unpivot_clause
5 FOR saldesc --<-- unpivot_for_clause
6 IN (d10_sal, d20_sal, d30_sal, d40_sal) --<-- unpivot_in_clause
7 );
JOB SALDESC DEPTSAL
---------- ---------- ----------
CLERK D10_SAL 1430
CLERK D20_SAL 2090
CLERK D30_SAL 1045
SALESMAN D30_SAL 6160
PRESIDENT D10_SAL 5500
MANAGER D10_SAL 2695
MANAGER D20_SAL 3272.5
MANAGER D30_SAL 3135
ANALYST D20_SAL 6600
PostgreSQL 行转列
https://www.postgresql.org/docs/devel/static/tablefunc.html
create extension tablefunc;
使用crosstab函数接口进行行列转换。
create table tbl_sellers_info (seller text,se_year int,se_month int,se_amount int);
insert into tbl_sellers_info values ('德哥',2011,01,123456);
insert into tbl_sellers_info values ('德哥',2011,02,234567);
insert into tbl_sellers_info values ('德哥',2011,03,345678);
insert into tbl_sellers_info values ('德哥',2011,04,345678);
insert into tbl_sellers_info values ('德哥',2011,05,567890);
insert into tbl_sellers_info values ('贝克汉姆',2011,01,12);
insert into tbl_sellers_info values ('贝克汉姆',2011,02,23);
insert into tbl_sellers_info values ('贝克汉姆',2011,03,34);
insert into tbl_sellers_info values ('贝克汉姆',2011,04,45);
insert into tbl_sellers_info values ('贝克汉姆',2011,05,56);
insert into tbl_sellers_info values ('卡洛斯',2011,03,12);
insert into tbl_sellers_info values ('卡洛斯',2011,04,45);
insert into tbl_sellers_info values ('卡洛斯',2011,05,56);
insert into tbl_sellers_info values ('罗纳尔多',2011,02,20);
insert into tbl_sellers_info values ('罗纳尔多',2011,03,30);
insert into tbl_sellers_info values ('罗纳尔多',2011,04,40);
insert into tbl_sellers_info values ('罗纳尔多',2011,05,50);
insert into tbl_sellers_info values ('德哥',2010,01,123456);
insert into tbl_sellers_info values ('德哥',2010,02,234567);
insert into tbl_sellers_info values ('德哥',2010,03,345678);
insert into tbl_sellers_info values ('德哥',2010,04,345678);
insert into tbl_sellers_info values ('德哥',2010,05,567890);
insert into tbl_sellers_info values ('德哥',2010,06,123456);
insert into tbl_sellers_info values ('德哥',2010,07,234567);
insert into tbl_sellers_info values ('德哥',2010,08,345678);
insert into tbl_sellers_info values ('德哥',2010,09,345678);
insert into tbl_sellers_info values ('德哥',2010,10,567890);
insert into tbl_sellers_info values ('德哥',2010,11,123456);
insert into tbl_sellers_info values ('德哥',2010,12,234567);
insert into tbl_sellers_info values ('贝克汉姆',2010,11,12);
insert into tbl_sellers_info values ('贝克汉姆',2010,12,23);
insert into tbl_sellers_info select * from tbl_sellers_info;
postgres=# select * from tbl_sellers_info ;
seller | se_year | se_month | se_amount
----------+---------+----------+-----------
德哥 | 2011 | 1 | 123456
德哥 | 2011 | 2 | 234567
德哥 | 2011 | 3 | 345678
德哥 | 2011 | 4 | 345678
德哥 | 2011 | 5 | 567890
贝克汉姆 | 2011 | 1 | 12
贝克汉姆 | 2011 | 2 | 23
贝克汉姆 | 2011 | 3 | 34
贝克汉姆 | 2011 | 4 | 45
贝克汉姆 | 2011 | 5 | 56
卡洛斯 | 2011 | 3 | 12
卡洛斯 | 2011 | 4 | 45
卡洛斯 | 2011 | 5 | 56
罗纳尔多 | 2011 | 2 | 20
罗纳尔多 | 2011 | 3 | 30
罗纳尔多 | 2011 | 4 | 40
罗纳尔多 | 2011 | 5 | 50
德哥 | 2010 | 1 | 123456
德哥 | 2010 | 2 | 234567
德哥 | 2010 | 3 | 345678
德哥 | 2010 | 4 | 345678
德哥 | 2010 | 5 | 567890
德哥 | 2010 | 6 | 123456
德哥 | 2010 | 7 | 234567
德哥 | 2010 | 8 | 345678
德哥 | 2010 | 9 | 345678
德哥 | 2010 | 10 | 567890
德哥 | 2010 | 11 | 123456
德哥 | 2010 | 12 | 234567
贝克汉姆 | 2010 | 11 | 12
贝克汉姆 | 2010 | 12 | 23
德哥 | 2011 | 1 | 123456
德哥 | 2011 | 2 | 234567
德哥 | 2011 | 3 | 345678
德哥 | 2011 | 4 | 345678
德哥 | 2011 | 5 | 567890
贝克汉姆 | 2011 | 1 | 12
贝克汉姆 | 2011 | 2 | 23
贝克汉姆 | 2011 | 3 | 34
贝克汉姆 | 2011 | 4 | 45
贝克汉姆 | 2011 | 5 | 56
卡洛斯 | 2011 | 3 | 12
卡洛斯 | 2011 | 4 | 45
卡洛斯 | 2011 | 5 | 56
罗纳尔多 | 2011 | 2 | 20
罗纳尔多 | 2011 | 3 | 30
罗纳尔多 | 2011 | 4 | 40
罗纳尔多 | 2011 | 5 | 50
德哥 | 2010 | 1 | 123456
德哥 | 2010 | 2 | 234567
德哥 | 2010 | 3 | 345678
德哥 | 2010 | 4 | 345678
德哥 | 2010 | 5 | 567890
德哥 | 2010 | 6 | 123456
德哥 | 2010 | 7 | 234567
德哥 | 2010 | 8 | 345678
德哥 | 2010 | 9 | 345678
德哥 | 2010 | 10 | 567890
德哥 | 2010 | 11 | 123456
德哥 | 2010 | 12 | 234567
贝克汉姆 | 2010 | 11 | 12
贝克汉姆 | 2010 | 12 | 23
(62 rows)
行列变换，用JSON，将需要GROUP BY的多个字段合并成1个。
select
js->>'seller' as seller,
js->>'se_year' as se_year,
jan ,
feb ,
mar ,
apr ,
may ,
jun ,
jul ,
aug ,
sep ,
oct ,
nov ,
dec
from crosstab(
-- 这个是需要进行行列变换的源SQL ， 数据源。
-- 排序字段为group by字段 ，最后一个字段为转换后的内容字段，导数第二个字段为行列变换的字段（内容为枚举，比如月份）
-- （必须在下一个参数中提取出对应的所有枚举值）
$$select jsonb_build_object('seller', seller, 'se_year', se_year) as js, se_month, sum(se_amount) from tbl_sellers_info group by 1,2 order by 1$$,
-- 行列转换的行，有哪些值被提取出来作为列。 这个在这里代表的是月份，也就是se_month的值
-- 或(select * from (values('jan'),...('dec')) t(se_month))
'select distinct se_month from tbl_sellers_info order by 1'
)
as -- crosstab 输出格式
( js jsonb, -- 第一个参数SQL内对应的order by对应的字段（1个或多个）
Jan numeric, -- 第一个参数SQL内对应导数第二个字段的枚举值，（行转列）
feb numeric, -- ...同上
mar numeric,
apr numeric,
may numeric,
jun numeric,
jul numeric,
aug numeric,
sep numeric,
oct numeric,
nov numeric,
dec numeric
)
order by 1,2;
结果
seller | se_year | jan | feb | mar | apr | may | jun | jul | aug | sep | oct | nov | dec
----------+---------+--------+--------+--------+--------+---------+--------+--------+--------+--------+---------+--------+--------
卡洛斯 | 2011 | | | 24 | 90 | 112 | | | | | | |
德哥 | 2010 | 246912 | 469134 | 691356 | 691356 | 1135780 | 246912 | 469134 | 691356 | 691356 | 1135780 | 246912 | 469134
德哥 | 2011 | 246912 | 469134 | 691356 | 691356 | 1135780 | | | | | | |
罗纳尔多 | 2011 | | 40 | 60 | 80 | 100 | | | | | | |
贝克汉姆 | 2010 | | | | | | | | | | | 24 | 46
贝克汉姆 | 2011 | 24 | 46 | 68 | 90 | 112 | | | | | | |
(6 rows)
PostgreSQL 列转行
例子，将以上行列转换的结果，再转换回去。
with a as ( -- A对应原始数据（即需要列转行的数据）
select
js->>'seller' as seller,
js->>'se_year' as se_year,
jan ,
feb ,
mar ,
apr ,
may ,
jun ,
jul ,
aug ,
sep ,
oct ,
nov ,
dec
from crosstab(
-- 这个是需要进行行列变换的源SQL ， 数据源。
-- 排序字段为group by字段 ，最后一个字段为转换后的内容字段，导数第二个字段为行列变换的字段（内容为枚举，比如月份）
-- （必须在下一个参数中提取出对应的所有枚举值）
$$select jsonb_build_object('seller', seller, 'se_year', se_year) as js, se_month, sum(se_amount) from tbl_sellers_info group by 1,2 order by 1$$,
-- 行列转换的行，有哪些值被提取出来作为列。 这个在这里代表的是月份，也就是se_month的值
-- 或(select * from (values('jan'),...('dec')) t(se_month))
'select distinct se_month from tbl_sellers_info order by 1'
)
as -- crosstab 输出格式
( js jsonb, -- 第一个参数SQL内对应的order by对应的字段（1个或多个）
Jan numeric, -- 第一个参数SQL内对应导数第二个字段的枚举值，（行转列）
feb numeric, -- ...同上
mar numeric,
apr numeric,
may numeric,
jun numeric,
jul numeric,
aug numeric,
sep numeric,
oct numeric,
nov numeric,
dec numeric
)
order by 1,2
)
,
-- b ， 用jsonb把多列合并为一列，并使用jsonb_each展开。
b as (select seller, se_year, jsonb_each(row_to_json(a)::jsonb-'seller'::text-'se_year'::text) as rec from a)
select seller, se_year, (b.rec).key as month, (b.rec).value as sum from b;
结果
seller | se_year | month | sum
----------+---------+-------+---------
卡洛斯 | 2011 | apr | 90
卡洛斯 | 2011 | aug | null
卡洛斯 | 2011 | dec | null
卡洛斯 | 2011 | feb | null
卡洛斯 | 2011 | jan | null
卡洛斯 | 2011 | jul | null
卡洛斯 | 2011 | jun | null
卡洛斯 | 2011 | mar | 24
卡洛斯 | 2011 | may | 112
卡洛斯 | 2011 | nov | null
卡洛斯 | 2011 | oct | null
卡洛斯 | 2011 | sep | null
德哥 | 2010 | apr | 691356
德哥 | 2010 | aug | 691356
德哥 | 2010 | dec | 469134
德哥 | 2010 | feb | 469134
德哥 | 2010 | jan | 246912
德哥 | 2010 | jul | 469134
德哥 | 2010 | jun | 246912
德哥 | 2010 | mar | 691356
德哥 | 2010 | may | 1135780
德哥 | 2010 | nov | 246912
德哥 | 2010 | oct | 1135780
德哥 | 2010 | sep | 691356
德哥 | 2011 | apr | 691356
德哥 | 2011 | aug | null
德哥 | 2011 | dec | null
德哥 | 2011 | feb | 469134
德哥 | 2011 | jan | 246912
德哥 | 2011 | jul | null
德哥 | 2011 | jun | null
德哥 | 2011 | mar | 691356
德哥 | 2011 | may | 1135780
德哥 | 2011 | nov | null
德哥 | 2011 | oct | null
德哥 | 2011 | sep | null
罗纳尔多 | 2011 | apr | 80
罗纳尔多 | 2011 | aug | null
罗纳尔多 | 2011 | dec | null
罗纳尔多 | 2011 | feb | 40
罗纳尔多 | 2011 | jan | null
罗纳尔多 | 2011 | jul | null
罗纳尔多 | 2011 | jun | null
罗纳尔多 | 2011 | mar | 60
罗纳尔多 | 2011 | may | 100
罗纳尔多 | 2011 | nov | null
罗纳尔多 | 2011 | oct | null
罗纳尔多 | 2011 | sep | null
贝克汉姆 | 2010 | apr | null
贝克汉姆 | 2010 | aug | null
贝克汉姆 | 2010 | dec | 46
贝克汉姆 | 2010 | feb | null
贝克汉姆 | 2010 | jan | null
贝克汉姆 | 2010 | jul | null
贝克汉姆 | 2010 | jun | null
贝克汉姆 | 2010 | mar | null
贝克汉姆 | 2010 | may | null
贝克汉姆 | 2010 | nov | 24
贝克汉姆 | 2010 | oct | null
贝克汉姆 | 2010 | sep | null
贝克汉姆 | 2011 | apr | 90
贝克汉姆 | 2011 | aug | null
贝克汉姆 | 2011 | dec | null
贝克汉姆 | 2011 | feb | 46
贝克汉姆 | 2011 | jan | 24
贝克汉姆 | 2011 | jul | null
贝克汉姆 | 2011 | jun | null
贝克汉姆 | 2011 | mar | 68
贝克汉姆 | 2011 | may | 112
贝克汉姆 | 2011 | nov | null
贝克汉姆 | 2011 | oct | null
贝克汉姆 | 2011 | sep | null
(72 rows)
参考
http://www.oracle.com/technetwork/cn/articles/11g-pivot-101924-zhs.html
http://www.cnblogs.com/zlja/archive/2011/12/10/2449515.html
http://www.dba-oracle.com/t_pivot_examples.htm
http://www.postgresonline.com/journal/archives/283-Unpivoting-data-in-PostgreSQL.html
https://modern-sql.com/use-case/pivot
https://www.postgresql.org/docs/devel/static/tablefunc.html
《行列变换 - Use tablefunc complete row & column cross display in PostgreSQL》