1:多表连接产生重复值时使用distinct/distinct on的合理性及最佳实践咨询

核心问题定位 #

该统计值放大问题本质是不同独立维度的表直接JOIN产生笛卡尔积：

• 就诊、OR时长数据的聚合维度是「医生+单条就诊记录」
• 手术块时长数据的聚合维度是「医生+单条手术块记录」
  两个维度没有直接关联字段，直接JOIN会导致每条就诊记录匹配该医生名下所有符合条件的手术块记录，所有sum/count结果都会被放大为原数值的N倍（N为该医生符合条件的手术块总数）。

标准最佳实践 #

这类场景的标准处理方案是先将不同维度的指标分别预聚合到相同的统计粒度，再做关联，distinct确实属于临时补丁，只会掩盖JOIN逻辑的底层问题，无法保证所有统计场景下的结果准确性。

改造后查询代码 #

WITH -- 用CTE做预聚合，逻辑更清晰
-- 预聚合1：先单独算每个医生的9月OR总手术块时长，粒度到单个医生
phys_block_agg AS (
    SELECT
        pt1.phys1_num,
        SUM(elt2.esla1_bt_end[1] - elt2.esla1_bt_beg[1]) AS total_block_hours
    FROM physician_table1 pt1
    INNER JOIN ews_location_table2 elt2
        ON LPAD(pt1.phys1_num::VARCHAR, 6, '0') = ANY(elt2.esla1_bt_surg)
        AND elt2.esla1_loca IN ('OR1','OR2','OR3','OR4')
        AND elt2.esla1_date BETWEEN '2021-09-01' AND '2021-09-30'
    GROUP BY pt1.phys1_num
),
-- 预聚合2：单独算每个医生的就诊总数、OR使用总时长，粒度到单个医生
phys_or_visit_agg AS (
    SELECT
        pt1.phys1_num,
        pt1.phys1_name AS surgeon,
        COUNT(DISTINCT v.visit_id) AS total_visits, -- 加distinct避免同一就诊匹配多医生关系的重复
        SUM(mad2.nsma1_ans::TIME - mad.nsma1_ans::TIME) AS or_hours_utilized
    FROM visit v
    INNER JOIN pat_phy_relation_table pprt 
        ON pprt.patphys_pat_num = v.visit_id
    INNER JOIN physician_table1 pt1
        ON pt1.phys1_num = pprt.patphys_phy_num
    INNER JOIN multi_app_documentation mad2 
        ON mad2.nsma1_patnum = v.visit_id
        AND mad2.nsma1_code = 'OROUT'
    INNER JOIN multi_app_documentation mad 
        ON mad.nsma1_patnum = v.visit_id
        AND mad.nsma1_code = 'ORINTIME'
    WHERE v.visit_admit_date = '2021-09-01'
    GROUP BY pt1.phys1_num, pt1.phys1_name
)
-- 最后把两个预聚合结果按医生关联，不会产生重复行
SELECT
    pova.total_visits,
    pova.or_hours_utilized,
    pba.total_block_hours,
    pova.surgeon
FROM phys_or_visit_agg pova
INNER JOIN phys_block_agg pba
    ON pova.phys1_num = pba.phys1_num;

改造说明 #

• 两个CTE分别把两个独立维度的指标聚合到「单个医生」的相同粒度，每个CTE里单个医生只有一行数据
• 最终关联时不会产生笛卡尔积，统计结果不会被放大
• 如果需要保留没有手术块/没有就诊记录的医生，把最终的INNER JOIN改成对应的LEFT JOIN即可

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