如何优化查询以高效计算医疗记录中多维度平均就诊间隔天数

问题背景 #

这是医疗记录领域的处理需求，目标是按患者、医疗单元、年度三个维度，计算两次就诊之间间隔的平均天数。处理大规模医疗记录时存在性能瓶颈：对于患者数不足50人、就诊记录少于200条的小型医疗单元，单医疗单元、单年度的HQL查询可正常运行且速度相对可观，但就诊量更大时会出现「组合爆炸」问题，给数据库带来极大负载。当前需要一次性完成80个医疗单元最长10年的数据统计分析。

原问题查询代码 #

SELECT
HB3 patient.pati_nip AS NIPP,
UPPER(cufm.cufm_libelle) AS CAT_UFM,
grp.unfo_libelle AS SECTEUR_DISP,
uf_ex.codeLibelle AS UNITE,
COUNT(DISTINCT raa.id) AS RAA,
COUNT(DISTINCT patient.id) AS PATIENTS,
ROUND(AVG(raa2.traa_date-raa.traa_date),1) AS DELAIMOY_J_INTER_RAA

FROM
Ide_patient AS patient
JOIN patient.pms_edgars AS redg
JOIN redg.bas_uf AS uf_ex
JOIN redg.pms_edgar_actes AS acte
JOIN acte.bas_catalogue_gen_by_Edgr_id_cage_nature AS type
JOIN acte.pms_raas as raa
JOIN patient.pms_edgars AS redg2
JOIN redg2.bas_uf AS uf_ex2
JOIN redg2.pms_edgar_actes AS acte2
JOIN acte2.bas_catalogue_gen_by_Edgr_id_cage_nature AS type2
JOIN acte2.pms_raas as raa2
JOIN uf_ex.bas_etablissement AS etab
JOIN uf_ex.bas_uf_by_Unfo_id_unfo_grp as grp
JOIN uf_ex.bas_categorie_ufm AS cufm

WHERE
etab.id = <ETAB>
AND raa.traa_date BETWEEN INVITE(D: Actes exportés effectués entre le ) AND INVITE(D: et le )
AND type.cage_code NOT LIKE 'R%'
AND uf_ex.id = INVITE(B:UF_MED_FILT_VAL: File active+nouveaux patients pour cette UF exécutante)
AND raa.traa_dat_export IS NOT NULL
AND raa2.traa_date = (SELECT MIN(raa3.traa_date)
    FROM patient.pms_edgars AS redg3
    JOIN redg3.bas_uf AS uf_ex3
    JOIN redg3.pms_edgar_actes AS acte3
    JOIN acte3.bas_catalogue_gen_by_Edgr_id_cage_nature AS type3
    JOIN acte3.pms_raas as raa3
    WHERE raa3.traa_dat_export IS NOT NULL
    AND raa3.traa_date > raa.traa_date
    AND uf_ex3.id = uf_ex
    AND type3.cage_code NOT LIKE 'R%')

ORDER BY
patient.pati_nip, UPPER(cufm.cufm_libelle), grp.unfo_libelle, uf_ex.codeLibelle

无间隔计算的最小查询版本 #

SELECT
HB3 patient.id AS PATI_ID,
uf_ex.codeLibelle AS UNITE,
raa.traa_date AS DATE_CONSULT_DATE

FROM
Ide_patient AS patient
JOIN patient.pms_edgars AS redg
JOIN redg.bas_uf AS uf_ex
JOIN redg.pms_edgar_actes AS acte
JOIN acte.bas_catalogue_gen_by_Edgr_id_cage_nature AS type
JOIN acte.pms_raas as raa
JOIN uf_ex.bas_etablissement AS etab

WHERE
etab.id = <ETAB>
AND raa.traa_date BETWEEN INVITE(D: consultations between ) AND INVITE(D: and )
AND type.cage_code NOT LIKE 'R%'
AND uf_ex.id = INVITE(B:UF_MED_FILT_VAL: consultations done in this care-unit)
AND raa.traa_dat_export IS NOT NULL

GROUP BY uf_ex.codeLibelle, patient.id, raa.traa_date

业务规则说明 #

