SQLのヒントとコツ

 (github.com/ben-n93)
7 ポイント 投稿者 GN⁺ 2024-09-26 | 2件のコメント | WhatsAppで共有
  • データ分析業務でよく使うSQLの書き方の習慣とクエリパターンを集めた一覧であり、すべてのRDBMSに同じように当てはまるとは限らないという前提がある
  • 可読性の面では、先頭カンマ、WHERE 1=1、インデント、CTE、コメント、USINGによって、クエリを読みやすく修正しやすくする方法を推奨している
  • データ処理では、anti-join、QUALIFY、GROUP BY ROLLUP、EXCEPTのように、実務で結果のフィルタリング・合計生成・テーブル差分確認に使う構文を例示している
  • パフォーマンスと正確性の面では、NULLが混ざったNOT IN、暗黙的型変換、計算フィールドのalias衝突が、クエリ結果や速度を不安定にすることがある
  • 複雑なクエリでは、実行順序、ドキュメント確認、カラムの出所明記、保存クエリ名といった基本的な習慣が、デバッグと再利用性を高めるうえで重要である

SQLの可読性を高める書き方の習慣

  • このリポジトリは、長年かけて身につけたSQLのヒントとコツを整理した一覧であり、データアナリストの日常業務で役立つものや、SQLを書き始めた頃に知っておきたかった内容を中心としている
  • 一部のヒントは、すべてのRDBMSに適しているとは限らない

  • 先頭カンマと先頭AND

    • SELECT句のフィールド区切りには、末尾カンマよりも先頭カンマを使う方法を推奨している
    • 新しいカラムなのか、改行されたコードなのかが、より明確に見える
    • 行の長さが異なっていても、カンマの抜け漏れを見つけやすい
    • 同じ理由で、WHERE句の条件の前にも先頭ANDを置ける

  • WHERE 1=1で条件テストを簡単にする

    • WHERE句にダミー条件1=1を入れておくと、テスト中に条件をコメントアウトしてもクエリが壊れない
    • すべての条件をコメントアウトしても1=1が残るため、クエリはそのまま実行できる

  • インデントとフォーマッタ

    • インデントは、同僚や将来の自分がクエリをより読みやすくする助けになる
    • チームや会社のガイドラインがあるならそれに従い、なければ自分に合った方法を使うのがよい
    • オンラインフォーマッタのpoorsqlや、リンターのsqlfluffを利用できる

  • 複雑なクエリではCTEを検討する

    • inline viewを2〜3段階以上ネストすると、数週間後に見直したとき理解しにくいクエリになりやすい
    • CTEは、長いクエリをより整理された形にし、再利用性とデバッグを助ける方法として示されている

  • コメントは「なぜ」を説明する

    • 時間が経つと、特定の処理をなぜ行ったのか思い出しにくくなることがある
    • コメントは一般に、コードが「どのように」動くかよりも、なぜそうしたのかを説明するほうがよい
    • 例では、新しいCMSがarchive動画フォーマットを処理できないため、archiveコンテンツを除外する条件にコメントを付けている

  • 同名カラムの結合にはUSING

    • 2つのテーブルを同じ名前のカラムで結合するとき、USINGを使うとONより簡潔に結合を表現できる
    • USINGは共通カラムを結果から重複排除し、1つだけ返す
    • ONを使う際に共通カラムを明示しないと、ambiguous column nameエラーになることがある

