SQLアタマアカデミー

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

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

目的のために手段を選ぶ

さて,理論的な理解はともかく,最高値をつけた商品名も一緒に表示したい,という要望もあるでしょう。これから,そういう場合に対処する方法をいくつか紹介します。

まず,筆者が一番妥当な案と思うのは,次のように相関サブクエリを使うものですリスト6)⁠

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

SELECT item_group, item_name, price
  FROM Items I1
 WHERE price = (SELECT MAX(price)
                  FROM Items I2
                 WHERE I1.item_group = I2.item_group …❶ ← この結合条件がポイント
                 GROUP BY item_group);

この方法だと,グループ内で最高値をつけた商品名が網羅的に表示されます。したがって,⁠野菜」グループのように複数の商品が同点首位の場合,それらの行がすべて表示されます図11)⁠

リスト6で重要なポイントは2点あります。まず1つ目は,サブクエリ内で使用している「I1.item_group =I2.item_group」という結合条件です。これによって,商品グループ内に制限した最高値を求めることができます。したがって,この条件(リスト6-の行)をなくしたクエリは,エラーとなります。

図11 リスト6の実行結果

なぜエラーになるかというと,あたりまえですが次のような複数行を返すからです。つまり,このサブクエリが,スカラサブクエリではないからです。

  • 150
  • 200
  • 500
  • 400

複数行を返すサブクエリを,スカラ演算子である「=」で受けることはできません注1)⁠そう,ここが2つ目のポイントなのですが,この場合,正しい結果を得るには,サブクエリはスカラサブクエリでなくてはならないのです。

正確には,リスト6のサブクエリも複数行を返しています。しかし,リスト6-の結合条件を付加することで,計算を「グループ単位」で行うよう指定できます。そして,1つのグループ内では,最大値は常に1つであることが保証されます。だから,事実上スカラサブクエリとして扱われるため,エラーにはなりません。混乱しがちですがここは重要なポイントなので,よく理解してください。

さて,話を元に戻しましょう。筆者は先ほど,複数の商品が同点首位の場合はそれらをすべて結果に表示するのが良いのではないか,と言いました。ですが要件によっては,あくまで「1グループ1行」という結果にこだわりたい場合もあるでしょう。

そんなときは,最初のクエリをリスト7のように変えましょう。実行結果は図12のようになります。

リスト7 1グループ1行にこだわるクエリ(その1)

SELECT item_group, MAX(item_name) AS item_name, MAX(price)
  FROM Items I1
 WHERE price = (SELECT MAX(price)
                  FROM Items I2
                 WHERE I1.item_group = I2.item_group
                 GROUP BY item_group)
 GROUP BY item_group;

図12 リスト7の実行結果

外側のクエリにおいて,商品グループで集約すれば,必ずグループ単位で一意になることが保証できます。この場合,問題の「野菜」グループについて,ピーマンとにんじんのどちらを取るかについては特に優先順位はないはずですから,MAX関数を使って適当に選んでいます。MAXは集約関数ですから,GROUP BY句を使った場合にも使用できます。もちろん,MINを使ってもかまいません。

注1)
逆に言うと,ここをINやANYのような非スカラ演算子で受ければ,構文上のエラーは出ません。ただし,選択結果は全然違うものになります。

トリッキーな手段

これと同じ結果を求める方法が,もうひとつあります。これもやはりスカラサブクエリを応用するのですが,見る人によってはかなり衝撃的なものですリスト8)⁠

リスト8 1グループ1行にこだわるクエリ(その2)

SELECT item_group,
       (SELECT MAX(item_name)                          
          FROM Items I2                                
         WHERE I1.item_group = I2.item_group           
           AND I2.price = MAX(I1.price)) AS item_name, 
       MAX(price)
  FROM Items I1
 GROUP BY item_group;

基本的な考え方はリスト7と同じです。商品グループ単位で最高値をつけた商品の集合に限定し,その中から「MAX(item_name)」によって,適当な商品名を1つ選び出しているのです。だから,このスカラサブクエリも,商品グループについて1行の結果だけを返すため,エラーにならないのです注2)⁠

しかし,私たちがSQLを習うときに教えられる原則では,WHERE句に集約関数を書いてはならないはずです。だからこそ,次のようなクエリはエラーになるのです。

SELECT item_name
  FROM Items
 WHERE price = MAX(price);

すべてのデータベースが,このクエリに対しては「WHERE句で集約関数は使ってはならない」というエラーメッセージを返します。それなのに,リスト8のクエリでは平然と「I2.price = MAX(I1.price)」と,集約関数をWHERE句で使っています。いったい,なぜこんなことが許されるのでしょうか。

これは,SQLの内部動作についての理解度を測る格好の問題なので,演習問題にしましょう。皆さん,理由を考えてみてください。

注2)
とはいえ,このクエリは人間だけでなくデータベースエンジンにとってもトリッキーなようで,正しく動作するのは,今のところSQL Server,Postgre SQLなどの一部のデータベースだけです。

著者プロフィール

ミック

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

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

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

コメント

コメントの記入