SQLのお勉強 15.サブクエリ
2020-09-23
こんにちは、0371です。
今回は、SQLのお勉強をしたいと思います。
基本情報技術者試験で出題される範囲を中心に進めます。
お勉強
※ PostgreSQLはインストール済みかつ、パスも通しているという前提で進めていきます。
分からないという方は、以下の記事から始めてください。
dockerを使って、再構築が容易なSQLの練習場を作成する
"https://0371.blog/docker-sql"
前回の記事はこちら
SQLのお勉強 14.view
"https://0371.blog/sql-14"
サブクエリ
サブクエリとは、使い捨てのviewです。
fromにサブクエリを入れて使います。
副問合せとも呼ばれていたりします。
select 列名1, サブクエリの列名1 from (select 列名1, サブクエリの列名1 from テーブル名) as サブクエリの名前;
実際にサブクエリを使用してみます。
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
事務用品 | 2
(3 rows)
スカラ・サブクエリという問合せもあります。 「必ず1行1列だけの戻り値を返す」クエリです。
スカラ・サブクエリでは、以下のような状況を解決できます。
select shohin_id, shohin_mei, hanbai_tanka from Shohin where hanbai_tanka > avg(hanbai_tanka);
// whereの中では、集約関数が使えないのでエラーになります。
ERROR: aggregate functions are not allowed in WHERE
LINE 1: ...ei, hanbai_tanka from Shohin where hanbai_tanka > avg(hanbai...
この状況に対して、スカラ・サブクエリを使用するとこのようになります。
まずは、avg(hanbai_tanka)の部分をselect文で取得してみます。
select avg(hanbai_tanka) from Shohin;
1行1列だけの戻り値を返しているので、これがスカラ・サブクエリです。
avg
-----------------------
2097.5000000000000000
(1 row)
次に、これを実行したかったselect文に入れたいと思います。
select shohin_id, shohin_mei, hanbai_tanka from Shohin where hanbai_tanka > (select avg(hanbai_tanka) from Shohin);
このように記述することによって、販売単価の平均(2097.5)よりも大きい値の商品名を取得することができるようになりました。
shohin_id | shohin_mei | hanbai_tanka
-----------+----------------+--------------
0003 | カッターシャツ | 4000
0004 | 包丁 | 3000
0005 | 圧力鍋 | 6800
(3 rows)
スカラ・サブクエリ
スカラ・サブクエリは、定数や列名を書くことのできる場所全てに記述することが可能です。
select文
select shohin_id, shohin_mei, hanbai_tanka, (select avg(hanbai_tanka) from Shohin) as avg_tanka from Shohin;
shohin_id | shohin_mei | hanbai_tanka | avg_tanka
-----------+----------------+--------------+-----------------------
0001 | Tシャツ | 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)
having
select shohin_bunrui, avg(hanbai_tanka) from Shohin group by shohin_bunrui having avg(hanbai_tanka) > (select avg(hanbai_tanka) from Shohin);
group byで商品分類ごとの平均販売単価を出した後に、havingで商品全体の平均販売単価と比較しています。
shohin_bunrui | avg
---------------+-----------------------
キッチン用品 | 2795.0000000000000000
衣服 | 2500.0000000000000000
(2 rows)
相関サブクエリ
相関サブクエリは、小分けにしたグループ内での比較をするときに使用します。
「各グループの平均販売単価よりも高く売られている商品」を取得したいとき、前述のスカラ・サブクエリだと、エラーが発生してしまいます。
select shohin_bunrui, shohin_mei, hanbai_tanka from Shohin where hanbai_tanka > (select avg(hanbai_tanka) from Shohin group by shohin_bunrui);
スカラ・サブクエリが3行を戻り値として返すからです。
ERROR: more than one row returned by a subquery used as an expression
これを防ぐために、相関サブクエリを使用します。
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);
whereの中は、「各商品の販売単価と平均単価の比較を、同じ商品分類の中で行う」という意味になります。
shohin_bunrui | shohin_mei | hanbai_tanka
---------------+----------------+--------------
事務用品 | 穴あけパンチ | 500
衣服 | カッターシャツ | 4000
キッチン用品 | 包丁 | 3000
キッチン用品 | 圧力鍋 | 6800
(4 rows)
結合条件(where)は、必ずサブクエリの中に書いてください。
今日はここまで。
参考書籍
SQL 第2版 ゼロからはじめるデータベース操作
"https://amzn.to/2QYMBJD"
今日の一言
サブクエリが分かったのでSQLはマスターしたも同然！！！！
次回の記事はこちら
SQLのお勉強 16.like
"https://0371.blog/sql-16"