さて、今日はSQLシリーズ第3弾でSQLのビュー、サブクエリ、相関サブクエリについていつも通りサクッと解説していきたいと思います。
①ビューについて
さて、最初はビューについてですが、ビューとは「SELECT文そのもの」です。
通常はテーブルのデータを保存するのですが、ビューにおいてはSELECT文そのものを保存しておくことで、いつでもSELECT文を取り出せるようにしたものになります。
これから、ビューやサブクエリについて解説していくのですが、前回からのテーブルがどうなっていたかを確認しておきましょう。
現在のShohinテーブルは以下のようになっています。
shohin_id | shohin_mei | shohin_bunrui | hanbai_tanka | shiire_tanka | torokubi
-----------+----------------+---------------+--------------+--------------+------------
0001 | Tシャツ | 衣服 | 1000 | 500 | 2009-09-20
0002 | 穴あけパンチ | 事務用品 | 500 | 320 | 2009-09-11
0003 | カッターシャツ | 衣服 | 4000 | 2800 |
0004 | 包丁 | キッチン用品 | 3000 | 2800 | 2009-09-20
0005 | 圧力鍋 | キッチン用品 | 6800 | 5000 | 2009-01-15
0006 | フォーク | キッチン用品 | 500 | | 2009-09-20
0007 | おろしがね | キッチン用品 | 880 | 790 | 2008-04-28
0008 | ボールペン | 事務用品 | 100 | | 2009-11-11
0009 | 印鑑 | 事務用品 | 95 | 10 | 2009-11-30
(9 行)
ビューの使い方
ビューの使い方ですが、まずはビューを作る必要があります。
ビューの作り方は以下のようになります。
-- 商品分類ごとのレコード数をカウント
CREATE VIEW ShohinSum(shohin_bunrui, cnt_shohin)
AS
SELECT shohin_bunrui, COUNT(*)
FROM Shohin
GROUP BY shohin_bunrui;`
この「CREATE VIEW」でShohinSumという名前のビューを作っています。
そして、ビューを使うときには以下のようになります。
-- ShohinSumというビューを実行
SELECT shohin_bunrui, cnt_shohin
FROM ShohinSum;
実行結果は以下になります。
shohin_bunrui | cnt_shohin
---------------+------------
キッチン用品 | 4
衣服 | 2
事務用品 | 3
(3 行)
これで、商品分類ごとのレコード数を集計するSELECT文をまとめたビューが実行されました。
ビューの実行順序
次に、ビューの実行順序について少しだけ説明しておきます。
ビューは基本的に以下の順序で実行されています。
- 最初にビューに定義されたSELECT文が実行される
- その結果に対して、ビューをFROM句に指定したSELECT文が実行される
つまり、ビューは常に2つ以上のSELECT文が実行されていることを頭に置いておいてください。
ビューの注意事項
ビューを使う際の注意事項として、以下が挙げられます。
- ビューの上にビューを重ねることはなるべくしない
- ビューの定義でORDER BY句は使えない
- ビューの更新はかなり条件が厳しい
2についてはそのままの意味なので、解説は省きます。
1については、意味が分からないと思いますが、実はビューの上にビューを重ねることができます。
具体例を書きます。
たとえば、先ほどのShohinSumというビューの上にさらにShohinSumJimというビューを重ねるとします。
そうすると、次のようになります。
-- 商品分類ごとにグループ分けした後に、事務用品に絞ってレコード数をカウントする
CREATE VIEW ShohinSumJim(shohin_bunrui, cnt_shohin)
AS
SELECT shohin_bunrui, cnt_shohin
FROM ShohinSum
WHERE shohin_bunrui = '事務用品';
ビューを実行します。
SELECT shohin_bunrui, cnt_shohin
FROM ShohinSumJim;
これで、ちゃんとビューが実行できてしまうのです。
ですが、このようにビューの上にビューを重ねることはなるべくしない方がよいです。
理由は、パフォーマンスの低下を招くからです。
少なくとも、慣れないうちは重ねないようにしましょう。
続いて、3について。
実は、ビューの更新はかなり条件が厳しくなります。
どういうことかというと、たとえば以下のようにビューを更新しようとしたとします。
-- ビューに電化製品のレコードを挿入する
INSERT INTO ShohinSum VALUES ('電化製品', 5);
これはエラーとなります。
なぜ、エラーになるか。
それは、ShohinSumのビューはGROUP BY句によって集約されているからです。
ビューとはあくまで元となるテーブルから派生したものです。
ということは、ビューが変更されたら元のテーブルも変更しなければなりません。しかし、上のコードだと、商品IDも商品名も販売単価もわかりません。
では、いったいどうテーブルを変更したらいいかがわかりません。
よって、エラーになってしまうのです。
ところが、集約なしの下のコードだと成立します。
CREATE VIEW ShohinJim(shohin_id, shohin_mei, shohin_bunrui, hanbai_tanka, torokubi)
AS
SELECT *
WHERE shohin_bunrui = '事務用品';
ビューを実行します。
INSERT INTO ShohinJim VALUES('0009', '印鑑', '事務用品', 95, 10, '2009-11-30');
実行結果を確認します。
-- 確認
shohin_id | shohin_mei | shohin_bunrui | hanbai_tanka | shiire_tanka | torokubi
-----------+--------------+---------------+--------------+--------------+------------
0002 | 穴あけパンチ | 事務用品 | 500 | 320 | 2009-09-11
0008 | ボールペン | 事務用品 | 100 | | 2009-11-11
0009 | 印鑑 | 事務用品 | 95 | 10 | 2009-11-30
(3 行)
はい、問題なくレコードを挿入できました。
つまり、集約されたビューは更新できない。
集約されていないビューは更新できる。そういうこと。
ビューの更新は条件が厳しいと覚えておいてください。
②サブクエリについて
サブクエリとは、「使い捨てのビュー」のことです。
サブクエリはFROM句に直接ビュー定義のSELECT文を書きます。
例文。
SELECT shohin_bunrui, cnt_shohin
FROM (SELECT shohin_bunrui, COUNT(*) AS cnt_shohin
FROM Shohin
GROUP BY shohin_bunrui) AS ShohinSum;
実行結果。
shohin_bunrui | cnt_shohin
---------------+------------
キッチン用品 | 4
衣服 | 2
事務用品 | 3
(3 行)
ビューでは、先にShohinSumを定義しておいてから実行していましたが、サブクエリではFROM句に直接SELECT文を書いて実行します。
サブクエリでは、SELECT文の実行終了後にはそのSELECT文は消えてなくなります。
これが、使い捨ての意味です。
③スカラ・サブクエリについて
スカラ・サブクエリとは、単一の結果だけを返すサブクエリのことです。
ゆえに、戻り値(出力値)に対して、=(等価)や<>(否定)などの比較演算子を使うことができます。
たとえば、「販売単価が、全体の平均よりも高い商品だけを検索する」場合。
AVG関数を使って以下のように書くとエラーになってしまいます。
SELECT shohin_id, shohin_mei, hanbai_tanka
FROM Shohin
WHERE hanbai_tanka > AVG(hanbai_tanka);
これで、一見イケそうに見えるのですが、WHERE句に集約関数を使うことができないのでNGとなります。
じゃあ、どうするか。そこで、スカラ・サブクエリが登場します。
具体的には、以下のように書きます。
SELECT shohin_id, shohin_mei, hanbai_tanka
FROM Shohin
WHERE hanbai_tanka > (SELECT AVG(hanbai_tanka)
FROM Shohin);
これでは
(SELECT AVG(hanbai_tanka)
FROM Shohin)
の部分がスカラ・サブクエリになります。
スカラ・サブクエリは、必ず出力結果が一行だけになります。
そして、実行順序としては、まず内側のスカラ・サブクエリが実行され、次に外側のクエリが実行されます。
また、上のコードは実質的に以下と同じになります。
SELECT shohin_id, shohin_mei, hanbai_tanka
FROM Shohin
WHERE hanbai_tanka > 2097.5;
つまり、集約関数を使わずに実行している。そういうことです。
最後に確認しておきましょう。
-- 確認
shohin_id | shohin_mei | hanbai_tanka
-----------+----------------+--------------
0003 | カッターシャツ | 4000
0004 | 包丁 | 3000
0005 | 圧力鍋 | 6800
(3 行)
今回はWHERE句にスカラ・サブクエリを適用しましたが、スカラ・サブクエリは基本どこでも書けるので必要になれば使っていきましょう。
④相関サブクエリ
最後に、相関サブクエリを見ていきます。
まず、「商品分類ごとに平均販売単価より高い商品」を商品分類のグループから選び出すことを考えてみましょう。
つまり、各商品分類ごとに平均販売単価を計算し、その商品分類の中で平均販売単価より高い商品を選び出す、というのを各商品分類ごとにやった結果を出力するわけです。（かえってわかりずらい？笑）
前回までの内容で、以下のように書けば取り出せると思われるが…
SELECT shohin_id, shohin_mei, hanbai_tanka
FROM Shohin
WHERE hanbai_tanka > (SELECT AVG(hanbai_tanka)
FROM Shohin
GROUP BY shohin_bunrui);
ところがどっこい、これはエラーになります。
なぜか。
それは、スカラ・サブクエリは一つの結果しか出力できないからです。
上のコードだと、GROUP BYで商品分類ごとにグループ分けしているので、スカラ・サブクエリの結果は3つ同時に出てくることになります。
だから、エラーなわけです。
では、どのように書けばいいでしょうか。そこで出てくるのが相関サブクエリです。
正解のコードはこちら。
SELECT shohin_bunrui, shohin_mei, hanbai_tanka
FROM Shohin AS S1
WHERE hanbai_tanka > (SELECT AVG(hanbai_tanka)
FROM Shohin AS S2
WHERE S1.shohin_bunrui = S2.shohin_bunrui
GROUP BY shohin_bunrui);
出力結果は次の通り。
shohin_bunrui | shohin_mei | hanbai_tanka
---------------+----------------+--------------
事務用品 | 穴あけパンチ | 500
衣服 | カッターシャツ | 4000
キッチン用品 | 包丁 | 3000
キッチン用品 | 圧力鍋 | 6800
(4 行)
この書き方こそが相関サブクエリです。
スカラ・サブクエリと何が違うのかというと、相関サブクエリでは「各商品の販売単価と平均単価の比較を、同じ商品分類の中で行っている」ということです。
つまり、テーブル全体で比較するのではなく、同じ商品分類の中で行っているので結果が1つになっているわけです。
これを俗に「縛る」と言ったりします。「商品分類で縛って」比較を行っているわけですね。
また、S1やS2はテーブルを表しています。
実際のShohinテーブルとサブクエリの中のShohinテーブルを区別しているわけですね。
では今回はこのへんで。以上！
コメント