时间戳

首页 > 解决方案 > 在 Postgres 上选择连接表的多个聚合

sql - 在 Postgres 上选择连接表的多个聚合

问题描述

鉴于表格projects：

 id         | bigint                         | not null default nextval('projects_id_seq'::regclass)
 name       | character varying              | 
 created_at | timestamp(6) without time zone | not null
 updated_at | timestamp(6) without time zone | not null

和tasks：

 id         | bigint                         | not null default nextval('tasks_id_seq'::regclass)
 name       | character varying              | 
 project_id | bigint                         | not null
 created_at | timestamp(6) without time zone | not null
 updated_at | timestamp(6) without time zone | not null
 status     | task_status                    |

task_status是一个枚举：

CREATE TYPE task_status AS ENUM ('pending', 'in_progress', 'complete')

我想从项目和任务的计数中选择名称和pendingID 。in_progresscomplete

 id |              name              | pending_tasks_count | in_progress_tasks_count | complete_tasks_count 
----+--------------------------------+---------------------+-------------------------+----------------------
  2 | Dickens, Walker and Rutherford |                   1 |                       8 |                    5
  5 | Bailey-Kreiger                 |                   0 |                       0 |                    4
  4 | Ledner, Ullrich and Davis      |                   2 |                       1 |                    2
  1 | Price-Fisher                   |                   3 |                       4 |                    1
  3 | Harber LLC                     |                   1 |                       2 |                    1

到目前为止，我只是做了三个笨拙的子查询：

SELECT projects.id, projects.name, 
  (SELECT COUNT(tasks.*) FROM tasks WHERE tasks.project_id = projects.id 
    AND tasks.status = 'pending') AS pending_tasks_count, 
  (SELECT COUNT(tasks.*) FROM tasks WHERE tasks.project_id = projects.id 
    AND tasks.status = 'in_progress') AS in_progress_tasks_count, 
  (SELECT COUNT(tasks.*) FROM tasks WHERE tasks.project_id = projects.id 
    AND tasks.status = 'complete') AS complete_tasks_count 
FROM projects
LEFT OUTER JOIN tasks
  ON tasks.project_id = projects.id GROUP BY projects.id

是否有更优雅/高性能的解决方案来选择聚合？

标签： sqlpostgresqlgroup-bypivot

解决方案

您可以使用条件聚合来透视数据。在 Postgres 中，filter聚合函数的功能很方便：

select 
    p.id,
    p.name,
    count(*) filter(where t.status = 'pending') pending_tasks_count,
    count(*) filter(where t.status = 'in_progress') in_progress_tasks_count,
    count(*) filter(where t.status = 'complete') complete_tasks_count
from projects p
inner join tasks t on t.project_id = p.id
group by p.id, p.name

推荐阅读