使用 presto 或 mysql 添加每个组中的缺失值

MySQL和Presto/Trino是两种有点不同的SQL方言.基本思路是一样的--打造产品和群体的笛卡尔产品的"基".如果您确定所有产品都在基地，您可以简单地使用以下Presto/Trino:

-- sample data
WITH dataset(prod_name, quantity, "group") as (
    values ('tv'       , 30, 'base'),
        ('microwave', 10, 'base'),
        ('watch'    , 5 , 'base'),
        ('phone'    , 25, 'base'),
        ('washer'   , 15, 'base'),
        ('dryer'   , 14, 'base'),
        ('microwave', 7 , 'inventory_2021'),
        ('phone'    , 16, 'inventory_2021'),
        ('tv'       , 30, 'inventory_2022'),
        ('watch'    , 5 , 'inventory_2022'),
        ('phone'    , 25, 'inventory_2022')
),
-- query parts
base_products AS (
    SELECT prod_name, quantity
    FROM dataset AS p1
    WHERE "group" = 'base' -- select all base products
),
all_groups as (
    SELECT distinct "group" grp
    FROM dataset
),
base_all as(
    SELECT b.prod_name, b.quantity, grp -- build base lookup table
    FROM base_products b
    CROSS JOIN all_groups
)

select b.prod_name,
    coalesce(d.quantity, b.quantity) quantity, -- use "original" quantity
    b.grp "group"
from base_all b
left join dataset d on b.prod_name = d.prod_name and b.grp = d."group"
;

输出: