Skip to main content
 首页 » 数据库

PostgreSQL 实现交叉表查询(1)

2022年07月19日131jillzhang

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

本文参考链接:https://blog.csdn.net/neweastsun/article/details/118445868
阅读延展