特集2

SQLアタマトビラ 08.10.16 15:51 ページ1

特集 2

分岐とループ、集合操作…
プログラミング言語とはここが違う！
■ミック http://www.geocities.jp/mickindex/
本特集では、SQL の基礎は一通り理解しており、日々の業務でもSQLを利用している。でも何となくSQLの考え方がしっくりこない…
そんな現場のエンジニアに向けて、SQL特有の原理を明らかにし、より一層SQLを使いこなしていく指針をまとめていきます。
全章をとおして、C言語やPerl、Javaといった手続き型言語、あるいは手続き型に基礎を持つ言語の考え方とSQLのそれを対比して、両者の共通点と相違点を描き出します。分岐とループという手続き型になくてはならない機能をSQLで実現するにはどうするのかという点を中心に、わかりやすく解説しています。

第1章

第2章

第3章

SQL流 条件分岐

SQL流 行間比較

SQL流 集合操作「文」から「式」へのパラダイムシフト

OLAP関数と自己結合があればループがなくても平気思考の基本単位は「レコード」ではなく「レコードの集合」

048

055

063

特集2-01̲4 08.10.16 15:56 ページ48

第1章「文」から「式」へのパラダイムシフト

SQL流
条件分岐
き型の考え方に親しんできたエンジニア（私たちのはじめにほとんどすべて、ということですが）は、この点に無自覚なままSQLを扱い、その力を十全に引き出す

私たちが通常、C言語やPerl、Javaなどの手続きことができないままフラストレーションを溜めます。

型言語（またそれに基礎を持つ言語）を使ってプロ

これは、私たちにとってもSQLにとっても不幸なこ

グラミングを行う場合、最も多用する基本的な制御とです。

構造が分岐とループです。この2つを使わずにプロ

そこで本章ではまず、SQLにおいて分岐を表現す

グラミングしろ、と言われたら、それはかなりきつる強力な道具である「CASE式」について学び、そい制約になるでしょう。腕試しや暇つぶしに試すにの使い方をマスターしていきたいと思います（ルーはおもしろいかもしれませんが、およそ実務的なプについては、次章で取り上げます）。

コーディングは不可能になるに違いありません。

なお、本特集で取り上げているSQLは、SQL92/

話は、SQLとデータベースの場合でも同じです。

99/2003準拠のもので、RDBMSはPostgreSQL 8.3、

SQLにおいても、やはり分岐とループは非常に重要

MySQL 5.0、Oracle 10g リリース2（10.2）、DB2な役割を果たす機能であり、SQLプログラミングの

9.1、SQL Server 2005を対象としています。また、際にこの2つの機能を欠かすことはできません。しOLAP関数は標準SQLの新しい機能であり、Postgre

かしながら、手続き型言語を使いこなすプログラマ

SQLとMySQLではまだサポートされていません。

の多くが、なぜかSQLを使う段になると思い通りの制御構造を記述できないことに苛立ちを感じ、結果、
非効率的なSQL文が多く生み出されています。これはなぜでしょう？

qウォーミングアップ
条件に応じて使う列を切り替える
CASE式は、SQLにおいて条件分岐を記述するた

SQLで分岐とループを表現すること自体は、何のめに導入された非常に重要な機能です。その名前が問題もなく可能なのです。通常の手続き型言語で表示すとおり「式」であるため、SQLの実行時には評現可能なアルゴリズムはすべてSQLでも表現可能な価されて単一のスカラ値注1 に定まるところに特徴がことが知られています。それなのに、なぜ多くのプあります。C言語やVBなどのCASE文と見た目が似ログラマやエンジニアがSQLに戸惑いを感じるかとているので同じような感覚で使われることがありま

いえば、その処理の基本単位の違いに無頓着なまますが（特に終端子の「END」が、一連の手続きの終だからです。

わりを示すように錯覚しやすい）、それではCASE式

手続き型言語が「文（statement）」を基本単位との強みを十分に引き出せません。

して分岐やループを記述するのに対し、SQLの基本

CASE式の特性を理解するために、ちょっとしたは「式（expression）」です。これがSQLが「宣言練習から始めましょう。表1のサンプルテーブルを的」と呼ばれる特徴の1つなのですが、長い間手続使います。

注1）文字列値、整数値などの単一値。

48●WEB +DB PRESS Vol.44

特集2-01̲4 08.10.16 15:56 ページ49「文」から「式」へのパラダイムシフト

SQL流 条件分岐
ChangeColsは年単位で何らかの数値を管理しているテーブルですが、具体的に数値が何を意味する

きますから、リスト2、図2のようにWHERE句で利用することも問題なく可能です。

かは今は気にしないでください。このテーブル自体

これは、先ほどのクエリで作ったnew̲colの値がに特におかしなところはないのですが、ある日、あ4以上の年度を選択するクエリです。右辺のCASE

なたのもとに妙な要件が持ち込まれてきます。それ

式は、一見すると値には見えませんが、ちゃんと実は、2000年まではcol̲1の値を使って、2001年から

行時には評価されて「10」や「3」などの単一の値はcol̲2の値を使って集計をしたい、というもので（＝スカラ値）になります。だからこそ、このようにす。求める結果は図1のような形です。

比較述語の引数に取ることも可能なのです。

いわば、行によって使う列を変えて1列にまとめw列の交換る、というイメージです。このテーブルがファイル順列と組み合わせで、手続き型言語で1行ずつ読み出すのであれば、各行のyear列の値によって条件を分岐させ、使う
フィールドを変える、ということになるでしょう。
SQLでもその考え方は変わりません。リスト1のように記述します。

2問目は、「列の切り替え」問題の発展版を練習してみましょう。
ある店舗で、売れた商品の明細を顧客ごとに記録する表2のようなテーブルがあるとします。列持ち条件を記述するWHEN句は手続き型言語と同じ形式のため、定義されている列数以上の商品が売れですが、CASE式に特徴的なのは、THENのあとのたときは記録できないという欠点を抱える設計です

実行部です。C言語などでは、ここで変数new̲colが、最初はその点には目をつぶります（あとでこのにcol̲1（またはcol̲2）の値を「代入」する文を記

欠点に対する解決策も示します）。

述しますが、SQLにおいてはcol̲1、col̲2を直接にさて、このテーブルを使って、一緒に売れた商品

戻り値としています。あたかも「2つで1つの列」ののペアを単位としていろいろな分析を行うことを考ような返し方をするわけです。

えます。その際、このテーブルのままだと不都合な

式は列や定数を記述できるところには常に記述でことが起きます。顧客IDが001と003の行を見れば●リスト1 列の切り替え（SELECT句で）●図1 求める結果

SELECT year,
CASE WHEN year <= 2000 THEN col_1
WHEN year >= 2001 THEN col_2
ELSE NULL END AS new_col
FROM CahngeCols;

year new_col----- ------1998 10← col_1
1999 20← col_1
2000 30← col_1
2001 4← col_2
2002 3← col_2●リスト2 WHERE句での利用●図2 リスト2の実行結果

SELECT year
FROM CahngeCols
WHERE 4 <= CASE WHEN year <= 2000 THEN col_1
WHEN year >= 2001 THEN col_2
ELSE NULL END;

year---1998
1999
2000
2001

●表2 Perm2テーブル●表1 ChangeColsテーブル
year
1998
1999
2000
2001
2002

col̲1
10
20
30
40
50

第1章

col̲2
7
6
5
4
3

cust̲id(顧客ID)

item̲1(商品1)

item̲2
(商品2)

001
002
003
004
005

時計
携帯電話
浄水器
携帯電話
インク

浄水器
携帯電話
時計
携帯電話
メガネ

WEB +DB PRESS Vol.44● 49

特集2-01̲4 08.10.16 15:56 ページ50

分岐とループ、集合操作…

養成講座

特集 2 プログラミング言語とはここが違う！
わかるように、この2人は、商品の組み合わせとしては {時計、浄水器} という同じペアを買っていま

CASE式を使えばこんなことも朝飯前です。リス
ト3のクエリを見てください（図3は実行結果）。

す。しかしおそらく、テーブルに記録されるときは

item̲1とitem̲2は文字列ですので、不等号で比較

買った順番で記録したためでしょう、テーブル上で可能な順序を持っています。ということは、最初のは順序を入れ替えた、異なるペアとして存在してい列「c1」に小さいほう、2番目の列「c2」に大きいます。これでは、単純に item̲1、item̲2 をほうを配置してやれば、同じ要素を持っていて順序「SELECT DISTINCT」で選択したり、GROUP BY

だけ異なる順序集合同士も、同じ並び順に配置し直句で集約したとしても、正しい商品のペアが求めらすことができるわけです。あとは、重複行を排除しれません。

てできあがり（リスト4、図4）。

そこで、表2のようなテーブルから、表3のようにこの方法は、比較したい列が文字型でも数値型で商品の並び順を無視した組み合わせを求めましょも日付型でも、とにかく順序づけられるデータ型なう。

らば適用できる汎用性の高い方法です。

言い方を変えると、順序を意識した「順序集合」
を、順序を無視した「非順序集合」へ変換する、ということです。あるいは、学校で習った馴染み深い表現を使うなら、順列（Permutation）を組み合わせ（Combination）へ変換するのです。

ではこの問題を一般化します。商品列を3列に増やした場合（表4）はどうなるでしょう。
やりたいことは同じなので、求めたい結果は表5のようなものになります。
この場合も、原理的には2列の場合と同じように、●リスト3 組み合わせ⇒順列（重複行排除前）●表3 商品の並び順を無視した組み合わせ

SELECTitem̲1(商品1)

item̲2
(商品2)

時計
携帯電話
インク

浄水器
携帯電話
メガネ

CASE WHEN item_1 < item_2 THEN item_1
ELSE item_2 END AS c1,
CASE WHEN item_1 < item_2 THEN item_2
ELSE item_1 END AS c2
FROM Perm2;●図3 リスト3の実行結果
c1
c2--------- --------時計
携帯電話
時計
携帯電話
インク

浄水器
携帯電話
浄水器
携帯電話
メガネ

●リスト4 組み合わせ⇒順列（重複行排除後）
SELECT DISTINCTCASE WHEN item_1 < item_2 THEN item_1
ELSE item_2 END AS c1,
CASE WHEN item_1 < item_2 THEN item_2
ELSE item_1 END AS c2
FROM Perm2;●表4 Perm3テーブル
cust̲id(顧客ID)

item̲1(商品1)

item̲2(商品2)

item̲3
(商品3)

001
002
003
004
005
006

時計
ティッシュ
カレンダー
カレンダー
文庫本
文庫本

浄水器
浄水器
ノート
ノート
ゲームソフト
メガネ

ティッシュ
時計
時計
インク
メガネ
ゲームソフト

●表5 求めたい結果●図4 リスト4の実行結果
c1
c2--------- --------インク
携帯電話
時計

メガネ
携帯電話
浄水器

50●WEB +DB PRESS Vol.44

item̲1(商品1)

item̲2(商品2)

