履歴テーブルにおいて最新のデータを取得するようなSQLを必死に書いてみた

はじめに

どうもこんにちは torihaziです

今日は絶賛SQL祭りです。

プログラミング始めたてでやった生のSQLを、

Railsに毒されすぎて、生のSQLなんてほとんど書かなくなった今になって

書かなければならなそうな事態に陥り、

案の定詰まった。

が、無事解決できたのでそれを書き残そうと思います。

本当に何もSQL理解してへんやったんなと思います。

ちなみにそれ動かないでしょ、みたいなコードもそのままエラー付きで書いてます。

最後には解決してると思うので温かい目で見てください。

ふぁいと

どういうもの？

状況を説明すると、4つテーブルがあります。

• A has many B
• B has many C
• C has many D
• C にはversionという概念がある。(履歴テーブル？というのかしら)

このような時、あるA(idが渡されるとする)に紐づくB、Bに紐づくCでかつその中でも最新のCに紐づくDを合わせて取って来たいとする。

この時にどうするかという。

やってみる

とにかく、ひたすらに INNER JOINすればいいのはわかるが最新のCというところだけ気を使う必要がありそう。

第一段階は

SELECT 
    * 
FROM 
    A 
    INNER JOIN B on A.id = B.a_id
    INNER JOIN C on B.id = C.b_id
    INNER JOIN D on C.id = D.c_id
WHERE
    A.id = "hogehoge"

ここから最新のCということで頑張っていく。

色々調べてサブクエリというのを使うのが良さそうだということでやってみた。

第二段階

SELECT 
    * 
FROM 
    A 
    INNER JOIN B on A.id = B.a_id
    INNER JOIN (
            SELECT 
                B.*,
                MAX(B.version)
            FROM
                B
            GROUP BY B.key
    ) as latest_B on latest_B.id = C.b_id
    INNER JOIN D on C.id = D.c_id
WHERE
    A.id = "hogehoge"

これでいけるのかしら。

column "B.id" must appear in the GROUP BY clause or be used in an aggregate function

なーに、これ。

SQLエラー:must appear in the GROUP BY clause or be used in an aggregate function - diadia

GROUP BYを使っているのに集約関数を使ってないから、だそうだ

集約関数って何。

SQLの集計関数（COUNT, SUM, AVG, MAX, MIN）｜GROUP BY句、HAVING句と組み合わせた使用方法も合わせて解説します。

らしい。MAXあるやん。なんでや。

GROUP BYを使用した時には、SELECTでは集約関数を伴わない限りは、基本的にGROUP BYで指定した項目しか記述できません。

SQL GROUP BYでエラーが起きる原因

なるほど。今回はGROUP BY で B.keyの指定しかしていないのにも関わらず、selectでそれ以外のものも出力しようとしたから

エラーが出たのか。

だとしたら？

どうすればいいのかというと、

SELECT 
    * 
FROM 
    A 
    INNER JOIN B on A.id = B.a_id
    INNER JOIN (
            SELECT 
                MAX(B.version)
            FROM
                B
            GROUP BY B.key
    ) as latest_B on latest_B.id = C.b_id
    INNER JOIN D on C.id = D.c_id
WHERE
    A.id = "hogehoge"

サブクエリのSELECTに指定できるものが集約関数使ったものかB.keyしか使えないのだが、

それだとのちのINNER JOINでidが持って来れないので、、、

どうしたものか。

B.keyでグルーピングして、versionが新しいものの全てのレコードをselectで持ってきたい。

B.key	version	他のdata
1	0	hgoe
1	1	huga
1	2	ge
2	0	geee
2	1	fefe
2	2	fefef

【SQL】DISTINCT ONの使い方まとめ【PostgreSQL】 #初心者 - Qiita

distinct on 使ってみる。

SELECT 
    * 
FROM 
    A 
    INNER JOIN B on A.id = B.a_id
    INNER JOIN (
            SELECT 
                DISTINCT ON (B.key) *
            FROM
                B
            ORDER BY
               B.version DESC
    ) as latest_B on latest_B.id = C.b_id
    INNER JOIN D on C.id = D.c_id
WHERE
    A.id = "hogehoge"

こうしたらversionの降順にした後まとめるらしいので、最新が取って来られる仕組みらしい。

だが。

SELECT DISTINCT ON expressions must match initial ORDER BY expressions

distinct onに設定するものはorder byの最初に与えろということでこうした。

SELECT 
    * 
FROM 
    A 
    INNER JOIN B on A.id = B.a_id
    INNER JOIN (
            SELECT 
                DISTINCT ON (B.key) *
            FROM
                B
            ORDER BY
               B.key,
               B.version DESC
    ) as latest_B on latest_B.id = C.b_id
    INNER JOIN D on C.id = D.c_id
WHERE
    A.id = "hogehoge"

ようやく出た。

ベンダーロックイン

調べてみるとこの言葉に辿り着いた。

DISTINCT ON って postgresqlにしかないらしい。

つまり他のmysqlで動かそうにもダメらしい。

多分実務だとpostgre以外にならなそうだから大丈夫だと思うけど、

嫌な未来は早めに消しとくべきなのでもう少し粘る。

履歴テーブルから最新の1件を取ってくる方法 - そーだいなるらくがき帳

なんか窓関数とか言われるもんがあるらしい。

【データ分析】知っててよかったSQL3選 - KIYONO Engineer Blog

ROW_NUMBERとかいうやつ。

SELECT 
    * 
FROM 
    A 
    INNER JOIN B on A.id = B.a_id
    INNER JOIN (
            SELECT 
                *,
                ROW_NUMBER() OVER (PARTITION BY B.key ORDER BY B.version DESC) as num
            FROM
                B
    ) as latest_B on latest_B.id = C.b_id AND latest_B.num = 1
    INNER JOIN D on C.id = D.c_id
WHERE
    A.id = "hogehoge"

これでもいけた。

結論

なっがすぎ。

なんなんこれ。

postgresqlから変わらないことを願う。

最後の確かに便利だけど、カラムを一個追加しないといけないのが面倒。

Railsで返す時はシリアライザで外すのかな？

そんなことするならdistinct onの方が良さげな気はする。

どっちがいいんでしょう。

わかるひと教えてください。