SQL:计算学生就读的不同学校的数量

Question

我正在使用 Netezza SQL。

我有下表(my_table)，其中显示了不同年份的学生 GPA 以及他们就读的学校:

    student_id year gpa school_year
1           1 2010   6    School A
2           1 2011   4    School B
3           1 2012   4    School A
4           2 2010   5    School A
5           2 2011   1    School B
6           2 2012   2    School B
7           3 2009   5    School A
8           3 2010   6    School A
9           3 2011   5    School A
10          4 2010   5    School A
11          4 2011   7    School B
12          4 2014   6    School C
13          5 2010   4    School C
14          5 2011   2    School A
15          5 2012   2    School A

我的问题:我想知道:

• 对于 2010 年就读“A 学校”并且 GPA > 5 的任何学生
• 2010 年至 2015 年间，这些学生就读了多少所不同的学校？

这是我尝试为这个问题编写一个查询 - 我首先确定了 2010 年到 2015 年之间所有行的子集，然后我“标记”了 2010 年就读于 A 学校并且 GPA > 5 的学生。最后，我使用连接将所有这些组合在一起 - 并使用两层计数聚合来获得最终答案:

with cte_a as
(select * from my_table where school_year >= 2010 and school_year<=2015),

cte_b as
(select distinct student_id from cte_a
where school = 'School A' and gpa>5 and school_year = 2010)

select count_1, count(student_id) from(
select t.student_id, count(distinct school) as count_1
from my_table t
join cte_b
on t.student_id = cte_b.student_id
group by t.student_id)a
group by count_1;

我很困惑，在最后一段代码中，我是否需要使用 cte_b 或 my_table。

有人可以告诉我如何正确执行此操作吗？最后，我期待这样的最终答案:

  count_distinct_schools      count(student_id)
       2                           1
       1                           1

谢谢!

Answer 1

我不确定为什么需要双重聚合，但这不应该起作用吗？

SELECT t.student_id, COUNT(DISTINCT school)
FROM my_table t
JOIN (
    SELECT DISTINCT student_id
    FROM my_table
    WHERE school = 'School A' AND gpa > 5 AND year = 2010
) s ON s.student_id = t.student_id
WHERE t.year BETWEEN 2010 AND 2015
GROUP BY t.student_id