運用のTips [クエリ]

説明のために使用するデータベース "sample" (スーパーの商品管理風?) :

bcord bunrui
1 カコウショクヒン
2 カシ
3 セイカツヨウヒン
table名 tbunrui
bcord カラム: 部門コード (serial UNIQUE)
bunrui カラム: 分類名
kcord bcord kamoku
1 1 ハム
2 1 オデンタネ
3 2 チョコレート
4 2 スナック
5 3 センザイダイドコロ
6 3 シャンプー
table名 tkamoku
kcord カラム: 科目コード (serial UNIQUE)
bcord カラム: 外部キー制約で tbunrui の bcord を参照
kamoku カラム: 科目名
scord kcord hinmei shikiri joudai
1 1 マルタイボンレス 300 500
2 1 メイボウロースハム 400 600
3 2 キフンハンペン 150 300
4 2 ナカムラガンモドキ 200 350
5 3 クリコアーモンド 100 200
6 3 ロットマカダミア 110 220
7 4 カルヒーポテトシオ 80 150
8 4 カルヒーポテトミソ 80 150
9 5 チャーミーブルー 160 250
10 5 ファミリーチョイ 150 240
11 6 スーパーナチュラル 180 300
12 6 ソフトインツー 190 320
table名 tshohin
scord カラム: 商品コード (serial UNIQUE)
kcord カラム: tkamoku の kcord を外部参照
hinmei カラム: 品名
shikiri カラム: 仕切値
joudai カラム: 上代

手っ取り早く実験してみたい人のために、sample データベースのダンプを置いておく。ダンプ時に -C は指定してないので、自由な名称でデータベース (入れ物) を作ってからリストアすべし。文字コードは EUC になっている。

副問い合わせ (Sub Query) 評価式

サブクエリからの結果を利用してクエリを行うことができる。複数のテーブルの値を参照する複雑なクエリを、ひとつのクエリで済ませることができる。phpなどで利用するとき、変数を増やさずに済む。副問い合わせ部分は ( ) で囲まなければならない。副問い合わせ評価演算子にはこの他、EXISTS, ALL, SOME, ANY がある。

= を使う

SELECT kcord, kamoku FROM tkamoku
 WHERE bcord = ( SELECT bcord FROM tbunrui WHERE bunrui = 'カシ' );

結果:

kcord kamoku
3 チョコレート
4 スナック

"=" の場合、サブクエリは必ず1行1カラムを返さなければならない。反対の意味は "<>"。

IN を使う

SELECT kcord, kamoku FROM tkamoku WHERE bcord
 IN (
 SELECT bcord FROM tbunrui WHERE bunrui = 'カコウショクヒン' OR bunrui = 'カシ');

結果:

kcord kamoku
1 ハム
2 オデンタネ
3 チョコレート
4 スナック

"IN" の場合、サブクエリが返す値 (行) は複数でもよい。複数カラムはダメ。これは、IN が配列を受け付け可能で、こういう使い方もできるから。

SELECT kcord, kamoku FROM tkamoku WHERE kcord IN (1, 2);

「kcord の値が 1 または 2」のレコードを取り出すので結果は同じ。意味の反転は "NOT IN"。
詳細は、PostgreSQL 7.3.x ユーザガイド

なお、上記クエリは、パターンマッチングの POSIX 正規表現を使って下のように書いても同じ結果が得られ、場合によっては、正規表現マッチの方が検索コストが低くくできる;

SELECT kcord, kamoku FROM tkamoku WHERE kcord ~ '^[12]$';

さらに、同じ検索は WHERE ... OR を使って;

SELECT kcord, kamoku FROM tkamoku WHERE kcord = 1 OR kcord = 2; 

とも書けるわけだが、筆者の経験からすると、たいていの場合 POSIX 正規表現と IN のどちらよりも検索コストが悪化する。

パターンマッチング

WHERE 句で、「まさにこの文字列」ではなく、近似表現でレコードを取り出したいことがよくある。 PostgreSQL には、2種類のパターンマッチング演算子があったが、7.3 で "SIMILAR TO" が増えて3種類になった。
※詳細は、PostgreSQL 7.3.x ユーザガイド

