前年度との比較

SQL

売り上げを保持しているテーブルがこんな感じに

取引先 取引日時 金額
1 2008-5-10 100
1 2008-5-9 200
2 2008-5-1 300
1 2008-4-20 500
2 2008-4-2 100
2 2008-4-1 200
略 略 略
1 2007-5-20 10
1 2007-5-10 50
1 2007-4-25 100
2 2007-4-20 100
1 2007-4-10 200
略 略 略

あったとして、2008年の5月に実行した場合、

取引先 年度 4月 5月 6月 7月 8月 9月 10月 11月 12月 1月 2月 3月
1 2008 500
1 2007 300
2 2008 300
2 2007 100

こんな感じで、取引先別、年別で合計したいとする。5月はまだ締めていないから出力は4月分だけ、さらに、2007年分もデータはあるけど対応する今年度のデータがない限り出力しない*1のが条件。
これを実現するSQLを、MySQLで組んでみた。

SELECT
    E.customer AS '取引先'
  , E.doc_year AS '年度'
    -- 対応する月を合計
  , sum(CASE E.doc_month WHEN  4 THEN E.price ELSE 0 END) AS  '4月'
  , sum(CASE E.doc_month WHEN  5 THEN E.price ELSE 0 END) AS  '5月'
  , sum(CASE E.doc_month WHEN  6 THEN E.price ELSE 0 END) AS  '6月'
  , sum(CASE E.doc_month WHEN  7 THEN E.price ELSE 0 END) AS  '7月'
  , sum(CASE E.doc_month WHEN  8 THEN E.price ELSE 0 END) AS  '8月'
  , sum(CASE E.doc_month WHEN  9 THEN E.price ELSE 0 END) AS  '9月'
  , sum(CASE E.doc_month WHEN 10 THEN E.price ELSE 0 END) AS '10月'
  , sum(CASE E.doc_month WHEN 11 THEN E.price ELSE 0 END) AS '11月'
  , sum(CASE E.doc_month WHEN 12 THEN E.price ELSE 0 END) AS '12月'
  , sum(CASE E.doc_month WHEN  1 THEN E.price ELSE 0 END) AS  '1月'
  , sum(CASE E.doc_month WHEN  2 THEN E.price ELSE 0 END) AS  '2月'
  , sum(CASE E.doc_month WHEN  3 THEN E.price ELSE 0 END) AS  '3月'
FROM
  -- 年度計算用
  (SELECT
      customer
    , doc_date
      -- 1月、2月、3月は年度としては前年の4月〜12月と同じ
    , year(doc_date) - CASE WHEN month(doc_date) IN (1, 2, 3) THEN 1 ELSE 0 END AS doc_year
    , month(doc_date) AS doc_month
    , price
  FROM
      Earnings
  ) E
WHERE
    -- 同じ月は対象外
    (month(now()) <> month(E.doc_date)) AND (
      -- 前年度の対象範囲は・・・
      E.doc_date BETWEEN
        -- 前年度の4月1日から
        concat(year(now()) - CASE WHEN month(now()) IN (1, 2, 3) THEN 2 ELSE 1 END, '-4-1') AND
        -- 前年度の先月の末日まで
        date_sub(date_format(date_sub(now(), interval CASE WHEN month(now()) IN (1, 2, 3) THEN 2 ELSE 1 END year), '%Y-%m-01'), interval 1 day) OR
      -- 今年度の範囲も前年度と同様
      E.doc_date BETWEEN
        concat(year(now()) - CASE WHEN month(now()) IN (1, 2, 3) THEN 1 ELSE 0 END, '-4-1') AND
        date_sub(date_format(date_sub(now(), interval CASE WHEN month(now()) IN (1, 2, 3) THEN 1 ELSE 0 END year), '%Y-%m-01'), interval 1 day)
    )
GROUP BY
    E.customer
  , E.doc_year
ORDER BY
    E.customer
  , E.doc_year desc;

