Help us understand the problem. What is going on with this article?

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

More than 1 year has passed since last update.

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の評価順序は以下のようです。

  1. FROM
  2. ON
  3. JOIN
  4. WHERE
  5. GROUP BY
  6. HAVING
  7. SELECT
  8. DISTINCT
  9. ORDER BY
  10. TOP(LIMIT)

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

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

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

Reference

Why do not you register as a user and use Qiita more conveniently?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away