如何基于Study表匹配与非匹配规则提取Obs表指定记录

数据表结构 #

Study表 #

id      startedDate             practitioner        reference       laterality
-------------------------------------------------------------------------------
1s      2022-09-01 00:00:00     p1                  1-reference      R
2s      2022-10-01 00:00:00     p1                  1-reference      L
3s      2022-09-03 00:00:00     null                2-reference      R
4s      2022-09-03 00:00:00     null                3-reference      R
5s      2022-10-03 00:00:00     p1                  4-reference      L

Obs表 #

id          reference       laterality          effectiveDate         length
-------------------------------------------------------------------------------
1o         1-reference         R                2022-08-30 00:00:00    2.1
2o         1-reference         R                2022-08-01 00:00:00    2.2
3o         1-reference         R                2022-09-03 00:00:00    2.1
4o         1-reference         L                2022-08-03 00:00:00    2.0
5o         2-reference         R                2022-08-03 00:00:00    2.0
6o         2-reference         R                2022-08-01 00:00:00    2.0
7o         3-reference         R                2022-10-01 00:00:00    2.0
8o         5-reference         L                2022-10-02 00:00:00    1.9
9o         5-reference         L                2022-10-03 00:00:00    2.0

输出要求 #

• 当Study表中practitioner != null时，按reference和laterality分组，提取Obs表中effectiveDate < study.startedDate的最大effectiveDate记录；
• 当Obs表的记录未出现在Study表（且Study表中practitioner != null的reference和laterality组合）时，按reference和laterality分组提取最新effectiveDate记录。

预期结果 #

id         reference          laterality        effectiveDate          length
1o         1-reference         R                2022-08-30 00:00:00    2.1
4o         1-reference         L                2022-08-03 00:00:00    2.0
5o         2-reference         R                2022-08-03 00:00:00    2.0
7o         3-reference         R                2022-10-01 00:00:00    2.0
9o         5-reference         L                2022-10-03 00:00:00    2.0

解决方案 #

实现思路 #

1. 先提取Study表中practitioner IS NOT NULL的记录，作为有效匹配基准组；
2. 对Obs表记录分两类处理：
   • 属于基准组的，筛选出effectiveDate小于对应Study记录startedDate的最大日期记录；
   • 不属于基准组的，直接取每组reference+laterality的最新日期记录；
3. 合并两类结果，得到最终符合要求的数据集。

SQL查询语句 #

WITH valid_study AS (
    -- 筛选Study表中practitioner不为空的有效匹配组
    SELECT reference, laterality, startedDate
    FROM Study
    WHERE practitioner IS NOT NULL
),
obs_processed AS (
    SELECT 
        o.*,
        -- 标记当前Obs记录是否属于有效Study匹配组
        CASE WHEN vs.reference IS NOT NULL THEN 1 ELSE 0 END AS in_valid_group,
        -- 按规则计算排名：有效组内仅保留日期小于startedDate的记录参与排序，无效组直接按日期倒序
        ROW_NUMBER() OVER (
            PARTITION BY o.reference, o.laterality
            ORDER BY 
                CASE 
                    WHEN vs.reference IS NOT NULL THEN 
                        CASE WHEN o.effectiveDate < vs.startedDate THEN o.effectiveDate ELSE '1970-01-01' END
                    ELSE o.effectiveDate 
                END DESC
        ) AS row_rank
    FROM Obs o
    LEFT JOIN valid_study vs 
        ON o.reference = vs.reference AND o.laterality = vs.laterality
)
SELECT id, reference, laterality, effectiveDate, length
FROM obs_processed
WHERE row_rank = 1
-- 过滤有效组中不符合日期条件的无效记录
AND (in_valid_group = 0 OR effectiveDate < (SELECT startedDate FROM valid_study WHERE reference = obs_processed.reference AND laterality = obs_processed.laterality))
ORDER BY reference, laterality;

语句说明 #

• valid_study：提取Study表中符合条件的基准记录，用于后续匹配判断；
• obs_processed：对Obs表每条记录标记所属组类型，并按规则计算排名，确保有效组内仅符合日期条件的记录能排到第一；
• 最终查询筛选排名第一的记录，并过滤有效组中不符合日期要求的无效数据，得到预期结果。

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