気ままに気ままのエンジニアブログ

定期的に得た知見を気ままに発信中

【SQL向上ポイント】SQL力を上げるためには、構文理解じゃなくテーブル構成の理解を深めるべきだ!

こんにちは。

GWがあけました。悲しい...

どうもハチマキです。

はじめに

SQLを学び始めた当初、手も足も出ないSQLに課題を感じ、SQLチャレンジという名の毎月数個のクエリ作成を自分に課せ、組んだSQLにレビューして頂くという、先輩巻き込み企画を数ヶ月行なっておりました。

そのおかげもあり、今ではjoin, union allはもちろん、状況に応じてサブクエリを4つ組み合わせたSQLなども組めるようになり、数ヶ月前とは桁違いにSQL力が向上しました。

そこで感じたことは、SQLは構文(select, fromなど)を覚えれば組めると思っていたが、実はそうではないということ。
タイトルの通り、SQL力を上げるためには、むしろテーブル構成(どんなテーブルで、どのようなデータが管理されているのか)の理解が重要だということに気付きました。

今回の記事では、0からSQLを学習した私が、学びながら感じたSQL向上のポイントについて書いていきたいと思います。

*補足:既存アプリケーションでのデータ抽出を対象にしております

対象
  • データ抽出のやり方がいまいち分からない方
  • クエリの組み方のコツを知りたい方
  • SQLの学習を始めようとしている方
  • SQL構文の理解は進んできたけど、いまいち理解がまだふわっとしている方
環境
  • DB:MySQL
  • ツール:Sequel Pro(Mac用のMySQLのデータをGUI操作で管理するためのアプリケーション)
  • マシン:Mac
参考テーブル例
  • usersテーブル
    • id
    • email
    • name
    • created_at
  • postsテーブル
    • id
    • text
    • created_at
    • user_id

初級編:理解すべきSQL構文はこれで良い!

まず初期に理解すべきSQL構文はこれだけで良いという事です。以下を理解できれば、シンプルなデータ抽出のクエリは、問題なく組めるようになると思います。

  • SELECT:データ取得
    • COUNT関数:数を数える関数
    • SUM関数:合計値を求める関数
  • FROM:テーブル指定
  • INNER JOIN(内部結合):テーブル結合*外部結合との違いを理解しておく必要あり
  • OUTER JOIN(外部結合):テーブル結合*内部結合との違いを理解しておく必要あり
  • WHERE:条件指定
    • BETWEEN:範囲指定
    • AND:複数の条件指定
    • IN:複数要素の判定
    • =:単数要素の判定
    • IS NULL:NULLを判定
    • IS NOT NULL:NULL以外を判定
  • GROUP BY:データのグループ化
  • ORDER BY:データ並び替え
    • DESC・ASC:昇順・降順ソート

**重要ポイント
SQL構文のインプット学習だけは、ぜひやめて頂きたい!!
良くありがちなのは、SQLの書籍を購読するインプット学習です。しかし残念ながら、いざ手を動かすと何も身に付いていないことに驚愕する羽目になります(体験談です..)

0からSQLを学ぶ時こそ、Sequel Proなどのツールを活用して体感で学ぶ学習方法の選択をおすすめします。

中級編:初級編で実現出来ない場合に学ぶべきSQL構文

中級編では、少々複雑なデータ抽出であっても、以下を使いこなせれば、問題なくSQLを組めるようになることでしょう。

  • サブクエリ:クエリに含まれるクエリ
  • UNION / UNION ALL:結果の統合
  • CASE:条件分岐(データ分別)
  • その他
    • 関数/日付関数[比較, 加算, 差分, 抽出](time, TIMESTAMPDIFF, truncate, concat, group_concat , date_format, convert_tz, etc...)
    • 拡張機能(with rollup, etc...)

**重要ポイント
中級編を学び始めたということは、初級編の構文だけでは実現出来ないデータ抽出に出会したということです。

おそらくすでに思考は、初級編の構文だけでは実現出来なくないか?このクエリとこのクエリを組み合わせればデータ抽出できるかも?クエリとクエリの統合はどうやったら実現出来るんだ?などなど、様々な良い疑問を持つ思考になっていることでしょう。この疑問を持つことが初級から中級にステップアップする上で、非常に重要なポイントだと私は感じました。

SQL向上のポイント

今回の本題は、ここからです。
上記、初級編/中級編の構文が理解出来たからといって、SQLが組めるようになるかと言うと、実はそうでもありません。
あくまでこれまでは、構文を理解しただけだからです。SQLを組めるようになるためには、構文理解以外に構文の使い方をマスターする必要があります。(なので、体感で学習する必要があるのです)

先に述べますが、重要なことは、要件のキーワードからクエリを組み立てられる力を身につけることです。

そのためには、要件(抽出したいデータ)を自然言語からSQL構文への変換作業を身につける必要があります。

以下ステップでは、SQLを組む上での手順について書いていきます。
私の場合、SQLを組む際は、一度頭の中で構成を考え、クエリを組むようにしています。頭の中での組み立て、そこからどのようにしてアウトプットを出しているのかを言語化していきたいと思います。

