时间戳

首页 > 解决方案 > 选择不同的值必须出现在分组依据

postgresql - 选择不同的值必须出现在分组依据

问题描述

尝试运行以下查询时遇到错误：“列“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

标签： postgresql

解决方案

问题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但是，在分组依据的东西上使用是没有意义的。group 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

推荐阅读