如何替代Checksum实现考勤punch_start小时变更的检测逻辑？

需要检测昨日与今日考勤文件中，因意外时区小时变更（非分钟级）导致punch_start字段小时数变化的数据。

数据示例 #

Dataset1（昨日文件） #

chcecksum   person_id applied_date punch_start          punch_end            punch_hours
-1552866149  650067    2022-09-04   2022-09-04T20:11:00Z 2022-09-04T22:52:00Z 2.68333333333333
-1367087212  650067    2022-09-04   2022-09-04T22:52:00Z 2022-09-04T23:26:00Z 0.566666666666667

Dataset2（今日文件） #

chcecksum   person_id applied_date  punch_start           punch_end             punch_hours
-1564056421 650067    2022-09-04    2022-09-04T20:11:00Z  2022-09-04T22:52:00Z  2.683333333
-1470176798 650067    2022-09-04    2022-09-04T20:52:00Z  2022-09-04T23:26:00Z  0.566666667

现有问题 #

• 使用Checksum无法满足需求：即使punch_start未变化，仅punch_hours精度差异（如2.683333333变为2.68333）会生成不同Checksum，干扰有效数据筛选。
• 尝试生成的唯一ID存在重复：

  CONCAT(
          [person_id],
          [applied_date] ,
          [punch_hours], 
          DATEPART(MINUTE,  CONVERT(DATETIME, CAST([punch_start] AS DATETIME), 112)) 
  )
  

  同一员工当日两段时长相同、分钟数相同的考勤记录（如9:00-12:00和13:00-17:00）会生成重复ID，无法准确匹配对应记录。

1. 生成稳定的唯一匹配键 #

要避免重复，需结合更多能区分同员工同日同时长同分钟的字段，比如punch_end的分钟部分，同时统一punch_hours的精度，避免小数位数差异影响匹配：

CONCAT(
    [person_id],
    [applied_date],
    -- 统一punch_hours精度为6位小数，消除精度差异
    ROUND([punch_hours], 6),
    -- 取punch_start的分钟部分
    DATEPART(MINUTE, CONVERT(DATETIME, [punch_start])),
    -- 加入punch_end的分钟部分，区分同start分钟、同时长的记录
    DATEPART(MINUTE, CONVERT(DATETIME, [punch_end]))
) AS unique_match_key

如果需要更严谨的匹配（比如排除跨天的特殊情况），可以直接保留punch_start和punch_end的日期+分钟部分（去掉小时）：

CONCAT(
    [person_id],
    [applied_date],
    -- 保留punch_start的日期和分钟，去掉小时
    FORMAT(CONVERT(DATETIME, [punch_start]), 'yyyyMMddmm'),
    -- 保留punch_end的日期和分钟，去掉小时
    FORMAT(CONVERT(DATETIME, [punch_end]), 'yyyyMMddmm')
) AS unique_match_key

2. 关联数据集并筛选小时变化记录 #

生成匹配键后，关联昨日和今日的数据集，对比punch_start的小时部分是否不同：

-- 先为两个数据集生成匹配键
WITH Dataset1_with_key AS (
    SELECT
        *,
        CONCAT(
            [person_id],
            [applied_date],
            ROUND([punch_hours], 6),
            DATEPART(MINUTE, CONVERT(DATETIME, [punch_start])),
            DATEPART(MINUTE, CONVERT(DATETIME, [punch_end]))
        ) AS unique_match_key
    FROM Dataset1
),
Dataset2_with_key AS (
    SELECT
        *,
        CONCAT(
            [person_id],
            [applied_date],
            ROUND([punch_hours], 6),
            DATEPART(MINUTE, CONVERT(DATETIME, [punch_start])),
            DATEPART(MINUTE, CONVERT(DATETIME, [punch_end]))
        ) AS unique_match_key
    FROM Dataset2
)
-- 关联并筛选小时变化的记录
SELECT
    d1.person_id,
    d1.applied_date,
    d1.punch_start AS 昨日打卡开始时间,
    d2.punch_start AS 今日打卡开始时间,
    DATEPART(HOUR, CONVERT(DATETIME, d1.punch_start)) AS 昨日小时数,
    DATEPART(HOUR, CONVERT(DATETIME, d2.punch_start)) AS 今日小时数
FROM Dataset1_with_key d1
JOIN Dataset2_with_key d2
    ON d1.unique_match_key = d2.unique_match_key
WHERE
    DATEPART(HOUR, CONVERT(DATETIME, d1.punch_start)) <> DATEPART(HOUR, CONVERT(DATETIME, d2.punch_start))

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