SQLの忘備録2

#02:SQLの書き方のポイント
SQLで間違いやすいポイント
- SELECTのカンマ忘れ、カンマ多すぎ
- テーブル連結時のテーブル名不足
- WHEREでAND忘れ



演習問題①：
右側のコードエリアのSQLは、サンプルデータベースのeventlogテーブルを10行だけ表示します。
ここに、usersテーブルをINNER JOINで連結して、表示させてください。
なお、eventlogテーブルとusersテーブルは、userIDで連結します。

```sql:2-1.sql
-- 修正前SQL
SELECT * FROM eventlog
WHERE logID <= 10;

--①模範解答-- logIDが10以下のイベントログを表示
SELECT * FROM eventlog
	INNER JOIN users ON users.userID = eventlog.userID
WHERE logID <= 10;
```

演習問題②
下記のコードエリアのSQLは、サンプルデータベースのeventlogテーブルを10行だけ表示しますが、
エラーになってしまします。コードを修正して、logIDとusers.nameが表示されるようにしてください。
コードを実行して、エラーが出なければ、演習課題クリアです！

```sql:2-2.sql
-- logIDが10以下のイベントログを表示
SELECT logID users.name
FROM eventlog
	INNER JOIN users ON users.userID = eventlog.userID
WHERE logID <= 10;

--②模範解答
-- logIDが10以下のイベントログを表示　logIDの後のカンマ忘れによるエラー
SELECT logID, users.name
FROM eventlog
	INNER JOIN users ON users.userID = eventlog.userID
WHERE logID <= 10;
```

演習課題③
右側のコードエリアのSQLは、サンプルデータベースのeventlogテーブルで、logIDが20番台のデータだけを表示しますが、エラーになってしまいます。コードを修正して、正しく表示されるようにしてください。

```sql:2-3.sql
-- 修正前SQL
-- logIDが20番台のイベントログを表示
SELECT * FROM eventlog
WHERE logID >= 20
	logID < 30;
--③模範解答-- logIDが20番台のイベントログを表示
SELECT * FROM eventlog
WHERE logID >= 20
	AND logID < 30;
--ANDがなくてエラーになっていた。
```

#03:ログ解析してみよう 

-- 日次のアクセス数を求める
SELECT DATE(startTime), COUNT(logID)
FROM eventlog
GROUP BY DATE(startTime);
--特定の範囲の日次アクセス数の基本形

-- 日次のアクセス数を求める

SELECT DATE(startTime), COUNT(logID)
FROM eventlog
WHERE DATE(startTime) BETWEEN "2015-04-01" AND "2015-04-30"
GROUP BY DATE(startTime);

月次アクセス数の基本形

-- 月次のアクセス数を求める
SELECT DATE_FORMAT(startTime, '%Y-%m'), COUNT(logID)
FROM eventlog
GROUP BY DATE_FORMAT(startTime, '%Y-%m');


###演習課題①「日次アクセス数を求める」
右側のコードエリアのSQLは、サンプルデータベースのeventlogテーブルで、日時とlogIDを表示します。
COUNT関数とGROUP BYを追加して、日次のアクセス数を表示させてください。

```sql:3-1.sql
-- 日次のアクセス数を求める
SELECT DATE(startTime), logID
FROM eventlog;

--↓模範解答
SELECT DATE(startTime), COUNT(logID)
FROM eventlog
GROUP BY DATE(startTime);
```


###演習課題②「日次アクセス数を7日分求める」
右側のコードエリアのSQLは、サンプルデータベースのeventlogテーブルで、日次アクセス数を表示します。ここに、WHEREを追加して、3月の最初の7日間('2015-03-01'から'2015-03-07'の期間)の日次アクセス数を表示させてください。

```sql:3-3.sql
-- 3月の日次アクセス数を求める
SELECT DATE(startTime), COUNT(logID)
FROM eventlog
GROUP BY DATE(startTime);

--模範解答1--BETWEEN ANDで期間を指定する
-- 3月の日次アクセス数を求める
SELECT DATE(startTime), COUNT(logID)
FROM eventlog
WHERE DATE(startTime) BETWEEN "2015-03-01" AND "2015-03-07"
GROUP BY DATE(startTime);
```

