巨大SQLに対する解読術

こんにちは！ゲンシュンです。
この記事は執筆中に「もはやAIに聞けば済むことね？」と思い投稿をお蔵入りしたものの、やっぱりまだ必要な知見かもしれないと思い直して残骸から蘇生したものとなります笑。

背景

前職では古来から存在するアプリケーションの集計用SQLをdbtを導入してリファクタリングしたり、現職では作成者が不在となった1500行超える大量のスケジュールクエリを全てDataformで書き直したり、SQLの考古学を強いられる機会がたくさんありました。ドメイン知識がないので手探りなものから、全てのカラムに select any_value(xx having max created_at) が実装されているものまで多種多様な考古学をしてきたので、読みづらいSQLの解読やデバッグで自分が意識していることを言語化してみました。

解読編、デバッグ編、作り直し編みたいな順序でまとめます。
※ぶっちゃけ、GeminiやGithub Copilotに聞けば大体済む気もしてます

解読編

秘伝のタレのように、古代から継ぎ接ぎを続け膨大に成長してしまったSQLの解読が必要になったケースを想定して書いてみます。かなり簡素になってますが、以下のSQLをサンプルとします。一応この記事ではわかりやすくするために、解読対象のSQLは予約語小文字、自分が書くSQLは予約語大文字にしています。

with a as (
  select ,,,,
),
b as (
  select ,,,
)
c as (
  select ,,,
)
select * from a
left join b
left join c .....

ユニークキーの把握

まず最初に、このSQLによるアウトプットは結局何を集計・抽出しているのかを把握することから調べます。そのために集計粒度、つまりユニークキーをまず知る。 user_id 単位でユニークなのか company_id と target_month の複合ユニークかのか。
サクッと把握するために、サンプルSQLを全部WITH句で囲ってユニークキー候補のカラムで COUNT DISTINCT します。テーブルの全レコード件数と COUNT DISTINCT した件数が一緒 → ユニークキー発見だ！的なノリです。複合ユニークの判定は、自分はハイフンで雑に文字列結合して判断しちゃってます。

WITH tmp AS (
-- 対象SQLをここに全部含ませる
)

SELECT
  COUNT(*),
  COUNT(DISTINCT ユニークキー候補),
  COUNT(DISTINCT CONCAT(ユニークキー候補1, "-", ユニークキー候補2))
FROM tmp

補足ですが、複合ユニークキーがわかってて件数を知りたいのなら DISTINCT ユニークキーa,ユニークキーb で済むと思いますが、テーブル全体のレコード数もユニークキーもわかっていない状況で都度クエリするのが面倒なので上記のようなクエリを叩いてます。文字列結合はハイフンじゃなくてもいいんですが、ユニークキーがUUIDを除いた文字列であることがほぼ無いことを鑑みて雑にハイフンで結合してます自分は。

サブクエリから構成を把握する

集計粒度がわかった次に全体構成の把握をします。WITH句はある程度意味のある単位で処理しているはずなので、何の塊を作っているのかをサブクエリ単位で見ていきます。サブクエリが無くてひたすらLEFT JOINテーブルしてる場合は、登場するテーブルと結合条件だけざっくり見ます。
サンプルSQLだとこんな感じでざっくり把握してます。

-- このSQLのWITH句はa,b,cの3つある
-- 最終アウトプットのユニークキーはa.idである
-- つまり「3つの集合(a,b,c)を何かしらの条件でくっつけて、a.id単位で集計している」だけ理解すれば十分
-- a,b,cってどういう粒度なのか？何でJOINしているんだろう〜？を次に把握していく
with a as (
  select ,,,,
),
b as (
  select ,,,
)
c as (
  select ,,,
)
select * from a
left join b
left join c .....

WITH句が20個あったり多重ネストしてると簡単に把握できないので、その場合は以下のような流れで大雑把に把握してます。
基本的に、大きな塊 → 次の大きな意味のある塊 → その次に小さい意味のある塊、と解像度をあげていくのを地道にやっていくだけ。。。

