SQL计算连续年份间颜色的总数、新增、流失及留存数量的查询修正求助

我有一个记录颜色对应年份的SQL表，表结构和初始化数据如下：

CREATE TABLE myt ( color TEXT, year INTEGER );
INSERT INTO myt (color, year) VALUES 
('red', 2000), ('blue', 2000), ('green', 2000), ('cyan', 2000), ('white', 2000),
('blue', 2001), ('green', 2001), ('yellow', 2001), ('black', 2001), ('white', 2001),
('green', 2002), ('yellow', 2002), ('orange', 2002), ('black', 2002), ('pink', 2002),
('yellow', 2003), ('orange', 2003), ('purple', 2003), ('pink', 2003), ('grey', 2003);

我想要针对每一对连续的年份，计算这四个指标：

• 当年的总颜色数量
• 相比上一年的新增颜色数（仅当年出现、上年没有的颜色）
• 相比上一年的流失颜色数（上年有但当年没再出现的颜色）
• 从上年留存的颜色数（当年和上年都存在的颜色）

我尝试用CTE写了一个查询，但结果有明显问题：不仅多了一行NA NA的无效数据，而且流失颜色数（left_colors）的计算完全错误（比如2001对比2000，实际流失了red和cyan两种颜色，但查询结果显示0）。我的原始查询代码如下：

WITH distinct_colors AS (
    SELECT DISTINCT color, year FROM myt
),
year_pairs AS (
    SELECT 2001 AS current_year, 2000 AS previous_year
    UNION ALL SELECT 2002, 2001
    UNION ALL SELECT 2003, 2002
),
curr_prev_colors AS (
    SELECT 
        yp.current_year, 
        yp.previous_year, 
        curr.color AS curr_color, 
        prev.color AS prev_color
    FROM year_pairs yp
    LEFT JOIN distinct_colors curr ON curr.year = yp.current_year
    FULL OUTER JOIN distinct_colors prev 
        ON prev.year = yp.previous_year AND curr.color = prev.color
)
SELECT 
    current_year, 
    previous_year,
    COUNT(DISTINCT curr_color) AS total_colors,
    COUNT(DISTINCT CASE WHEN curr_color IS NOT NULL AND prev_color IS NOT NULL THEN curr_color END) AS from_last_year,
    COUNT(DISTINCT CASE WHEN curr_color IS NOT NULL AND prev_color IS NULL THEN curr_color END) AS new_colors,
    COUNT(DISTINCT CASE WHEN curr_color IS NULL AND prev_color IS NOT NULL THEN prev_color END) AS left_colors
FROM curr_prev_colors
GROUP BY current_year, previous_year
ORDER BY current_year;

得到的错误输出：

有没有大佬能帮我修正这个查询，得到正确的结果？

解决方案 #

你的查询主要有两个问题：一是FULL JOIN的关联逻辑错误，导致无法正确捕获“上年有但当年无”的颜色；二是没有过滤无效的跨年份对行，出现了NA的无效行。下面提供两种修正方案：

方案1：用集合运算实现（简洁高效，适合支持数组函数的数据库）

这种写法通过预计算每年的颜色集合，再用集合交集、差集来计算指标，而且不需要硬编码年份，扩展性更强：