###演習課題「月次アクセス数を求める」
右側のコードエリアのSQLは、サンプルデータベースのeventlogテーブルで、日時とlogIDを表示します。COUNT関数とGROUP BYを追加して、月次のアクセス数を表示させてください。
日時を年月に変換するには、
「GROUP BY DATE_FORMAT(startTime, '%Y-%m')」を使います。

```sql:3-3.sql
-- 月次のアクセス数を求める
SELECT DATE(startTime), logID
FROM eventlog;
--模範解答1---年月をGROUP BYでまとめて、logIDをCOUNT関数で集計する
SELECT DATE_FORMAT(startTime, '%Y-%m'), COUNT(logID)
FROM eventlog
GROUP BY DATE_FORMAT(startTime, '%Y-%m');
```


#04:アクティブユーザーを調べよう 
ここでは、SQLを使って、オンラインRPGに登録したままのアクティブユーザー数を求めます。

 カラム名を別名で表示する (AS)

SELECT userID AS "アクティブユーザー"
FROM users;
演習課題「userIDが50番以降のアクティブユーザー」
右側のコードエリアのSQLは、サンプルデータベースのusersテーブルから、userIDが50番以降のアクティブユーザーを表示します。ここから、登録されたままのユーザーだけを表示するようにしてください。登録されたままのユーザーは、deleted_atカラムがNULLになっています。

```sql:4.sql
-- userIDが50番以降のアクティブユーザーを表示する
SELECT *
FROM
	users
WHERE
	userID >= 50;
--模範解答1
--WHEREに、ANDで、deleted_at IS NULLを追加
-- userIDが50番以降のアクティブユーザーを表示する
SELECT *
FROM
	users
WHERE
	userID >= 50
	AND deleted_at IS NULL;
```

演習課題「東京都のアクティブユーザー」
右側のコードエリアのSQLは、サンプルデータベースのusersテーブルから、アクティブな(登録したままの)ユーザーを表示します。ここに、areaテーブルを連結して、東京都のアクティブユーザーだけを表示してください。

なお、usersテーブルとareaテーブルは、areaIDで連結します。
また、都道府県名は、areaテーブルのarea_nameカラムに格納されています。

コードの実行をして、エラーが出なければ、演習課題クリアです！

```sql:4.sql
-- 東京都のアクティブユーザーを表示する
SELECT *
FROM
	users
	INNER JOIN area ON area.areaID = users.areaID
WHERE
	deleted_at IS NULL;
--模範解答1
--WHEREに、ANDで、「area_name="東京都"」を追加
SELECT *
FROM
	users
	INNER JOIN area ON area.areaID = users.areaID
WHERE
	deleted_at IS NULL
	AND area_name = "東京都";
```

#05:データを集計しよう 
ここでは、SQLを使って、獲得経験値の合計や平均を集計する方法を学習します。
さらに、ユーザーのプレイ開始日とプレイ最終日を調べてみましょう。

 SELECT文の処理順
- 1. FROM 対象テーブルからデータを取り出す
- 2. WHERE 条件に一致するレコードを絞り込み
- 3. GROUP BY グループ化
- 4. HAVING 集計結果から絞り込み
- 5. SELECT 指定したカラムだけを表示

```sql:sample.sql
--重複した行を省いて表示する (DISTINCT)
SELECT DISTINCT userID AS "アクティブユーザー"
FROM users;

--空のカラムの行を表示する (IS NULL)
SELECT userID AS "アクティブユーザー"
FROM users
WHERE deleted_at IS NULL;
```

演習課題「獲得所持金が50以上のユーザーだけ表示する」
右側のコードエリアのSQLは、サンプルデータベースのeventlogテーブルから、userIDおよび合計の獲得所持金と平均の獲得所持金を表示します。ここに、HAVINGを追加して、合計の獲得所持金が50以上のユーザーだけ表示してください。

