データベーススペシャリスト試験 2009年 午後1 問03

1. 問題の整理とゴール

SELECT * 
  FROM 顧客
 WHERE 顧客コード = :顧客コード            -- ① 顧客を特定
   AND 適用開始日 <= CURRENT_DATE        -- ② 開始日は今日以前
   AND ( a OR b )                        -- ③ 終了日の扱い

2. 模範解答のキーワード・論点整理

3. なぜその解答になるのか

3.1 有効期間の定義

“適用終了日が未定の場合は NULL が設定される。”

適用開始日  ≦ 対象日 ≦ 適用終了日（ただし終了日が NULL なら上限なし）

3.2 WHERE 句の各条件

1. 適用開始日 <= CURRENT_DATE … 既に開始している行だけに絞る。（②）
2. 終了日の判定（③）
   • 終了日がまだ来ていない、または今日そのもの → 適用終了日 >= CURRENT_DATE
   • 終了日が未定（NULL） → 適用終了日 IS NULL

3.3 適用終了日 = CURRENT_DATE でもよい理由

4. 受験者が誤りやすいポイント

5. 試験対策まとめ

• 期間テーブル（開始日・終了日型）の基本形

  開始日 <= 対象日
  AND ( 終了日 IS NULL OR 終了日 >= 対象日 )
  

• NULL 判定は IS NULL / IS NOT NULL。= NULL は誤り。
• OR で NULL と比較条件を組み合わせるのが定石。
• 等号と不等号の向きより「どの行が取れるか」を必ず日付例で確認する。
• 先日付（未来開始）のレコードは「開始日 > 今日」なので自然に除外されることを意識する。

小問の論点整理

• 変更連番
  「変更連番は … 1から始まり1ずつ増加する整数が付与される。連番に抜けはない。」（表２より）
  → 前回レコードの次の連番（X.変更連番＋1）と結合する。
• 優遇レベルの変化
  変更連番が連続しているだけではなく，「優遇レベルが変更となる場合」だけを抽出する必要がある。
• 自己結合（エイリアスX,Y）
  同一顧客について，Xが前回の履歴，Yが次回の履歴を表す。
• 適用開始日
  Yの「適用開始日」が，優遇レベル変更後の実際の適用開始日となる。

解答の導出手順と根拠

WHERE X.顧客コード = :顧客コード
  AND X.顧客コード = Y.顧客コード
  AND  c  = Y.変更連番
  AND  d  <> Y.優遇レベル

1. 「変更連番は … 1から始まり1ずつ増加する整数が付与される。連番に抜けはない。」
   → Yのレコードは，Xの次回履歴を指すので，
   c＝ X.変更連番 + 1
2. 「優遇レベルが変更となる場合は … 新たな優遇レベルが設定され，翌月の1日から適用される。」
   → Yレコードの優遇レベルと，Xレコードの優遇レベルが異なることを条件にするので，
   d＝ X.優遇レベル

正解

...
AND X.変更連番 + 1 = Y.変更連番
AND X.優遇レベル <> Y.優遇レベル
...

受験者が誤りやすいポイント

• 「+1」を忘れて，単に X.変更連番 = Y.変更連番 としてしまう
• <> と = を取り違えて「変更のない行」を抽出してしまう
• エイリアスX,Yが逆になり，結合条件を逆転させる

試験対策として覚えておくべき知識

• 変更履歴を持つテーブルでは，自己結合を使って「前回レコード」と「次回レコード」を比較する
• 連番項目（シーケンス）に+1を適用することで隣接行を結びつける手法
• 変更検出には前回値と次回値の不一致（<>）を条件に用いる

解答のキーワードと論点整理

• G部長の指摘①：
  「変更連番が１以外の場合の適用開始日は、変更連番が一つ前の行の適用終了日と連続していなければならない」
• 連続性のチェック方法：
  • 同一顧客の隣接する履歴レコードを探す（同じ顧客コードかつ変更連番が前後）
  • 前の行の終了日の翌日（NEXT_DAY）と次の行の開始日が等しくないレコードを抽出
