SQL初学者が2ヶ月コードを書いて感じたこと

はじめに

こんにちは!
ourlyでバックエンドエンジニアをしております @naoto_911 です。
業務としてエンジニアリングを始めてから今まではRuby/Railsでの新規開発をずっとやってきましたが、ここ2ヶ月ほどデータ分析基盤の構築に携わっており、初めて生のSQLを書きました。データ集計のクエリを数千行ゴリゴリ書き続けたおかげで、初心者を無事脱出し、ある程度SQLに対する理解度が上がったので初学者のころと変わったことを言語化します。

初学者とのギャップ

①「加工」の操作の解像度が上がった

SQLの中でカラムを用いた操作には「選択」と「加工」の2つに分類できると思います。
「選択」は、必要なカラムを”そのまま”用います。(ただのSELECT句のことなので割愛します。)
「加工」は、カラムを”集計/置換/条件分岐”した後で「選択」します。

以下に、「加工」の代表的なものを記載しました。

集計系:

SUM
COUNT
AVE
MAX
MIN

置換系:

CAST
COALEASE

条件分岐系:

CASE

この「加工」の操作について
・どういった挙動をすることができるか?
・いつ使うのか?
・使う時の制約は?
などの解像度が上がることで、実装時の戦略が早く立てられるようになりました。

例えば、
集計時に、「GROUP BYで指定したカラム以外も一緒にSELECTはできない」とか

-- aがGROUP BYで指定してないからSELECTできない
SELECT,
    a,
    b,
    COUNT(b)
FROM
    table
GROUP BY
    b

CASEを使うタイミングは、「SELECT句の値を直接条件分岐させる」使い方以外に、「集計時のデータを事前に分岐させる」使い方ができる

--  SELECT句の値を直接条件分岐する用いるパターン
SELECT
    CASE
      WHEN a = 'hoge' THEN FALSE
      ELSE TRUE
    END AS flag
FROM
    table

--  集計時のデータを事前に分岐させるパターン
SELECT
    SUM(CASE WHEN a = true THEN 1 ELSE 0 END) AS flag
FROM
    table

このように自分の言葉でこれらを「説明可能な状態」にできていなければまずは、ここを意識して整理しておくことをオススメします。

②JOIN時のON句で、事前に部分集合を作るようになった

初めて、SQLで結合を用いた時に私は、以下のフローで絞り込みをしていました。
・結合条件でテーブルA,Bをjoin
・joinしたテーブルに対して、Bのカラムの値で絞り込みを行う
・絞り込まれた結合テーブルが完成

SELECT
    ...
FROM
    table_A
    JOIN table_B
        ON table_B.a_id = table_A.b_id
WHERE
    table_B.b = 絞り込む条件

一方、実務で使われているコードを読むと以下のフローで絞り込みを行うことがあることを知りました。
・結合時に条件でBのテーブル自体から絞り込みを行う
・絞り込まれた結合テーブルが完成

SELECT
    ...
FROM
    table_A
    JOIN table_B
        ON table_B.a_id = table_A.b_id
        AND table_B.b = 絞り込む条件 -- ここでテーブルB自体の部分集合が作れる(絞り込み可能)

このように、結合とは、 「データを増やす操作に一見見えるが、実はデータを同時に絞り込むことも可能である」 ということに気づくことができていませんでした。

SQLは集合指向の言語です。複数のテーブルを用いて集合関数的に論理積和を扱います。
テーブルAとテーブルBを結合するということは、A,Bを何らかの形で集合的に解釈をすることを意味します。しかし、今回の場合だと、テーブルBに対してあらかじめ部分集合を作り出しておき、その部分集合をテーブルAと結合しています。

つまり、以下の発想の転換が必要です。

元々の記述方法が問題があるわけではないですが、あらかじめ考慮しないでよいデータを部分集合を作り出して排除することで、読み手のレビュー時の脳のメモリ負荷を削減できるので良いなと思いました。

③LEFT JOINと INNER JOIN の使い分けが言語化できるようになった

コードを書き始めた当初は、LEFT JOINとINNER JOIN をどう使い分けるかが言語化できてませんでした。

結論は以下です。
LEFT JOIN: レコード数(縦幅)を減らしたくない場合
INNER JOIN: レコード数(縦幅)を減らしたい場合

まず、テーブルを結合する時点でカラム(横幅)は増えます。
また、JOINは結合条件に複数一致したら複数レコードが増えます。
そのためレコード数が増えることはLEFT JOIN, INNER JOINどちらもありえます。
しかし、レコード数が減ることはINNER JOINでしかありえません。
そのため、今扱っているテーブルのデータに対してレコード数(縦幅)がどうなって欲しいのか? を考えることがポイントです。

以下に例を示します。
例えば、以下のような「ユーザーテーブル」と「成績テーブル」があったとします。

「対象のユーザー全員分の成績テーブルを表示する場合」は、

・「対象のユーザーのリスト」のレコード数は絶対に欠損させてはいけないです。
・そのため、こちらを軸にLEFT JOINを用います。
・LEFT JOINを用いることで、レコード数が減ることはないので、必要なカラムを追加していけばよいです。

SELECT
    ユーザーid,
    名前,
    科目,
    得点
FROM
    ユーザーテーブル
    LEFT JOIN 成績テーブル
        ON 成績テーブル.ユーザーid = ユーザーテーブル.id

「ユーザーの中から欠点(成績が60点以上)の人だけを表示する場合」ならば、

・「対象のユーザーのリスト」のレコード数をこれから減らしたいです。
・そのため INNER JOIN を用います。
・INNER JOINの条件で絞りこむ部分集合を作り出すことでこれが実現可能です。

SELECT
    ユーザーid,
    名前,
    科目,
    得点
FROM
    ユーザーテーブル
    INNER JOIN 成績テーブル
        ON 成績テーブル.ユーザーid = ユーザーテーブル.id
        AND 成績テーブル.得点 >= 60

このように、テーブル結合操作の目的挙動について言語化できるようにすることで、実装の戦略を立てる際のスピードが向上するのでおすすめです。

④実装前の思考プロセスの変化

実装前の思考プロセスにも変化がありました。

before
・最終的な出力に必要なカラムを場当たり的に取得してくる

after
・最終的な出力に必要なテーブルを特定する
・そのテーブルを取得するために必要な変数を特定する
・その変数をもった中間のテーブルを特定する
・これらを順番に結合していきながら、必要なデータだけを絞りこむ

つまり、「何のデータが必要でどのテーブルから取れるのか?」と「そのテーブルの特定に必要な変数を認知」した上で、「変数を集めるように中間テーブルを結合」し「最後に絞り込む」 ということです。

このように、SQLを完成させるまでのフローとは「パズルを組み必要な絵柄だけ写真をとる」ことと等価だと思いました。
「テーブルを結合していく作業」は、パズルのピースを繋ぎ合わせていくような感じで、島をどんどん大きくしていく作業と似ています。
「データを取得する作業」は、完成したパズルの中から必要な絵柄の部分だけ写真を撮るという作業と似ています。

実装進める前の戦略立てがうまくいかない場合は、すでに戦略が立てられる人から思考プロセスに着目してスキルデリバリーしていくと効果できたと感じました。

最後に

SQLを触り始めた時は、「自分はセンスがないな」と正直焦りました。
ただ実務で必要になり、キャッチアップをして、実際にコードを書いていくことで、短期的にほとんどの集計処理を実現できるようになりました。また、学習し始めた頃と今でもう一度学習するとしたら上記の観点を意識してキャッチアップをすると効果的だったと個人的には感じています。
ですので、これからSQLを扱う方もぜひ参考にしていただけばと思います。