SQLアタマアカデミー
第1回 連番の特性を利用してデータ操作をもっと自由に SQLで連番を扱う (5)断絶区間を求める
断絶区間を求める
最後に,
表10は1から始まる連番テーブル…だったのですが,
表10 Numbers
num |
---|
1 |
3 |
4 |
7 |
8 |
9 |
12 |
図7 欠番のカタマリ
gap_start ~ gap_end --------- ---- ------- 2 ~ 2 5 ~ 6 10 ~ 11
集合指向的な解法
方法はいくつかありますが,
手続き型言語であれば,
一方,
表11 ある行を起点として上界集合を求める
N1. | N2. | |
---|---|---|
S1 | 1 | 3 ― ×断絶あり (1+1)≠3 |
1 | 4 | |
1 | 7 | |
1 | 8 | |
1 | 9 | |
1 | 12 | |
S2 | 3 | 4 ― ○断絶なし (3+1)=4 |
3 | 7 | |
3 | 8 | |
3 | 9 | |
3 | 12 | |
S3 | 4 | 7 ― ×断絶あり< (4+1)≠7 |
4 | 8 | |
4 | 9 | |
4 | 12 | |
S4 | 7 | 8 ― ○断絶なし (7+1)=8 |
7 | 9 | |
7 | 12 | |
S5 | 8 | 9 ― ○断絶なし (8+1)=9 |
8 | 12 | |
S6 | 9 | 12 ― ×断絶あり (9+1)≠12 |
リスト14 欠番のカタマリを表示する
SELECT (N1.num + 1) AS gap_start,
'~',
(MIN(N2.num) - 1) AS gap_end
FROM Numbers N1 INNER JOIN Numbers N2
ON N2.num > N1.num
GROUP BY N1.num
HAVING (N1.num + 1) < MIN(N2.num);
このS1~S6のうち,
あとは,
- 注5)
- 詳しくはgihyo.
jp連載 「SQLアタマ養成講座」 第5回~第7回を参照のこと。
おわりに
手続き型言語とSQLの考え方の相違点を浮き彫りにしつつも,
参考資料
- ・
J.セルコ 『SQLパズル 第2版』
(翔泳社, 2007, ISBN:978-4-7981-1413-2) - 「 パズル4 入館証」
と 「パズル56 ホテルの部屋番号」 では連番を更新する方法が, 「パズル9 席空いてますか?」 では欠番のカタマリを取り出す見事な解法を学ぶことができます。また, 非グループ化 (GROUP BYの逆演算) に連番テーブルを利用する斬新なトリックを利用する 「パズル48 非グループ化」 も見逃せません。 - ・
ミック 『達人に学ぶ SQL徹底指南書』
(翔泳社, 2008, ISBN:978-4-7981-1516-0) - 「1-4 HAVING句の力」
と 「1-10 帰ってきたHAVING句」 では, 自然数の集合とテーブルに含まれる数とに一対一対応がつくかどうかを調べることで欠番探索を行う方法を, また, 本稿でも取り上げたDigits集合から連番を生成するクエリについては 「1-9 SQLで数列を扱う」 で詳しく図解しています。 - ・
J.セルコ 『Joe Celko’ s Analytics And Olap in SQL』
(Morgan Kaufmann Pub, 2006, ISBN:978-0-1236-9512-3) - 連番を使ったメジアンの求め方は
「8. 1 OLAP Functionality8. 1」 で紹介されているアイデア。OLAPのツールという視点からSQLの使い方を解説したユニークでおもしろい本ですが, 邦訳はなし。
バックナンバー
SQLアタマアカデミー
- 最終回 OLAP関数で強力な統計処理を実現!―手続き型から理解するSQL (5)集合指向と手続き型
- 最終回 OLAP関数で強力な統計処理を実現!―手続き型から理解するSQL (4)OLAP関数と集約関数を組み合わせる
- 最終回 OLAP関数で強力な統計処理を実現!―手続き型から理解するSQL (3)OLAP専用関数
- 最終回 OLAP関数で強力な統計処理を実現!―手続き型から理解するSQL (2)OLAP関数の基本構文
- 最終回 OLAP関数で強力な統計処理を実現!―手続き型から理解するSQL (1)OLAP関数とは何か
- 第10回 結合大全 (5)非等値結合
- 第10回 結合大全 (4)自己結合
- 第10回 結合大全 (3)外部結合
- 第10回 結合大全 (2)内部結合
- 第10回 結合大全 (1)クロス結合