裏MySQL MySQL のユーザー変数で支払いの繰越処理 1

mysql

ほぼ1年ぶりにMySQLをまともに使ったので、メモとして書いておきます。

サンプルはAmazonのアフィリエイト報酬などに良くある最低支払い金額5000円未満の場合は翌月に繰り越されるという条件で、その月の収益から、その月の支払い金額、その月の繰越金額を表示する処理をMySQLのSQLで書く方法。

このSQLはわりとめんどくさい。MySQLのユーザー変数を用いて解決する。MySQLのユーザー変数の実行の順番が重要なので、カラムの並びは変えてはならない。あと上から順に処理していく為、limitも、order byも使えない、limitは使えなくもないが、その場合は1つ前の行の繰越金額を取得して、SQL冒頭のSET @carry_over:=1行前の繰越金額;としてあげる必要がある。order byは全体を処理した後に並べ替える。

SET @carry_over:=0;
SELECT
  month_year,
  revenue,
  IF(revenue+@carry_over>=5000,
     revenue+@carry_over+(@carry_over:=0),
     (@carry_over:=@carry_over+revenue)*0) payment,
  @carry_over carry_over 
FROM (
  SELECT "2008/04" month_year,3000 revenue UNION ALL
  SELECT "2008/05"           ,1000 UNION ALL
  SELECT "2008/06"           ,2000 UNION ALL
  SELECT "2008/07"           ,7000 UNION ALL
  SELECT "2008/08"           ,1000
) t ORDER BY month_year ASC;
month_year revenue payment carry_over
2008/04 3000 0 3000
2008/05 1000 0 4000
2008/06 2000 6000 0
2008/07 7000 7000 0
2008/08 1000 0 1000

関連

他のデータベースだとどう実現するのかの参考リンク...。前後の行データをとってくる場合は分析関数を使うのが定番だが、何個前のデータが必要か分からないこの場合は駄目かもしれない。ストアドプロシージャでカーソルでまわすのが妥当か。