はじめに

SQLという言語の大きな特徴として、処理をレコード単位ではなく、レコードの「集合」単位でひとまとめにして記述するというものが挙げられます。具体的には、GROUP BY句とHAVING句、それに伴って利用されるSUMやCOUNTなどの集約関数の使い方が鍵になります。SQLでは、これら集合操作の機能が充実しているため、手続き型言語ならば複雑なループや分岐を使って記述せねばならない処理を、非常に簡単で見通しよくコーディングすることが可能になっています。

しかし一方で、プログラミングにおける思考の基本単位を「レコード」から「レコードの集合」に切り替えるためには、多少の発想の転換を要します。この切り替えがうまくいかないために、せっかくSQLが最も本領を発揮するフィールドであるにもかかわらず、その機能を十全に利用できないまま、もどかしい思いを抱えているエンジニアも少なくないでしょう。

本章では、このSQLの一番「SQLらしい」機能の活かし方を、これまで同様、いくつかの小さなケーススタディを通じて見ていきたいと思います。

複数行を1行にまとめる

SQLには、集約関数（aggregate function）という名前で、ほかのいろいろな関数とは区別されて呼ばれる関数が存在します。具体的には以下の5つです。

• COUNT
• SUM
• AVG
• MAX
• MIN

たぶん、みなさんにとってもお馴染みの関数ばかりでしょう。これ以外にも拡張的な集約関数を用意している実装もありますが注1、標準SQLで用意されているのはこの5つです。なぜこれらに「集約」という接頭辞がついているかというと、文字通り、複数行を1行にまとめる効果を持つからです。

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

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⁠）⁠。

SELECT id, data_1, data_2
  FROM NonAggTbl
 WHERE id = 'Jim'
   AND data_type = 'A';

id     data_1     data_2
----   -------    -------
Jim    100        10

SELECT id, data_3, data_4, data_5
  FROM NonAggTbl
 WHERE id = 'Jim'
   AND data_type = 'B';

id     data_3    data_4     data_5
----   ------    ------     ------
Jim    167       77         90

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

id    data_6
----  ------
Jim   457

これは、3回発行するためにパフォーマンス上のコストがかかるだけでなく、結果の形式を1行にまとめることもできないうえ、クエリごとに列数が異なるためUNIONで1つのクエリにまとめることも困難という、はなはだ非効率的な方法と言わねばなりません。

したがって本当は、表2のような形のテーブルが一番使いやすい理想的なテーブルということになります。

先ほどのNonAggTblと比べれば、その違いは明らかです。非集約テーブルでは、1人についての情報が複数行に分散していたため1人の情報を参照するためにも複数の行にアクセスする必要があったのですが、集約後のテーブルを見れば、1人の人間についての情報がすべて同じ行にまとめられているので1つのクエリで済みます。

では問題です。NonAggTblからAggTblへの変換を行うSQLを考えましょう。考え方としては、まず人物単位に集約するので、GROUP BY句に書く集約キーは、id列ということになります。後は、選択する列をデータタイプによって分岐させればよいわけです。ここで、第1章で登場したCASE式が有効です。すると、まずリスト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句に書くことのできるのは、

1. 定数
2. GROUP BY句で指定した集約キー
3. 集約関数

に限定されるからです。いま、CASE式の中で使われているdata_1～data_6は、このどれにも該当しません。確かに、id列でグループ化したうえ、さらにCASE式でデータタイプまで指定したなら、それによって行は一意に定まります。したがって別に集約関数を使わなくても、data_1～data_6を「裸で」書いたとしても、データベースエンジンが気を利かせてくれれば値は一意に定まります（実際、MySQLではこのクエリはエラーにはなりません⁠）⁠。

しかしこれは、単元集合と要素を混同する、SQLの原理（それはつまり集合論の原理でもある）に反する行いです。したがって正しくは、面倒でも集約関数を使ってリスト5のように書く必要があります（実行結果は図4⁠）⁠。

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;

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問ではないでしょうか。