Netezza SQL需求：统计符合条件学生的就读Distinct学校数量分布

需求 #

• 筛选2010年就读School A且GPA>5的学生
• 统计这些学生在2010-2015年间就读的不同学校数量，以及对应数量的学生人数

原始数据表my_table #

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

修正后的查询语句 #

WITH cte_a AS (
    -- 先筛选2010-2015年的有效数据
    SELECT * 
    FROM my_table 
    WHERE year BETWEEN 2010 AND 2015
),
cte_b AS (
    -- 筛选符合条件的学生：2010年就读School A且GPA>5
    SELECT DISTINCT student_id 
    FROM cte_a
    WHERE school_year = 'School A'
      AND gpa > 5 
      AND year = 2010
)
-- 统计每个目标学生的不同学校数量，再按学校数量分组统计学生人数
SELECT 
    count_distinct_schools,
    COUNT(student_id) AS student_count
FROM (
    SELECT 
        t.student_id,
        COUNT(DISTINCT t.school_year) AS count_distinct_schools
    FROM cte_a t  -- 使用已过滤年份的cte_a，避免统计2010年前的记录
    JOIN cte_b 
        ON t.student_id = cte_b.student_id
    GROUP BY t.student_id
) a
GROUP BY count_distinct_schools;

关键修正说明 #

1. 字段名修正：原SQL混淆了年份字段year和学校名字段school_year，修正后使用正确字段设置筛选条件
2. 数据源选择：内层聚合查询使用cte_a而非my_table，确保只统计2010-2015年间的学校记录，排除2010年之前的无效数据
3. 语法优化：用BETWEEN简化年份范围判断，提升语句可读性

预期结果 #

count_distinct_schools      student_count
       2                           1
       1                           1

内容的提问来源于stack exchange，提问作者stats_noob