如何在SAM数据中匹配合同生效日对应的最早有效注册行

我需要处理CONTRACT数据（生效日期）和SAM数据（实体注册信息），目标是找到合同生效时处于活跃状态的对应SAM注册数据行，具体规则可参考以下示例：

SAM数据示例 #

匹配规则示例 #

• 合同生效日为01-FEB-2024，返回第7行
• 合同生效日为15-JUL-2024，返回第6行
• 合同生效日为01-AUG-2024，返回第3行

当前SQL问题 #

我目前使用的SQL如下，但在CTE中包含多个合同生效日时会返回多余行，无法精准匹配每个生效日对应的目标行：

WITH CONTRACTS AS ( SELECT 'P2LYU1JBH7U9' AS SAM
                           ,TO_DATE('01-FEB-2024') AS CONTRACTSTARTDATE1
                           ,TO_DATE('15-JUL-2024') AS CONTRACTSTARTDATE2
                           ,TO_DATE('01-AUG-2024') AS CONTRACTSTARTDATE3
                      FROM DUAL)
    SELECT S.UEISAM
         , TRUNC(S.INITIAL_REGISTRATION_DATE)      AS INITIAL_REGISTRATION_DATE
         , TRUNC(S.ACTIVATION_DATE)                AS ACTIVATION_DATE
         , TRUNC(S.REGISTRATION_EXPIRATION_DATE)   AS REGISTRATION_EXPIRATION_DATE
         , s.*
      FROM OC_ARB_SAM_SUP_EXTRACTS S
 LEFT JOIN CONTRACTS C ON C.SAM = S.UEISAM
     WHERE UEISAM = 'P2LYU1JBH7U9'
--       AND (CONTRACTSTARTDATE3 between S.ACTIVATION_DATE AND S.REGISTRATION_EXPIRATION_DATE)
  ORDER BY S.ACTIVATION_DATE DESC, S.REGISTRATION_EXPIRATION_DATE

核心需求 #

当一个实体（UEISAM）有多条SAM数据时，需找到合同生效日处于其活跃区间（ACTIVATION_DATE ≤ 生效日 ≤ REG_EXPIRATION_DATE）内的、激活日期最晚的行（与示例规则一致）。

解决方案SQL #

WITH CONTRACTS AS (
    -- 将多列合同生效日转换为行式数据，确保每个生效日单独匹配
    SELECT 'P2LYU1JBH7U9' AS UEISAM, TO_DATE('01-FEB-2024') AS CONTRACT_STARTDATE FROM DUAL
    UNION ALL
    SELECT 'P2LYU1JBH7U9' AS UEISAM, TO_DATE('15-JUL-2024') AS CONTRACT_STARTDATE FROM DUAL
    UNION ALL
    SELECT 'P2LYU1JBH7U9' AS UEISAM, TO_DATE('01-AUG-2024') AS CONTRACT_STARTDATE FROM DUAL
),
RANKED_SAM AS (
    SELECT
        C.UEISAM,
        C.CONTRACT_STARTDATE,
        S.INITIAL_REG_DATE,
        TRUNC(S.ACTIVATION_DATE) AS ACTIVATION_DATE,
        TRUNC(S.REG_EXPIRATION_DATE) AS REG_EXPIRATION_DATE,
        -- 按实体+生效日分组，对符合条件的SAM行按激活日期降序排序，取第一行即为目标行
        ROW_NUMBER() OVER (
            PARTITION BY C.UEISAM, C.CONTRACT_STARTDATE
            ORDER BY S.ACTIVATION_DATE DESC
        ) AS rn
    FROM CONTRACTS C
    JOIN OC_ARB_SAM_SUP_EXTRACTS S
        ON C.UEISAM = S.UEISAM
        -- 过滤出合同生效日处于SAM活跃区间内的行
        AND C.CONTRACT_STARTDATE BETWEEN S.ACTIVATION_DATE AND S.REG_EXPIRATION_DATE
)
SELECT
    UEISAM,
    CONTRACT_STARTDATE,
    INITIAL_REG_DATE,
    ACTIVATION_DATE,
    REG_EXPIRATION_DATE
FROM RANKED_SAM
WHERE rn = 1;

逻辑说明 #

1. 行式合同数据：把CTE中多列的生效日期转为独立行，确保每个生效日能单独关联SAM数据。
2. 窗口函数排序：用ROW_NUMBER()按实体和生效日分组，对符合活跃区间的SAM行按激活日期降序排序，排序后的第一行就是最接近生效日的活跃SAM行。
3. 筛选目标行：最后只保留排序为1的行，即可得到每个合同生效日对应的精准SAM数据。

内容的提问来源于stack exchange，提问作者W.A. Russell