如何使用Presto或MySQL基于基准组填充其他分组的缺失记录

To solve this problem, we need to ensure every group has all the product records present in the base group—retaining existing quantities where they exist, and using the base group's quantity for any missing entries. Here's a straightforward SQL approach:

Step-by-Step Breakdown #

1. Capture Base Group Reference: First, we extract all product names and their quantities from the base group—this acts as our source of truth for missing entries.
2. Get All Unique Groups: We collect every distinct group name in the table to make sure we cover all groups that need updating.
3. Generate Full Product-Group Pairs: Using a cross join between base products and all groups, we create every possible product-group combination. This guarantees no product is missing from any group.
4. Merge with Original Data: Left join these combinations with the original table to pull in existing quantities, then fall back to the base quantity if no entry exists for a product-group pair.

SQL Query #

WITH base_data AS (
    -- Extract all products and their quantities from the base group
    SELECT prod_name, quantity
    FROM your_table
    WHERE "group" = 'base'
),
all_groups AS (
    -- Get every distinct group present in the table
    SELECT DISTINCT "group"
    FROM your_table
),
full_product_group_pairs AS (
    -- Create all possible product-group combinations
    SELECT bg.prod_name, ag."group"
    FROM base_data bg
    CROSS JOIN all_groups ag
)
-- Combine with original data, using base quantity where entries are missing
SELECT 
    fpgp.prod_name,
    COALESCE(t.quantity, bd.quantity) AS quantity,
    fpgp."group"
FROM full_product_group_pairs fpgp
LEFT JOIN your_table t 
    ON fpgp.prod_name = t.prod_name 
    AND fpgp."group" = t."group"
JOIN base_data bd 
    ON fpgp.prod_name = bd.prod_name
ORDER BY fpgp."group", fpgp.prod_name;

Key Notes #

• Replace your_table with the actual name of your dataset table.
• The COALESCE function ensures we use existing quantities first; if a product is missing in a group, it defaults to the base group's quantity for that product.
• The final ORDER BY clause organizes the output to match the expected structure, grouping by group name and sorting products alphabetically.

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