我要评论
sql实现寻找中位数
思路1
对于一个 奇数 长度数组中的 中位数，大于这个数的数值个数等于小于这个数的数值个数。
算法
根据上述的定义，我们来找一下 [1, 3, 2] 中的中位数。首先 1 不是中位数，因为这个数组有三个元素，却有两个元素 (3，2) 大于 1。3 也不是中位数，因为有两个元素小于 3。对于最后一个 2 来说，大于 2 和 小于 2 的元素数量是相等的，因此 2 是当前数组的中位数。
当数组长度为 偶数，且元素唯一时，中位数等于排序后 中间两个数 的平均值。对这两个数来说，大于当前数的数值个数跟小于当前数的数值个数绝对值之差为 1，恰好等于这个数出现的频率。
总的来说，不管是数组长度是奇是偶，也不管元素是不是唯一，中位数出现的频率一定大于等于大于它的数和小于它的数的绝对值之差。这个规律是这道题的关键，可以通过下面这个搜索条件来过滤。
--架构
Create table If Not Exists Employee (Id int, Company varchar(255), Salary int)
Truncate table Employee
insert into Employee (Id, Company, Salary) values ('1', 'A', '2341')
insert into Employee (Id, Company, Salary) values ('2', 'A', '341')
insert into Employee (Id, Company, Salary) values ('3', 'A', '15')
insert into Employee (Id, Company, Salary) values ('4', 'A', '15314')
insert into Employee (Id, Company, Salary) values ('5', 'A', '451')
insert into Employee (Id, Company, Salary) values ('6', 'A', '513')
insert into Employee (Id, Company, Salary) values ('7', 'B', '15')
insert into Employee (Id, Company, Salary) values ('8', 'B', '13')
insert into Employee (Id, Company, Salary) values ('9', 'B', '1154')
insert into Employee (Id, Company, Salary) values ('10', 'B', '1345')
insert into Employee (Id, Company, Salary) values ('11', 'B', '1221')
insert into Employee (Id, Company, Salary) values ('12', 'B', '234')
insert into Employee (Id, Company, Salary) values ('13', 'C', '2345')
insert into Employee (Id, Company, Salary) values ('14', 'C', '2645')
insert into Employee (Id, Company, Salary) values ('15', 'C', '2645')
insert into Employee (Id, Company, Salary) values ('16', 'C', '2652')
insert into Employee (Id, Company, Salary) values ('17', 'C', '65')
SELECT
Employee.Id, Employee.Company, Employee.Salary
FROM
Employee,
Employee alias
WHERE
Employee.Company = alias.Company
GROUP BY Employee.Company , Employee.Salary
HAVING SUM(CASE
WHEN Employee.Salary = alias.Salary THEN 1
ELSE 0
END) >= ABS(SUM(SIGN(Employee.Salary - alias.Salary)))
ORDER BY Employee.Id;
--alias是别名
1.运用CASE表达式，非等值自连接和HAVING子句来找中位数
2.通过 WHERE e1.Company = e2.Company 进行分组
3.最后通过GROUP BY 去重
--更好理解一些：
select Id, Company, Salary
from Employee
where Id in (select e1.Id
from Employee e1, Employee e2
WHERE e1.Company = e2.Company
GROUP BY e1.Id
HAVING SUM(CASE WHEN e1.Salary >= e2.Salary THEN 1 ELSE 0 END) >= COUNT(*)/2 AND SUM(CASE WHEN e1.Salary <= e2.Salary THEN 1 ELSE 0 END) >= COUNT(*)/2)
GROUP BY Company, Salary
ORDER BY Company
case
The CASE statement goes through conditions and returns a value when the first condition is met (like an IF-THEN-ELSE statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.If there is no ELSE part and no conditions are true, it returns NULL.
--用例
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END;
sign（）：
这里是引用
ps：注意：在 MySQL 5.6 中，这个代码是可以运行的，但如果你用的是 MySQL 5.7+，就需要在 SELECT 语句中 把 Employee.id 改成 ANY_VALUE(Employee.Id)。
思路二：排序后再找中位数
根据 salary 排序记录，利用会话变量计算排名。由于不需要级联表，这个方法要比方法一更高效。
SELECT
Id, Company, Salary
FROM
(SELECT
e.Id,
e.Salary,
e.Company,
IF(@prev = e.Company, @Rank:=@Rank + 1, @Rank:=1) AS rank,
@prev:=e.Company
FROM
Employee e, (SELECT @Rank:=0, @prev:=0) AS temp
ORDER BY e.Company , e.Salary , e.Id) Ranking
INNER JOIN
(SELECT
COUNT(*) AS totalcount, Company AS name
FROM
Employee e2
GROUP BY e2.Company) companycount ON companycount.name = Ranking.Company
WHERE
Rank = FLOOR((totalcount + 1) / 2)
OR Rank = FLOOR((totalcount + 2) / 2)
;
自定义变量
本文地址：https://blog.csdn.net/qq_43515555/article/details/107309590
您可能感兴趣的文章:
如您对本文有疑问或者有任何想说的，请点击进行留言回复，万千网友为您解惑！
网友评论