阅读 186

PostgreSQL_行列转换pivot_unpivot

背景

做过数据清洗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 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)
复制代码
关注下面的标签,发现更多相似文章
评论