如何用集合式方法实现不重复的Top N预约-日期范围匹配

数据表结构 #

Table 1（预约表） #

Table 2（日期范围表） #

需求说明 #

将Table 1中的预约按ApptDate从早到晚的顺序，匹配到Table 2中符合条件的日期范围（即ApptDate落在Date From和Date To之间）；当某个日期范围匹配的预约数达到其Allowed Num Appointments上限后，不再参与后续匹配；每个预约只能被匹配一次。最终生成Table 3，包含字段：ApptID、ApptDate、Matched（bit类型，标识是否匹配成功）、DateRangeId（匹配到的日期范围ID，未匹配则为NULL）。

当前问题 #

已通过游标实现该逻辑，但大数据量下性能极差；尝试用row_count()做Top N分组匹配时，出现同一预约被多次匹配的问题，不符合需求。

集合式解决方案 #

可以通过CTE+窗口函数的组合实现纯集合式的匹配逻辑，核心思路是先给预约按日期排序，再给每个符合条件的日期范围和预约的组合分配优先级，最后筛选出每个预约的最优匹配，同时确保日期范围不超过允许的匹配数。

以下是适用于SQL Server的实现代码：

WITH RankedAppointments AS (
    -- 给预约按日期排序，生成序号，确保匹配遵循"先到先得"顺序
    SELECT 
        ApptID,
        ApptDate,
        ROW_NUMBER() OVER (ORDER BY ApptDate) AS ApptRank
    FROM Table1
),
RangeApptMatches AS (
    -- 关联符合日期条件的预约和日期范围，给每个日期范围的匹配条目按预约顺序排序
    SELECT 
        ra.ApptID,
        ra.ApptDate,
        dr.DateRangeId,
        dr.[Allowed Num Appointments],
        ROW_NUMBER() OVER (PARTITION BY dr.DateRangeId ORDER BY ra.ApptRank) AS RangeMatchRank
    FROM RankedAppointments ra
    JOIN Table2 dr ON ra.ApptDate BETWEEN dr.[Date From] AND dr.[Date To]
),
FinalMatches AS (
    -- 筛选出每个日期范围内未超限额的匹配，同时给每个预约的多个匹配分配优先级，只保留第一个有效匹配
    SELECT 
        ApptID,
        ApptDate,
        DateRangeId,
        ROW_NUMBER() OVER (PARTITION BY ApptID ORDER BY DateRangeId) AS ApptMatchRank
    FROM RangeApptMatches
    WHERE RangeMatchRank <= [Allowed Num Appointments]
)
-- 生成最终结果表
SELECT 
    t1.ApptID,
    t1.ApptDate,
    CASE WHEN fm.DateRangeId IS NOT NULL THEN 1 ELSE 0 END AS Matched,
    fm.DateRangeId
FROM Table1 t1
LEFT JOIN FinalMatches fm ON t1.ApptID = fm.ApptID AND fm.ApptMatchRank = 1
ORDER BY t1.ApptDate;

逻辑说明 #

1. RankedAppointments：为所有预约按日期顺序生成唯一序号，保证匹配的先后顺序。
2. RangeApptMatches：关联所有符合日期条件的预约与日期范围，同时给每个日期范围内的匹配条目按预约序号排序，便于判断该范围已匹配的预约数量。
3. FinalMatches：过滤掉日期范围超限额的匹配，同时给每个预约的多个可能匹配分配优先级（示例按DateRangeId排序，可根据实际需求调整规则），仅保留每个预约的第一个有效匹配。
4. 最后通过左连接Table1与FinalMatches，生成包含匹配状态的最终结果。

性能优化建议 #

• 给Table1的ApptDate字段创建索引，加速排序和关联操作。
• 给Table2的Date From、Date To字段创建复合索引，提升日期范围匹配的效率。
• 若数据量极大，可考虑分批处理，但上述集合式方法已远优于游标方案。

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