連続する〇〇な区間を探す

今回は、達人SQL1-9の「連続した特定条件を満たす数列の抽出」をまとめる

この時の考え方は2つ。

自己結合で始点と終点の組み合わせを作る
始点と終点の間の全ての点が満たすべき条件を記述する

この2つ目について、「始点と終点の間」を考えるためにBETWEENを使い、また「全ての点が満たすべき条件」については条件を2重否定文にし、これらをNOT EXIXTSのサブクエリとして探索する。（全称量の書き換え）

ここでは、以下のような○×が順番に並んだテーブルを考える。

id	mark
1	X
2	O
3	X
4	O
5	O
6	X
7	X
8	O
9	O
10	O

この内、◯が連続している区間を見つけたい。
そこで先程の2つの考え方から、まずは自己結合を用いて考えるべき区間を作る。

select
    m1.id as start_id, m2.id as end_id
from
    marubatsu m1, marubatsu m2
where
    m1.id < m2.id;

where句に条件をつけることで、id=1から昇順に順番に並んだ組み合わせを考えることができる。

次に2つ目について、まず「始点と終点の間」についてはstart_idとend_idをbetweenに用いればよい。
また、「◯が連続している区間」というのは、「特定の2点間で、◯でないところが1つもない」というふうに考えられる。
これらをsqlで表現すると以下のようになる。

select
    m1.id as start_id, m2.id as end_id
from
    marubatsu m1, marubatsu m2
where
    m1.id < m2.id
    and not exists(
        select *
        from marubatsu m3
        where m3.id BETWEEN m1.id and m2.id
        and m3.mark <> '◯'
    );

start_id	end_id
4	5
8	9
8	10
9	10

このクエリでは、8~10の区間についてその部分集合も取得している。
結果から部分集合を取り除きたい場合は極値関数を使う。

select min(start_id) as start_id,
    end_id
from (
    select
        m1.id as start_id, max(m2.id) as end_id
    from
        marubatsu m1, marubatsu m2
    where
        m1.id <= m2.id
        and not exists(
            select *
            from marubatsu m3
            where m3.id BETWEEN m1.id and m2.id
                  and m3.mark <> '◯'
        )
    group by m1.id) tmp
group by end_id;

start_id	end_id
4	5
8	10

また、今回は「連続した」区間を考えたので、連続せず一行だけ○になるid=2は除外されている。
これも結果に含める場合には、はじめの区間を求める自己結合のwhere区の条件を「<=」にすれば取得できる。

達人に学ぶ SQL徹底指南書 (CodeZine BOOKS)

posted with amazlet at 17.07.10

ミック
翔泳社
売り上げランキング: 10,012

Amazon.co.jpで詳細を見る