-- 今回はWITH句が大量にあるケースをサンプルとする
-- 20個のWITH句...
with a as ( select ,,,),
d as (
  select ... from a
  left join b
  left join c
),
x as (
  select 加工 from v
),
y as (
  select 集計 from x
  group by キー
),
z as (
  select * from y
  where 特定の条件
)

-- 最終アウトプット
select * from d
left join z

上記のサンプルの最終アウトプットを見ると、d句とz句を把握すれば実質済むことがわかる

• d句 → a,b,cをjoinしているだけじゃん！
• z句 → y句をfilterしただけ → y句はx句を集計しただけ → x句はv句を加工しただけ → 実質v句だけ把握すればいいじゃん！

みたいな感じで、サブクエリの構造を地道に見て理解する対象をしぼめていくイメージです。

サブクエリを攻略する

攻略対象のサブクエリがわかったらSQLを自分で書いて、何をどう処理しているのか？そのサブクエリの結果は何かしらのユニークキーでまとまっているのか？等を頑張って理解していきます。ある意味デバッグ作業に近いので、サブクエリの攻略方法はデバッグ編で詳細に書きます。
500行を超えるSQLは、上から順番にサブクエリを理解していくより、攻略対象の塊の優先順位つけてざっくり理解していく方が手っ取り早いと思ってます。

デバッグ編

ある日突然データ欠損や集計値に違和感があることがわかり、大量のSQLを読まざるを得ない時がありますよね。違和感の正体を早く見つける術は正直ないんですが、1つ1つの解読を早める技は色々あると思うので、ちょこっと紹介します。

サブクエリをサクッと把握

サンプルSQLの最終SELECT文をとりあえず全部コメントアウトします。で、知りたいサブクエリのSELECTを書きます。

with a as (
  select ,,,,
),
b as (
  select ,,,
)
c as (
  select ,,,
)
-- 最終アウトプットコメントアウトする
/*
select * from a
left join b
left join c .....
*/

-- 知りたい句について知りたいことをselectする
SELECT
  COUNT(*),
  COUNT(DISTINCT a句のユニークキー候補),
  COUNT(DISTINCT CONCAT(a句のユニークキー候補1, "-", a句のユニークキー候補2))
FROM a

これを攻略対象のサブクエリ単位で、知りたいことを地道に把握していきます。レコード数、ユニークキー、期間、なんちゃらTypeの値ごとの数などを調べながら、以下のようなイメージを持てるようにします。

with a as (
  -- user_id単位の流入データで面談idと日付を持ってる
),
b as (
  -- 面談id単位の面談履歴データ
),
c as (
  -- userごとの得意科目が配列で格納されている
)

-- 最終アウトプットはa.user_idがユニークキーである
select * from a
left join b
left join c

上記の粒度までわかると

• a句とb句は面談idで、a句とc句はuser_idでJOIN可能である
• ドメイン知識が求められるが、恐らくユーザは複数面談可能なのでuserと面談は1対多である。また得意科目が配列で集約されてるのでuser_idとは1対1であるはず
• 最終アウトプットが a left join b ってことは単純に考えると user_id ユニークじゃない可能性がある
• でもCOUNT DISTINCTでuser_idユニークなのは把握したので、つまりwhereの条件とかに集計ロジックありそう

という細かい部分は後回しでいいので、確度の高い推測を持てるようにしたい。実態の重複データと比較して「じゃ、whereの条件にバグがあってuser_idに対して複数のレコードが紐づいているんじゃね？」という怪しいポイントを早く見つけやすくなる、ような感じです。
まぁ、こんな上手くいかないんですけど、打率がちょっっっとでも上がるはず・・！

多重ネストは分解するしかない

以下のようなSQLは、ぶっちゃけ書き直してます笑。一番内側をwith句X、その次をwith句Y、最後をwith句Zとし、今までと同じようにレコード数とユニークキーが一緒であれば「多重ネストの書き直しSQL」が正しいと判断しちゃいます。