item̲3
(商品3)

インク
カレンダー
ゲームソフト
ティッシュ

カレンダー
ノート
メガネ
時計

ノート
時計
文庫本
浄水器

特集2-01̲4 08.10.16 15:56 ページ51「文」から「式」へのパラダイムシフト

SQL流 条件分岐不等号の比較条件をCASE式で記述することができるのですが、条件がかなり複雑になりますし、4列、第1章する（MIN(CI2.item)）e最後に、その値も除いた集合から最小値（最後5列…と増えていった場合にはさらに厳しくなりまなので 1 つしか残っていないが）を選択する

す。ちょっと読むに堪えないクエリになるでしょう。

（MIN(CI3.item)）

こういう一般化したケースをなんとかしたいという相談が持ち込まれてきたとしたら、私ならリスト

これなら、商品数が何列に増えても対応できます

5、図5のように列持ちの形式を行持ちの形式に直す（ただし、この方法では、同じ商品の組み合わせは選方法を奨めます。

択できなくなります。これに対応する方法は、みないったんこの形式に直してしまえば、あとはリスさんも考えてみてください）。このことからテーブル

ト6のクエリで組み合わせへ変換できます（実行結設計においては、例外的なケースを除いて、なるべ

果は図6）
。

く列持ちよりは行持ちの形式を採用したほうが拡張

やはり不等号を使って商品ごとに順序づけを行的な要件にも耐える、という教訓が得られます。安う、という点はCASE式のときと同じですが、MIN

易に配列やフラットファイルをそのままテーブルの関数を併用することで比較条件を非常に簡潔なもの「列」に写し取る無芸な設計をしてしまうと、あとあに抑えることができるのです。

とまで大きな禍根を抱え込むことになるので、注意

このクエリの意味は、次のようなものです。
qまず1人の顧客について、3つの商品の中から最小値を選択する（MIN(CI1.item)）
w次に、その最小値を除いた集合から最小値を選択が必要です。

e表頭の複雑な集計
これはCASE式の使い方の中で最も使い勝手がよ●リスト5 列持ち⇒行持ち●リスト6 組み合わせ⇒順列（3列拡張版）

CREATE VIEW CustItems (cust_id, item) AS
SELECT cust_id, item_1
FROM Perm3
UNION ALL
SELECT cust_id, item_2
FROM Perm3
UNION ALL
SELECT cust_id, item_3
FROM Perm3;

SELECT DISTINCT MIN(CI1.item) AS c1,
MIN (CI2.item) AS c2,
MIN (CI3.item) AS c3
FROM CustItems CI1
INNER JOIN CustItems CI2
ON CI1.cust_id = CI2.cust_id
AND CI1.item < CI2.item
INNER JOIN CustItems CI3
ON CI2.cust_id = CI3.cust_id
AND CI2.item < CI3.item
GROUP BY CI1.cust_id;●図5 リスト5の実行結果
cust_id item-------- ----------001
浄水器
001
時計
001
ティッシュ
002
浄水器
002
ティッシュ
002
時計
003
ノート
003
カレンダー
003
時計
004
ノート
004
カレンダー
004
インク
005
ゲームソフト
005
文庫本
005
メガネ
006
文庫本
006
ゲームソフト006
メガネ●図6 リスト6の実行結果
c1
c2
c3------------- ---------- ------インク
カレンダー
ゲームソフトティッシュ

カレンダー ノート
ノート
時計
メガネ
文庫本
時計
浄水器

WEB +DB PRESS Vol.44● 51

特集2-01̲4 08.10.16 15:56 ページ52

分岐とループ、集合操作…

養成講座

特集 2 プログラミング言語とはここが違う！
きます。

く応用範囲の広い技術なので、ぜひマスターしてくq「ウォーミングアップ」でも見たように、CASE

ださい。表6のような、性別・年齢・部署別の給与式をSELECT句で使うことによって、条件に応じてを管理する人事テーブルがあるとします。
ここからいろいろな組み合わせのクロス表を作っ集計したい行を指定できます。すると、表頭の4列てみましょう。こういう要件は実務の中でも頻繁にをCASE式で作ることが可能になります（リスト7）。

発生すると思いますが、まずは表7のような表頭がCASE式の戻り値を0/1で指定しているのは、各

年齢階級・性別、表側が部署で、人数を集計した表行に対してビットフラグを立てていると思えばわかです。いま年齢階級は便宜的に30歳以下を「若手」、

りやすいでしょう。あとはこのフラグが1の行数を

それ以上を「ベテラン」という簡単な区分にしておSUM関数で数えることで、条件に合致した行数だ
けをカウントできるのです（このトリックは第3章でもう一度見ることになります）。

●表6 Employeesテーブル
emp̲iddeptsex
age
salary（社員ID） （部署） （性別） （年齢） （給与：万円）
001
002
003
004
005
006
007
008
009
010
011
012

製造
製造
製造
会計
会計
営業
営業
営業
営業
営業
人事
人事

男
男
女
男
男
女
女
男
男
女
男
女

32
30
23
45
50
40
42
52
34
41
29
36

では、表頭に小計・合計の列も追加して、表8の場合はどうでしょう。

30
29
19
35
45
50
40
38
28
25
25
29

これも、リスト7のクエリを簡単に修正するだけで対応できます（リスト8）。
●表7 表頭：年齢階級・性別、表側：部署
若手
男
1
0
0
1

女
1
0
0
0

ベテラン
男
1
2
2
0

ベテラン
計
1
2
5
1

男
1
2
2
0

女
0
0
3
1

製造
会計
営業
人事

●表8 表頭：年齢階級・性別 表側：部署（小計・合計あり）
合計
製造
会計
営業
人事

3
2
5
2

若手
計
2
0
0
1

男
1
0
0
1

女
1
0
0
0

●リスト7 表頭：部署・性別、表側：年齢階級
SELECT dept,
SUM(CASE WHEN age <= 30 AND sex = '男' THEN 1 ELSE 0 END) AS "若手（男）",SUM(CASE WHEN age <= 30 AND sex = '女' THEN 1 ELSE 0 END) AS "若手（女）",SUM(CASE WHEN age >= 31 AND sex = '男' THEN 1 ELSE 0 END) AS "ベテラン（男）",SUM(CASE WHEN age >= 31 AND sex = '女' THEN 1 ELSE 0 END) AS "ベテラン（女）"FROM Employees
GROUP BY dept;●リスト8 表頭：年齢階級・性別、表側：部署（小計・合計あり）
SELECT dept,
COUNT(*),
SUM(CASE WHEN age <= 30 THEN 1 ELSE 0 END) AS "若手（計）",SUM(CASE WHEN age <= 30 AND sex = '男' THEN 1 ELSE 0 END) AS "若手（男）",SUM(CASE WHEN age <= 30 AND sex = '女' THEN 1 ELSE 0 END) AS "若手（女）",SUM(CASE WHEN age >= 31 THEN 1 ELSE 0 END) AS "ベテラン（計）",SUM(CASE WHEN age >= 31 AND sex = '男' THEN 1 ELSE 0 END) AS "ベテラン（男）",SUM(CASE WHEN age >= 31 AND sex = '女' THEN 1 ELSE 0 END) AS "ベテラン（女）"FROM Employees
GROUP BY dept;

52●WEB +DB PRESS Vol.44

女
0
0
3
1

特集2-01̲4 08.10.16 15:56 ページ53「文」から「式」へのパラダイムシフト

SQL流 条件分岐

第1章●リスト9 全列をCOUNT関数で揃える
SELECT dept,
COUNT(*),
COUNT(CASE WHEN age <= 30 THEN 1 ELSE NULL END) AS "若手（計）",COUNT(CASE WHEN age <= 30 AND sex = '男' THEN 1 ELSE NULL END) AS "若手（男）",COUNT(CASE WHEN age <= 30 AND sex = '女' THEN 1 ELSE NULL END) AS "若手（女）",COUNT(CASE WHEN age >= 31 THEN 1 ELSE NULL END) AS "ベテラン（計）",COUNT(CASE WHEN age >= 31 AND sex = '男' THEN 1 ELSE NULL END) AS "ベテラン（男）",COUNT(CASE WHEN age >= 31 AND sex = '女' THEN 1 ELSE NULL END) AS "ベテラン（女）"FROM Employees
GROUP BY dept;

合計列のCOUNT(*)はSUM(1)としても同じです。
あるいはそう書いたほうが「無条件に行数をカウントしている」という意味が明確になるかもしれませんが、一般的にあまり見ない書式なので初めて見た人が驚くかもしれません。
また、リスト9のように、全列をCOUNT関数で揃えてもかまいません。その場合は、ELSE句で返すときにNULLを指定する必要があります。これは、
COUNT関数が集計の際にNULLを除外してからカ●リスト10 部署ごとの人数を選択する
SELECT dept,
COUNT(*) AS cnt
FROM Employees
GROUP BY dept;●図7 リスト10の実行結果
dept cnt----- ----人事 2
製造 3
会計 2
営業 5

ウントを取るという特性を利用しています（0を指定するとCOUNT関数はその行も数えてしまうのです）。
このように、どれだけ表頭が複雑でも、入れ子が深くても、CASE式を集約関数の中に埋め込むことで簡単に記述できます。今の例題では人数を集計し●リスト11 集約結果に対する条件設定：集約関数を引数にとる
SELECT dept,
CASE WHEN COUNT(*) <= 2 THEN '2 人以下'
ELSE '3 人以上' END AS cnt
FROM Employees
GROUP BY dept;ていましたが、もし給与の合計や平均を出したいな
らば、CASE式のTHEN句で返す値にsalary列を指●図8 リスト11の実行結果

定することで問題なく可能です。

dept
cnt
----- ------人事 2 人以下
製造 3 人以上
会計 2 人以下
営業 3 人以上

ちなみに、表側が複雑な表を作る場合は、今回見たような表頭の複雑な表を作るよりもはるかに難しくなります。表側が入れ子であったり、合計・小計も合わせて出力するには、少し工夫をこらさねばな
りません。これらの話については、また別の機会にず、問題eで使った人事テーブルから、部署ごとのすることにしましょう。

人数を選択する簡単なクエリから始めます（リスト10、図7）。

r集約関数の外でCASE式を使う

によっては、「2人以下の部署と、それ以上の部署を問題eでは、集約関数の中にCASE式を記述する

別々のグループに分けたい」ということも生じるでことで、集約する対象を柔軟に使い分けるという技

しょう。いわば、集約した結果に対する、さらに一術を解説しました。今度は、ある意味でその反対を段上の分岐を記述したい、ということです。

してみましょう。すなわち、すでに集約された結果をCASE式の引数にとって分岐させます。
どういうことか、具体的に見ていきましょう。まこれだけならごく単純な話なのですが、業務要件

凄いことに、CASE式はこういうケースにも適用できるのです。今度は引数にCOUNT関数を取ります（リスト11、図8）。

WEB +DB PRESS Vol.44● 53

