SQLを使いこなすために

SQLを学んでみよう

データベースの操作や制御をするための言語であるSQLを学んでいきましょう。
Write your awesome label here.

もくじ

1. データベースの準備

SQLを学ぶ前に、まず次のリンク先を確認して、サンプルのデータベースの取得と、Dr.SumでSQLを実行するために使用するSQL Executorの使い方を学習しておきましょう。
  • まずはSQLを実行する環境を作りましょう。データベースのダウンロードと設定を参照してデータベースをダウンロードし、あなたのDr.Sumの中で扱えるように設定をしてください。
  • Dr.SumはSQL実行ツールである、SQL Executorを使用します。SQL Executorの使い方について、こちらの記事を参照してください。

SQL（Structured Query Language）は、データベースを操作や制御するための言語のことだよ！

SQLは大きく分けて３つの要素があります。下記の表から確認してみましょう。
名称
データ定義言語

データ操作言語

データ制御言語

略語
DDL

DML

DCL

略語の説明

Data Definition Language

Data Manipulation Language

Data Control Language

説明

テーブルの定義や削除を行う命令。

データ操作を行う基本命令。

データベースの権限管理に使用。

コマンド例

CREATE, ALTER, DROP, TRUNCATE

SELECT, UPDATE, INSERT, DELETE

GRANT, REVOKE

データベース関連の書籍ではこれらの説明をしっかりと行うのですが、本内容は「データ活用に使う最低限のSQL」に特化するため、BIツールでのデータ分析で使用するSQLの中でおよそ9割にあたるSELECTを中心に解説をします。

2. SQLってたとえばこんな感じ

まずはみんながよく見るSQLを解説するよ。
「あ、これは知ってる！」っていうクエリがあるか探してみてね！

ここではSELECTを含む、主に使用する４つの命令について解説します。
こんな［顧客］テーブルがありました。
名前
田中
鈴木
年齢
30
25
住所
東京
大阪

データの検索（SELECT）


SELECTはテーブルのデータを抽出します。
結果：［顧客］テーブル全体が抽出されます。
名前
田中
鈴木
年齢
30
25
住所
東京
大阪

データの挿入（INSERT）


INSERTはテーブルにデータを挿入します。
結果：「佐藤」「40」「名古屋」の値を持つデータが［顧客］テーブルに挿入されます。
名前
田中
鈴木
佐藤
年齢
30
25
40
住所
東京
大阪
名古屋

データの更新（UPDATE）


UPDATEはテーブルの中のデータを更新します。
結果：名前「田中」のデータの住所が「福岡」に更新されます。
名前
田中
鈴木
佐藤
年齢
30
25
40
住所
福岡
大阪
名古屋

データの削除（DELETE）


DELETEはテーブルの中のデータを削除します。
結果：名前「鈴木」のデータが削除されます。
名前
田中
佐藤
年齢
30
40
住所
福岡
名古屋

3. SELECT文にくわしくなろう

SELECT文ってどうやって書くの？

SELECTは特定のテーブルから該当するデータを抽出するSQLです。ここではどのような指定をしたらデータを抽出できるか確認していきましょう。
まずはSELECTの基本操作を解説します。
SQL構文
SELECT


FROM


WHERE


GROUP BY


HAVING


ORDER BY




意味

選択


どこから


条件


グループ化


グループの条件


並び替え




説明

データベースから取得したい列（カラム）を指定します。

データを取得するテーブルを指定します。

特定の条件を満たすデータだけを取得します。

特定の列に基づいてデータをグループ化し、集計します。

グループ化した後のデータに対して条件を指定します。

取得したデータを特定の列に基づいて並び替えます。



例

SELECT 名前, 年齢
この例では、「名前」と「年齢」という列を取得します。

FROM 人
この例では、「人」というテーブルからデータを取得します。

WHERE 年齢 > 20
この例では、年齢が20歳より大きい人だけを取得します。

GROUP BY 性別
この例では、性別ごとにデータをグループ化します。

