PostgreOptimize查询与计算列

Question

我必须在postgresql中创建一个计算列的视图，但是我的查询需要很长时间才能运行。

我有一个名为surveys的表，其中包含每个调查的一个条目。

Sno=被叫号码

调查轮=调查进行了多个周期称为轮，在此基础上，我们需要分组和计算指标

call_status=调查呼叫状态

我想获得每轮的统计数据，比如有多少用户被调用，即唯一的sno，有多少调用被完成，每个不同的调用状态值计数

我当前的查询是=

select distinct  sc.survey_round, 
(select  count(distinct sno) FROM "Suvita".survey_call_details sc1 where sc.survey_round =sc1.survey_round  ) as "Total respondents call",
(select count(*) fROM "Suvita".survey_call_details sc2 where sc.survey_round =sc2.survey_round and sc2.iscomplete = 'Yes') as "Survey Completed",
(select count(*) FROM "Suvita".survey_call_details sc3 where sc.survey_round =sc3.survey_round)  as "Calls Attempted",
(select count(*) FROM "Suvita".survey_call_details sc4 where sc.survey_round =sc4.survey_round and sc4.call_status = 'Picked Up')  as "Calls Answered"  
FROM "Suvita".survey_call_details sc

请建议一个更好的方法在postgresql中编写这些查询。

编辑:我有答案了。根据Alexey的回复，我使用count和sum重新引用查询，它们工作得非常快!!

SELECT sc.survey_round, 
COUNT(distinct sc.sno) as "Total respondents call",
COUNT(*) as "Calls Attempted",
sum(CASE WHEN sc.call_status = 'Picked Up' THEN 1 ELSE 0 END) as "Calls Answered",
sum(CASE WHEN sc.iscomplete = 'Yes' THEN 1 ELSE 0 END) as "Survey Completed",    
sum(CASE WHEN sc.nominate = 'Yes' THEN 1 ELSE 0 END) as "Agreed to Nominate"
FROM "Suvita".survey_call_details sc
group by sc.survey_round  order by sc.survey_round desc

Answer 1

首先，您不需要子查询来计算一个表中的内容，只需使用CASE语句和COUNT中的条件。

第二，COUNT(DISTINCT sno)可以比COUNT(sno)慢。要获得不同的sno值，您可以使用JOIN LATERAL，这将用作子查询。

那么你的查询就变成了这个

SELECT 
sc.survey_round,
COUNT(sc1.sno) as "Total respondents call",
COUNT(CASE WHEN sc.iscomplete = 'Yes' THEN 1 ELSE 0 END) as "Survey Completed",
COUNT(*) as "Calls Attempted",
COUNT(CASE WHEN sc.call_status = 'Picked Up' THEN 1 ELSE 0 END) as "Calls Answered"
FROM "Suvita".survey_call_details sc
JOIN LATERAL (
SELECT DISTINCT sno 
FROM "Suvita".survey_call_details 
WHERE survey_round = sc.survey_round
) sc1 ON TRUE
GROUP BY sc.survey_round

最后，您应该在survey_round和sno列上有索引。对于实时数据，survey_round + sno上的复杂索引可能会表现得更好。