如何在SQL中将食品偏好表转换为百分比矩阵？

问题背景 #

现有food_table食品偏好表，缺少(burger, burger)的记录，需生成4×4百分比矩阵，每行占比总和为100%，展示不同食品组合的偏好人数占比。手动计算的计数矩阵和百分比矩阵已明确，但自行编写的CTE SQL结果与预期不符，需调整语句。

原表数据 #

food_1    food_2 number_of_people
     pizza     pizza                3
 chocolate     pizza                3
     tacos     pizza               10
    burger     pizza                2
     pizza chocolate                6
 chocolate chocolate                5
     tacos chocolate                4
    burger chocolate                6
     pizza     tacos                9
 chocolate     tacos               10
     tacos     tacos                5
    burger     tacos                3
     pizza    burger                9
 chocolate    burger                9
     tacos    burger                9

预期结果 #

计数矩阵 #

pizza chocolate tacos burger
 pizza         3         6     9      9
 chocolate     3         5    10      9
 tacos        10         4     5      9
 burger      NULL        6     3      3

百分比矩阵 #

pizza chocolate    tacos   burger
pizza     11.11111  22.22222 33.33333 33.33333
chocolate 11.11111  18.51852 37.03704 33.33333
tacos     35.71429  14.28571 17.85714 32.14286
burger    NULL       50       25       25

原SQL语句问题分析 #

原SQL存在以下核心问题：

• 未处理缺失记录：未补全(burger, burger)的缺失数据，导致该行百分比计算时总和错误，无法匹配预期结果。
• 多余分组子句：step1中添加的GROUP BY food_1, food_2属于冗余操作，窗口函数SUM(...) OVER (PARTITION BY food_1)已按food_1完成分组求和，额外分组会干扰计算逻辑。
• 未覆盖全量组合：未生成所有可能的food_1与food_2组合，导致缺失的列无法在矩阵中显示为NULL。

修正后的SQL语句 #

WITH all_foods AS (
    -- 获取所有唯一食品名称
    SELECT DISTINCT food_1 AS food FROM food_table
    UNION
    SELECT DISTINCT food_2 AS food FROM food_table
),
all_combinations AS (
    -- 生成所有food1与food2的笛卡尔积组合
    SELECT f1.food AS food_1, f2.food AS food_2
    FROM all_foods f1
    CROSS JOIN all_foods f2
),
filled_data AS (
    -- 左连接原表，补全缺失的(burger, burger)记录，其他缺失记录保留NULL
    SELECT 
        ac.food_1,
        ac.food_2,
        CASE 
            WHEN ac.food_1 = 'burger' AND ac.food_2 = 'burger' THEN 3
            WHEN ft.number_of_people IS NULL THEN NULL
            ELSE ft.number_of_people
        END AS number_of_people
    FROM all_combinations ac
    LEFT JOIN food_table ft ON ac.food_1 = ft.food_1 AND ac.food_2 = ft.food_2
),
percent_calculations AS (
    -- 计算每行的百分比，排除NULL值参与求和
    SELECT 
        food_1,
        food_2,
        CASE 
            WHEN number_of_people IS NULL THEN NULL
            ELSE number_of_people * 100.0 / SUM(number_of_people) OVER (PARTITION BY food_1) 
        END AS percent
    FROM filled_data
)
-- 转换为矩阵格式，保留6位小数
SELECT 
    food_1 AS "food1/food2",
    ROUND(MAX(CASE WHEN food_2 = 'pizza' THEN percent END), 6) AS pizza,
    ROUND(MAX(CASE WHEN food_2 = 'chocolate' THEN percent END), 6) AS chocolate,
    ROUND(MAX(CASE WHEN food_2 = 'tacos' THEN percent END), 6) AS tacos,
    ROUND(MAX(CASE WHEN food_2 = 'burger' THEN percent END), 6) AS burger
FROM percent_calculations
GROUP BY food_1
ORDER BY food_1;

修正说明 #

1. 生成全量食品组合：通过all_foods和all_combinations确保每个food_1都对应所有food_2选项，避免矩阵缺失列。
2. 补全缺失数据：针对(burger, burger)手动填充3条记录，其他缺失记录保留NULL以匹配预期计数矩阵。
3. 正确计算百分比：在求和时自动排除NULL值，确保每行百分比总和为100%，同时保留NULL的展示格式。
4. 格式化输出：使用ROUND函数保留6位小数，与预期结果的精度一致。

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