WHERE句の中がちょっと複雑だから、ストアドファンクションにしてしまったほうが良いかも。
あと、WHERE句は丸ごと中のSELECTに移せるから、移したほうが高速かも。


ちなみに、おそらく一番複雑であろう部分は整形するとこんな感じ。

-- 1日を引く(先月の末日を求める)・・・・・・・・・・・・・・・・・(3)
date_sub(
  -- 1日を求める・・・・・・・・・・・・・・・・・・・・・・・・・(2)
  date_format(
    -- 1月、2月、3月なら2年を、それ以外なら1年を今日から引く・・・(1)
    date_sub(
      now(),
      interval CASE WHEN month(now()) IN (1, 2, 3) THEN 2 ELSE 1 END year
    ),
    '%Y-%m-01'
  ),
  interval 1 day
)


追記：バグってるし。(3)で一日分引いてるけど、これいらないな。これのせいで、月末のデータが無視されてしまう。
一番最初の条件で同じ月は対象外にしてるから、一日引かなくてもいいや*2。


正しくは、

SELECT
    E.customer AS '取引先'
  , E.doc_year AS '年度'
    -- 対応する月を合計
  , sum(CASE E.doc_month WHEN  4 THEN E.price ELSE 0 END) AS  '4月'
  , sum(CASE E.doc_month WHEN  5 THEN E.price ELSE 0 END) AS  '5月'
  , sum(CASE E.doc_month WHEN  6 THEN E.price ELSE 0 END) AS  '6月'
  , sum(CASE E.doc_month WHEN  7 THEN E.price ELSE 0 END) AS  '7月'
  , sum(CASE E.doc_month WHEN  8 THEN E.price ELSE 0 END) AS  '8月'
  , sum(CASE E.doc_month WHEN  9 THEN E.price ELSE 0 END) AS  '9月'
  , sum(CASE E.doc_month WHEN 10 THEN E.price ELSE 0 END) AS '10月'
  , sum(CASE E.doc_month WHEN 11 THEN E.price ELSE 0 END) AS '11月'
  , sum(CASE E.doc_month WHEN 12 THEN E.price ELSE 0 END) AS '12月'
  , sum(CASE E.doc_month WHEN  1 THEN E.price ELSE 0 END) AS  '1月'
  , sum(CASE E.doc_month WHEN  2 THEN E.price ELSE 0 END) AS  '2月'
  , sum(CASE E.doc_month WHEN  3 THEN E.price ELSE 0 END) AS  '3月'
FROM
  -- 年度計算用
  (SELECT
      customer
    , doc_date
      -- 1月、2月、3月は年度としては前年の4月〜12月と同じ
    , year(doc_date) - CASE WHEN month(doc_date) IN (1, 2, 3) THEN 1 ELSE 0 END AS doc_year
    , month(doc_date) AS doc_month
    , price
  FROM
      Earnings
  ) E
WHERE
    -- 同じ月は対象外
    (month(now()) <> month(E.doc_date)) AND (
      -- 前年度の対象範囲は・・・
      E.doc_date BETWEEN
        -- 前年度の4月1日から
        concat(year(now()) - CASE WHEN month(now()) IN (1, 2, 3) THEN 2 ELSE 1 END, '-4-1') AND
        -- 前年度の今日まで
        date_sub(now(), interval CASE WHEN month(now()) IN (1, 2, 3) THEN 2 ELSE 1 END year) OR
      -- 今年度の範囲も前年度と同様
      E.doc_date BETWEEN
        concat(year(now()) - CASE WHEN month(now()) IN (1, 2, 3) THEN 1 ELSE 0 END, '-4-1') AND
        date_sub(now(), interval CASE WHEN month(now()) IN (1, 2, 3) THEN 1 ELSE 0 END year)
    )
GROUP BY
    E.customer
  , E.doc_year
ORDER BY
    E.customer
  , E.doc_year desc;

こうか？
テストしてないからまだ何か間違ってるかも。

*1:0を出力してもOK

*2:もっと言うと、別に月の頭である必要もないな