按一定数量的列分组，但在select子句中排除它们

Question

我想创建一些汇总统计信息，并通过Postgresql在我的数据中实现控制结构。为此，我使用group by。问题是我不想在"Select“子句中显示列，但group by子句强制我也使用该列

我已经尝试使用下面的代码，但不幸的是，我不希望按“Valuenum”进行分组。但是，我想使用该列创建一个新列(within_range)，如下面的查询所示。我的实际数据如下所示。请注意，这只是一个示例。对于不同hadm_ids，subject_ids可能会重复

​

​

select subject_id,hadm_id,count(*) as "Total no of records",
         case 
             when valuenum between 80 and 110 then 1
             else 0
             end as "within_range"
         from labevents where itemid in ('50809','50931','51529') and 
hadm_id is not null 
         group by subject_id,hadm_id,within_range
         order by subject_id,hadm_id

我希望输出结果类似于每个对象，按照它们的hadm_id和每个患者及其各自hadm_ids下的记录计数(记录总数，范围内)进行分组

​

​

Answer 1

不能在group by中使用别名，必须直接在group by中使用该别名，或者必须使用子查询或cte

select subject_id,hadm_id,count(*) as "Total no of records",
         case 
             when valuenum between 80 and 110 then 1
             else 0
             end as "within_range"
         from labevents where itemid in ('50809','50931','51529') and 
hadm_id is not null 
         group by subject_id,hadm_id,case 
             when valuenum between 80 and 110 then 1
             else 0
             end
         order by subject_id,hadm_id

以下是您的代码的cte版本

with cte as
(
select subject_id,hadm_id,case 
                 when valuenum between 80 and 110 then 1
                 else 0
                 end as "within_range"
             from labevents where itemid in ('50809','50931','51529') and 
    hadm_id is not null 


) select subject_id,hadm_id,within_range,count(*) as cnt
  from cte group by subject_id,hadm_id,within_range