SQL进阶--Having子句的力量

查找缺失值

drop table if exists seqtbl;
create table seqtbl(
seq int8,
name1 varchar(10)
);
insert into seqtbl values(1, '迪克');
insert into seqtbl values(2, '安');
insert into seqtbl values(3, '莱露');
insert into seqtbl values(5, '卡');
insert into seqtbl values(6, '玛丽');
insert into seqtbl values(8, '本');

-- 将表整体看做是一个集合，就可以像一下解决问题——寻找缺失的编号
-- ## 1 确定是否存在缺失
select '存在缺失编号' as gap -- 当having 为True 时会返回该值
from seqtbl
having count(*) <> max(seq);
-- ## 2 查询缺失编号的最小值
select  min(a.seq + 1) as gap
  from  seqtbl a
 where  not exists (select * from seqtbl b where a.seq + 1 = b.seq);  # 可以防止NULL的干扰

求众数

-- 用having句子进行子查询：求众数
drop table if exists graduates;
create table graduates(
name1 varchar(20),
income integer
);
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):使用谓语
select income, count(1) as cnt
  from graduates
 group by income
having count(1) >= all(select count(1) from graduates group by income);
-- all 会受 NULL 影响，可修正为以下
select income, count(1) as cnt
  from graduates
 group by income
having count(1) >= (select max(cmt) from (select count(1) cnt from graduates group by income));

求中位数

-- having子句进行自连接：求中位数
-- 使用非等值自连接
select avg(distinct income)
from (select t1.income 
	  from graduates t1, graduates t2
      group by t1.income  -- 对 income 分组 分组条件having中的True
      -- 求低工资部分子集 等号是为了让两个子集拥有交集
      having sum(case when t2.income >= t1.income then 1 else 0 end) >= count(1) /2 
      -- 求高工资部分子集
            and sum(case when t2.income <= t1.income then 1 else 0 end) >= count(1) / 2) tmp;

关系除法运算进行购物篮分析

-- 用关系除法运算进行购物篮分析
drop table if exists items;
create table items(
item varchar(20)
);
insert into items values('啤酒');
insert into items values('纸尿裤');
insert into items values('自行车');

drop table if exists shopitems;
create table shopitems(
shop varchar(20),
item varchar(20)
);
insert into shopitems values('仙台', '啤酒');
insert into shopitems values('仙台', '纸尿裤');
insert into shopitems values('仙台', '自行车');
insert into shopitems values('仙台', '窗帘');
insert into shopitems values('东京', '啤酒');
insert into shopitems values('东京', '纸尿裤');
insert into shopitems values('东京', '自行车');
insert into shopitems values('大阪', '电视');
insert into shopitems values('大阪', '纸尿裤');
insert into shopitems values('大阪', '自行车');

-- 查询啤酒、纸尿裤和自行车同时在库的店铺
select  si.shop
  from  shopitems si, items i
 where  si.item = i.item
group by si.shop
having count(si.item) =( select count(item) from items);

-- 精确关系除法运算：使用外链接和count函数
-- 即只选择没有剩余商品的店铺
select   si.shop
  from   shopitems si 
		 left join items i    -- left outer join 等价于 left join
         on si.item = i.item
group by si.shop
having   count(si.item) = (select count(item) from items)
   and   count(i.item)  = (select count(item) from items);

练习

 -- 1-4-1 修改编号缺失的检查逻辑
select
	case when count(*) <> max(seq) then '存在缺失编号' else '不存在缺失编号' end as gap
from seqtbl;

-- 1-4-2 练习“特征函数”
drop table if exists students ;
create table students(
student_id int8,
dpt varchar(10),
sbmt_data date
);
insert into students values(100,'理学院',20051010);
insert into students values(101,'理学院',20050922);
insert into students values(102,'文学院',null);
insert into students values(103,'文学院',20050910);
insert into students values(200,'文学院',20050922);
insert into students values(201,'工学院',NULL);
insert into students values(202,'经济学院',20050925);
-- 全体学生都在9月提交了报告的学院
select dpt, '全体学生都在9月提交了报告的学院' as res
from students
group by dpt
having  max(month(sbmt_data))<=9
   and  count(dpt) = count(sbmt_data);

-- 1-4-3 购物篮分析问题的一般化
  select  a.shop
		  ,count(b.item) my_item_cnt
		  ,3-count(b.item) diff_cnt
    from  shopitems a
left join items     b
      on  a.item = b.item
group by  a.shop
;

中位数(非等值自连接)

回答 三生暮雨渡瀟瀟 提问

-- 0 笛卡尔
select *
from  tmp_grade as t1, tmp_grade as t2;  -- 这个时候 t1和t2会组成笛卡尔积
/* 比如
史密斯	20000	史密斯	20000
史密斯	20000	斯科特	10000
史密斯	20000	阿诺德	20000
史密斯	20000	迈克	30000
史密斯	20000	哈德逊	15000
史密斯	20000	肯特	10000
史密斯	20000	贝克	10000
史密斯	20000	桑普森	400000
史密斯	20000	劳伦斯	15000
史密斯	20000	怀特	20000
*/

-- 1 
select    avg(disticnt income) --中位数 (集合正中间元素的均值为中位数)
from(
  select      T1.income
    from       tmp_grade as t1,  tmp_grade as t2  -- 这个时候 t1和t2会组成笛卡尔积
group by      T1.income 
  having      SUM(CASE WHEN T2.income >= T1.income THEN 1 ELSE 0 END) >= COUNT(1) / 2  -- 比较 每一行的数 取数在上半部分的集合
/* 比如 比较 明显 收入20000 超过8人 >  小组的一半人员(10/2 = 5)
史密斯	20000	史密斯	20000     1
史密斯	20000	斯科特	10000     1
史密斯	20000	阿诺德	20000     1
史密斯	20000	迈克	30000     0
史密斯	20000	哈德逊	15000     1
史密斯	20000	肯特	10000     1
史密斯	20000	贝克	10000     1
史密斯	20000	桑普森	400000    0
史密斯	20000	劳伦斯	15000     1
史密斯	20000	怀特	20000     1
*/
    and       SUM(CASE WHEN T2.income <= T1.income THEN 1 ELSE 0 END) >= COUNT(1) / 2 -- 同上
)

内容多来自 《SQL进阶教材》，仅做笔记。 练习代码均为原创。