PostgreSQL技术问答43 - 行转列本文主要讨论的内容是PG的行转列操作。包括了行转列的基本概念和理解，然后结合

本文是《PostgreSQL技术问答》系列文章中的一篇。关于这个系列的由来，可以参阅开篇文章：

文章的编号只是一个标识，在系列中没有明确的逻辑顺序和意义。读者进行阅读时，不用太关注这个方面。

本文主要讨论的内容是在Postgres中，一个比较冷门的知识和内容：行转列。实际上，虽然看起来这个功能比较冷门，但如果能够理解和掌握，在某些特定的场景中，也是非常有效和方便的。比如本文能够成文，就是因为笔者在实际工作中有一个场景用到了这个功能，笔者才会有一定的研究和了解，才有了本文比较充实的内容。

什么是行转列

“行转列”，从字面上理解，就是将数据库中的数据，从以行常规的进行存储和展示的形式（就是标准的数据库记录集），转换成为以行和列，就是在工作中常见的表格的形式来展示。这里的行就变成了对象，而列是对象的属性，行和列对应的值，就是属性的值。

下面举个例子，让读者能够更加直观的理解。就拿我们熟悉的考试科目成绩表来说，在数据库中，通常的数据在数据库表中的存储结构如下：

学生	科目	成绩
刘备	语文	98
关羽	语文	92
张飞	语文	90
刘备	数学	63
张飞	数学	74
曹操	数学	85
刘备	英语	76
关羽	英语	88
曹操	英语	90

但在实际业务中，我们可能更习惯这样的展现方式：

学生\科目	语文	数学	英语
刘备	98	63	76
关羽	92	--	88
张飞	90	74	--
曹操	--	85	90

从逻辑角度和信息承载的实质而言，其实两者没有本质区别，只是在业务上更加清晰明了，用户体验更好。从这个角度而言，就是将以行为主要形式的数据库方式，转换成为Excel表格的形式，每列是一个对象属性。也是将一维的数据结构，转换成为更容易观察和理解的二维数据结构。我们将这个呈现形式的转换，可以简称为“行转列”。

那么问题在于，为什么不一开始，就将数据表设计成为属性列的方式呢？笔者理解，通常这样处理，可能是考虑以下的因素：

数据抽象

在数据设计的时候，不需要预先设计好具体的结构，只需要设计好"对象-属性-值"这样数据结构框架，就可以保持一种抽象和灵活性。

可扩展

抽象的目的是可以进行方便的扩展。比如如果要增加一个科目，不需要改变数据库表结构，直接增加对应属性值和数据就可以了。

数据管理

关系型数据库使用记录行方式进行数据管理的一大特点，是可以方便的在纵向维度上，结合数据特性，进行快速的分离和汇总计算，从而满足各种各样的业务需求。

如何操作和实现

在初步的理解了“行转列”的概念之后，我们随即结合前面实际数据和案例，来进行实际的操作和分析。相关的操作代码如下：


-- 创建扩展 
CREATE EXTENSION IF NOT EXISTS tablefunc;

-- 行转列查询

select * from crosstab(
'select student, course, score from ctest order by 1, 2',
'select distinct course from ctest order by 1'
) as ct( student varchar, sx varchar, yw varchar,  yy varchar  );

-- 查询结果
student   sx      yw      yy
关羽		92	88
刘备	63	98	76
张飞	74	90	
曹操	85		90

在开始分析之前，我们可以先来从逻辑上构想一下，这个行转列将如何操作，这有助于我们理解实际代码的编写和实现。

首先，我们可以想象，我们想要的一个结果，就是一个表，表的内容来自原始记录集，但将其转换成为了一个二维结构。表的第一个字段(第一列)，就是学生的列表；而表的第一行就是表头，应该就是所有的科目；而表的单元格的内容值，就是以学生和科目为依据，查询的成绩数值。所以，无论实际的具体实现的形式如何，都应该包括上面分析的要素。

然后我们就可以深入分析和理解crosstab的实现方式了。

tablefunc

这是一个前提设定。在Postgres中，“行专列”的功能，是通过Crosstab(交叉表)相关的功能实现的。而crosstab，并不是PG内置的功能特性，而是它的一个扩展，所以需要先在数据库中加载，才能够来使用。有趣的是，这个扩展的名字，并不是"crosstab"，而是叫"tablefunc"。 crosstab其实是在这个扩展加载后，提供的一个可以用于进行行转列操作的函数名字。

