そういえば最近そこそこへんなクエリを書くことがあったのでメモ

RETURNING

使ってますか?RETURNING。

僕自身はその時までえぐいUPDATE文とかを書いた時に「ホントにUPDATEされてる?」

とチェックするときにしかつかっていなかったです。

例えば「serial型のカラムを持っているテーブルにINSERTしてその際に発行されたIDを使い回してINSERT/UPDATEしたい」場合

いつもnextval()して、IDを持ってきて、その他のテーブルに対してINSERTなりUPDATEなりしていたんだけど、

RETURNINGすればINSERTした際に返ってくるからこれでいいじゃん。ってことになった。

   Column    |            Type             |                                  Modifiers                   | Storage  |  Description   
-------------+-----------------------------+--------------------------------------------------------------+----------+----------------
 item_cd     | integer                     | not null default nextval('test_tbl_item_cd_seq'::regclass)   | plain    | 項目ID
 insert_date | timestamp without time zone | not null                                                     | plain    | 登録日
 update_date | timestamp without time zone | not null                                                     | plain    | 更新日
 del_flg     | smallint                    | not null default 0                                           | plain    | 論理削除フラグ
 item_nm     | character varying(128)      | not null                                                     | extended | 項目名

な感じのテーブルがあったとしたら

test=> INSERT INTO test_tbl (insert_date, update_date,item_nm) VALUES ('now','now','a') RETURNING item_cd;
 item_cd 
---------
      18
(1 row)

な感じで使い回せばよろしい。使うべきかどうかは仕様次第。

ARRAY+UNNEST

データを配列で持てるARRAY。あんまり使わない、というか普通のアプリで使うのってどうなんだろ。

WEB屋先生の記事読んでまじかよお。早いのかよお。って思った記憶があります。

もう最近では普通?なのかどうかわからないけどこんな使い方がありますよの例。

列から行に

こんなテーブルがあったとして

test=# SELECT * FROM user_tbl ;
 id | user_id | nick_name | point_a | point_b | point_c | point_d | point_e 
----+---------+-----------+---------+---------+---------+---------+---------
 1  | test1   | ユーザ1   |       1 |       2 |       3 |       4 |       5
 2  | test2   | ユーザ2   |       2 |       3 |       4 |       5 |       1
 3  | test3   | ユーザ3   |       3 |       4 |       5 |       1 |       2
 4  | test4   | ユーザ4   |       4 |       5 |       1 |       2 |       3
 5  | test5   | ユーザ5   |       5 |       1 |       2 |       3 |       4
(5 rows)

各IDごとに特定のカラムに対して条件付きで集約関数使いたいとかの場合、行から列にするした方がスマートかと思います。

列から行はARRAY+UNNESTで列にできます。

例えばpoint_a、point_bを抽出して行化したいのであれば

test=# SELECT id,
test-#     unnest(ARRAY[point_a, point_b]) AS point
test-# FROM user_tbl
test-# ;
 id | point 
----+-------
 1  |     1
 1  |     2
 2  |     2
 2  |     3
 3  |     3
 3  |     4
 4  |     4
 4  |     5
 5  |     5
 5  |     1
(10 rows)

って感じ。サブクエリとかにしてCASE文をかませば、集約関数とかかけられるなあ、と思います。

板垣さんのブログの方が参考になると思いますがとりあえず。

ARRAY_TO_STRING+ARRAY_AGG

こんな感じのデータがあったとして。

この例だとデータ量が大したことないんでアレですけど、すげえレコード数で非正規化せんといかん場合とか。

test=# SELECT * FROM user_tbl ;
 id | user_id | nick_name 
----+---------+-----------
 1  | test1   | ユーザ1
 2  | test2   | ユーザ2
 3  | test3   | ユーザ3
 4  | test4   | ユーザ4
 5  | test5   | ユーザ5
(5 rows)

test=# SELECT * FROM point_tbl ;
 id | item_cd 
----+---------
 1  |       1
 1  |       2
 1  |       3
 1  |       4
 1  |       5
 2  |       1
 2  |       2
 2  |       3
 3  |       1
 3  |       2
 3  |       3
 4  |       4
 5  |       5
(13 rows)

であればこんな感じできますよ。と。

test=# SELECT u.id, u.user_id,u.nick_name,ARRAY_TO_STRING(ARRAY_AGG(item_cd), ',') AS csv 
FROM user_tbl u,point_tbl p 
WHERE u.id = p.id GROUP BY u.id,u.user_id,u.nick_name
ORDER BY u.id;
 id | user_id | nick_name |    csv    
----+---------+-----------+-----------
 1  | test1   | ユーザ1   | 1,2,3,4,5
 2  | test2   | ユーザ2   | 1,2,3
 3  | test3   | ユーザ3   | 1,2,3
 4  | test4   | ユーザ4   | 4
 5  | test5   | ユーザ5   | 5
(5 rows)

終わり。

Add Comments

名前
URL
 
  絵文字
 
 
livedoor プロフィール
Twitter
instagram(SnapWidget)
タグクラウド
  • ライブドアブログ