LoginSignup
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@gamelike319(ムトウ ヨシオ)

SQLの忘備録2

Last updated at Posted at 2019-09-21

02:SQLの書き方のポイント

SQLで間違いやすいポイント

  • SELECTのカンマ忘れ、カンマ多すぎ
  • テーブル連結時のテーブル名不足
  • WHEREでAND忘れ

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

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が表示されるようにしてください。
コードを実行して、エラーが出なければ、演習課題クリアです！

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番台のデータだけを表示しますが、エラーになってしまいます。コードを修正して、正しく表示されるようにしてください。

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を追加して、日次のアクセス数を表示させてください。

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'の期間)の日次アクセス数を表示させてください。

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')」を使います。

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になっています。

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カラムに格納されています。

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

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 対象テーブルからデータを取り出す
    1. WHERE 条件に一致するレコードを絞り込み
    1. GROUP BY グループ化
    1. HAVING 集計結果から絞り込み
    1. SELECT 指定したカラムだけを表示
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以上のユーザーだけ表示してください。

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:ユーザーの年齢を計算をしよう

sample.sql
--現在の日時を求める
CURRENT_DATE()) AS 現在日時

--2つの日時の間の期間を整数で求める
TIMESTAMPDIFF(YEAR, (誕生日), (現在の日時))

演習課題6-1「ユーザーの平均年齢を求める」

右側のコードエリアのSQLは、サンプルデータベースのusersテーブルから、2016年12月1日時点のユーザーの年齢を表示します。これを利用して、AVG関数を使って平均年齢を求めてください。
出力結果には、「平均年齢」を表示します。

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:テキストを検索しよう

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

演習課題「山のある都道府県」

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

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

-- 模範解答   LIKEで、「%山%」を取り出す。
SELECT *
FROM area
WHERE area_name LIKE "%山%";

08:サブクエリでアクティブユーザー数を求めよう

sample.sql
--サブクエリの基本形
-- FROM句に書く場合
SELECT *
FROM (サブクエリ) AS (サブクエリ名);

演習課題「月次のアクティブユーザー数を求める」
右側のコードエリアのSQLは、サンプルデータベースのusersテーブルから、年月とuserIDを表示します。これをサブクエリにして、月次のアクセス数を表示するようにしてください

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の基本形

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未満 ： 発展途上
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に「大金持ち」「小金持ち」「発展途上」というカラムを追加して、クロス集計表を作ってください。

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関数で求めることができます。

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

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

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?