Blank?=False

ゆるゆる仕事したいフリーランスエンジニアの記事

SQL ゼロから始めるデータベース操作 第5章複雑な問い合わせ VIEWについて

f:id:stonebeach-dakar:20160424222031p:plain

Oracleは放置(オイ)して、SQL本読書に戻ります。
今回はVIEWです。実は一度も作ったことがありません。
というよりクライアント側でVIEWに相当するものを作ってるんですよね。
DBMSのVIEWとはなにかをちゃんと勉強していきます。

今回のキーワード
CREATE VIEW DROP VIEW VIEWに対する更新

SQL ゼロからはじめるデータベース操作

SQL ゼロからはじめるデータベース操作

サンプルテーブル

今回もこいつを使わせて頂きます。
テーブル名はSampleです。
更新で色々いじったせいでちょっとデータが変わってます。

id Name VALUE Date Category
0001 2000000 2009-11-25 移動手段
0002 えんぴつ 200 2009-11-27 文房具
0003 キャビネット 12500 2009-11-27 家具
0004 消しゴム 12500 2009-12-11 文房具
0005 バイク 195000 2009-12-21 移動手段
0006 椅子 8500 2010-05-12 家具
0007 ボールペン 17000 2010-06-08 文房具
0008 クレヨン 1640 2010-06-15 文房具
0009 ガレージ 5282000 2010-09-01 未設定
0010 MacBookPro 0 2011-02-03 未設定

VIEWって何?

SELECT文が保存されたもの。実際のデータは持たない。
プログラミング的に言えば、関数の定義なのかな。
イメージはこんな感じです。
f:id:stonebeach-dakar:20160426215159p:plain

ACCESSを使ったことがある人なら、クエリのようなものと考えればわかりやすいです。
f:id:stonebeach-dakar:20160426215723p:plain

VIEWについて、本書では以下のように書かれています。

SQLの観点から見ると、ビューは「テーブルと同じもの」
これは、テーブルと同じようにFROM句でビューを指定できることから来ているのかな。

VIEWを使うメリット

毎日のデータチェック等でいちいちSELECT文を打つ手間が省けます。
データそのものは毎日更新されるので、チェックする人はVIEWを使うだけですぐにデータが見れるようになります。
簡単なSQL文ならいいですが、複雑なSQL文となると毎回打つのは手間ですし、打ち間違いで
ウガー!と叫ぶこともなくなります。

また、データをいちいち持つ必要がない(設計的には重複したデータがあっちこっちにあるのはそもそも良くない)ので、
記憶領域を圧迫しなくなります。

VIEWを使う時の注意点

パフォーマンスの低下

VIEWはテーブルと同じものとして考えられるので、
VIEWからVIEWを呼び出す事もできます。
f:id:stonebeach-dakar:20160426223108p:plain

こういうふうに、階層構造になったVIEWを多段VIEWと呼びます。
VIEWはデータがあるわけではなく、1つ1つのVIEWでSELECT文が動くので、
階層構造が深くなれば、その分処理速度が低下してしまいます。

本書では、鉄則として

ビューにビューを重ねることは(なるべく)しない。

と書かれています。

VIEWの作成と使用・削除方法

VIEWの作成

以下の例では、V_sampleというビューを作成しています。
Categoryが'文房具'のものを表示するようにしています。

CREATE VIEW V_bunbogu ( id, Name, VALUE, Date, Category )
    -> SELECT * FROM Sample
    -> WHERE Category='文房具';
Query OK, 0 rows affected (0.06 sec)

VIEWの名称、表示するフィールドのリストの後にASをつけて後は普段通りのSELECT文です。
ただし、ORDER BY句は使えないことに注意。(行には順序がないというルールのため)

以下の例は、GROUP BY文を使うVIEWです。
Categoryごとにグループ化し、それぞれのカテゴリ名とVALUEの合計と平均値を表示します。

mysql> CREATE VIEW V_GrCategory ( Category, SUM, AVG ) AS
    -> SELECT Category, SUM(VALUE), AVG(VALUE)
    -> FROM Sample
    -> GROUP BY Category;        
 Query OK, 0 rows affected (0.15 sec)


注意事項として、
VIEWではAS が絶対に必要です。SELECT句で列の別名に使うASとは違うので要注意!

VIEWの使用

Tableと同じような感覚で使えます。

mysql> SELECT * FROM V_sample;
+------+-----------------+-------+------------+-----------+
| id   | Name            | VALUE | Date       | Category  |
+------+-----------------+-------+------------+-----------+
| 0002 | えんぴつ        |   200 | 2009-11-27 | 文房具    |
| 0004 | 消しゴム        | 20000 | 2009-12-11 | 文房具    |
| 0007 | ボールペン      | 17000 | 2010-06-08 | 文房具    |
| 0008 | クレヨン        |  1640 | 2010-06-15 | 文房具    |
+------+-----------------+-------+------------+-----------+
4 rows in set (0.02 sec)


カテゴリでグループに分けるVIEWもこの通り。

