SQL中的多数功能 [英] Majority function in SQL

问题描述

我正在编写一条SQL命令来查找表的大部分值（如果表中出现的值超过50％，则返回值为多数）。对于例如一个雇主（姓名，年龄，部门）
在表中列出：（32,33,45,45,45 21,45）

所以如果用户想要获得领域时代的大多数价值，他会运行命令：

pre $ c $ SELECT多数（年龄）从雇员

现在在后端生成相应的SQL命令，如下所示：

 选择年龄从Emp GROUP BY年龄HAVING COUNT（*）> （（SELECT COUNT（*）FROM Emp）/ 2）; 
  

该查询返回45作为结果。

但对于用户查询，如：

  SELECT多数（年龄），部门FROM Emp GROUP BY部门
 
 code> 

然后我不确定如何通过特性创建与组相似的查询，还是应该创建一些其他查询？提前致谢


解决方案

（假设您使用的是Oracle--因为在MySQL中您无法编写自己的聚合） p>



我认为你可以用窗口函数做到这一点：

这与第一个例子相同：

 选择不同年龄
从（
选择年龄，
部门，
计数（* ）over（）作为total_count，
 count（*）over（按年龄划分）age_count 
 from emp 
）
 where age_count> = total_count / 2; 
  

通过简单地将部门添加到分区（组）定义中，您应该可以得到您想要的结果：

 选择不同年龄
 from（
选择年龄，
部门，
计数（*）over（按部门划分）作为dept_count，
 count（*）over（按部门划分，age）作为age_count 
 from emp 
）
 where age_count> = dept_count / 2; 
  

I am writing a SQL command to find majority of value(a value is returned as majority if its occurence in the table is more then 50%) in a table. For e.g. a Emp(name, age, dept) with values in table: (32, 33, 45, 45 ,45 21, 45)

So if user wants to get majority value of field age, he will run the command:

SELECT majority(age) FROM Emp

Now at the backend the corresponding SQL command generated will be like:

SELECT age FROM Emp GROUP BY age HAVING COUNT(*) > ((SELECT COUNT(*) FROM Emp) / 2);

This query returns 45 as result.

But for a user query like:

SELECT majority(age), dept FROM Emp GROUP BY dept

Then I am not sure how to create similar query with group by feature, or should I create some other query? Thanks in advance

解决方案

(Assuming you are using Oracle - because in MySQL you can't write your own aggregates anyway)

I think you can do this with window functions:

This is the equivalent to the first example:

select distinct age
from (
  select age, 
         dept,
         count(*) over () as total_count, 
         count(*) over (partition by age) as age_count
  from emp
) 
where age_count >= total_count / 2;

By simply adding the department to the partition (group) definition this should give you what you want:

select distinct age
from (
  select age, 
         dept,
         count(*) over (partition by dept) as dept_count, 
         count(*) over (partition by dept, age) as age_count
  from emp
) 
where age_count >= dept_count / 2;

这篇关于SQL中的多数功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！