特集2-01̲4 08.10.16 15:56 ページ54

分岐とループ、集合操作…

養成講座

特集 2 プログラミング言語とはここが違う！
最初にこのクエリを見たとき、少し違和感を持つ¡CASE式は、見た目が「文」に見えがちだが、実人もいるでしょう。それは、WHERE句で同じよう際は名前が示すとおり「式」であり、文法的にはに集約関数に条件を記述しようとしてエラーになる、

通常の列や定数、あるいは「1 + 1」のような式をというSQL初心者がよく犯してしまう間違いに原因記述しているのと変わらないがあるのでしょう。しかし落ち着いて考えれば、SELECT句では集約関数はすでに1つの定数に定¡それゆえ、SQLのほとんどどんなところにでも記述できる汎用性の高さが最大の魅力

まっています。だからこそ、結果は1行につき1つの値で返されています。ということは、CASE式の引数としても、1つの定数として与えられるわけですから、構文的に問題は何1つないわけです。
もっとも、このような結果の見た目を整形する処
理は、本当はSQLでやるべきことではありません。

SQLにおける分岐についてさらに深く学びたい方
は、以下の参考文献を参照してください。
『プログラマのためのSQL 第2版』
（J.セルコ 著、
ピアソン・エデュケーション、2001）

ホスト言語の表示用の機能において実現すればよい

セルコは、米国データベース界の重鎮の一人で、ことです。というのも、SQLは本来、検索のために特に高度なSQLプログラミング技術の解説に手腕の作られた言語であるため、表示用の整形機能はそれ冴えを見せます。

ほど充実していないからです。この例題で見たよう

本書は、中級SQLプログラミングをマスターする

な簡単な整形であればまだ問題はないのですが、きために必要な知識がすべて網羅された決定版の教科

め細かい表示設定を行う必要が出てきたときには、書です。ただし、お世辞にもあまり読みやすくはあ

SQLでは対応しきれなくなるでしょう。そのことも、りません。
「7.1 CASE式」は、CASE式についての頭の隅には入れておいてください。

概念的な基礎から実際の応用例まで広くカバーする
必読のテキストです。セルコもこのCASE式についまとめては「SQLにとって最重要の機能である」という破格の評価を与えています。

手続き型言語においてIF文やCASE文といった条
件分岐の機能が必須であるのと同様に、SQLにおいてもCASE式は生命線の1つです。したがって、DB『達人に学ぶ SQL徹底指南書』（ミック 著、翔泳社、2008）

エンジニアはこの機能の使い方について熟知していセルコの本は敷居が高くて…という方には、手前なければなりません。またそれだけに、CASE式を味噌ですがこちらの拙著をお薦めします。脱初級〜

使いこなせるようになることによって、SQLプログ

中級入門のレベルを噛み砕いて解説しています。

ラミングでできることの幅がぐっと広がり、データ

私は、SQLを教える際は、必ずCASE式の話を最ベースの世界が開けていきます。この爽快感を経験

初に配置するようにしています。本書も、その例にしてもらうことが、実は本章の一番の目的です。

漏れず、筆頭に持ってきています（「1-1. CASE式の◇ ◇ ◇ススメ」参照）。WHEN句の中でEXISTSなどの述

それでは、主なポイントをまとめておきましょう。
¡手続き型言語で表現可能なアルゴリズムは、SQLにおいても表現可能である¡その際に基本的な重要性を持つ道具が、分岐とループ¡SQLにおける分岐は、CASE式を使って表現する。
「文パラダイム」から「式パラダイム」への飛躍が理解の重要な鍵

54●WEB +DB PRESS Vol.44

語と組み合わせたり、入れ子のCASE式を使うなど、本章で紹介しきれなかった重要なテクニックも紹介しています。#

特集2-02̲4 08.10.16 15:56 ページ55

第2章
OLAP関数と自己結合があればループがなくても平気

SQL流
行間比較
クエリ）を使う方法の2通りを紹介します。この両はじめに者を学ぶことには、SQLに存在する手続き型的な側面と集合指向的な側面を一挙に学ぶことができるとデータベースを利用する目的の1つとして、過去いうメリットがあります。

の情報を時系列順に、文字通り「データの集積」と

まあ、そうしたことは、本章を読み進む過程でして保持する用途があります。そうした情報は、た追々わかってくるでしょう。今は、頭の片隅にメモ

とえばシステムのパフォーマンスログや会社の財務程度に覚えておいていただければ結構です。それで状況、あるいは集団の人口推移だったりするでしょは、早速始めましょう。

さかのぼう。多くの業務では、こうした過去のデータへ遡っqまずは基本て現在と比較する、あるいは過去の2点や期間同士直近を求めるを比較して将来の指針を策定する手がかりとすることが重要になります。いわゆる「データウェアハウ
ス」と総称される用途です。

まずは基本的な時系列分析から始めましょう。時
系列にデータを比較する場合、基本となるのは、時

リレーショナルデータベースとSQLにおいては、系列に従って、1行ずつ過去へ遡る、または未来へ

そうした時系列的なデータの多くは、「時間」を表す

進むSQLです。サンプルに、表1のようなサーバの列（時刻、日付、年度などなど）をキーとして、異時間ごとの負荷量を記録したテーブルを使います。

なる時刻のデータは異なる行として保持するのが一

サンプリングは思いついたときに不定期に行われる般的なテーブル設計です。これを「列持ち」の形でため、不連続で間隔もランダムな日付が格納されて保持することは、例外的なケースに属します 注1。

います。

そうすると当然、ある2点の時刻におけるデータ

まずは、各行について過去の直近の行を求めてみを比較するためには行間比較が必要となります。そましょう。OLAP関数を使える実装ならば、リストのために必要な記述方法の基礎を学ぶことが本章の1のような簡潔な書き方で実現できます（実行結果目的です。手続き型言語でファイルを扱う場合には、こういう行間比較のためには「ループ」による処理が基本となりますが、SQLには文単位のループは一切現れません。代わりにSQLは、独自の原理に基づいた方法を用います。
具体的には、標準SQLの新しい機能であるOLAP
関数を利用する方法と、従来の自己結合（相関サブ●表1 LoadSampleテーブル
sample̲date
(計測日)

load
(負荷量)

2008-02-01
2008-02-02
2008-02-05
2008-02-07
2008-02-08
2008-02-12

1024
2366
2366
985
780
1000

注1）もし列持ちの形式を取るとすれば、固定的な結果を出力するためのテーブルとして使用する場合でしょう。しかし、列数を拡張することはコストがかさむため、これはあくまで例外的なケースですし、また本章で見るように、行持ちから列持ちの形式へ変換することが可能なため、基本的には行持ちの形式をとるべきです。

WEB +DB PRESS Vol.44● 55

特集2-02̲4 08.10.16 15:56 ページ56

分岐とループ、集合操作…

養成講座

特集 2 プログラミング言語とはここが違う！
●リスト1 過去の直近を求める（OLAP関数：現在のところ実装依存）●図1 リスト1の実行結果

