postgresql - 我应该使用 distinct / distinct on 删除由 1:many 连接引起的重复项吗？

Question

随着我继续尝试不同的解决方案，对我之前的问题进行了修订。select distinct on让我非常接近我的预期输出，但如果不添加到 GROUP BY 语句，我就不能让它完全工作。我现在想知道我是否应该专注于select distinct on工作而不是尝试改进原始表连接。我读过的其他问题表明这更像是一种“创可贴”的方法。是否有我应该遵循的最佳实践？下面的原始问题：

我很难找到加入 1:many 表而不增加重复输出的最佳方法。我尝试过使用select distinct on v.visit.idwhich 让我接近，但前提是我更改了 GROUP BY 语句，这会弄乱我想要的输出。我的最终目标是从下表中计算患者/就诊在 OR 中的时间以及外科医生为他们的阻滞安排的时间：

表1（访问）

访问ID
123
321

表 2 (pat_phy_relation_table)

pathphys_pat_num	pathphys_rel_type	pathphys_phy_num
123	出席	1306
321	出席	1306

表 3 (physician_table1)

phys1_num	phys1_name
1306	X博士

表 4 (multi_app_documentation) (OR 次)

nsma1_patnum	nsma1_code	nsma1_ans
123	奥林时报	1037
123	奥特	1352
321	奥林时报	0723
321	奥特	0952

表 5 (ews_location_table2) (块时间)

esla1_loca	esla1_date	esla1_bt_beg	esla1_bt_end	esla1_bt_surg
OR3	2021-09-02	{'07:00:00',,,,,,,,,}	{'17:00:00',,,,,,,,,}	{001306,,,,,,,,,}
OR3	2021-09-16	{'07:00:00',,,,,,,,,}	{'17:00:00',,,,,,,,,}	{001306,,,,,,,,,}
OR3	2021-09-30	{'07:00:00',,,,,,,,,}	{'17:00:00',,,,,,,,,}	{001306,,,,,,,,,}

预期成绩

总访问次数	or_hours_utilized	total_block_hours	外科医生
2	9:31:00	30:00:00	X博士

实际结果

总访问次数	or_hours_utilized	total_block_hours	外科医生
6	28:33:00	60:00:00	X博士

我的假设是，由于我对表 5 使用了内部联接，因此我的结果与返回的行数重复。但是，我不知道加入此表的另一种方法，因为我所有的其他联接都是 1:1。这是唯一的 1:many 关系。我似乎想不出一个解决方案，因为表 5 没有与访问表相关的列。

我目前正在研究子查询，但我对它们不够熟悉，不知道我是否可以一次处理表 5 的计算并将结果传回主查询。

我试图删除与问题无关的信息，但如果我能减少其他任何信息，请告诉我。如下查询：

select 
count(v.visit_id) as total_visits,
sum(mad2.nsma1_ans::time - mad.nsma1_ans::time) as or_hours_utilized,
sum(esla1_bt_end[1] - esla1_bt_beg[1]) as total_block_hours,
pt1.phys1_name as surgeon
from visit as v
inner join pat_phy_relation_table as pprt 
    on pprt.patphys_pat_num = v.visit_id
inner join physician_table1 as pt1
    on pt1.phys1_num = pprt.patphys_phy_num
inner join ews_location_table2 elt2
    on lpad(pt1.phys1_num::varchar, 6, '0') = any (elt2.esla1_bt_surg)
    and esla1_loca in ('OR1','OR2','OR3','OR4')
    and esla1_date between '2021-09-01' and '2021-09-30'
inner join multi_app_documentation mad2 
    on mad2.nsma1_patnum = v.visit_id
    and mad2.nsma1_code = 'OROUT' --only pulling visits/physicians with an OROUT    
inner join multi_app_documentation mad 
    on mad.nsma1_patnum = v.visit_id
    and mad.nsma1_code = 'ORINTIME' --only pulling visits/physicians with an ORINTIME
where v.visit_admit_date = '2021-09-01'
group by pt1.phys1_name

标签： postgresql

Answer 1

我不知道加入此表的另一种方法，因为我所有的其他联接都是 1:1。这是唯一的 1:many 关系

不完全是，您pat_phy_relation_table和您ews_location_table2都与外科医生有 1:many 的关系（physician_table1）。所以实际上它是患者和或块之间的多：多关系 - 但这不是你想要的，你不能在那里使用 JOIN 。相反，为每个外科医生做两个独立的LATERAL子查询：

select
  a.total_visits,
  a.or_hours_utilized,
  pt1.phys1_name as surgeon,
  b.total_block_hours
from
  physician_table1 as pt1,
  lateral (
    select
      count(v.visit_id) as total_visits,
      sum(mad2.nsma1_ans::time - mad.nsma1_ans::time) as or_hours_utilized,
    from visit as v
    inner join pat_phy_relation_table as pprt
      on pprt.patphys_pat_num = v.visit_id
    inner join multi_app_documentation mad2
      on mad2.nsma1_patnum = v.visit_id
      and mad2.nsma1_code = 'OROUT' -- only pulling visits/physicians with an OROUT    
    inner join multi_app_documentation mad 
      on mad.nsma1_patnum = v.visit_id
      and mad.nsma1_code = 'ORINTIME' -- only pulling visits/physicians with an ORINTIME
    where
      pt1.phys1_num = pprt.patphys_phy_num -- joining against the particular physician
      and v.visit_admit_date = '2021-09-01'
  ) as a,
  lateral (
    select
      sum(esla1_bt_end[1] - esla1_bt_beg[1]) as total_block_hours,
    from
      ews_location_table2 elt2
    where
      lpad(pt1.phys1_num::varchar, 6, '0') = any (elt2.esla1_bt_surg) -- joining against the particular physician
      and esla1_loca in ('OR1','OR2','OR3','OR4')
      and esla1_date between '2021-09-01' and '2021-09-30'
  ) as b;