本文介绍了在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')我想从项目和pending,in_progress和complete任务的计数中选择名称和ID.
I want to select name and id from projects and counts of the pending, in_progress and complete tasks.
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到目前为止,我只是在做三个笨拙的子查询:
What I have so far is just doing three ungainly subqueries:
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是否有一种更优雅/更高效的解决方案来选择骨料?
Is there a more elegant/performant solution to select the aggregates?
推荐答案您可以使用条件聚合来透视数据.在Postgres中,聚合函数的filter功能对此很方便:
You can pivot your data with conditional aggregation. In Postgres, the filter functionality of aggregate functions comes handy for this:
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更多推荐
在Postgres上选择一个联接表的多个聚合
发布评论