日付が直近のレコードと比較してレーティングを求める

SQL

ミックさんの本を読み進めました。

達人に学ぶ SQL徹底指南書 (CodeZine BOOKS)

達人に学ぶ SQL徹底指南書 (CodeZine BOOKS)

「相関サブクエリ」の続きです。

例えば、ブログの更新日とふぁぼられた数をテーブルに保存しておいて、
直近の日付のレコードと比べて増えた・減った・変わらずというレーティングを算出するときどういうSQLを書けばいいでしょうか？

データとしてはこんなデータで、

mysql> select * from blog_entry;
+------------+------------+------+
| written_by | entry_date | favs |
+------------+------------+------+
| ギイ       | 2011-11-01 |   10 |
| ギイ       | 2011-11-02 |   10 |
| ギイ       | 2011-11-03 |   10 |
| 託生       | 2011-11-10 |   50 |
| ギイ       | 2011-11-20 |    5 |
| 託生       | 2011-11-22 |   70 |
| ギイ       | 2011-12-10 |    6 |
| 託生       | 2011-12-12 |   20 |
| ギイ       | 2011-12-30 |    6 |
| 託生       | 2011-12-30 |   30 |
+------------+------------+------+
10 rows in set (0.01 sec)

結果としてはこんな感じ。

+------------+------------+------+--------+
| written_by | entry_date | favs | rating |
+------------+------------+------+--------+
| ギイ       | 2011-11-01 |   10 | -      |
| ギイ       | 2011-11-02 |   10 | →      |
| ギイ       | 2011-11-03 |   10 | →      |
| ギイ       | 2011-11-20 |    5 | ↓      |
| ギイ       | 2011-12-10 |    6 | ↑      |
| ギイ       | 2011-12-30 |    6 | →      |
| 託生       | 2011-11-10 |   50 | -      |
| 託生       | 2011-11-22 |   70 | ↑      |
| 託生       | 2011-12-12 |   20 | ↓      |
| 託生       | 2011-12-30 |   30 | ↑      |
+------------+------------+------+--------+
10 rows in set (0.00 sec)

このデータの嫌なところは、
必ず前日のレコードがあるとは限らず、日付が飛んでしまう場合があるってことです。

こういうときは

  1. 自己相関サブクエリ
  2. LEFT OUTER JOIN

この二つを使うとうまくいきます☆

僕が書いたSELECT文はこんなやつです。

SELECT
    B1.written_by,
    B1.entry_date,
    B1.favs,
    CASE WHEN B1.favs = B2.favs THEN '→'
        WHEN B1.favs > B2.favs THEN '↑'
        WHEN B1.favs < B2.favs THEN '↓'
        ELSE '-' END rating
FROM
    blog_entry B1
    LEFT OUTER JOIN blog_entry B2
    ON
        B1.written_by = B2.written_by
        AND B2.entry_date = (
            SELECT
                MAX(entry_date) as entry_date
            FROM
                blog_entry B3
            WHERE
                B1.written_by = B3.written_by
                and B1.entry_date > B3.entry_date
            GROUP BY
                B3.written_by
        )
ORDER BY
    B1.written_by, B1.entry_date
;

わけがわからないので図解します ToT

答えだけいきなり見せられても意味が解りませんよね；；

この結果を取得するためには3つのステップを踏みます。

  1. 相関サブクエリを使って「直近の日付」を求める。
  2. 「直近の日付」を保持する集合を作る。
  3. その集合ともとの集合を比較する。

ちょっと図を書いてみました。これでわかってもらえるかな？？

まず、相関サブクエリを使って「直近の日付」を求めます。

ところが、これだと直近の日付を保持しておけないので、別の集合を用意します。

二つを比べます。

ちなみに、本で紹介されているのはちょっとだけ違う例です。
(「ギイ」とかミックさんの本にはもちろん出てきませんｗ)
「直近の日付」を求めるためにB3を使って、更にB2を比較の為に使うっていう発想が僕にはなかったので勉強になりました。
最初、B1とB2だけで求めようと思ってJOINと相関サブクエリをいじっていたんですけど、うまくいきませんでした。
もうひとつ、B3を使って、B1とB3、B1とB2を結びつけるんだってわかったときは「へぇ〜」って思いました。

初回登場データを漏らさない為にLEFT OUTER JOINを使う

LEFT OUTER JOINをINNER JOINに変えると

+------------+------------+------+--------+
| written_by | entry_date | favs | rating |
+------------+------------+------+--------+
| ギイ       | 2011-11-02 |   10 | →      |
| ギイ       | 2011-11-03 |   10 | →      |
| ギイ       | 2011-11-20 |    5 | ↓      |
| ギイ       | 2011-12-10 |    6 | ↑      |
| ギイ       | 2011-12-30 |    6 | →      |
| 託生       | 2011-11-22 |   70 | ↑      |
| 託生       | 2011-12-12 |   20 | ↓      |
| 託生       | 2011-12-30 |   30 | ↑      |
+------------+------------+------+--------+
8 rows in set (0.00 sec)

この通り、初回登場のレコードが出なくなってしまいます。
言い換えれば、LEFT OUTER JOINにするとこの問題を解決できるので覚えておくといいと思います☆

Gist: https://gist.github.com/1342897