SQLアタマアカデミー

第4回 行か列か,それが問題だ~スカラサブクエリの使い方 (3)存在の階層

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

存在の階層

どんな言語にせよそうですが,使い始めてしばらくしたころ,直観的に理解できない癖のようなものに突き当たることが起こります。SQLにも当然,そういう「誰もが一度は通る道」がいくつか存在します。NULLのデータを検索するときに「col = NULL」と書いてしまうことなどがその典型ですが,それと並んで初心者がよく嵌ってしまう間違いが,⁠集約キー以外の列をSELECT句に書いてしまう」というものです。

具体的に,図8の商品テーブルをサンプルに考えてみましょう。

図8 サンプルのテーブル(その2)

Items

item_id
(商品ID)
item_name
(商品名)
item_group
(分類)
price
(値段)
001白菜野菜100
002にんじん野菜150
003ピーマン野菜150
004バナナ果物100
005キウイ果物200
006豚バラ肉類500
007ヨーグルト乳製品120
008クリーム乳製品400

さて,まずはリスト4のクエリとその実行結果図9から見てください。商品分類ごとに集計して,各分類の中で一番高い値段を求めています。野菜ならにんじんとピーマンの150円,果物ならキウイの200円。このSQLに疑問のある人はいないでしょう。

リスト4 各分類の中で一番高い値段を求めるクエリ

SELECT item_group, MAX(price)
  FROM Items
 GROUP BY item_group;

図9 各分類の中で一番高い値段

item_groupmax
野菜150
果物200
肉類500
乳製品400

では,このクエリをほんの少しだけ変えたコードを見てみましょうリスト5)⁠このクエリを作った意図は,⁠最高値の商品名も一緒に表示したい」というものです。多分,期待する結果としては,図10のようなものをイメージしているのでしょう。分類が「野菜」の行については,最高値の商品が2つあるため,そのうち1つを任意で表示しています。今回は,たまたま「にんじん」を使っています。

リスト5 各分類の中で一番高い値段と商品名を求めようとするクエリ

SELECT item_group, item_name, MAX(price)
  FROM Items
 GROUP BY item_group;

図10 リスト5を用いて求めたい結果

item_groupitem_namemax
野菜にんじん150
果物キウイ200
肉類豚バラ500
乳製品クリーム400

しかし,残念! このクエリは,My SQL以外ではエラーになります(My SQLの場合も,結果は図10のようにはなりません)⁠エラーになる理由は,item_nameが集約キー(GROUP BY句に指定される列)ではないからです。

本誌Vol.44の「SQLアタマ養成講座」でも述べましたが,GROUP BY句を使用した場合,SELECT句に書ける要素は次の3つに制限されます。

  • 定数
  • 集約キー
  • 集約関数

リスト5のクエリにおけるitem_nameは,このどれにも当てはまらないため,エラーになるわけです。では,なぜそもそもこの3種類以外の要素をSELECT句に記述することが許されないのでしょうか。

一言で言うと,これは存在の階層の差に起因するものです。GROUP BY句を使うということは,テーブルを小分けにして,文字通りいくつかのグループ(集合)を作るということです。そして,SQLにおいては,こうして作られた集合のほうが,集合に含まれる要素よりも一段レベルの高い,高次の存在と見なされることになっています。

そのため,GROUP BYによって作られたグループに対しては,もともとテーブルに存在していた行の属性は適用できません。適用できるのは,合計,平均,最大といった集合レベルの属性(=統計的な属性)だけです。これが,GROUP BY句を使ったら実質的に集約関数を使わないと意味のある結果が得られない理由です。リレーショナルデータベースにおいて,テーブルの列が理論上「属性(attribute)⁠と呼ばれるのも,この理由によります。したがって,My SQLの独自拡張は,要素と集合のレベルを混同したおかしなものなのです。

著者プロフィール

ミック

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

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

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

コメント

コメントの記入