複雑な問い合わせ
ビュー
まずビューとテーブルの違いはデータを保存しているかしていないか。
テーブルを作成し。INSERT文でデータを格納するとデータベースにデータを保存する。
保存されている場所はコンピュータ内の記憶装置 （ハードディスクなど)
SELECT文は記憶装置からデータを引っ張り出し計算を行う。
一方ビューはどこにもデータを保存していない。
ビューが保存しているのはSELECT文そのもの。
ビューのメリットはデータを保存したないので記憶装置のデータ領域を節約できる。
もう一つのメリットは頻繁に使うSELECT文を毎回書く必要がなく、使い回すことができる。
ビューの作り方
create view shohinsum(shohin_bunrui,cnt_shohin) --ビューの列名
as --asは省略不可
select shohin_bunrui, count(*) --ビューの定義
from shohin
group by shohin_bunrui;
select shohin_bunrui,cnt_shohin
from shohinsum;
shohin_bunrui | cnt_shohin
---------------+------------
キッチン用品 | 4
衣服 | 2
事務用品 | 2
(3 rows)
上記は商品分類ごとに商品数を集計した結果を保存している。
なので毎回GROUP BY と COUNT関数を使ってSELECTを書く必要がない。
また商品テーブルのデータが更新されたらビューも更新される。
さらにビューからビューを作成することもできる
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;
shohihn_bunrui | cnt_shohin
----------------------+------------
事務用品 | 2
(1 row)
注意として、View定義にはORDER BY句が使えない。
というより使ってはいけない。
理由はテーブルもビューも「行には順序がない」と定められているから。
ビューの更新
以下の条件でビューの更新ができる
1、SELECT句にDISTINCTが含まれていない
2、FROM句に含まれるテーブルが1つだけである
3、GROUP BY句が含まれていない
4、HAVING句を使用していない
もっとまとめるとFROM句に複数テーブルある場合や、集約されたものはビューでは更新ができないということ。
insert into shohinsum values ('電化製品', 5);
のように追加しようとしても、もとのビューの派生元の商品テーブルでは、分類が電化製品ということはわかっても、5行あるデータのIDなどが不明なため、ビュー元のテーブルが更新できない。
なので、集約されたものはビューでは更新ができない。
なので集約なしのビューは更新ができる
create view shohinjim (shohin_id, shohin_mei, shohin_bunrui, hanbai_tanka, shiire_tanka, torokubi)
as
select *
from shohin
where shohin_bunrui = '事務用品';
select * from shohinjim;
shohin_id | shohin_mei | shohin_bunrui | hanbai_tanka | shiire_tanka | torokubi
-----------+-------------+---------------+--------------+--------------+------------
0002 | 穴あけパンチ | 事務用品 | 500 | 320 | 2009-09-11
0008 | ボールペン | 事務用品 | 100 | | 2009-11-11
(2 rows)
insert into shohinjim values('0009','印鑑','事務用品',95,19,'2009-11-30');
INSERT 0 1
--ビューが更新されている
select * from shohinjim;
shohin_id | shohin_mei | shohin_bunrui | hanbai_tanka | shiire_tanka | torokubi
-----------+--------------+---------------+--------------+--------------+------------
0002 | 穴あけパンチ | 事務用品 | 500 | 320 | 2009-09-11
0008 | ボールペン | 事務用品 | 100 | | 2009-11-11
0009 | 印鑑 | 事務用品 | 95 | 19 | 2009-11-30
(3 rows)
--元のテーブルも更新されている
select * from 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 | 19 | 2009-11-30
(9 rows)
ビューの削除
drop view ビュー名
drop view shohinjim;
DROP VIEW
ただしビューにビューが紐づく多段ビューの場合はエラーになる。
drop view shohinsum;
ERROR: cannot drop view shohinsum because other objects depend on it
DETAIL: view shohinsumjim depends on view shohinsum
HINT: Use DROP ... CASCADE to drop the dependent objects too.
多段ビュー(ビューからビューを作成している) で、作成元のビューを削除する場合は以下のようにする
drop view ビュ-名 cascade;
drop view shohinsum cascade;
DROP VIEW
これでshohinsumjimとshohinsumが削除された。
サブクエリ
サブクエリは使い捨てのビュー(SELECT文)です。
ビューと異なり、SELECT文の実行終了後に消去される。
create view shohinsum (shohin_bunrui, cnt_shohin)
as
select shohin_bunrui, count(*)
from shohin
group by shohin_bunrui;
select shohin_bunrui, cnt_shohin
from shohinsum;
shohin_bunrui | cnt_shohin
---------------+------------
キッチン用品 | 4
衣服 | 2
事務用品 | 2
(3 rows)
--上記をサブクエリで表現すると以下になる
--2
select shohin_bunrui, cnt_shohin
--1
from (select shohin_bunrui, count(*) as cnt_shohin
from shohin
group by shohin_bunrui)
--2
as shohinsum;
shohin_bunrui | cnt_shohin
---------------+------------
キッチン用品 | 4
衣服 | 2
事務用品 | 2
(3 rows)
--結果は同じとなる
--1のFROM句の中のSELECT文をサブクエリという。まず1のサブクエリが実行される
select shohin_bunrui, count(*) as cnt_shohin
from shohin
group by shohin_bunrui
--1の結果にたいして、外側のSELECT文が実行される
select shohin_bunrui, cnt_shohin
from shohinsum;
サブクエリの中にサブクエリを書くような入れ子をつくることもできる
select shohin_bunrui, cnt_shohin
from (select *
from (select shohin_bunrui, count(*) as cnt_shohin
from shohin
group by shohin_bunrui ) as shohinsum
where cnt_shohin < 4)
as shohinsum2;
shohin_bunrui | cnt_shohin
---------------+------------
衣服 | 2
事務用品 | 2
(2 rows)
ただしサブクエリの入れ子が深くなればなるほど、読みにくくなるので注意
スカラ
スカラとは単一のという意味。
サブクエリは複数行を結果として返すのに対し、スカラ・サブクエリは1行1列の戻り値を返す。
たとえば販売単価が、返金販売単価より上のものと取り出したい場合、以下のようなsqlを書きたくなるが、WHERE句に集約関数を書くことはできない。
select shohin_id, shohin_mei, hanbai_tanka
from shohin
where hanbai_tanka > avg(hanbai_tanka);
ではどのように書くか。
--まず販売単価の平均は以下のようにかける。またこれが平均販売単価のスカラ・サブクエリという
select avg(hanbai_tanka) from shohin;
avg
-----------------------
2097.5000000000000000
(1 row)
--このスカラをWHERE句に書くことで、求めたい結果が得られる。
select shohin_id, shohin_mei, hanbai_tanka
from shohin
where hanbai_tanka > (select avg(hanbai_tanka) from shohin);
shohin_id | shohin_mei | hanbai_tanka
-----------+----------------+--------------
0003 | カッターシャツ | 4000
0004 | 包丁 | 3000
0005 | 圧力鍋 | 6800
(3 rows)
--サブクエリ同様にスカラ・サブクエリもスカラ部分が実行され、外側のクエリが実行される。
スカラ・サブクエリはSELECT句、WHERE句、GROUP BY句、HAVING句、ORDER BY句などあらゆる場所に書くことができる。
select shohin_id
shohin_mei,
hanbai_tanka,
(select avg(hanbai_tanka) from shohin) as avg_tanka
from shohin;
shohin_mei | hanbai_tanka | avg_tanka
------------+--------------+-----------------------
0001 | 1000 | 2097.5000000000000000
0002 | 500 | 2097.5000000000000000
0003 | 4000 | 2097.5000000000000000
0004 | 3000 | 2097.5000000000000000
0005 | 6800 | 2097.5000000000000000
0006 | 500 | 2097.5000000000000000
0007 | 880 | 2097.5000000000000000
0008 | 100 | 2097.5000000000000000
(8 rows)
--以下は商品分類ごとの平均単価が商品全ての平均単価より大きいもの、という意味
select shohin_bunrui, avg(hanbai_tanka) -- avg(hanbai_tanka)とgroup by shohin_bunruiで分類ごとの販売単価の平均
from shohin
group by shohin_bunrui
having avg(hanbai_tanka) > (select avg(hanbai_tanka) from shohin);
shohin_bunrui | avg
---------------+-----------------------
キッチン用品 | 2795.0000000000000000
衣服 | 2500.0000000000000000
(2 rows)
相関サブクエリ
相関サブクエリは以下のようなグループごとの平均でそれぞれの条件以上のものを取り出したいときに使用する。
--以下のようなグループごとの平均以上のものを取り出したいとする
select avg(hanbai_tanka) from shohin group by shohin_bunrui;
avg
-----------------------
2795.0000000000000000
2500.0000000000000000
300.0000000000000000
(3 rows)
--すると以下のような文を連想するがうまく行かない。理由はwhere区でサブクエリを使用する場合は必ず結界は1行だから。
select shohin_bunrui, shohin_mei, hanbai_tanka
from shohin
where hanbai_tanka > ( select avg(hanbai_tanka) --ここで複数行の商品分類ごとの販売単価の平均が選択されている。
from shohin
group by shohin_bunrui);
ERROR: more than one row returned by a subquery used as an expression
各商品の販売単価と平均単価の比較を、同じ商品分類の中で行う
--- まずはs1
select shohin_bunrui, shohin_mei, hanbai_tanka
from shohin as s1;
shohin_bunrui | shohin_mei | hanbai_tanka
---------------+------------+--------------
衣服 | Tシャツ | 1000
事務用品 | 穴あけパンチ | 500
衣服 | カッターシャツ | 4000
キッチン用品 | 包丁 | 3000
キッチン用品 | 圧力鍋 | 6800
キッチン用品 | フォーク | 500
キッチン用品 | おろしがね | 880
事務用品 | ボールペン | 100
(8 rows)
---次にs2
select avg(hanbai_tanka)
from shohin
group by shohin_bunrui;
avg
-----------------------
2795.0000000000000000
2500.0000000000000000
300.0000000000000000
(3 rows)
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 rows)
上記の相関サブクエリは以下のようなイメージ
