ビューとマテリアライズドビュー
通常のビューが返す値と、再集計を必要とする保存済みの値を、原表の更新時点と対応させて説明します。
説明
ビューはSELECT文に名前を付け、表のように参照できる仕組みです。通常のビューが保持するのは問合せの定義であり、検索結果の行ではありません。一方、集計した行を表に保存すると、原表が変わっても保存した値はそのまま残ります。
注文表から店舗別売上を求めるビューと、同じ結果を保存した表を作ります。注文の追加・削除後に両方を検索し、保存済みの集計を更新する手順と、更新に失敗した場合の取消しを確認します。
通常のビューが返す値と、再集計を必要とする保存済みの値を、原表の更新時点と対応させて説明します。
1注文と集計の定義
注文はA店が100円と200円、B店が50円です。店別の合計を求めるSELECTをshop_totalsという名前で登録します。
CREATE TABLE orders(id INTEGER PRIMARY KEY,shop TEXT NOT NULL,amount INTEGER NOT NULL CHECK(amount>=0)) STRICT;
INSERT INTO orders VALUES(1,'A',100),(2,'A',200),(3,'B',50);
CREATE VIEW shop_totals ASSELECTに名前を付ける SELECT shop,SUM(amount)店舗内の注文金額を合計する AS total FROM orders GROUP BY shop;
SELECT * FROM shop_totals ORDER BY shop;A店300円、B店50円です。SELECTするたびにビューの定義に従ってordersを参照します。
2検索結果を表に保存
同じ集計結果をsaved_totalsへコピーします。これは通常の表を使った保存方法です。自動更新の仕組みは付いていないため、後で明示的に再集計します。
CREATE TABLE saved_totals(shop TEXT PRIMARY KEY,total INTEGER NOT NULL CHECK(total>=0)) STRICT;
INSERT INTO saved_totals SELECT集計結果を表へ挿入する * FROM shop_totals;
SELECT type,name,rootpage表などのB-treeの先頭ページ番号 FROM sqlite_schema WHERE name IN ('orders','shop_totals','saved_totals') ORDER BY name;
SELECT * FROM saved_totals ORDER BY shop;ビューのrootpageは0、二つの表には格納領域の先頭ページ番号があります。保存表には現在の300円と50円が入り、ビューの定義とは別に行として保持されます。
3注文追加後の参照
A店へ100円の注文を追加して、二つの結果を一つの表に並べます。
INSERT INTO orders VALUES(4,'A',100);
SELECT 'view' AS source,shop,total FROM shop_totals UNION ALL二つの結果を出所の列付きで並べる SELECT 'saved',shop,total FROM saved_totals ORDER BY source,shop;ビューのA店は400円、保存表は300円です。保存表の読出しでは集計をやり直さないため、元の注文が増えても値は変わりません。
4最後の注文が消えた店舗
B店の注文は1件だけです。それを削除するとGROUP BYの対象からB店がなくなります。
DELETE FROM orders注文を削除する WHERE shop='B';
SELECT 'view' AS source,shop,total FROM shop_totals UNION ALL SELECT 'saved',shop,total FROM saved_totals ORDER BY source,shop;ビューはA店400円の1行です。保存表にはB店50円も残ります。再集計では金額の変更だけでなく、不要になった集計行の削除も必要です。
5全体を再集計
保存表を空にして現在の集計結果を入れ直します。二つの文を一つのトランザクションにまとめます。
BEGIN更新を一つのトランザクションにまとめる;
DELETE FROM saved_totals古い集計行を取り除く;
INSERT INTO saved_totals SELECT * FROM shop_totals;
COMMIT新しい集計行を確定する;
SELECT * FROM saved_totals ORDER BY shop;保存表もA店400円だけになりました。DELETEとINSERTをまとめて確定することで、更新を途中まで確定させずに済みます。
6再集計の途中で制約違反
次は保存表を空にした後、負の集計値を入れて失敗させます。SQLiteの通常の制約違反は、失敗した文を取り消しますが、それ以前の文まで自動的に戻すわけではありません。
BEGIN;
DELETE FROM saved_totals;
INSERT INTO saved_totals制約に反する負の値を挿入する VALUES('A',-1);CHECK制約でINSERTが拒否されました。まだトランザクションの途中であり、先に実行したDELETEはこの接続から見える状態に残っています。
7取消しで元の集計を戻す
失敗後の行数を確認してから、トランザクション全体を取り消します。
SELECT COUNT(*) AS before_rollback FROM saved_totals;
ROLLBACKBEGIN以降の変更を取り消す;
SELECT * FROM saved_totals ORDER BY shop;取消し前は0行、取消し後はA店400円に戻りました。再集計に失敗したときは、成功した前半の変更も含めて戻す必要があります。
8ビューを直接更新する場合
集計ビューのtotalを書き換えようとしても、どの注文をいくら変更するのかは決まりません。SQLiteのビューは、INSTEAD OFトリガーを定義していなければ直接更新できません。
UPDATE shop_totalsビューへの更新を試す SET total=999 WHERE shop='A';SQLiteはビューを更新できないというエラーを返します。ビューは保存表の別名ではなく、原表から結果を求める定義です。
9ビューを削除した後
最後にビューの定義だけを削除します。元の注文と保存済みの集計は、それぞれ独立した表として残ります。
DROP VIEW shop_totalsビューの定義を削除する;
SELECT * FROM orders ORDER BY id;
SELECT * FROM saved_totals ORDER BY shop;ordersには100円・200円・100円の3件、saved_totalsには400円が残ります。定義の削除と、元データや保存結果の削除は別の操作です。
まとめ
通常のビューは問合せの定義を持ち、参照時に原表から結果を求めます。
集計結果を表に保存すると読出し時の再計算を省けますが、更新と削除を反映する処理が必要です。
再集計を一つのトランザクションにまとめ、失敗時にROLLBACKすると、以前の保存結果へ戻せます。
操作履歴と解説を振り返れます。
SQLite 3.54.0
CREATE TABLE orders(id INTEGER PRIMARY KEY,shop TEXT NOT NULL,amount INTEGER NOT NULL CHECK(amount>=0)) STRICT;
INSERT INTO orders VALUES(1,'A',100),(2,'A',200),(3,'B',50);
CREATE VIEW shop_totals AS SELECT shop,SUM(amount) AS total FROM orders GROUP BY shop;
SELECT * FROM shop_totals ORDER BY shop;╭──────┬───────╮ │ shop │ total │ ╞══════╪═══════╡ │ A │ 300 │ │ B │ 50 │ ╰──────┴───────╯
CREATE TABLE saved_totals(shop TEXT PRIMARY KEY,total INTEGER NOT NULL CHECK(total>=0)) STRICT;
INSERT INTO saved_totals SELECT * FROM shop_totals;
SELECT type,name,rootpage FROM sqlite_schema WHERE name IN ('orders','shop_totals','saved_totals') ORDER BY name;
SELECT * FROM saved_totals ORDER BY shop;╭───────┬──────────────┬──────────╮ │ type │ name │ rootpage │ ╞═══════╪══════════════╪══════════╡ │ table │ orders │ 2 │ │ table │ saved_totals │ 3 │ │ view │ shop_totals │ 0 │ ╰───────┴──────────────┴──────────╯ ╭──────┬───────╮ │ shop │ total │ ╞══════╪═══════╡ │ A │ 300 │ │ B │ 50 │ ╰──────┴───────╯
INSERT INTO orders VALUES(4,'A',100);
SELECT 'view' AS source,shop,total FROM shop_totals UNION ALL SELECT 'saved',shop,total FROM saved_totals ORDER BY source,shop;╭────────┬──────┬───────╮ │ source │ shop │ total │ ╞════════╪══════╪═══════╡ │ saved │ A │ 300 │ │ saved │ B │ 50 │ │ view │ A │ 400 │ │ view │ B │ 50 │ ╰────────┴──────┴───────╯
DELETE FROM orders WHERE shop='B';
SELECT 'view' AS source,shop,total FROM shop_totals UNION ALL SELECT 'saved',shop,total FROM saved_totals ORDER BY source,shop;╭────────┬──────┬───────╮ │ source │ shop │ total │ ╞════════╪══════╪═══════╡ │ saved │ A │ 300 │ │ saved │ B │ 50 │ │ view │ A │ 400 │ ╰────────┴──────┴───────╯
BEGIN;
DELETE FROM saved_totals;
INSERT INTO saved_totals SELECT * FROM shop_totals;
COMMIT;
SELECT * FROM saved_totals ORDER BY shop;╭──────┬───────╮ │ shop │ total │ ╞══════╪═══════╡ │ A │ 400 │ ╰──────┴───────╯
BEGIN;
DELETE FROM saved_totals;
INSERT INTO saved_totals VALUES('A',-1);Error: CHECK constraint failed: total>=0
SELECT COUNT(*) AS before_rollback FROM saved_totals;
ROLLBACK;
SELECT * FROM saved_totals ORDER BY shop;╭─────────────────╮ │ before_rollback │ ╞═════════════════╡ │ 0 │ ╰─────────────────╯ ╭──────┬───────╮ │ shop │ total │ ╞══════╪═══════╡ │ A │ 400 │ ╰──────┴───────╯
UPDATE shop_totals SET total=999 WHERE shop='A';Parse error: cannot modify shop_totals because it is a view
DROP VIEW shop_totals;
SELECT * FROM orders ORDER BY id;
SELECT * FROM saved_totals ORDER BY shop;╭────┬──────┬────────╮ │ id │ shop │ amount │ ╞════╪══════╪════════╡ │ 1 │ A │ 100 │ │ 2 │ A │ 200 │ │ 4 │ A │ 100 │ ╰────┴──────┴────────╯ ╭──────┬───────╮ │ shop │ total │ ╞══════╪═══════╡ │ A │ 400 │ ╰──────┴───────╯
演習完了