ファイヤープロジェクト
表の演算
2003-12-23T18:00+09:00   matsu
なんかアレなタイトルだが,とにかく表を結合したりしてみた.
UNIONはSELECTによる選択結果を足しあわせる.以下はresult_linpackからn>20000のレコードを選択したものとnb>300のレコードを選択したものを結合する例である.
benchmark=> SELECT * FROM result_linpack 
benchmark-> WHERE n > 20000 
benchmark-> UNION
benchmark-> SELECT * FROM result_linpack
benchmark-> WHERE nb > 300;
   n   | nb  | cluster_id | gflops | exec_date  
-------+-----+------------+--------+------------
 20000 | 320 |          3 |  6.991 | 
 20000 | 320 |          4 |  9.666 | 2003-12-13
 21000 | 120 |          1 |   5.21 | 
 21000 | 120 |          4 |  8.255 | 2003-12-13
(4 rows)
和をとる二つのSELECTの選択結果のターゲットリストは,属性名,名前,数が一致し互換性のある型(※)でなければならない.また,SQL92にはCORRESPONDINGで結合する列を指定できるが,PostgreSQLではサポートされていない.ただ,これにより言語としての表現力が劣るわけではない(別の表記で対応できる).
benchmark=> SELECT n, nb, exec_date FROM result_linpack
benchmark-> WHERE n > 20000
benchmark-> UNION CORRESPONDING (n, nb)
benchmark-> SELECT n, nb, gflops FROM result_linpack
benchmark-> WHERE nb > 300;
ERROR:  parser: parse error at or near "corresponding"
benchmark=> SELECT n, nb FROM result_linpack
benchmark-> WHERE n > 20000
benchmark-> UNION
benchmark-> SELECT n, nb FROM result_linpack
benchmark-> WHERE nb > 300;
   n   | nb  
-------+-----
 20000 | 320
 21000 | 120
(2 rows)
UNIONでは重複は一つにまとめられるが,UNION ALLによって重複を許すことができる.確認してみた.
benchmark=> SELECT * FROM result_linpack
benchmark-> WHERE n >= 20000
benchmark-> UNION    
benchmark-> SELECT * FROM result_linpack
benchmark-> WHERE nb >= 300;
では28レコードあるが,
benchmark=> SELECT * FROM result_linpack
benchmark-> WHERE n >= 20000
benchmark-> UNION ALL
benchmark-> SELECT * FROM result_linpack
benchmark-> WHERE nb >= 300;
では26レコードである.実際
benchmark=> SELECT * FROM result_linpack
benchmark-> WHERE n >= 20000 AND nb >= 300;
   n   | nb  | cluster_id | gflops | exec_date  
-------+-----+------------+--------+------------
 20000 | 320 |          3 |  6.991 | 
 20000 | 320 |          4 |  9.666 | 2003-12-13
(2 rows)
である.
※ リレーショナル代数における和両立条件(次数が同じで対応する属性のドメインも同じでなければならないという条件)に相当する.詳細に調査したわけではないが,例えば数と文字列は互換性がない...そもそも,より上位の方針として同じ属性名なら同じ型にするなどした方が,システムとしてすっきりすると思う.
INTERSECTを使用すると,二つのSELECT結果の共通部分を取得できる.以下はn>20000によるSELECT結果とnb>300によるSELECT結果の共通部分を出力する.
benchmark=> SELECT n, nb FROM result_linpack
benchmark-> WHERE n >= 20000
benchmark-> INTERSECT
benchmark-> SELECT n, nb FROM result_linpack
benchmark-> WHERE nb >= 300;
   n   | nb  
-------+-----
 20000 | 320
(1 row)
INTERSECTでもUNIONと同様に結果の重複は取り除かれる.そしてINTERSECT ALLを使用すると重複を許す.また,和両立条件がある.
EXCEPTを使用すると,二つのSELECT結果の差を取得できる.以下はn>=20000によるSELECT結果からnb>=200によるSELECT結果を除いたものを表示する.
benchmark=> SELECT n, nb FROM result_linpack
benchmark-> WHERE n >= 20000
benchmark-> EXCEPT
benchmark-> SELECT n, nb FROM result_linpack
benchmark-> WHERE nb >= 200;
   n   | nb  
-------+-----
 20000 |  20
 20000 |  40
 20000 |  80
 20000 | 120
 20000 | 140
 20000 | 160
 21000 | 120
