SQL查询验证：每年冰淇淋口味新增、重复及总数统计逻辑是否正确？

需求 #

统计每年首次出现的口味数量、此前已出现的重复口味数量，以及当年的独特口味总数。

数据表结构与数据 #

CREATE TABLE ice_cream_table  (
  "Year" INT,
  "Flavor" VARCHAR(100)
);

INSERT INTO ice_cream_table 
  ("Year", "Flavor")
VALUES
  ('2008', 'Mint'),
  ('2008', 'Mint'),
  ('2008', 'Cookie Dough'),
  ('2008', 'Cookie Dough'),
  ('2008', 'Pistachio'),
  ('2013', 'Chocolate'),
  ('2013', 'Cookie Dough'),
  ('2013', 'Pistachio'),
  ('2013', 'Chocolate'),
  ('2013', 'Pistachio'),
  ('2017', 'Chocolate'),
  ('2017', 'Vanilla'),
  ('2017', 'Chocolate'),
  ('2017', 'Cookie Dough'),
  ('2017', 'Strawberry'),
  ('2019', 'Mango'),
  ('2019', 'Lemon'),
  ('2019', 'Vanilla'),
  ('2019', 'Mango'),
  ('2019', 'Mango'),
  ('2022', 'Chocolate'),
  ('2022', 'Chocolate'),
  ('2022', 'Mint'),
  ('2022', 'Strawberry'),
  ('2022', 'Cherry');

用户编写的SQL代码 #

with yearly_flavor as (
    select 
        year,
        flavor,
        row_number() over (partition by flavor order by year) as rn 
    from 
        ice_cream_table 
    group by 
        flavor, year
),

new_flavor as (
    select
        year, 
        count(flavor) as new_flavor 
    from 
        yearly_flavor
    where 
        rn = 1
    group by 
        year
),

repeated_flavor as (
    select
        year, 
        case 
            when count(flavor) is null then 0 
            else count(flavor) 
        end as repeated_flavor,
        count(flavor) as new_flavor 
    from 
        yearly_flavor 
    where 
        rn > 1
    group by 
        year
),

total_flavor as (
    select
        year, 
        count(distinct flavor) as total_flavor 
    from 
        ice_cream_table
    group by 
        year
)

select 
    n.year,
    n.new_flavor,
    r.repeated_flavor,
    t.total_flavor
from 
    new_flavor n
left join 
    repeated_flavor r on n.year = r.year
join 
    total_flavor t on n.year = t.year
order by
    n.year;

执行结果 #

+------+-----------+----------------+--------------+
| Year | new_flavor | repeated_flavor| total_flavor |
+------+-----------+----------------+--------------+
| 2008 | 3         | NULL           | 3            |
| 2013 | 1         | 2              | 3            |
| 2017 | 2         | 2              | 4            |
| 2019 | 2         | 1              | 3            |
| 2022 | 1         | 3              | 4            |
+------+-----------+----------------+--------------+

逻辑验证与优化建议 #

逻辑准确性验证 #

逐年份核对统计逻辑：

• 2008年：所有3种口味（Mint、Cookie Dough、Pistachio）均为首次出现，因此new_flavor=3、repeated_flavor=0、total_flavor=3。结果中repeated_flavor显示NULL是因为left join无匹配，逻辑上正确但显示不友好。
• 2013年：新增口味为Chocolate（1种），重复口味为Cookie Dough、Pistachio（2种），独特口味总数3种，与结果一致。
• 2017年：新增口味为Vanilla、Strawberry（2种），重复口味为Chocolate、Cookie Dough（2种），独特口味总数4种，与结果一致。
• 2019年：新增口味为Mango、Lemon（2种），重复口味为Vanilla（1种），独特口味总数3种，与结果一致。
• 2022年：新增口味为Cherry（1种），重复口味为Chocolate、Mint、Strawberry（3种），独特口味总数4种，与结果一致。

整体统计逻辑准确，核心思路通过row_number() over (partition by flavor order by year)标记口味首次出现的年份，再分别统计新增与重复口味，逻辑清晰合理。

优化点 #

1. repeated_flavor CTE中的case语句无意义：因为where rn>1过滤后，count(flavor)不可能为NULL，可以直接写count(flavor) as repeated_flavor。
2. 主查询中处理repeated_flavor的NULL值：使用COALESCE(r.repeated_flavor, 0)替换原字段，让2008年的重复口味数量显示为0而非NULL，结果更直观。

优化后的主查询部分：

select 
    n.year,
    n.new_flavor,
    COALESCE(r.repeated_flavor, 0) as repeated_flavor,
    t.total_flavor
from 
    new_flavor n
left join 
    repeated_flavor r on n.year = r.year
join 
    total_flavor t on n.year = t.year
order by
    n.year;

内容的提问来源于stack exchange，提问作者Uk rain troll