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

rating.sql

CREATE TABLE blog_entry (
    written_by VARCHAR(32) NOT NULL,
    entry_date DATE NOT NULL,
    favs INT NOT NULL
)
;
ALTER TABLE blog_entry ADD CONSTRAINT UNIQUE (written_by, entry_date)
;

INSERT INTO blog_entry VALUES
    ('ギイ', '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)
;

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
;

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
    INNER 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
;

/* MySQLでlinesは予約語なのでバッククォートで囲まないと使えない
CREATE TABLE blog_entry2 (
    written_by VARCHAR(32) NOT NULL,
    entry_date DATE NOT NULL,
    `lines` INT NOT NULL
)
;
*/