とまと あんらいぷ…

エンジニアの活動記録とかつぶやきとか

GitHub
スポンサードリンク

SQLにおける期間重複チェックの考え方


データベースに登録された一定期間を表すデータに
指定した日付がかぶっているかを確認するSQLを作成する要件は意外と多いです。

実際の業務、小売業のデータマートをサンプルとして考えてみます。
とまとあんらいぷスーパーでは、
商品データを登録する時
商品マスタに
その商品を販売する期間と金額を設定しています。

    商品マスタ
  1. 商品ID
  2. 適用開始日
  3. 適用終了日
  4. 商品名
  5. 金額

商品マスタの金額とは別に、セール(特売)期間テーブルが存在し
セール期間中の金額を設定できます。

    特売期間テーブル
  1. 商品ID
  2. 適用開始日
  3. 適用終了日
  4. 金額

データ登録時の制約として、
商品マスタには同一商品IDの適用開始日が
既に存在する適用開始日~適用終了日の間になるような指定はできません。

【商品マスタ】

商品ID 適用開始日 適用終了日 商品名 金額
登録OK 0001 2013-03-01 2013-08-31 とまとM 258
登録時にエラー 0001 2013-04-01 2013-07-31 とまとM 218

特売期間テーブルに登録したデータは
商品マスタの適用開始日~終了日の期間と重複する場合
特売期間テーブルの金額を優先します。

【特売期間テーブル】

商品ID 適用開始日 適用終了日 金額
商品マスタより
優先される
0001 2013-04-01 2013-07-31 198

さて、特売期間の登録時に、商品マスタのように
特売期間の重複が発生するエラーチェックを行なっていなかった事が発覚しました。

【特売期間テーブル】

商品ID 適用開始日 適用終了日 金額
商品マスタより
優先される
0001 2013-04-01 2013-07-31 198
特売日がかぶってる 0001 2013-04-15 2013-06-31 158


特売期間とはいえ、特売金額が同じ日に複数あるのは問題なので
特売期間テーブルも商品マスタと同様に
同一商品IDによる期間重複制限を行います。

その前に、既に登録している特売期間のうち
特売期間がかぶっているデータの洗い出しを行わなければいけません。

というようなシチュエーションです。
期間重複しているデータを洗い出す方法を考えてみます。

テストデータの考え方

期間の重複が発生するデータパターンを洗い出します。
通常の販売期間と特売期間がかぶっているパターンはケース6まで
念のためテストデータとしてケース9まで用意します。


ケース1
特売期間が通常販売期間に内包
ケース1

ケース2
特売開始日が通常販売期間の開始日をまたぐ
ケース2

ケース3
特売終了日が通常販売期間の終了日をまたぐ
ケース3

ケース4
特売期間が通常販売期間より長い
ケース4

ケース5
特売日(当日のみ)が通常販売期間の開始日と一致
ケース5
ケース6
特売日(当日のみ)が通常販売期間の終了日と一致
ケース6

ケース7
特売期間と通常販売期間が完全に一致
ケース7

ケース8
特売期間の開始日が通常販売期間の開始日と一致し、期間が内包されている
ケース8

ケース9
特売期間の終了日が通常販売期間の終了日と一致し、期間が内包されている
ケース9
(うう・・・画像が切れてる・・・)
逆に登録がOKとなるケースは以下の2ケースです。


OKケース1
特売期間が通常販売期間の前
OKケース1

OKケース2
特売期間が通常販売期間の後
OKケース2

法則性の抽出


机上の考えをアプリケーションで実現する場合は
法則性をプログラムコードに落としこむ必要があります。

期間重複のケース1~9をSQLのクエリで実現するために
法則の確認を行います。

少しトリッキーなのですが、特売期間の開始日と終了日に焦点を当てると
期間重複が発生するケースでは

  1. 特売開始日が通常販売期間の終了日と同じか過去日付
  2. 特売終了日が通常販売期間の開始日と同じか未来日付

必ずこの2つの条件に当てはまる事が分かります。

重複データの確認
では早速テストデータを作成し、SQLに落とし込みます。

テストデータ

ケース1~9を抽出します。
日付とは別に商品IDが同一のものという条件を指定して
テーブルの結合を行います。
データ重複SQL1
ケース1~9が取得できている事が確認できたので
期間重複を判定するSQLが正しい事がわかりました。

今回の要件では、通常の販売期間と特売期間は1レコードのみであれば重複することが許されています。
必要なのは特売期間が重複しているデータなので、別テーブルではなく
自己結合を行なって重複データを抽出する必要があります。

今回のテストデータではケース10も11も期間の重複してしまっているので
データを削除して確認してみます。

テストデータ2

同一テーブルとの比較を行う場合、必然的に同一主キーとの比較が発生するので
同レコードとの比較は不要です。
今回はケース列を主キーとして考えているのでWHERE句に自レコードを除く一文を追加しています。
自己結合で期間の重複検出
DISTINCTしている理由は、1レコード毎に比較を行なって対象となるデータが増幅されるので
同一レコードを除外して表示しています。

商品マスタへの登録時のエラーチェックは、プログラム側から渡された
開始日と~終了日を変数に指定して比較します。

登録時のエラーチェック
同じロジックを特売期間テーブルの登録時に実装すれば
要件が満たされます。

特売期間の値段を抽出する方法は
販売日をプログラム側から受け取り、商品IDと日付のBETWEEN句などで特売期間を検索し
値がNULLであれば通常販売期間の金額を返す事で可能ですが

プログラム側でまずは特売期間テーブルを参照して値が存在すれば
次のクエリ(通常販売期間)は流さないようにする方がレスポンスはよいと思います。
▼この記事を読んだ方は、こんな記事も読んでいます。▼

スポンサードリンク

テーマ:プログラミング - ジャンル:コンピュータ

コメント

コメントの投稿


管理者にだけ表示を許可する

トラックバック

トラックバック URL
http://dalmore.blog7.fc2.com/tb.php/67-fe2bd1e3
この記事にトラックバックする(FC2ブログユーザー)

FC2Ad