在子组的子组上应用COUNT函数 [英] Apply COUNT function on a subgroup of groups

问题描述

我编造了这个奇怪的例子，试图说明我想做什么（这很愚蠢，但忍耐着我）： 考虑下表： / p>

员工

已婚，已认证和宗教只是布尔字段（在Oracle的情况下，它们的类型为NUMBER（1,0））。

我需要为每个hire_year显示SQL，以下薪水类别中的已婚，认证和宗教雇员的人数：

• A SALARY> 2000


• B 1000与2000之间的薪水


• C SALARY < 1000

基于以上数据集，我期望得到：

到目前为止，我只提出了以下SQL：

  SELECT 
 COUNT（CASE WHEN已结婚= 1则1结束）视为已婚，
计数（认证结束时= 1则1结束），
计数（宗教= 1则1结束时为宗教），
 hire_year 
 FROM employees 
 GROUP BY hire_year; 
  

执行此SQL的结果是：

这几乎就是我需要，但我也需要根据工资范围将这些计数器进一步分成组。

我猜测一些分析函数会根据一些SQL表达式将组分成桶，但我无法弄清楚哪一个。我尝试过使用 NTILE ，但它希望将一个正常数作为参数，而不是SQL表达式（例如 X和Y 之间的SALARY BETWEEN）。

解决方案

不需要解析函数;他们很难与聚合函数在同一个查询中。

您正在寻找 case 声明，您只需将其放在GROUP BY中即可。

 选择hire_year 
，已婚（结婚）已婚b $ b，已认证为已认证
，作为宗教
，工资> 2000然后'A'
当salary> = 1000时，那么'B'
 else'C'结束为salary_class 
 from employees 
 by hire_year 
，case when薪水> 2000然后'A'
当薪水> = 1000时，'B'
 else'C'结束
  

请注意，我已将您的 count（case when when ...）更改为 sum（）。这是因为您使用的是布尔型1/0，所以这将以相同的方式工作，但它更清晰。

出于同样的原因，我忽略了您的薪金计算中之间的;没有特别的需要，就好像薪水大于2000第一个CASE已经完成。

I made up this weird example trying to illustrate what I want to do (it's kind of stupid, but bear with me):

Consider the following table:

EMPLOYEES

married, certified and religious are just boolean fields (in case of Oracle, they are of type NUMBER(1,0)).

I need to come up with SQL that displays for each hire_year, count of married, certified and religious employees within the following salary categories:

• A SALARY > 2000
• B SALARY BETWEEN 1000 AND 2000
• C SALARY < 1000

Based on the above dataset, here is what I expect to get:

So far, I've only come up with the following SQL:

SELECT
COUNT(CASE WHEN married = 1 THEN 1 END) as MARRIED,
COUNT(CASE WHEN certified = 1 THEN 1 END) as certified,
COUNT(CASE WHEN religious = 1 THEN 1 END) as religious,
hire_year
FROM employees
GROUP BY hire_year;

The result of executing this SQL is:

Which is almost what I need, but I also need to divide these counters further down into the groups based on a salary range.

I guess that some analytic function, that divides groups into the buckets based on some SQL expression would help, but I can't figure out which one. I tried with NTILE, but it expects a positive constant as a parameter, rather than an SQL expression (such as SALARY BETWEEN X and Y).

解决方案

Nope, no need for analytic functions; they're difficult to have in the same query as an aggregate function anyway.

You're looking for the case statement again, you just have to put it in the GROUP BY.

select hire_year
     , sum(married) as married
     , sum(certified) as certified
     , sum(religious) as religious
     , case when salary > 2000 then 'A'
            when salary >= 1000 then 'B'
            else 'C' end as salary_class
  from employees
 group by hire_year
     , case when salary > 2000 then 'A'
            when salary >= 1000 then 'B'
            else 'C' end

Note that I've changed your count(case when...) to sum(). This is because you're using a boolean 1/0 so this'll work in the same manner but it's a lot cleaner.

For the same reason I've ignored your between in your salary calculation; there's no particular need for it as if the salary is greater than 2000 the first CASE has already been fulfilled.

这篇关于在子组的子组上应用COUNT函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！