```sql:5.sql
-- ユーザーごとの合計獲得金額と平均獲得金額
SELECT
	eventlog.userID AS "ユーザーID",
	SUM(events.increase_gold) AS "合計",
	AVG(events.increase_gold) AS "平均"
FROM
	eventlog
	INNER JOIN events ON events.eventID = eventlog.eventID
GROUP BY eventlog.userID;

--模範解答1
--levelでグループ化、平均経験値と平均所持金を表示
SELECT
	eventlog.userID AS "ユーザーID",
	SUM(events.increase_gold) AS "合計",
	AVG(events.increase_gold) AS "平均"
FROM
	eventlog
	INNER JOIN events ON events.eventID = eventlog.eventID
GROUP BY eventlog.userID
HAVING SUM(events.increase_gold) >= 50;
```



#06:ユーザーの年齢を計算をしよう 
```sql:sample.sql
--現在の日時を求める
CURRENT_DATE()) AS 現在日時

--2つの日時の間の期間を整数で求める
TIMESTAMPDIFF(YEAR, (誕生日), (現在の日時))
```
###演習課題6-1「ユーザーの平均年齢を求める」
右側のコードエリアのSQLは、サンプルデータベースのusersテーブルから、2016年12月1日時点のユーザーの年齢を表示します。これを利用して、AVG関数を使って平均年齢を求めてください。
出力結果には、「平均年齢」を表示します。

```sql:6-1.sql
-- ユーザーの平均年齢を求める
SELECT
	TIMESTAMPDIFF(YEAR, birth, '2016-12-01') AS '満年齢'
FROM
	users;

-- 模範解答1--ユーザーの平均年齢を求める
SELECT
	AVG(TIMESTAMPDIFF(YEAR, birth, '2016-12-01')) AS '平均年齢'
FROM
	users;
```

#07:テキストを検索しよう 

```sql:sample.sql
--テキストに部分的に一致するレコードを取り出す
-- テキスト検索
SELECT
	events.event_summary
FROM
	eventlog
	INNER JOIN events ON events.eventID = eventlog.eventID
WHERE  events.event_summary LIKE '%との闘い'
```

###演習課題「山のある都道府県」
右側のコードエリアのSQLは、サンプルデータベースのareaテーブルから、都道府県名を表示します。ここから、「山」という文字が含まれる都道府県だけを表示してください。

```sql:7.sql
-- 特定の都道府県を絞り込む
SELECT *
FROM area;

-- 模範解答   LIKEで、「%山%」を取り出す。
SELECT *
FROM area
WHERE area_name LIKE "%山%";
```
##08:サブクエリでアクティブユーザー数を求めよう 

```sql:sample.sql
--サブクエリの基本形
-- FROM句に書く場合
SELECT *
FROM (サブクエリ) AS (サブクエリ名);
```
演習課題「月次のアクティブユーザー数を求める」
右側のコードエリアのSQLは、サンプルデータベースのusersテーブルから、年月とuserIDを表示します。これをサブクエリにして、月次のアクセス数を表示するようにしてください

```sql:8.sql
-- 月次アクティブユーザー数を求める
SELECT DISTINCT
	DATE_FORMAT(startTime, '%Y-%m') AS yearMonth,
	eventlog.userID AS user
FROM eventlog
	INNER JOIN users ON users.userID = eventlog.userID
WHERE deleted_at IS NULL;

--模範解答1
--外側のSELECTにカラムを追加。サブクエリに名前を付ける。GROUP BYでグループ化。
-- 月次アクティブユーザー数を求める
SELECT yearMonth, COUNT(user)
FROM (SELECT DISTINCT
	DATE_FORMAT(startTime, '%Y-%m') AS yearMonth,
	eventlog.userID AS user
FROM eventlog
	INNER JOIN users ON users.userID = eventlog.userID
WHERE deleted_at IS NULL) AS ActiveUsers
GROUP BY yearMonth;
```


#09:グループ分けしよう 
ここでは、すでに分類されたデータを別の基準でグループ分けしてみます。
たとえば、オンラインRPGでユーザーのレベルを初級・中級・上級に分けて集計する、
都道府県を関東や関西といった地域にまとめて集計する、といった操作が可能になります。
そのために、SQLのCASE(ケース)命令を紹介します。

 CASEの基本形

