将一个聚合查询除以来自不同表的另一个聚合查询

Question

我有一个查询，它计算组织内每个组的平均每日人数，按季度细分。此查询为Table_A中的每个季度创建一个包含单个数字的结果集。

我有第二个查询，它计算组织内每个组的自然减员的n计数，按季度细分。此查询为Table_B中的每个季度创建一个包含单个数字的结果集。

我不想生成两个单独的结果集，而是希望编写一个查询，该查询调用两个表，并允许我将消耗汇总除以人数汇总结果，从而在最终结果集中显示消耗速率。

有什么建议吗？

由于我正在创建聚合select查询，我假设连接将不起作用？我可以在中编写一些东西，使两个结果集中的每一个都成为临时表中的一行，然后查询以运行除法过程吗？

提前感谢！

更新：

因此，这两个select查询由多个sums组成，每个sums从2010到2015创建自己的季度列。因此，消耗计数器查询如下所示：

select 
case when unified_rollup_level_2 = "Group A" then "A" 
     when unified_rollup_level_2 = "Group B" and job_family_code <> "PROD_MGT" then "B" 
     when unified_rollup_level_2 = "Group C" or job_family_code = "PROD_MGT" then "C"
     when unified_rollup_level_2 = "Group D" then "D" end as [Group],
sum(case when unified_rollup_level_1 = "Organization A" and person_type = 'Employee' and termination_counter = 1 and quarterofyear(termination_date) = 1 and year(termination_date) = 2010 then 1 else 0 end) Q1_2010,
sum(case when unified_rollup_level_1 = "Organization A" and person_type = 'Employee' and termination_counter = 1 and quarterofyear(termination_date) = 2 and year(termination_date) = 2010 then 1 else 0 end) Q2_2010,
sum(case when unified_rollup_level_1 = "Organization A" and person_type = 'Employee' and termination_counter = 1 and quarterofyear(termination_date) = 3 and year(termination_date) = 2010 then 1 else 0 end) Q3_2010,
sum(case when unified_rollup_level_1 = "Organization A" and person_type = 'Employee' and termination_counter = 1 and quarterofyear(termination_date) = 4 and year(termination_date) = 2010 then 1 else 0 end) Q4_2010,
sum(case when unified_rollup_level_1 = "Organization A" and person_type = 'Employee' and termination_counter = 1 and quarterofyear(termination_date) = 1 and year(termination_date) = 2011 then 1 else 0 end) Q1_2011,
sum(case when unified_rollup_level_1 = "Organization A" and person_type = 'Employee' and termination_counter = 1 and quarterofyear(termination_date) = 2 and year(termination_date) = 2011 then 1 else 0 end) Q2_2011,
sum(case when unified_rollup_level_1 = "Organization A" and person_type = 'Employee' and termination_counter = 1 and quarterofyear(termination_date) = 3 and year(termination_date) = 2011 then 1 else 0 end) Q3_2011,
sum(case when unified_rollup_level_1 = "Organization A" and person_type = 'Employee' and termination_counter = 1 and quarterofyear(termination_date) = 4 and year(termination_date) = 2011 then 1 else 0 end) Q4_2011,
sum(case when unified_rollup_level_1 = "Organization A" and person_type = 'Employee' and termination_counter = 1 and quarterofyear(termination_date) = 1 and year(termination_date) = 2012 then 1 else 0 end) Q1_2012,
sum(case when unified_rollup_level_1 = "Organization A" and person_type = 'Employee' and termination_counter = 1 and quarterofyear(termination_date) = 2 and year(termination_date) = 2012 then 1 else 0 end) Q2_2012,
sum(case when unified_rollup_level_1 = "Organization A" and person_type = 'Employee' and termination_counter = 1 and quarterofyear(termination_date) = 3 and year(termination_date) = 2012 then 1 else 0 end) Q3_2012,
sum(case when unified_rollup_level_1 = "Organization A" and person_type = 'Employee' and termination_counter = 1 and quarterofyear(termination_date) = 4 and year(termination_date) = 2012 then 1 else 0 end) Q4_2012,
sum(case when unified_rollup_level_1 = "Organization A" and person_type = 'Employee' and termination_counter = 1 and quarterofyear(termination_date) = 1 and year(termination_date) = 2013 then 1 else 0 end) Q1_2013,
sum(case when unified_rollup_level_1 = "Organization A" and person_type = 'Employee' and termination_counter = 1 and quarterofyear(termination_date) = 2 and year(termination_date) = 2013 then 1 else 0 end) Q2_2013,
sum(case when unified_rollup_level_1 = "Organization A" and person_type = 'Employee' and termination_counter = 1 and quarterofyear(termination_date) = 3 and year(termination_date) = 2013 then 1 else 0 end) Q3_2013,
sum(case when unified_rollup_level_1 = "Organization A" and person_type = 'Employee' and termination_counter = 1 and quarterofyear(termination_date) = 4 and year(termination_date) = 2013 then 1 else 0 end) Q4_2013,
sum(case when unified_rollup_level_1 = "Organization A" and person_type = 'Employee' and termination_counter = 1 and quarterofyear(termination_date) = 1 and year(termination_date) = 2014 then 1 else 0 end) Q1_2014,
sum(case when unified_rollup_level_1 = "Organization A" and person_type = 'Employee' and termination_counter = 1 and quarterofyear(termination_date) = 2 and year(termination_date) = 2014 then 1 else 0 end) Q2_2014,
sum(case when unified_rollup_level_1 = "Organization A" and person_type = 'Employee' and termination_counter = 1 and quarterofyear(termination_date) = 3 and year(termination_date) = 2014 then 1 else 0 end) Q3_2014,
sum(case when unified_rollup_level_1 = "Organization A" and person_type = 'Employee' and termination_counter = 1 and quarterofyear(termination_date) = 4 and year(termination_date) = 2014 then 1 else 0 end) Q4_2014,
sum(case when unified_rollup_level_1 = "Organization A" and person_type = 'Employee' and termination_counter = 1 and quarterofyear(termination_date) = 1 and year(termination_date) = 2015 then 1 else 0 end) Q1_2015
#copy row above and adjust Month and Year to add in current Quarter's data
from TerminationDetail
group by Group
having Group IN ("A","B","C","D")
order by Group asc

平均每天的员工人数在性质上是相似的，每个季度都有一个单独的求和函数。

鉴于此，死区，我该如何结合您提出的策略呢？

Answer 1

您没有指定正在使用的SQL类型。但这应该适用于Microsoft SQL Server，使用公用表表达式(或CTE)。

WITH 
  Query1 AS (SELECT Quarter, Headcount FROM...),
  Query2 AS (SELECT Quarter, Attrition FROM...)
SELECT Query1.Quarter, Query1.Headcount / Query2.Attrition AS AttritionRate
FROM Query1
INNER JOIN Query2 on Query2.Quarter = Query1.Quarter

您必须填写Query1和Query2的查询。但这应该会给你你想要的东西。