背景
做过数据清洗ETL工作的都知道,行列转换是一个常见的数据整理需求。在不同的编程语言中有不同的实现方法,比如SQL中使用case+group,或者Power BI的M语言中用拖放组件实现。今天正好需要在PostgreSQL中处理一个数据行列转换,就把这个方法记录下来。
首先明确一下啥叫行列转换,因为这个叫法也不是很统一,有的地方叫转置,有的地方叫透视,不一而足。我们就以下表为例,定义如下:
- 从表1这种变成表2这种,叫透视(pivot)
- 反之叫逆透视(unpivot)
表1
项目 | 月份 | 金额 |
---|---|---|
A | 1 | 10 |
A | 2 | 20 |
A | 2 | 30 |
B | 1 | 30 |
表2
项目 | 1月 | 2月 |
---|---|---|
A | 10 | 50 |
B | 30 |
构造样本数据
构造一个表以行格式保存数据
drop table if exists demo.tf_pivot;
create table demo.tf_pivot (
city text,
year integer,
month integer,
income integer
);
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