SQL の列名は同じ種類の ID なら同じ名前にしておけば USING(xx_id) で JOIN できたり便利なので 列名を一意にしようなんて思ったことはありませんでした

しかし 「SELECT * FROM ～」 のように全取得してると LEFT JOIN で JOIN 先にマッチする行がないと id が null になってしまう問題に困りました
同じ列名なのでオブジェクトや連想配列やディクショナリとして取得するとあとの方の null で上書きされてしまうのですよね
「*」 を使わず全部書けば解決ですが 長いです
ワイルドカードで除外が選べれば助かるケースはかなりあるのですが SQL では対応してません
それに SQL って全然更新されないので JavaScript などのように待てば便利なる期待もありません
せめて 「select a.* as a__*」 で a__ プレフィックスつけてくれるみたいなのができたらよかったのですけど

それで思ったのが最初から列名を 「tablename__columnname」 みたいなのにしておくことです
見た目はいまいちですが JOIN が入ってくると列名が被る可能性があって 結局 WHERE や SELECT で 「tablename.columnname」 のようにテーブル名も含めて書くならあんまり変わりません
とはいえやっぱり長いし 実際に 「tablename.columnname」 と書くときはエイリアス使って 1, 2 文字のテーブル名です
なのでエイリアスを含めた 「tablealias__columnname」 にしておくのはありな気がしました
列名指定のためのエイリアスが不要になりますし

CREATE TABLE foo (
	f__id integer,
	f__name text,
	PRIMARY KEY (f__id)
);
CREATE TABLE bar (
	b__id integer,
	b__name text,
	b__f_id integer,
	PRIMARY KEY (b__id)
);

SELECT * FROM foo LEFT JOIN bar ON f__id = b__f_id WHERE f__id < 10;

SQL でグループごとの最大値の行がほしいとき
単純にやると group by して最大値の値を取り出してそれで where するという 2 段階な考え方

window 関数がいいよっという紹介をみたので 調べてみるとグループごとにソートしてランクとか順番のデータの列を作ることができるみたい
便利そうだけど対象テーブルの行全部にランクの列を追加した一時テーブル作ってそこから検索ってあんまり速くなさそう

気になったので実際に試してみた
データは 30000 件ちょっと
id 列が主キーで group_id 列はインデックスあり

PARTITION BY

explain analyze
select "id", "group_id"
  from (select "id", "group_id", row_number() OVER (PARTITION BY group_id ORDER BY id DESC) as n from "tbl") as "sub"
  where "n" = 1

"Subquery Scan on sub  (cost=3678.01..4868.84 rows=183 width=8) (actual time=29.683..41.953 rows=3 loops=1)"
"  Filter: (sub.n = 1)"
"  Rows Removed by Filter: 36705"
"  ->  WindowAgg  (cost=3678.01..4410.83 rows=36641 width=16) (actual time=29.681..40.401 rows=36708 loops=1)"
"        ->  Sort  (cost=3678.01..3769.61 rows=36641 width=8) (actual time=29.671..31.515 rows=36708 loops=1)"
"              Sort Key: tbl.group_id, tbl.id DESC"
"              Sort Method: quicksort  Memory: 3087kB"
"              ->  Seq Scan on tbl  (cost=0.00..900.41 rows=36641 width=8) (actual time=0.024..7.945 rows=36708 loops=1)"
"Planning time: 0.178 ms"
"Execution time: 42.396 ms"

MAX

explain analyze
select * from tbl where id = ANY (select max(id) from tbl group by group_id)

"Nested Loop  (cost=1083.97..1108.64 rows=3 width=80) (actual time=15.348..15.352 rows=3 loops=1)"
"  ->  HashAggregate  (cost=1083.68..1083.71 rows=3 width=4) (actual time=15.331..15.332 rows=3 loops=1)"
"        Group Key: max(tbl_1.id)"
"        ->  HashAggregate  (cost=1083.62..1083.64 rows=3 width=8) (actual time=15.327..15.328 rows=3 loops=1)"
"              Group Key: tbl_1.group_id"
"              ->  Seq Scan on tbl tbl_1  (cost=0.00..900.41 rows=36641 width=8) (actual time=0.020..5.036 rows=36708 loops=1)"
"  ->  Index Scan using tbl_pkey on tbl  (cost=0.29..8.31 rows=1 width=80) (actual time=0.005..0.005 rows=1 loops=3)"
"        Index Cond: (id = (max(tbl_1.id)))"
"Planning time: 0.410 ms"
"Execution time: 15.421 ms"

