基于Group By、Rank、Row_Number的重复客户合并及关联表更新求助

Got it, let's solve this problem step by step. The key is to first identify which duplicate customer IDs should be kept (winners) based on your rules, then map the losing IDs to the winners, update the reference table, and finally clean up the losing entries.

Step 1: Understand the Winning Rules #

• VIP customers always win, regardless of order date.
• For non-VIP duplicates, the customer with the latest order date wins.
• If there's a tie (same VIP status and order date), we'll use the smallest Cust_ID as the winner to keep things deterministic (you can adjust this if needed).

Step 2: SQL Solution #

First, we'll create a mapping of losing Cust_IDs to their corresponding winning Cust_IDs, then use that mapping to update the #OrderCustomerRef table, and finally delete the losing entries from #CustomerOrders.

Full SQL Code

-- Step 1: Identify winning Cust_IDs and create a mapping for losers to winners
WITH CustomerRankings AS (
    -- Get each Cust_ID's VIP status and latest order date
    SELECT 
        Cust_ID,
        LName,
        FName,
        [Customer_E-mail],
        -- Mark as VIP if any entry for this Cust_ID is VIP
        MAX(CASE WHEN CustomerType = 'VIP' THEN 1 ELSE 0 END) AS IsVIP,
        -- Get the latest order date for this Cust_ID
        MAX(Order_Date) AS LatestOrderDate
    FROM #CustomerOrders
    GROUP BY Cust_ID, LName, FName, [Customer_E-mail]
),
Winners AS (
    -- Rank each Cust_ID in its duplicate group to find the winner
    SELECT 
        Cust_ID,
        LName,
        FName,
        [Customer_E-mail],
        ROW_NUMBER() OVER (
            PARTITION BY LName, FName, [Customer_E-mail] 
            -- Order by VIP first, then latest date, then smallest Cust_ID for ties
            ORDER BY IsVIP DESC, LatestOrderDate DESC, Cust_ID ASC
        ) AS Rank
    FROM CustomerRankings
),
CustIDMapping AS (
    -- Create a mapping from losing Cust_IDs to winning ones
    SELECT 
        w_loser.Cust_ID AS LosingCustID,
        w_winner.Cust_ID AS WinningCustID
    FROM Winners w_loser
    JOIN Winners w_winner 
        ON w_loser.LName = w_winner.LName 
        AND w_loser.FName = w_winner.FName 
        AND w_loser.[Customer_E-mail] = w_winner.[Customer_E-mail]
    WHERE w_loser.Rank > 1 
        AND w_winner.Rank = 1
)
-- Step 2: Update the OrderCustomerRef table to replace losing Cust_IDs with winners
UPDATE ocr
SET ocr.Cust_ID = cm.WinningCustID
FROM #OrderCustomerRef ocr
JOIN CustIDMapping cm ON ocr.Cust_ID = cm.LosingCustID;

-- Step3: Delete losing Cust_ID entries from CustomerOrders
WITH CustomerRankings AS (
    SELECT 
        Cust_ID,
        LName,
        FName,
        [Customer_E-mail],
        MAX(CASE WHEN CustomerType = 'VIP' THEN 1 ELSE 0 END) AS IsVIP,
        MAX(Order_Date) AS LatestOrderDate
    FROM #CustomerOrders
    GROUP BY Cust_ID, LName, FName, [Customer_E-mail]
),
Winners AS (
    SELECT 
        Cust_ID,
        ROW_NUMBER() OVER (
            PARTITION BY LName, FName, [Customer_E-mail] 
            ORDER BY IsVIP DESC, LatestOrderDate DESC, Cust_ID ASC
        ) AS Rank
    FROM CustomerRankings
)
DELETE FROM #CustomerOrders
WHERE Cust_ID IN (SELECT Cust_ID FROM Winners WHERE Rank > 1);

How This Works #

1. CustomerRankings CTE: For each unique Cust_ID, we determine if it's a VIP (by checking if any of its entries are VIP) and get its latest order date.
2. Winners CTE: We rank each Cust_ID within its duplicate group (same name and email). The rank 1 is assigned to the winner based on your rules.
3. CustIDMapping CTE: Creates a lookup table that maps every losing Cust_ID to the winning one in its group.
4. Update Statement: Uses the mapping to replace all losing Cust_IDs in #OrderCustomerRef with the winning ones.
5. Delete Statement: Removes all losing Cust_ID entries from #CustomerOrders.

Testing with Your Sample Data #

Let's verify a few cases from your sample:

• John Smith: Cust_ID 2 (VIP) wins over Cust_ID 1 (Regular), so OrderID 1's Cust_ID updates to 2.
• Thomas Johnson: Cust_ID9 (latest order date 2016-05-01) wins over 10 and 11, so OrderIDs 11 and12 update to9.
• James Snatcher: Cust_ID6 (smallest ID) wins over 7 (same status and date), so OrderID8 updates to6.

This should handle all your requirements correctly!

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