SELECT sample_date AS cur_date,
MIN(sample_date)
OVER (ORDER BY sample_date ASC
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS latest
FROM LoadSample;

cur_date latest-------- -------08-02-01
08-02-02 08-02-01
08-02-05 08-02-02
08-02-07 08-02-05
08-02-08 08-02-07
08-02-12 08-02-08●リスト2 直近（相関サブクエリ：実装非依存）
SELECT LS0.sample_date AS cur_date,(SELECT MAX(sample_date)
FROM LoadSample LS1
WHERE LS1.sample_date < LS0.sample_date) AS latest
FROM LoadSample LS0;は図1）注2。

ト行（LS0）のsample̲dateより小さい日付の集合

2月1日より前のデータはこのテーブルには登録（LS1）から、最大の日付を選択する、ということでされていないので、2月1日の行については直前のす。このクエリの意味がわかりづらいなら、相関サ

日付はNULLです。2月2日以降についてはそれぞ

ブクエリを自己結合に変えたものを見るとわかりやれ直前の日付が存在するので、これがlatest列に入すくなるでしょう（リスト3）。

ることになります。このクエリのポイントは、

この自己結合の場合も、考え方は相関サブクエリ「BETWEEN 1 PRECEDING AND 1 PRECEDING」の場合とまったく同じです。試しに、集約抜きにしによって、OLAP 関数が動く範囲をあくまでてヒラで結果を得てみましょう（リスト4、図2）。

sample̲dateでソートした場合の直前の1行に限定

このように、カレントの日付であるcur̲dateに対していることです。普通、「BETWEEN」というのして、それぞれより小さい日付の集合が得られるこは、複数行の範囲を指定するために使う場合が多いとが確認できます。あとは、各集合の中からMAXのですが、ここはあえて範囲を1行に限定するため関数で最大の日付（図2中の太字の箇所です）を選に利用しています。

択すればOK。こういうある集合の中で、基準値よこれを実装非依存のクエリにするには、OLAP関数の部分を相関サブクエリに書き換えます（リスト2）。
これも結果はリスト1と同じです。ポイントは
WHERE句 の 「 LS1.sample̲date < LS0.sample̲
date」という非等値結合です。この意味は、カレン

り小さい最大の要素のことを、集合論では最大下界●図2 リスト4の実行結果
cur_date latest-------- -------08-02-01
08-02-02 08-02-01●リスト3 直近（自己結合：実装非依存）← S0 ： 2 月 1 日より小さい日付は
1 つもないので、0 個← S1 ： 2 月 2 日より小さい日付は 1 個

08-02-05 08-02-01
08-02-05 08-02-02← S2 ： 2 月 5 日より小さい日付は 2 個

08-02-07 08-02-01
08-02-07 08-02-02
08-02-07 08-02-05← S3 ： 2 月 7 日より小さい日付は 3 個← S4 ： 2 月 8 日より小さい日付は 4 個●リスト4 説明用：非集約の状態で取ると

08-02-08 08-02-01
08-02-08 08-02-02
08-02-08 08-02-05
08-02-08 08-02-07

SELECT LS0.sample_date AS cur_date,
LS1.sample_date AS latest
FROM LoadSample LS0
LEFT OUTER JOIN LoadSample LS1
ON LS1.sample_date < LS0.sample_date;

08-02-12 08-02-01
08-02-12 08-02-02
08-02-12 08-02-05
08-02-12 08-02-07
08-02-12 08-02-08← S5 ： 2 月 12 日より小さい日付は 5 個

SELECT LS0.sample_date AS cur_date,
MAX(LS1.sample_date) AS latest
FROM LoadSample LS0
LEFT OUTER JOIN LoadSample LS1
ON LS1.sample_date < LS0.sample_date
GROUP BY LS0.sample_date;

注2）Oracleには、これをもっと簡略化したLAGという関数があります（未来へ進むならLEAD関数）。独自拡張の関数のためここでは紹介しませんが、Oracleユーザの方は使い方を試してみてください。

56●WEB +DB PRESS Vol.44

特集2-02̲4 08.10.16 15:56 ページ57

OLAP関数と自己結合があればループがなくても平気SQL流 行間比較

第2章●図3 最大下界を得るためのノイマン型再帰集合
S:LoadSample

S5

S4

S3

S2

S1

S0

2008-02-01
2008-02-02
2008-02-05
2008-02-07
2008-02-08

2008-02-01
2008-02-02
2008-02-05
2008-02-07

2008-02-01
2008-02-02
2008-02-05

2008-02-01
2008-02-02

2008-02-01

φ（greatest lower bound）と呼んでいます 注3。過去の同心円的な集合は、数学的にはフォン・ノイマンに直近の日付を求めるとは、つまり最大下界を求める

よる再帰集合の構造にそのアイデアの源泉が求めらことと同義なのです。

れます 注5。

S0〜S5の部分集合は、図3からも明らかなように、

現在の日付の処理量と、直近の日付の処理量も併せて表示したいなら、リスト5のようにcur̲load̲
amtとlatest̲load̲amtの2列を追加すればよいで

S5 ⊃ S4 ⊃ S3 ⊃ S2 ⊃ S1 ⊃ S0

しょう（実行結果は図4）。

という包含関係のある部分集合群です 注4。こういうただし注意が必要なのは、latest̲load̲amtを求め●リスト5 処理量も併せて表示する（執筆時点ではPostgreSQLでのみ動作）
SELECT LS0.sample_date AS cur_date,
MAX(LS0.load_amt) AS cur_load_amt,
MAX(LS1.sample_date) AS latest,(SELECT MAX(load_amt)
FROM LoadSample
WHERE sample_date = MAX(LS1.sample_date)) AS latest_load_amt
FROM LoadSample LS0
LEFT OUTER JOIN LoadSample LS1
ON LS1.sample_date < LS0.sample_date
GROUP BY LS0.sample_date;●図4 リスト5の実行結果（処理量も併せて表示）
cur_date cur_load_amt---------- --------2008-02-01
1024
2008-02-02
2366
2008-02-05
2366
2008-02-07
985
2008-02-08
780
2008-02-12
1000

latestlatest_load_amt---------- ----------2008-02-01
2008-02-02
2008-02-05
2008-02-07
2008-02-08

1024
2366
2366
985
780

注3）反対概念はもちろん「最小上界」（least upper bound）です。非等値結合の不等号の向きを逆にして、MIN関数を使うことで取得できます。練習として試してみてください。
注4）「⊃」は集合の包含関係を表す記号で、たとえば「哺乳類⊃人類」のように使います。
今、S0〜S5は、大きな数字の集合が小さな数字の集合をすっぽり含む構造になっています（S0は空集合ですが、空集合は定義上すべての集合に含まれるとされるので、やはりS1〜S5のすべてに含まれます）。
注5）この再帰集合の理論的背景については、『指南書』「1-2 自己結合の使い方」および「2-7 SQLと再帰集合」で詳細に取り上げたので、そちらも参照のほど。

WEB +DB PRESS Vol.44● 57

特集2-02̲4 08.10.16 15:56 ページ58

分岐とループ、集合操作…

養成講座

特集 2 プログラミング言語とはここが違う！
るサブクエリ内のWHERE句でMAX関数を使ってして、そこから順次過去へ遡った日付を求める方法

いることです。LS1.sample̲dateは外側のGROUP

を考えます。とりあえず3つ前まで求めるとすると、

BY句によって集約されているため、SELECT句で

結果は図5のようにピラミッド型（というか階段型

は集約関数を適用する形でしか参照することができというか）になるでしょう。

のっとないのです。したがってこれは標準SQLに則った妥当なSQLなのですが、現在ではPostgreSQLでしかまずはOLAP関数を使うならば、リスト7のようになります。

正しく動作しません注6。

BETWEENによる行の指定先を「1行前」、「2行

そこで代替案として、リスト6のように最大下界前」、「3行前」…と変更するだけなので、非常に簡を求めるロジックをWHERE句に移す方法が考えら単に済むのがよいところです。後は何行前でも同じれます。

やり方で拡張できますし、「行の順序」という手続き

これも結果は先ほどと同じになります

注7。

w直近、直近の1つ前、
そのまた1つ前…

型の発想に基づく方法なので、多くのプログラマにとっても理解しやすいでしょう。
一方、OLAP関数を持たない実装でも動作する、
より一般的な解法は、集合指向に基づいて考える必これで1つ前の日付を求めることはできるように要があります。方法は、基本的に先ほどの直近の場なりました。ですが実務では、もう少し比較の範囲

合と同じで、時刻をずらした集合を複数用意して自を広げて、「直近の前」の日付や「直近の前のその己結合を使います。

また前」の日付、欲を言えば、一般的に「n個前の答えはリスト8のようになります。

日付」と比較したい、という要望も生じることでクエリの中に使いたいだけ集合を追加できるのがしょう。つまり、行間比較の一般化です。
この要望に応えるため、まずはある日付を起点と集合指向という考え方の柔軟なところです（その分、パフォーマンスを圧迫するのでやり過ぎには注意が必要ですが）。

●リスト6 最大下界を求めるロジックをWHERE句に記述
SELECT LS0.sample_date AS cur_date,
LS0.load_amt
AS cur_load,
LS1.sample_date AS latest,
LS1.load_amt
AS latest_load
FROM LoadSample LS0
LEFT OUTER JOIN LoadSample LS1
ON LS1.sample_date = (SELECT MAX(sample_date)
FROM LoadSample
WHERE sample_date < LS0.sample_date);●リスト7 3つ前の日付まで出力する：OLAP関数の利用
SELECT sample_date AS cur_date,
MIN(sample_date)
OVER (ORDER BY sample_date ASC
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS latest_1,
MIN(sample_date)
OVER (ORDER BY sample_date ASC
ROWS BETWEEN 2 PRECEDING AND 2 PRECEDING) AS latest_2,
MIN(sample_date)
OVER (ORDER BY sample_date ASC
ROWS BETWEEN 3 PRECEDING AND 3 PRECEDING) AS latest_3
FROM LoadSample;

S0が「今日」、S1が「その1つ前」、
S2が「さらにそのもう1つ前」を表します。確かに2月5日の前の日付として、2月2日と2月1日が選択できています。外部結合を使っているのは、繰
り返しになりますが、「前の日付」がテーブル上に存在しない日付について
も結果から落とさずに一覧表示するた●図5 想定される実行結果
cur_date latest_1 latest_2 latest_3-------- -------- -------- -------08-02-01
08-02-02 08-02-01
08-02-05 08-02-02 08-02-01
08-02-07 08-02-05 08-02-02 08-02-01
08-02-08 08-02-07 08-02-05 08-02-02
08-02-12 08-02-08 08-02-07 08-02-05

注6）この一見すると構文違反に見える（でも適法な）SQLの論理的な背景については、『指南書』の「2-10 SQLにおける存在の階層」
を参照。
注7）Oracle 10gでは、外部結合の条件にサブクエリを指定できないという制限があるため、このクエリは動作しません。その場合は内部結合に変える必要がありますが、そうすると今度は現在の日付が「2008-02-01」の行が結果に現れないという不便が生じます。
悩ましいものです。ほかの実装にはこういう不都合はありません。

58●WEB +DB PRESS Vol.44

特集2-02̲4 08.10.16 15:56 ページ59

OLAP関数と自己結合があればループがなくても平気SQL流 行間比較
めのトリックです。反対に、そういう行を結果に含リスト10のようなクエリで可能です（実行結果は図める必要がないなら、単純な内部結合を使えばOK

6）
。

第2章

OLAP版、自己結合版ともに、「未来の直近」を求です。結果がどのように異なるか、自分で上のクエめる関数をMAXからMINに変えているのがおわか

リを書き換えて確かめてみてください。
そうしたら、後は同じ要領で集合Snと結合条件をりいただけるでしょう。たとえばカレントの日付が2追加していけば、どれだけ過去にでも遡れます。無月5日だとすると、集合論的な見方をすれば、これ論、過去だけでなく未来へも自由に動けます。たとは集合を上下2つに分割する境界線（bound）を与えば、2月5日の前後の直近を求めるなら、リスト9、

える役割を果たしているのです（図7）。

●リスト8 3つ前の日付まで出力する：自己結合の利用
SELECT LS0.sample_date AS sample_date,
MAX(LS1.sample_date) AS latest_1,
MAX(LS2.sample_date) AS latest_2,
MAX(LS3.sample_date) AS latest_3
FROM LoadSample LS0
LEFT OUTER JOIN LoadSample LS1
ON LS1.sample_date < LS0.sample_date
LEFT OUTER JOIN LoadSample LS2
ON LS2.sample_date < LS1.sample_date
LEFT OUTER JOIN LoadSample LS3
ON LS3.sample_date < LS2.sample_date
GROUP BY LS0.sample_date;●リスト9 現在と過去の直近を求める：OLAP版
SELECT MIN(sample_date)
OVER (ORDER BY sample_date ASC
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS past,sample_date AS cur_date,MAX(sample_date)
OVER (ORDER BY sample_date DESC
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS future
FROM LoadSample;●リスト10 現在と過去の直近を求める：自己結合版●図6 リスト9、10の実行結果

SELECT MAX(LS1.sample_date) AS past,
LS.sample_date AS sample_date,
MIN(LS2.sample_date) AS future
FROM LoadSample LS
LEFT OUTER JOIN LoadSample LS1
ON LS1.sample_date < LS.sample_date
LEFT OUTER JOIN LoadSample LS2
ON LS2.sample_date > LS.sample_date
GROUP BY LS.sample_date;past
cur_date future-------- -------- -------08-02-01 08-02-02
08-02-01 08-02-02 08-02-05
08-02-02 08-02-05 08-02-07
08-02-05 08-02-07 08-02-08
08-02-07 08-02-08 08-02-12
08-02-08 08-02-12●図7 リスト9、10で行っていること
LS:LoadSample

2008-02-05
MIN(LS2.sample̲date)

LS2:上位集合

LS1:下位集合

2008-02-07
2008-02-08
2008-02-12

2008-02-01
2008-02-02

MAX(LS1.sample̲date)

WEB +DB PRESS Vol.44● 59

特集2-02̲4 08.10.16 15:56 ページ60

分岐とループ、集合操作…

養成講座

特集 2 プログラミング言語とはここが違う！
2月5日の前後の日付を求めるということは、上位化したテーブルをサンプルに使いましょう（表2）。

集合の最小元（＝最小上界）と下位集合の最大元

今回は、日付の列だけを見た場合には、テーブル（最大下界）を求めることと同義です。もちろん、範内で一意になっていません。こういうケースでは、

囲は上下にいくらでも広げられます。こう考えてみ

日付を時系列に並べる際の基準としても、

ると、順序の概念を使うOLAP関数の手続き型発想と、集合の概念を使う集合指向の考え方も、使う道具立てが異なるだけで、ロジックの構造として帰す

1． あくまでテーブル全体で日付を並べる
2． マシンごとに集合を区切り、その中で日付を並べるるところは1つであることがわかるのではないでしょうか。

という2通りが考えられます。これに対応するよう、

このように、与えられた集合（テーブル）をカット

w「直近、直近の1つ前、そのまた1つ前…」のクして新たな部分集合を作り、それを操作することにエリを拡張するのが今回の目的です。まず1番目のよってさまざまな演算を自在にこなせるようになる方針に従って過去の日付を順に求めるなら、結果はと、SQLの使い方が一段レベルアップします。このwの場合と同じく図8のようになるでしょう。

「集合のカット」という技術については、次章でさらに別の観点から詳しく見ることになります。
さて、それでは最後に、応用問題へ進みましょう。

wのクエリをそのまま適用するだけでは、上の結果は得られません。論より証拠、実験してみましょう（リスト11、図9）。
そう、結果はそのままテーブルの12行が現れてしe小分けにしたグループ内での行間比較

まいます。OLAP関数そのものに集約機能はないのだから、これは当然と言えば当然の結果です。といこれまで使っていたLoadSampleテーブルは、あうことは、裏を返せば、正しい結果を得るには、事る特定のマシンに限定した内容になっていました。

前に日付で一意になるような中間テーブルを作って今度はこれにマシンを管理する列を追加して、一般おけばよい、ということになります。したがって、
正しいクエリはリスト12のようになります。

●表2 LoadSample2テーブル
machine(マシン名)

sample̲date
(計測日)

load
(負荷量)

PC1
PC1
PC1
PC1
PC1
PC1
PC2
PC2
PC2
PC2
PC3
PC3

2008-02-01
2008-02-02
2008-02-05
2008-02-07
2008-02-08
2008-02-12
2008-02-01
2008-02-02
2008-02-05
2008-02-07
2008-02-01
2008-02-02

1024
2366
2366
985
780
1000
999
50
328
913
2000
1000

ポイントは最後のGROUP BY句にあります。SQLはGROUP BY句で指定したキーによって一意になる集合を作ります。ここでは、sample̲dateを指定しているので、これによって元のテーブルでは重複値の存在したsample̲date列を一意にすることができるわけです。
なお、SELECT句のOLAP関数には、wのクエリ
から手を加える必要はありません。これは、SELECT句がGROUP BY句より後に実行されるからです。GROUP BY句でwと同じ中間テーブルを●リスト11 wのクエリをそのまま実行してみる：うまくいかない●図8 1．の実行結果

SELECT sample_date AS cur_date,
MIN(sample_date)
OVER (ORDER BY sample_date ASC
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS latest_1,
MIN(sample_date)
OVER (ORDER BY sample_date ASC
ROWS BETWEEN 2 PRECEDING AND 2 PRECEDING) AS latest_2,
MIN(sample_date)
OVER (ORDER BY sample_date ASC
ROWS BETWEEN 3 PRECEDING AND 3 PRECEDING) AS latest_3
FROM LoadSample2;

cur_date
latest_1 latest_2 latest_3----------- -------- -------- -------08-02-01
08-02-02
08-02-01
08-02-05
08-02-02 08-02-01
08-02-07
08-02-05 08-02-02 08-02-01
08-02-08
08-02-07 08-02-05 08-02-02
08-02-12
08-02-08 08-02-07 08-02-05

60●WEB +DB PRESS Vol.44

特集2-02̲4 08.10.16 15:56 ページ61

OLAP関数と自己結合があればループがなくても平気SQL流 行間比較●リスト12 OLAP版（machine列なし）●図9 リスト11の実行結果

SELECT sample_date,
MIN(sample_date)
OVER (ORDER BY sample_date ASC
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS latest_1,
MIN(sample_date)
OVER (ORDER BY sample_date ASC
ROWS BETWEEN 2 PRECEDING AND 2 PRECEDING) AS latest_2,
MIN(sample_date)
OVER (ORDER BY sample_date ASC
ROWS BETWEEN 3 PRECEDING AND 3 PRECEDING) AS latest_3
FROM LoadSample2
GROUP BY sample_date;cur_date latest_1 latest_2 latest_3-------- -------- -------- -------08-02-01
08-02-01 08-02-01
08-02-01 08-02-01 08-02-01
08-02-02 08-02-01 08-02-01 08-02-01
08-02-02 08-02-02 08-02-01 08-02-01
08-02-02 08-02-02 08-02-02 08-02-01
08-02-05 08-02-02 08-02-02 08-02-02
08-02-05 08-02-05 08-02-02 08-02-02
08-02-07 08-02-05 08-02-05 08-02-02
08-02-07 08-02-07 08-02-05 08-02-05
08-02-08 08-02-07 08-02-07 08-02-05
08-02-12 08-02-08 08-02-07 08-02-07

用意できれば、後の演算には影響が及ばない、といPC3には、2月12日が現れないことに注目してくだう仕掛けです。

さい。ほかにも、PC3には2月5日や2月8日も結果

実装非依存の自己結合バージョンも同様の考え方でいけます。やはりGROUP BY句を追加します（リスト13）。

に現れません。これはもとのテーブルの情報からも妥当なことです。
こうした元のテーブルを重複しない部分集合（「類」

さて、それでは次に、2番目の方法、つまりマシと呼びます）に分割する方法を、SQLはちゃんと

ン列ごとに集合を区切って計測日を並べる場合を考持っています。それが、次章で詳しく取り上げるこ

えましょう。今度は、求める結果は図10のようになとになるGROUP BYとPARTITION BY句です。答ります。

えはリスト14、リスト15のようになります。

もともと2月12日に計測を行っていないPC2や第2章

OLAP版 も 自 己 結 合 版 も 、 GROUP BYと●リスト13 自己結合版（machine列なし）●図10 2．の実行結果

SELECT LS0.sample_date AS cur_date,
MAX(LS1.sample_date) AS latest_1,
MAX(LS2.sample_date) AS latest_2,
MAX(LS3.sample_date) AS latest_3
FROM LoadSample2 LS0
LEFT OUTER JOIN LoadSample2 LS1
ON LS1.sample_date < LS0.sample_date
LEFT OUTER JOIN LoadSample2 LS2ON LS2.sample_date < LS1.sample_date
LEFT OUTER JOIN LoadSample2 LS3
ON LS3.sample_date < LS2.sample_date
GROUP BY LS0.sample_date;machine cur_date latest_1 latest_2 latest_3------ -------- -------- -------- -------PC1
08-02-01
PC1
08-02-02 08-02-01
PC1
08-02-05 08-02-02 08-02-01
PC1
08-02-07 08-02-05 08-02-02 08-02-01
PC1
08-02-08 08-02-07 08-02-05 08-02-02
PC1
08-02-12 08-02-08 08-02-07 08-02-05
PC2
08-02-01
PC2
08-02-02 08-02-01
PC2
08-02-05 08-02-02 08-02-01
PC2
08-02-07 08-02-05 08-02-02 08-02-01
PC3
08-02-01
PC3
08-02-02 08-02-01●リスト14 OLAP版（machine列あり）
SELECT machine,sample_date AS cur_date,
MIN(sample_date)
OVER (PARTITION BY machine ORDER BY sample_date ASC
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS latest_1,
MIN(sample_date)
OVER (PARTITION BY machine ORDER BY sample_date ASC
ROWS BETWEEN 2 PRECEDING AND 2 PRECEDING) AS latest_2,
MIN(sample_date)
OVER (PARTITION BY machine ORDER BY sample_date ASC
ROWS BETWEEN 3 PRECEDING AND 3 PRECEDING) AS latest_3
FROM LoadSample2
GROUP BY machine, sample_date;

WEB +DB PRESS Vol.44● 61特集2-02̲4 08.10.16 15:56 ページ62

分岐とループ、集合操作…

養成講座

特集 2 プログラミング言語とはここが違う！
●リスト15 自己結合版（machine列あり）
SELECT LS0.machine AS machine,
LS0.sample_date AS sample_date,
MAX(LS1.sample_date) AS latest_1,
MAX(LS2.sample_date) AS latest_2,
MAX(LS3.sample_date) AS latest_3
FROM LoadSample2 LS0
LEFT OUTER JOIN LoadSample2 LS1
ON LS1.sample_date < LS0.sample_date
LEFT OUTER JOIN LoadSample2 LS2ON LS2.sample_date < LS1.sample_date
LEFT OUTER JOIN LoadSample2 LS3
ON LS3.sample_date < LS2.sample_date
GROUP BY LS0.machine, LS0.sample_date;

PARTITION BYのキーとしてmachine列を使っています。これによって、日付探索の範囲を同一マシン内に制限できるわけです。
OLAP版 の ク エ リ で は 、 PARTITION BYと¡SQLで行間比較を行う方法には次の2つがある−1つは手続き型の「順序」の概念を前面に押し出したOLAP関数。簡潔に記述できるが、まだ実装に依存する

GROUP BYの両方にmachine列を追加する必要が−もう1つは、伝統的な集合指向に基づく自己結あることに注意してください。PARTITION BY句に合。実装非依存だが、パフォーマンスに注意が追加し忘れると、動作はしますが結果が正しくなり必要ません。GROUP BY句に追加し忘れると、構文エラーとなります。それぞれ、なぜ正しく動作しない
か、理由を考えてみてください。これがわかれば本行間比較についてさらに学びたい方は、以下の参
考文献を参照してください。

章の理解は十分です。
『SQLパズル 第2版』
（J.セルコ 著、翔泳社、2007）

まとめ「過去の直近の日付」という形で最大下界を求める
本章ではSQLを利用した行間比較の方法を、いろ
いろなパターンに応じて解説してきました。
ここまで読んでいただいた方には、章の冒頭で述のが「パズル 30

買 い 物 の 平 均 サ イ ク ル 」、

DELETE文とUPDATE文で行間比較を利用する問題が「パズル2

欠勤」と「パズル38

記録の更べた「行間比較を学ぶことで、SQLに存在する手続

新」。そして相関サブクエリで行同士を比較する高き型的な側面と集合指向的な側面を一挙に学ぶこと

度な応用問題が「パズル70 株価の動向」。いずれができる」という言葉の意味も理解してもらえたのもSQLにおける行間比較をマスターするのに格好のではないでしょうか。手続き型の考え方に基礎を置練習問題です。

くOLAP関数と、伝統的な集合指向による自己結合。
熟練したDBエンジニアには、この両者をともに使
いこなし、状況に応じて使い分けることが要請されます。きっちり理解しておいてください。
それでは、本章の要点です。

『達人に学ぶ SQL徹底指南書』（ミック、翔泳社、
2008）「1-6 相関サブクエリで行と行を比較する」では、
主に相関サブクエリと自己結合を使った行間比較を取り上げました。移動平均や移動累計のような「集¡一般的なテーブル設計では、時系列的なデータは普通、行持ちの形式を取る
したがって、時間をまたいだ比較を行うことは、
行をまたいだ比較（＝行間比較）を必要とする

62●WEB +DB PRESS Vol.44

合をずらして重ね合う」考え方などは、本章の発展版としておもしろいでしょう。#

特集2-03̲5 08.10.16 15:57 ページ63

第3章
思考の基本単位は「レコード」
ではなく「レコードの集合」

SQL流
集合操作
¡COUNT

はじめに

¡SUM
¡AVG

SQLという言語の大きな特徴として、処理をレ¡MAX

コード単位ではなく、レコードの「集合」単位でひ¡MINとまとめにして記述するというものが挙げられます。
具体的には、GROUP BY句とHAVING句、それにたぶん、みなさんにとってもお馴染みの関数ばか伴って利用されるSUMやCOUNTなどの集約関数

りでしょう。これ以外にも拡張的な集約関数を用意の使い方が鍵になります。SQLでは、これら集合操している実装もありますが 注1、標準SQLで用意され作の機能が充実しているため、手続き型言語ならばているのはこの5つです。なぜこれらに「集約」と複雑なループや分岐を使って記述せねばならない処

いう接頭辞がついているかというと、文字通り、複理を、非常に簡単で見通しよくコーディングするこ

数行を1行にまとめる効果を持つからです。

とが可能になっています。
しかし一方で、プログラミングにおける思考の基
本単位を「レコード」から「レコードの集合」に切

この効果を体感するために、1つ例題を解いてみましょう。いま、表1のようなサンプルテーブルがあるとします。

り替えるためには、多少の発想の転換を要します。

CSVや固定長などのフラットファイルをそのまま

この切り替えがうまくいかないために、せっかくテーブルに写し取った形の、よく見かける擬似配列

SQLが最も本領を発揮するフィールドであるにもかテーブルです。人物を管理するid列に、データの種

かわらず、その機能を十全に利用できないまま、も別を管理するrec̲typeを加えて、主キーとしていまどかしい思いを抱えているエンジニアも少なくないす。data̲1〜data̲6の列は、人物一人ひとりについでしょう。

ての何らかの情報を表していると考えてください。

本章では、このSQLの一番「SQLらしい」機能のさて、テーブルの色分けに注目しましょう。data̲

活かし方を、これまで同様、いくつかの小さなケー

type列がAの行については、data̲1とdata̲2、Bのススタディを通じて見ていきたいと思います。

行についてはdata̲3〜data̲5、Cの行については

q複数行を1行にまとめる

data̲6について背景の濃度を変えています。これは、SQLには、集約関数（aggregate function）というすると、この非集約テーブルのように1人の人間

名前で、ほかのいろいろな関数とは区別されて呼ばについての情報が複数行に分散していると、その情れる関数が存在します。具体的には以下の5つです。

報にアクセスするためのクエリも複数回発行する必

この業務において実際に使用したいデータのフィールドを示すものです。

注1）たとえばOracleなら、分散や最頻値を求める集約関数も持っています。用途が統計に偏っているのは、統計もまた集団の個々の要素ではなく、集団そのものを扱う基本単位とする分野であることを考えれば当然の話です。リレーショナルデータベースと統計は、非常に相性がよいのです。

WEB +DB PRESS Vol.44● 63特集2-03̲5 08.10.16 15:57 ページ64

分岐とループ、集合操作…

養成講座

特集 2 プログラミング言語とはここが違う！
●表1 NonAggTbl：非集約テーブル
iddata̲typedata̲1data̲2data̲3data̲4data̲5

Jim
Jim
Jim
Ken
Ken
Ken
Beth
Beth
Beth

A
B
C
A
B
C
A
B
C

100
45

10
2
3
5
12

34
167
687
724
178
23
190
183

346
77
1355
457
346
46
25

54
90
324

78
123
45
75
435
96

0
0
128

0

85
687
356
4
0

data̲6
157
457
1
235
33
325
12

要が生じます。たとえば、Jimについての情報を得したがって本当は、表2のような形のテーブルがようとする場合、リスト1〜3のように3つの異なる一番使いやすい理想的なテーブルということになりクエリが必要になります（実行結果は図1〜3）。

ます。

これは、3回発行するためにパフォーマンス上の先ほどのNonAggTblと比べれば、その違いは明コストがかかるだけでなく、結果の形式を1行にまらかです。非集約テーブルでは、1人についての情

とめることもできないうえ、クエリごとに列数が異報が複数行に分散していたため1人の情報を参照すなるためUNIONで1つのクエリにまとめることも困るためにも複数の行にアクセスする必要があったの難という、はなはだ非効率的な方法と言わねばなりですが、集約後のテーブルを見れば、1人の人間にません。

ついての情報がすべて同じ行にまとめられているの●リスト1 データタイプ「A」の行に対するクエリ
SELECT id, data_1, data_2
FROM NonAggTbl
WHERE id = 'Jim'
AND data_type = 'A';

で1つのクエリで済みます。
では問題です。NonAggTblからAggTblへの変換を行うSQLを考えましょう。考え方としては、まず人物単位に集約するので、GROUP BY句に書く集
約キーは、id列ということになります。後は、選択●図1 リスト1の実行結果する列をデータタイプによって分岐させればよいわ

id---Jim

けです。ここで、第1章で登場したCASE式が有効

data_1------100

data_2------10です。すると、まずリスト4のような形のクエリができます。

●リスト2 データタイプ「B」の行に対するクエリ
SELECT id, data_3, data_4, data_5
FROM NonAggTbl
WHERE id = 'Jim'
AND data_type = 'B';となります 注2。というのも、GROUP BYを使って集約操作を行った場合、SELECT句に書くことのでき●図2 リスト2の実行結果
id---Jimdata_3-----167

data_4-----77

このクエリは、残念ながら構文違反のためエラー●リスト3 データタイプ「C」の行に対するクエリ
data_5-----90

SELECT id, data_6
FROM NonAggTbl
WHERE id = 'Jim'
AND data_type = 'C';

●表2 AggTbl：1人1行に集約したテーブル
id
Jim
Ken
Beth

data̲1100
78
75

data̲210
50data̲3
167
178
183

data̲477346

data̲59085
4data̲64573312●図3 リスト3の実行結果
id
data_6---- -----Jim 457

注2）MySQLは、このクエリを通すような独自拡張を施していますが、標準違反でほかの実装との互換性もないため、使わないほうがよ
いでしょう。なぜMySQLがこのような独自拡張をしているかというと、本文にも書いたとおり、単元集合と要素を混同しているからです。詳しくは、『指南書』の「2-10 SQLにおける存在の階層」を参照。

64●WEB +DB PRESS Vol.44

特集2-03̲5 08.10.16 15:57 ページ65

思考の基本単位は「レコード」
ではなく「レコードの集合」

SQL流 集合操作

第3章（実行結果は図4）。

るのは、

MAX関数を使ったのは、どうせ切り分けた集合が1． 定数

1行しか含まないのだから、最大値を取ろうと値が変2． GROUP BY句で指定した集約キー

わらないからです 注3。あとは、この結果を別に用意

3． 集約関数

したAggTblテーブルにINSERTすればOKです。まに限定されるからです。いま、CASE式の中で使わたは、パフォーマンスに不安がなければ、このクエれているdata̲1〜data̲6は、このどれにも該当しま

リをそのままビューに保存してもよいでしょう。

せん。確かに、id列でグループ化したうえ、さらにいかがでしょう。まさに「複数行を1行に集約す

CASE式でデータタイプまで指定したなら、それにる」というGROUP BY句の特徴（そしてもちろん、

よって行は一意に定まります。したがって別に集約

CASE式の便利さも）がよく理解できる1問ではな関数を使わなくても、data̲1〜data̲6を「裸で」書

いでしょうか。

いたとしても、データベースエンジンが気を利かせ

w合わせ技1本てくれれば値は一意に定まります（実際、MySQL
ではこのクエリはエラーにはなりません）。
しかしこれは、単元集合と要素を混同する、SQL

引き続き、集約操作の練習をもう1問やっておきの原理（それはつまり集合論の原理でもある）に反ましょう。問題は、『SQLパズル 第2版』の「パズする行いです。したがって正しくは、面倒でも集約

ル65 製品の対象年齢の範囲」を使います。表3の関数を使ってリスト5のように書く必要があります

ような、複数の製品の対象年齢ごとの値段を管理す●リスト4 惜しいけど間違い
SELECT id,
CASE WHEN data_type = 'A' THEN data_1 ELSE NULL END AS data_1,
CASE WHEN data_type = 'A' THEN data_2 ELSE NULL END AS data_2,
CASE WHEN data_type = 'B' THEN data_3 ELSE NULL END AS data_3,
CASE WHEN data_type = 'B' THEN data_4 ELSE NULL END AS data_4,
CASE WHEN data_type = 'B' THEN data_5 ELSE NULL END AS data_5,
CASE WHEN data_type = 'C' THEN data_6 ELSE NULL END AS data_6
FROM NonAggTbl
GROUP BY id;●リスト5 これが正解。どの実装でも通る
SELECT id,
MAX(CASE WHEN data_type = 'A' THEN data_1 ELSE NULL END) AS data_1,
MAX(CASE WHEN data_type = 'A' THEN data_2 ELSE NULL END) AS data_2,
MAX(CASE WHEN data_type = 'B' THEN data_3 ELSE NULL END) AS data_3,
MAX(CASE WHEN data_type = 'B' THEN data_4 ELSE NULL END) AS data_4,
MAX(CASE WHEN data_type = 'B' THEN data_5 ELSE NULL END) AS data_5,
MAX(CASE WHEN data_type = 'C' THEN data_6 ELSE NULL END) AS data_6
FROM NonAggTbl
GROUP BY id;●図4 リスト5の実行結果
id-----Jim
Ken
Bethdata_1-----1007875

data_2-----1050

data_3-----167
178
183

data_4-----77
346
NULL

data_5-----90854

data_6-----457
33
12

注3）だから別に、MINやAVG、SUMを使ってもこの場合は同じです。ただし、今回はdata̲1〜data̲6の型が数値型なのでAVGやSUM
でもかまいませんが、文字型や日付型など対応するために、MAXかMINのどちらかを使う習慣をつけておくのがよいと思います。

WEB +DB PRESS Vol.44● 65特集2-03̲5 08.10.16 15:57 ページ66

分岐とループ、集合操作…

養成講座

特集 2 プログラミング言語とはここが違う！
るテーブルがあるとします。同じ製品IDでも値段の製品1は今回の条件を確かに満たします。一方、製異なる製品があるのは、対象年齢によって設定や難品3の数直線を見ると、3レコードも使っているにも易度を変えたバージョンの違いによるもの、くらいかかわらず、21〜30の間が断絶していることが見てに考えてください。また1つの製品について、年齢取れます。こちらは残念ながらNGです。

範囲の重複するレコードはないものと仮定します。

このように、たとえ1レコードで全年齢範囲をカ

すると、このテーブルにおいては、（製品ID, 対象

バーできなかったとしても、複数のレコードを組み

年齢の下限）で、レコードが一意に定まります（下合わせてカバーできたなら、「合わせ技1本」とみな限の代わりに上限を使ってもかまいません）。考えてす、というのがこの問題の主旨です。

もらう問題は、これらの製品の中から、0〜100歳まそうとわかれば、あとの話は先ほどの問題と同じでのすべての年齢で遊べる製品を求めるというものです。まず、集約する単位は製品ですから、集約です。もちろん、バージョンの相違は無視して、製キーは製品IDに決まります。あとは、各レコードの品ID単位で考えます。

範囲の大きさをすべて足しこんだ合計が101に到達

図5のように図示してみると、問題の意図がよりしている製品を探し出せば任務完了です（0から100までなので、値の個数は101個であることに注意）。

わかりやすくなるでしょう。
製品1の場合、2レコードを使って0〜100までの整数の全範囲をカバーできています。したがって、答えはリスト6のようになります。
HAVING句の「high̲age - low̲age + 1」で、各行の年齢範囲が含む値の個数が算出されま●表3 PriceByAge
product̲id（製品ID）

low̲age（対象年齢の下限）

high̲age（対象年齢の上限）

price
（値段）

製品1
製品1
製品2
製品3
製品3
製品3
製品4

0
51
0
0
31
71
0

50
100
100
20
70
100
99

2000
3000
4200
500
800
1000
8900

す。あとは、それを同じ製品内で足し合わせればよいわけです。
今は、サンプルとして「年齢」という数値型のデータを用いましたが、より一般的に日付や時刻に拡張することもできます。
たとえば、応用問題としてこんなのはどうでしょう。ホテルの部屋ごとに、投宿日と出発日の履歴を記録するテーブルを使いま●図5 製品の対象年齢の範囲す（表4）。
このテーブルから、稼働日数が10日を

製品1の年齢範囲：OK超える部屋を選択してください。稼働日数
0

10

20

30

40

50

60

70

80

90

100

の定義は、宿泊日数で計ることとします。
だから、投宿日が2月1日、出発日が2月6日の場合は、5泊なので5日です。これは演習問題として、宿題にしておきましょう

製品3の年齢範囲：NG
0

10

20

30

40

50

60

（解答は後日、筆者のWebサイト注4 に掲載
70

80

90

100

予定です）
。

●リスト6 正解
数値の連続性に断絶が生じている

注4）「リレーショナル・データベースの世界」

66●WEB +DB PRESS Vol.44

SELECT product_id
FROM PriceByAge
GROUP BY product_id
HAVING SUM(high_age - low_age + 1) = 101;

http://www.geocities.jp/mickindex/database/idx̲database.html

特集2-03̲5 08.10.16 15:57 ページ67

思考の基本単位は「レコード」
ではなく「レコードの集合」

SQL流 集合操作

第3章

eあなたは肥り過ぎ？ 痩せ過ぎ？ 〜カットとパーティション〜ん。今度は、この「カット」の機能に焦点を当ててこれまでの2問では、GROUP BYの「集約」といあなたは、上司からこのテーブルを使って簡単なう側面を強調してその機能を調べてきました。です

集計作業を依頼されたとします。まずは小手調べ。

が、冒頭でも少し触れたようにGROUP BYには、集名簿のインデックスを作るために、名前の頭文字の約以外にも、もう1つ重要な機能があります。それ

アルファベットごとに何人がテーブルに存在するかが、「カット」という機能です。これは要するに、母を集計しましょう。

みましょう。サンプルに、表5のような個人の身長などの情報を保持するテーブルを使います。

集合である元のテーブルを小さな部分集合に切り分

これはつまり、Persons集合を図6のようなS1〜

けることです。だからGROUP BYというのは、実はS4の部分集合に切り分けて、それぞれの要素数を調

これ1つの中に、べる、ということです。
集合の要素数を調べる関数は、もちろんCOUNT。

1． カット

あとは、頭文字をGROUP BYのキーに指定すれば、

2． 集約

カット完了です。SQLはリスト7です。

という2つの操作が組み込まれた演算なのです 注5。1

こういうGROUP BY句でカットして作られた1つつの句の中に2つの演算が組み込まれているというひとつの部分集合は、数学的には「類（partition）」のもGROUP BYに対する理解を阻む一因になってと呼ばれます。同じ母集合からでも、類の作り方はいるのですが、まあそれはいま言っても始まりませ

切り分け方によってさまざまあります。たとえば、●表5 Persons●表4 HotelRooms
room̲nbr(部屋番号)

start̲date(投宿日)

end̲date
(出発日)

101
101
101
202
202
202
303

2008-02-01
2008-02-06
2008-02-10
2008-02-05
2008-02-08
2008-02-09
2008-02-03

2008-02-06
2008-02-08
2008-02-12
2008-02-06
2008-02-09
2008-02-10
2008-02-17●図6 4つの部分集合に切り分けてそれぞれの要素数を調べる
S0:Persons

name
(名前)

age
(年齢)

height(身長 cm)weight(体重 kg)Anderson
Adela
Bates
Becky
Bill
Chris
DarwinDawsonDonald

30
21
87
54
39
90
12
25
30

188
167
158
187
177
175
160
182
176

90
55
48
70
120
48
55
90
53

●リスト7 頭文字のアルファベットごとに何人がテーブルに存在するか集計するSQLSELECT SUBSTRING(name, 1, 1) AS label,
COUNT(*)
FROM Persons
GROUP BY SUBSTRING(name, 1, 1);

S2:B
Bates
Becky
Bill

S1:A
Anderson
Adela
S3:C
Chris

●図7 リスト7の実行結果
S4:D
DarwinDawsonDonaldlabel-----ABCD

COUNT(*)--------2
3
1
3注5）GROUP BYから集約の機能を取り去って、カットの機能だけを残したのがPARTITION BYです。この2つの句を比較すると、興味深いことが見えてきます。『指南書』の「2-5 GROUP BYとPARTITION BY」を参照。

WEB +DB PRESS Vol.44● 67特集2-03̲5 08.10.16 15:57 ページ68

分岐とループ、集合操作…

養成講座

特集 2 プログラミング言語とはここが違う！
年齢によって、子供（20歳未満）、成人（20〜69とがあると思います。身長を （t メートル）、体重をw

歳）、老人（70歳以上）に分けるなら、図8のように（キログラム）とすると、以下の式で求められます。

カットされます。
当然、GROUP BYのキーもこの3つの区分に対応する形になります。これは、CASE式を使ってリス
ト8のように表現します（実行結果は図9）。

BMI = w / t2
ここで、身長はセンチではなくメートルであることに注意してください。これによって求められた数カットしたい区分を、GROUP BY句とSELECT

値に基づいて、日本では18.5未満を「やせている」、

句の両方に書いてやるのがポイントです。Postgre

18.5以上25未満を標準、25以上を肥満としていま

SQLとMySQLでは、SELECT句で付けた「age̲

す。この基準に基づいて、Personsテーブルの人々

class」という別名を使って、「GROUP BY age̲の体重を分類して、各階級の人数を求めます。ちなclass」という簡潔な書き方も許しているのですが、みに、各人のBMIは表6のとおり。

標準違反なので勧めません。

すると、カットのイメージは図10のようになりま

さて、それでは最後の問題。もっと複雑な基準です。

Persons集合をカットしてみましょう。これが解け

まずBMIを計算しましょう。これは「weight /たら、GROUP BY句に対するみなさんの理解は十分

POWER(height / 100, 2)」という式で簡単に求めらであることを保証しましょう。

れます。こうして求められたBMIをCASE式で3つの階級に振り分ければ、カットする基準が作れます。

BMIによるカット

あとは、これをGROUP BY句とSELECT句に書け

健康診断などで、BMIという体重の指標を見たこ

ばできあがり（リスト9。実行結果は図11）。

●図8 年齢によるカット
S0:Persons
S2:成人

S1:子供
DarwinAnderson
Adela
Becky
Bill
DawsonDonald

S3:老人
Bates
Chris

●リスト8 年齢による区分を実施
SELECT CASE WHEN age < 20 THEN '子供'
WHEN age BETWEEN 21 AND 69 THEN '成人'
WHEN age > 70 THEN '老人'ELSE NULL END AS age_class,
COUNT(*)
FROM Persons
GROUP BY CASE WHEN age < 20 THEN '子供'
WHEN age BETWEEN 21 AND 69 THEN '成人'
WHEN age > 70 THEN '老人'ELSE NULL END;

●表6 BMI
名前
Anderson
Adela
Bates
Becky
Bill
Chris
DarwinDawsonDonald

BMI
25.5
19.7
19.2
20.0
38.3
15.7
21.5
27.2
17.1

分類
肥満
標準
標準
標準
肥満
やせ
標準
肥満
やせ

●図10 BMIによるカットのイメージ
S0:Persons
S2:標準●図9 リスト8の実行結果

S1:やせ

age_class----------

Chris
Donald

子供
成人
老人

COUNT(*)--------1
6
2

68●WEB +DB PRESS Vol.44

Adela
Bates
Becky
DarwinS3:肥満
AndersonBill
Dawson特集2-03̲5 08.10.16 15:57 ページ69

思考の基本単位は「レコード」
ではなく「レコードの集合」

SQL流 集合操作
GROUP BY句が「SQLの本領」である、という的な差はありません。

言葉の意味が、少しわかっていただけたのではないということは、です。PARTITION BY句にも、やでしょうか。GROUP BY句には列名を書くものだとはり単純な列名だけでなく、CASE式や計算式を利思い込んでいる人にとっては、こんな複雑な基準に用した複雑な基準を記述できてもおかしくないはずよるパーティションカットが可能であると知ることです。そして事実、それは可能なのです。

は、一種の感動をもたらします（かつて私もそうで

第3章

たとえば、さっきの年齢階級別のパーティション
カットを使いましょう。これをPARTITION BY句にした）。

年齢階級別のパーティションカット
それでは最後に、発展的な話をして章を締めくく

記述して、同一年齢階級内で年齢の上下によって順位をつけるクエリは、リスト10のようになります（実行結果は図12）。

りましょう。先ほど私は、注5で「GROUP BYから

結果に横棒を引いたのは、パーティション（類）

集約機能を取り去って、カットの機能だけ残したのの区切りを明確にするためです。最後尾のがPARTITION BYだ」と述べました。実際、その1age̲rank̲in̲classが各パーティション内部での年齢点を除けば、GROUP BYとPARTITION BYに機能の順位を示す列です。PARTITION BYはGROUP●図12 リスト10の実行結果●図11 リスト9の実行結果
BMI-----------やせている
標準
肥満

COUNT(*)---------2
4
3

name
-------Darwin
Adela
DawsonAnderson
Donald
Bill
Becky
Bates
Chris

age
----12
21
25
30
30
39
54
87
90

age_class-------子供
成人
成人
成人
成人
成人
成人
老人
老人

age_rank_in_class----------------1
1
2
3
3
5
6
1
2

●リスト9 BMIによる体重分類を求めるクエリ
SELECT CASE WHEN weight / POWER(height /100, 2) < 18.5
THEN 'やせている'
WHEN 18.5 <= weight / POWER(height /100, 2)
AND weight / POWER(height /100, 2) < 25 THEN '標準'
WHEN 25 <= weight / POWER(height /100, 2)
THEN '肥満'
ELSE NULL END AS bmi,
COUNT(*)
FROM Persons
GROUP BY CASE WHEN weight / POWER(height /100, 2) < 18.5
THEN 'やせている'
WHEN 18.5 <= weight / POWER(height /100, 2)
AND weight / POWER(height /100, 2) < 25 THEN '標準'
WHEN 25 <= weight / POWER(height /100, 2)
THEN '肥満'
ELSE NULL END;

●リスト10 PARTITION BYに式を入れてみる
SELECT name,age,
CASE WHEN age < 20 THEN '子供'
PARTITION BY 句に
WHEN age BETWEEN 21 AND 69 THEN '成人'式を指定している
WHEN age > 70 THEN '老人'ELSE NULL END AS age_class,
RANK() OVER(PARTITION BY CASE WHEN age < 20 THEN '子供'
WHEN age BETWEEN 21 AND 69 THEN '成人'
WHEN age > 70 THEN '老人'ELSE NULL END
ORDER BY age) AS age_rank_in_class
FROM Persons
ORDER BY age_class, age_rank_in_class;

WEB +DB PRESS Vol.44● 69特集2-03̲5 08.10.16 15:57 ページ70

分岐とループ、集合操作…

養成講座

特集 2 プログラミング言語とはここが違う！
BYと違って集約を伴わないため、もとのPersons

しかし、「行」ではなく「行の集合」を操作の基本単テーブルの行がすべてそのまま「ヒラ」で出てくる位とするというSQLの原理に照らして見た場合、当ことに注目してください。

然、集合について条件を設定する機能が用意されて

GROUP BYが式を引数にとれる以上PARTITION
BYもまた同様であるということは、論理的には何いて然るべきです。そのために活躍するのが、今からスポットを当てるHAVING句です。

のためらいもなく得られる結論ではありますが、実サンプルに、前節e「あなたは肥り過ぎ？ 痩せ際にクエリを目にしてみると「こんなことが可能な過ぎ？ 〜カットとパーティション〜」で使った年齢のか…」という感慨にとらわれるのではないでしょ階級別に人数を求めるクエリを使いましょう。そのうか。

基準に従えば、各人は表7のように階級に振り分けられるのでした。

r集合の性質を調べる
これまでは、主に集合の切り分け方のいろいろな
バリエーションを紹介してきました。第2章で見たでは、ここからさらに、「その階級の全員のBMIが『標準』か『やせ』のどちらかに分類される」階
級だけを選択してください。結果は、「子供」と「老人」の階級だけになります。

ような入れ子の再帰集合を作る方法から、本章で見

まず、
「子供」
「成人」
「老人」の3階級に分割した

たような重なり合わないパーティション単位に切り部分集合を作るまでは、先ほどのクエリと同じです。

分ける方法まで、GROUP BY句を使えば思うままに

あとはそこに、「階級」という集合自身の性質を調べる条件を付加するだけです。ここでHAVING句が集合を組み替えられます。
ここでは、そうして作られた部分集合について、活躍します（リスト11。実行結果は図13）。

その性質を調べる方法を取り上げます。私たちが頻ここで重要な役割を果たすトリックは、HAVING

繁に条件を記述するWHERE句というのは、言うま句の右辺で、CASE式で特性関数（戻り値が0またでもなく「行」に対する条件を記述する場所です。

は1の関数）を用いていることです。この特性関数●リスト11 集合の性質を調べるためのクエリ
SELECT CASE WHEN age < 20 THEN '子供'
WHEN age BETWEEN 21 AND 69 THEN '成人'
WHEN age > 70 THEN '老人'ELSE NULL END AS age_class,
COUNT(*)
FROM Persons
GROUP BY CASE WHEN age < 20 THEN '子供'
WHEN age BETWEEN 21 AND 69 THEN '成人'
WHEN age > 70 THEN '老人'ELSE NULL END
HAVING COUNT(*) = SUM(CASE WHEN weight / POWER(height /100, 2) < 25 THEN 1
ELSE 0 END);●図13 リスト11の実行結果●表7 階級の振り分け
name
(名前)

age
(年齢)

age̲class(年齢階級)Height(身長 cm)weight(体重 kg)Anderson
Adela
Bates
Becky
Bill
Chris
DarwinDawsonDonald

30
21
87
54
39
90
12
25
30

成人
成人
老人
成人
成人
老人
子供
成人
成人

188
167
158
187
177
175
160
182
176

90
55
48
70
120
48
55
90
53

70●WEB +DB PRESS Vol.44

age_classcount(*)------------ ---------老人2
子供1特集2-03̲5 08.10.16 15:57 ページ71

思考の基本単位は「レコード」
ではなく「レコードの集合」

SQL流 集合操作
によって、BMIが25未満の人物については「1」、25以上の人物については「0」という、一種のビット
フラグを立てています。あとはこのビットフラグを合計した結果が集合の要素数と一致すれば、「全員のビットフラグが1だった」ことが保証されるわけ

第3章¡GROUP BY句はさらに、カットした集合単位に行をまとめる「集約」の機能も持つ¡GROUP BY句から集約の機能を取り去って、カットだけを残したのがPARTITION BY句¡どちらの句も、列名に限らず「式」を引数に取れる柔軟さが強み。これによって、SQLは集合をどです。
もしもっと直截に「各階級の肥満ではない人物の数」を結果として求めたいなら、リスト12のようにんな複雑な基準によってでも切り刻み、料理することが可能となっている

SELECT句でビットフラグの合計を数えてやればよいでしょう（実行結果は図14）。

GROUP BYやPARTITION BYは、もちろんそれ

こうすることで、先ほどは条件を満たさないため単独で見ても、強力で使いでのある武器です。しかに結果から除外されていた「成人」階級も結果に現し、本章でも見たように、CASE式などのほかの武れます。こちらのほうが、成人はAnderson、Bill、器と組み合わせたときに、最もその真価を発揮しま

Dawsonの3人が「肥満」に該当してしまうのだ、とす。そして実は、GROUP BY句と最も相性のよい武いう事実も明確に示す結果となっていることがおわ

器はHAVING句です。最後で少しだけ紹介しました

かりいただけるでしょう。

が、この2つを組み合わせたときの記述力には目を見張るものがあります。これについては、取り上げおわりにSQLの集合操作の勘所を学ぶために、GROUP BY（とPARTITION BY）を中心に解説をしてきました。
いかがでしょう。少し、SQLの寄って立つ基礎について理解を深められたでしょうか。もう一度、重要なポイントをまとめておきましょう。

ようとするとかなり紙幅を必要とするので、また機会を改めてお話しすることにしましょう注6。
本章の内容について、さらに踏み込んで学びたい方は、以下の参考文献を参照してください。
『SQLパズル 第2版』
（J.セルコ 著、翔泳社、2007）
GROUP BYの応用として興味深いのは、「カット」の機能をうまく利用した「パズル29 最頻値を求め¡SQLの基本的な処理単位は、レコード（行）ではなく「レコードの集合」

る」や入れ子の再帰集合を作って累計を求める「パズル35 在庫調整」。HAVING句については、特性¡GROUP BY句は元のテーブルを小さな部分集合

関数を駆使する「パズル11 作業依頼」や集合指向（類）に切り分ける「カット」の機能を持つの極致と言うべき芸術的な欠番探索の方法を紹介す●リスト12 集合の性質を調べるためのクエリ●図14 リスト12の実行結果

SELECT CASE WHEN age < 20 THEN '子供'
WHEN age BETWEEN 21 AND 69 THEN '成人'
「肥満」以外のWHEN age > 70 THEN '老人'
人数を数える式
ELSE NULL END AS age_class,
COUNT(*) AS all_cnt,
SUM(CASE WHEN weight / POWER(height /100, 2) < 25 THEN 1
ELSE 0 END) AS not_fat_cnt
FROM Persons
GROUP BY CASE WHEN age < 20 THEN '子供'
WHEN age BETWEEN 21 AND 69 THEN '成人'
WHEN age > 70 THEN '老人'ELSE NULL END;

age_classall_cnt not_fat_cnt------------ ---------- ----------成人
6
3
老人
2
2
子供
1
1

注6）HAVING句については、『指南書』の中でかなり詳しく取り上げています。先に知りたい人は「1−4．HAVING句の力」や「1−10．
帰ってきたHAVING句」を参照。

WEB +DB PRESS Vol.44● 71特集2-03̲5 08.10.16 15:57 ページ72

分岐とループ、集合操作…

養成講座

特集 2 プログラミング言語とはここが違う！
る「パズル57 欠番探し――バージョン1」など。
『達人に学ぶ SQL徹底指南書』（ミック、翔泳社、
2008）

たちエンジニアにとっては、正面からこうしたなじ
みの薄い概念に取り組むよりも、なるべく慣れ親しんだ手続き型の考えからSQLの集合指向に接近する
ほうが理解しやすいのではないか、と考えて、この本書ではHAVING句をかなり重点的に取り上げまような構成をとりました。

した。
「1-4 HAVING句の力」や「1-10 帰ってきた

この特集を読んで、SQLについて、よりハイレベ

HAVING句」を参照。一方、HAVING句の復権にルな技術を学びたいと思った方（あるいは全然物足力を入れるあまり、GROUP BY句についての説明がりなかった方）は、まずは拙著『SQL徹底指南書』

薄くなってしまったので、今回の特集と併せて読んを参照されることを勧めます。その後に、『指南書』でいただくと、より一層効果的でしょう。

の巻末の「参考文献」に挙げた書籍を紐解くことで、より高度で興味深いデータベースの世界が開けてい特集のおわりにくことでしょう。#

以上、3つの章にわたって、SQLの基本的なロジックを解説してきました。なるべく手続き型言語
かきょうの考えにひきつけて、SQLとの架橋を果たそうと試
みてきたつもりですが、いかがだったでしょう。
SQLは、基本的には集合と述語という、それ自身、大変しっかりした数学的・論理的な基礎を持つ道具立てをうまく応用した言語ですが、おそらく私

改訂新版

反復学習ソフト付き

SQL書き方ドリル
データベースが理解しやすい（パフォーマンスの良い）SQLを書くためには，SQLの「書き順」
マスターが重要です。本書でSQLが実行されるしくみとそれに合わせた書き順を理解し，「書き込み式ドリル」とCD-ROM収録の「SQUAT」でとことん反復学習，身体で覚えてください。

こんな方におすすめ！
・これからSQLを学習したい方
・思いどおりのSQLが書けないという方・SQLのパフォーマンスに自信がないという方羽生章洋，和田省二 著
B5判／320ページ／CD1枚
定価2,583円（本体2,460円）
ISBN 978-4-7741-3085-9

72●WEB +DB PRESS Vol.44