はじめに
SQLという言語の大きな特徴として、処理をレコード単位ではなく、レコードの「集合」単位でひとまとめにして記述するというものが挙げられます。具体的には、GROUP BY句とHAVING句、それに伴って利用されるSUMやCOUNTなどの集約関数の使い方が鍵になります。SQLでは、これら集合操作の機能が充実しているため、手続き型言語ならば複雑なループや分岐を使って記述せねばならない処理を、非常に簡単で見通しよくコーディングすることが可能になっています。
しかし一方で、プログラミングにおける思考の基本単位を「レコード」から「レコードの集合」に切り替えるためには、多少の発想の転換を要します。この切り替えがうまくいかないために、せっかくSQLが最も本領を発揮するフィールドであるにもかかわらず、その機能を十全に利用できないまま、もどかしい思いを抱えているエンジニアも少なくないでしょう。
本章では、このSQLの一番「SQLらしい」機能の活かし方を、これまで同様、いくつかの小さなケーススタディを通じて見ていきたいと思います。
複数行を1行にまとめる
SQLには、集約関数(aggregate function)という名前で、ほかのいろいろな関数とは区別されて呼ばれる関数が存在します。具体的には以下の5つです。
たぶん、みなさんにとってもお馴染みの関数ばかりでしょう。これ以外にも拡張的な集約関数を用意している実装もありますが注1 、標準SQLで用意されているのはこの5つです。なぜこれらに「集約」という接頭辞がついているかというと、文字通り、複数行を1行にまとめる効果を持つからです。
この効果を体感するために、1つ例題を解いてみましょう。いま、表1 のようなサンプルテーブルがあるとします。
表1 NonAggTbl:非集約テーブル
id data_type data_1 data_2 data_3 data_4 data_5 data_6
Jim A 100 10 34 346 54
Jim B 45 2 167 77 90 157
Jim C 3 687 1355 324 457
Ken A 78 5 724 457 1
Ken B 123 12 178 346 85 235
Ken C 45 23 46 687 33
Beth A 75 0 190 25 356
Beth B 435 0 183 4 325
Beth C 96 128 0 0 12
CSVや固定長などのフラットファイルをそのままテーブルに写し取った形の、よく見かける擬似配列テーブルです。人物を管理するid列に、データの種別を管理するrec_typeを加えて、主キーとしています。data_1~data_6の列は、人物一人ひとりについての何らかの情報を表していると考えてください。
さて、テーブルの色分けに注目しましょう。data_type列がAの行については、data_1とdata_2、Bの行についてはdata_3~data_5、Cの行についてはdata_6について背景の濃度を変えています。これは、この業務において実際に使用したいデータのフィールドを示すものです。
すると、この非集約テーブルのように1人の人間についての情報が複数行に分散していると、その情報にアクセスするためのクエリも複数回発行する必要が生じます。たとえば、Jimについての情報を得ようとする場合、リスト1~3 のように3つの異なるクエリが必要になります(実行結果は図1~3 ) 。
リスト1 データタイプ「A」の行に対するクエリ
SELECT id, data_1, data_2
FROM NonAggTbl
WHERE id = 'Jim'
AND data_type = 'A';
図1 リスト1の実行結果
id data_1 data_2
---- ------- -------
Jim 100 10
リスト2 データタイプ「B」の行に対するクエリ
SELECT id, data_3, data_4, data_5
FROM NonAggTbl
WHERE id = 'Jim'
AND data_type = 'B';
図2 リスト2の実行結果
id data_3 data_4 data_5
---- ------ ------ ------
Jim 167 77 90
リスト3 データタイプ「C」の行に対するクエリ
SELECT id, data_6
FROM NonAggTbl
WHERE id = 'Jim'
AND data_type = 'C';
図3 リスト3の実行結果
id data_6
---- ------
Jim 457
これは、3回発行するためにパフォーマンス上のコストがかかるだけでなく、結果の形式を1行にまとめることもできないうえ、クエリごとに列数が異なるためUNIONで1つのクエリにまとめることも困難という、はなはだ非効率的な方法と言わねばなりません。
したがって本当は、表2 のような形のテーブルが一番使いやすい理想的なテーブルということになります。
表2 AggTbl:1人1行に集約したテーブル
id data_1 data_2 data_3 data_4 data_5 data_6
Jim 100 10 167 77 90 457
Ken 78 5 178 346 85 33
Beth 75 0 183 4 12
先ほどのNonAggTblと比べれば、その違いは明らかです。非集約テーブルでは、1人についての情報が複数行に分散していたため1人の情報を参照するためにも複数の行にアクセスする必要があったのですが、集約後のテーブルを見れば、1人の人間についての情報がすべて同じ行にまとめられているので1つのクエリで済みます。
[1] たとえばOracleなら、分散や最頻値を求める集約関数も持っています。用途が統計に偏っているのは、統計もまた集団の個々の要素ではなく、集団そのものを扱う基本単位とする分野であることを考えれば当然の話です。リレーショナルデータベースと統計は、非常に相性がよいのです。
では問題です。NonAggTblからAggTblへの変換を行うSQLを考えましょう。考え方としては、まず人物単位に集約するので、GROUP BY句に書く集約キーは、id列ということになります。後は、選択する列をデータタイプによって分岐させればよいわけです。ここで、第1章で登場したCASE式が有効です。すると、まずリスト4 のような形のクエリができます。
リスト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;
このクエリは、残念ながら構文違反のためエラーとなります[2] 。というのも、GROUP BYを使って集約操作を行った場合、SELECT句に書くことのできるのは、
定数
GROUP BY句で指定した集約キー
集約関数
に限定されるからです。いま、CASE式の中で使われているdata_1~data_6は、このどれにも該当しません。確かに、id列でグループ化したうえ、さらにCASE式でデータタイプまで指定したなら、それによって行は一意に定まります。したがって別に集約関数を使わなくても、data_1~data_6を「裸で」書いたとしても、データベースエンジンが気を利かせてくれれば値は一意に定まります(実際、MySQLではこのクエリはエラーにはなりません) 。
しかしこれは、単元集合と要素を混同する、SQLの原理(それはつまり集合論の原理でもある)に反する行いです。したがって正しくは、面倒でも集約関数を使ってリスト5 のように書く必要があります(実行結果は図4 ) 。
リスト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 data_1 data_2 data_3 data_4 data_5 data_6
------ ------ ------ ------ ------ ------ ------
Jim 100 10 167 77 90 457
Ken 78 5 178 346 85 33
Beth 75 0 183 NULL 4 12
MAX関数を使ったのは、GROUP BYで切り分けた時点では、各集合は三つの要素を含んでいますが、集約関数を適用すると、その時点でNULLが除外されて一つの要素に限定されるからです注3 。あとは、この結果を別に用意したAggTblテーブルにINSERTすればOKです。または、パフォーマンスに不安がなければ、このクエリをそのままビューに保存してもよいでしょう。
いかがでしょう。まさに「複数行を1行に集約する」というGROUP BY句の特徴(そしてもちろん、CASE式の便利さも)がよく理解できる1問ではないでしょうか。
[2] MySQLは、このクエリを通すような独自拡張を施していますが、標準違反でほかの実装との互換性もないため、使わないほうがよいでしょう。なぜMySQLがこのような独自拡張をしているかというと、本文にも書いたとおり、単元集合と要素を混同しているからです。詳しくは、『 指南書』の「2-10 SQLにおける存在の階層」を参照。