Netezza SQL中统计年份组合出现次数的错误修正求助

表结构与测试数据 #

CREATE TABLE MY_TABLE (
    id VARCHAR(50),
    year VARCHAR(50)
);

INSERT INTO MY_TABLE (id, year) VALUES ('123', '2010');
INSERT INTO MY_TABLE (id, year) VALUES ('123', '2011');
INSERT INTO MY_TABLE (id, year) VALUES ('123', '2012'); 

INSERT INTO MY_TABLE (id, year) VALUES ('124', '2011');
INSERT INTO MY_TABLE (id, year) VALUES ('124', '2012');
INSERT INTO MY_TABLE (id, year) VALUES ('124', '2013');

INSERT INTO MY_TABLE (id, year) VALUES ('125', '2011');
INSERT INTO MY_TABLE (id, year) VALUES ('125', '2012');
INSERT INTO MY_TABLE (id, year) VALUES ('125', '2013');

INSERT INTO MY_TABLE (id, year) VALUES ('127', '2011');
INSERT INTO MY_TABLE (id, year) VALUES ('127', '2012');
INSERT INTO MY_TABLE (id, year) VALUES ('127', '2015');

INSERT INTO MY_TABLE (id, year) VALUES ('126', '2019');

需求 #

统计每种年份组合的出现次数，预期结果：

combination freq
1   2011,2012,2015    1
2   2011,2012,2013    2
3 2010,2011,2012    1
4             2019    1

尝试的代码及错误结果 #

原尝试的SQL代码：

WITH CTE AS (
    SELECT id, year, ROW_NUMBER() OVER (PARTITION BY id ORDER BY year) AS rn
    FROM MY_TABLE
),
CTE2 AS (
    SELECT id, MAX(rn) AS max_rn
    FROM CTE
    GROUP BY id
),
CTE3 AS (
    SELECT CTE2.id, CTE.year, CTE.rn, CTE2.max_rn
    FROM CTE2
    JOIN CTE ON CTE2.id = CTE.id
),
CTE4 AS (
    SELECT id,
        MAX(CASE WHEN rn = 1 THEN year END) ||
        MAX(CASE WHEN rn = 2 THEN ',' || year END) ||
        MAX(CASE WHEN rn = 3 THEN ',' || year END) ||
        MAX(CASE WHEN rn = 4 THEN ',' || year END) ||
        MAX(CASE WHEN rn = 5 THEN ',' || year END) ||
        MAX(CASE WHEN rn = 6 THEN ',' || year END) ||
        MAX(CASE WHEN rn = 7 THEN ',' || year END) ||
        MAX(CASE WHEN rn = 8 THEN ',' || year END) ||
        MAX(CASE WHEN rn = 9 THEN ',' || year END) ||
        MAX(CASE WHEN rn = 10 THEN ',' || year END)
        AS combination
    FROM CTE3
    GROUP BY id
)
SELECT combination, COUNT(*) AS freq
FROM CTE4
GROUP BY combination
ORDER BY freq DESC;

运行后得到错误结果：

combination freq
1                                              <NA>   94
2 2010,2012,2013,2014,2015,2016,2017,2018,2019,2020    1
3 2010,2011,2012,2013,2014,2015,2016,2017,2019,2020    1
4 2010,2011,2012,2013,2014,2016,2017,2018,2019,2020    1
5 2010,2011,2012,2014,2015,2016,2017,2018,2019,2020    1
6 2010,2011,2013,2014,2015,2016,2017,2018,2019,2020    1
7 2010,2011,2012,2013,2014,2015,2016,2017,2018,2019    1

补充说明 #

• 使用rn=10是为了覆盖数据中的年份范围（2010-2020），希望能用UNION ALL方式编写手动解决方案；
• Netezza无GROUP_CONCAT函数，否则可用以下代码实现需求：

  SELECT GROUP_CONCAT(year, ',' ORDER BY year) AS combination, COUNT(*) AS freq
  FROM my_data
  GROUP BY id
  ORDER BY freq DESC;
  

• 类似功能的R代码：

  agg <- aggregate(year ~ id, my_table, paste, collapse = ", ")
  final = as.data.frame(table(agg$year))
  

一、修正原代码 #

原代码问题点：

