本文介绍了进行“分组依据"会导致多列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个( Postgres
)查询,该查询具有通常的 group by
子句:
I have a (Postgres
) query with a usual group by
clause:
select extract(year from a.created) as Year,a.testscoreid, b.irt_tlevel, count(a.*) as Questions
from asmt.testscores a join asmt.questions b
on a.questionid = b.questionid
where a.answered = True
group by Year,a.testscoreid, b.irt_tlevel
order by Year desc, a.testscoreid
列 b.irt_tlevel
的值为 low
, medium
和 high
,所有这些结果均位于行格式,例如:
The column b.irt_tlevel
has values low
, medium
and high
, all these results are in a row-format, for example:
Year TestScoreId Irt_tlevel Questions
2015 1 Low 2
2015 1 Medium 3
2015 1 High 5
我希望结果采用以下格式:
I'd like my results to be in the following format:
Year TestScoreId Low Medium High TotalQuestions
2015 1 2 3 5 10
任何帮助将不胜感激.
推荐答案
如果已知irt_tlevel列中的不同值的数目是固定的,则可以使用条件聚合.
You can use conditional aggregation if it is known that the number of distinct values in irt_tlevel column are fixed.
select
extract(year from a.created) as Year,
a.testscoreid,
sum(case when b.irt_tlevel = 'Low' then 1 else 0 end) as Low,
sum(case when b.irt_tlevel = 'Medium' then 1 else 0 end) as Medium,
sum(case when b.irt_tlevel = 'High' then 1 else 0 end) as High,
count(*) as Questions
from asmt.testscores a
join asmt.questions b on a.questionid = b.questionid
where a.answered = True
group by Year, a.testscoreid
order by Year desc, a.testscoreid
这篇关于进行“分组依据"会导致多列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!