MySQL 8.0含LEAD分区查询转MySQL 5.7方案求助

Got it, let's work through converting your MySQL 8.0 UPDATE query to be compatible with 5.7. The core challenge here is replacing the LEAD() window function (which isn't available in 5.7) with logic that replicates its behavior using either self-joins or session variables.

First, let's recap what your original query does:

• It targets rows in better.DimUserTemp where the id exists in better.user_update_staging
• For rows where end_date is NULL, it sets end_date to the start_date of the next row in the same id group (sorted by start_date)
• Rows with a non-NULL end_date stay as-is

Option 1: Self-Join (Most Intuitive for Maintenance) #

This approach uses a self-join to find the next start_date for each row. It's straightforward and easy to debug, making it a good choice if your dataset isn't extremely large.

UPDATE `better`.`DimUserTemp` se
JOIN (
    -- Subquery to get the next start_date for each row in the same id group
    SELECT 
        se1.id,
        se1.start_date,
        MIN(se2.start_date) AS next_start_date
    FROM `better`.`DimUserTemp` se1
    JOIN `better`.`DimUserTemp` se2 
        ON se1.id = se2.id 
        AND se2.start_date > se1.start_date -- Only match rows with later start dates
    WHERE se1.id IN (SELECT id FROM `better`.user_update_staging)
    GROUP BY se1.id, se1.start_date
) next_dates 
    ON se.id = next_dates.id 
    AND se.start_date = next_dates.start_date
SET se.end_date = next_dates.next_start_date
WHERE se.end_date IS NULL;

How this works:

• The subquery joins each row (se1) with all other rows in the same id group that have a later start_date
• MIN(se2.start_date) grabs the earliest of those later dates, which is exactly what LEAD() returns (the next row's start_date when sorted)
• We only update rows where end_date is NULL, matching your original logic
• If a row is the last one in its id group (no later rows), it won't appear in the subquery, so its end_date stays NULL (just like LEAD() returns NULL for the final row)

Option 2: Session Variables (Better for Large Datasets) #

If you're working with a large table, the self-join might get slow. This method uses session variables to simulate the LEAD() window function more efficiently.

UPDATE `better`.`DimUserTemp` se
JOIN (
    SELECT 
        id,
        start_date,
        @next_start := CASE 
            WHEN @current_id = id THEN @current_start 
            ELSE NULL 
        END AS next_start_date,
        -- Update variables for the next row (order matters here!)
        @current_start := start_date,
        @current_id := id
    FROM (
        -- First, sort rows by id and start_date in DESC order
        SELECT id, start_date
        FROM `better`.`DimUserTemp`
        WHERE id IN (SELECT id FROM `better`.user_update_staging)
        ORDER BY id, start_date DESC
    ) sorted
    -- Initialize variables
    CROSS JOIN (SELECT @current_id := NULL, @current_start := NULL) vars
) next_dates 
    ON se.id = next_dates.id 
    AND se.start_date = next_dates.start_date
SET se.end_date = next_dates.next_start_date
WHERE se.end_date IS NULL;

How this works:

• We first sort the target rows in descending order of start_date within each id group
• As we iterate through the sorted rows, we use variables to "remember" the start_date of the previous row (which, in descending order, is the next row in the original ascending sort)
• The CASE statement checks if we're still in the same id group; if so, it sets next_start_date to the remembered start_date
• This mimics the LEAD() behavior without needing a join, which can be much faster for large datasets

Important Testing Tip #

Before running the UPDATE, always verify the results with a SELECT query to make sure the new end_date values match what you expect. For example, using the self-join approach:

SELECT 
    se.id,
    se.start_date,
    se.end_date,
    next_dates.next_start_date AS new_end_date
FROM `better`.`DimUserTemp` se
LEFT JOIN (
    SELECT 
        se1.id,
        se1.start_date,
        MIN(se2.start_date) AS next_start_date
    FROM `better`.`DimUserTemp` se1
    JOIN `better`.`DimUserTemp` se2 
        ON se1.id = se2.id 
        AND se2.start_date > se1.start_date
    WHERE se1.id IN (SELECT id FROM `better`.user_update_staging)
    GROUP BY se1.id, se1.start_date
) next_dates 
    ON se.id = next_dates.id 
    AND se.start_date = next_dates.start_date
WHERE se.id IN (SELECT id FROM `better`.user_update_staging);

This lets you confirm that new_end_date is correct before modifying your table.

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