验证SQL中按用户年度计算累计百分比的方案正确性

原表结构与数据 #

CREATE TABLE myt (
  name VARCHAR(50),
  food VARCHAR(50),
  d1 INT
);

INSERT INTO myt (name, food, d1) VALUES
('john', 'pizza', 2010),
('john', 'pizza', 2011),
('john', 'cake', 2012),
('tim', 'apples', 2015),
('david', 'apples', 2020),
('david', 'apples', 2021),
('alex', 'cookies', 2005),
('alex', 'cookies', 2006);

表数据展示：

name    food   d1 food_year
  john   pizza 2010      2010
  john   pizza 2011      2011
  john    cake 2012      2012
   tim  apples 2015      2015
 david  apples 2020      2020
 david  apples 2021      2021
  alex cookies 2005      2005
  alex cookies 2006      2006

原查询：计算用户各食物总占比 #

原查询用于统计每个用户每种食物占其总记录数的百分比：

WITH FoodCounts AS (
    SELECT name, 
           food, 
           COUNT(*) as food_count
    FROM myt
    GROUP BY name, food
),
TotalCounts AS (
    SELECT name, 
           COUNT(*) as total_count
    FROM myt
    GROUP BY name
)
SELECT fc.name, 
       fc.food, 
       (fc.food_count * 100.0) / tc.total_count as percentage
FROM FoodCounts fc
JOIN TotalCounts tc
ON fc.name = tc.name;

查询结果：

name    food percentage
  alex cookies  100.00000
 david  apples  100.00000
  john    cake   33.33333
  john   pizza   66.66667
   tim  apples  100.00000

需求：计算累计时间维度的食物占比 #

需要修改查询，计算截至指定年份的食物占比，比如：

• 截至2011年John的食物占比情况
• 截至2012年John的食物占比情况

尝试的解决方案 #

使用CTE和窗口函数编写的查询：

WITH YearlyFoodCounts AS (
    SELECT name, 
           food, 
           food_year,
           COUNT(*) as food_count
    FROM myt
    GROUP BY name, food, food_year
),
CumulativeCounts AS (
    SELECT name, 
           food_year,
           SUM(food_count) OVER (PARTITION BY name ORDER BY food_year) as cumulative_count
    FROM YearlyFoodCounts
)
SELECT yfc.name, 
       yfc.food, 
       yfc.food_year,
       yfc.food_count,
       cc.cumulative_count,
       (yfc.food_count * 100.0) / cc.cumulative_count as percentage
FROM YearlyFoodCounts yfc
JOIN CumulativeCounts cc
ON yfc.name = cc.name AND yfc.food_year = cc.food_year
ORDER BY yfc.name, yfc.food_year;

查询结果：

name    food food_year food_count cumulative_count percentage
  alex cookies      2005          1                1  100.00000
  alex cookies      2006          1                2   50.00000
 david  apples      2020          1                1  100.00000
 david  apples      2021          1                2   50.00000
  john   pizza      2010          1                1  100.00000
  john   pizza      2011          1                2   50.00000
  john    cake      2012          1                3   33.33333
   tim  apples      2015          1                1  100.00000

方案验证 #

你的处理方式完全正确，核心逻辑精准匹配需求：

1. YearlyFoodCounts按用户、食物、年份分组，准确统计了每个用户每年每种食物的记录数，为后续累计计算打下基础；
2. CumulativeCounts中使用窗口函数SUM(food_count) OVER (PARTITION BY name ORDER BY food_year)，按用户分区、年份排序，计算出截至对应年份的累计总记录数，保证了累计的时间顺序正确性；
3. 最后通过关联两个CTE，计算出当年该食物记录数占截至当年累计总数的百分比，结果完全符合预期——比如John截至2011年累计2条记录，2011年的pizza占比50%；截至2012年累计3条，当年的cake占比33.33%，和需求中的示例完全匹配。

额外优化建议 #

可以合并CTE简化语句，避免额外的JOIN操作，提升查询效率，结果完全一致：

WITH YearlyFoodCounts AS (
    SELECT name, 
           food, 
           d1 as food_year,
           COUNT(*) as food_count
    FROM myt
    GROUP BY name, food, d1
)
SELECT name, 
       food, 
       food_year,
       food_count,
       SUM(food_count) OVER (PARTITION BY name ORDER BY food_year) as cumulative_count,
       (food_count * 100.0) / SUM(food_count) OVER (PARTITION BY name ORDER BY food_year) as percentage
FROM YearlyFoodCounts
ORDER BY name, food_year;

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