按RecordSeriesId随机化OccurrenceDate月日并维持日期顺序

需求：

• 对Record表的每一行，随机化OccurrenceDate的日和月，年份保持与原日期一致
• 同一RecordSeriesId组（组内记录数>1）的记录，需维持原OccurrenceDate的先后顺序，随机化后后续记录的日期仍晚于之前的记录
• 表包含递增唯一INT类型主键Id

当前问题：已实现同年份的日期随机化，但无法保证同RecordSeriesId组内的记录顺序。

示例数据 #

现有脚本（未处理组内顺序） #

WITH RandomizedDates AS (
  SELECT Id,
    RecordSeriesId,
    OccurrenceDate,
    ROW_NUMBER() OVER (PARTITION BY RecordSeriesId ORDER BY OccurrenceDate) AS RowNumber,
    DATEADD(DAY, ABS(CHECKSUM(NEWID())) % 30, DATEADD(MONTH, ABS(CHECKSUM(NEWID())) % 12, DATEFROMPARTS(YEAR(OccurrenceDate), 1, 1))) AS RandomizedDate
  FROM 
    Record
)
SELECT Id,
  RecordSeriesId,
  RandomizedDate
FROM 
  RandomizedDates
ORDER BY 
  RecordSeriesId,
  RandomizedDate;

解决方案脚本 #

核心思路：先为每个RecordSeriesId+年份的分组生成一组有序的随机日期，再根据组内原顺序的行号匹配到对应记录，保证顺序不变。

WITH GroupedRecords AS (
    -- 按组和年份分组，给每条记录分配组内顺序号
    SELECT 
        Id,
        RecordSeriesId,
        OccurrenceDate,
        YEAR(OccurrenceDate) AS DateYear,
        ROW_NUMBER() OVER (PARTITION BY RecordSeriesId, YEAR(OccurrenceDate) ORDER BY OccurrenceDate) AS GroupRowNum
    FROM Record
),
RandomOrderedDates AS (
    -- 为每个(RecordSeriesId, 年份)生成一组有序的随机日期
    SELECT 
        gr.RecordSeriesId,
        gr.DateYear,
        gr.GroupRowNum,
        -- 生成同年的随机日期，覆盖全年所有可能天数
        DATEADD(DAY, ABS(CHECKSUM(NEWID())) % (DATEDIFF(DAY, DATEFROMPARTS(gr.DateYear,1,1), DATEFROMPARTS(gr.DateYear+1,1,1))), DATEFROMPARTS(gr.DateYear,1,1)) AS RandomDate,
        ROW_NUMBER() OVER (PARTITION BY gr.RecordSeriesId, gr.DateYear ORDER BY NEWID()) AS RandomRowNum
    FROM GroupedRecords gr
    GROUP BY gr.RecordSeriesId, gr.DateYear, gr.GroupRowNum
),
FinalDates AS (
    -- 将原组内顺序号和随机日期的顺序号匹配，保证原顺序不变
    SELECT 
        gr.Id,
        ro.RandomDate AS NewOccurrenceDate
    FROM GroupedRecords gr
    JOIN RandomOrderedDates ro 
        ON gr.RecordSeriesId = ro.RecordSeriesId
        AND gr.DateYear = ro.DateYear
        AND gr.GroupRowNum = ro.RandomRowNum
)
-- 执行更新操作
UPDATE Record
SET OccurrenceDate = fd.NewOccurrenceDate
FROM Record r
JOIN FinalDates fd ON r.Id = fd.Id;

脚本说明 #

1. GroupedRecords：按RecordSeriesId和年份分组，给每条记录分配基于原OccurrenceDate顺序的组内编号
2. RandomOrderedDates：为每个分组生成随机日期，并对这些日期重新分配序号，确保组内日期呈递增状态
3. FinalDates：将原记录的组内序号与随机日期的序号一一对应，保证原记录的先后顺序不变
4. 最后通过JOIN关联原表，完成日期更新

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