(7 rows)
EXCEPTでもUNIONと同様に結果の重複レコードは取り除かれる.そしてEXCEPT ALLを使用すると重複を許す.また,和両立条件がある.
CROSS JOINを使用すると,直積を取得できる.直積は二つのテーブルのレコードの全組み合わせである.したがって,テーブルA,Bの直積はテーブルA,Bのレコード数の積と一致する.
benchmark=> SELECT count(*) FROM cluster CROSS JOIN result_linpack;
 count 
-------
   224
(1 row)

benchmark=> SELECT count(*) FROM cluster ;
 count 
-------
     4
(1 row)

benchmark=> SELECT count(*) FROM result_linpack;
 count 
-------
    56
(1 row)
CROSS JOINは以下の形式と同じである.
benchmark=> SELECT count(*) FROM cluster, result_linpack;
 count 
-------
   224
(1 row)
この形式は今まで何度も使用してきた.
NATUNAL JOINで自然結合を取得できる.テーブルA,Bに同じ名前の列A1,B1があるとする.これをNATURAL JOINすると,AとBの直積からA1=B1であるレコードを取得できる.以下は,result_linpackとresult_netpipeから列exec_date等を省いて,cluster_idによるNATURAL JOINを取得している.
benchmark=> SELECT count(*) 
benchmark-> FROM (SELECT cluster_id, n, nb, gflops FROM result_linpack) AS T1
benchmark-> NATURAL JOIN (SELECT cluster_id, size, mbps FROM result_netpipe) AS T2;
 count 
-------
 13546
(1 row)
cluster_idと同様,exec_dateもresult_linpackとresult_netpipeに存在する.したがって,両テーブルを直接NATURAL JOINすると「cluster_idが等しいかつexec_dateが等しい」レコードが選択されてしまう.
benchmark=> SELECT count(*)
benchmark-> FROM result_linpack NATURAL JOIN result_netpipe;
 count 
-------
     0
(1 row)
この例ではターゲットリストがcount(*)なので見えないが,NATURAL JOINでは,結合に使用された列(ここではcluster_id)は一つにまとめられる.
benchmark=> SELECT *
benchmark-> FROM (SELECT cluster_id, n, nb, gflops FROM result_linpack) AS T1
benchmark-> NATURAL JOIN (SELECT cluster_id, size, mbps FROM result_netpipe) AS T2;
 cluster_id |   n   | nb  | gflops |   size    |    mbps    
------------+-------+-----+--------+-----------+------------
          1 | 10000 | 120 |  3.242 |         1 |   0.111561
          1 | 10000 | 120 |  3.242 |         2 |   0.220551
          1 | 10000 | 120 |  3.242 |         3 |     0.3412
...以下略...
NATURAL JOINを使用しない方法として以下がある.
benchmark=> SELECT count(*) 
benchmark-> FROM result_linpack, result_netpipe
benchmark-> WHERE result_linpack.cluster_id = result_netpipe.cluster_id;
 count 
-------
 13546
(1 row)
ただし,ここでターゲットリストを*に変更しても,結合に使用した列cluster_idはresult_linpackのものとresult_netpipeのものの二つが表示される.
NATURAL JOINでは同名の列でなければ結合に使用されなかった.これに対しては直積に対してWHEREでレコードを選択する方法が考えられる.他の方法,JOIN...ON...条件式は結果的にこれと同様に見えるが,意味的にはテーブルの結合に関する条件を指定する.以下にJOIN...ON...条件式の例を示す.
benchmark=> SELECT count(*)
benchmark-> FROM result_linpack JOIN result_netpipe
benchmark-> ON result_linpack.cluster_id = result_netpipe.cluster_id;
 count 
-------
 13546
(1 row)
これは以下と同じ結果を得るものである.
benchmark=> SELECT count(*)
benchmark-> FROM result_linpack, result_netpipe
benchmark-> WHERE result_linpack.cluster_id = result_netpipe.cluster_id;
 count 
-------
 13546
(1 row)
前者はテーブルの結合の際の条件を指定して結合結果のテーブルを全て表示している.これに対して後者はテーブルの直積を作成してそれに対して選択条件を指定している.
NATURAL JOINでは同名の列のものはすべて結合に使用された.JOIN...USING ON(属性リスト)を使用すると,属性リストで指定した列を使用してテーブルを結合することができる.以下はcluster_idを使用して列を結合する例である.
benchmark=> SELECT count(*)
benchmark-> FROM result_linpack JOIN result_netpipe
benchmark-> USING(cluster_id);
 count 
-------
 13546
