基于INPUT字段历史值变化更新FLAG字段的SQL实现需求

Alright, let's figure out how to solve this. You want to update the FLAG column to 1 whenever the INPUT value changes for the same PERSON_ID (compared to their prior record sorted by START_DATE), and 0 when it stays the same. Here's a solid approach using window functions, which works across most modern databases:

The key here is to use the LAG() window function to grab the previous INPUT value for each person, ordered by their START_DATE. We can then compare that previous value to the current one to set the correct FLAG.

1. First, verify the logic with a SELECT query #

Before updating the table, it's always smart to check that our logic returns the right values. Run this query to see what the updated FLAG should be:

SELECT 
    INPUT,
    START_DATE,
    PERSON_ID,
    CASE 
        WHEN LAG(INPUT) OVER (PARTITION BY PERSON_ID ORDER BY START_DATE) != INPUT THEN 1
        ELSE 0
    END AS calculated_flag
FROM your_table_name;

For your sample data, this will output exactly the FLAG values you have:

INPUT START_DATE PERSON_ID calculated_flag
42707 2017-01-01 227317 0
40000 2018-01-01 227317 1
42400 2019-01-01 227317 1
42400 2019-01-02 227317 0

2. Update the FLAG column #

Once you confirm the logic is correct, use one of these approaches to update the table. The syntax varies a bit by database, but here are the most common versions:

For MySQL 8.0+, PostgreSQL, or SQL Server (using CTE) #

This is the cleanest method for most databases that support Common Table Expressions (CTEs):

WITH updated_flag_values AS (
    SELECT 
        PERSON_ID,
        START_DATE,
        CASE 
            WHEN LAG(INPUT) OVER (PARTITION BY PERSON_ID ORDER BY START_DATE) != INPUT THEN 1
            ELSE 0
        END AS new_flag
    FROM your_table_name
)
UPDATE your_table_name t
SET FLAG = uf.new_flag
FROM updated_flag_values uf
WHERE t.PERSON_ID = uf.PERSON_ID 
  AND t.START_DATE = uf.START_DATE;

For MySQL 8.0+ (alternative subquery approach) #

If CTEs feel unfamiliar, you can use a correlated subquery instead:

UPDATE your_table_name t
SET FLAG = (
    SELECT CASE 
        WHEN LAG(INPUT) OVER (PARTITION BY PERSON_ID ORDER BY START_DATE) != INPUT THEN 1
        ELSE 0
    END
    FROM your_table_name t2
    WHERE t2.PERSON_ID = t.PERSON_ID 
      AND t2.START_DATE = t.START_DATE
);

How this works #

Let's break down the key parts:

• PARTITION BY PERSON_ID: Groups all records by each individual person, so we only compare INPUT values within the same person's history.
• ORDER BY START_DATE: Ensures we're comparing each record to the earliest prior record for that person (so we're checking changes in chronological order).
• LAG(INPUT): Pulls the INPUT value from the immediately preceding record in the ordered group. The first record for each person will have no prior value (returns NULL), so the CASE statement sets FLAG to 0 for that record.
• The CASE statement checks if the current INPUT is different from the prior one: if yes, set FLAG to 1; if no (or if there's no prior record), set it to 0.

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