mysql> SELECT * FROM V_GrCategory;
+--------------+---------+--------------+
| Category     | SUM     | AVG          |
+--------------+---------+--------------+
| 家具         |   21000 |   10500.0000 |
| 文房具       |   38840 |    9710.0000 |
| 未設定       | 5282000 | 2641000.0000 |
| 移動手段     | 2195000 | 1097500.0000 |
+--------------+---------+--------------+
4 rows in set (0.01 sec)


また、VIEWではORDER BYが使えないという制約があるので、
使うときにORDER BY句を使うことで並び替えることになります。
(ORDER BYも含めて全部自動でやってくれるとなると、スクリプトが必要なのかな?)

mysql> SELECT * FROM V_GrCategory ORDER BY SUM DESC, AVG DESC;
+--------------+---------+--------------+
| Category     | SUM     | AVG          |
+--------------+---------+--------------+
| 未設定       | 5282000 | 2641000.0000 |
| 移動手段     | 2195000 | 1097500.0000 |
| 文房具       |   38840 |    9710.0000 |
| 家具         |   21000 |   10500.0000 |
+--------------+---------+--------------+
4 rows in set (0.01 sec)

VIEWの削除

TABLEと同様、DROPで削除できます。

DROP VIEW V_bunbogu;

VIEWに対する更新

VIEWに対しても、UPDATE INSERT``DELETEがかけられます。
ただし、以下の制限があります。

  • SELECT句にDISTINCTが含まれていない
  • FROM句に含まれるテーブルが1つだけである
  • GROUP BY句を使用していない

本書では、HAVING句を使用していないというものもありましたが、
そもそもGROUP BYを使わなければHAVINGは使えないので、除外しました。

一言でまとめると、データを纏める(集約)しているVIEWに対しては更新不可能ということですね。
できない理由は、集約されてしまうと、更新しようとしても集約された元データのどこを更新すればいいのかDBMSが判断できないからですね。

上の制限にかからないVIEWに対してなら、更新ができます。 例えば、先ほどの文房具だけ表示するV_bunboguビューに対してなら更新できます。

mysql> INSERT INTO V_bunbogu VALUES ('0011', 'マジック', 50, '2012-03-01', '文房具');
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM V_sample;
+------+-----------------+-------+------------+-----------+
| id   | Name            | VALUE | Date       | Category  |
+------+-----------------+-------+------------+-----------+
| 0002 | えんぴつ        |   200 | 2009-11-27 | 文房具    |
| 0004 | 消しゴム        | 20000 | 2009-12-11 | 文房具    |
| 0007 | ボールペン      | 17000 | 2010-06-08 | 文房具    |
| 0008 | クレヨン        |  1640 | 2010-06-15 | 文房具    |
| 0011 | マジック        |    50 | 2012-03-01 | 文房具    |
+------+-----------------+-------+------------+-----------+
5 rows in set (0.00 sec)


ちょっと気になったので、V_bunboguに文房具カテゴリ以外のデータを追加してみます。

mysql> INSERT INTO V_sample VALUES ('0012', '一輪車', 2500, '2013-12-25', '移動手段');
Query OK, 1 row affected (0.01 sec)

できちゃった・・・
条件などは関係なく、VIEWを経由して元のテーブルに更新がかかる感じなのでしょうか。

更新処理後の元のテーブルを確認すると、

mysql> SELECT * FROM Sample;
+------+--------------------+---------+------------+--------------+
| id   | Name               | VALUE   | Date       | Category     |
+------+--------------------+---------+------------+--------------+
| 0001 | 車                 | 2000000 | 2009-11-25 | 移動手段     |
| 0002 | えんぴつ           |     200 | 2009-11-27 | 文房具       |
| 0003 | キャビネット       |   12500 | 2009-11-27 | 家具         |
| 0004 | 消しゴム           |   20000 | 2009-12-11 | 文房具       |
| 0005 | バイク             |  195000 | 2009-12-21 | 移動手段     |
| 0006 | 椅子               |    8500 | 2010-05-12 | 家具         |
| 0007 | ボールペン         |   17000 | 2010-06-08 | 文房具       |
| 0008 | クレヨン           |    1640 | 2010-06-15 | 文房具       |
| 0009 | ガレージ           | 5282000 | 2010-09-01 | 未設定       |
| 0010 | MacBookPro         |       0 | 2011-02-03 | 未設定       |
| 0011 | マジック           |      50 | 2012-03-01 | 文房具       |
| 0012 | 一輪車             |    2500 | 2013-12-25 | 移動手段     |
+------+--------------------+---------+------------+--------------+

ちゃんとデータが追加されています。


まとめ

VIEWに対して更新というのが間違ってないかと思いました。
元に、PostgreSQLはデフォルトではVIEWは読み取り専用です。

VIEWに対する更新は、テーブルに対する更新を使う場合に比べてどのようなメリットがあるのか、が気になります。
ググっても更新の説明ばかりでメリットについての説明はなかったので、ほとんどないのでしょうか。
現場で実際にVIEWに対する更新を使うシチュエーションってどんなのがあるんでしょうか。

個人的には、「やろうと思えばできるけどあんまり使わない方がいい」という印象です。

次回は、サブクエリ・相関サブクエリについてやっていきます。