hrendoh's tech memo

次の行の開始日から終了日を計算するSQL

時価で変動する商品の値段のように、期間で管理されるデータ管理する場合のテーブルの持ち方とSQLについての考察です。

例えば、以下のように通貨変換レートが適用されるようなケースがあるとします。

sql-currency-enable-period

各期間には適用開始日と終了日がありますが、現在進行系の期間は終了日が無いですし、次の期間の開始日から前の期間の終了日は計算できるので、テーブルには開始日のみ管理すれば良さそうです。

開始日のみを管理するメリットとしては、余分な値「終了日」を保持しなくてよいのでカラムが減らせることと、終了日をプログラム(プロシージャ)やトリガーなどでセットしないのでバグりにくいことなどがあります。

この場合に、次の期間のレコードから計算した終了日を含むレコードを取得するSQLを作成してみます。

サンプルのテーブル

CREATE TABLE `currency_rates` (
  `from_currency_code` char(3) NOT NULL,
  `to_currency_code` char(3) NOT NULL,
  `enabled_at` date NOT NULL,
  `rate` decimal(12,2) NOT NULL,
  PRIMARY KEY (`from_currency_code`,`to_currency_code`,`enabled_at`)
);

このテーブルに以下のようなデータを用意します

INSERT INTO `currency_rates` (`from_currency_code`, `to_currency_code`, `enabled_at`, `rate`) VALUES
('USD', 'JPY', '2015-10-01', 119.89),
('USD', 'JPY', '2015-10-08', 120.02),
('USD', 'JPY', '2015-10-20', 119.42),
('USD', 'JPY', '2015-10-24', 118.98);

以下のようにセルフjOINすると終了日を取得できます

SELECT T1.`from_currency_code`, T1.`to_currency_code`, T1.`enabled_at`, MIN(T2.`enabled_at` - INTERVAL 1 DAY) AS end_enabled_at, T1.`rate`
FROM currency_rates T1
LEFT OUTER JOIN currency_rates T2
ON T1.`enabled_at` < T2.`enabled_at`
GROUP BY T1.`from_currency_code`, T1.`to_currency_code`, T1.`enabled_at`, T1.`rate`

結果は以下のように終了日が計算されたレコードを取得できています

+--------------------+------------------+------------+----------------+--------+
| from_currency_code | to_currency_code | enabled_at | end_enabled_at | rate   |
+--------------------+------------------+------------+----------------+--------+
| USD                | JPY              | 2015-10-01 | 2015-10-07     | 119.89 |
| USD                | JPY              | 2015-10-08 | 2015-10-19     | 120.02 |
| USD                | JPY              | 2015-10-20 | 2015-10-23     | 119.42 |
| USD                | JPY              | 2015-10-24 | NULL           | 118.98 |
+--------------------+------------------+------------+----------------+--------+

参考

StackOverflow: Creating new date field dynamically from next row
第6回　SQL流行間比較（2）　直近，直近の1つ前，そのまた1つ前…
MySQL5 – n分前、n時間前、n日後の日付を取得する
Tableau Doctor: 1 Column, 2 Dates? Use Custom SQL

hrendoh