PostgreSQL 利用 array_agg 生成交叉表记录

前文我们通过多种方法创建交叉表，但有时前端语言需要容易处理 json 形式记录，然后通过适当控件渲染交叉表或图表。

本文还是通过前文的数据进行讲解,示例数据可以通过前文获取，这里就直接开始了。

查看每个学生每月的各科测评情况

	select stu_name, extract (month from eval_day) eval_month, subject, max(eval_result) eval_result
	from evaluations
	group by 1, 2, 3

返回结果：

stu_name	eval_month	subject	eval_result
peter	3.0	chinese	4.0
peter	3.0	geography	10.0
peter	3.0	maths	10.0
smith	3.0	history	9.0
smith	3.0	geography	9.0
peter	3.0	music	2.0
smith	4.0	maths	4.0
peter	3.0	history	7.0
smith	4.0	music	7.0
smith	3.0	chinese	7.0

我们以及看到交叉表比较直观，我们需要对结果进行分组处理，结合array_agg 函数和 group by 语句：

select stu_name, array_agg(row(eval_month, subject, eval_result)) eval_list
from (
	select stu_name, extract (month from eval_day) eval_month, subject, max(eval_result) eval_result
	from evaluations
	group by 1, 2, 3
) as row
group by stu_name
order by 1;

返回结果：

stu_name	eval_list
peter	{"(3,chinese,4.0)","(3,geography,10.0)","(3,maths,10.0)","(3,music,2.0)","(3,history,7.0)"}
smith	{"(4,music,7.0)","(4,maths,4.0)","(3,chinese,7.0)","(3,history,9.0)","(3,geography,9.0)"}

月度各科测评的总分和平均分

利用上述方法，也可以利用聚集函数进行统计分析：

select eval_month, array_agg(row(subject,avg_result,sum_result)) eval_list
from (
	select extract (month from eval_day) eval_month, subject, round(avg(eval_result),2) avg_result, sum(eval_result) sum_result
	from evaluations
	group by 1, 2
) as row
group by eval_month 
order by 1;

eval_month	eval_list
3.0	{"(chinese,5.50,11.0)","(history,8.00,16.0)","(music,2.00,2.0)","(geography,9.50,19.0)","(maths,10.00,10.0)"}
4.0	{"(maths,4.00,4.0)","(music,7.00,7.0)"}

总结

本文及前面几篇文章都描述如何实现交叉表，这里主要利用数组相关函数，后续再对数组相关的内容进行分享。