【MySQL】前のレコードからデータ取得（OFFSET句）

先日以下の記事にてMySQLを駆使して日本株投資成績のデータベース化を行いました。

米国株にかける

munokuno.com

技術が足りず連騰、続落の連続数の入力部分は手入力にするとしていましたが、スクリプトで実行できるようになりました。その方法を備忘録として記事にしました。

なにが障壁となっていたか

連騰、続落の連続数を入力するためには前日の連続数のデータを取得する必要があります。
この前日のレコードからデータを取得する処理がMySQLを勉強したての私には実現できませんでした。Excelなら一瞬なのに！

前のレコードのデータの取得に使うコマンド

前のレコードのデータは以下のコマンドで選択できます。

SELECT カラム名 FROM テーブル名 ORDER BY カラム名 DESC LIMIT 取得件数 OFFSET 開始位置;

LIMITにより取得件数、OFFSETにより開始位置が指定できます。
私の例では、前日のcontinuous_daysのデータを選択する場合は、

SELECT continuous_days FROM test ORDER BY post_name DESC LIMIT 1 OFFSET 1;

となります。

実戦

前日のcontinuous_daysのデータの選択方法が分かりましたので、やりたいことを実現するためのコマンドを作っていきます。

実現したい内容

当日の損益金額(day_change)の数値と前日のcontinuous_daysの値に応じて以下のように当日のcontinuous_daysに入力します。

• day_changeがプラスの場合
  • 前日のcontinuous_daysがプラス　 → 「前日のcontinuous_days + 1」を入力
  • 前日のcontinuous_daysがマイナス → 「1」を入力
• day_changeがマイナスの場合
  • 前日のcontinuous_daysがプラス　 → 「-1」を入力
  • 前日のcontinuous_daysがマイナス → 「前日のcontinuous_days – 1」を入力
• day_changeが0の場合
  「前日のcontinuous_days」をそのまま入力

作成したMySQLコマンド

SELECT @prev_continuous := continuous_days FROM test
  ORDER BY post_name DESC LIMIT 1 OFFSET 1; #①
SELECT @cur_day_change := day_change FROM test
  ORDER BY post_name DESC LIMIT 1; #②

UPDATE test SET
  continuous_days =
    CASE #③
      WHEN @cur_day_change > 0 THEN #条件1
        #入れ子始まり
	CASE
	  WHEN @prev_continuous > 0 THEN @prev_continuous + 1
	  ELSE 1
	END
        #入れ子終わり
      WHEN @cur_day_change < 0 THEN #条件2
        #入れ子始まり
	CASE
	  WHEN @prev_continuous < 0 THEN @prev_continuous - 1
	  ELSE -1
	END
        #入れ子終わり
      ELSE @prev_continuous #条件3
    END
WHERE post_content IS NOT null; #④

<補足説明>
■変数に数値を格納(①②)
条件式や入力に使う数値を変数に格納しています。
　@prev_continuous：前日のcontinuous_daysの数値
　@cur_day_change：当日の損益金額(day_change)の数値

■CASE文で条件分岐(③)
自動的に整形されてしまってインデントが取れていて見にくいですが、入れ子構造となっています。

■post_contentがNULLでない場合のみ実行(④)
本データベースでは処理済みのものはpost_contentをNULLにしています。
「WHERE post_content IS NOT null」とすることで処理が終わっていないレコードのみに処理を行うように制限しています。

実行結果

実行結果は以下となり、最新のレコードのcontinuous_daysに適切な数値が入力されていることがわかります。