SQLアタマ養成講座

第10回 SQL流集合操作(3) あなたは肥り過ぎ? 痩せ過ぎ?

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

あなたは肥り過ぎ? 痩せ過ぎ? ~カットとパーティション~

これまでの2問では,GROUP BYの「集約」という側面を強調してその機能を調べてきました。ですが,冒頭でも少し触れたようにGROUP BYには,集約以外にも,もう1つ重要な機能があります。それが,「カット」という機能です。これは要するに,母集合である元のテーブルを小さな部分集合に切り分けることです。だからGROUP BYというのは,実はこれ1つの中に,

  1. カット
  2. 集約

という2つの操作が組み込まれた演算なのです注5。1つの句の中に2つの演算が組み込まれているというのもGROUP BYに対する理解を阻む一因になっているのですが,まあそれはいま言っても始まりません。今度は,この「カット」の機能に焦点を当ててみましょう。サンプルに,表5のような個人の身長などの情報を保持するテーブルを使います。

表5 Persons

name
(名前)
age
(年齢)
height
(身長 cm)
weight
(体重 kg)
Anderson3018890
Adela2116755
Bates8715848
Becky5418770
Bill39177120
Chris9017548
Darwin1216055
Dawson2518290
Donald3017653

あなたは,上司からこのテーブルを使って簡単な集計作業を依頼されたとします。まずは小手調べ。名簿のインデックスを作るために,名前の頭文字のアルファベットごとに何人がテーブルに存在するかを集計しましょう。

これはつまり,Persons集合を図6のようなS1~S4の部分集合に切り分けて,それぞれの要素数を調べる,ということです。

図6 4つの部分集合に切り分けてそれぞれの要素数を調べる

図6 4つの部分集合に切り分けてそれぞれの要素数を調べる

集合の要素数を調べる関数は,もちろんCOUNT。あとは,頭文字をGROUP BYのキーに指定すれば,カット完了です。SQLはリスト7です。

リスト7 頭文字のアルファベットごとに何人がテーブルに存在するか集計するSQL

SELECT SUBSTRING(name, 1, 1) AS label,
         COUNT(*)
  FROM Persons
 GROUP BY SUBSTRING(name, 1, 1);

図7 リスト7の実行結果

label       COUNT(*)
------      ---------
A           2
B           3
C           1
D           3

こういうGROUP BY句でカットして作られた1つひとつの部分集合は,数学的には「類(partition)」と呼ばれます。同じ母集合からでも,類の作り方は切り分け方によってさまざまあります。たとえば,年齢によって,子供(20歳未満),成人(20~69歳),老人(70歳以上)に分けるなら,図8のようにカットされます。

図8 年齢によるカット

図8 年齢によるカット

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

リスト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;

図9 リスト8の実行結果

age_class      COUNT(*)
----------     ---------
子供              1
成人              6
老人              2

カットしたい区分を,GROUP BY句とSELECT句の両方に書いてやるのがポイントです。PostgreSQLとMySQLでは,SELECT句で付けた「age_class」という別名を使って,「GROUP BY age_class」という簡潔な書き方も許しているのですが,標準違反なので勧めません。

さて,それでは最後の問題。もっと複雑な基準でPersons集合をカットしてみましょう。これが解けたら,GROUP BY句に対するみなさんの理解は十分であることを保証しましょう。

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

著者プロフィール

ミック

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

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

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

コメント

コメントの記入