データ処理に役立つ構文

  • anti-joinで他テーブルに存在しない行を探す

    • anti-joinは、一方のテーブルにはあるが他方のテーブルにはマッチしない行を返すときに使う
    • 例では、archiveされていないコンテンツのvideo_idだけを取得するケースを扱っている
    • 実装方法はいくつかある
    • LEFT JOINの後、結合先テーブルのキーがNULLの行だけをフィルタする
    • NOT INとサブクエリを使う
    • NOT EXISTSと相関サブクエリを使う
    • NOT INはNULL値のため意図どおりに動かないことがあるので、使用は推奨されない

  • QUALIFYでウィンドウ関数の結果をフィルタする

    • QUALIFYを使うと、ウィンドウ関数の結果に基づいてクエリ結果をフィルタできる
    • inline viewなしでフィルタできるため、コード行数を減らせる
    • 例では、製品ごとの上位10市場をDENSE_RANK()で選び、その後QUALIFYでフィルタしている
    • QUALIFYはSnowflake、Amazon Redshift、Google BigQueryのような大規模データウェアハウスでのみ提供されているようだという制約がある

  • カラム位置ベースのGROUP BYとORDER BY

    • カラム名の代わりにカラム位置でGROUP BY 1、ORDER BY 2のように書ける
    • 一時的または単発のクエリには便利な場合がある
    • 本番コードでは、常にカラム名を直接参照する方法を推奨している

  • GROUP BY ROLLUPで総計を作る

    • GROUP BY ROLLUPは、小計と総計を作るのに使える
    • 例では、部署ごとの給与合計を求めつつ、全体の給与総計行も同時に生成している
    • Transact-SQLのドキュメントでは、ROLLUPはカラム式の組み合わせごとのグループを作り、右から左へグループ数を減らしながら小計と総計を生成すると説明されている
    • COALESCEを適用すると、総計行をTotalのように表示できる
    • 総計行が結果の末尾に来るよう、ソート用カラムに注意する必要がある

  • EXCEPTで2つの結果セットの差分を見つける

    • EXCEPTは、1つ目のクエリ結果にはあるが2つ目のクエリ結果にはない行を返す
    • EXCEPTとUNION ALLを組み合わせると、2つのテーブルが同じデータを持っているか検証できる
    • 返される行がなければ、2つのテーブルは同一である
    • 返される行があれば、それらの行が差分の原因である

パフォーマンスと正確性を損なうパターン

  • NULLを取りうるカラムではNOT EXISTSがNOT INよりよい

    • 比較対象カラムがNULLを許容する場合、NOT INは通常NOT EXISTSより遅くなることがある
    • Snowflakeでこの現象を経験しており、PostgreSQL WikiのDon’t Do Thisには、NOT IN (SELECT ...)は最適化がうまく行われないと書かれている
    • NOT INは比較対象値にNULLがあると意図どおりに動作しない
    • カラムがNULLを許容するといっても実際にNULL値があるとは限らないが、修正できないテーブルを扱う場合には、NOT EXISTSが速度改善に役立つことがある

  • 暗黙的型変換は遅くなったり失敗したりする

    • カラムと異なるデータ型の値を条件に入れると、データベースが暗黙的型変換を試みることがある
    • 例では、文字列型のvideo_idカラムに整数200050を比較するケースを扱っている
    • 暗黙的型変換に依存すると問題が起きることがある
    • 変換できない値があるとエラーが発生する可能性がある
    • 各値を指定型に変換する追加処理のため、クエリが遅くなることがある
    • カラムと同じデータ型を使うか、エラー回避のためにSnowflakeのTRY_TO_NUMBERのような関数を使える
    • 速度への影響は、処理するデータセットのサイズによって異なる

よくあるミス

  • NOT INとNULL

    • NOT INは比較対象値にNULLがあると動作しない
    • NULLはUnknownを表すため、SQLエンジンは検査値がその一覧に存在しないと検証できない
    • この場合はNOT EXISTSを使う方法が代替になる

  • 計算フィールドのalias衝突

    • 計算フィールドの名前を既存カラムと同じにすると、予期しない動作が起こることがある
    • SnowflakeのGROUP BYドキュメントには、GROUP BY句の名前がカラム名とaliasの両方に一致する場合はカラム名を使うと書かれている
    • 例でLEFT(product, 1) AS productというaliasを作ってGROUP BY productを書くと、先頭文字ではなく元のproductカラムでグループ化され、3行が返る
    • 解決策は2つある
    • product_letterのような一意なaliasを使う
    • GROUP BY LEFT(product, 1)のように式を明示する
    • ウィンドウ関数でもaliasの問題が起こることがある
    • 例では、CASEでRobotのrevenueを0に変えるが、ウィンドウ関数の実行後に適用されるため、順位が期待どおりにならない
    • 可能なら一意なaliasを使うか、ウィンドウ関数のORDER BY内に計算式を直接入れる必要がある

  • カラムがどのテーブルに属するかを明示する

    • 複数の結合がある複雑なクエリでは、値の問題を元テーブルまで追跡できる必要がある
    • 2つのテーブルが同じカラム名を共有しているとき、カラムの所属を明示しないとRDBMSがエラーを出すことがある
    • 例では、vc.video_id、metadata.seasonのようにテーブルaliasを付けてカラムの出所を明確にしている

