gnarbarian gnarbarian - 2 months ago 8
SQL Question

Is there a way to unify the subqueries in this with clause into a single query? Would it improve performance?

Schema info:

v_ProjectMilestone references project via project_id

milestones can be internal or external (milestone.is_external=1)

Milestones have three date columns that we care about ( Actual, Revised or proposed)

They want to be able to see the percentage of milestones that have a value for each of those dates and all of those dates divided up between internal and external milestones and the combination of both for each project.

with AllMilestones as(
SELECT
(select count(project_id)
from v_ProjectMilestone
where v_ProjectMilestone.project_id=pp.record_id) AS all_milestones,
(select count(project_id)
from v_ProjectMilestone
where v_ProjectMilestone.project_id=pp.record_id
and Actual_date is not null
) AS all_Actual_milestones,
(select count(project_id)
from v_ProjectMilestone
where v_ProjectMilestone.project_id=pp.record_id
and Actual_date is null
and proposed_date is not null
and revised_date is null
) AS all_Proposed_milestones,
(select count(project_id)
from v_ProjectMilestone
where v_ProjectMilestone.project_id=pp.record_id
and Actual_date is null
and revised_date is not null
) AS all_Revised_milestones,
(select count(project_id)
from v_ProjectMilestone
where v_ProjectMilestone.project_id=pp.record_id
and
(Actual_date is not null
or revised_date is not null
or proposed_date is not null)
) AS all_scheduled_milestones,

(select count(project_id)
from v_ProjectMilestone
where v_ProjectMilestone.project_id=pp.record_id
and is_external=1) AS External_milestones,
(select count(project_id)
from v_ProjectMilestone
where v_ProjectMilestone.project_id=pp.record_id
and Actual_date is not null
and is_external=1
) AS External_Actual_milestones,
(select count(project_id)
from v_ProjectMilestone
where v_ProjectMilestone.project_id=pp.record_id
and Actual_date is null
and proposed_date is not null
and revised_date is null
and is_external=1
) AS External_Proposed_milestones,
(select count(project_id)
from v_ProjectMilestone
where v_ProjectMilestone.project_id=pp.record_id
and Actual_date is null
and revised_date is not null
and is_external=1
) AS External_Revised_milestones,
(select count(project_id)
from v_ProjectMilestone
where v_ProjectMilestone.project_id=pp.record_id
and is_external=1
and
(Actual_date is not null
or revised_date is not null
or proposed_date is not null)
) AS External_scheduled_milestones,

(select count(project_id)
from v_ProjectMilestone
where v_ProjectMilestone.project_id=pp.record_id
and is_external=0) AS internal_milestones,
(select count(project_id)
from v_ProjectMilestone
where v_ProjectMilestone.project_id=pp.record_id
and Actual_date is not null
and is_external=0
) AS internal_Actual_milestones,
(select count(project_id)
from v_ProjectMilestone
where v_ProjectMilestone.project_id=pp.record_id
and Actual_date is null
and proposed_date is not null
and revised_date is null
and is_external=0
) AS internal_Proposed_milestones,
(select count(project_id)
from v_ProjectMilestone
where v_ProjectMilestone.project_id=pp.record_id
and Actual_date is null
and revised_date is not null
and is_external=0
) AS internal_Revised_milestones,

(select count(project_id)
from v_ProjectMilestone
where v_ProjectMilestone.project_id=pp.record_id
and is_external=0
and
(Actual_date is not null
or revised_date is not null
or proposed_date is not null)
) AS internal_scheduled_milestones,

project_id


from
project pp
inner join v_ProjectMilestone on v_ProjectMilestone.project_id=pp.record_id
)



select
Cast(Round(
(100.0*AllMilestones.all_actual_milestones)/
nullif(
(1.0*AllMilestones.all_milestones)
,0)
,0)as int) as percent_all_Actual_Milestones,

Cast(Round(
(100.0*AllMilestones.all_Proposed_milestones)/
nullif(
(1.0*AllMilestones.all_milestones)
,0)
,0)as int) as percent_all_Proposed_Milestones,

Cast(Round(
(100.0*AllMilestones.all_Revised_milestones)/
nullif(
(1.0*AllMilestones.all_milestones)
,0)
,0)as int) as percent_all_Revised_Milestones,

Cast(Round(
(100.0*AllMilestones.all_scheduled_milestones)/
nullif(
(1.0*AllMilestones.all_milestones)
,0)
,0)as int) as percent_all_Scheduled_Milestones,

Cast(Round(
(100.0*AllMilestones.External_actual_milestones)/
nullif(
(1.0*AllMilestones.External_milestones)
,0)
,0)as int) as percent_External_Actual_Milestones,

Cast(Round(
(100.0*AllMilestones.External_Proposed_milestones)/
nullif(
(1.0*AllMilestones.External_milestones)
,0)
,0)as int) as percent_External_Proposed_Milestones,

Cast(Round(
(100.0*AllMilestones.External_Revised_milestones)/
nullif(
(1.0*AllMilestones.External_milestones)
,0)
,0)as int) as percent_External_Revised_Milestones,

Cast(Round(
(100.0*AllMilestones.External_scheduled_milestones)/
nullif(
(1.0*AllMilestones.External_milestones)
,0)
,0)as int) as percent_External_Scheduled_Milestones,

Cast(Round(
(100.0*AllMilestones.Internal_actual_milestones)/
nullif(
(1.0*AllMilestones.Internal_milestones)
,0)
,0)as int) as percent_Internal_Actual_Milestones,

Cast(Round(
(100.0*AllMilestones.Internal_Proposed_milestones)/
nullif(
(1.0*AllMilestones.Internal_milestones)
,0)
,0)as int) as percent_Internal_Proposed_Milestones,

Cast(Round(
(100.0*AllMilestones.Internal_Revised_milestones)/
nullif(
(1.0*AllMilestones.Internal_milestones)
,0)
,0)as int) as percent_Internal_Revised_Milestones,

Cast(Round(
(100.0*AllMilestones.Internal_scheduled_milestones)/
nullif(
(1.0*AllMilestones.Internal_milestones)
,0)
,0)as int) as percent_Internal_Scheduled_Milestones
,project.*,
AllMilestones.*

from AllMilestones inner join project on project.record_id = AllMilestones.project_id

Answer

Your query is quite complicated; again and again you must read the same table. What you need instead is conditional aggregation. I moved the NULLIF to avoid division by zero to the WITH clause so as not to have to repeat it in every line.

with allmilestones as
( 
  project_id,
  nullif(count(*),0) as all_milestones,
  count(case when actual_date is not null then 1 end) as all_actual_milestones,
  count(case when actual_date is null
              and proposed_date is not null
              and revised_date is null then 1 end) as all_proposed_milestones,
  from v_projectmilestone
  group by project_id
)
select 
  project_id,
  round(100.0 * all_actual_milestones / all_milestones, 0) as percent_all_actual_milestones,
  round(100.0 * all_proposed_milestones / all_milestones, 0) as percent_all_proposed_milestones,
  round(100.0 * all_revised_milestones / all_milestones, 0) as percent_all_revised_milestones,
  ...
from allmilestones;

This should be as fast as it gets, as you'd read the table only once. I hope I got it right that you want one result row per project.

Comments