Netezza SQL：两种停车票统计查询的正确性辨析

需求说明 #

需要统计不同奖学金状态（1=有奖学金，0=无奖学金）的学生，在各缴费年份（year_parking_ticket_paid）中，分别有多少人恰好缴纳了指定数量的停车罚单。
补充规则：

• 同一学生同一年缴费的所有罚单，统计为该学生在该缴费年份的罚单总数，仅计入对应数量的统计行
• scholarship字段对应学生缴费年份的奖学金状态（同一年学生的奖学金状态固定）

表结构与示例数据 #

表my_table包含字段：

• student_id：学生ID
• scholarship：奖学金状态（1=有，0=无）
• year_parking_ticket_received：罚单开具年份（同一学生同年可获多张）
• year_parking_ticket_paid：缴费年份（≥罚单开具年份）

示例数据：

student_id scholarship year_parking_ticket_received year_parking_ticket_paid
1          1           2010                     2015
2          1           2011                     2015
3          0           2012                     2016
4          1           2012                     2016
5          0           2020                     2023
6          0           2021                     2023
7          0           2018                     2023
8          0           2017                     2023

两种方法的错误分析 #

你的方法 #

with cte as
(select student_id,
year_parking_ticket_paid, scholarship,
count(distinct year_parking_ticket_received) as distinct_year
from my_table
group by student_id, year_parking_ticket_paid,  scholarship),
cte2 as (
select year_parking_ticket_paid, distinct_year, sum(distinct_year) as count_distinct_year, scholarship
from cte
group by year_parking_ticket_paid, distinct_year, scholarship)
select * from cte2

错误点：

1. count(distinct year_parking_ticket_received)统计的是学生缴费年份下收到罚单的不同年份数，不是实际缴纳的罚单数量，完全偏离需求。
2. CTE2中sum(distinct_year)逻辑错误，需求是统计符合条件的学生人数，应该用count(student_id)而非求和错误的数值。

朋友的方法 #

select year_parking_ticket_paid, number_of_parking_tickets, count(*)
from
(
select student_id, year_parking_ticket_paid, sum(scholarship) as schol, count(*) as number_of_parking_tickets
from my_table
group by student_id, year_parking_ticket_paid
) as a
group by year_parking_ticket_paid, number_of_parking_tickets

错误点：

1. 子查询中sum(scholarship)逻辑错误：scholarship是0/1状态，同一年学生状态固定，不需要求和，应该直接将scholarship纳入分组保留状态。
2. 外层查询未按scholarship分组，最终结果缺失“不同奖学金状态”的统计维度，不符合需求。

正确的Netezza SQL实现 #

WITH student_ticket_summary AS (
    SELECT 
        student_id,
        scholarship,
        year_parking_ticket_paid AS year_paid,
        COUNT(*) AS number_of_parking_tickets
    FROM my_table
    GROUP BY student_id, scholarship, year_parking_ticket_paid
)
SELECT 
    scholarship,
    year_paid,
    number_of_parking_tickets,
    COUNT(student_id) AS number_of_students_that_paid_exactly_this_number_of_tickets
FROM student_ticket_summary
GROUP BY scholarship, year_paid, number_of_parking_tickets
ORDER BY scholarship, year_paid, number_of_parking_tickets;

逻辑说明：

1. 第一部分CTE：按student_id+scholarship+year_parking_ticket_paid分组，统计每个学生在对应缴费年份、奖学金状态下的罚单总数（每一行对应一张罚单，COUNT(*)即为数量）。
2. 第二部分：按scholarship+year_paid+number_of_parking_tickets分组，统计符合该组合的学生人数，完全匹配需求的统计维度。

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