(1 row)
NATURAL JOINと同様,JOIN...ON USING(属性リスト)でも結合に使用した列は一つにまとめられる.以下は上の例のターゲットリストを変更したものだが,result_linpackとresult_netpipeの両方にある列cluster_idが一つにまとめられている.
benchmark=> SELECT *
benchmark-> FROM result_linpack JOIN result_netpipe
benchmark-> USING(cluster_id);
 cluster_id |   n   | nb  | gflops | exec_date  | type  |   size    |    mbps    | exec_date  
------------+-------+-----+--------+------------+-------+-----------+------------+------------
          1 | 10000 | 120 |  3.242 |            | lam   |         1 |   0.111561 | 2003-12-01
          1 | 10000 | 120 |  3.242 |            | lam   |         2 |   0.220551 | 2003-12-01
          1 | 10000 | 120 |  3.242 |            | lam   |         3 |     0.3412 | 2003-12-01
...以下略...
以上の結合はINNER JOINと呼ばれる.実際上記のJOINはINNER JOINの略である.
benchmark=> SELECT count(*)
benchmark-> FROM result_linpack INNER JOIN result_netpipe
benchmark-> USING(cluster_id);
 count 
-------
 13546
(1 row)
benchmark=> SELECT count(*)
benchmark-> FROM result_linpack NATURAL INNER JOIN result_netpipe;
 count 
-------
     0
(1 row)
INNER JOINでは二つのテーブルのレコードを比較して,結合条件に合致するレコードの組を結合した.結合条件に合致する相手のないレコードは結合結果には現れない.以下はid=1のレコードのみからなるテーブルとid=2のレコードのみからなるレコードをidを使用して結合しているので,結果は0レコードである.
benchmark=> SELECT *
benchmark-> FROM (SELECT * FROM cluster WHERE id=1) AS T1
benchmark-> INNER JOIN
benchmark-> (SELECT * FROM cluster WHERE id=2) AS T2
benchmark-> USING(id);
 id | name | cpunum | cpuclock | memory | network | name | cpunum | cpuclock | memory | network 
----+------+--------+----------+--------+---------+------+--------+----------+--------+---------
(0 rows)
INNER JOINに対して外部結合OUTER JOINがある.OUTER JOINにはLEFT,RIGHT,FULLとの組み合わせによる三種類がある.LEFTとの組み合わせでは,一つ目(左側)のテーブルのレコードは二つ目のテーブルに結合相手がいるかどうかにかかわらず,すべて表示する.結合条件の判定結果で二つ目(右側)のテーブルに結合対象のレコードがなければNULLで埋める.
benchmark=> SELECT *
benchmark-> FROM (SELECT * FROM cluster WHERE id=1) AS T1
benchmark-> LEFT OUTER JOIN
benchmark-> (SELECT * FROM cluster WHERE id=2) AS T2
benchmark-> USING(id);
 id | name | cpunum | cpuclock | memory |   network   | name | cpunum | cpuclock | memory | network 
----+------+--------+----------+--------+-------------+------+--------+----------+--------+---------
  1 | hoge |    256 |        2 |   1024 | 1000BASE-TX |      |        |          |        | 
(1 row)
RIGHTはLEFTの逆である.
benchmark=> SELECT *
benchmark-> FROM (SELECT * FROM cluster WHERE id=1) AS T1
benchmark-> RIGHT OUTER JOIN
benchmark-> (SELECT * FROM cluster WHERE id=2) AS T2
benchmark-> USING(id);
 id | name | cpunum | cpuclock | memory | network | name | cpunum | cpuclock | memory | network 
----+------+--------+----------+--------+---------+------+--------+----------+--------+---------
  2 |      |        |          |        |         | fuga |        |          |        | 
(1 row)
FULLはLEFTとRIGHTの組み合わせである.
benchmark=> SELECT *
benchmark-> FROM (SELECT * FROM cluster WHERE id=1) AS T1
benchmark-> FULL OUTER JOIN
benchmark-> (SELECT * FROM cluster WHERE id=2) AS T2
benchmark-> USING(id);
 id | name | cpunum | cpuclock | memory |   network   | name | cpunum | cpuclock | memory | network 
----+------+--------+----------+--------+-------------+------+--------+----------+--------+---------
  1 | hoge |    256 |        2 |   1024 | 1000BASE-TX |      |        |          |        | 
  2 |      |        |          |        |             | fuga |        |          |        | 
(2 rows)
matsu(C)
Since 2002
Mail to matsu