Skip to main content
 首页 » 数据库

PostgreSQL 实现交叉表查询(2)

2022年07月19日126zhoujg

上节我们学习使用 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  

每月平均成绩

我们通过 学生名称和月份进行分组:

select stu_name, extract (month from eval_day) eval_month, round(avg(eval_result),2) avg_result 
from  evaluations 
group by stu_name , extract (month from eval_day)  

显示结果:

stu_name eval_month avg_result
smith 3.0 8.33
smith 4.0 5.50
peter 3.0 6.60

下面我们使用 实现交叉表查询:

select * from crosstab( 
$$ 
select stu_name, extract (month from eval_day) eval_month, round(avg(eval_result),2) avg_result 
from  evaluations 
group by stu_name , extract (month from eval_day)  
order by 1,2 
$$ 
) as avg_result(stu_name text, month3_avg numeric, month4_avg numeric) 

执行返回错误:

SQL 错误 [42804]: 错误: invalid return type 
  详细:SQL rowid datatype does not match return rowid datatype. 

这是因为 crosstab 不能智能识别返回的月份信息,我们需要通过第二个参数进行显示设定,crosstab 完整语法如下:

crosstab(text source_sql, text category_sql)   

修改上面代码:

select * from crosstab( 
$$ 
select stu_name, extract (month from eval_day) eval_month,round(avg(eval_result),2) avg_result 
from  evaluations 
group by stu_name , extract (month from eval_day)  
order by 1,2 
$$ 
,'select distinct  extract (month from eval_day) eval_month from evaluations order by 1' 
) as avg_result(stu_name text, month3_avg numeric, month4_avg numeric) 
 

再次执行返回:

stu_name month3_avg month4_avg
peter 6.60
smith 8.33 5.50

与我们的预期一致。这时我们可能会想交叉表能不能同时显示每月的平均成绩和总成绩。

每月平均成绩与总成绩

上面已经完成了月度平均成绩的计算,同理计算月度总成绩就简单了:

select * from crosstab( 
$$ 
select stu_name, extract (month from eval_day) eval_month,round(sum(eval_result),2) sum_result 
from  evaluations 
group by stu_name , extract (month from eval_day)  
order by 1,2 
$$ 
,'select distinct  extract (month from eval_day) eval_month from evaluations order by 1' 
) as avg_result(stu_name text, month3_sum numeric, month4_sum numeric) 

执行结果:

stu_name month3_sum month4_sum
peter 33.00
smith 25.00 11.00

既然两者都已经计算处理,那么我们通过with进行组合:

with avg_data as ( 
	select * from crosstab( 
	$$ 
	select stu_name, extract (month from eval_day) eval_month,round(avg(eval_result),2) avg_result 
	from  evaluations 
	group by stu_name , extract (month from eval_day)  
	order by 1,2 
	$$ 
	,'select distinct  extract (month from eval_day) eval_month from evaluations order by 1' 
	) as avg_result(stu_name text, month3_avg numeric, month4_avg numeric) 
), 
sum_data as ( 
	select * from crosstab( 
	$$ 
	select stu_name, extract (month from eval_day) eval_month,round(sum(eval_result),2) sum_result 
	from  evaluations 
	group by stu_name , extract (month from eval_day)  
	order by 1,2 
	$$ 
	,'select distinct  extract (month from eval_day) eval_month from evaluations order by 1' 
	) as avg_result(stu_name text, month3_sum numeric, month4_sum numeric) 
) 
select a.stu_name,a.month3_avg, a.month4_avg, month3_sum, month4_sum 
from avg_data as a join sum_data as b 
on a.stu_name = b.stu_name 
 

执行结果:

stu_name month3_avg month4_avg month3_sum month4_sum
peter 6.60 33.00
smith 8.33 5.50 25.00 11.00

漂亮,与我们预想的一致,只是代码稍微有点冗长,但又有一定相似性。未来我们继续进行优化。

总结

本文通过 tablefunc实现交叉表展示聚合函数的值,并利用with展示多个聚合函数的值,突破了 tablefunc功能限制。


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