ChatGPTが作った全ての欠番が探せるSQLクエリを読み解く

はじめに

業務で欠番を探す必要がありクエリを作ったのですが、ふとChatGPTにクエリを作ってもらったらどうなるか試してみました。

クエリの紹介

table_nameのidから欠番をすべて取得するクエリです。
id最大値までの中ですべての欠番を取得します。

ChatGPTによって生成されたクエリ(修正済み)

WITH cte AS(
    SELECT
        MIN(id) AS min_id,
        MAX(id) AS max_id
    FROM
        table_name
)
SELECT
    min_id + i - 1 AS missing_id
FROM
    cte,
    generate_series(min_id, max_id) AS i
    LEFT JOIN
        table_name
    ON  i = table_name.id
WHERE
    table_name.id IS NULL
;

僕の考えたクエリ

SELECT
    i
FROM
    generate_series(最小値, 最大値) AS i
    LEFT JOIN
        publishers
    ON  i = publishers.id
WHERE
    publishers.id IS NULL
;

ChatGPTのほうが最小値、最大値を動的に設定できます。
クエリは比較しやすいように一部記述を修正しています。

クエリ解説

with句

WITH cte AS(
    SELECT
        MIN(id) AS min_id,
        MAX(id) AS max_id
    FROM
        table_name
)

一時的な最小値と最大値のテーブルを作成しています。
これにより自分の作ったクエリのように最大値と最小値をgenerate_seriesにセットする必要がありません。
MAX(), MIN()をそのままgenerate_seriesに設定すればいいと思うかもですが、SELECT句でしか使えないのでエラーが出ます。

謎の-1

min_id + iだと結果が1ずれるので-1で修正しました。
そもそもmin_idつけなければよかったのでは？と記事を書きながら思ってます。

SELECT
    min_id + i - 1 AS missing_id

generate_series()

generate_series(min_id, max_id) AS i

postgre特有の関数、連番を作成することができる。
第3引数で連番を飛ばすことができる。

generate_series ( start integer, stop integer [, step integer ] )

FROM句で複数のテーブルを指定

FROM
    cte,
    generate_series(min_id, max_id) AS i

これは今回知ったことですが、FROM句はカンマ区切りで複数テーブルを指定できて内部結合ができるようです。
JOIN句よりも前からある書き方らしい？

generate_seriesによって生成された連番テーブル欠番のあるテーブルを

さいごに

ChatGPTでとりあえず動くクエリが作れるのはすごいなと思いました。(今更)