SQLアタマ養成講座

第8回 SQL流集合操作(1) 複数行を1行にまとめる

この記事を読むのに必要な時間:およそ 3 分

はじめに

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のようなサンプルテーブルがあるとします。

表1 NonAggTbl:非集約テーブル

iddata_typedata_1data_2data_3data_4data_5data_6
JimA100103434654 
JimB4521677790157
JimC 36871355324457
KenA785724457 1
KenB1231217834685235
KenC45 234668733
BethA75019025356 
BethB4350183 4325
BethC96128 0012

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行に集約したテーブル

iddata_1data_2data_3data_4data_5data_6
Jim100101677790457
Ken7851783468533
Beth750183 412

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

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

著者プロフィール

ミック

SI企業に勤務するDBエンジニア。主にデータウェアハウス業務に従事している。自身のサイト「リレーショナル・データベースの世界」でデータベースとSQLについての技術情報を公開している。『Web+DB Press』で「SQLアタマアカデミー」を連載中。

著書:『達人に学ぶ SQL徹底指南書』(翔泳社、2008)訳書:J.セルコ『SQLパズル 第2版』(翔泳社、2007)

SQLアタマアカデミー:サポートページ

コメント

コメントの記入