More than 1 year has passed since last update.

SQLを書くときの考え方とその手順

Posted at 2023-05-06

はじめに

この記事では、SQL文を書く際の考え方と手順を紹介します。また、インターネットTVのデータベースを題材に、具体的に考え方と手順を確認していきます。

SQLを書くときの考え方

SQLを書く際、まず問題を理解し、必要なデータを特定し、クエリの構造を考えるという順番で進めます。

問題を理解する
問題を理解することで、クエリの目的を明確にし、その結果として必要なデータを抽出することができます。
必要なデータを特定する
問題を理解した上で、必要なデータがどのテーブルやカラムにあるのかを特定し、リストアップします。
クエリの構造を考える
クエリの構造を考える際には、JOINの種類、サブクエリやCTEの使用、ウィンドウ関数などを検討します。これにより、効率的で正確なクエリを作成することができます。
必要なデータがどのテーブルやカラムにあるのかを特定する方法💡

MySQLでは、データベース内のテーブルやカラムに関する情報を調べるために、SHOW TABLESやDESCRIBEコマンドを使用します。これらのコマンドを使用して、必要なデータがどのテーブルやカラムにあるのかを特定し、リストアップします。
1. データベース内のテーブルを一覧表示するには、以下のコマンドを実行します。
  SHOW TABLES;
2. 各テーブルのカラム情報を調べるには、以下のコマンドを実行します。
  DESCRIBE table_name;
  ここで、table_name は調べたいテーブル名に置き換えてください。
基本的な手順です。
1. SHOW TABLES コマンドでデータベース内のテーブルを確認し、関連するテーブルを特定する。
2. DESCRIBE コマンドを使用して、各テーブルのカラム情報を調べ、必要なカラムを特定する。
3. 特定したテーブルとカラムをリストアップし、これを元にクエリの構造を考える。
このように、SHOW TABLES と DESCRIBE コマンドを使ってテーブルとカラムの情報を取得し、問題の要件に応じてリストアップすることで、効率的なクエリの構造を考えることができます。

SQLを書く手順

SELECT句で必要なカラムを指定し、データを抽出します。
FROM句でテーブルを指定し、必要に応じてJOINを行うことで関連するデータを結合します。
WHERE句で条件を指定し、特定のデータだけを抽出します。
必要に応じてGROUP BY句でグループ化し、HAVING句で条件を指定ことで、特定のグループだけを抽出します。
ORDER BY句を使用して、結果を昇順または降順にソートします。

実践編

実践編で取り扱うインターネットTVのテーブル設計です。

インターネットTVのテーブル設計

テーブル名: channel_table

カラム名	データ型	NULL	キー	初期値	AUTO INCREMENT
channel_id	BIGINT(20)		PRIMARY		YES
channel_name	VARCHAR

外部キー制約: なし
ユニークキー制約: channel_name

テーブル名: time_slot_table

カラム名	データ型	キー	AUTO INCREMENT
time_slot_id	BIGINT(20)	PRIMARY	YES
start_time	TIME
end_time	TIME
channel_id	BIGINT(20)	FOREIGN

外部キー制約: channel_id -> channel_table.channel_id
ユニークキー制約: channel_id, start_time

テーブル名: program_table

カラム名	データ型	NULL	キー	AUTO INCREMENT
program_id	BIGINT(20)		PRIMARY	YES
program_title	VARCHAR
program_description	TEXT	YES

外部キー制約: なし
ユニークキー制約: program_title

テーブル名: season_table

カラム名	データ型	キー	AUTO INCREMENT
season_id	BIGINT(20)	PRIMARY	YES
season_number	BIGINT(20)
program_id	BIGINT(20)	FOREIGN

外部キー制約: program_id -> program_table.program_id
ユニークキー制約: season_number, program_id

テーブル名: episode_table

カラム名	データ型	NULL	キー	初期値	AUTO INCREMENT
episode_id	BIGINT(20)		PRIMARY		YES
episode_number	BIGINT(20)	YES
episode_title	VARCHAR
episode_description	TEXT	YES
video_length	TIME
release_date	DATE
views	BIGINT(20)			0
season_id	BIGINT(20)	YES	FOREIGN
program_id	BIGINT(20)		FOREIGN