示例中是一个简单但是完整的crosstab的使用方式。其实这个结构包括三个部分。

crosstab函数

crosstab函数是行专列操作的核心，它会返回一个记录集，就是转换后的结果表。我们随后深入分析。

select选择器

我们需要通过select * 来读取表记录集中的内容。

as ct(..)

这个语句，其实是在定义字段列表，就是表头了。

显然，这个结构的核心，是crosstab函数。这个函数其实包括两个参数，形式都是字符串也就是查询语句。

值查询语句

这个语句，其实是用于提取转置后的单元格值，它有一定的语法规则。其实是严格的三个字段，第一个字段是表的第一个列的值；第二个字段是表头(行)的值；第三个内容，就是列和行对应的单元格中的值。理解了这个，我们就可以方便的根据业务需求，编写合适的查询语句了。

此数使用的值查询语句就是：

'select student, course, score from ctest order by 1, 2'

选择字段列表

这里也通过一个sql语句，从原始表中，提取出用于作为表头的字段名称和列表。

而此处使用的表头列表查询语句就是：

select distinct course from ctest order by 1

需要注意的问题

虽然已经比较清晰的理解了crosstab的原理和逻辑，但笔者实际使用的过程中，还是觉得有很多问题需要注意。

少的值如何处理

这个比较简单，如果列和行组合在原始记录里面没有，则当前的单元格的值就为空。

多的值如何处理

比如，如果在原始记录中，增加一门课程。而不改变字段的定义内容，系统将如何处理呢？和想象中，查询会忽略这个值不同，系统会抛出错误：

Detail: Query-specified return tuple has 4 columns but crosstab returns 5.

大体的意思是在语句中只定义了四个列，但实际查询产生了五个列，它认为是一种错误。避免出现这个错误呢？笔者觉得可以在原始查询中，限制查询范围，就是先过滤一下，保证不会出现多余的列。

排序

就笔者的使用经验而言。在定义所选择的数据和字段的时候，需要进行严格的匹配。比如例子中查询学生的成绩信息，需要先对学生进行排序，然后对课程进行排序；在定义转列的字段，也需要对课程进行排序，两者必须匹配。如果没有这个排序，可能就不能得到想要的结果，比如下面的情况：

- 非排序查询
select * from crosstab(
'select student, course, score from ctest ',
'select distinct course from ctest order by 1'
) as ct( student varchar, sx varchar , yw varchar,  yy varchar  );

- 转置结果

刘备		98	
关羽		92	
张飞		90	
刘备	63		
张飞	74		
曹操	85		
刘备			76
关羽			88
曹操			90

笔者认为，这样的设计，是为了更好的处理数据，来提高转置操作的效率。crosstab函数，会简单的从前到后遍历查询结果集来构造结果数据，而不用考虑后续的处理，和可能的再次数据遍历。

限定类型和字段

如果需要预先使用和限定字段，显然示例中select distinct 方式，会导致不可预料的结果，这时可以考虑使用预定义数组，需要注意这里需要转换为记录集。相关的示例代码如下：


select * from crosstab(
$$ select student, course, score from ctest 
where course in ('yx','sx','yy') 
order by 1, 2 $$,
$$ select unnest(array['yw','sx','yy']) $$
) as ct( student varchar, yw varchar , sx varchar,  yy varchar  );

单引号处理

如果要在crosstab查询中使用文本类型的数据，一般需要使用单引号来表示，显然，这个和crosstab函数需要使用字符串sql作为参数就产生了冲突。这时我们可以使用postgres的 $$ 语句声明的方式来解决这个问题，就像上面的示例中展示的那样。

所以，要编写完整而且正确的转置操作，要特别注意这些细节和问题，才可能得到预期的结果。

小结

这一段参考了掘金提供的AI自动总结的内容。

本文主要讨论的内容是在Postgre中进行数据查询的行转列操作。包括了行转列的基本概念和理解，然后结合实际案例和代码展开讨论了其具体的操作和实现，包括创建扩展、相关代码及核心函数 crosstab 的使用和操作。还提到实际使用中需注意少值、多值、排序、限定类型和字段、单引号处理等细节方面的问题。