HAVING COUNT(*) > 1
この例では、2人以上のデータがあるグループだけを取得します。

ORDER BY 年齢 DESC
この例では、年齢のDESC（降順：大きい順）にデータを並び替えます。何も指定しないとASC（昇順：小さい順）になります。

SELECT文は「どこから何の情報をどれだけ持ってくるか」を規則に則って命じるイメージです。その条件が細かいほど、複雑なデータの抽出をしてくれます。

このコンテンツをつくっている筆者も、SELECT文のすべての40%も知らないと思う。
だけどSQLを活かしてデータ活用をしているよ。
最初の一歩を踏み出すことが大事だね！

SELECT文の基本操作がわかりましたら、例文を見ていきましょう。SQL Executorで実際にコードを書いてみたり、サンプルからコピーをして見て、実際の環境でも確認しながら進めていきましょう。

ためしてみよう

30歳より上の従業員を年齢の大きい順に抽出
名前
加藤
一ノ瀬
渡辺
…
佐藤
年齢
60
58
55
…
37
性別
男性
女性
女性
…
男性
[従業員マスタ]テーブルには様々な年代の従業員情報が登録されています。ここではWHERE句で年齢を30より大きく指定し、さらにORDER BYで年齢の降順（大きい順）に指定をしています。

GROUP BYについてもう少し知ろう

SQLのGROUP BY句はデータ活用でよく使用するので、より分かりやすく説明します。GROUP BY句は、データをグループにまとめて、各グループごとに集計を行うために使います。以下の手順で、具体例を交えて説明します。

GROUP BYの基本的な考え方

  1. グループ化：指定した列（カラム）ごとにデータを分けて、グループ化をします。
  2. 集計：各グループごとに計算を行います（例えば人数を数える、平均を出す など）。
例 

簡単なテーブル

以下のような「部署従業員ビュー」があるとします。
名前
七草
中村
小林
…
石井
部署
営業2部
営業1部
営業2部
…
営業1部
年齢
24
20
24
…
39
このテーブルから、部署ごとの平均年齢を知りたいとします。

GROUP BYの使い方

以下のようにSQL文を書きます。

説明：

  1. SELECT 部署, AVG(年齢) AS 平均年齢：部署ごとに、年齢の平均を計算して表示します。ASはAVG(年齢)に別名を与えています。画面に表示するときに"平均年齢"と出した方がわかりやすくなるためです。
  2. FROM 従業員：データは「従業員」テーブルから取得します。
  3. GROUP BY 部署：部署ごとにデータをグループ化します。

結果：

[部署従業員ビュー]は従業員が所属する部署情報が登録されています。GROUP BY句で部署名を分けて集計し、AVG(年齢)をすることで、平均年齢を表示しています。
平均値を四捨五入したいときはROUND関数を使い ROUND(AVG(年齢),1)とすることで、任意の桁位置で四捨五入ができます。
上記のSQL文を実行すると、以下のような結果が得られます。
部署
営業2部
営業1部
営業3部
平均年齢
37.636363636363
40.2857142857142
40

他の集計関数

GROUP BY句と一緒に使える他の集計関数も見てみましょう。
1.  COUNT()：行数を数えます。
　 例：COUNT(*)は各部署の人数を数えます。
2.  SUM()：合計を計算します。
　 例：SUM(年齢)は各部署の年齢の合計を計算します。
3.  MAX()：最大値を取得します。
　 例：MAX(年齢)は各部署の最大年齢を取得します。
4.  MIN()：最小値を取得します。
　 例：MIN(年齢)は各部署の最小年齢を取得します。

HAVING句

HAVING句を使って、グループ化した後の条件を指定できます。
例えば、平均年齢が40歳以上の部署だけを表示したい場合、以下のようにSQL文を書きます。
部署
営業1部
営業3部
平均年齢
40.2857142857142
40
このように、平均年齢が37.6歳の営業２部を除いて抽出することが可能です。

