`PostgreSQL` 实现交叉表查询(1)

自 PostgreSQL 8.3 版本开始，引入了 tablefunc扩展功能。它提供了很多有趣功能，其中就包括 tablefunc 函数，可以创建交叉表功能。

学习最佳方式就是从示例开始，我们首先初始化表和数据，然后创建交叉表。本文先实现简单交叉表，后续在增加更复杂功能。

创建示例表和数据

我们示例表是学生成绩表，包括学生姓名、科目、成绩、考试日期：

create table evaluations(
	stu_name varchar(60),
	subject varchar(60),
	eval_result numeric(3,1),
	eval_day    date
);

insert into evaluations(stu_name,subject,eval_result,eval_day) values
('smith',	'music',	7.0,	'2016-03-01'),
('smith',	'maths',	4.0,	'2016-03-01'),
('smith',	'history',	9.0,	'2016-03-22'),
('smith',	'chinese',	7.0,	'2016-03-15'),
('smith', 	'geography',	9.0,	'2016-03-04'),
('peter',	'music',	2.0,'2016-03-01'),
('peter',	'maths',	10.0,	'2016-03-01'),
('peter',	'history',	7.0,	'2016-03-22'),
('peter',	'chinese',	4.0,	'2016-03-15'),
('peter',	'geography',	10.0,	'2016-03-04')

-- select * from evaluations e 

展示结果方便理解：

stu_name	subject	eval_result	eval_day
smith	music	7.0	2016-04-01
smith	maths	4.0	2016-04-01
smith	history	9.0	2016-03-22
smith	chinese	7.0	2016-03-15
smith	geography	9.0	2016-03-04
peter	music	2.0	2016-03-01
peter	maths	10.0	2016-03-01
peter	history	7.0	2016-03-22
peter	chinese	4.0	2016-03-15
peter	geography	10.0	2016-03-04

示例1：创建交叉表

下面表格非常直观，很容易看到学生的每科成绩，一般我们称为交叉表。仔细观察你会发现，它使用科目值作为列头（字段名称）：

stu_name	music	maths	history	chinese	geography
peter	4.0	10.0	7.0	10.0	2.0
smith	7.0	9.0	9.0	4.0	7.0

安装 tablefunc 扩展

前面已经提到，交叉表是 tablefunc 扩展 功能的一部分。要使用交叉表，首先需要安装扩展：

CREATE extension tablefunc;

详解交叉表

crosstab 函数接收 sql 的select 语句作为参数，但必须服从下列限制：

1. SELECT 必须返回3列
2. SELECT 的第一列 必须是交叉表的行的标识列，在我们的示例中是学生名称。第二列表示交叉表的类别，我们示例中为科目，要特别注意，这列的值将在交叉表中被扩展为多列；如果返回5个不同值，交叉表将生成5列。
3. 第三列将被赋给交叉表对应的单元格，我们示例是考试分数。

如果把交叉表比作二维数组，那么select第一列数组第一维，select第二列表示数组第二维，第三个值是数组元素值。如：grid[first_value][second_value]=third_value.

我们的SELECT 语句为：

select stu_name, subject, eval_result from evaluations e order by 1,2

crosstab 函数 需要在from 子句中使用，因此必须定义最终结果的列名和数据类型：

 as final_result(stu_name  varchar, music NUMERIC, maths NUMERIC, history NUMERIC, chinese NUMERIC, geography NUMERIC)

完整语句为：

select * from crosstab(
	'select stu_name, subject, eval_result from evaluations e order by 1,2'
) as final_result(stu_name  varchar, music NUMERIC, maths NUMERIC, history NUMERIC, chinese NUMERIC, geography NUMERIC)

结果如下：

stu_name	music	maths	history	chinese	geography
peter	4.0	10.0	7.0	10.0	2.0
smith	7.0	9.0	9.0	4.0	7.0

示例2 ： 查找缺考学生记录

本节解决上节示例可能存在的问题。假设有下面场景：

我们想查询一些学生某些科目没有考试结果。可能你尝试下面查询代码如下：

SELECT * 
FROM crosstab( 'select stu_name, subject, eval_result from evaluations where extract (month from eval_day) = 3 order by 1,2') 
     AS final_result(Student varchar, Geography NUMERIC,History NUMERIC,Language NUMERIC,Maths NUMERIC,Music NUMERIC);

结果：

student	geography	history	language	maths	music
peter	4.0	10.0	7.0	10.0	2.0
smith	7.0	9.0	9.0

但如果只查询 smith 3月份科目成绩：

select stu_name, subject, eval_result from evaluations where extract (month from eval_day) = 3 and stu_name = 'smith' order by 1,2

结果为：

stu_name	subject	eval_result
smith	chinese	7.0
smith	geography	9.0
smith	history	9.0

当然这个结果是正确的，仅显示了原始数据。但问题是在交叉表中一些类别丢失了。为了修复这个问题，crosstab提供了第二个参数。

SELECT * 
FROM crosstab( 'select stu_name, subject, eval_result from evaluations where extract (month from eval_day) = 3 and stu_name = ''smith'' order by 1,2',
	'select distinct subject from evaluations order by 1'
) AS final_result(Student varchar, Geography NUMERIC,History NUMERIC,Language NUMERIC,Maths NUMERIC,Music NUMERIC);

结果为：

student	geography	history	language	maths	music
smith	7.0	9.0	9.0