WITH distinct_colors AS (
    -- 提取每年的唯一颜色
    SELECT DISTINCT year, color FROM myt
),
-- 自动生成连续年份对（无需硬编码，适配任意年份范围）
year_pairs AS (
    SELECT 
        curr.year AS current_year, 
        prev.year AS previous_year
    FROM (SELECT DISTINCT year FROM distinct_colors) curr
    INNER JOIN (SELECT DISTINCT year FROM distinct_colors) prev 
        ON curr.year = prev.year + 1
),
-- 预计算每年的颜色列表和总数量
year_color_sets AS (
    SELECT 
        year,
        ARRAY_AGG(DISTINCT color) AS color_list,
        COUNT(DISTINCT color) AS total_colors
    FROM distinct_colors
    GROUP BY year
)
SELECT 
    yp.current_year,
    yp.previous_year,
    curr.total_colors,
    -- 留存颜色数：两年共有的颜色数量（集合交集的大小）
    (SELECT COUNT(*) 
     FROM unnest(curr.color_list) curr_color
     INNER JOIN unnest(prev.color_list) prev_color ON curr_color = prev_color) AS from_last_year,
    -- 新增颜色数：当年总颜色数 - 留存数（集合差集 curr \ prev 的大小）
    curr.total_colors - (SELECT COUNT(*) 
                         FROM unnest(curr.color_list) curr_color
                         INNER JOIN unnest(prev.color_list) prev_color ON curr_color = prev_color) AS new_colors,
    -- 流失颜色数：上年总颜色数 - 留存数（集合差集 prev \ curr 的大小）
    prev.total_colors - (SELECT COUNT(*) 
                         FROM unnest(curr.color_list) curr_color
                         INNER JOIN unnest(prev.color_list) prev_color ON curr_color = prev_color) AS left_colors
FROM year_pairs yp
INNER JOIN year_color_sets curr ON curr.year = yp.current_year
INNER JOIN year_color_sets prev ON prev.year = yp.previous_year
ORDER BY yp.current_year;

方案2：兼容所有SQL数据库的写法（不用数组函数）

如果你的数据库不支持数组相关函数（比如MySQL 5.x），可以用JOIN和CASE语句的组合来实现：

WITH distinct_colors AS (
    SELECT DISTINCT year, color FROM myt
),
year_pairs AS (
    SELECT 
        curr.year AS current_year, 
        prev.year AS previous_year
    FROM (SELECT DISTINCT year FROM distinct_colors) curr
    INNER JOIN (SELECT DISTINCT year FROM distinct_colors) prev 
        ON curr.year = prev.year + 1
),
-- 关联当年和上年的所有颜色，覆盖所有可能的情况
curr_prev_colors AS (
    SELECT 
        yp.current_year,
        yp.previous_year,
        curr.color AS curr_color,
        prev.color AS prev_color
    FROM year_pairs yp
    -- 用FULL JOIN关联当年和上年的颜色，确保不遗漏任何情况
    FULL OUTER JOIN distinct_colors curr ON curr.year = yp.current_year
    FULL OUTER JOIN distinct_colors prev 
        ON prev.year = yp.previous_year AND curr.color = prev.color
    -- 过滤掉不属于当前年份对的无效行
    WHERE yp.current_year IS NOT NULL
)
SELECT 
    current_year,
    previous_year,
    -- 当年总颜色数
    COUNT(DISTINCT curr_color) AS total_colors,
    -- 留存颜色数：两年都存在的颜色
    COUNT(DISTINCT CASE WHEN curr_color IS NOT NULL AND prev_color IS NOT NULL THEN curr_color END) AS from_last_year,
    -- 新增颜色数：当年有、上年没有的颜色
    COUNT(DISTINCT CASE WHEN curr_color IS NOT NULL AND prev_color IS NULL THEN curr_color END) AS new_colors,
    -- 流失颜色数：上年有、当年没有的颜色
    COUNT(DISTINCT CASE WHEN curr_color IS NULL AND prev_color IS NOT NULL THEN prev_color END) AS left_colors
FROM curr_prev_colors
GROUP BY current_year, previous_year
ORDER BY current_year;

正确结果展示 #

运行上述任意一个查询，都会得到符合预期的结果：

关键修正说明 #

1. 自动生成年份对：不再硬编码固定年份对，而是通过curr.year = prev.year +1自动关联连续年份，新增年份后无需修改代码。
2. 正确统计流失颜色：通过FULL JOIN覆盖所有颜色的存在情况，再用CASE语句精准筛选“上年有、当年无”的颜色，解决了原始查询中流失数为0的错误。
3. 去除无效NA行：通过过滤条件或INNER JOIN年份对，彻底消除了无意义的NULL年份行。

内容来源于stack exchange