• type.cage_code的首字母代表就诊类型，可选值为('E','D','G','A','R')，其中R类型代表无患者到场的医疗团队内部会议，需要排除
• 核心目标为计算同一患者在指定时间区间内所有非R类型的连续相邻就诊的间隔差值，raa.traa_date的日期格式包含时、分、秒
• uf_ex.id为本次就诊对应的医疗单元ID

优化方案 #

1. 替换高复杂度关联逻辑，用窗口函数计算相邻间隔 #

原查询性能瓶颈来自自关联+嵌套子查询的笛卡尔积计算，每一条就诊记录都需要全表扫描匹配后续最近的就诊，数据量越大复杂度越高。改用LEAD窗口函数直接取同一患者、同一医疗单元下的下一次就诊时间，计算复杂度从O(n²)降到O(n)，修改后查询逻辑如下：

WITH base_consult AS (
    -- 仅做一次多表关联拉取全量符合条件的基础就诊数据
    SELECT
        patient.pati_nip AS NIPP,
        patient.id AS PATI_ID,
        UPPER(cufm.cufm_libelle) AS CAT_UFM,
        grp.unfo_libelle AS SECTEUR_DISP,
        uf_ex.codeLibelle AS UNITE,
        raa.id AS RAA_ID,
        raa.traa_date AS CONSULT_DATE,
        -- 按患者、医疗单元分组，按就诊时间排序，直接取同组下一条就诊时间
        LEAD(raa.traa_date) OVER (PARTITION BY patient.id, uf_ex.id ORDER BY raa.traa_date) AS NEXT_CONSULT_DATE
    FROM
        Ide_patient AS patient
        JOIN patient.pms_edgars AS redg
        JOIN redg.bas_uf AS uf_ex
        JOIN redg.pms_edgar_actes AS acte
        JOIN acte.bas_catalogue_gen_by_Edgr_id_cage_nature AS type
        JOIN acte.pms_raas as raa
        JOIN uf_ex.bas_etablissement AS etab
        JOIN uf_ex.bas_uf_by_Unfo_id_unfo_grp as grp
        JOIN uf_ex.bas_categorie_ufm AS cufm
    WHERE
        etab.id = <ETAB>
        AND raa.traa_date BETWEEN :start_date AND :end_date
        AND type.cage_code NOT LIKE 'R%'
        AND raa.traa_dat_export IS NOT NULL
        -- 批量跑多医疗单元时直接删除下面的单UF过滤条件即可
        -- AND uf_ex.id = :target_uf_id
)
-- 聚合计算最终指标
SELECT
    NIPP,
    CAT_UFM,
    SECTEUR_DISP,
    UNITE,
    COUNT(DISTINCT RAA_ID) AS RAA,
    COUNT(DISTINCT PATI_ID) AS PATIENTS,
    ROUND(AVG(DATEDIFF(NEXT_CONSULT_DATE, CONSULT_DATE)),1) AS DELAIMOY_J_INTER_RAA
FROM base_consult
-- 排除每个患者的最后一次就诊（无后续就诊记录）
WHERE NEXT_CONSULT_DATE IS NOT NULL
GROUP BY NIPP, CAT_UFM, SECTEUR_DISP, UNITE
ORDER BY NIPP, CAT_UFM, SECTEUR_DISP, UNITE

2. 底层数据层优化 #

• 提前构建中间层：将患者、就诊记录、医疗单元的常用关联结果做成预计算中间表，避免每次查询重复关联多张大表
• 新增分区规则：按uf_ex.id（医疗单元）、year(traa_date)（就诊年度）做分区，批量跑数时可直接命中对应分区，无需扫描全量历史数据
• 加联合索引：给raa.traa_date、type.cage_code、raa.traa_dat_export、uf_ex.id建立联合索引，大幅加快过滤速度

3. 批量跑数策略优化 #

80个医疗单元10年的数据无需一次性计算完成，可按「医疗单元+年度」拆分多个小任务并行执行，每个任务仅处理单个医疗单元单年度的数据，既避免单个任务占用过高数据库资源，也方便出错后单独重跑对应分片。

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