• SQL中の(e),(f)：
  • (e)：同一顧客を結合する条件
  • (f)：連続性比較の右辺となる開始日

解答

AND X.適用終了日 IS NOT NULL
AND EXISTS (
  SELECT * FROM 顧客 Y
  WHERE 
    X.変更連番 - 1 = Y.変更連番   -- (c)
    AND X.顧客コード = Y.顧客コード  -- (e)
    AND NEXT_DAY(X.適用終了日) <> Y.適用開始日  -- (f)
)

なぜこの解答になるか

1. G部長の指摘①にある制約
   「変更連番が1以外の場合の適用開始日は、変更連番が一つ前の行の適用終了日と連続していなければならない」
   → 連続しない（抜けや重なりがある）行を検出するには 前行の終了日の翌日 と 現在行の開始日 が等しいかをチェックします。
2. 前行との結合条件
   • 「同一顧客」のレコード同士を比較するために、XとYの 顧客コード を結合
   • これが(e)=X.顧客コード
3. 日付連続性の比較
   • NEXT_DAY(X.適用終了日) で前行の終了日の翌日を求め、これと現在行の開始日を比較
   • これが(f)=Y.適用開始日

よくある誤りと注意点

• 「(e)に Y.顧客コード を入れる」
  → 自己結合で凡その習いとしては X.顧客コード = Y.顧客コード の形にするため、X.顧客コード が左辺に来るべきです。
• 日付比較を < や > だけで済ませる
  → 連続していない日を検出するには 翌日が一致しない (<>) 条件が必要です。
• NEXT_DAY の引数や比較対象を取り違える
  → 引数には「前行の終了日(X)」を、比較先には「後行の開始日(Y)」を指定する点を押さえてください。

試験対策として覚えておくべきポイント

1. 自己結合による履歴の前後比較
   履歴テーブルで日付の前後関係や連続性を検証するときは、自己結合（同一テーブルを別名で結ぶ）を用いる。
2. 日付の連続性チェック
   前レコードの終了日＋1日と次レコードの開始日が等しいかを比較することで、抜け・重複を検出できる。
3. NULLチェック
   適用終了日が NULL（現在有効）や「削除日付」である行を対象外・対象内に分ける条件を明確に記述する。
4. エイリアスの使い分け
   自己結合時はテーブル名ではなくエイリアス（X, Y）で列を指定し、どちらの行を比較しているか明示する。

論点整理

• 図３のSQL文は、以下のWHERE句で「現在日付に適用中」の行を抽出している。

  WHERE 顧客コード = :顧客コード
    AND 適用開始日 <= CURRENT_DATE
    AND (
      適用終了日 IS NULL
      OR CURRENT_DATE <= 適用終了日
    )
  

• 指摘事項①「同一顧客の適用期間は連続していなければならない」を守らず，変更連番３と変更連番５で期間が重複している。
• その結果，CURRENT_DATE が 2009-05-25 のときに両方の行が抽出条件を満たし，期待する「１行」ではなく「複数行」が返される（多くの環境でSQLエラーになる）。

解説

1. 【問題文】より
   「変更連番３ の行は 2009-02-01 から 2009-05-31，変更連番５ の行は 2009-05-25 から 2009-05-31」
   と記載されています。
2. 図３の抽出条件を当てはめると，
   • 変更連番３：
     • 適用開始日 (2009-02-01) ≤ CURRENT_DATE (2009-05-25) → ○
     • CURRENT_DATE (2009-05-25) ≤ 適用終了日 (2009-05-31) → ○
   • 変更連番５：
     • 適用開始日 (2009-05-25) ≤ CURRENT_DATE (2009-05-25) → ○
     • CURRENT_DATE (2009-05-25) ≤ 適用終了日 (2009-05-31) → ○
       よって両行が返され，結果行が２行以上になります。

該当部分の再掲（例）

誤りやすいポイント

