SQL技术问询：合并4张含相同列与唯一列的表并按维度汇总

Hey there! Let's work through this SQL challenge together—since you're new to SQL, I'll keep things clear and actionable with a concrete example you can adapt to your actual tables.

First, let's assume your four tables look like this (swap in your real table/column names):

• Sales: Has shared columns MonthDate, Department, Category, Subcategory, plus unique column TotalRevenue
• Customers: Same 4 dimension columns, plus unique column UniqueVisitors
• Returns: Same dimensions, plus unique column ItemReturns
• Promotions: Same dimensions, plus unique column PromoSpend

The core problem is no single table covers all dimension combinations, so we'll first gather every possible combination across all tables, then pull in the unique column values for each.

Step 1: Collect All Unique Dimension Combinations #

We'll use a Common Table Expression (CTE) to grab every distinct set of dimension values from all four tables. UNION automatically removes duplicates, so we only get each combination once:

WITH AllDimensionPairs AS (
    SELECT MonthDate, Department, Category, Subcategory FROM Sales
    UNION
    SELECT MonthDate, Department, Category, Subcategory FROM Customers
    UNION
    SELECT MonthDate, Department, Category, Subcategory FROM Returns
    UNION
    SELECT MonthDate, Department, Category, Subcategory FROM Promotions
)

Step 2: Join Dimension List to Each Table #

Next, we'll left-join this dimension list to each of your tables. A left join ensures we keep every dimension combination even if a table has no data for it (those missing values will show as NULL, which we can clean up later):

SELECT
    ad.MonthDate,
    ad.Department,
    ad.Category,
    ad.Subcategory,
    -- Pull in unique columns from each table
    s.TotalRevenue,
    c.UniqueVisitors,
    r.ItemReturns,
    p.PromoSpend
FROM AllDimensionPairs ad
LEFT JOIN Sales s
    ON ad.MonthDate = s.MonthDate
    AND ad.Department = s.Department
    AND ad.Category = s.Category
    AND ad.Subcategory = s.Subcategory
LEFT JOIN Customers c
    ON ad.MonthDate = c.MonthDate
    AND ad.Department = c.Department
    AND ad.Category = c.Category
    AND ad.Subcategory = c.Subcategory
LEFT JOIN Returns r
    ON ad.MonthDate = r.MonthDate
    AND ad.Department = r.Department
    AND ad.Category = r.Category
    AND ad.Subcategory = r.Subcategory
LEFT JOIN Promotions p
    ON ad.MonthDate = p.MonthDate
    AND ad.Department = p.Department
    AND ad.Category = p.Category
    AND ad.Subcategory = p.Subcategory

Step 3: Add Aggregation (If Needed) #

If you need to summarize values (like sum revenue or count visitors) for each dimension combination, first aggregate each table separately, then join to the dimension list. We'll use COALESCE to replace NULL values with 0 for cleaner results:

WITH AllDimensionPairs AS (
    SELECT MonthDate, Department, Category, Subcategory FROM Sales
    UNION
    SELECT MonthDate, Department, Category, Subcategory FROM Customers
    UNION
    SELECT MonthDate, Department, Category, Subcategory FROM Returns
    UNION
    SELECT MonthDate, Department, Category, Subcategory FROM Promotions
),
AggregatedSales AS (
    SELECT
        MonthDate, Department, Category, Subcategory,
        SUM(TotalRevenue) AS TotalSalesRevenue
    FROM Sales
    GROUP BY MonthDate, Department, Category, Subcategory
),
AggregatedCustomers AS (
    SELECT
        MonthDate, Department, Category, Subcategory,
        SUM(UniqueVisitors) AS TotalVisitors
    FROM Customers
    GROUP BY MonthDate, Department, Category, Subcategory
),
AggregatedReturns AS (
    SELECT
        MonthDate, Department, Category, Subcategory,
        SUM(ItemReturns) AS TotalItemsReturned
    FROM Returns
    GROUP BY MonthDate, Department, Category, Subcategory
),
AggregatedPromotions AS (
    SELECT
        MonthDate, Department, Category, Subcategory,
        SUM(PromoSpend) AS TotalPromoCost
    FROM Promotions
    GROUP BY MonthDate, Department, Category, Subcategory
)
SELECT
    ad.MonthDate,
    ad.Department,
    ad.Category,
    ad.Subcategory,
    COALESCE(asl.TotalSalesRevenue, 0) AS TotalSalesRevenue,
    COALESCE(ac.TotalVisitors, 0) AS TotalVisitors,
    COALESCE(ar.TotalItemsReturned, 0) AS TotalItemsReturned,
    COALESCE(ap.TotalPromoCost, 0) AS TotalPromoCost
FROM AllDimensionPairs ad
LEFT JOIN AggregatedSales asl
    ON ad.MonthDate = asl.MonthDate
    AND ad.Department = asl.Department
    AND ad.Category = asl.Category
    AND ad.Subcategory = asl.Subcategory
LEFT JOIN AggregatedCustomers ac
    ON ad.MonthDate = ac.MonthDate
    AND ad.Department = ac.Department
    AND ad.Category = ac.Category
    AND ad.Subcategory = ac.Subcategory
LEFT JOIN AggregatedReturns ar
    ON ad.MonthDate = ar.MonthDate
    AND ad.Department = ar.Department
    AND ad.Category = ar.Category
    AND ad.Subcategory = ar.Subcategory
LEFT JOIN AggregatedPromotions ap
    ON ad.MonthDate = ap.MonthDate
    AND ad.Department = ap.Department
    AND ad.Category = ap.Category
    AND ad.Subcategory = ap.Subcategory

Key Notes to Keep in Mind #

• Match Data Types: Ensure the four dimension columns (MonthDate, Department, etc.) have identical data types across all tables—otherwise, joins might fail or return unexpected results.
• Handling NULLs: If your dimension columns ever have NULL values, use database-specific logic to handle them (e.g., IS NOT DISTINCT FROM in PostgreSQL, or wrapping columns in ISNULL() in SQL Server) to avoid missing matches.
• Performance: If your tables are large, add indexes on the four dimension columns to speed up the initial UNION and subsequent joins.

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