達人に学ぶSQL徹底指南書 1-4 HAVING句の力

データの歯抜けを探す

テーブル定義

create table tbl1_4_1_SeqTbl(
    seq int,
    name varchar(10)
)

insert into dbo.tbl1_4_1_SeqTbl values
    (1, 'ディック'),
    (2, 'アン'),
    (3, 'ライル'),
    (5, 'カー'),
    (6, 'マリー'),
    (8, 'ベン')

-- seqの歯抜けチェック
select '歯抜けあり' as gap from dbo.tbl1_4_1_SeqTbl
having count(*) <> max(seq);

--歯抜けの最小値を探す
select min(seq + 1) as gap from dbo.tbl1_4_1_SeqTbl
where (seq+1) not in (select seq from dbo.tbl1_4_1_SeqTbl)
--gap
--4

--not exists版
select min(seq + 1) as gap from dbo.tbl1_4_1_SeqTbl a
where not exists (select * from dbo.tbl1_4_1_SeqTbl b where a.seq + 1 = b.seq)
--gap
--4

最頻値を求める

テーブル定義

create table tbl1_4_3_Graduates(
    name nvarchar(10),
    income int
)

insert into dbo.tbl1_4_3_Graduates values
    ('a', 400000),
    ('b', 30000),
    ('c', 20000),
    ('d', 20000),
    ('e', 20000),
    ('f', 15000),
    ('g', 15000),
    ('h', 10000),
    ('i', 10000),
    ('j', 10000)

--最頻値を求める(allの利用)
select income, count(*) as cnt from dbo.tbl1_4_3_Graduates
group by income
having count(*) >= all(
    select count(*) from dbo.tbl1_4_3_Graduates
    group by income
)

--income   cnt
--10000    3
--20000    3

--最頻値を求める(maxの利用)
select income, count(*) as cnt from dbo.tbl1_4_3_Graduates
group by income
having count(*) >= (
    select max(cnt) from(
        select count(*) as cnt from dbo.tbl1_4_3_Graduates
        group by income
    ) tmp
)
--income   cnt
--10000    3
--20000    3

中央値（メジアン）を求める

--メジアンを求める
select avg(distinct income) as median from(
    select t1.income from dbo.tbl1_4_3_Graduates t1, dbo.tbl1_4_3_Graduates 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
) tmp

--median
--17500

NULLを含まない集合を探す

テーブル定義

create table tbl1_4_5_Students(
    id int,
    dpt nvarchar(10),
    submit_date date
)

insert into dbo.tbl1_4_5_Students values
    (100, '理学部', '2005-10-10'),
    (101, '理学部', '2005-09-22'),
    (102, '文学部', null),
    (103, '文学部', '2005-09-10'),
    (200, '文学部', '2005-09-22'),
    (201, '工学部', null),
    (202, '経済学部', '2005-09-25')

--提出日にnullを含まない(=全員提出)学部を選択する
select dpt from dbo.tbl1_4_5_Students
group by dpt
having count(*) = sum(
    case when submit_date is not null then 1 else 0 end
)

関係除算でバスケット解析

テーブル定義

create table tbl1_4_6_Items(
    item nvarchar(10)
)

insert into dbo.tbl1_4_6_Items values
    ('ビール'),
    ('紙オムツ'),
    ('自転車')

create table tbl1_4_6_ShopItems(
    shopname nvarchar(10),
    item nvarchar(10)
)

insert into dbo.tbl1_4_6_ShopItems values
    ('仙台', 'ビール'),
    ('仙台', '紙オムツ'),
    ('仙台', '自転車'),
    ('仙台', 'カーテン'),
    ('東京', 'ビール'),
    ('東京', '紙オムツ'),
    ('東京', '自転車'),
    ('大阪', 'テレビ'),
    ('大阪', '紙オムツ'),
    ('大阪', '自転車')

--Itemsテーブルの商品を全て備えた店を探すSQL（他の物があってもよい）
select si.shopname from dbo.tbl1_4_6_ShopItems si, dbo.tbl1_4_6_Items i
where si.item = i.item
group by si.shopname
having count(si.item) = (select count(item) from dbo.tbl1_4_6_Items)
--shopname
--仙台
--東京

--Itemsテーブルの商品を全て備えた店を探すSQL（他の物があるとダメ）
select si.shopname 
from dbo.tbl1_4_6_ShopItems si
left outer join dbo.tbl1_4_6_Items i
on si.item = i.item
group by si.shopname
having count(si.item) = (select count(item) from dbo.tbl1_4_6_Items)
    and count(i.item) = (select count(item) from dbo.tbl1_4_6_Items)
--shopname
--東京