如何解决分组和聚合函数在PostgreSQL中的问题 [英] how to solve the issue with group by and aggregate function in postgresql

问题描述

我正在尝试编写一个查询来划分两个SQL语句，但是它向我显示

I am trying to write a query for dividing the two SQL statements but it shows me

 ERROR: column "temp.missed" must appear in the GROUP BY clause or be used in 
 an aggregate function
  SQL state: 42803

虽然当我按温度分组时未选中时，它似乎正在工作，但是由于多个分组而显示了错误的结果。

although when I do group by temp.missed, it seems to be working but shows wrong results because of multiple group by.

我在PostgreSQL中有以下表

I have following tables in PostgreSQL

 create table test.appointments (
      appointment_id serial
      , patient_id integer references test.patients(id)
      , doctor_id integer references test.doctors(id)
      , appointment_time timestamp
      , appointment_status enum('scheduled','completed','missed')
      );

 create table test.visits (
      visit_id serial
      , patient_id integer references test.patients(id)
      , doctor_id integer references test.doctors(id)
      , walk_in_visit boolean
      , arrival_time timestamp
      , seen_time timestamp
      );

我写了以下查询来查找错过的约会率（错过的约会/总约会），但显示

I have written following query for finding Missed appointment rate (missed appointments / total appointments) but its shows the above mentioned error.

  select tem.doctor_id, (temp.missed::float/tem.total) as ratio from 
    (select doctor_id, count(appointment_status) as missed from appointments 
     where appointment_status='missed' group by doctor_id)as temp
      join (select doctor_id, count(appointment_status) as total from 
      appointments group by doctor_id) as tem  on temp.doctor_id = 
       tem.doctor_id group by tem.doctor_id;

推荐答案

您可以简化很多查询，如果您使用PostgreSQL 9.4，您可以使用一种非常清晰的语法：

You can simplify the query a lot and if you are using PostgreSQL 9.4 you can use a very nice and clear syntax:

SELECT   doctor_id, 
     COUNT(appointment_status)::float
       FILTER (WHERE appointment_status='missed') / 
     COUNT(*) AS ratio
FROM     appointments
GROUP BY doctor_id

过滤器仅影响 COUNT 是在此之后，因此在这种情况下，它只会计算错过的约会。

The FILTER will affect only the COUNT it is after, so in this case it will only count missed appointments.

这篇关于如何解决分组和聚合函数在PostgreSQL中的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！