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

## はじめに

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

## SQLを書くときの考え方

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

1. 問題を理解する
問題を理解することで、クエリの目的を明確にし、その結果として必要なデータを抽出することができます。
2. 必要なデータを特定する
問題を理解した上で、必要なデータがどのテーブルやカラムにあるのかを特定し、リストアップします。

3. クエリの構造を考える
クエリの構造を考える際には、JOINの種類、サブクエリやCTEの使用、ウィンドウ関数などを検討します。これにより、効率的で正確なクエリを作成することができます。

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

     ---

## SQLを書く手順

1. SELECT句で必要なカラムを指定し、データを抽出します。

2. FROM句でテーブルを指定し、必要に応じてJOINを行うことで関連するデータを結合します。

3. WHERE句で条件を指定し、特定のデータだけを抽出します。

4. 必要に応じてGROUP BY句でグループ化し、HAVING句で条件を指定ことで、特定のグループだけを抽出します。

5. ORDER BY句を使用して、結果を昇順または降順にソートします。

## 実践編

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

<details>
<summary>インターネットTVのテーブル設計</summary>
<p>

テーブル名: channel_table

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

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

テーブル名: time_slot_table

| カラム名 | データ型 | NULL | キー | 初期値 | 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

| カラム名 | データ型 | NULL | キー | 初期値 | 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

</p>
</details>

**ER図**
![Internet-TV.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3014485/78a6bbbf-eddb-52b6-8e97-10a82ddcc52a.png)

## 実践編：初級

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

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

- **手順1: 問題を理解する**

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

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

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

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

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

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

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

```sql
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クエリを作成する**

1. SELECT句で必要なカラムを指定する
    - ジャンル名（`genre_name`）と平均視聴数（`AVG(views)`）を取得するため、`SELECT genre_name, AVG(views) AS average_views`と記述します。
2. 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`と記述します。
3. 今回はWHERE句が不要です。
4. 必要に応じてGROUP BY句でグループ化し、HAVING句で条件を指定する
    - ジャンルごとにグループ化するため、`GROUP BY genre_table.genre_id, genre_name`と記述します。
5. 今回はORDER BY句が不要です。
6. クエリを完成させます。
```sql
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クエリを作成する**

1. 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`と記述します。
    
2. 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`と記述します。
    
3. 今回はWHERE句が不要です。
4. 必要に応じてGROUP BY句でグループ化し、HAVING句で条件を指定する
    - シーズンごとにグループ化するため、`GROUP BY program_table.program_title, season_table.season_number`と記述します。
5. 今回はORDER BY句が不要です。
6. クエリを完成させます。
```sql
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やデータベースを学習している方々の助けになるなれば嬉しいです。