SQL实现：按年份统计颜色首次出现与重复出现的多类别计数

嘿，我来帮你搞定这个多类别统计的需求！先理清楚咱们的目标：针对每一年，把所有出现的颜色按6个规则分成不同类别并计数，所有类别加起来就是当年的颜色总数。先看看咱们的原始数据：

原始数据表定义与数据 #

CREATE TABLE myt ( color VARCHAR(20), year INTEGER, var INTEGER );
INSERT INTO myt (color, year, var) VALUES 
('red', 2020, 1), ('red', 2021, 1), ('red', 2022, 0), ('red', 2023, 1),
('blue', 2020, 0), ('blue', 2021, 0), ('blue', 2022, 0), ('blue', 2023, 1),
('green', 2021, 1), ('green', 2022, 1), ('green', 2023, 0),
('yellow', 2021, 0), ('yellow', 2022, 0), ('yellow', 2023, 0),
('purple', 2022, 1), ('purple', 2023, 1),
('orange', 2022, 0), ('orange', 2023, 1),
('pink', 2023, 1), ('black', 2023, 0),
('white', 2020, 0), ('white', 2022, 1), ('white', 2023, 0);

原始数据展示：

优化后的通用SQL实现 #

你之前的转置思路可行，但硬编码年份扩展性差（新增年份就得改代码）。我给你写一个更通用的版本，不用固定年份：

WITH color_metadata AS (
    -- 统计每个颜色的首次出现年份、历史是否出现过var=1
    SELECT 
        color,
        MIN(year) AS first_year,
        MAX(CASE WHEN var = 1 THEN 1 ELSE 0 END) AS has_ever_var1
    FROM myt
    GROUP BY color
),
yearly_color_data AS (
    -- 关联元数据，计算当前年份之前是否所有var都是0
    SELECT 
        m.year,
        m.color,
        m.var AS current_var,
        cm.first_year,
        cm.has_ever_var1,
        CASE 
            WHEN EXISTS (
                SELECT 1 
                FROM myt m_prev 
                WHERE m_prev.color = m.color 
                  AND m_prev.year < m.year 
                  AND m_prev.var = 1
            ) THEN 0 
            ELSE 1 
        END AS all_prev_var0
    FROM myt m
    JOIN color_metadata cm ON m.color = cm.color
),
categorized_colors AS (
    -- 按规则给每个颜色的每年记录分配类别
    SELECT 
        year,
        color,
        CASE
            -- 类别1：首次出现且当前var=1
            WHEN year = first_year AND current_var = 1 THEN 'category1'
            -- 类别2：首次出现且当前var=0
            WHEN year = first_year AND current_var = 0 THEN 'category2'
            -- 类别3：重复出现，之前所有var都是0且当前var=0
            WHEN year > first_year AND all_prev_var0 = 1 AND current_var = 0 THEN 'category3'
            -- 类别6：重复出现，之前所有var都是0且当前var首次为1
            WHEN year > first_year AND all_prev_var0 = 1 AND current_var = 1 THEN 'category6'
            -- 类别4：重复出现，当前var=1且之前有过var=1记录
            WHEN year > first_year AND current_var = 1 AND has_ever_var1 = 1 AND all_prev_var0 = 0 THEN 'category4'
            -- 类别5：重复出现，当前var=0但之前有过var=1记录
            WHEN year > first_year AND current_var = 0 AND has_ever_var1 = 1 THEN 'category5'
            ELSE 'other' -- 理论上不会触发
        END AS category
    FROM yearly_color_data
)
-- 按年份统计各类别数量
SELECT 
    year,
    COUNT(CASE WHEN category = 'category1' THEN 1 END) AS category1,
    COUNT(CASE WHEN category = 'category2' THEN 1 END) AS category2,
    COUNT(CASE WHEN category = 'category3' THEN 1 END) AS category3,
    COUNT(CASE WHEN category = 'category4' THEN 1 END) AS category4,
    COUNT(CASE WHEN category = 'category5' THEN 1 END) AS category5,
    COUNT(CASE WHEN category = 'category6' THEN 1 END) AS category6
FROM categorized_colors
GROUP BY year
ORDER BY year;

思路说明 #

1. color_metadata：先拿到每个颜色的核心元数据，为后续分类打基础；
2. yearly_color_data：关联元数据后，计算all_prev_var0这个关键指标——用来判断当前年份之前的记录是否全是var=0；
3. categorized_colors：严格按照你定义的6个类别规则，给每条记录分配对应类别；
4. 最后一步按年份分组统计，得到最终的分类计数结果。

执行结果 #

运行上述SQL后，会得到你预期的结果：

这个版本的好处是不用硬编码年份，以后新增年份数据时，直接跑SQL就能自动统计，不用修改代码逻辑~

内容来源于stack exchange