前回は、データベースに文字列として保存されているデータに対してLIKE句を使用してテキストの検索を行いました。

LIKE句は、

events.event_summary LIKE '%との闘い'


のように使い、これはeventsテーブルのevent_summaryから、との闘いで終わる文字列を検索できます。

“%”は0文字以上の任意の文字列を指定しており、他にも任意を1文字を指定する”_”があります。

今回学習する内容は、以前学習したアクティブユーザー数の求め方を、サブクエリを使って求めて見たいと思います。

なお、今回使用するサンプルデータベースは前回までと同じようにこちらです。

まずは、サブクエリについて説明していきます。

サブクエリとは、副問い合わせとも呼ばれ、簡単にいうとSQL文の中にSQL文を書くことを指し、SQLを実行した結果をさらに別のSQLと組み合わせて使う事ができます。

例として、このようなものをサブクエリと呼びます。

(WHERE句で使う場合)

SELECT カラム
FROM テーブル
WHERE カラム名 = (SELECT カラム
FROM テーブル
WHERE カラム名 = 値)


実行順序は、まずはカッコ内のSQLを通常通り、FROMで指定したテーブルにWHERE句で値を指定し、SELECTで取り出します。

それから、カッコ外のSQLを実行し、FROMで指定したテーブルからWHERE句で指定している、カラム名とカッコ内の値が当てはまる条件をSELECTで取り出します。


一見複雑そうに見えるサブクエリですが、覚えてしまえば簡単でとても便利です。

ですが、サブクエリを実行する場合動作が重くなるというデメリットもあります。

まずは、イベントログから日ごとで重複しないユーザーだけを取り出します。

SELECT DISTINCT
DATE(startTime) AS day,
eventlog.userID AS user
FROM eventlog
INNER JOIN users ON users.userID = eventlog.userID
WHERE deleted_at IS NULL;


DISTINCTで重複データを取り除き、eventlogテーブルにusersテーブルを連結させ、deleted_atの値が空のユーザーを抽出しています。

このSQLを実行すると、


このように、日付と重複しないuserIDを取り出すことができました。

では、今記述したSQLをサブクエリを使って新たに記述していきます。

SELECT *
FROM (SELECT DISTINCT
DATE(startTime) AS day,
eventlog.userID AS user
FROM eventlog
INNER JOIN users ON users.userID = eventlog.userID
WHERE deleted_at IS NULL)
AS ActiveUsers;


このSQLを実行すると、


このように、サブクエリを使わなかったSQLとまったく同じ実行結果が得られました。

今回作成したSQLでは、サブクエリで得られたデータをそのまま表示しているため、同じ結果が得られました。

続いては、カッコ外のSQLに追加で記述していき、日ごとのアクティブユーザー数を求めてみます。

SELECT day, COUNT(user) AS アクティブユーザー数
FROM (SELECT DISTINCT
DATE(startTime) AS day,
eventlog.userID AS user
FROM eventlog
INNER JOIN users ON users.userID = eventlog.userID
WHERE deleted_at IS NULL)
AS ActiveUsers
GROUP BY day;


このSQLでは、サブクエリにあるdayというカラムをカッコ外のGROUP BYでまとめています。

そして、userをカッコ外でカウントしています。

この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;


DATE_FORMAT関数を使用してyyyy-mmという形で年と月を指定しています。

あとは、日ごとを同じようにDISTINCTで重複しないように、deleted_atに値が入っていないデータを取り出しています。

このSQLを実行すると、


このように、月ごとで重複しないユーザーを取り出せました。

次は、サブクエリを使ってアクティブユーザー数を求めてみます。

SELECT yearMonth, COUNT(user) AS アクティブユーザー数
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;


日ごとのアクティブユーザー数の求め方と同じように、GROUP BYで年月をまとめ、userの数をCOUNTでカウントしています。

このSQLを実行すると、


このように、月ごとのアクティブユーザー数を求める事ができました。

サブクエリを使用してSQLを記述することができたので、ここでは次回学習予定のグループ分けに少しだけ触れてみます。

この学習で取り扱っている題材はオンラインRPGなので、例えばレベル毎に初心者・中級者・上級者とまとめたり、ユーザーごとに都道府県が登録されているため、東北地方・関東地方だったり関東・関西などの地域にまとめて集計するなどをしてみます。


そのために、CASE命令を使用します。

CASEの基本的な使い方は、このようになります。

SELECT
CASE
WHEN (条件) THEN (条件を満たしている場合に表示する内容)
ELSE (条件を満たしていなかった場合に表示する内容)
END
FROM (テーブル名);


CASEとENDで囲んだ中に、条件式を入れます。

この条件は、複数指定することも可能で、最後にELSEの行にどれにも当てはまらなかった場合の処理を記述します。