SELECT
DBの構築ができたので,ここから本格的にSQLを試していこうと思う.まずはSELECT.
SELECTはレコードを選択するSQLである.SELECTの基本形は以下である.
SELECT フィールド FROM テーブル WHERE 条件;フィールド,テーブルは複数指定可能である.条件一つだが,複数の条件をANDやORで結合して一つの条件とすることができる.以下はテーブルclusterからmemoryが1024以上のレコードを選択する.フィールドに*を指定することで全フィールドを表示している.
benchmark=> SELECT * FROM cluster WHERE memory >= 1024; id | name | cpunum | cpuclock | memory | network ----+------+--------+----------+--------+------------- 1 | hoge | 256 | 2 | 1024 | 1000BASE-TX 4 | var | 512 | 3 | 2048 | (2 rows)ここで,全フィールドではなく,例えばnameとmemoryだけを表示したければ,以下のようにする.
benchmark=> SELECT name, memory FROM cluster WHERE memory >= 1024; name | memory ------+-------- hoge | 1024 var | 2048 (2 rows)
いろんな条件をためしてみる.memoryが1024以上かつ1500以下というのをANDを使用して書いてみた.
benchmark=> SELECT name, memory FROM cluster WHERE memory >= 1024 and memory <= 1500; name | memory ------+-------- hoge | 1024 (1 row)
NULLかどうかはIS NULL,IS NOT NULLで指定する.以下はnetworkがNULLのものを選択.
benchmark=> SELECT * FROM cluster WHERE network IS NULL; id | name | cpunum | cpuclock | memory | network ----+------+--------+----------+--------+--------- 2 | fuga | | | | 3 | foo | | | 256 | 4 | var | 512 | 3 | 2048 | (3 rows)逆にnetworkがNULLでないものを選択.
benchmark=> SELECT * FROM cluster WHERE network IS NOT NULL; id | name | cpunum | cpuclock | memory | network ----+------+--------+----------+--------+------------- 1 | hoge | 256 | 2 | 1024 | 1000BASE-TX (1 row)
nameがfではじまるものをLIKEを使用して選択.
benchmark=> SELECT * FROM cluster WHERE name LIKE 'f%'; id | name | cpunum | cpuclock | memory | network ----+------+--------+----------+--------+--------- 2 | fuga | | | | 3 | foo | | | 256 | (2 rows)ありがたいことにPostgreSQLでは正規表現も使用できる.正規表現と一致するものは~,一致しないものは!~である.以下はnameが3文字のものを選択.
benchmark=> SELECT * FROM cluster WHERE name ~ '^...$'; id | name | cpunum | cpuclock | memory | network ----+------+--------+----------+--------+--------- 3 | foo | | | 256 | 4 | var | 512 | 3 | 2048 | (2 rows)nameにaを含まないもの.
benchmark=> SELECT * FROM cluster WHERE name !~ 'a'; id | name | cpunum | cpuclock | memory | network ----+------+--------+----------+--------+------------- 1 | hoge | 256 | 2 | 1024 | 1000BASE-TX 3 | foo | | | 256 | (2 rows)~*,!~*を使用すると大文字小文字を無視する.
benchmark=> SELECT * FROM cluster WHERE name ~ 'A'; id | name | cpunum | cpuclock | memory | network ----+------+--------+----------+--------+--------- (0 rows) benchmark=> SELECT * FROM cluster WHERE name ~* 'A'; id | name | cpunum | cpuclock | memory | network ----+------+--------+----------+--------+--------- 2 | fuga | | | | 4 | var | 512 | 3 | 2048 | (2 rows)
複数のテーブルを指定してSELECTすることができる.複数のテーブルを指定すると,それらの直積が取られる.例えばclusterのレコード数は4,result_linpackのレコード数は56なので,以下の結果のレコード数は224(=4*56)である.
benchmark=> SELECT * FROM cluster,result_linpack;result_linpackのcluster_idは外部キーであり,clusterのidを参照する.すなわち,clusterとresult_linpackの直積のほとんどは意味的に無意味であり,直積のレコードのうち,cluster_idとidが一致するレコードが有意である.以下ではclusterとresult_linpackの直積からgflopsが10より大きいものを選択している.8レコードが出力されているが,実際に10gflopsを超えたのはvarだけなので,idとcluster_idが一致しない8レコードは無意味である.
benchmark=> SELECT name, id, cluster_id FROM cluster,result_linpack benchmark-> where gflops > 10; name | id | cluster_id ------+----+------------ hoge | 1 | 4 fuga | 2 | 4 foo | 3 | 4 var | 4 | 4 hoge | 1 | 4 fuga | 2 | 4 foo | 3 | 4 var | 4 | 4 (8 rows)というわけで,複数のテーブル(の直積)から選択する場合には,しばしば外部キーとその参照しているキーが等しいものを条件に指定する.
benchmark=> SELECT name, id, cluster_id FROM cluster,result_linpack benchmark-> where gflops > 10 and id=cluster_id; name | id | cluster_id ------+----+------------ var | 4 | 4 var | 4 | 4 (2 rows)
クエリ(SQL文)の中にさらにクエリを埋め込むことができる.これをサブクエリと呼ぶ.ここではWHERE句の条件式にサブクエリを指定する例を示す.以下はclusterとresult_linpackの直積からid=cluster_idかつnameが'hoge'のものを出力している.
benchmark=> SELECT name, result_linpack.* benchmark-> FROM cluster, result_linpack benchmark-> WHERE id=cluster_id and name = 'hoge'; name | n | nb | cluster_id | gflops | exec_date ------+-------+-----+------------+--------+----------- hoge | 10000 | 120 | 1 | 3.242 | hoge | 11000 | 120 | 1 | 3.467 | hoge | 12000 | 120 | 1 | 3.64 | hoge | 13000 | 120 | 1 | 3.904 | hoge | 14000 | 120 | 1 | 4.059 | hoge | 15000 | 120 | 1 | 5.649 | hoge | 16000 | 120 | 1 | 6.261 | hoge | 17000 | 120 | 1 | 6.562 | hoge | 18000 | 120 | 1 | 6.932 | hoge | 19000 | 120 | 1 | 6.964 | hoge | 21000 | 120 | 1 | 5.21 | (11 rows)ここで出力にnameが不要な場合,出力はresult_linpackの属性のみになる.そうするとFROMにclusterを指定して直積(しばしば馬鹿でかくなる)を作成するよりも,WHERE句でサブクエリを指定した方が効率がよいだろう.
benchmark=> SELECT * FROM result_linpack benchmark-> WHERE cluster_id=(SELECT id FROM cluster WHERE name = 'hoge'); n | nb | cluster_id | gflops | exec_date -------+-----+------------+--------+----------- 10000 | 120 | 1 | 3.242 | 11000 | 120 | 1 | 3.467 | 12000 | 120 | 1 | 3.64 | 13000 | 120 | 1 | 3.904 | 14000 | 120 | 1 | 4.059 | 15000 | 120 | 1 | 5.649 | 16000 | 120 | 1 | 6.261 | 17000 | 120 | 1 | 6.562 | 18000 | 120 | 1 | 6.932 | 19000 | 120 | 1 | 6.964 | 21000 | 120 | 1 | 5.21 | (11 rows)注意点としては,出力フィールドにはFROM句で指定したテーブルの属性しか指定できない点と,サブクエリの結果が1レコードでなければならない点である.後者との絡みもあって,サブクエリにはしばしば集約関数が使用される.以下はcluster_idがidの最小値と一致するレコードを選択している.
benchmark=> SELECT * FROM result_linpack benchmark-> WHERE cluster_id=(SELECT min(id) FROM cluster); n | nb | cluster_id | gflops | exec_date -------+-----+------------+--------+----------- 10000 | 120 | 1 | 3.242 | 11000 | 120 | 1 | 3.467 | 12000 | 120 | 1 | 3.64 | 13000 | 120 | 1 | 3.904 | 14000 | 120 | 1 | 4.059 | 15000 | 120 | 1 | 5.649 | 16000 | 120 | 1 | 6.261 | 17000 | 120 | 1 | 6.562 | 18000 | 120 | 1 | 6.932 | 19000 | 120 | 1 | 6.964 | 21000 | 120 | 1 | 5.21 | (11 rows)
先節の方法では,サブクエリの結果が1レコードでなければならない.しかし複数レコードを返すサブクエリを使用したいこともある.このような場合にはINを使用する.以下はテーブルresult_linpackからgflops>=8のレコードを選択し,それらのレコードのcluster_idと,idが一致するテーブルclusterのレコードを表示する.すなわちLinpackで8GFlops以上のクラスタのデータを表示している.
benchmark=> SELECT * FROM cluster benchmark-> WHERE id IN (SELECT cluster_id FROM result_linpack WHERE gflops >= 8); id | name | cpunum | cpuclock | memory | network ----+------+--------+----------+--------+--------- 2 | fuga | | | | 4 | var | 512 | 3 | 2048 | (2 rows)INの否定はNOT INである.
EXSISTは主たるSELECT文の結果の各レコードに対してEXISTSに続くサブクエリを適用し,そのサブクエリの結果にレコードがあれば(1行以上が選択されれば),主たるSELECT文の最終的な検索結果とするものである.なんだかわかりにくいので例を示す.以下はclusterから1行ずつ取り出し,EXISTSに続くサブクエリで(その行のid)=cluster_idかつexec_dateがNULLの行があれば,clusterの行を選択,出力している.
benchmark=> SELECT * FROM cluster benchmark-> WHERE EXISTS (SELECT * FROM result_linpack WHERE id = cluster_id AND exec_date IS NOT NULL); id | name | cpunum | cpuclock | memory | network ----+------+--------+----------+--------+--------- 4 | var | 512 | 3 | 2048 | (1 row)この例ではサブクエリの中で主たるクエリの属性を参照している.このようなサブクエリを相関サブクエリと呼ぶ.この例をサブクエリの中で主たるクエリの属性を参照しないようにすると,結果は以下のようになる.
benchmark=> SELECT * FROM cluster benchmark-> WHERE EXISTS (SELECT * FROM result_linpack WHERE exec_date IS NOT NULL); id | name | cpunum | cpuclock | memory | network ----+------+--------+----------+--------+------------- 1 | hoge | 256 | 2 | 1024 | 1000BASE-TX 2 | fuga | | | | 3 | foo | | | 256 | 4 | var | 512 | 3 | 2048 | (4 rows)これは相関サブクエリではなく,サブクエリは一度だけ実行される.ざっくりとではあるが,以下のイメージがあてはまる.
if (サブクエリで1行以上選択される) then 主たるクエリを実行 else 何も選択しない endifINを使用するものの多くはEXISTSで表現でき,EXISTSを使用した方が高速らしい.EXISTSはサブクエリが1行以上選択されると真となるが,何も選択されない場合に真としたい場合は,NOT EXISTSを使用する.以下はclusterから1行ずつ取り出し,NOT EXISTSに続くサブクエリで(その行のid)=cluster_idかつexec_dateがNULLの行がなければ,clusterの行を選択し,出力している.
benchmark=> SELECT * FROM cluster benchmark-> WHERE NOT EXISTS (SELECT * FROM result_linpack WHERE id = cluster_id AND exec_date IS NULL); id | name | cpunum | cpuclock | memory | network ----+------+--------+----------+--------+--------- 4 | var | 512 | 3 | 2048 | (1 row)この例は本節最初の例結果は同じだがクエリの意味的には同値でないことを注意しておく.
FROM句にもサブクエリを使用することができる.以下はclusterからnetworkがNULLであるレコードをSELECTし,それにt1という相関名をつけ,t1.idがINに続くサブクエリの結果のcluster_id内にあればt1.nameを出力している.INに続くサブクエリでは,result_linpackからgflops>=3のレコードを選択し,そのcluster_idを出力している.
benchmark=> SELECT t1.name benchmark-> FROM (SELECT * FROM cluster WHERE network IS NULL) AS t1 benchmark-> WHERE t1.id IN (SELECT cluster_id FROM result_linpack WHERE gflops >=3); name ------ fuga foo var (3 rows)
使いどころがよくわからないのだが,ターゲットリスト(SELECT結果から出力するフィールド)にもサブクエリを使用できる.
benchmark=> SELECT (SELECT name FROM cluster WHERE name='hoge'), * benchmark-> FROM result_linpack benchmark-> WHERE cluster_id=(SELECT id FROM cluster WHERE name='hoge'); ?column? | n | nb | cluster_id | gflops | exec_date ----------+-------+-----+------------+--------+----------- hoge | 10000 | 120 | 1 | 3.242 | hoge | 11000 | 120 | 1 | 3.467 | hoge | 12000 | 120 | 1 | 3.64 | hoge | 13000 | 120 | 1 | 3.904 | hoge | 14000 | 120 | 1 | 4.059 | hoge | 15000 | 120 | 1 | 5.649 | hoge | 16000 | 120 | 1 | 6.261 | hoge | 17000 | 120 | 1 | 6.562 | hoge | 18000 | 120 | 1 | 6.932 | hoge | 19000 | 120 | 1 | 6.964 | hoge | 21000 | 120 | 1 | 5.21 | (11 rows)ターゲットリストのサブクエリの結果はちょうど1行かつ出力フィールドは一つでなければならない.
