SQL-access log analysis

テーマ：ネットサービスのアクセスログ解析

サンプルデータベースのテーブル構成

━━━━━━━━━━━━━━━━━━━━━━━━━━━━

eventlog（イベントのアクセス記録）

users（ユーザー一覧）

events（イベント一覧）

jobs（職業一覧）

area（都道府県一覧）

https://gyazo.com/32f2ec6b347e7396a7ca0868df3b1b8f

SQL（SELECT文）の処理順

━━━━━━━━━━━━━━━━━━━━━━━━━━━━

- 1. FROM 対象テーブルからデータを取り出す

- 2. WHERE 条件に一致するレコードを絞り込み

- 3. GROUP BY グループ化

- 4. HAVING 集計結果から絞り込み

- 5. SELECT 指定したカラムだけを表示

内部結合

━━━━━━━━━━━━━━━━━━━━━━━━━━━━

jobs + area テーブルの連結をする（INNER JOIN）

https://gyazo.com/9487fa364083fd99a9ceefc43b43a97e

内部結合のポイント

FROM句（結合を行う時はFROM句に複数のテーブルを記述する、これを可能にする⇒「INNER JOIN」）

ON句　（内部結合でON句は必須、記述場所はFROMとWHEREの間）

SELECT句（結合を使った場合のSELECT句の列は次の書式で書く⇒＜テーブルの別名＞.＜列名＞）

ログ解析

━━━━━━━━━━━━━━━━━━━━━━━━━━━━

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

https://gyazo.com/629cdfd2bd49b5e1d2c092c65a8ee6ba

GROUP BYで日付別にまとめて、COUNT関数で行数カウント※時間を表示したくないのでDATE関数使用

月次のアクセス数を求める

https://gyazo.com/1ec0a68f825e8f0d4fcd57c1845a3c26

MEMO

DATE関数（年月と日付を表示、日次で集計）

DATE_FORMAT関数（指定したフォーマットで日付データを取り出す、月次で集計）

COUNT関数（行数をカウント）

GROUP BY句（テーブルをいくつかのグループに切り分ける役割）

WHERE句（選択したい行の条件を指定する）

⇒特定の期間を指定する（BETWEEN '日付 ' AND '日付 '）

アクティブユーザーを調べる

━━━━━━━━━━━━━━━━━━━━━━━━━━━━

アクティブユーザーを求める

https://gyazo.com/b3dadb7dec905e8a2d57213224cf5140

日別のアクティブユーザー数を求める（DAU）

https://gyazo.com/61aa7ec647d96764b575d446533e79f7

MEMO

AS（カラムの別名をつける）

NULL（データがからっぽ）

DISTINCT（SELECT文の実行結果の重複レコード(データ行)を1つにまとめる）

データの集計をする

━━━━━━━━━━━━━━━━━━━━━━━━━━━━

ユーザーごとの獲得経験値の平均を求める

https://gyazo.com/4a82076e7e78aa2fbee2f1e09cfd2e72

ユーザーごとの獲得経験値の平均を求める

https://gyazo.com/adf8562e0a8b23f682967cd731ca2dc8

獲得経験値が3000以上のユーザーだけを求める

https://gyazo.com/6792d6203bc7e5761773ed62113c6933

MEMO

SUM関数（合計を求める）

AVG関数（平均を求める）

GROUP BY＋関数（関数とGROUP BYを使って合計を求める）

HAVING（グループに対する条件指定※GROUP BYの後ろに書く）

日付に関するデータを計算

━━━━━━━━━━━━━━━━━━━━━━━━━━━━

プレイ期間を求める

⇒開始日から終了日を引き算（期間を表すため＋１）

https://gyazo.com/a1656bfb3cc7fde8cca2dad76de4cad7

ユーザーの年齢を計算する

⇒現在の年から生まれた年を引き算 + 満年齢を算出

https://gyazo.com/1c12353c7521b257a9d1f520bc2e0008

MEMO

MAX関数（最大値を求める）

MIN関数（最小値を求める）

TIMESTAMPDIFF関数（自動的に満年齢を計算してくれる）

テキスト検索

━━━━━━━━━━━━━━━━━━━━━━━━━━━━

誰がいつどのイベントに参加したのかを調べる

https://gyazo.com/f06b1607192a6f58afd41b1e21b4588e

特定のユーザーで絞り込む（no.2のユーザー）

https://gyazo.com/df913f8176fda101c37f32ea40b89da2

サブクエリでアクティブユーザー数を求める

━━━━━━━━━━━━━━━━━━━━━━━━━━━━

サブクエリの基本形

-- FROM句に書く場合

SELECT *

FROM (サブクエリ) AS (サブクエリ名);

日ごとのアクティブユーザー数を求める

https://gyazo.com/291945b36153cb7cbca6cc0055aa1a68

CASE文でグループ分け

━━━━━━━━━━━━━━━━━━━━━━━━━━━━

CASEの基本形

https://gyazo.com/cf46923a3fcd4492d1fb36ccf747b0f7

データを分類し直す

初級、中級、上級の3つの「クラス」に分ける

https://gyazo.com/c18c5b0b11bfaeb763caddb314d0bf9a

上記のクラスごとにユーザー数をカウントする

https://gyazo.com/b9a5aad016a838985dad1a80f74a642e

MEMO

CASE式（CASE式とは「場合分け」「条件分岐」）

クロス集計

━━━━━━━━━━━━━━━━━━━━━━━━━━━━

イベントログの情報を初級、中級、上級の３つの「クラス」に分けて、月別に集計する

https://gyazo.com/182b4b4cc153447edaa697b1bdbda942

サブクエリで平均や割合を求める

━━━━━━━━━━━━━━━━━━━━━━━━━━━━

サブクエリで平均レベル以上の割合を求める

https://gyazo.com/a0a316378c8067d183789e56ebdda212