実行順序、ドキュメント、保存名

  • SQLの実行順序を理解する

    • SQLを学ぶ人への最も重要な助言の1つとして、句の実行順序の理解が挙げられている
    • 実行順序を知ると、クエリの書き方が大きく変わることがある
    • 参考資料としてA beginner’s guide to the true order of SQL operationsが示されている

  • ドキュメントは最後まで読む

    • Snowflakeで複数の日付カラムのうち最新日付を返すためにGREATEST()を使った事例がある
    • GREATEST()は、引数のいずれか1つがNULLだとNULLを返す
    • ドキュメントをもう少し読んでいれば、COALESCE(GREATEST(...), ...)の代わりにGREATEST_IGNORE_NULLS()を使えた
    • 多くの場合、ドキュメントにざっと目を通すのに1分もかからず、予想外の動作原因を探す手間を減らせる

  • 保存クエリには説明的な名前を使う

    • 後で再実行したり参照したりすべきクエリを見つけられない事態を避けるには、説明的な名前で保存するのがよい
    • 保存名には通常、クエリのテーマ、実行月、依頼者名が入る
    • 例はLapsed users analysis - 2023-09-01 - Olivia Roberts形式である

関連記事

2件のコメント

 
hiyama 2024-09-26

この投稿では、先頭カンマがすべて末尾カンマとして書かれていますね。原文では先頭カンマで入力されています。

-- Good:  
SELECT   
timeslot_date  
, timeslot_channel   
, overnight_fta_share  
, IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) > 7, -- First argument of IFF.  
	LAG(overnight_fta_share, 1) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity), -- Second argument of IFF.  
		NULL) AS C7_fta_share -- Third argument of IFF.  
, IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) >= 29,   
		LAG(overnight_fta_share, 2) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity),   
			NULL) AS C28_fta_share  
FROM timeslot_data  
;
 
