Select distinct on value必须出现在group by中

Question

在尝试运行以下查询时遇到错误:"column "v.v visit_id"必须出现在GROUP BY子句中，或者在聚合函数中使用。">

我的问题是，我相信我已经在第2行count(v.visit_id) as total_visits的聚合函数中使用了这列。这还不算满足误差吗?我不能直接添加到GROUP BY，因为那样会弄乱我的输出。

我的最终目标是选择不同的访问id，同时只按医生名称对输出进行分组。

select distinct on (v.visit_id)
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,
sum(extract(epoch from mad2.nsma1_ans::time) - extract(epoch from mad.nsma1_ans::time)) /
(sum(extract(epoch from esla1_bt_end[1])) - sum(extract(epoch from esla1_bt_beg[1]))) * 100 as or_percentage,
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
and pprt.patphys_rel_type = 'ATTENDING' 
inner join physician_table1 as pt1
on pt1.phys1_num = pprt.patphys_phy_num
and pt1.phys1_arid = v.visit_arid --need to confirm how to handle ARIDs
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'
and esla1_seid = pt1.phys1_arid
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    
and mad2.nsma1_ans !~ '[x,X,C,END,S]' --removing non-standard data
and mad2.nsma1_ans != '' and mad2.nsma1_ans != '0' and mad2.nsma1_ans != '1' and mad2.nsma1_ans != '0000'
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 between '2021-09-01' and '2021-09-30'
and v.visit_arid = 5
group by pt1.phys1_name

Answer 1

问题是distinct on (v.visit_id)不是一个聚合函数。你需要把它添加到group by。

select
distinct on (v.visit_id)
count(v.visit_id) as total_visits,
...
group by v.visit_id, pt1.phys1_name

然而，在分组时使用distinct是没有意义的。组by已经为每个visit_id只显示一行。

select
v.visit_id,
count(v.visit_id) as total_visits,
...
group by v.visit_id, pt1.phys1_name

如果v.visit_id是主键或唯一键，这也没有意义。每个visit_id将只出现一次，你的计数将永远是1。你可能想把它完全去掉。

select
count(v.visit_id) as total_visits
...
group by pt1.phys1_name