基于Netezza SQL计算分组累计缺失年份的方案合理性问询

背景与需求 #

现有SQL表myt记录学生在校年份，表结构及插入数据如下：

CREATE TABLE myt (
  student_name VARCHAR(50),
  student_year INT
);

INSERT INTO myt (student_name, student_year) VALUES
('john', 2010),
('John', 2011),
('John', 2012),
('John', 2019),
('John', 2020),
('alex', 2005),
('tim', 2000),
('tim', 2000),
('jack', 2020),
('jack', 2024);

表数据：

student_name student_year
         john         2010
         John         2011
         John         2012
         John         2019
         John         2020
         alex         2005
          tim         2000
          tim         2000
         jack         2020
         jack         2024

需求：针对每个学生，计算其在校最小年份到最大年份之间的累计缺失年份数及缺失占比，预期结果如下：

student_name student_year total_years missed_years percent_missed
         john         2010           1            0              0
         john         2011           2            0              0
         john         2012           3            0              0
         john         2013           4            1             25
         john         2014           5            2             40
         john         2015           6            3             50
         john         2016           7            4           57.1
         john         2017           8            5           62.5
         john         2018           9            6           66.7
         john         2019          10            6             60
         john         2020          11            6           54.5
         alex         2005           1            0              0
          tim         2000           1            0              0
          tim         2000           1            0              0
         jack         2020           1            0              0
         jack         2021           2            1             50
         jack         2022           3            2           66.7
         jack         2023           4            3             75
         jack         2024           5            3             60

当前实现方案 #

由于Netezza不支持递归查询、序列生成函数，采用以下方案：

• 手动生成包含所有所需年份的日历CTE
• 将日历CTE与原表关联，标记缺失年份
• 通过窗口函数计算累计缺失数、总年份数及占比

具体SQL代码：

WITH calendar_years AS (
    SELECT 2000 AS year UNION ALL
    SELECT 2001 UNION ALL
    SELECT 2002 UNION ALL
    SELECT 2003 UNION ALL
    SELECT 2004 UNION ALL
    SELECT 2005 UNION ALL
    SELECT 2006 UNION ALL
    SELECT 2007 UNION ALL
    SELECT 2008 UNION ALL
    SELECT 2009 UNION ALL
    SELECT 2010 UNION ALL
    SELECT 2011 UNION ALL
    SELECT 2012 UNION ALL
    SELECT 2013 UNION ALL
    SELECT 2014 UNION ALL
    SELECT 2015 UNION ALL
    SELECT 2016 UNION ALL
    SELECT 2017 UNION ALL
    SELECT 2018 UNION ALL
    SELECT 2019 UNION ALL
    SELECT 2020 UNION ALL
    SELECT 2021 UNION ALL
    SELECT 2022 UNION ALL
    SELECT 2023 UNION ALL
    SELECT 2024
),
student_years AS (
  SELECT 
    student_name,
    MIN(student_year) AS min_year,
    MAX(student_year) AS max_year
  FROM myt
  GROUP BY student_name
),
student_calendar AS (
  SELECT 
    s.student_name,
    c.year
  FROM student_years s
  JOIN calendar_years c ON c.year BETWEEN s.min_year AND s.max_year
),
filled_years AS (
  SELECT 
    sc.student_name,
    sc.year,
    CASE WHEN m.student_year IS NULL THEN 1 ELSE 0 END AS is_missing
  FROM student_calendar sc
  LEFT JOIN myt m ON sc.student_name = m.student_name AND sc.year = m.student_year
),
aggregated AS (
  SELECT 
    student_name,
    year,
    SUM(is_missing) OVER (PARTITION BY student_name ORDER BY year) AS missed_years,
    COUNT(*) OVER (PARTITION BY student_name ORDER BY year) AS total_years
  FROM filled_years
)
SELECT 
  student_name,
  year,
  total_years,
  missed_years,
  (missed_years * 1.0 / total_years * 1.0) * 100 AS percent_missed
FROM aggregated
ORDER BY student_name, year;

执行结果：

student_name year total_years missed_years percent_missed
     John 2010           1            0        0.00000
     John 2011           2            0        0.00000
     John 2012           3            0        0.00000
     John 2013           4            1       25.00000
     John 2014           5            2       40.00000
     John 2015           6            3       50.00000
     John 2016           7            4       57.14286
     John 2017           8            5       62.50000
     John 2018           9            6       66.66667
     John 2019          10            6       60.00000
     John 2020          11            6       54.54545
     alex 2005           1            0        0.00000
     jack 2020           1            0        0.00000
     jack 2021           2            1       50.00000
     jack 2022           3            2       66.66667
     jack 2023           4            3       75.00000
     jack 2024           5            3       60.00000
      tim 2000           2            0        0.00000
      tim 2000           2            0        0.00000

方案合理性分析 #

整体合理性 #

该方案的核心思路完全适配Netezza的功能限制：

1. 手动生成日历表是Netezza不支持序列生成/递归时的标准替代方案，能够覆盖所需的年份范围
2. 通过CTE分层处理逻辑清晰：先确定每个学生的年份范围，再关联日历表补全年份，标记缺失后用窗口函数计算累计值，步骤逻辑连贯，符合SQL的常规处理流程
3. 最终计算逻辑正确，除细节外，大部分结果与预期一致

需调整的细节问题 #

1. 学生名字大小写区分：原表中john和John应为同一学生，但当前代码按student_name分组会因大小写差异（若Netezza开启大小写敏感）导致拆分，建议统一大小写后分组，比如将student_name替换为UPPER(student_name)或LOWER(student_name)
2. 重复年份的处理：Tim的2000年存在重复记录，当前执行结果中total_years为2，但预期为1。需先对原表的学生-年份去重，比如在student_years和filled_years中使用SELECT DISTINCT student_name, student_year FROM myt代替原表直接关联
3. 百分比格式化：预期结果保留一位小数，当前结果为多位，可使用ROUND((missed_years * 1.0 / total_years) * 100, 1)实现格式化

总结 #

该方案整体合理，是Netezza限制下实现需求的有效方案，仅需针对上述细节调整即可完全匹配预期结果。

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