我有一个最喜欢的食物表（称为“food_table”）-注意，没有（burger，burger）的信息：

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

字符串
我试着做一个4x 4矩阵，显示喜欢每个组合的人的相对百分比：

# step 1: counts (food1 , food2)

               pizza chocolate tacos burger

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

# step 2 :precents (each row should add to 100)
(food1 , food2)
e.g. (pizza,pizza) = 3/(3+6+9+9), (pizza,chocolate) = 6/(3+6+9+9), (pizza,tacos) = 9/(3+6+9+9), (pizza,burger) = 9/(3+6+9+9)

             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

型
我试着这样做：

with step1 as (
select
 food_1, 
food_2,
 number_of_people * 100/ sum(number_of_people) over (partition by food_1) as percent
from food_table
group by 
food_1, 
food_2),

step2 as(
select food_1 as "food1/food2",
max(case when food_2 = 'pizza' then percent end) as pizza,
max(case when food_2 = 'tacos' then percent end) as tacos,
max(case when food_2 = 'burger' then percent end) as burger,
max(case when food_2 = 'chocolate' then percent end) as chocolate
from step1
group by "food1/food2")

select * from step2;

型
但我不认为这是正确的-代码的结果不匹配我的手计算。
我该如何解决此问题？