• 「適用開始日 ≤ CURRENT_DATE ≤ 適用終了日」の条件を単純に書くと，重複期間があると複数行取得になる点。
• 仕様では「１行しか返らない」ことを前提にしているSQL設計が多いため，重複時にはクライアント側でエラー扱い（例：ORA-01422など）になる場合がある。

試験対策ポイント

• 時間帯／期間を表すテーブルでは，「開始日～終了日」の連続性と非重複性を制約（チェック制約やトリガ）で保証する。
• 取得SQLでは，期間条件において期待する結果行数（１行／複数行）を常に確認し，重複時の動作（エラー or 意図的取得）を設計段階で明確化する。
• 「NULLを無期限扱いにする」場合も，重複チェックは必須。

1. 核心キーワード・論点整理

2. 解答に至る論理的説明

(1) 現状（適用順番3 まで）の確認

(2) これから行う 2 つの変更

1. 電話番号の変更依頼（番号を 333-3333 へ）
   − 適用開始日：2009-05-25（依頼者が指定）
   − 優遇レベルは まだ 2 のまま
2. 優遇レベルを 3 へ先日付で変更
   − 適用開始日：2009-06-01
   − 電話番号は同じ 333-3333 を維持

(3) 行 4・行 5 の作成

• 2/1〜5/24（順番3），5/25〜5/31（順番4），6/1〜NULL（順番5）と日付が切れ目なく連続
• 電話番号・優遇レベルの履歴も正しく保持

(4) 模範解答との対応

3. 受験者が誤りやすいポイント

4. 試験対策ポイントまとめ

模範解答の核心となるキーワード・論点整理

• 同じ適用開始日に異なる変更受付日の顧客属性情報が存在する場合
• 先日付で設定した適用開始日よりも前に顧客コードを削除する場合

なぜこの解答になるのかの論理的説明

追加された「変更受付日」列の意味

１．同じ適用開始日に異なる変更受付日が存在する場合

• 「適用開始日」は 変更した属性値が適用される開始日
• 「変更受付日」は 変更依頼を受け付けた実際の日付

• 変更は先日付でまとめて適用開始日を設定（例えば月初）。
• しかし複数の変更依頼は異なる日（変更受付日）に受け付けられている。

２．先日付で設定した適用開始日より前に顧客コードを削除する場合

顧客コードが削除されたときは、当該顧客行は削除されず、削除日付が設定される。

• 先日付（過去の日付）で適用開始日を設定した場合、たとえば「2009-06-01」から適用開始日を設定し、
• その適用開始日よりも前（例えば2009年5月中）に顧客コードが削除されると、適切に履歴を表現できなくなります。

受験者が誤りやすいポイント・ひっかけ解説

• 「変更受付日」と「適用開始日」は同じものだと思い込む誤り
  問題では、変更適用開始日は「変更履歴の適用開始日」という意味であり、変更依頼が受け付けられた日付とは異なります。これを混同すると、履歴管理の要件を正しく理解できません。
• 先日付変更に伴う履歴表現の複雑さを見落とす
  先日付変更により、適用開始日は過去の日付になる場合があります。これが「変更受付日」と異なり、履歴が特定の1行で一意に表せない状況の原因です。
• 「顧客コードの削除」との関連を理解しない点
  顧客コードが削除された場合にも履歴行を残す設計ですが、これが変更受付日の記録漏れに影響することに気づかない受験者も多いです。

試験対策のポイント・覚えておくべき知識

1. 履歴管理では「適用開始日」「適用終了日」と「変更受付日」の役割の違いを正確に理解する
   • 適用開始日…属性が有効となる日付
   • 変更受付日…実際に変更依頼があった日付
2. 履歴テーブルのキー構成と一意性の関係を押さえる
   • 変更連番は連続・一意となるが、同じ適用開始日で複数の変更受付日を管理できない設計上の限界に注意。
3. 先日付変更処理が履歴管理に与える影響を理解する
   • 過去の日付での適用開始日は、履歴の一貫性チェックや削除処理と絡んで矛盾を生じやすい。
