前回の続き。
FIRST VALUE(カラム名) OVER(～)
FIRST VALUE直後で指定したカラムを参照し、文字通り一番最初の値を返す。
で、「一番最初」ってどう決めるのよ？という話なのだが、それに関わるのがOVER句以降。なので基本的に2つでワンセット的な関数。両方ないとエラーになってSQLは実行されない（はず）
OVERの中に入れ込めるオプションは次の2つ。
ORDER BY
みんな大好きORDER BY。
指定したカラムを昇順降順でソートする。
PARTITION BY カラム名
指定したカラム名をチェックし、カラムの値が等しいデータ群の中でFIRST VALUE句を実行する。例えばidを指定したら、id=1 id=2など、idの値が等しいデータごとに文字通り区切られる。もしこれをつけなかった場合、区切りがなくなり、全データがFIRST VALUE化するのでこれを使うときはだいたい付いているイメージが個人的にはある。
例:飼い主ごとに最もIDの小さい犬の犬種を取得する
SELECT ow.id, ow.owners_name,
FIRST_VALUE(dog_breed) OVER(PARTITION BY ow.owners_name order by dog.id),
dog.id AS dog_id
FROM dogs dog
INNER JOIN owners ow ON dog.dog_owners_id = ow.id;
| id | owners_name | first_value(varchar) | dog_id |
|---|---|---|---|
| 5 | 関根 | ボルゾイ | 4 |
| 5 | 関根 | ボルゾイ | 8 |
| 5 | 関根 | ボルゾイ | 9 |
| 3 | 今永 | スコティッシュ・ディアハウンド | 3 |
| 3 | 今永 | スコティッシュ・ディアハウンド | 7 |
| 3 | 今永 | スコティッシュ・ディアハウンド | 15 |
| 1 | 三浦 | ダルメシアン | 5 |
| 1 | 三浦 | ダルメシアン | 6 |
| 1 | 三浦 | ダルメシアン | 13 |
| 4 | 山﨑 | ジャック・ラッセル・テリア | 10 |
| 4 | 山﨑 | ジャック・ラッセル・テリア | 11 |
| 4 | 山﨑 | ジャック・ラッセル・テリア | 14 |
| 2 | 筒香 | ラブラドール・レトリーバー | 1 |
| 2 | 筒香 | ラブラドール・レトリーバー | 2 |
| 2 | 筒香 | ラブラドール・レトリーバー | 12 |
PARTITION BYによって→ownersのidごとに
ORDER BYによって→dogsの中でidが最も若いデータのdog_breedの値が入ってきているのがわかる。
上の例でいうと、id=5に紐づくdogsのデータ群の中で最も若いdog_idは4なので、dog_id:4のdog_breedである「ボルゾイ」が、first valueとしてpartitionによって区切られた、id:5を取る列のdog_breedの全てに代入されているのだ。
出力するにあたって、FIRST VALUEを他の行に代入しているだけなので、値そのものが変わっているわけではない。これは一番右のdogsのidが元データのままというところからもわかると思います。（もしそんなことがあればえらいことなのですが）
FIRST VALUEのカラムは参照元カラムのデータ型に依存するので、上記の例だとdog_breedのデータ型、つまりvarcharになる。
絞込をしたいとき→DISTINCT
元データの件数分出てきているのでSQLとしては忠実な動きなのだが、しかし同じデータが何行もあるのはデータとしても見栄えもよろしくない。
当然、絞り込みをしようということになるのだが、FIRST VALUEを使った時はGROUP BYは使えない。
FIRST VALUEのような、テーブルの中の複数の行を対象にして、カラム値を集約したり、計算したりする関数のことをウインドウ関数というのだが、
（参考→ http://www.postgresql.jp/document/9.5/html/functions-window.html ）
（少なくとも）PostgreSQLにおいて、ウインドウ関数によって得たカラムをGROUP BYに指定することはできない。
さらに、元カラムであるdog_breedはSELECT句の中にないため使えない。
では、どうやって絞り込むのかというと、今回はDISTINCTを使う。
DISTINCTとは、selectで指定したカラムについて値が重複している行を省いて表示してくれるオプション。GROUP BYに似ているといえば似ている。違いはあるんだろうけどわからん。使い方は簡単で、SELECT句にDISTINCTと追加するだけ。他の書き方は変わらない。
SELECT DISTINCT ow.owners_name,
FIRST_VALUE(dog_breed) OVER(PARTITION BY ow.id order by dog.id)
FROM dogs dog
INNER JOIN owners ow ON dog.dog_owners_id = ow.id;
| owners_name | first_value |
|---|---|
| 筒香 | ラブラドール・レトリーバー |
| 山﨑 | ジャック・ラッセル・テリア |
| 今永 | スコティッシュ・ディアハウンド |
| 関根 | ボルゾイ |
| 三浦 | ダルメシアン |
すっきり集約された。が、表示順がownersのID昇順から崩れてしまっている。
(ここの原因がいまいちよくわかってない・・・)
IDでORDER BYすればいいのだが、DISTINCTを使う時はORDER BYにSELECT句で指定していないカラム、つまりIDを使うことはできないため、IDを表示カラムに追加した上でDISTINCT、ORDER BYする。
SELECT DISTINCT ow.id, ow.owners_name,
FIRST_VALUE(dog_breed) OVER(PARTITION BY ow.id order by dog.id)
FROM dogs dog
INNER JOIN owners ow ON dog.dog_owners_id = ow.id
ORDER BY id;
| id | owners_name | first_value |
|---|---|---|
| 1 | 三浦 | ダルメシアン |
| 2 | 筒香 | ラブラドール・レトリーバー |
| 3 | 今永 | スコティッシュ・ディアハウンド |
| 4 | 山﨑 | ジャック・ラッセル・テリア |
| 5 | 関根 | ボルゾイ |
COALESCE（式1, 式2・・・）
個人的に一番使いどころがよくわからない関数のひとつ。
（）内の式やカラムを実行・参照し、
- その結果がNULL以外→結果の値を返す。
- その結果がNULL→次の式に移動。
（）内の式やカラムはカンマ区切りでいくらでも表記ができる。
最後の式を評価しても結果がNULLだった場合は、その値にNULLを返す。
NULLになるカラムに適当にデフォルト値などを入れ込みたい時などに使うらしい。条件分岐WHEN ~ THENに似ているけど、こちらはisset(カラム名)→代入に特化している、みたいな感じかなあ。集計の意図がはっきりしているのであれば簡潔に書けると思います。どちらが早いとかそういうのは知らん。
例:飼い主ごとに、DBに飼っている犬のデータが登録されているかどうか調べる
サンプルデータownersに以下を追加する。
INSERT INTO owners(
id, owners_name, gender, generation)
VALUES (6, '白崎', 2, 1),(7, '熊原', 1, 2),(8, '後藤', 1, 4),(9, '梶谷', 2, 3),(10, '久保', 1, 4);
>INSERT 0 5
SELECT * FROM owners;
| id | owners_name | gender | generation |
|---|---|---|---|
| 1 | 三浦 | 1 | 3 |
| 2 | 筒香 | 2 | 1 |
| 3 | 今永 | 1 | 2 |
| 4 | 山﨑 | 2 | 4 |
| 5 | 関根 | 1 | 5 |
| 6 | 白崎 | 2 | 1 |
| 7 | 熊原 | 1 | 2 |
| 8 | 後藤 | 1 | 4 |
| 9 | 梶谷 | 2 | 3 |
| 10 | 久保 | 1 | 4 |
6行目以降には、紐づくdogsのデータはない状態である。
手始めに、dogsのデータがある場合は名前を1匹ずつ表示するように、登録されていなければ「登録なし」を表示するようにする。
SELECT owners_name, COALESCE(dog.dog_name, '登録なし')
FROM owners ow
LEFT JOIN dogs dog on ow.id = dog.dog_owners_id
ORDER BY ow.id;
| owners_name | coalesce(varchar) |
|---|---|
| 三浦 | プリン |
| 三浦 | リン |
| 三浦 | ショコラ |
| 筒香 | モモ |
| 筒香 | ココ |
| 筒香 | マロン |
| 今永 | モカ |
| 今永 | レオ |
| 今永 | 小太郎 |
| 山﨑 | ココア |
| 山﨑 | 姫 |
| 山﨑 | ベル |
| 関根 | ラッキー |
| 関根 | 空 |
| 関根 | クッキー |
| 白崎 | 登録なし |
| 熊原 | 登録なし |
| 後藤 | 登録なし |
| 梶谷 | 登録なし |
| 久保 | 登録なし |
今回は犬の名前が1匹ずつ違うため、同じownersのidだからといってグルーピングやDISTINCTはできない。念のため。
LEFT JOINにしたのは、JOIN句の処理が先に行われる関係でINNER JOINだと「登録なし」のデータが省略されてしまうからである。
そして、FIRST VALUE同様、coalesceカラムのデータ型は元データのdog_nameに依存するので、varcharとなる。
では、先ほどのFIRST VALUEと組み合わせて、そのownerのidに紐づく犬が登録されていたら一番若いIDの犬種を、登録されていなかったら「登録なし」を表示するようにする。
SELECT DISTINCT ow.id, owners_name, FIRST_VALUE(COALESCE(dog_breed, '登録なし')) OVER(PARTITION BY ow.id order by dog.id)
FROM owners ow
LEFT JOIN dogs dog ON ow.id = dog.dog_owners_id
ORDER BY id;
| id | owners_name | first_value |
|---|---|---|
| 1 | 三浦 | ダルメシアン |
| 2 | 筒香 | ラブラドール・レトリーバー |
| 3 | 今永 | スコティッシュ・ディアハウンド |
| 4 | 山﨑 | ジャック・ラッセル・テリア |
| 5 | 関根 | ボルゾイ |
| 6 | 白崎 | 登録なし |
| 7 | 熊原 | 登録なし |
| 8 | 後藤 | 登録なし |
| 9 | 梶谷 | 登録なし |
| 10 | 久保 | 登録なし |
一見すると脳がショートしそうだが、ひとつひとつの要素自体はさほど難しくない。これはあくまで推測だが、2行目の処理順番は
OVER句内（対象カラムの絞込とFIRST VALUEの決定）
COALESCE句内（dog_breedが入るか,"登録なし"が入るか）
FIRST VALUE句内（値を対象全行に入れてカラム名を変更）
とかじゃないかなーと。
COALESCEの使い方としてはこういったあたりが多いのではないかと思われる。WHEN ~ THENでも上記の条件分岐はもちろんできるが、書く行数が少ないこちらのほうが好まれるパターンは多そう。
UNION
読んで字のごとく、カラムとカラムを結合させ、一つの結果として表示する。他テーブルと結合させることも可能。その場合にはカラム数とデータ型を揃えることが重要になる。
例として、以下のサンプルデータをowners_2テーブルに用意し、ownersと一緒に表示させてみる。今回はカラム数もデータ型も同じテーブルを使っているため、気にする必要はほとんどない。
| id | owners_name | gender | generation |
|---|---|---|---|
| 1 | 岡本 | 2 | 1 |
| 2 | 小林 | 2 | 4 |
| 3 | 大田 | 1 | 2 |
| 4 | 菅野 | 2 | 4 |
| 5 | 長野 | 1 | 2 |
| 6 | 桜井 | 1 | 3 |
| 7 | ギャレット | 1 | 3 |
| 8 | クルーズ | 2 | 2 |
| 9 | 鬼屋敷 | 2 | 5 |
| 10 | 立岡 | 2 | 1 |
SELECT id,owners_name FROM owners
UNION
SELECT id,owners_name FROM owners2
ORDER BY id;
| id | owners_name |
|---|---|
| 1 | 三浦 |
| 1 | 岡本 |
| 2 | 小林 |
| 2 | 筒香 |
| 3 | 今永 |
| 3 | 大田 |
| 4 | 山﨑 |
| 4 | 菅野 |
| 5 | 関根 |
| 5 | 長野 |
| 6 | 桜井 |
| 6 | 白崎 |
| 7 | 熊原 |
| 7 | ギャレット |
| 8 | クルーズ |
| 8 | 後藤 |
| 9 | 梶谷 |
| 9 | 鬼屋敷 |
| 10 | 久保 |
| 10 | 立岡 |
2つのテーブルのデータが一緒に表示されているのが分かる。
データが混ぜ込ぜで表示されているので、ownersとowners2に入っているデータを分けて表示したいという場合には、ソート用のカラムを作って最後にその値を含めたソートをする。UNIONする個々のテーブルやカラムごとにORDER BYすることはできないのでそこは注意が必要。
SELECT 1 AS sort_num, id,owners_name FROM owners1
UNION
SELECT 2 AS sort_num, id,owners_name FROM owners2
ORDER BY sort_num, id
| sort_num | id | owners_name |
|---|---|---|
| 1 | 1 | 三浦 |
| 1 | 2 | 筒香 |
| 1 | 3 | 今永 |
| 1 | 4 | 山﨑 |
| 1 | 5 | 関根 |
| 1 | 6 | 白崎 |
| 1 | 7 | 熊原 |
| 1 | 8 | 後藤 |
| 1 | 9 | 梶谷 |
| 1 | 10 | 久保 |
| 2 | 1 | 岡本 |
| 2 | 2 | 小林 |
| 2 | 3 | 大田 |
| 2 | 4 | 菅野 |
| 2 | 5 | 長野 |
| 2 | 6 | 桜井 |
| 2 | 7 | ギャレット |
| 2 | 8 | クルーズ |
| 2 | 9 | 鬼屋敷 |
| 2 | 10 | 立岡 |
しかし、こうするとsort_numカラムも表示しなければならないのが気になる。そこで副問い合わせという機能を使う。
副問い合わせ(FROM (SELECT~))
FROM句の中にSELECT句を入れ込み、そのSELECTの結果からSELECTする。仮テーブルを作って取り込むイメージに近いかも。ビューとか言ったりもする。
SELECT id, owners_name
FROM
(
SELECT 1 AS sort_num, id,owners_name FROM owners1
UNION
SELECT 2 AS sort_num, id,owners_name FROM owners2
) AS hoge
ORDER BY sort_num, id
| id | owners_name |
|---|---|
| 1 | 三浦 |
| 2 | 筒香 |
| 3 | 今永 |
| 4 | 山﨑 |
| 5 | 関根 |
| 6 | 白崎 |
| 7 | 熊原 |
| 8 | 後藤 |
| 9 | 梶谷 |
| 10 | 久保 |
| 1 | 岡本 |
| 2 | 小林 |
| 3 | 大田 |
| 4 | 菅野 |
| 5 | 長野 |
| 6 | 桜井 |
| 7 | ギャレット |
| 8 | クルーズ |
| 9 | 鬼屋敷 |
| 10 | 立岡 |
FROM句の中はひとつ上のSQLと全く同じである。そのSQLの出力結果を親として、idとowners_nameだけを取得している。
なので、DB内部の処理順序的には副問い合わせ→主問い合わせとなる。ちなみに主問い合わせとは大元の命令文であり、上の例で行くと1行目のid, owners_nameを取得しているところになる。
他のRDBMSではどうなのかわからないが、少なくともPostgreSQLではFROM句に別名をつけることが必須なので今回は暫定的にhogeとしている。
この副問い合わせという機能はWHERE句やINSERT句など、他の句に応用が利きやすいためよく使われる。
副問い合わせの例:あるownerと同じ世代・性別のownerを取得する
where句内に副問い合わせを置く場合の用例。
SELECT owners_name,generation FROM owners
WHERE generation =
(
SELECT generation FROM owners WHERE owners_name = '久保'
)
| owners_name | generation |
|---|---|
| 山﨑 | 4 |
| 後藤 | 4 |
| 久保 | 4 |
先にwhere句内の副問い合わせを実行する。'久保'のgenerationは4なので、
where generation = 4
と書くのと結果的に同義ではある。
そして、先ほどのfrom句の副問い合わせとも併用可。今度はowner,owners2の双方を対象に、性別がowners2の特定の行と一致するものを抽出する。
SELECT ROW_NUMBER() OVER(order by sort_num,id) AS rownum, owners_name, gender
FROM
(
SELECT 1 AS sort_num, id,owners_name, gender FROM owners
UNION
SELECT 2 AS sort_num, id,owners_name, gender FROM owners2
) AS hoge
WHERE gender =
(
SELECT gender FROM owners2 WHERE owners_name = 'ギャレット'
);
| rownum | owners_name | gender |
|---|---|---|
| 1 | 三浦 | 1 |
| 2 | 今永 | 1 |
| 3 | 関根 | 1 |
| 4 | 熊原 | 1 |
| 5 | 後藤 | 1 |
| 6 | 久保 | 1 |
| 7 | 大田 | 1 |
| 8 | 長野 | 1 |
| 9 | 桜井 | 1 |
| 10 | ギャレット | 1 |
まず、1行目にしれっと登場しているROW_NUMBER() OVER()。これはソートした出力結果に対して連番を振りなおしてくれるウインドウ関数の一種。
(参考→ http://lets.postgresql.jp/documents/technical/window_functions )
FIRST_VALUE同様、OVER()でORDER BYを使ってソートすることができる。こうすることでROW_NUMBERがソートの役割も果たしてくれるため最後にORDER BYを使わなくてもsort_num→idのソート順で表示してくれる。
肝心の副問い合わせは、（おそらく）from句内→where句内の順で行われている。from句内で2つのテーブルから指定したカラムをUNIONして取り出し、where句で'ギャレット'のgender、すなわちgender = 1の行を絞り込んでいる。一見複雑そうに見えるが、やっていることはそれなりに単純な処理の積み重ねでもある。
まとめ
- 出力結果内で「最初の値」を返すFIRST VALUE。その「最初」を決めるのがOVER(PARTITION BY... ORDER BY...)句内。このような、複数行の結果に対して計算したり値をいじったりする関数を総称してウインドウ関数という。
- ウインドウ関数のデータ型は元カラムのデータ型に依存する。
- ウインドウ関数の表示を絞り込むときはGROUP BYではなくDISTINCT。FIRST VALUEを使うときは特にお世話になる・・・はず。
- カラムや式の結果を評価するCOALESCE()関数。（）内のカラムや式を書かれた順番に評価し、nullでない値を持つときに代入する。nullのカラムに定数を入れたりしたい時に使ったりする。FIRST VALUEと相性が良い。
- 他のテーブルなどを結合して一つの出力結果として表示するUNION。UNIONするどうしのデータの重複を弾かないUNION_ALLというのもある。どちらもカラム数やデータ型が一致していることが絶対。UNIONのデータを元テーブルごとにソートしたい場合は、ソート用のカラムを作ってあげたりする。
- FROM句やWHERE句などでさらにSELECTしたい時などに使う副問い合わせ。PostgreSQLではFROM句の副問い合わせにはASで名前をつけるのが必須である。