plan 的には PARTITION BY のほうが速いけど実際の結果は MAX のほうが速かった
何回かやっても一緒

短いし速いしなら MAX でよさそう

毎回のようによくわからなくなるのでまとめ

IN
IN で指定したどれかに行の値が一致すると true
NOT はどれにも一致しないと true
単純に IN にマッチしない行が true になる
「x NOT IN y」 と 「NOT (x IN y)」 は一緒

SELECT c
  FROM unnest(ARRAY[1,2,3,4,5]) as c
  WHERE c IN (1, 2)

-- 1, 2

SELECT c
  FROM unnest(ARRAY[1,2,3,4,5]) as c
  WHERE c NOT IN (1, 2)

-- 3, 4, 5

SELECT c
  FROM unnest(ARRAY[1,2,3,4,5]) as c
  WHERE NOT (c IN (1, 2))

-- 3, 4, 5

ANY
通常は IN と一緒
postgresql の場合は配列を指定するので $1 などのパラメータ化できるのが特徴
<> を使うと配列の要素のどれにもマッチしない行が true になる
「x <> ANY(y)」 と 「NOT (x = ANY(y))」 は別
「NOT (x = ANY(y))」 を使うと 「x NOT IN y」 と同じになる

SELECT c
  FROM unnest(ARRAY[1,2,3,4,5]) as c
  WHERE c = ANY(ARRAY[1, 2])

-- 1, 2

SELECT c
  FROM unnest(ARRAY[1,2,3,4,5]) as c
  WHERE c <> ANY(ARRAY[1, 2])

-- 1, 2, 3, 4, 5

SELECT c
  FROM unnest(ARRAY[1,2,3,4,5]) as c
  WHERE c <> ANY(ARRAY[1, 1])

-- 2, 3, 4, 5

SELECT c
  FROM unnest(ARRAY[1,2,3,4,5]) as c
  WHERE NOT (c = ANY(ARRAY[1, 2]))

-- 3, 4, 5

SOME
ANY と全く同じ

SELECT c
  FROM unnest(ARRAY[1,2,3,4,5]) as c
  WHERE c = SOME(ARRAY[1, 2])

-- 1, 2

SELECT c
  FROM unnest(ARRAY[1,2,3,4,5]) as c
  WHERE c <> SOME(ARRAY[1, 2])

-- 1, 2, 3, 4, 5

SELECT c
  FROM unnest(ARRAY[1,2,3,4,5]) as c
  WHERE c <> SOME(ARRAY[1, 1])

-- 2, 3, 4, 5

SELECT c
  FROM unnest(ARRAY[1,2,3,4,5]) as c
  WHERE NOT (c = SOME(ARRAY[1, 2]))

-- 3, 4, 5

ALL
配列の全てに一致する行が true になる
<> するとひとつでも一致しないのがあると true になる
「x <> ALL(y)」 と 「NOT (x = ALL(y))」 は別
「x <> ALL(y)」 と 「NOT (x = ANY(y))」 が同じになる

SELECT c
  FROM unnest(ARRAY[1,2,3,4,5]) as c
  WHERE c = ALL(ARRAY[1, 2])

-- none

SELECT c
  FROM unnest(ARRAY[1,2,3,4,5]) as c
  WHERE c = ALL(ARRAY[1, 1])

-- 1

SELECT c
  FROM unnest(ARRAY[1,2,3,4,5]) as c
  WHERE c <> ALL(ARRAY[1, 2])

-- 3, 4, 5

SELECT c
  FROM unnest(ARRAY[1,2,3,4,5]) as c
  WHERE NOT (c = ALL(ARRAY[1, 2]))

-- 1, 2, 3, 4, 5