4. 顧客コード削除時の履歴保持ルールを理解する
   • 削除は行の物理削除ではなく履歴行の適用終了日設定で管理し、追加の日時管理が必要になる場合があること。
5. 履歴テーブルでの「漏れなく記録したい」要望は仕様的に困難なケースが存在することを意識する
   物理的には追えない情報がある場合、設計の再検討や別テーブルの検討が必要になる。

模範解答のキーワードと論点整理

• 「顧客テーブルの変更履歴」への結合条件と，集計期間の重なり判定
• WHERE句中の

  ISODATE('2009-04-01') <= z.適用開始日
  AND (z.適用終了日 <= ISODATE('2009-04-30') OR z.適用終了日 IS NULL)
  

  が**「適用開始日が月初以降」かつ「終了日が月末以前または未定」**という条件になっている点
• 結果として
  • 該当月に「適用開始日」が１件だけ存在 → 正しい件数
  • ２件以上存在 → 預入明細行が重複して集計され，多くなる
  • ０件 → 結合できず，0円になってしまう

なぜその解答になるのか

1. 図５で本来必要だった「履歴レコードと集計期間との**,重なり(overlap)*判定」が，
   ―――適用開始日 ≤ 集計終了日 AND (適用終了日 ≥ 集計開始日 OR 適用終了日 IS NULL)
   ではなく，

   ISODATE('2009-04-01') <= z.適用開始日
   AND (z.適用終了日 <= ISODATE('2009-04-30') OR z.適用終了日 IS NULL)
   

   と開始日が集計期間の開始日以降という逆向きの条件になっているため，
   • 集計期間中に開始日を持つ履歴だけを取り出す
   • 開始日が「4月1日以前」の履歴は無視
   • 開始日が「4月30日以降」の履歴も無視
2. 結果として，顧客ごとに「適用開始日」が4月1日～4月30日の間に
   • ちょうど1件　→ 取引明細行は1回だけヒットし，正しい合計額
   • 2件以上　　 → 同じ取引明細が複数の履歴行に結合，二重計上などで「多くなる」
   • 0件　　　　 → そもそも結合できず，0円として扱われる
3. グラフで示された A～I の適用期間線分を見ると，
   • A，D，F，G は「4月中に適用開始日を持つ履歴がちょうど1件」
   • B，E は「4月中に適用開始日を持つ履歴が2件以上」
   • C，H，I は「4月中に適用開始日を持つ履歴が0件」
   したがって，上記の分類に対応します。

受験者が誤りやすいポイント

1. 日付の重なり判定の定石を逆にしてしまう
   • 重なり条件は「開始日 ≤ 期間終了日 ＆ （終了日 ≥ 期間開始日 OR 終了日 IS NULL）」
   • 本試験の誤り例では「期間開始日 ≤ 開始日 …」と逆向き
2. 履歴テーブルとの結合で，明細行の重複・欠落が起きることに注意
   • 結合条件がゆるいほど多重集計
   • 結合条件がきついほど結合漏れ
3. NULLを含む日付比較
   • 「適用終了日 IS NULL」を入れ忘れると，継続中履歴をすべて除外してしまう

試験対策として覚えておくべきポイント

• 期間（期間）の重なりoverlap判定は，必ず

  期間開始 ≤ 履歴終了（またはNULLを含む継続）
  AND
  履歴開始 ≤ 期間終了
  

  の形で記述する。
• SQLで履歴テーブルを扱う際は，履歴行ごとに合致するかどうかが多重計上・欠落の原因に
  → 必ず集計前に「該当履歴行が 1件だけ」になるよう調整（ビュー化，DISTINCTなど）を検討する。
• NULL比較の扱い
  • 「終了日が未定」＝継続中　は IS NULL を必ず条件に含める。
• 問題文中の誤ったSQLやワークフローを疑ってかかるクセをつけること。
  特に「日付の向き」「NULLの扱い」「JOIN句の多重度」に着目しましょう。