按project_type和使用string_agg的状态连接项目列表

Question

我使用postgresql 11

我有一个表名为project_type包含这个值:

id    lbl
1     Research
2     Business
3     Maintenance

和我有一个名为:technical_project的表它们有这些数据:

id  name  status      project_type
1   test1 open        1
2   test2 open        1
3   test3 inProgress  2
4   test4 inProgress  1
5   test5 closed      2
6   test6 inProgress  2

我想在一个查询中:concat所有项目类型的列表，并为每个状态concat按项目类型的technical_projec的计数。

这是我想要的查询的输出:

[Research,Business,Maintenance], open  ,[2,0,0] ,inProgress  ,[1,2,0] ,closed  ,[0,1,0] 

或者像这样:

[Research,Business,Maintenance] ,[2,0,0]  ,[1,2,0]  ,[0,1,0] 

表示开放状态它有2个项目类型为研究，0个项目类型为商业，0个项目类型为维护

状态open, inProgress和closed是固定的，而project_type是动态的，为什么要从表project_type中读取。

我认为我应该使用string_agg

有人能帮我找到正确的查询吗

Answer 1

更新14.04.2022

在我看来这是一个更有效的解决方案。我将留下一个链接到旧版本(那里有很多子查询)。主要思想是通过status, i.id分组来做merge，然后计算not zero的数量。

SELECT COUNT(status), status, i.id
FROM project_type AS i
LEFT OUTER JOIN technical_project AS c
ON i.id = c.project_type
WHERE  status != ''
GROUP BY status, i.id
;

给了什么:

count |   status   | id
-------+------------+----
2 | inProgress |  2
1 | inProgress |  1
1 | closed     |  2
2 | open       |  1

在WITH vars_null中，我创建了一个由id和status填充的虚拟表。我通过i.id = q.id和i.status = q.status将vars_null与subquery的结果连接起来。通过COALESCE，我将0设置为q.count，其中没有值。

和使用array_agg我创建数组通过删除array_remove NULL值。连接:||','||

所有代码:

SELECT 
(
SELECT array_agg(lbl)::text FROM project_type)||','||
array_remove(array_agg(CASE WHEN r.status = 'open' THEN r.cou END), NULL)::text||','||
array_remove(array_agg(CASE WHEN r.status = 'inProgress' THEN r.cou END), NULL)::text||','||
array_remove(array_agg(CASE WHEN r.status = 'closed' THEN r.cou END), NULL)::text AS row
FROM
(
WITH vars_null as (
(select 'open' as status, * from generate_series(1, 3) as id)
UNION ALL
(select 'inProgress' as status, * from generate_series(1, 3) as id)
UNION ALL
(select 'closed' as status, * from generate_series(1, 3) as id)
)
SELECT i.id, COALESCE(q.count,0) AS cou, i.status
FROM vars_null AS i
LEFT OUTER JOIN
( 
SELECT COUNT(status), status, i.id
FROM project_type AS i
LEFT OUTER JOIN technical_project AS c
ON i.id = c.project_type
WHERE  status != ''
GROUP BY status, i.id
)
AS q
ON i.id = q.id and i.status = q.status
ORDER BY i.status, i.id
) r
;

这是旧的代码，其中有很多子查询。