SQL按Location分组获取PartNumberID最后变更后的最小日期记录

• 业务表需按Location维度分组，提取每个Location下最新PartNumberID自最后一次值变更后，对应的最小RecordAddedDate记录，即每个Location当前最新PartNumberID首次出现的行
• 注意：ExpectedRow为人工标注列，不属于原表结构

原CTE写法未识别PartNumberID随时间变更的断点，直接关联同Location下同PartNumberID的全量历史记录，会将历史上出现过、后续被其他PartNumberID打断的同ID记录纳入统计，典型如Location=15的场景：早期PartNumberID=1的记录和最新的PartNumberID=1记录中间被ID=2的记录打断，原逻辑会错误取到最早的2022-06-23，而非正确的2022-06-26。

采用SQL经典的*间隙岛屿（Gaps and Islands）*思路处理连续值分段问题，步骤如下：

1. 按Location分区、RecordAddedDate升序排序，通过窗口函数LAG()获取每条记录上一行的PartNumberID，标记值变更断点
2. 累加断点标记，为每个连续相同PartNumberID的分段生成唯一段ID
3. 取每个Location下时间最晚的分段，返回该分段内最小的RecordAddedDate即为目标行

兼容MySQL 8.0+、SQL Server、PostgreSQL等支持窗口函数的数据库版本，代码如下：

WITH sorted_with_prev AS (
    SELECT
        Location,
        RecordAddedDate,
        PartNumberID,
        -- 获取同Location下按时间排序的上一条记录的PartNumberID
        LAG(PartNumberID) OVER (
            PARTITION BY Location 
            ORDER BY RecordAddedDate
        ) AS prev_part_id
    FROM mytable
    WHERE RecordDeleted = 0 -- 保留原逻辑中过滤已删除记录的条件
),
segment_mark AS (
    SELECT
        Location,
        RecordAddedDate,
        PartNumberID,
        -- 遇到值和上一条不一致则标记为新分段起点，累加生成分段ID
        SUM(
            CASE WHEN prev_part_id IS NULL OR prev_part_id <> PartNumberID THEN 1 ELSE 0 END
        ) OVER (
            PARTITION BY Location 
            ORDER BY RecordAddedDate 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS segment_id
    FROM sorted_with_prev
),
latest_segment_calc AS (
    SELECT
        Location,
        RecordAddedDate,
        PartNumberID,
        segment_id,
        -- 标记每个Location下最新的分段
        ROW_NUMBER() OVER (
            PARTITION BY Location 
            ORDER BY segment_id DESC
        ) AS segment_rn,
        -- 计算每个分段内最早的记录日期
        MIN(RecordAddedDate) OVER (
            PARTITION BY Location, segment_id
        ) AS first_date_of_segment
    FROM segment_mark
)
SELECT DISTINCT
    Location,
    first_date_of_segment AS RecordAddedDate,
    PartNumberID
FROM latest_segment_calc
WHERE segment_rn = 1
ORDER BY Location;

执行后返回结果与期望输出完全一致。

内容的提问来源于stack exchange，提问作者Alejandro Gomez Guerrero