POSIX正規表現 "~"

Perl や egrep で馴染み深い正規表現が使え、最も柔軟性が高い。Perl 正規表現の "/i" のように大文字小文字を区別したくない場合は "~*" を、全体を否定するには "!~" が使える。 7.4 でさらに拡張が進んでほぼ Perl 正規表現近似となり、前方先読参照や、結果に格納しないグループ参照、\b, \n, \r, \t などのエスケープ表現も使えるようになった (pgsql.conf 参照)。ただし、高機能な分、作業コストがやや高く、複雑なマッチが必要ない場面では後述の LIKE のほうが検索時間が短くできることが多い。

例:

SELECT scord, hinmei FROM tshohin WHERE hinmei ~ '^.*ポ[タチツテ]ト(シオ|ミソ)$';

結果:

scord hinmei
7 カルヒーポテトシオ
8 カルヒーポテトミソ

注意点: POSIX 正規表現は部分マッチであり、 ^$ を使わないと、思惑よりもたくさんの文字列がマッチしてしまう。例えば上記の例で頭の ^ を付けずに;

SELECT scord, hinmei FROM tshohin WHERE hinmei ~ '.*ポ[タチツテ]ト(シオ|ミソ)$';

とすると、「カルヒーポテトシオ」などにもマッチしてしまうので注意が必要だ。また、「カルヒーポテト」の後に続く文字が何でもいい場合には;

SELECT scord, hinmei FROM tshohin WHERE hinmei ~ '^.*ポ[タチツテ]ト.*';

と書く必要性はなく、

SELECT scord, hinmei FROM tshohin WHERE hinmei ~ '^.*ポ[タチツテ]ト';

でも同じ結果が得られる。

LIKE

伝統的な SQL互換のパターンマッチング。mysql にも oracle にもある。移植性の高いコードを書きたければ、これ (?)。大文字小文字を区別しないマッチングには ILIKE があるが、こちらは PostgreSQL 固有。

LIKEの特殊文字:

_ 任意の1文字 (POSIXのドット)
% 任意の文字の0回以上の連続
例: (結果は上記と同じ)
SELECT scord, hinmei FROM tshohin WHERE hinmei LIKE '_ルヒーポテト%';

否定するには、NOT LIKE

SIMILAR TO

PostgreSQL 7.3 で新たに導入された、SQL99 準拠の演算子。LIKEの拡張版で、POSIX 的な以下の特殊文字も解釈される。

SIMILAR TO の追加特殊文字:

| OR
* 直前の文字の0回以上の連続
+ 直前の文字の1回以上の連続
[] POSIXの文字クラスと同様
() パターンのグループ化
例: (結果は同じ)
SELECT scord, hinmei FROM tshohin WHERE hinmei SIMILAR TO 'カ[リル]_+(シオ|ミソ)';

結合テーブル

JOIN は、2つのテーブルを結合した答を得る。

例:

SELECT * FROM tshohin s LEFT JOIN tkamoku k ON (s.kcord = k.kcord) WHERE bcord = 2;

結果:

scord kcord hinmei shikiri joudai kcord bcord kamoku
5 3 クリコアーモンド 100 200 3 2 チョコレート
6 3 ロットマカダミア 110 220 3 2 チョコレート
7 4 カルヒーポテトシオ 80 150 4 2 スナック
8 4 カルヒーポテトミソ 80 150 4 2 スナック

上のやり方では、tshohin の kcord カラムと tkamoku の kcord カラムが重複出力されてしまう。これを避けるには:

SELECT * FROM tshohin s LEFT JOIN tkamoku k USING (kcord) WHERE bcord = 2;

とするか、

SELECT * FROM tshohin s NATURAL LEFT JOIN tkamoku k WHERE bcord = 2;

USING で両方に共通するカラムを指定する。複数あるなら (culumn1, culumn2 ) とする。NATURAL はこれを自動的にやってくれる。

