sql查詢（三）--having子句求衆數、中位數 - wl413911

sql查詢（三）--having子句求衆數、中位數

　

一、建立需要查詢的表
CREATE TABLE Graduates
(name   VARCHAR(16) PRIMARY KEY,
 income INTEGER NOT NULL);
-- 桑普森是個離群值，會拉高平均數
INSERT INTO Graduates VALUES(\'桑普森\', 400000);
INSERT INTO Graduates VALUES(\'邁克\',     30000);
INSERT INTO Graduates VALUES(\'懷特\',   20000);
INSERT INTO Graduates VALUES(\'阿諾德\', 20000);
INSERT INTO Graduates VALUES(\'史密斯\',     20000);
INSERT INTO Graduates VALUES(\'勞倫斯\',   15000);
INSERT INTO Graduates VALUES(\'哈德遜\',   15000);
INSERT INTO Graduates VALUES(\'肯特\',     10000);
INSERT INTO Graduates VALUES(\'貝克\',   10000);
INSERT INTO Graduates VALUES(\'斯科特\',   10000);      

1、求衆數
（1）在having子句中用包含謂詞all 的子查詢
SELECT income, COUNT(*) AS cnt 
FROM Graduates 
GROUP BY income HAVING COUNT(*) >= ALL ( SELECT COUNT(*) FROM Graduates GROUP BY income)；
缺點：謂詞all 用于null和 空集時會出現問題
（2）在having子句中 用 包含極值函數的子查詢
select income ,count(*) as cnt
from graduates
group by income
having cnt >= (select max(cnt)
　　　　　　　　　from (select count(*) as cnt
　　　　　　　　　　　　from graduates
　　　　　　　　　　　　group by income) as tmp));

      

2、求中位數
用having子句進行自連接配接求中位數
第一步-- 将集合裡的元素按照大小分為上半部分、下班部分 兩個子集，求其交集（無論聚合資料的數目是 奇數 偶數）
select  t1.income 
from gradutes t1 , gradutes t2
group by t1.income
having sum(case when t2.income >=t1.income then 1 else 0 end) >= count(*)/2
and sum(case when t2.income <=t1.income then 1 else 0 end) >= count(*)/2;
第二步 -- 将上下部分集合求得的交集，去重，然後求平均，得到中值
select avg(distinct income)
from ( select t1.income
　　　　from gradutes t1,gradutes t2
　　　　group by t1.income
　　　　having sum(case when t2.income >= t1.income then 1 else 0) >= count(*)/2
　　　　　　and sum (case when t2.incomme <= t1.income then 1 else 0 ) >= count(*)/2) as tmp