5,355
做过数据清洗ETL工作的都知道，行列转换是一个常见的数据整理需求。在不同的编程语言中有不同的实现方法，比如SQL中使用case+group，或者Power BI的M语言中用拖放组件实现。今天正好需要在PostgreSQL中处理一个数据行列转换，就把这个方法记录下来。
首先明确一下啥叫
行列转换
，因为这个叫法也不是很统一，有的地方叫转置，有的地方叫透视，不一而足。我们就以下表为例，定义如下：
从表1这种变成表2这种，叫
透视（pivot）
反之叫
逆透视（unpivot）
insert
into
demo.tf_pivot
values
(
'a'
,
2018
,
1
,
1
);
insert
into
demo.tf_pivot
values
(
'a'
,
2018
,
2
,
1
);
insert
into
demo.tf_pivot
values
(
'a'
,
2018
,
3
,
1
);
insert
into
demo.tf_pivot
values
(
'a'
,
2018
,
4
,
1
);
insert
into
demo.tf_pivot
values
(
'a'
,
2019
,
1
,
2
);
insert
into
demo.tf_pivot
values
(
'a'
,
2019
,
2
,
2
);
insert
into
demo.tf_pivot
values
(
'a'
,
2019
,
3
,
2
);
insert
into
demo.tf_pivot
values
(
'a'
,
2019
,
4
,
2
);
insert
into
demo.tf_pivot
values
(
'b'
,
2018
,
1
,
3
);
insert
into
demo.tf_pivot
values
(
'b'
,
2018
,
2
,
3
);
insert
into
demo.tf_pivot
values
(
'b'
,
2018
,
3
,
3
);
insert
into
demo.tf_pivot
values
(
'b'
,
2018
,
4
,
3
);
insert
into
demo.tf_pivot
values
(
'b'
,
2019
,
1
,
4
);
insert
into
demo.tf_pivot
values
(
'b'
,
2019
,
2
,
4
);
insert
into
demo.tf_pivot
values
(
'b'
,
2019
,
3
,
4
);
insert
into
demo.tf_pivot
values
(
'b'
,
2019
,
4
,
4
);
构造一个表以
列
格式保存数据
drop table if exists demo.tf_unpivot;
create table demo.tf_unpivot(
city text,
year int,
m01 int,
m02 int,
m03 int,
m04 int
insert into demo.tf_unpivot values('a', 2018, 1,2,3,4);
insert into demo.tf_unpivot values('a', 2019, 10,20,30,40);
insert into demo.tf_unpivot values('b', 2018, 100,200,300,400);
透视Pivot
CASE语法
首先展示一下传统的case when语法。在PostgreSQL中，还提供了一个filter语法（简化case when）
select
city,
sum(income) filter (where year=2018) as "2018",
sum(income) filter (where year=2019) as "2019"
from demo.tf_pivot
group by city
order by city;
city | 2018 | 2019
a | 4 | 8
b | 12 | 16
(2 rows)
CROSSTAB语法
在PostgreSQL中，如果安装了
tablefunc
扩展，就可以使用crosstab()函数来简化pivot操作。crosstab()提供了多个版本，这里仅演示最基础的版本 crosstab(sql text, sql text)。
crosstab()透视操作简单直接，关键点说明如下
第一个参数，带有按X，Y汇总的SQL子句，返回X，Y，Value格式的数据集；
第二个参数，SQL子句，返回用于水平表头中透视内容的所有值；
使用AS子句明确指定返回的每一个字段名称和类型；
select
*
from
crosstab(
'select city,year,sum(income)
from demo.tf_pivot
group by city,year
order by 1, 2'
,
'select distinct year from demo.tf_pivot order by 1'
)
as
(
"city"
text
,
"y2018"
int
,
"y2019"
int
);
city | y2018 | y2019
a | 4 | 8
b | 12 | 16
(2 rows)
多维表格的用法
在实际应用中，可能我们需要的最终结果包括多维数据，比如下面这种，行上面有两个维度，分别是city和year，然后对month进行透视。（这个结果和我们构造的样例表demo.tf_unpivot是一样的）
city | year | m01 | m02 | m03 | m04
a | 2018 | 1 | 1 | 1 | 1
a | 2019 | 2 | 2 | 2 | 2
b | 2018 | 3 | 3 | 3 | 3
b | 2019 | 4 | 4 | 4 | 4
(4 rows)
因为原生crosstab仅能支持X，Y两个维度，所以我们设计一个取巧算法来达到目的
在X上构造一个由多个字段构成的字段；
crosstab透视；
使用split_part()函数把组合字段拆分为多个结果字段；
select
split_part(city_year,
'~'
,
1
)
as
city,
split_part(city_year,
'~'
,
2
)::
int
as
year
,
m01,m02,m03,m04
from
crosstab(
'select city || ''~'' || year, month, sum(income)
from demo.tf_pivot
group by city || ''~'' || year, month
order by 1,2,3'
,
'select distinct month from demo.tf_pivot order by 1'
)
as
(
"city_year"
text
,
"m01"
int
,
"m02"
int
,
"m03"
int
,
"m04"
int
);
逆透视Unpivot
PostgreSQL没有提供函数来实现unpivot操作，不过我们可以使用PG提供的几个高级功能来间接实现需求。需要用到的函数和语法包括：
row_to_json()
把行数据转换为json串；
json_each_text()
把最外层的json对象转换成Key/Value，每个对象一行；
lateral
独立子查询内支持JOIN子查询外面的表（这个还没有搞太明白 :-D）
原始数据如下
select * from demo.tf_unpivot;
city | year | m01 | m02 | m03 | m04
a | 2018 | 1 | 2 | 3 | 4
a | 2019 | 10 | 20 | 30 | 40
b | 2018 | 100 | 200 | 300 | 400
(3 rows)
select
r.city,
r.year,
key as month_str,
substr(key, 2)::int as month_int,
value::int as income
from (select city, year, row_to_json(t.*) as line from demo.tf_unpivot t) as r
join lateral json_each_text(r.line) on (key ~* '^m[0-9]+');
city | year | month_str | month_int | income
a | 2018 | m01 | 1 | 1
a | 2018 | m02 | 2 | 2
a | 2018 | m03 | 3 | 3
a | 2018 | m04 | 4 | 4
a | 2019 | m01 | 1 | 10
a | 2019 | m02 | 2 | 20
a | 2019 | m03 | 3 | 30
a | 2019 | m04 | 4 | 40
b | 2018 | m01 | 1 | 100
b | 2018 | m02 | 2 | 200
b | 2018 | m03 | 3 | 300
b | 2018 | m04 | 4 | 400
(12 rows)
wait4friend
大数据架构师
粉丝
