计数和最大/最小值和(具有?)在一个查询/ AdventureWorks2017任务中

Question

我在AdventureWorks2017 DB中有一个训练任务。任务如下： 我需要一份职位列表，其中最少和大多数女性按比例工作。只考虑那些至少有 4 人工作的公司。

到目前为止，我的代码如下所示：

select a.JobTitle, AVG(ertek) as avg_women, COUNT(*) as sum_women
from
(select BusinessEntityID, JobTitle, Gender, CASE WHEN Gender = 'F' THEN 1.0 ELSE 0.0
END as ertek
from HumanResources.Employee) a
group by a.JobTitle

这让我回到了所有的职称，这些头衔中女性的平等以及职称中女性的总和。问题是我不能在查询中使用 MAX 和 MIN(也许我需要创建另一个子查询？我需要获得任务的最后一部分"只考虑那些至少有 4 人工作的那些"。 感谢您的帮助！

急诊室图：https://homel.vsb.cz/~dan11/ddj/AdventureWorks2008_db_diagram.pdf

Answer 1

一次满足一个此类要求，并逐步构建解决方案。

在每个附加步骤之后运行查询以确认预期结果！

步骤

1. 仅限超过 4 人的职称：
   group by e.JobTitle having count(1) >= 4
2. 按比例计算女性(我将其解释为"一个职位内女性的相对人数")。这需要总数，而女性只计数：
   count(1) as JobTitleCount和
   count(case when e.Gender = 'F' then 1 end) as FemaleCount
   相对(比例)数字或百分比然后变为：
   count(case when e.Gender = 'F' then 1 end)*100.0/count(1) as FemalePercentage
">
3. 最少"和"最多"表示使用order by子句对函数进行排名：
   步骤 2 中的dense_rank() over(order by<% 来到这里>) as RankLeast
   步骤 2 中的dense_rank() over(order by<% 来到这里>desc) as RankMost
筛选任一排名
4. 中排名第一的职位名称：
   where jfpr.RankLeast = 1 or jfpr.RankMost = 1

中间结果

步骤 1. 至 3。

select  e.JobTitle,
count(1) as JobTitleCount,
count(case when e.Gender = 'F' then 1 end) as FemaleCount,
count(case when e.Gender = 'F' then 1 end)*100.0/count(1) as FemalePercentage,
dense_rank() over(order by count(case when e.Gender = 'F' then 1 end)*100.0/count(1)     ) as FemalePercentageRankLeast,
dense_rank() over(order by count(case when e.Gender = 'F' then 1 end)*100.0/count(1) desc) as FemalePercentageRankMost
from HumanResources.Employee e
group by e.JobTitle
having count(1) >= 4
order by FemalePercentage;

完整解决方案

将整个上一个查询移动到子查询jfpr，并省略我们不再需要的列作为最终结果(JobTitleCount和FemaleCount)。order by移动到子查询外部。

select  jfpr.JobTitle,
jfpr.FemalePercentage
from (  select  e.JobTitle,
count(case when e.Gender = 'F' then 1 end)*100.0/count(1) as FemalePercentage,
dense_rank() over(order by count(case when e.Gender = 'F' then 1 end)*100.0/count(1)     ) as FemalePercentageRankLeast,
dense_rank() over(order by count(case when e.Gender = 'F' then 1 end)*100.0/count(1) desc) as FemalePercentageRankMost
from HumanResources.Employee e
group by e.JobTitle
having count(1) >= 4 ) jfpr -- job female percentage rank
where jfpr.FemalePercentageRankLeast = 1
or jfpr.FemalePercentageRankMost = 1
order by jfpr.FemalePercentage;

结果

在我的AdventureWorks副本上(不知道我现在有什么版本)，这会产生：

JobTitle                      FemalePercentage
----------------------------  ----------------
Quality Assurance Technician   0.000000000000
Scheduling Assistant           0.000000000000
Janitor                       50.000000000000
Application Specialist        50.000000000000