GN⁺ 2024-09-26
Hacker Newsのコメント

  • 私が付け加えるコツはこうだ。DBサーバーをきちんと理解し、実行計画を頻繁に確認すること。意外な結果になることがあるので、調整して再確認するのがよい
    通常は EXISTS が IN より速く、NOT EXISTS は NULL の扱いで EXCEPT とは異なる挙動をする。テーブルを結合してから DISTINCT のようなもので行を絞り込むより、SELECT リストのサブクエリ列を使うほうがはるかに速い場合がある。同じテーブルから10個を超える値を取ってくる場合でもそうで、DBサーバーが lateral join をサポートしていても同じことがあり得る。ただしサブクエリは最大1行だけを返す必要がある
    一回限りではないクエリでは、テーブル全体のスキャンをしないようにすべき。今日のテーブルスキャンが明日の障害になり得るので、インデックスを追加すべきだ。GROUP BY 句が通常、インデックス利用を左右することも覚えておくこと
    式でフィルタリングする必要があるなら、たとえば部分文字列が特定の値と等しいかを確認する必要があるなら、計算列を追加してそこにインデックスを張れる。一部のDBは式インデックスを直接サポートしている。OR の代わりに UNION ALL を使うと、複雑なクエリや複数の OR 条件でも大幅に速くなることが多い
    DBがフィルタリング順序を賢く決められない場合は、サブクエリを JOIN して順序を強制するのも有用だ

    • 最も有用なのはDBMSそのものを学ぶことだ。DBごとに性能や分離レベルの癖があり、おまけ機能も違うので避けて通れない
      Postgresで興味深かったのは、ほかのDBでもそうかもしれないが、INSERT (SELECT ...) 処理をCPUコア数に合わせて手動でシャーディングすると、ほぼ線形に速くなり得ることだ。結合が10個くらいあっても可能だった。まず EXPLAIN を見て最も内側または外側の結合を見つけ、各行範囲ごとに別々の並列クエリ（id >= start AND id < end）を実行すればよい。6年前のある仕事で、変な理由からこの方法をよく使っていた。Postgres 10+ には並列性が追加されたが、私の知る限りではまだこの程度まで先進的ではない
    • SELECT リストでサブクエリ「列」を使う、というのが正確に何を意味するのか分からない
      たとえば SELECT column1, (SELECT column2, column3, ... FROM table_b WHERE table_a.id = table_b.a_id) FROM table_a を実行すると、予想どおり “subquery must return only one column” が出る。複数の列をレコード/複合型として返せという意味なのか？
      GROUP BY 句が通常インデックス利用を左右する理由がすぐには腑に落ちなかったが、気になる人にはこの記事が段階的によく説明している: https://www.brentozar.com/archive/2015/06/indexing-for-group...
    • 同意する。EXPLAIN を使い、好みのツールでそれを解釈する方法を学ぶべきだ。クエリも監視する必要がある
      以前のスタートアップで PgHero を導入したが、性能最適化と優先順位付けに本当に大きく役立った
    • よく設計されたクエリでも、予想と違う動きをすることは多い。列統計が更新されていない場合や、大きなテーブルでデータが断片化している場合が典型だ。たとえばランダムな主キー挿入のようなケースだ
    • 「一回限りではないクエリはテーブル全体をスキャンしてはいけない。今日のテーブルスキャンが明日の障害になり得る」には同意しない
      テーブル全体スキャンが最も効率的なアクセス戦略であるクエリもある。通常、テーブル全体を読む分析/集計クエリがそうで、場合によっては全行の50%だけを取得する場合でもテーブルスキャンのほうがよいことがある。
      読み取り専用のテーブルスキャンがどう障害につながるのかもよく分からない。同時アクセスを妨げないからだ。欠点はI/O負荷が大きくなることだけだが、サーバーがその程度をさばけないなら、そもそも深刻なほどスペック不足だと思う

  • 「可読性」セクションの3つの例は変だ。最初の2つは書きやすくするために文字どおり可読性を犠牲にしており、最後のものはインデントしてもほとんど救いようがない、読みにくい怪物のような形だ

    • 先頭カンマ形式には、可読性以外にも利点がある。たとえばバージョン管理システムでは、引数1つにつき1行 + 先頭カンマ形式だと、引数変更が1行だけの差分として現れる
      開発者は実際のソースコードと同じくらいコミット履歴もよく見ると思う
    • 最初の2つの慣習の見た目がとても好きというわけではないが、実際にSQLを書く人たちが実際に使っている慣習だ。なぜ存在するのかも理解できる
      十分頻繁に見てきたので、今ではあまり気にならない
    • 代案としては、3歳児が初めてMSPaintを見つけたかのようにSQLをめちゃくちゃに書いてから「beautifier」ボタンを押し、早めの昼食に出かければよい
    • なぜより悪いと思うのか分からない
      問題も見えないし
      間違っているようにも見えない
    • SELECT ブロックで列を1行ずつ分けておきながら、150文字の行をそのままにする人が一体どこにいるのか？ これは壊れた可読性の定義だ。カンマの話は始めることすらできない
      コードレビューでは長い行を誰もまともに見ない。それがAngularJSの最大の問題だった。マージが誤って処理され、すべてが壊れたのに、90列目あたりまで来ると目がかすむからだ。コードレビューのあるチームを半ダース以上経験したが、いつも同じだった。この問題をかなり意識して避けようとしていても、私自身も他人の半分くらいの頻度では今なおミスをする
      もう少し分けて書こう。特に他人に例を見せるなら、なおさらそうすべきだ

  • 複雑なストアドプロシージャを扱うときのコツはこうだ

    1. プロシージャの冒頭で永続テーブルをすぐに一時テーブルへコピーし、必要な行だけを指定／制限／フィルタリングする
    2. 途中では必要に応じて一時テーブルを操作する
    3. 終盤でトランザクション内で永続テーブルを更新する。エラーが検出されたらすぐにトランザクションをロールバックしてプロシージャを終了する。この3ステップに従うと同時実行性が向上し、データの残骸を手作業で片付けなくてもプロシージャを再実行できる
    4. リモートテーブルを扱うときは極めて慎重になるべき。リモートテーブルは現在のRDBMS内にないため、そのRDBMSの統計情報やインデックスをほとんど活用できない可能性が高い。多くの場合、リモートテーブル全体を一時テーブルにダンプ／コピーしてから作業する方が速い。リモートテーブルに期待できる最大限のことは、WHERE句の実行程度だ。JOINや複雑な処理を試みるとタイムアウトする可能性が高い
    5. 実行計画は簡単に誤解を招く。場合によっては、実行計画が行単位の処理に落ちて性能が止まってしまうことがある。複雑なストアドプロシージャは、一時テーブルを使う小さなステップに分けた方がよい場合が多い
    6. RDBMSが実際に何をしているのかを見るには、常に実行計画を確認する必要がある
    • 5番を特に必要のない状況で適用していたコードを戻して、クエリ性能を大きく改善したことがある。ときにはクエリを複数の小さなクエリに分割することが、クエリオプティマイザにクエリ全体を渡して最適な経路を見つけさせるよりも、はるかに非効率になる
      6番なしで5番をやっていたなら、最適でないことをしていると気づけない可能性が高い。早すぎる最適化を避け、まず最も直感的な方法で書き、必要なときだけ最適化する、というのが私の助言だ。最も重要なのは、SQLを手続き的に書かないこと。欲しいデータを記述するのであって、エンジンに取得方法を命令するのではない
    • 一時テーブルを大量に使わなければならないのは嫌だが、クエリプランナーに任せると決して終わらないクエリによく出会う。コンパイラと同じように、クエリプランナーの能力も大きく過大評価されている
      一方でMicrosoftは、クエリプランナーが一番よく分かっていると言わんばかりに、それをチューニングしようとするなという警告を付け続けている
    • これらのルールは、あるベンダーのDBでは完全に正しいかもしれないが、別のDBでは優先順位や特性、トレードオフが大きく異なることがある
      DBのバージョンも影響しうる
    • 1〜3番は、データサイズが妥当だと保証できるなら問題ない。しかしデータがハードウェアで扱うには大きくなりすぎると、大きなデータセットをコピーしてから再び大きなデータセットを更新する作業が、かなりのオーバーヘッドを追加しうる

  • 「念のため」の開発は好きではない。インターフェースもそうだし、where 1=1のようなプレースホルダーも同じだ
    必要になったときにやればいい。いつか将来必要になるかもしれないからといって、やるべきではない。プロダクションコードは開発用の補助を残しておく場所ではない。開発中は好きにしてもよいが、プロダクションコードでは可読性と明確な意図の方がはるかに重要だ

    • テーブル名とカラム名の参照をすべて完全修飾して書く方だろうか？ そうすると可読性が一桁以上よくなることが多かったが、すぐに非常に冗長になり、書くのが信じられないほど苦痛なほど退屈になる

  • 「アンチジョイン」についてもう1つ。別の大きなテーブルやサブクエリに条件に合う行が存在するかどうかだけを確認したいなら、INやLEFT JOINの代わりにEXISTSを使うのがよい
    EXISTSは一致する項目を見つけた瞬間に真を返す。LEFT JOINとINの場合、エンジンは評価前にすべての結果を集める

    • その部分は少し混乱した。私がテストしたすべてのケースで、(NOT) EXISTSは(LEFT) JOINや(NOT) INより良い実行計画を作るか、同じ計画を作った
      しかも意図もより明確だ

  • 「コードにコメントを書け」に関連して、少なくともMSSQLでは、コメントに--ではなく/**/を使うようよく勧められる。Query Storeのような機能は改行なしでクエリを保存することが多く、そこからクエリを取り出すと、IDEのフォーマッタをすぐ使う代わりに手作業で全部直さなければならないからだ

    • それはQuery Storeのバグのように聞こえる
    • XMLにキャストできる？ 私はOBJECT_DEFINITIONにそれを使っている
      select name,cast((select OBJECT_DEFINITION(object_id) for xml path('')) as xml) from sys.procedures
      改行が保持されるので、整えるのが楽になるかもしれない。ただし、>が>に変わるように、他のXML文字は壊れる。もう1つの選択肢はVARBINARYと、それを再び展開する何かを使うことだ

  • みんなカンマの提案には大騒ぎしているのに、WHERE句の1=1は良い考えだと見ているのだろうか？ コードレビューでそれを見たら、書いた人をどう考えればいいのか分からない

    • 後置カンマと同じ理由で正当化することはできる。WHERE文の変更が他の行に影響しないので、コードレビューが楽になるという理由だ
      ただし、この場合のように動的条件を追加するための理由なら、私の職場では確実に解雇されるだろう

  • DB設定で速度を上げる、ほとんど「購入」に近い方法と、実質的に手作業で実装する「構築」との境界をどこに引くべきか、一般的な指針を共有できる人はいるだろうか。自分の限られた経験では、有能なDBAはずっと高い報酬で別の場所で働いているため、この仕事はアプリ開発者に回ってくることが多い。上で述べたように、DBを知っていることは重要だ
    典型的な例は、時間とともに大量に蓄積され、最新データが最も頻繁にアクセスされるデータだ。DBAならパーティショニングや部分インデックスでアクセスを高速に保てるが、アプリ開発者はレコードを別のアーカイブテーブルへ裏側で移しつつ、データセット全体の最終的な検索のような機能を引き続きサポートすることもある。クラウドDBの機能不足に制約される場合のように、適切なタイミングで1つのテーブルを複数に分割する初期作業は、ツールでかなり自動化できそうだという気もする
    もう1つの管理上の選択肢は、大きなBlob/ファイルをすべて別のデータベース、あるいはファイルシステムに保存して、別のストレージ設定を使うことだ。これもDB側で対応する場合もあれば、手作業で処理する場合もある
    極端には、インデックスを自分で実装するところまで行けそうだ。自動増分の主キー1つと多数のカラムを持つ巨大なテーブルを用意し、そのIDと検索可能なカラムをいくつか持つテーブルを別に作る方式だ。全文検索やベクトルまで行くこともあり得る
    MSSQL 2016+でマテリアライズドビュー・パターンを手動実装する際に役立つコツは、パーティション切り替えを併用することだ。https://github.com/cajuncoding/SqlBulkHelpers?tab=readme-ov-...でうまく説明・実装されている。偶然見つけた、商業的には最も役に立ったものの検索順位は低くスターも少ない小さなライブラリで、.NETからMSSQLへ大量挿入することに重点を置いている。パーティション切り替えの自動化によって、購入/構築の境界を適切に引いた好例だと思う

  • 抜けている点: SELECT * の使用はやめるべきだ。ほぼ間違いなくテーブルの全幅は必要ないし、そうするとフィルタリングして転送するデータが増え、優れた機能であるセミジョインも妨げてしまう

    • SQLを使う人は大きく2種類に分かれる。アナリストと開発者だ
      開発者ならその通り。SELECT * には落とし穴があり、ほぼ常にカラムを明示するか、代わりにそうしてくれるクエリビルダーを使うべきだ
      だがアナリストなら人生は短く、ときにはすべてのカラムを打ち込みたくないこともある。SELECT * でも構わない

  • 少し本題から外れるかもしれないが、管理者がプルリクエストをコメントも議論もなくただ閉じるのは、許容できるやり方なのだろうか。
    そのリポジトリにたまに貢献した、あるいは貢献しようとした立場から尋ねている。
    例: https://github.com/ben-n93/SQL-tips-and-tricks/pulls?q=is%3A...