Netezza SQL如何批量运行min_year与var1mod所有组合的查询

我正在使用Netezza SQL，现有数据表my_table的结构及数据如下：

id year var1 var3 date_1
1  1 2017    1    1    NA
2  1 2018    0    1    NA
3  1 2019    1    1    NA
4  2 2017    0    1    NA
5  2 2018    1    1    NA
6  3 2017    1    1    NA
7  3 2018    1    1    NA
8  3 2019    0    1    NA

当前使用的查询仅针对min_year=2000和var1mod=0的情况执行：

WITH cte1 AS (
    SELECT
        id,
        year,
        SUM(var1) AS var1mod,
        date_1
    FROM my_table
    WHERE var3 = 1
    GROUP BY id, year, date_1
),

cte2 AS (
    SELECT
        id
    FROM cte1
    WHERE var1mod = 0
),

cte3 AS (
    SELECT
        id,
        COUNT(DISTINCT year) AS year_count
    FROM cte1
    WHERE id IN (SELECT id FROM cte2)
    AND date_1 IS NULL
    GROUP BY id
),

cte4 AS (
    SELECT
        id,
        MIN(year) AS min_year
    FROM cte1
    GROUP BY id
)

SELECT
    year_count,
    COUNT(*) AS count_per_year
FROM cte3
WHERE id IN (SELECT id FROM cte4 WHERE min_year = 2000)
GROUP BY year_count;

目前我通过多次复制查询并使用UNION ALL来实现所有min_year与var1mod组合的统计，但希望优化这种方式，请问如何修改查询以直接聚合得到所有min_year与var1mod组合的结果？

你可以通过整合CTE逻辑，将min_year作为每个ID的属性，同时把var1mod的取值作为分组维度，一次性统计所有组合的结果，无需重复执行查询并拼接UNION ALL。

优化后的查询语句如下：

WITH cte1 AS (
    SELECT
        id,
        year,
        SUM(var1) AS var1mod,
        date_1
    FROM my_table
    WHERE var3 = 1
    GROUP BY id, year, date_1
),
-- 计算每个ID的基础属性：最小年份、有效年份计数（仅date_1为NULL的记录）
id_base_info AS (
    SELECT
        id,
        MIN(year) AS min_year,
        COUNT(DISTINCT year) AS year_count
    FROM cte1
    WHERE date_1 IS NULL
    GROUP BY id
),
-- 关联每个ID对应的所有var1mod值（一个ID可能对应多个不同的var1mod）
id_var1mod_map AS (
    SELECT DISTINCT
        ib.id,
        ib.min_year,
        ib.year_count,
        c1.var1mod
    FROM id_base_info ib
    JOIN cte1 c1 ON ib.id = c1.id
    WHERE c1.date_1 IS NULL
)
-- 按三个维度分组统计最终结果
SELECT
    min_year,
    var1mod,
    year_count,
    COUNT(*) AS count_per_year
FROM id_var1mod_map
-- 若需筛选特定var1mod值，可在此添加WHERE条件，例如 WHERE var1mod IN (0,1)
GROUP BY min_year, var1mod, year_count
ORDER BY min_year, var1mod, year_count;

核心改进点： #

• 用id_base_info一次性计算每个ID的min_year和year_count，避免重复的分组查询操作
• 通过id_var1mod_map将每个ID对应的所有var1mod值展开，确保每个ID的每个var1mod取值都被纳入统计
• 最终直接按min_year、var1mod、year_count三个维度分组，一次性输出所有组合的统计结果，彻底替代多次UNION ALL的繁琐方式

如果你的需求仅针对存在var1mod=0记录的ID，可以在id_var1mod_map的JOIN后添加过滤条件：

AND EXISTS (SELECT 1 FROM cte1 c2 WHERE c2.id = ib.id AND c2.var1mod = 0)

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