Netezza SQL查询正确性验证：2011年学生分组及在校年份统计

问题背景 #

使用Netezza SQL操作my_table学生数据表，表中记录2010-2015年大学生的就读专业、考试结果（1=通过，0=未通过）、备考情况（1=备考，0=未备考），学生未在校的年份无对应行。

需求：

• 获取2011年各学生的就读专业、考试结果及备考情况；
• 按2011年的专业、考试结果、备考情况分组，统计每组学生的在校年份分布，生成含各年份在校标记及计数的表格。

数据表结构 #

student_id current_major year exam_result studied_for_exam
1            1       Science 2010           0                1
2            1          Arts 2013           1                1
3            1          Arts 2014           0                1
4            2       Science 2011           1                0
5            2          Arts 2012           1                1
6            2       Science 2013           1                1
7            3          Arts 2014           1                0
8            3          Arts 2015           1                1
9            4          Arts 2012           0                1
10           4       Science 2013           1                1
11           5          Arts 2010           0                0
12           5          Arts 2011           0                0
13           5       Science 2014           1                1

预期结果示例 #

major_in_year exam_result_in_year studied_for_exam_in_year year_2010 year_2011 year_2012 year_2013 year_2014 year_2015 count
1         Science                   0                        1         0         1         0         1         0         0            3
2         Science                   1                        1         0         1         0         1         0         0            8
3            Arts                   0                        1         1         1         1         0         0         0            5
4         Science                   0                        1         0         1         1         1         1         0            3

编写的查询语句 #

WITH major_exam_study_in_year AS (
    SELECT student_id, current_major AS major_in_year, exam_result AS exam_result_in_year, studied_for_exam AS studied_for_exam_in_year
    FROM (
        SELECT student_id, current_major, exam_result, studied_for_exam, year,
        ROW_NUMBER() OVER (PARTITION BY student_id ORDER BY year) AS rn
        FROM my_table
        WHERE year = 2011
    ) sub
    WHERE rn = 1
)
SELECT mesy.major_in_year,
       mesy.exam_result_in_year,
       mesy.studied_for_exam_in_year,
       year_2010,
       year_2011,
       year_2012,
       year_2013,
       year_2014,
       year_2015, 
       COUNT(*) 
FROM (
    SELECT student_id,
           MAX(CASE WHEN (year = 2010) THEN 1 ELSE 0 END) AS year_2010,
           MAX(CASE WHEN (year = 2011) THEN 1 ELSE 0 END) AS year_2011,
           MAX(CASE WHEN (year = 2012) THEN 1 ELSE 0 END) AS year_2012,
           MAX(CASE WHEN (year = 2013) THEN 1 ELSE 0 END) AS year_2013,
           MAX(CASE WHEN (year = 2014) THEN 1 ELSE 0 END) AS year_2014,
           MAX(CASE WHEN (year = 2015) THEN 1 ELSE 0 END) AS year_2015
    FROM my_table
    GROUP BY student_id
) a
LEFT JOIN major_exam_study_in_year mesy ON a.student_id = mesy.student_id
GROUP BY mesy.major_in_year, 
         mesy.exam_result_in_year,
         mesy.studied_for_exam_in_year,
         year_2010, 
         year_2011, 
         year_2012, 
         year_2013, 
         year_2014, 
         year_2015;

查询验证分析 #

核心逻辑梳理 #

1. CTE部分（major_exam_study_in_year）：
   筛选year=2011的记录，但这里存在冗余：题目明确学生未在校年份无对应行，在校年份不会有重复行，因此每个学生在2011年最多只有一条记录，ROW_NUMBER()和rn=1的判断完全多余，可简化为：

   SELECT student_id, current_major AS major_in_year, exam_result AS exam_result_in_year, studied_for_exam AS studied_for_exam_in_year
   FROM my_table
   WHERE year = 2011
   

2. 学生在校年份标记子查询（a）：
   通过MAX(CASE...)按学生ID分组，标记每个学生在2010-2015各年份是否在校，逻辑正确，符合需求。
3. 最终分组统计：
   将学生在校年份标记结果与2011年的学生信息左连接，再按指定维度分组统计人数，逻辑符合需求，但需注意：
   • 左连接会保留2011年未在校的学生（这些学生的mesy字段为NULL），如果需求仅关注2011年在校的学生，应将LEFT JOIN改为INNER JOIN；
   • 最终的COUNT(*)统计每组学生数量，逻辑正确。

结论 #

原查询整体符合需求，但CTE部分存在冗余代码可简化，若仅针对2011年在校学生统计，需调整连接类型为内连接。

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