PostgreSQL_行列转换pivot_unpivot

做过数据清洗ETL工作的都知道，行列转换是一个常见的数据整理需求。在不同的编程语言中有不同的实现方法，比如 SQL 中使用case+group，或者Power BI的M语言中用拖放组件实现。今天正好需要在PostgreSQL中处理一个数据行列转换，就把这个方法记录下来。

首先明确一下啥叫 行列转换 ，因为这个叫法也不是很统一，有的地方叫转置，有的地方叫透视，不一而足。我们就以下表为例，定义如下：

• 从表1这种变成表2这种，叫 透视（pivot）
• 反之叫 逆透视（unpivot）

表1

表2

构造样本数据

构造一个表以 行 格式保存数据

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 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）

-- case when ， 在PG中可以使用filter
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(
   -- 基础查询，返回X，Y，V格式的数据集
   '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')
  -- 因为crosstab()返回的结果是不能动态确定的，所以需要指定字段名称和类型 
  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, -- 结果集中指定拼接字段类型为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, -- 把 m01 转换成 1 	
   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)
复制代码