学习《SQL进阶教程》时遇到的问题。
书上写的:
书上的代码
-- 求中位数的 SQL 语句 :在 HAVING 子句中使用非等值自连接
SELECT AVG(DISTINCT income)
FROM (SELECT T1.income
FROM Graduates T1, Graduates T2
GROUP BY T1.income
--S1 的条件
HAVING SUM(CASE WHEN T2.income >= T1.income THEN 1 ELSE 0 END)
>= COUNT(*) / 2
--S2 的条件
AND SUM(CASE WHEN T2.income <= T1.income THEN 1 ELSE 0 END)
>= COUNT(*) / 2 ) TMP;
看到这里卡住了,卡了两天,到处查资料,最后看到了这篇博客,有些启发。利用SQL求中位数(已修复BUG)_日积月累,天道酬勤-CSDN博客_sql求中位数https://blog.csdn.net/yjw123456/article/details/100988587
因为SQL是面向集合的,所以要求中位数的基本思路就是查询位于集合正中间的元素。
当数据个数为奇数时,最中间的数就是中位数;当数据个数为偶数时,中间两个数的平均值为中位数,所以可以想到用到聚合函数AVG。如果已知数据个数为奇数,就没必要用AVG,但是为了,增强代码的通用性,最好还是用。
CREATE TABLE Graduates
(name VARCHAR(16) PRIMARY KEY,
income INTEGER NOT NULL);
INSERT INTO Graduates VALUES('桑普森', 2);
INSERT INTO Graduates VALUES('迈克', 3);
INSERT INTO Graduates VALUES('怀特', 5);
INSERT INTO Graduates VALUES('阿诺德', 8);
使用非等值自连接将表连接起来。
--全连接,16条数据
select t1.income,t2.income
from Graduates t1,graduates t2;
接着单独运行s1的条件
--s1的条件
select t1.income,
sum( case when t2.income >= t1.income then 1 else 0 end) as sum,
count(*)/2 as cnt
from graduates t1,graduates t2
group by t1.income ;
--满足条件 sum>cnt 的有 2,3,5
单独运行s2的条件
select t1.income,
sum( case when t2.income <= t1.income then 1 else 0 end) as sum,
count(*)/2 as cnt
from graduates t1,graduates t2
group by t1.income ;
--满足条件 sum>cnt 的有 3,5,8
>=count(*)/2 表示要选出元素(满足条件:表t2的income <= 表 t1的income)的t1.income的个数大于等于总个数的一半,取出一半+1个元素。
sum( case when t2.income >= t1.income then 1 else 0 end) as sum, count(*)/2 as cnt
选出的就是集合的下半部分元素。
同理 s2的条件选出的就是集合的上半部分元素。
最后取出交集(既满足s1的条件,又满足s2的条件)即3,5。
最后avg(3,5)=4.