AIに指示するときも役立つ！ややこしいSQLを書くときの考え方

ちょっとややこしい SQL を書いているときに、自分がどんなことを考えているのかをざっくりまとめてみました。具体的なテクニックではなく、考え方の話が中心です。

最近では、 SQL も生成AIに指示して書いてもらう、という選択肢があります。ですが、出来上がった SQL やその実行結果が正しいかどうかは、最終的に自分で判断しなければなりません。

それだけでなく、AIに指示を出す際も、 SQL の考え方を知っている方がより的確な指示ができるはずです。だからこそ、 SQL の考え方を知っておくことには、大いに意味があると思います。

そして何より、 SQL の可愛さを知ってください。

※ この記事のSQLはPostgreSQLを前提にしています。

1. 最終的な形をイメージする
2. その値はどこから来るのか考える
- 例えば
3. 個別に取得できるか試す
4. 面倒なところを頑張る
- 累計の考え方
まとめ

1. 最終的な形をイメージする

雑な図でも表でも何でも良いので、まずは最終的な出力のイメージを持つことが大切です。

このとき、分かりやすい名前をつけておくのも重要です。もし分からない値があれば、しっかり確認しておきましょう。

生成AIに SQL を書いてもらう場合でも、自分が欲しい値を正確に説明する必要があります。

ここを曖昧にすると、意図した結果が得られないこともあるので、慎重に整理しておきたいところです。

2. その値はどこから来るのか考える

SQL　を書く前に、データの出どころをしっかり把握しておくことが大切です。次のようなポイントを整理すると、後の作業がスムーズになります。

どのテーブルを使えばそれが取得できそうか
どのカラムを参照するのか
カウントするのか、合計するのか、名前が必要なのか
加工する必要があるか
group by は必要か
join もありそうか

ここで具体的に考えておけば、後が楽になります。逆に曖昧なままだと、途中で迷ったり、手戻りが発生したりしがちです。

もし分からないことが出てきたら、一つ前に戻って整理し直しましょう。

例えば

こんなデータが欲しいと言われたとします。

何年何月何日(月曜の日付)
その週の合計売上額
その週の登録ユーザ数
その週の退会ユーザ数
その週の退会率(登録と同月に退会)
その週の退会率(継続後退会)

この場合、次のように考えます。

週ごとに group by できれば良さそう → isoyear と week を使う？
売上額はレポートテーブルを group by すれば週ごとに集計できそう
登録ユーザ数はユーザテーブルの登録日時から週を算出すれば良さそう
退会率は退会ユーザ数と全体のユーザ数があれば計算できそう
退会ユーザ数は退会日時から週を出せば取れる
退会率2つは、登録日時と退会日時が同じ月のユーザを退会週毎にカウントすればいけそう
全体のユーザ数は、その週以前に登録したユーザをカウントすれば求められそう（少し面倒かも？）

このくらいまでイメージしておきます。

ここまでイメージできると、「週ごとに group by して、それを軸に join すればいけそう」と考えられるようになります。

さあ、そろそろ SQL を書きたくなってきましたよね？

3. 個別に取得できるか試す

いきなりすべてを書こうとすると大変なので、少しずつ試していきます。

不安な場合は、1つの値に対して SQL を1文ずつ書くのが良いでしょう。複数の処理をマージするのは、「確実に一緒にできる」と判断できてからで問題ありません。

もし取得方法が分からなくなったら、一つ前のステップに戻って整理し直します。

この時点で「この2つは一緒に取得できそう」と思えたら、そのときにマージします。うまくいった SQL はメモしておくと便利です。

今回の例であれば、

週毎の売上額
週毎の登録ユーザ数
週毎の退会ユーザ数
登録した同月に退会したユーザーの数

といったデータを、まずは個別の SQL で取得してみます。

そうすると、基本的にはすべて isoyear と week で group by しているのが見えてきます。そうしたら最後にそれらを join すれば良いと分かってきます。

「なんだ、できるじゃん！」という気持ちになり、楽しくなってきます。

4. 面倒なところを頑張る

例えば、退会率を求める際に「その時点までのユーザ数の累計」が必要になることがあります。

このような累計の集計処理は、少し考えるのが面倒かもしれませんが、よくあるケースです。調べれば実例が見つかるはずですし、それこそ生成AIに相談するのも一つの手でしょう。（なので、頑張れ！）

せっかくなので、累計の考え方もまとめておきます。

累計の考え方

例えば、週ごとの登録ユーザ数の select とその結果が以下だったとします。

with weekly_users as (
    select
        extract(isoyear from created_at) as year,
        extract(week from created_at) as week,
        count(*) as count
    from users
        group by extract(isoyear from created_at), extract(week from created_at)
)

select * from weekly_users

year	week	count
2024	52	1
2025	1	2
2025	2	3
2025	3	4

累計を求めるために、まずは cross join で全組み合わせを作成してみましょう。

select
    u1.year, u1.week, u1.count, u2.year, u2.week, u2.count
    from weekly_users as u1
        cross join weekly_users as u2

その結果はこうなります。結果が爆発的に増えますが、心配しないで大丈夫です。

u1.year	u1.week	u1.count	u2.year	u2.week	u2.count
2024	52	1	2024	52	1
2025	1	2	2024	52	1
2025	2	3	2024	52	1
2025	3	4	2025	52	1
2024	52	1	2025	1	2
2025	1	2	2025	1	2
2025	2	3	2025	1	2
2025	3	4	2025	1	2
2024	52	1	2025	2	3
2025	1	2	2025	2	3
2025	2	3	2025	2	3
2025	3	4	2025	2	3
2024	52	1	2025	3	4
2025	1	2	2025	3	4
2025	2	3	2025	3	4
2025	3	4	2025	3	4

ここから必要な行を見極めていきましょう。

ここで u1 は、週ごとの結果を得るためのテーブルで、u2 は累計を得るためのテーブルとして使っています。

この場合、u2 は「過去の数値の累計」を求めるために使用する必要があります。したがって、u1 の年週よりも前の週（または同じ週以前）のデータだけを残す必要があります。

そのため、inner join を使用し、on 条件で u1 の年週と u2 の年週を比較して、必要な行だけを絞り込みます。

select
    u1.year, u1.week, u1.count, u2.year, u2.week, u2.count
    from weekly_users as u1
        inner join weekly_users as u2
        on u1.year > u2.year
            or (u1.year = u2.year and u1.week >= u2.week)

この結果はこうなります。

u1.year	u1.week	u1.count	u2.year	u2.week	u2.count
2024	52	1	2024	52	1
2025	1	2	2024	52	1
2025	2	3	2024	52	1
2025	3	4	2025	52	1
2025	1	2	2025	1	2
2025	2	3	2025	1	2
2025	3	4	2025	1	2
2025	2	3	2025	2	3
2025	3	4	2025	2	3
2025	3	4	2025	3	4

ここまできたら、あとは u1.year, u1.week で group by して u2.count を sum すれば、こうなるはずです。

select
    u1.year
    , u1.week
    , max(u1.count) as "週ごとの登録者数"
    , sum(u2.count) as "累計登録者数"
from weekly_users as u1
    inner join weekly_users as u2
        on u1.year > u2.year
            or (u1.year = u2.year and u1.week >= u2.week)
group by u1.year, u1.week