PostgreSQL 实现交叉表查询(2)

上节我们学习使用 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功能限制。