まとめ

  • GROUP BY句はデータをグループ化して、各グループごとに集計を行うために使います。
  • COUNT、SUM、AVG、MAX、MINなどの集計関数と一緒に使うと便利です。
  • HAVING句を使うと、グループ化後の条件を指定できます。
SELECT文の基本的な使い方は以上です。続いては演習問題をやってみましょう。

SELECT文を書いてみよう

やってみよう

今まで学習したことを振り返りながら、演習問題を説いてみましょう。データ活用をしていて、よくある課題を用意しました。
SQL Executorを開いて、SELECT文を書いてみましょう。
あなたは従業員マスタ（ダミー）に主キーがないことに気付きました。主キーとなる従業員IDにキーをセットしてみましたが、データが重複しているようでエラーになりました。該当のデータを特定し、従業員IDを重複がないようにしなければなりません。そんなときにどのようなSQLを書いて該当レコードを抽出するべきでしょうか。

主キーって何かわかるかな？ Primary Key（PK）とも呼ばれるんだけど、そのテーブルの中で特定の一件を見つけるためにセットしておく情報だよ。従業員マスタならば同姓同名の人が存在するかも知れないけど、従業員IDを指定すれば、必ず特定の人にたどり着くよね！

従業員ID
1
件数
2
今回のSQLは少々難易度が高かったかもしれません。同じ従業員IDがテーブル内に入っているかを確認するために、GROUP BY句とHAVING句を活用しました。
従業員IDでGROUP BYをして、従業員IDのCOUNTをすれば、そのID毎の件数がわかります。さらにHAVING句でCOUNTが1件より大きいものを表示させれば、従業員IDが2件以上（重複している）のデータが表示されることになります。

WHERE句についてもう少し知ろう

SQLのWHERE句と、その中で使えるAND、OR、IN、CASEの使い方をわかりやすく説明します。まずは、WHERE句の基本的な意味と使い方から説明します。

WHERE句の基本

説明：

WHERE句は、特定の条件を満たす行だけを選択するために使います。
テーブルからデータを取得するときにフィルタをかける役割を果たします。
例 

「従業員」テーブルから、年齢が30歳以上の従業員を取得する場合：

従業員名
井上
斎藤
…
石井
年齢
54
46
…
39

ANDとOR

説明：

  1. AND：複数の条件すべてを満たす行を選択します。
  2. OR：複数の条件のいずれかを満たす行を選択します。
例 

年齢が30歳以上で、かつ部署が「営業１部」の従業員を取得する場合
（両方の条件を満たす）：

従業員名
森
吉見
…
石井
年齢
45
50
…
39
部署名
営業1部
営業1部
…
営業1部
例 

年齢が30歳以上、または部署が「営業１部」の従業員を取得する場合
（どちらかの条件を満たす）：

従業員名
中村
井上
…
石井
年齢
20
54
…
39
部署名
営業1部
営業3部
…
営業1部

IN

説明：

INは、特定の値のリストの中にあるかどうかをチェックします。複数の値を指定して、そのいずれかに一致する行を選択します。
例 

部署が「営業１部」または「営業２部」の従業員を取得する場合：

従業員名
七草
中村
…
石井
部署名
営業2部
営業1部
…
営業1部

CASE

説明：

CASE文はIF文のように条件に基づいて異なる値を返すために使います。
例 

年齢が30歳以上の従業員には「シニア」、それ以外の従業員には「ジュニア」
と表示する場合：

従業員名
七草
中村
…
石井
年齢
24
20
…
39
カテゴリー
ジュニア
ジュニア
…
シニア

まとめ

  • WHERE句：条件に基づいて行を選択する
  • AND：複数の条件すべてを満たす行を選択する。
  • OR：複数の条件のいずれかを満たす行を選択する。
  • IN：特定の値のリストの中にある行を選択する。
  • CASE：条件に基づいて異なる値を返す。
これらの基本的な構文を使うことで、データベースから必要なデータを柔軟に取得することができます。

4. 結合の種類

