Abstract

例えば、ログ解析とかユーザーのライフタイムを予測するような問題設定を考えた時に、DBに格納されているイベントのcreated_atやupdated_atを用いて、差分を考えたりすることがあると思います。

MySQLの公式を見ると、　
3.6.4 特定のカラムのグループごとの最大値が格納されている行
というページがあって、最大業者の抽出クエリが紹介されています。

この中で出てくるleft joinを使って、私は今まで最大のレコードを抽出していたのですが、
異なるイベントを紐づけるようなケースで、必ずしも共通のIDが存在しないケース考えた時に少し問題があったのでクエリを考えてみました。いうまでもなく、Window関数とか使えばもっと楽になると思います。

Query

異なるイベントを設定するために、event1, event2というテーブルを考えます。
それぞれのテーブルには、user_id, created_atのみが存在するとします。

モチベーションは以下です。
* 両方のイベントに存在するuserを対象にする
* event2の最新created_atが欲しい
* event1はevent2より前のデータだけ欲しい

ユースケースの筆頭は、あるIDの流入から流出までのデータを取得するケースです。

SELECT e1.user_id, e1.created_at
FROM event1 AS e1
INNER JOIN (SELECT user_id, 
                   MAX(created_at) AS 'last_date'
            FROM event2 
            GROUP BY user_id
            ) AS e2 
            ON e1.user_id = e2.user_id 
                AND e1.created_at < e2.last_date
;

Details

SELECT文の評価順序の話によると、SELECTの評価順序は以下のようです。

FROM
ON
JOIN
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
ORDER BY
TOP（LIMIT)

故に、パフォーマンスを重視せざるをえないデータ量になると、なるべく早い段階で取得するデータ量を削減しておいた方がお得です。left joinでとりあえず結合して、whereで抽出するよりもonで条件を絞った上で、データを取得した方が合理的でしょう。

また、ある任意の期間で抽出する必要がある場合は、onの不等号の部分をbetweenに置き換えれば良いでしょう。
SQLを使う際は、必要なデータをコンパクトにシンプルに、集合論を意識しながらやると良いのかなあと思うところです。
クエリのパフォーマンスに影響しそうな部分はどこだろうと考えて、先にきりわけておくとかも有用に感じます。

今後は、高速化や効率化をするクエリについても調べていきたいです。

MySQL5.xで、最大最小のレコードを用いた条件抽出

Abstract

Query

Details

Reference