Location_ID变更时重置ROW_NUMBER()值的SQL实现问题

你当前的写法直接将Location_ID放入PARTITION BY子句，会将同EventDate、Asset_ID下所有Location_ID相同的行归为同一个分区，完全忽略了Location_ID的时序切换逻辑。当同一Location_ID中间切换为其他值再切回时，会被归到同一个分区计数，自然得不到你要的切换重置效果。

你要的效果属于SQL经典的「间断序列分组（孤岛问题）」场景，不需要额外做CTE关联，通过两层嵌套窗口函数即可实现：

1. 先用LAG()窗口函数标记Location_ID和上一行相比发生变更的行
2. 对变更标记做累加求和，得到连续相同Location_ID的专属分组ID
3. 基于该分组ID做行号计数即可

示例代码如下：

SELECT
  *,
  ROW_NUMBER() OVER (
    PARTITION BY EventDate, Asset_ID, Location_Group
    ORDER BY Scan_Timestamp
  ) AS RowNumY
FROM (
  SELECT
    *,
    -- 累加变更标记，连续相同Location_ID的行会得到相同的分组ID
    SUM(Change_Flag) OVER (
      PARTITION BY EventDate, Asset_ID
      ORDER BY Scan_Timestamp
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS Location_Group
  FROM (
    SELECT
      *,
      -- 和上一行Location_ID对比，发生变更则标记为1，否则为0
      CASE 
        WHEN Location_ID = LAG(Location_ID, 1, '') OVER (
          PARTITION BY EventDate, Asset_ID
          ORDER BY Scan_Timestamp
        ) THEN 0 
        ELSE 1 
      END AS Change_Flag
    FROM 你的业务表
  ) t1
) t2

如果你的数据库支持MATCH_RECOGNIZE语法（如Oracle、Snowflake、Spark SQL 3.0+、Trino），还可以用更简洁的模式匹配写法，直接识别连续相同Location_ID的序列：

SELECT *
FROM 你的业务表
MATCH_RECOGNIZE (
  PARTITION BY EventDate, Asset_ID
  ORDER BY Scan_Timestamp
  MEASURES 
    COUNT(*) AS RowNumY
  ALL ROWS PER MATCH
  PATTERN (same_loc+)
  DEFINE same_loc AS Location_ID = FIRST(Location_ID)
)

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