SQL删除重复记录:同ID同EnrollmentMonth下保留参保天数更高的有效记录
实现方案
核心思路
按「同一ID + 同一EnrollmentMonth」维度分组,计算每条记录在对应统计自然月内的有效参保天数,仅保留天数最高的1条记录,实现每个统计月、每个ID下只保留1条有效记录的目标。
有效天数计算规则
每条记录在统计月的有效参保天数,为记录本身的参保区间[STARTDATE, ENDDATE]与当前EnrollmentMonth所属自然月区间[月初第一天, 月末最后一天]的交集覆盖天数,无交集时天数为0。
结合提供的样本校验逻辑准确性:
- 2020年7月的统计区间为2020-07-01 ~ 2020-07-31
- Peter的参保区间为2019-01-01 ~ 2020-07-29,与统计月的交集为29天
- Tony的参保区间为2020-07-30 ~ 9999-12-31,与统计月的交集为2天
计算结果与实际参保时长完全匹配。
通用SQL实现
以下代码适配MySQL 8.0+、PostgreSQL等主流支持窗口函数的数据库,可直接替换原表名后运行:
WITH month_range AS ( -- 计算每个EnrollmentMonth对应的自然月首尾边界 SELECT ID, NAME, STARTDATE, ENDDATE, EnrollmentMonth, STR_TO_DATE(EnrollmentMonth, '%Y%m%d') AS stat_month_start, LAST_DAY(STR_TO_DATE(EnrollmentMonth, '%Y%m%d')) AS stat_month_end FROM 原表名 ), days_calc AS ( -- 计算每条记录在对应统计月的有效参保天数 SELECT *, CASE -- 两区间无交集时有效天数为0 WHEN LEAST(STR_TO_DATE(ENDDATE, '%Y%m%d'), stat_month_end) < GREATEST(STR_TO_DATE(STARTDATE, '%Y%m%d'), stat_month_start) THEN 0 -- 有交集时计算实际覆盖天数(日期差+1,包含首尾两天) ELSE DATEDIFF(LEAST(STR_TO_DATE(ENDDATE, '%Y%m%d'), stat_month_end), GREATEST(STR_TO_DATE(STARTDATE, '%Y%m%d'), stat_month_start)) + 1 END AS valid_days FROM month_range ), record_rank AS ( -- 按ID+EnrollmentMonth分组,对有效天数做降序排名 SELECT *, ROW_NUMBER() OVER ( PARTITION BY ID, EnrollmentMonth ORDER BY valid_days DESC ) AS rn FROM days_calc ) -- 提取每组排名第1的记录,即为最终筛选结果 SELECT ID, NAME, STARTDATE, ENDDATE, EnrollmentMonth FROM record_rank WHERE rn = 1;
适配注意事项
- 如果使用SQL Server数据库,只需替换日期处理函数:将
STR_TO_DATE(字段, '%Y%m%d')替换为CONVERT(DATE, 字段, 112),LAST_DAY()替换为EOMONTH(),调整DATEDIFF参数顺序即可,核心逻辑无需改动。 - 对于ENDDATE为
99991231这类代表长期参保的特殊值,无需做额外判断,计算交集时会自动被取小函数限制在统计月范围内,不会出现计算偏差。 - 如果遇到两条记录有效天数完全相等的边界场景,可在窗口函数的
ORDER BY后追加排序规则(比如优先保留参保起始时间更早的记录),避免随机取值的不确定性。
内容的提问来源于stack exchange,提问作者J.p
相关产品推荐
相关产品推荐

