SQLで統計量を求める

駆け出しデータサイエンティストのNishです
今回は、SQLで基本統計量を求める方法についての記事です
以下の統計量をSQLで求めます

• レコード数
• 最大値/最小値/平均/総和
• 最頻値

扱うデータ

テスト用に、以下のデータを用意しました
webサイトのアクセスログだと思って下さい
(テキトー過ぎてアレな感じですが、テスト用なのでご容赦下さい)

page_viewテーブル

+---------+-----+-----+---------+
| user_id | age | sex | page_id |
+---------+-----+-----+---------+
|       1 |  25 | M   |   10001 |
|       1 |  25 | M   |   10002 |
|       1 |  25 | M   |   10003 |
|       2 |  35 | F   |   10001 |
|       2 |  35 | F   |   10003 |
|       2 |  35 | F   |   10004 |
|       2 |  35 | F   |   10005 |
|       3 |  42 | M   |   10002 |
|       4 |  10 | F   |   10003 |
|       4 |  10 | F   |   10004 |
|       4 |  10 | F   |   10005 |
+---------+-----+-----+---------+

各種統計量の求め方

レコード数

これは簡単
COUNTすればいいだけ

レコード数

全体のレコード数を出すには、以下のようにCOUNT(*)すればok

SELECT
  COUNT(*) AS record_count
FROM
  page_view

+--------------+
| record_count |
+--------------+
|           11 |
+--------------+
1 row in set (0.13 sec)

UU(ユニークユーザ)数

ユニークな値の個数を知りたい場合がある
例えば、あるサービスの月間UU数などを抽出して、その推移を調べることがある
この場合もCOUNTすれば十分だが、ユニークな値の個数を抽出するにはCOUNT(DISTINCT hoge)とする

SELECT
  COUNT(DISTINCT user_id) as uu
FROM
  page_view

+----+
| uu |
+----+
|  4 |
+----+
1 row in set (0.00 sec)

各ページを閲覧したユーザのUU数を男性/女性で分けて集計するには、以下のようにする

SELECT
  page_id,
  COUNT(DISTINCT (CASE WHEN sex = 'M' THEN user_id ELSE NULL END)) AS pv_male,
  COUNT(DISTINCT (CASE WHEN sex = 'F' THEN user_id ELSE NULL END)) AS pv_female
FROM
  page_view
GROUP BY
  page_id

+---------+---------+-----------+
| page_id | pv_male | pv_female |
+---------+---------+-----------+
|   10001 |       1 |         1 |
|   10002 |       2 |         0 |
|   10003 |       1 |         2 |
|   10004 |       0 |         2 |
|   10005 |       0 |         2 |
+---------+---------+-----------+
5 rows in set (0.00 sec)

最大値/最小値/平均値/総和

これらも簡単
SQLに用意されている、集約関数を使いましょう

最大値/最小値/平均値

各ページ閲覧者の、年齢の最大値/最小値/平均値を求めるには、次のクエリを実行すればok

SELECT
  page_id,
  MAX(age) AS max_age,
  MIN(age) AS min_age,
  AVG(age) AS average_age
FROM
  page_view
GROUP BY
  page_id

+---------+---------+---------+-------------+
| page_id | max_age | min_age | average_age |
+---------+---------+---------+-------------+
|   10001 |      35 |      25 |     30.0000 |
|   10002 |      42 |      25 |     33.5000 |
|   10003 |      35 |      10 |     23.3333 |
|   10004 |      35 |      10 |     22.5000 |
|   10005 |      35 |      10 |     22.5000 |
+---------+---------+---------+-------------+
5 rows in set (0.00 sec)

総和

これも簡単
SQLに用意されている、SUM()関数を使用しましょう
各ページを閲覧した、男性/女性ユーザの総数を抽出するには、以下のクエリを実行する

SELECT
  page_id,
  SUM(CASE WHEN sex = 'M' THEN 1 ELSE 0 END) AS pv_male,
  SUM(CASE WHEN sex = 'F' THEN 1 ELSE 0 END) AS pv_female
FROM
  page_view
GROUP BY
  page_id

+---------+---------+-----------+
| page_id | pv_male | pv_female |
+---------+---------+-----------+
|   10001 |       1 |         1 |
|   10002 |       2 |         0 |
|   10003 |       1 |         2 |
|   10004 |       0 |         2 |
|   10005 |       0 |         2 |
+---------+---------+-----------+
5 rows in set (0.00 sec)

最頻値

最頻値については集約関数がない場合もあるので、自前で頑張ります
もっともよく閲覧されたページを特定するには、以下のようにサブクエリを使用しましょう
(スカラサブクエリにASで別名を与えると文法エラーになります。これで１時間くらいハマりました。。)

SELECT
  page_id,
  COUNT(*) as mode
FROM
  page_view
GROUP BY
  page_id
HAVING
  COUNT(*) >= (
    SELECT
      MAX(cnt)
    FROM (
      SELECT
        COUNT(*) AS cnt
      FROM
        page_view
      GROUP BY
        page_id
      ) AS pv_list
    )

+---------+------+
| page_id | mode |
+---------+------+
|   10003 |    3 |
+---------+------+
1 row in set (0.00 sec)

ORDER BYとLIMIT 1の組み合わせでも可能ですが、ソート処理は重いので避けましょう
(特にビックデータ分析では)