PostgreSQL的行转列和列转行

Oracle 中的行转列和列转行分别有 pivot 和 unpivot 方法。

在 PostgreSQL 中,行列互转的方法也有很多,在这里介绍常用的两种。

crosstab 行转列

有某平均温度数据(data_avg_temp):

name month avg_temp
康山 jan 5
康山 apr 16.3
康山 july 28.8
康山 oct 19.2
棠荫 jan 6
棠荫 apr 17.6
棠荫 july 29.7
棠荫 oct 20.3

要转换成如下格式:

name jan apr july oct
康山 5 16.3 28.8 19.2
棠荫 6 17.6 29.7 20.3

使用 crosstab 方法实现行转列:

  1. 使用 crosstab 方法,需要安装扩展模块 tablefunc:
1
CREATE EXTENSION tablefunc;--第一次使用crosstab前执行,后续无需再执行
  1. 实现代码:
1
2
3
4
5
6
7
SELECT * FROM crosstab
(
'SELECT name, month, avg_temp FROM data_avg_temp ORDER BY 1,2',
$$values('jan'::text),('apr'::text),('july'::text),('oct'::text)$$
)
AS data_avg_temp_cross
(name text, jan numeric, apr numeric, july numeric, oct numeric);

或:

1
2
3
4
5
6
7
SELECT * FROM crosstab
(
'SELECT name, month, avg_temp FROM data_avg_temp ORDER BY 1,2',
'SELECT DISTINCT month FROM data_avg_temp ORDER BY 1'
)
AS data_avg_temp_cross
(name text, jan numeric, apr numeric, july numeric, oct numeric);
  1. 转换原理

crosstab 内的第一个参数,是行转列的源表数据:

1
'SELECT name, month, avg_temp FROM data_avg_temp ORDER BY 1,2',

crosstab 内的第二个参数,是行转列的那一列数据,有两种形式:

  • values 形式:
1
$$values('jan'::text),('apr'::text),('july'::text),('oct'::text)$$
  • DISTINCT 形式:
1
'SELECT DISTINCT month FROM data_avg_temp ORDER BY 1'

最后要定义转换后的表结构:

1
2
AS data_avg_temp_cross
(name text, jan numeric, apr numeric, july numeric, oct numeric)

jsonb 列转行

有某平均温度数据(data_avg_temp2):

name jan apr july oct
康山 5 16.3 28.8 19.2
棠荫 6 17.6 29.7 20.3

要转换成如下格式:

name month avg_temp
康山 jan 5
康山 apr 16.3
康山 july 28.8
康山 oct 19.2
棠荫 jan 6
棠荫 apr 17.6
棠荫 july 29.7
棠荫 oct 20.3

使用 jsonb 方法实现列转行:

  1. 实现代码:
1
2
3
4
5
SELECT name, (b.rec).key as month, (b.rec).value FROM
(
SELECT name, jsonb_each(row_to_json(a)::jsonb-'name'::varchar) as rec FROM
(SELECT name, jan, apr, july, oct FROM data_avg_temp2) a
) b
  1. 转换原理

关键点在于:

1
jsonb_each(row_to_json(a)::jsonb-'name'::varchar)

row_to_json(a)::jsonb 把行数据转为 JSON,例如:

name jan apr july oct
康山 5 16.3 28.8 19.2

转为:

{"name":"康山","jan":"5","apr":"16.3","july":"28.8","oct":"19.2"}

-‘name’::varchar 把 JSON 中的 name 键值去掉:

{"jan":"5","apr":"16.3","july":"28.8","oct":"19.2"}

jsonb_each 再转换为 JSON 对象 rec,最后在外层 SELECT 中用(b.rec).key 和(b.rec).value 分别取出月份和平均温度。