postgreRaw SQL查询看起来很奇怪，正在寻找更好的方法

Question

我很少有机会写RAW SQL查询，我写了这个查询，但觉得有些地方不对。我想知道是否有更好的方法来写它。也许我会学到一些新东西。

select a.approved, c.review, d.pending, f.total, f.total - a.approved - c.review as ongoing
from
(select count(distinct sownumber) as approved from v_scope_of_work_combined where name = 'CTN-Approve' and assigned_engineer_my_tasks = 'NotClaimed' and sow_status = 'PM Approved'  and implementer = 'C1') as a,
(select count(distinct sownumber) as review from v_scope_of_work_combined where name = 'CTN-Approve' and assigned_engineer_my_tasks != 'NotClaimed'and sow_status = 'PM Approved'  and implementer = 'C1') as c,
(select count(distinct sownumber) as pending from v_scope_of_work_combined where name = 'CTN-Approve' and v_scope_of_work_combined.design_readiness = 'No'and sow_status = 'PM Approved'  and implementer = 'C1') as d,
(select count(distinct sownumber) as total from v_scope_of_work_combined where  sow_status = 'PM Approved'  and implementer = 'C1') as f;

Answer 1

您可以通过使用过滤聚合来改进这一点：

select approved, review, pending, total, total - approved - review as ongoing
from (
select count(distinct sownumber) filter (where name = 'CTN-Approve' and assigned_engineer_my_tasks = 'NotClaimed') as approved,
count(distinct sownumber) filter (where name = 'CTN-Approve' and assigned_engineer_my_tasks != 'NotClaimed') as review,
count(distinct sownumber) filter (where name = 'CTN-Approve' and design_readiness = 'No' )  as pending,
count(distinct sownumber) as total 
from v_scope_of_work_combined 
where implementer = 'C1'
and sow_status = 'PM Approved'
) t

Answer 2

您可以按如下方式编写条件聚合：

select approved, review, pending, total, total - approved - review as ongoing
from (
select count(distinct sownumber) filter (where name = 'CTN-Approve' and assigned_engineer_my_tasks = 'NotClaimed' and implementer = 'C1' and sow_status = 'PM Approved') as approved,
count(distinct sownumber) filter (where name = 'CTN-Approve' and assigned_engineer_my_tasks != 'NotClaimed' and implementer = 'C1' and sow_status = 'PM Approved') as review,
count(distinct sownumber) filter (where name = 'CTN-Approve' and design_readiness = 'No' and implementer = 'C1' and sow_status = 'PM Approved')  as pending,
count(distinct sownumber) filter (where implementer = 'C1' and sow_status = 'PM Approved') as total 
from v_scope_of_work_combined t) t

Answer 3

我认为编写这种没有几个子查询的查询更可读。

SELECT 
COUNT(DISTINCT CASE WHEN name = 'CTN-Approve' and assigned_engineer_my_tasks = 'NotClaimed' and sow_status = 'PM Approved'  and implementer = 'C1' THEN sownumber ELSE NULL END) 
AS approved,

COUNT(DISTINCT CASE WHEN name = 'CTN-Approve' and assigned_engineer_my_tasks != 'NotClaimed'and sow_status = 'PM Approved'  and implementer = 'C1' THEN sownumber ELSE NULL END) 
AS review,

COUNT(DISTINCT CASE WHEN name = 'CTN-Approve' and v_scope_of_work_combined.design_readiness = 'No'and sow_status = 'PM Approved'  and implementer = 'C1' THEN sownumber ELSE NULL END) 
AS pending,

COUNT(DISTINCT CASE WHEN sow_status = 'PM Approved'  and implementer = 'C1' THEN sownumber ELSE NULL END) 
AS total,

COUNT(DISTINCT CASE WHEN sow_status = 'PM Approved'  and implementer = 'C1' THEN sownumber ELSE NULL END) AS pending
- COUNT(DISTINCT CASE WHEN name = 'CTN-Approve' and assigned_engineer_my_tasks = 'NotClaimed' and sow_status = 'PM Approved'  and implementer = 'C1' THEN sownumber ELSE NULL END)
- COUNT(DISTINCT CASE WHEN name = 'CTN-Approve' and assigned_engineer_my_tasks != 'NotClaimed'and sow_status = 'PM Approved'  and implementer = 'C1' THEN sownumber ELSE NULL END)
AS ongoing
FROM
v_scope_of_work_combined