PostgreSQL中如何避免插入存在时间戳重叠的客户通话记录

Fixing Overlapping Customer Calls on Insert #

Your current approach picks a random customer ID without checking for existing overlapping calls, which is why you're seeing conflicts. The while loop you tried doesn't work because its condition is logically incorrect (you're comparing the record's call start to itself, which will never trigger the loop). Here's how to properly implement the check to avoid overlaps:

Step-by-Step Solution

1. Declare a variable to hold the valid customer ID we'll find.
2. For each record in temp_sdg_calls:
   • Keep selecting random customer IDs until we find one that has no existing calls overlapping with the current record's time window.
   • The overlap check uses the condition: existing_call_start < current_call_end AND existing_call_end > current_call_start (this catches all cases where two time ranges overlap).
3. Handle the existing customer_id IS NULL record in aa_dev.sdg_calls by finding a valid customer ID for it as well.

Corrected PL/SQL Code

DECLARE
    v_customer_id aa_dev.sdg_calls.customer_id%TYPE;
    v_null_call_start aa_dev.sdg_calls.call_start%TYPE;
    v_null_call_end aa_dev.sdg_calls.call_end%TYPE;
BEGIN
    -- First, process inserts from temp_sdg_calls
    FOR rec IN (SELECT t.agent_id, t.call_start, t.call_end, t.aht FROM temp_sdg_calls t) LOOP
        -- Loop until we find a customer with no overlapping calls
        LOOP
            -- Pick a random non-null customer ID from existing records
            SELECT customer_id INTO v_customer_id
            FROM aa_dev.sdg_calls
            WHERE customer_id IS NOT NULL
            ORDER BY random() LIMIT 1;
            
            -- Check if this customer has any overlapping calls with the current record
            IF NOT EXISTS (
                SELECT 1 
                FROM aa_dev.sdg_calls c
                WHERE c.customer_id = v_customer_id
                AND c.call_start < rec.call_end
                AND c.call_end > rec.call_start
            ) THEN
                EXIT; -- Valid customer found, exit inner loop
            END IF;
        END LOOP;
        
        -- Insert the record with the valid customer ID
        INSERT INTO aa_dev.sdg_calls (agent_id, call_start, call_end, aht, customer_id)
        VALUES (rec.agent_id, rec.call_start, rec.call_end, rec.aht, v_customer_id);
    END LOOP;
    
    -- Now handle the existing customer_id NULL record
    -- First get its call time range
    SELECT call_start, call_end INTO v_null_call_start, v_null_call_end
    FROM aa_dev.sdg_calls
    WHERE customer_id IS NULL;
    
    -- Find a valid customer ID for this record
    LOOP
        SELECT customer_id INTO v_customer_id
        FROM aa_dev.sdg_calls
        WHERE customer_id IS NOT NULL
        ORDER BY random() LIMIT 1;
        
        -- Check for overlaps with this customer's existing calls
        IF NOT EXISTS (
            SELECT 1 
            FROM aa_dev.sdg_calls c
            WHERE c.customer_id = v_customer_id
            AND c.call_start < v_null_call_end
            AND c.call_end > v_null_call_start
        ) THEN
            EXIT;
        END IF;
    END LOOP;
    
    -- Update the NULL record with the valid customer ID
    UPDATE aa_dev.sdg_calls
    SET customer_id = v_customer_id
    WHERE customer_id IS NULL;
END;
/

Key Notes:

• Overlap Check: The NOT EXISTS subquery ensures we only select customers who don't have any calls that overlap with the current record's time frame. This covers all overlap scenarios (one call starts during another, ends during another, or fully contains it).
• Inner Loop: The inner loop keeps picking random customer IDs until a valid one is found. This guarantees we won't insert overlapping calls for the same customer.
• Handling NULL Record: We first retrieve the time range of the existing NULL record, then find a valid customer ID for it using the same overlap check, before updating the record.

This approach ensures that all records in aa_dev.sdg_calls (both existing and new) have no overlapping calls for the same customer.

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