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