```sql:sample.sql
-- データを分類し直す
SELECT
	userID,
	level,
	CASE
		WHEN (条件式1) THEN (出力1)
		WHEN (条件式2) THEN (出力2)
		ElSE (出力3)
	END
FROM
	users
```

###演習課題「ユーザーの財務状況を調べる」
右側のコードエリアのSQLは、サンプルデータベースのusersテーブルから、userIDと所持金を表示します。そこで、 financeというカラムを追加して、下記のように表示してください。
- 所持金が3000以上 ： 大金持ち
- 所持金が1000以上 ： 小金持ち
- 所持金が1000未満 ： 発展途上

```sql:9.sql
-- 所持金で、お金持ちか分類する
SELECT userID, gold
FROM users;

--模範解答1  CASEで、gold別に分類
-- 所持金で、お金持ちか分類する
SELECT
	userID,
	gold,
	CASE
		WHEN gold >= 3000 THEN "大金持ち"
		WHEN gold >= 1000 THEN "小金持ち"
		ELSE "発展途上"
	END AS finance
FROM users;
```

#10:クロス集計してみよう 

 クロス集計表を作る手順
1. クロス集計の元になるデータを用意する
2. サブクエリとして読み込む
3. CASEで、特定の値だったら1にする。このとき別名を、特定の値と同じにする

CASE WHEN クラス = "初級" THEN 1 ELSE NULL END AS "初級",
CASE WHEN クラス = "中級" THEN 1 ELSE NULL END AS "中級",
CASE WHEN クラス = "上級" THEN 1 ELSE NULL END AS "上級"

###演習課題「ユーザーの財務状況をクロス集計する」
右側のコードエリアのSQLは、サンプルデータベースのeventlogテーブルから、userIDと所持金をfinanceというカラムに分類して、下記のようにサブクエリで表示します。

- 所持金が3000以上 ： 大金持ち
- 所持金が1000以上 ： 小金持ち
- 所持金が1000未満 ： 発展途上

外側のSQLに「大金持ち」「小金持ち」「発展途上」というカラムを追加して、クロス集計表を作ってください。

```sql:10.sql
-- 所持金で分類してクロス集計
SELECT
	日付,
	finance
FROM ( SELECT DISTINCT
	DATE_FORMAT(startTime, '%Y%m') AS 日付,
	eventlog.userID,
	(CASE
		WHEN gold >= 3000 THEN "大金持ち"
		WHEN gold >= 1000 THEN "小金持ち"
		ELSE "発展途上"
	END) AS finance
	FROM eventlog
		INNER JOIN users ON users.userID = eventlog.userID
	) AS クラス分け
--模範解答1   CASEで、gold別に分類
--所持金で分類してクロス集計
SELECT
	日付,
	SUM(CASE WHEN finance = "大金持ち" THEN 1 ELSE 0 END) AS "大金持ち",
	SUM(CASE WHEN finance = "小金持ち" THEN 1 ELSE 0 END) AS "小金持ち",
	SUM(CASE WHEN finance = "発展途上" THEN 1 ELSE 0 END) AS "発展途上"
FROM ( SELECT DISTINCT
	DATE_FORMAT(startTime, '%Y%m') AS 日付,
	eventlog.userID,
	(CASE
		WHEN gold >= 3000 THEN "大金持ち"
		WHEN gold >= 1000 THEN "小金持ち"
		ELSE "発展途上"
	END) AS finance
	FROM eventlog
		INNER JOIN users ON users.userID = eventlog.userID
	) AS クラス分け
GROUP BY 日付;
```

#11:サブクエリで、平均や割合を求めよう 

###演習課題「平均以上の所持金を持つユーザーだけ表示する」
右側のコードエリアのSQLは、サンプルデータベースのusersテーブルから、userIDと所持金を表示します。ここにWHEREとサブクエリを追加して、平均以上の所持金を持つユーザーだけ表示するようにしてください。平均値は、AVG関数で求めることができます。

```sql:11.sql
-- 平均以上の所持金を持つユーザー数を表示する
SELECT userID, name, gold
FROM users;

-- 平均以上の所持金を持つユーザー数を表示する
SELECT userID, name, gold
FROM users
WHERE gold >= (SELECT AVG(gold) FROM users);
```