SQLの結合（JOIN）は、複数のテーブルからデータを取得して一つの結果セットにまとめるための方法です。
ここでは、主要な結合の種類と、特に左外部結合（LEFT JOIN）について詳しく説明します。
  1. 内部結合（INNER JOIN）
  2. 左外部結合（LEFT JOIN）
  3. 右外部結合（RIGHT JOIN）
  4. 完全外部結合（FULL OUTER JOIN）

1. 内部結合（INNER JOIN）

説明：

  • 2つのテーブルの結合条件を満たす行だけを取得します。
  • 共通するデータのみを取り出します。
例 

従業員とその部署のデータを結合する場合：

結果：

共通する部署IDを持つ従業員と部署のデータのみが取得されます。

2. 左外部結合（LEFT JOIN）

説明：

  • 左のテーブルのすべての行と、右のテーブルの結合条件を満たす行を取得します。
  • 結合条件を満たさない場合、右のテーブルの列はNULLになります。
  • 左外部結合は、分析で欠損データを確認するのに非常に重要です。
例 

すべての従業員のデータと、その従業員の部署が存在する場合は
そのデータを結合する場合：

結果：

従業員が部署に所属していない場合でも、従業員のデータはすべて取得され、部署名はNULLになります。
詳しい説明と例 

左外部結合（LEFT JOIN）

左外部結合（LEFT JOIN）は、データ分析で欠損値の扱いや、全体像を把握する際に非常に役立ちます。以下に詳細な説明と例を示します。

テーブル例：

従業員テーブル
従業員ID
1
2
3
4
名前
田中
鈴木
佐藤
山田
部署ID
1
2
NULL
1
部署テーブル
部署ID
1
2
部署名
営業
開発

左外部結合クエリ：

結果：

名前
田中
鈴木
佐藤
山田
部署名
営業
開発
NULL
営業

詳細：

  • 「田中」と「山田」は「営業」部署に所属しています。
  • 「鈴木」は「開発」部署に所属しています。
  • 「佐藤」はどの部署にも所属していませんが、左外部結合を使うことで従業員の情報をすべて取得できます（部署名はNULL）。

3. 右外部結合（RIGHT JOIN）

説明：

  • 右のテーブルのすべての行と、左のテーブルの結合条件を満たす行を取得します。
  • 結合条件を満たさない場合、左のテーブルの列はNULLになります。
例 

すべての部署と、その部署に従業員が存在する場合は
そのデータを結合する場合：

結果：

部署が存在するが従業員がいない場合でも、部署のデータはすべて取得され、従業員の名前はNULLになります。従業員が部署に所属していない場合でも、従業員のデータはすべて取得され、部署名はNULLになります。

4. 完全外部結合（FULL OUTER JOIN）

説明：

  • 左右両方のテーブルのすべての行を取得し、結合条件を満たさない場合はNULLになります。
例 

すべての従業員と部署のデータを結合する場合：

結果：

従業員も部署もすべてのデータが取得され、どちらかに結合条件がない場合はNULLになります。

まとめ

  • 内部結合（INNER JOIN）：共通するデータだけを取得。
  • 左外部結合（LEFT JOIN）：左のテーブルのすべての行と、条件に一致する右のテーブルの行を取得。
  • 右外部結合（RIGHT JOIN）：右のテーブルのすべての行と、条件に一致する左のテーブルの行を取得。
  • 完全外部結合（FULL OUTER JOIN）：両方のテーブルのすべての行を取得。
左外部結合は、特にデータ分析で、すべてのデータを保持しながら欠損値を確認する際に非常に有用です。

5. 覚えておくと便利な関数

DECODE関数

DECODE関数は、特定の条件に基づいて値を変換するために使用します。言い換えると、IF文やCASE文のような働きをします。

基本的な使い方

書式：

説明：

  • 式：評価する値。
  • 検索値1, 検索値2, ...：比較する値。
  • 変換値1, 変換値2, ...：対応する変換後の値。
  • デフォルト値：式がどの検索値にも一致しなかった場合に返される値。
