Netezza SQL：统计最早关联国家与不同年份组合数的问题修正

原表结构与数据 #

建表与插入数据 #

CREATE TABLE MY_TABLE
(
    name VARCHAR(50),
    year INTEGER,
    country VARCHAR(50)
);

INSERT INTO MY_TABLE (name, year, country )
VALUES ('student1', 2010, 'usa');
INSERT INTO MY_TABLE (name, year, country )
VALUES ('student1', 2010, 'usa');
INSERT INTO MY_TABLE (name, year, country )
VALUES ('student1', 2013, 'canada');

INSERT INTO MY_TABLE (name, year, country )
VALUES ('student2', 2013, 'uk');

INSERT INTO MY_TABLE (name, year, country )
VALUES ('student3', 2020, 'usa');
INSERT INTO MY_TABLE (name, year, country )
VALUES ('student3', 2021, 'uk');
INSERT INTO MY_TABLE (name, year, country )
VALUES ('student3', 2022, 'uk');

INSERT INTO MY_TABLE (name, year, country  )
VALUES ('student4', 2009, 'usa');
INSERT INTO MY_TABLE (name, year, country )
VALUES ('student5', 2010, 'usa');

数据展示 #

name     year   country
1 student1   2010      usa
2 student1   2010      usa
3 student1   2013    canada
4 student2   2013       uk
5 student3   2020      usa
6 student3   2021      uk
7 student3   2022       uk
8 student4   2009      usa
9 student5   2010    usa

需求说明 #

步骤1：获取每个学生最早年份对应的国家，同时统计该学生的不同年份数量，预期结果：

name earliest_year earliest_country number_of_distinct_years
 student1          2010              usa                        2
 student2          2013               uk                        1
 student3          2020              usa                        3
 student4          2009              usa                        1
 student5          2010              usa                        1

步骤2：过滤掉最早年份小于2010的行，预期结果：

name earliest_year earliest_country number_of_distinct_years
 student1          2010              usa                        2
 student2          2013               uk                        1
 student3          2020              usa                        3
 student5          2010              usa                        1

步骤3：统计最早国家与不同年份数的组合出现次数，预期结果：

#expected results
  country number_of_distinct_years count
     usa                        1     1
     usa                        2     1
     usa                        3     1
      uk                        1     1

原错误SQL及问题 #

原尝试SQL（分步版） #

CREATE TABLE TABLE1 AS SELECT name,
MIN(year) AS earliest_year, 
MIN(country) AS earliest_country, 
COUNT(DISTINCT year) AS number_of_distinct_years
FROM MY_TABLE
GROUP BY name;

CREATE TABLE TABLE2 AS SELECT *
FROM TABLE1
WHERE earliest_year >= 2010;

CREATE TABLE FINAL_TABLE AS SELECT COUNT(*) AS COUNTS, 
number_of_distinct_years, earliest_country FROM TABLE2
GROUP BY  number_of_distinct_years, earliest_country;

整合为CTE版 #

WITH TABLE1 AS (
    SELECT name,
    MIN(year) AS earliest_year, 
    MIN(country) AS earliest_country, 
    COUNT(DISTINCT year) AS number_of_distinct_years
    FROM MY_TABLE
    GROUP BY name
),
TABLE2 AS (
    SELECT *
    FROM TABLE1
    WHERE earliest_year >= 2010
)
SELECT COUNT(*) AS COUNTS, 
number_of_distinct_years, earliest_country 
FROM TABLE2
GROUP BY number_of_distinct_years, earliest_country;

错误输出 #

COUNTS number_of_distinct_years earliest_country
      1                        2           canada
      1                        1               uk
      1                        3               uk
      1                        1              usa

错误原因 #

原SQL使用MIN(country)获取最早年份对应的国家是错误逻辑：MIN(country)是按字符串字典序取最小值，而非匹配最早年份的关联国家。比如student1最早年份是2010对应usa，但MIN(country)会返回canada（字母c在u之前），直接导致后续统计结果偏差。

修正后的SQL验证 #

修正后的CTE SQL #

WITH earliest_year AS (
    SELECT name, MIN(year) AS earliest_year
    FROM MY_TABLE
    GROUP BY name
),
earliest_country AS (
    SELECT e.name, e.earliest_year, m.country AS earliest_country
    FROM earliest_year e
    JOIN MY_TABLE m
    ON e.name = m.name AND e.earliest_year = m.year
),
number_of_years AS (
    SELECT e.name, e.earliest_year, e.earliest_country, COUNT(DISTINCT m.year) AS number_of_distinct_years
    FROM earliest_country e
    JOIN MY_TABLE m
    ON e.name = m.name
    GROUP BY e.name, e.earliest_year, e.earliest_country
),
filtered_years AS (
    SELECT *
    FROM number_of_years
    WHERE earliest_year >= 2010
)
SELECT COUNT(*) AS count, earliest_country, number_of_distinct_years
FROM filtered_years
GROUP BY earliest_country, number_of_distinct_years;

运行结果 #

count earliest_country number_of_distinct_years
     1              usa                        2
     1               uk                        1
     1              usa                        3
     1              usa                        1

正确性说明 #

该SQL逻辑完全正确：

1. 先通过earliest_year CTE精准获取每个学生的最早年份；
2. 再关联原表MY_TABLE，严格匹配学生+最早年份，拿到对应国家，彻底避免了原SQL的逻辑错误；
3. 后续统计不同年份数量、过滤年份、统计组合次数的逻辑均符合需求；
4. 最终结果仅列顺序与预期有差异，调整SELECT子句列顺序为earliest_country, number_of_distinct_years, COUNT(*) AS count即可与预期完全一致。

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