▼ステップ1:アプリケーションのDB構造を理解する

アプリケーションのDBファイルを確認し、ざっくりアプリケーション全体のDB構造の理解を深めましょう。
ポイントは、どんなテーブル構成で、カラムにはどのようなデータが管理されているのかを全体的に掴むことです。

*ここまで出来るとベスト!
抽出したいデータを構成しているテーブル構成・カラムを特定できると、このあとの手順をスムーズに進めることが出来るかと思います。

▼ステップ2:要件を整理し、明確にする

要件あるあるは、様々な要素が混在していて、結局どのようなデータを抽出すべきかがわからないことが、多々あります。
SQLを組む際は、SQL構文を考える前に、まず抽出要件を整理し、明確にしましょう。

例)以下、明確にした要件
・要件:直近1年間で、TODOリストを投稿したユーザデータ一覧

▼ステップ3:要件をキーワードごとに分解する

明確にした要件をキーワードごとに分解する作業です。ここで重要なことは、分解したキーワードのテーブル構成を把握することです。

上記例を参考にすると、「直近1年間」「TODOリストを投稿した」「ユーザ」のように分解できます。

直近1年間で、TODOリストを投稿したユーザデータ一覧」

*キーワード分解は、アプリケーションのテーブル構成に左右されますが、あくまでここでは考え方が重要です。

▼ステップ4:自然言語でアウトプットを明確にする

次にやるべきことは、自然言語でアウトプットを明確にすることです。もう少し具体的に言うと、ステップ1で得たDB構成、テーブル構成の理解をベースに、抽出したいカラム(SELECT文)のあらいだしをここで行っていきます。

例)以下、自然言語で明確にしたアウトプット
・SELECT
 ・id
 ・氏名
 ・メールアドレス
 ・投稿日
 ・etc..

▼ステップ5:from句に定義するテーブルを決める

ここでのポイントは、ステップ4であらいだした最終アウトプットのデータが何かを考え、どのテーブルをfrom句に定義すべきかを決めることです。

例を参考にすると、

直近1年間で、TODOリストを投稿したユーザデータ一覧

最終的なアウトプットは、ユーザデータ一覧です。そのため、from句にはusersテーブルを定義します。

・SELECT
 ・id
 ・氏名
 ・メールアドレス
 ・投稿日
 ・etc..
・FROM users

*複数クエリを結合する(サブクエリ)際も考え方は同様

▼ステップ6:要件を自然言語からSQL構文へ変換する

ここから実際にクエリを作成する作業に入ります。

**重要ポイント
ステップ1で理解を深めたDB構造(テーブル構成やカラム)が非常に役立ちます。
基本的にデータ抽出する際は、1テーブルだけで済むことはほとんどありません。いくつかのテーブルを結合し、要件に合わせたテーブルを作っていく必要があります。
ステップ1でDB構成を掴めていないと、要件に合わせたテーブルを作ることは不可能に近いと思います。

例を参考にSQL構文への変換作業です。

直近1年間で、TODOリストを投稿したユーザデータ一覧

  • 直近1年間
    • WHERE、BETWEENを用いて、created_atカラムの範囲を指定する
  • TODOリストを投稿したユーザ
    • from句に定義したusersテーブルに、外部キーを用いてpostsテーブルをjoinする(*要件では、未投稿ユーザは対象外にする必要があるため、join(内部結合)を使用します)
  • ユーザデータ一覧
    • GROUP BYを用いて、user.idでグループ化する
    • ORDER BYを用いて、post.created_atに並び替える

上記例を参考に組んだSQLです。

SELECT
 users.id
 users.name
 users.email
 posts. created_at
FROM users
JOIN posts on users.id = posts.user_id
WHERE posts.created_at BETWEEN '2020-01-01 00:00:00' and '2021-01-01 00:00:00'
GROUP BY user.id
ORDER BY post.created_at;

*今回の例は、非常にシンプルなデータ構造のため簡単ですが、現場では要件で分解したキーワードが複数テーブルから成り立っていることが多々あるため、fromで定義したテーブルに複数テーブルを結合する必要が出てきます。

*複雑なSQLの場合、クエリごとに抽出したいデータが取得出来ているかを確認しながら進めていくと良いかと思います。

最後に

これでSQL向上ポンイトは以上になります。

この思考が正しいのかは、正直わかりません。あくまでこの思考になったことで、個人的にはクエリが整理され、理解が進んだことでSQLを組みやすくなったということです。

改めて重要だと感じたことは、やはりテーブル構成の理解を深めることかと思います。この理解が進まないと、構文を理解出来たからといってSQLを組めるようにはならないからです。

ただ、構文を覚えないとSQLは組めないので、結局はどっちも重要なのかもしれません。

今回はSQL力を向上させるための一つの手段として得た学びを記事にしてみました。
ぜひご参考になれば幸いです。

ちなみにSQLは非常におもろいです。

                                            • -

日々勉強です。
以上、ハチマキでした。