PG中的行转列
Tags： PGPostgreSQL行转列
简介
PG有如下几种行转列写法：
1、group by + sum + case when
2、用postgresql的crosstab函数
3、group by + string_agg + split_part(分组，行转列，字符切割)
4、使用json函数
环境准备
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | CREATE TABLE sales ( year INTEGER, quarter INTEGER, sales_amount NUMERIC ); INSERT INTO sales VALUES (2018, 1, 100), (2018, 2, 200), (2018, 3, 300), (2018, 4, 400), (2019, 1, 500), (2019, 2, 600), (2019, 3, 700), (2019, 4, 800); |
我们想将每个季度的销售额作为一列，年份作为行，结果：
1 2 3 4 5 | year | q1 | q2 | q3 | q4 ------+-----+-----+-----+----- 2018 | 100 | 200 | 300 | 400 2019 | 500 | 600 | 700 | 800 (2 rows) |
方法1：使用crosstab函数
PostgreSQL中可以使用crosstab函数将行转列，需要使用一个额外的模块“tablefunc”，安装完成后，我们可以使用crosstab函数将行转列。
1 2 3 4 5 6 7 8 9 10 | CREATE EXTENSION tablefunc; SELECT * FROM crosstab( 'SELECT year, quarter, sales_amount FROM sales ORDER BY 1, 2', 'SELECT quarter FROM generate_series(1,4) AS quarter' ) AS sales_pivot(year INTEGER, q1 NUMERIC, q2 NUMERIC, q3 NUMERIC, q4 NUMERIC); |
在这个例子中，我们将查询作为第一个参数传递给crosstab函数。查询必须按年份和季度排序。第二个参数是一个子查询，用于生成列名。在本例中，我们使用generate_series函数生成1到4之间的数字作为季度列的名称。
crosstab函数将行转换为列，并返回一个新的表。我们在外部查询中指定每个生成的列的数据类型和名称，以便正确返回结果。
方法2：使用group by + sum + case when
使用GROUP BY + SUM + CASE WHEN 也可以实现将行转列的效果。以下是一个示例：
1 2 3 4 5 6 7 8 9 | SELECT year, SUM(CASE WHEN quarter = 1 THEN sales_amount ELSE 0 END) AS q1, SUM(CASE WHEN quarter = 2 THEN sales_amount ELSE 0 END) AS q2, SUM(CASE WHEN quarter = 3 THEN sales_amount ELSE 0 END) AS q3, SUM(CASE WHEN quarter = 4 THEN sales_amount ELSE 0 END) AS q4 FROM sales GROUP BY year ORDER BY year; |
在这个示例中，我们使用了四个不同的CASE WHEN表达式来计算每个季度的销售额。在每个CASE WHEN表达式中，我们检查季度是否等于1、2、3或4，如果是，就将对应的销售额加入到该季度的总计中。否则，我们将0加入到总计中。
在查询中，我们使用GROUP BY子句对年份进行分组，并对每个季度的销售额进行求和。结果与使用crosstab函数得到的结果相同。
方法3：使用group by + string_agg + split_part(分组，行转列，字符切割)
使用GROUP BY + string_agg + split_part 也可以实现将行转列的效果。以下是一个示例：
1 2 3 4 5 6 7 8 9 10 11 12 13 | SELECT year, split_part(sales_agg, ',', 1)::numeric AS q1, split_part(sales_agg, ',', 2)::numeric AS q2, split_part(sales_agg, ',', 3)::numeric AS q3, split_part(sales_agg, ',', 4)::numeric AS q4 FROM ( SELECT year, string_agg(sales_amount::text, ',' ORDER BY quarter) AS sales_agg FROM sales GROUP BY year ) AS sales_pivot; |
在这个示例中，我们使用string_agg函数将每个季度的销售额连接成一个以逗号分隔的字符串（这里一定需要加上order by子句）。然后，我们使用split_part函数将字符串拆分成四个部分，以获取每个季度的销售额，并将其转换为数字类型。最后，我们在外部查询中指定了每个季度的数据类型和名称。
在查询中，我们首先使用GROUP BY子句对年份进行分组，并使用string_agg函数将每个季度的销售额连接成一个以逗号分隔的字符串。然后，我们在外部查询中使用split_part函数将字符串拆分成四个部分，并将其转换为数字类型，以获取每个季度的销售额。结果与使用crosstab函数或GROUP BY + SUM + CASE WHEN得到的结果相同。
方法4：使用json函数
1 2 3 4 5 6 7 | select year , json_object_agg(quarter,sales_amount) ->>'1' as "q1" , json_object_agg(quarter,sales_amount) ->>'2' as "q2" , json_object_agg(quarter,sales_amount) ->>'3' as "q3" , json_object_agg(quarter,sales_amount) ->>'4' as "q4" from sales group by year; |