外部キー制約: season_id -> season_table.season_id, progrram_id -> program_table.program_id
ユニークキー制約: episode_number, season_id(season_idがNULLでない場合）

テーブル名: genre_table

カラム名	データ型	NULL	キー	初期値	AUTO INCREMENT
genre_id	BIGINT(20)		PRIMARY		YES
genre_name	VARCHAR

外部キー制約: なし
ユニークキー制約: genre_name

テーブル名: program_genre_table

カラム名	データ型	NULL	キー	初期値	AUTO INCREMENT
program_id	BIGINT(20)		PRIMARY
genre_id	BIGINT(20)		PRIMARY

外部キー制約: program_id -> program_table.program_id, genre_id -> genre_table.genre_id
ユニークキー制約: program_id, genre_id

ER図

実践編：初級

初級：ある番組名のすべてのエピソードを取得する方法

考え方と手順に従って、インターネットTVのデータベースから、指定した番組名 (例: 'News Hour') のすべてのエピソードタイトルを取得するSQLクエリを作成します。

手順1: 問題を理解する

今回の目的は、指定した番組名のすべてのエピソードタイトルを取得することです。これにより、ユーザーはその番組のエピソード一覧を確認できます。

手順2: 必要なデータを特定する

次に、どのテーブルからデータを取得する必要があるかを特定します。今回は、episode_tableからエピソードタイトルを取得し、program_tableから番組名を指定する必要があります。

手順3: クエリの構造を考える

必要なデータを特定したら、どのようにクエリを構成するか考えます。今回は、episode_tableとprogram_tableを結合し、指定した番組名に一致するレコードをフィルタリングする必要があります。

手順4: SQLクエリを作成する

SELECT句で必要なカラムを指定する
- エピソードタイトル（episode_title）を取得するため、SELECT episode_titleと記述します。
FROM句でテーブルを指定し、必要に応じてJOINを行う
- episode_tableとprogram_tableをJOINします。FROM episode_table JOIN program_table ON episode_table.program_id = program_table.program_idと記述します。
WHERE句で条件を指定する
- 指定した番組名（ここでは'Example Program'）に一致するレコードをフィルタリングするため、WHERE program_table.program_title = 'News Hour'と記述します。
今回はGROUP BY句は不要です。
クエリを完成させます。

SELECT episode_title
FROM episode_table
JOIN program_table ON episode_table.program_id = program_table.program_id
WHERE program_table.program_title = 'News Hour';

-- 実行結果
+----------------------------+
| episode_title              |
+----------------------------+
| The Beginning              |
| The Adventure Continues    |
| The Mystery Unfolds        |
| The Plot Thickens          |
| The Twist                  |
| The Final Battle           |
| A New Beginning            |
| The Return of the Villain  |
| The Secret of the Artefact |
| The Hunt Begins            |
| The Trap                   |
| The Final Showdown         |
+----------------------------+
12 rows in set (0.01 sec)

実践編：中級

中級：ジャンル別の平均視聴数を取得する方法

考え方と手順に従って、インターネットTVのデータベースから、各ジャンルごとにエピソードの平均視聴数を計算するSQLクエリを作成します。

手順1: 問題を理解する

今回の目的は、各ジャンルごとにエピソードの平均視聴数を計算することです。これにより、人気ジャンルやトレンドを把握できます。

手順2: 必要なデータを特定する

次に、どのテーブルからデータを取得する必要があるかを特定します。今回は、**genre_tableからジャンル名を取得し、episode_tableから視聴数を取得する必要があります。また、program_genre_tableを使ってジャンルとエピソードを関連付けます。

手順3: クエリの構造を考える

必要なデータを特定したら、どのようにクエリを構成するか考えます。今回は、genre_table、program_genre_table**、およびepisode_tableを結合し、ジャンルごとに平均視聴数を計算する必要があります。

手順4: SQLクエリを作成する

SELECT句で必要なカラムを指定する
- ジャンル名（genre_name）と平均視聴数（AVG(views)）を取得するため、SELECT genre_name, AVG(views) AS average_viewsと記述します。
FROM句でテーブルを指定し、必要に応じてJOINを行う
- genre_tableとprogram_genre_tableをJOINし、次にepisode_tableをJOINします。FROM genre_table JOIN program_genre_table ON genre_table.genre_id = program_genre_table.genre_id JOIN episode_table ON program_genre_table.program_id = episode_table.program_idと記述します。
今回はWHERE句が不要です。
必要に応じてGROUP BY句でグループ化し、HAVING句で条件を指定する
- ジャンルごとにグループ化するため、GROUP BY genre_table.genre_id, genre_nameと記述します。
今回はORDER BY句が不要です。
クエリを完成させます。

SELECT genre_name, AVG(views) AS average_views
FROM genre_table
JOIN program_genre_table ON genre_table.genre_id = program_genre_table.genre_id
JOIN episode_table ON program_genre_table.program_id = episode_table.program_id
GROUP BY genre_table.genre_id, genre_name;

-- 実行結果
+------------+---------------+
| genre_name | average_views |
+------------+---------------+
| Action     |     3750.0000 |
| Adventure  |     7133.3333 |
+------------+---------------+
2 rows in set (0.00 sec)

実践編：上級

上級：シーズンごとのエピソード数を取得する方法

考え方と手順に従って、インターネットTVのデータベースから、シーズンごとのエピソード数を計算するSQLクエリを作成します。

手順1: 問題を理解する

今回の目的は、シーズンごとのエピソード数を取得することです。これにより、各番組のシーズン構成を把握できます。

手順2: 必要なデータを特定する

次に、どのテーブルからデータを取得する必要があるかを特定します。今回は、program_tableから番組情報を取得し、season_tableからシーズン情報を取得し、episode_tableからエピソード情報を取得する必要があります。

手順3: クエリの構造を考える

必要なデータを特定したら、どのようにクエリを構成するか考えます。今回は、program_table、season_table、およびepisode_tableを結合し、シーズンごとにエピソード数を計算する必要があります。

手順4: SQLクエリを作成する

SELECT句で必要なカラムを指定する

番組名（program_title）、シーズン番号（season_number）、およびエピソード数（COUNT(episode_table.episode_id)）を取得するため、SELECT program_table.program_title, season_table.season_number, COUNT(episode_table.episode_id) AS episode_countと記述します。
FROM句でテーブルを指定し、必要に応じてJOINを行う

season_tableとprogram_tableをJOINし、次にepisode_tableをJOINします。FROM season_table JOIN program_table ON season_table.program_id = program_table.program_id JOIN episode_table ON season_table.season_id = episode_table.season_idと記述します。
今回はWHERE句が不要です。
必要に応じてGROUP BY句でグループ化し、HAVING句で条件を指定する
- シーズンごとにグループ化するため、GROUP BY program_table.program_title, season_table.season_numberと記述します。
今回はORDER BY句が不要です。
クエリを完成させます。

SELECT
  program_table.program_title,
  season_table.season_number,
  COUNT(episode_table.episode_id) AS episode_count
FROM season_table
JOIN program_table ON season_table.program_id = program_table.program_id
JOIN episode_table ON season_table.season_id = episode_table.season_id
GROUP BY program_table.program_title, season_table.season_number;

-- 実行結果
+--------------------+---------------+---------------+
| program_title      | season_number | episode_count |
+--------------------+---------------+---------------+
| Cooking with Julia |             7 |             9 |
| Cooking with Julia |             1 |             6 |
+--------------------+---------------+---------------+
2 rows in set (0.00 sec)

さいごに

最後まで読んでいただきありがとうございます。
私自身、SQLの書き方についていろいろと悩み、苦労しながら学んだ内容をこのブログ記事にまとめました。もし、この記事が現在SQLやデータベースを学習している方々の助けになるなれば嬉しいです。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up