PostgreSQLでSQLを書いた
すーーーーごい久々にBlog書きます。。
最近、負けているなーと思いつつSQLを書いているのですが、要件が複雑なデータ補正なので、「selectの基本構文はわかります」程度のレベルでは立ち行かず、9行の補正に4時間掛けてどうやればいいか調べて徹夜したりしてます。。。
（手修正すればいいけどそれはそれで負けている気がする）
基本PostgreSQLです。
- insert select 構文 〜 SQLでデータを作ってテーブルに保存
- coalesce 〜 nullだったらデフォ値を入れる
- joinに直値 〜 予め結合するデータを絞る（高速になるのかな）
- dense_rank() ウィンドウ関数 〜 max() + 1 じゃできないことをやる
- array_agg & 配列の要素アクセス 〜 first_value じゃできないことをやる
insert select 構文 〜 SQLでデータを作ってテーブルに保存
データの補正作業や、アプリで制御しているような各種テーブル間の整合性を取ったデータを作る必要性がある場合、既存のテーブルからデータを作ってテーブルに入れたいのですがそれを一発でやる方法です。
insert into テーブル名 select * from 取ってくるテーブル
insert行とselect行の間にはセミコロンとかカッコとかいりません。
insert行をコメントアウトしておけばselectした結果をコンソールで見て確認することもできます。
nullだったらデフォ値を入れる
「joinして結合先テーブルに存在しないならこれにしてください。」という要件。
単にleft outer join するとnullになるので、NOT NULL制約が付いているカラムだとイライラしてしまいます。
そこでcoalesce関数 ※いつも読み方わからないが「コウアレス」と読む。
select coalesce(name, '名無しさん') from users left outer join letters on users.name = letters.from_name
joinに直値 〜 予め結合するデータを絞る（高速になるのかな）
たまにサンプルとかで見るのですが、？？？ってなっていた構文。
select users.name from users left outer join letters on users.name = letters.from_name and letters.year = '2017'
on句って、テーブルAとテーブルBを結合するときの条件なので、AのカラムとBのカラムを比較するしかできないと思っていた...
けど、SQLは動くのです。いまいちどういう概念なのか調べることもできなかったのですが、たまたまものすごい「それだっ」というブログ記事を発見したのでついでに記録。
http://atsuizo.hatenadiary.jp/entry/2016/12/12/163921
「JOINに直値」
まんまだけどわかりやすい！まんまだけど！
もっと広がれ。
http://blogs.itmedia.co.jp/doc-consul/2015/11/sql.html
こちらもわかりやすい。ON句に書けるのは、「結合前抽出条件」と「結合条件」の二種類！
（SQL処理エンジンにとっては同じなのかもしれませんが）
「結合前抽出条件」と聞くと、いらんデータは絞ってから結合したほうが速いのかなぁ、って思うので結果が変わらないinner joinのケースではwhere句からon句に持ってきたりしてます。最適化エンジンが頑張りそうなのであまり意味は無いかもしれません（測定してません）。
dense_rank() ウィンドウ関数 〜 max() + 1 じゃできないことをやる
insert ~ select構文を使うとき、一番便利なのはmax()関数を使って、シーケンスっぽいことを実現するとき。
データをバルクで登録するとき、順序やキーが一意になるように最大値＋１を自動で設定する。
（同じ値が登録される気がするが、1行登録されるとmaxが更新されるらしくちゃんと動く。原理がよくわからない...）
insert into users select other_users.name, max(users.id) + 1 from other_users
だがしかし。ユーザクラス別にユニークなキーや順序を指定するときには使えない。
そこでウィンドウ関数を利用。user_orderをclassごとにユニークにしたい場合に使えます。
既存のテーブルに追加する場合はclass毎の最大値を取っておく必要があるのでちょっとメンドくさい。
insert into users select other_users.id, other_users.name, other_users.class, max_num + dense_rank() over (partition by other_users.class order by other_users.name) from other_users join ( select class, max(user_order) as max_num from users group by class ) as max_order_by_class on other_users.class = max_order_by_class.class
（ちょっとSQL怪しいかも）
ウィンドウ関数は最初わかりづらかった。。。
partitionとgroup by で混乱してしまってましたが、理解すると超便利。
http://postd.cc/window_functions_postgresql/
https://lets.postgresql.jp/documents/technical/window_functions/1
array_agg & 配列の要素アクセス 〜 first_value じゃできないことをやる
Group byしたとき、Group gyのキー以外のカラムにてきとーに値をいれたい、という要件
関数の名前から、ずーーーーと first_value（最初の1個取れそう）でできるだろうと思っていたけどできなかった。
Group byしたとき、集約キー以外の項目が２値以上取る場合にNULLになってしまうのが困るのです。
first_valueは「ウィンドウ関数」なので、行を集約しないのです。
そこで、array_agg集約関数
select class, (array_agg(name))[1] || 'さん、等' from user group by class
array_aggはgroup byした各グループの、特定のカラムを配列化して1行に集約しちゃう関数。
上記の場合、配列化せずに単に name || 'さん、等' *1を付けると1個目がピンで表示できる。
*2
array_aggは、配列型がわかってないと難しい。下記は例を用いて説明があるのでわかりやすい。
http://qiita.com/choplin/items/9d5e2ff8721fb9509bf8
最近の自分の最大の問題は、このBlog書き始めたのが5月なのに、最後の2つの項目書くのをずっと溜めてしまって9月公開となったこと。うーむ、、、
*1:※ 余談だが、「1行しかなかった場合は'等'を付けない」としたい場合は、 case式でcount(name)の結果によって場合分けすればよい))) とすると、nameがNULLになってしまうので||で結合した文字列全体がNULLになってしまう。 array_aggの戻り値は配列型なのだが、配列アクセス演算子の結合優先順位が高いのでカッコでくくって[1] ((PostgreSQLではデフォルトでは1始まりになる。
*2:なお、PostgreSQL 9.x以降は、order byを付けることで配列化するときの順序を指定できるようだ。
https://www.postgresql.jp/document/9.4/html/sql-expressions.html#SYNTAX-AGGREGATES