1. CTE3属于冗余逻辑，直接使用CTE即可；
2. 字符串拼接时，未匹配到的CASE语句返回NULL，导致整个拼接结果变为NULL，需用COALESCE处理空值；
3. 无需提前计算max_rn，直接在分组时处理即可。

修正后的代码：

WITH CTE AS (
    SELECT 
        id, 
        year, 
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY year) AS rn
    FROM MY_TABLE
),
CTE4 AS (
    SELECT 
        id,
        COALESCE(MAX(CASE WHEN rn = 1 THEN year END), '') ||
        COALESCE(MAX(CASE WHEN rn = 2 THEN ',' || year END), '') ||
        COALESCE(MAX(CASE WHEN rn = 3 THEN ',' || year END), '') ||
        COALESCE(MAX(CASE WHEN rn = 4 THEN ',' || year END), '') ||
        COALESCE(MAX(CASE WHEN rn = 5 THEN ',' || year END), '') ||
        COALESCE(MAX(CASE WHEN rn = 6 THEN ',' || year END), '') ||
        COALESCE(MAX(CASE WHEN rn = 7 THEN ',' || year END), '') ||
        COALESCE(MAX(CASE WHEN rn = 8 THEN ',' || year END), '') ||
        COALESCE(MAX(CASE WHEN rn = 9 THEN ',' || year END), '') ||
        COALESCE(MAX(CASE WHEN rn = 10 THEN ',' || year END), '') AS combination
    FROM CTE
    GROUP BY id
)
SELECT 
    combination, 
    COUNT(*) AS freq
FROM CTE4
WHERE combination <> '' -- 过滤空组合（若存在）
GROUP BY combination
ORDER BY freq DESC;

二、使用UNION ALL的手动解决方案 #

若已知每个id的年份数量上限（如测试数据中最多3个），可按年份数量拆分处理：

-- 统计每个id的年份数量
WITH id_year_count AS (
    SELECT id, COUNT(*) AS cnt
    FROM MY_TABLE
    GROUP BY id
),
-- 处理单年份的id
single_year AS (
    SELECT 
        mt.id, 
        mt.year AS combination
    FROM MY_TABLE mt
    JOIN id_year_count yc ON mt.id = yc.id
    WHERE yc.cnt = 1
    GROUP BY mt.id, mt.year
),
-- 处理双年份的id
double_year AS (
    SELECT 
        mt.id,
        CONCAT(mt1.year, ',', mt2.year) AS combination
    FROM MY_TABLE mt
    JOIN id_year_count yc ON mt.id = yc.id
    JOIN MY_TABLE mt1 ON mt.id = mt1.id AND mt1.year = MIN(mt.year) OVER (PARTITION BY mt.id)
    JOIN MY_TABLE mt2 ON mt.id = mt2.id AND mt2.year = MAX(mt.year) OVER (PARTITION BY mt.id)
    WHERE yc.cnt = 2
    GROUP BY mt.id, mt1.year, mt2.year
),
-- 处理三年份的id
triple_year AS (
    SELECT 
        mt.id,
        CONCAT(mt1.year, ',', mt2.year, ',', mt3.year) AS combination
    FROM MY_TABLE mt
    JOIN id_year_count yc ON mt.id = yc.id
    JOIN (SELECT id, year FROM MY_TABLE WHERE ROW_NUMBER() OVER (PARTITION BY id ORDER BY year) = 1) mt1 ON mt.id = mt1.id
    JOIN (SELECT id, year FROM MY_TABLE WHERE ROW_NUMBER() OVER (PARTITION BY id ORDER BY year) = 2) mt2 ON mt.id = mt2.id
    JOIN (SELECT id, year FROM MY_TABLE WHERE ROW_NUMBER() OVER (PARTITION BY id ORDER BY year) = 3) mt3 ON mt.id = mt3.id
    WHERE yc.cnt = 3
    GROUP BY mt.id, mt1.year, mt2.year, mt3.year
),
-- 合并所有组合
all_combinations AS (
    SELECT combination FROM single_year
    UNION ALL
    SELECT combination FROM double_year
    UNION ALL
    SELECT combination FROM triple_year
)
-- 统计频率
SELECT combination, COUNT(*) AS freq
FROM all_combinations
GROUP BY combination
ORDER BY freq DESC;

该方案需提前明确id的年份数量上限，适合年份数量较少且固定的场景。

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