具体例 

以下の売上データを例に考えてみましょう。
売上ID
1
2
3
4
5
売上金額
3732
44567
43613
46891
9804
税フラグ
1
2
1
1
0
このテーブルの税フラグ（1, 2）を、日本語の「税あり」「税なし」に変換するためにDECODE関数を使います。

クエリ：

結果：

売上ID
1
2
3
4
5
売上金額
3732
44567
43613
46891
9804
税フラグ
税あり
税なし
税あり
税あり
不明

説明：

  • DECODE(税フラグ, '1', '税あり', '2', '税なし', '不明')：税フラグが'1'の場合は「税あり」、'2'の場合は「税なし」、それ以外の場合は「不明」を返します。

NVL関数

NVL関数は、SQLでNULL値を他の値に置き換えるために使われる関数です。データ分析やレポート作成時に非常に便利です。

基本的な使い方

書式：

説明：

  • 式：NULLかもしれない値。
  • 置き換える値：式がNULLの場合に代わりに使う値。
具体例 

以下の「従業員」テーブルを例に考えてみましょう。
従業員ID
1
2
3
4
5
従業員名
七草
中村
小林
井上
佐久間
部署ID（NULLあり）
2
1
2
NULL
3
このテーブルで、NULLの部署IDを「未配属」と表示したい場合、NVL関数を使います。

クエリ：

結果：

従業員ID
1
2
3
4
5
従業員名
七草
中村
小林
井上
佐久間
部署ID（NULLあり）
2
1
2
未配属
3

説明：

  • NVL(部署ID（NULLあり）, '未配属')：部署IDがNULLの場合は「未配属」と表示され、NULLでない場合はそのままの値が表示されます。

TO_CHAR関数、TO_DATE関数、TO_NUMERIC関数

Dr.SumのTO_CHAR、TO_DATE、TO_NUMERIC関数は、データの型を変換するために使用されます。これらの関数を使うことで、文字列、日付、数値の間でデータを変換することができます。

1. TO_CHAR関数

説明：

  • TO_CHAR関数は、数値や日付を文字列に変換します。

使い方：

例 

1. 数値を文字列に変換

数値12345を文字列に変換し、カンマ区切りで表示する場合：
結果：'12,345'
例 

2. 日付を文字列に変換

日付2024-06-21をYYYY-MM-DD形式の文字列に変換する場合：
結果：'2024-06-21'

2. TO_DATE関数

説明：

  • TO_DATE関数は、文字列を日付に変換します。

使い方：

例 

文字列を日付に変換

文字列'2024-06-21'を日付に変換する場合：
結果：2024-06-21（日付型）

3. TO_NUMERIC関数

説明：

  • TO_NUMERIC関数は、文字列を数値に変換します。

使い方：

例 

文字列を数値に変換

文字列'12345'を数値に変換する場合：
結果：12345（数値型）

具体例を使った説明

例 

1：売上データ

売上ID
1
2
売上年月日（文字型）
‘2023/01/01’
‘2023/01/02’
このテーブルで、価格を数値に変換し、発売日を日付に変換したい場合：

クエリ：

結果：
商品ID
1
2
売上年月日
2023/01/01
2023/01/02
例 

2：給与データ

社員ID
1
2
名前
田中
鈴木
基本給
300000
350000
支給日
20240621
20240701
このテーブルで、支給日を日付に変換し、基本給を文字列に変換してフォーマットする場合：

クエリ：

結果：
社員ID
1
2
名前
田中
鈴木
文字列基本給
300,000
350,000
支給日_日付
2024-06-21
2024-07-01

まとめ

  • TO_CHAR関数：数値や日付を文字列に変換します。
  • TO_DATE関数：文字列を日付に変換します。
  • TO_NUMERIC関数：文字列を数値に変換します。
これらの関数を使うことで、データの型を柔軟に変換し、必要な形式でデータを表示・操作することができます。