-- 地獄。読めん。これのレコード数とユニークキー把握
select * from ( -- 外側をz
  select * from ( -- 真ん中をy
    select * from ( -- 内側をx
)))

-- 内側から順番にサブクエリ化する
WITH x AS (),  -- 一番内側の処理
y AS (
  SELECT * FROM x  -- 2段目の処理
),
z AS (
  SELECT * FROM y  -- 3段目の処理
)
SELECT * FROM z  -- 最終アウトプットが書き直し前のレコード数とユニークキーが合致してたら一緒だと判断

自分はこれらを駆使し、事業ドメイン詳しい人にヒアリングしながら、ざっくり解読してます。まぁ結局泥臭いことを続けることに変わりないです。。

書き直し編

品質担保的な話です。これまでたくさんのSQLを正しいロジックにわかりやすく書き直したんですが、既存実装が間違っていたSQLを書き直したパターンや、当時の実装者や開発に関わった方々がご卒業されており実装の背景が一切不明なパターンなどが、正しさの判断で結構面倒でした。妥当性チェック周りで自分が考えていることをまとめます。

既存実装が正しい時

既存SQLが正しくモデリング含めてキレイに書き直した場合はシンプルに、新旧のアウトプットの差分がなければOKとなります。レコード数やユニークキーなど比較しやすいものなら問題ないんですが、各カラムの値とか含めて中身ちゃんと比較したいが、レコード数多くて難しい〜っていうケースは、以下のようなSQLを書いて、ランダムに抽出されたID(ユニークキー)で比較してます。

WITH target_ids AS (
  WITH temp_row_numbered_data AS (
    SELECT
      *,
      -- ユニークキーでソートしたrow_numberを付与
      ROW_NUMBER() OVER (
        ORDER BY ユニークキー
      ) AS rn
    FROM
      新テーブル
  )

  SELECT
    ユニークキー
  FROM 
    temp_row_numbered_data
  WHERE
    -- 1万件ごとにサンプリング
    MOD(rn, 10000) = 0
)

SELECT
  *
FROM
  旧テーブル
INNER JOIN target_ids USING (ユニークキー)

既存実装が間違ってる時

どうやら正しくないことが発覚した時です。
これは本当にケースバイケースなので断言する話ではないんですが、基本的には「書き直せるタイミングで、正しいと思われるロジックで実装し直す」でいきたいです。ですが、すぐ修正するがいいとは必ずしも限らない時があるのが辛いんですよね、軽微な修正内容だったとしても。アプリケーション開発でもあるあるだと思いますが、SQL周りで自分が考えていること2パターン書きます。

1. データ利用者と意思決定者が多い場合

いきなりSQL修正したら「実装的に正しい集計データ」が作られたとしても、BIツールやSQL叩いている利用者からすると「あれ？なんかデータ変わってね？おかしくなった？」になります。データを元に意思決定されている方の今までの判断軸がブレてしまったり、既存の集計ロジックを元に業務フロー組んでるケースだって全然あります。
後々のことを考えると現場への影響を最小限にした方がいいケースが多いので、既存の仕様と修正後の仕様、それに伴うデータがどう変わるのかをちゃんと周知・説明した上で修正した方がいいです。やっぱ今じゃねぇな..となったら、作り直しフェーズでは既存仕様ベースで実装し、別途改修（ちゃんと期日を切った前提で）したいですね。

2. ぶっちゃけ何が正しい仕様なのか今決められない場合

なんかたまーにあるんですよね。「当時の集計仕様の意思決定者が不在で、意図がわからない」や「当時と比べ状況が変わったので、自分が考える仕様の方が今だと理にかなってる気がする」みたいなケースって、仕様の意思決定をしなきゃいけないので巻き込む対象がそれなりにあるかなと。新テーブルへの移管で精一杯なのに、こういう時に旧実装の負債を引いてしまうんですよねぇ・・・笑。

まとめ

長々と書きましたが、ぶっちゃけAIのSQL解読スピードすんごい早いので、AIをいい感じに活用しちゃいましょ！笑