More than 5 years have passed since last update.

SQLデータ分析入門#7『複数のテーブルにまたがって集計する』

Last updated at 2018-06-24Posted at 2018-01-05

スコープ

なお、「LEFT JOIN」で殆どの集計ができることから、意図的に他のJOINはスコープ外に置きます。
他には「INNER JOIN」「CROSS JOIN」「OUTER JOIN」などがあるので、興味があれば調べてください。

Excelと違って、データベースはテーブル数を贅沢に使って情報を貯めることが多いです。

例えばこのような内容を、

このように格納しています。

このように、各テーブルが、データを重複なく持っているような状態を「正規化」されていると言います。
詳しくは、データベース正規化 | リレーショナルデータベースのテーブル設計をする上で必要な知識。が詳しいです。

なお、このように正規化されたDBを、テーブル間が相互に関係をもっていることから、
リレーショナル・データベース(RDB)といい、この状態から集計するために「LEFT JOIN」は大変重要です。

テーブル①
LEFT JOIN テーブル② ON テーブル①のカラム = テーブル②のカラム

とすると、テーブル①にテーブル②がくっつきます。
例を見たほうが早いです。

こんなテーブルがあったとき、

Premium_UserテーブルにIDがある人を課金ユーザとした上で、

SELECT *
FROM
   Premium_User pu
   LEFT JOIN Profile p ON pu.user_id = p.user_id

Premium_User puはPremium_Userというテーブルにpuというあだ名(alias名)を付けるという意味で、
Profile pも同様にpというあだ名がつけられています

それを踏まえて、FROMの中を見てみると、

Premium_User pu
LEFT JOIN Profile p ON pu.user_id = p.user_id

これは、
「Premium_UserにProfileをくっつけます。」
「その時、puのユーザIDとpのユーザIDが等しいもの同士でくっつけてください」
という意味です。

その結果、こんなくっつき方をしました。
なお、Profileのくっつける先がなかったレコードは無視されます。

では、集計関数も組み合わせてつかってみましょう。FROMの中は変わりません。

SELECT COUNT(*), AVG(p.身長)
FROM
   Premium_User pu
   LEFT JOIN Profile p ON pu.user_id = p.user_id
GROUP BY 1

結果はこうなります。

すこし複雑になってきましたが、JOINを使うなど、複雑なことをして集計する場合は、
集計まえのテーブルのイメージを持つことが大事です。

FROMのなかに、

がいるから、集計すると

こうなるな、というイメージを持ってください。