現実には、上記のように2つのテーブルのすべてのカラムを取り出すことは少ないだろう。必要なカラムだけ取り出すよう明示するのが一般的。

より現実的な例:

SELECT k.kamoku, s.hinmei, s.joudai FROM tshohin s LEFT JOIN tkamoku k
 USING (kcord) WHERE k.bcord = 2;

結果:

kamoku hinmei joudai
チョコレート クリコアーモンド 200
チョコレート ロットマカダミア 220
スナック カルヒーポテトシオ 150
スナック カルヒーポテトミソ 150

LEFT の他に、RIGHT, CROSS, FULL, INNER の JOIN があるが、使う必要に迫られた経験無し。なお、LEFT, RIGHT は各々 "LEFT OUTER", "RIGHT OUTER" の略。ともに必ず OUTER なので記述する必要は無い。
※詳細は、PostgreSQL 7.3.x ユーザガイド または チュートリアル

3つのテーブルを結合するにはJOINを2回行う:
SELECT v.bunrui, v.kamoku, s.hinmei, s.joudai FROM tshohin s LEFT JOIN (
 SELECT * FROM tkamoku LEFT JOIN tbunrui USING (bcord)
) v
 USING (kcord) WHERE bcord = 2;

こう書くこともできる:

SELECT v.bunrui, v.kamoku, s.hinmei, s.joudai FROM tshohin s LEFT JOIN (
 tkamoku LEFT JOIN tbunrui USING (bcord)
) v
 USING (kcord) WHERE bcord = 2;

結果:

bunrui kamoku hinmei joudai
カシ チョコレート クリコアーモンド 200
カシ チョコレート ロットマカダミア 220
カシ スナック カルヒーポテトシオ 150
カシ スナック カルヒーポテトミソ 150

※多重JOINの例はPostgreSQL 7.3.x ユーザガイド 「明示的なJOINでプランナを制御」

テーブルのエイリアス

上の例の "s" や "k" や "v" のこと。2つのテーブルを使用した問い合わせでは、例えば kcord カラムは両方のテーブルにあるため、どちらのテーブルのカラムか明示してやらないと「カラム名が曖昧だ」 (ambiguous) と言われてエラーとなる。その時便利なのが、テーブルのエイリアス名。エイリアスを使用しないと、

SELECT tkamoku.kamoku, tshohin.hinmei, tshohin.joudai FROM tshohin
 LEFT JOIN tkamoku ON (tkamoku.kcord = tshohin.kcord) WHERE tkamoku.bcord = 2;

のようになってしまう。例ではすべてのカラムでテーブルを明示したが、片方のテーブルにしかないカラムについては明示する必要は無い。

集約(aggregate)関数

avg (平均), count (数値、個数), max (最大値), min (最小値), sum (合計), stddev (標準偏差標本), variance (分散標本) がある。

count

sampleテーブル:

col val
1 5
2  
SELECT count(*) FROM sample;   --> 結果:2 (sample テーブルの総行数を数えた)
SELECT count(val) FROM sample; --> 結果:1 (val カラムを基準に総行数を数えた。値が NULL だとカウントされない)

PostgreSQL 7.3.x ユーザガイド または チュートリアル

グループ化(GROUPとHAVING)

HAVING は、WHERE に似ているが、グループ化した行を、条件に従ってグループ単位でごっそり選択/除外してくれる。

SELECT kcord, avg(joudai) FROM tshohin GROUP BY kcord HAVING kcord IN (1,3,5);

結果:

kcord avg 科目コード毎に上代の平均を計算して、科目1,3,5 だけ出力
1 550.0000 <-- 500+600/2
3 210.0000 <-- 200+220/2
5 245.0000 <-- 250+240/2

上代が600円以上のものは計算に入れたくない場合:

SELECT kcord, avg(joudai) FROM tshohin WHERE joudai < 600
 GROUP BY kcord HAVING kcord IN (1,3,5);

結果:

kcord avg 説明
1 500.0000 <-- 500/1
3 210.0000 <-- 200+220/2
5 245.0000 <-- 250+240/2

PostgreSQL 7.3.x ユーザガイド