二番目に大きいデータに一致するレコードを取り出す

沖縄チームのキリです。SQLのあれっそういえばこれどうすりゃいいの？をなんとか解決する記事です。

例えば自動増分IDを使用している次のようなテーブルがあるとします。

SELECT id, user_id, content FROM articles

以下のようにユーザーごとの二番目に新しいレコードを取り出したいという場合、どのようなクエリを書けばいいでしょう？

パフォーマンスやグループ化に関連しないカラム等考慮してクエリを書くと意外と面倒なことに気付きました。

まずユーザーごとの最新のデータを取り出すクエリを考えます。

SELECT user_id, MAX(id) latest_id
FROM articles
GROUP BY user_id
ORDER BY user_id ASC

これは単純ですね。次に二番目に新しいレコードを取り出します。

SELECT t1.user_id, MAX(t1.id) AS second_to_last_id
FROM articles t1
INNER JOIN (
    SELECT user_id, MAX(id) AS latest_id
    FROM articles
    GROUP BY user_id
) AS t2
    ON t1.user_id = t2.user_id AND t1.id <> t2.latest_id
GROUP BY t1.user_id
ORDER BY t1.user_id ASC

さてなかなかしんどい記述量になってきましたね。最初は上のクエリで考えていましたが記述量とパフォーマンス的に次のクエリの方が良いような気がします。（もっと良い方法があれば知りたいです）

SELECT user_id, MAX(id) second_to_last_id
FROM articles
WHERE id NOT IN (
    SELECT MAX(id)
    FROM articles
    GROUP BY user_id
)
GROUP BY user_id
ORDER BY user_id ASC

ID2のユーザーはレコードを1つしか持っていないので2番目に新しいデータが取得できませんが、これをNULLとして表にしたいです。次はユーザーIDを抽出して先程のクエリと結合します。

SELECT t1.user_id, t2.second_to_last_id
FROM (
    SELECT user_id
    FROM articles
    GROUP BY user_id
) AS t1
LEFT JOIN (
    SELECT user_id, MAX(id) second_to_last_id
    FROM articles
    WHERE id NOT IN (
        SELECT MAX(id)
        FROM articles
        GROUP BY user_id
    )
    GROUP BY user_id
) AS t2 ON t1.user_id = t2.user_id
ORDER BY t1.user_id ASC

最後にsecond_to_last_idのレコードを結合して完了です。お疲れ様でした。

SELECT t1.user_id, t2.second_to_last_id, t3.content
FROM (
    SELECT user_id
    FROM articles
    GROUP BY user_id
) AS t1
LEFT JOIN (
    SELECT user_id, MAX(id) second_to_last_id
    FROM articles
    WHERE id NOT IN (
        SELECT MAX(id)
        FROM articles
        GROUP BY user_id
    )
    GROUP BY user_id
) AS t2 ON t1.user_id = t2.user_id
LEFT JOIN articles AS t3 ON t2.second_to_last_id = t3.id
ORDER BY t1.user_id ASC

シンプルなテーブルとシンプルな結果ですがクエリが長くて面倒…もっとスマートでパフォーマンスの良いクエリがあれば是非教えてください…。