时间戳

首页 > 解决方案 > 前一个分区 ID 更改时重置 ROW_NUMBER()

sql - 前一个分区 ID 更改时重置 ROW_NUMBER()

问题描述

当按日期时间顺序排序时其中一个 ID 发生更改时，我试图重置 ROW_NUMBER() 值。例如在下表中，当 Location_ID 更改时，我想将 RowNum 重置为 1。这是我正在使用的语句。

[RowNum] = ROW_NUMBER() OVER (PARTITION BY EventDate, Asset_ID, Location_ID ORDER BY Scan_Timestamp)

我得到 RowNumX，但想要 RowNumY。

活动日期 Scan_Timestamp 资产 ID Location_ID 行数X 行数
2021 年 9 月 1 日 21 年 9 月 1 日上午 12 点 28 分 30010712 996 1 1
2021 年 9 月 1 日 21 年 9 月 1 日上午 06:18 30010712 30000372 1 1
2021 年 9 月 1 日 21 年 9 月 1 日上午 06:52 30010712 30000345 1 1
2021 年 9 月 1 日 21 年 9 月 1 日上午 8 点 43 分 30010712 996 2 1 重置（Loc_ID 已更改）
2021 年 9 月 1 日 21 年 9 月 1 日上午 8 点 44 分 30010712 996 3 2
2021 年 9 月 1 日 21 年 9 月 1 日上午 8 点 47 分 30010712 30000402 1 1
2021 年 9 月 1 日 21 年 9 月 1 日上午 11:17 30010712 996 4 1 重置（Loc_ID 已更改）
2021 年 9 月 1 日 21 年 9 月 1 日上午 11:17 30010712 997 1 1
2021 年 9 月 1 日 21 年 9 月 1 日下午 1 点 34 分 30010712 997 2 2
2021 年 9 月 1 日 21 年 9 月 1 日下午 1 点 47 分 30010712 30000402 2 1 重置（Loc_ID 已更改）
2021 年 9 月 1 日 21 年 9 月 1 日下午 1 点 51 分 30010712 997 3 1 重置（Loc_ID 已更改）
2021 年 9 月 1 日 21 年 9 月 1 日下午 1 点 52 分 30010712 997 4 2

我想我必须使用 CTE 加入，但希望有人会看到一个我忽略的明显简单的解决方案。

标签： sqlsql-servertsql

解决方案

这是一种差距和孤岛问题。

有很多解决方案，这里有一个：

  • 用于LAG识别发生LocationID变化的行
  • 使用窗口计数为每个岛创建分组 ID
  • 然后使用此分组 ID 作为分区列计算行号
WITH Changes AS (
    SELECT *,
      IsChange = CASE WHEN LAG(Location_ID, 1, -999) OVER (PARTITION BY EventDate, Asset_ID
                      ORDER BY Scan_Timestamp) <> Location_ID THEN 1 END
    FROM YourTable t
),
Groups AS (
    SELECT *,
      GroupId = COUNT(IsChange) OVER (PARTITION BY EventDate, Asset_ID
                      ORDER BY Scan_Timestamp ROWS UNBOUNDED PRECEDING)
    FROM Changes
)
SELECT
  EventDate,
  Scan_Timestamp,
  Asset_ID,
  Location_ID,GroupId,IsChange,
  RowNumY = ROW_NUMBER() OVER (PARTITION BY EventDate, Asset_ID, GroupId ORDER BY Scan_Timestamp)
FROM Groups;

db<>小提琴

推荐阅读