无更新权限下，如何用Lead/Lag函数修正TableB的日期重叠问题

Absolutely, you can resolve this overlapping date issue using LEAD()—that’s exactly the right window function for the job! Since you don’t have data update permissions, we can create a corrected version of TableB (as a CTE, view, or temp table) that adjusts end dates to eliminate overlaps without modifying the original data.

Let’s Break Down the Solution #

First, I’m assuming TableB includes an id field to link it to TableA’s customer records (since your sample SQL uses PARTITION BY ID). If that’s missing, you’ll just need to join TableA and TableB first to associate each area record with the correct customer ID.

Step-by-Step SQL Implementation #

We’ll use a CTE to calculate the next record’s start date for each customer, then adjust the current record’s end date to avoid overlaps:

WITH ranked_customer_areas AS (
    SELECT
        b.id,
        b.StartDate,
        b.EndDate,
        b.AREA,
        -- Fetch the start date of the next area record for the same customer
        LEAD(b.StartDate) OVER (PARTITION BY b.id ORDER BY b.StartDate) AS next_area_start
    FROM TableB b
    -- Uncomment below if TableB doesn't have an id field, to link to TableA
    -- JOIN TableA a ON /* Add your join condition here, e.g., a.id = b.cust_id */
)
SELECT
    id,
    StartDate,
    -- Adjust end date to avoid overlap with the next area's start date
    CASE
        WHEN next_area_start IS NOT NULL AND EndDate >= next_area_start 
            THEN next_area_start - INTERVAL '1 day'
        ELSE EndDate
    END AS corrected_EndDate,
    AREA
FROM ranked_customer_areas
ORDER BY id, StartDate;

How This Works for Your Example Data #

For John (id=1) with these original TableB records:

The query will output:

This fixes the overlap on 1/8/19 and 1/9/19 perfectly.

Handling Multiple Overlapping Days #

This solution scales to multiple overlapping records too. For example, if you have three overlapping area entries for a customer:

The query will adjust all end dates to eliminate overlaps:

What If You Need to Persist the Corrected Data? #

Since you can’t update the original TableB, you can:

• Create a view using this query, so you can reference the corrected data just like a table.
• Export the results to a temporary table for ad-hoc analysis.

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