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

模範解答

解説

解答の論理構成

1. 問題の要求を確認
   • 【問題文】「圃場ごと農事日付ごとに1日の平均温度と行数を調べる。」
2. 必要な列と集約関数を決定
   • 1日の平均温度 → AVG(分平均温度)
   • 行数 → COUNT(*)
3. SELECT 句の構成
   • 答えるべき列は「圃場ID, 農事日付」と平均温度（集約関数）と行数。
   • よって (a) = 圃場ID, 農事日付, AVG(分平均温度)
4. GROUP BY 句の構成
   • 平均と行数を求める単位は「圃場ID, 農事日付」。
   • 集約関数以外で SELECT に書いた列を列挙する。
   • よって (b) = 圃場ID, 農事日付

誤りやすいポイント

• AVG の対象列を 分平均温度 ではなく 日平均温度 と書いてしまう。
• GROUP BY に AVG(分平均温度) を入れてしまい文法エラーになる。
• 観測日付 をグループ化対象に含めてしまい，日単位集計にならない。

FAQ

模範解答

解説

解答の論理構成

1. センサーは「毎分」計測し、分析システムはその 1 分単位データを “観測” テーブルに登録します。
2. SQL1 は GROUP BY で (a) (圃場ID, 農事日付) をまとめ、COUNT(*) で 1 日分のレコード数を算出します。
3. 【問題文】には
   「農事日付…1日の区切りを，圃場の日出時刻から翌日の日出時刻の1分前までとする」とあります。
4. 日出時刻は同じ圃場でも経度・緯度・季節で変わる（【業務の概要】1.(3)）ため、農事日付 1 日の長さ（分数）は
   • 夏：24 時間より長い
   • 冬：24 時間より短い
5. よって、1 日当たりの計測回数（行数）は 1,440 分を基準に増減するため、「1,440 行とは限らない」が正答となります。

誤りやすいポイント

• 標準日付＝24 時間と早合点し、農事日付の可変性を見落とす。
• 行数の不足を通信障害などの「不具合」で説明してしまう（問題文で除外条件と明記）。
• 日出時刻が固定だと思い込み、季節変動の影響を忘れる。

FAQ

模範解答

解説

解答の論理構成

1. SQL2 の計算仕様を確認
   【問題文】「AVG(日平均気温度) OVER ( ORDER BY 農事日付 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW )」
   → 直前２行と現在行、合計３行の算術平均を算出。
2. 元データを列挙
   図２より
   2023-02-01: 9.0 / 02: 14.0 / 03: 10.0 / 04: 12.0 / 05: 20.0 / 06: 10.0 / 07: 15.0 / 08: 14.0 / 09: 19.0 / 10: 18.0
3. 各行の移動平均
   • 2023-02-03 → (9.0+14.0+10.0)/3 = 11.0（図３に提示済み）
   • 2023-02-04 → (14.0+10.0+12.0)/3 = 12.0（提示済み）
   • 2023-02-05 → (10.0+12.0+20.0)/3 = 14.0 ⇒ (c)
   • 2023-02-06 → (12.0+20.0+10.0)/3 = 14.0（提示済み）
   • 2023-02-07 → (20.0+10.0+15.0)/3 = 15.0 ⇒ (d)
   • 2023-02-08 → (10.0+15.0+14.0)/3 = 13.0（提示済み）
   • 2023-02-09 → (15.0+14.0+19.0)/3 = 16.0 ⇒ (e)
4. よって (c) 14.0、(d) 15.0、(e) 16.0 が確定。

誤りやすいポイント

• PRECEDING を「日前」ではなく「行数」基準と誤読する。
• ３日平均なのに“列数が２＋１＝３”であることを忘れ、２日平均で計算してしまう。
• 先頭行・２行目も３行平均に含めてしまい図３との対応が取れなくなる。

FAQ

模範解答

解説

解答の論理構成

1. 目的確認
   【問題文】「指定した農事日付の期間について，圃場ごと農事日付ごとの積算温度を調べる。」
   ⇒ “圃場ごと”＝PARTITION BY、 “農事日付ごとに累積”＝ORDER BY 農事日付 が必要。
2. 加算対象（f）
   積算温度は【業務の概要】「1日の平均温度をある期間にわたって合計したもの」。
   SQL1 の CTE R が生成する列は 日平均温度 なので、(f)＝日平均温度。
3. 区分列（g）
   “圃場ごと” の累積を別々に計算させるには PARTITION BY に 圃場ID を指定。
4. 並べ替え列（h）
   日付順に累積するため ORDER BY 農事日付。
   厳密にソート安定性を考え ORDER BY 圃場ID, 農事日付 も正しく、設問が両方を解と認めている。
5. 以上より
   f：日平均温度
   g：圃場ID
   h：農事日付（又は 圃場ID, 農事日付）

誤りやすいポイント

• 日平均気温 と 日平均温度 の表記ゆれをそのまま書いて減点。原文では SQL1 列名が 日平均温度。
• PARTITION BY 農事日付 としてしまい、圃場をまたいで累積してしまう。
• 累積窓の範囲句を確認せず ROWS BETWEEN 2 PRECEDING … と混同する。積算は “UNBOUNDED PRECEDING” が必須。

FAQ

模範解答

解説

解答の論理構成

1. 本文の仕様確認
   • ハッシュ区分は「区分キーの値に基づき, RDBMS が生成するハッシュ値によって行を一定数の区分に分配する。 区分数を変更する場合, 全行を再分配する。」
   • レンジ区分は「区分キーの値の範囲が既存の区分と重複しなければ区分を追加でき, 任意の区分を切り離すこともできる。」
2. 運用要件
   • 「新たな圃場を追加する都度、当該圃場に対してそのときの年度の区分を1個追加する。」
   • 年度が進むたびに５年以上前の区分を削除（切離し）する。
3. 問題点の導出
   ①区分を毎年・毎圃場追加→ハッシュ区分ではそのたびに「全行を再分配」⇒実運用で長時間停止。
   ②削除時にも同様の再分配が必要でストレージ／CPU負荷が高い。
   ③圃場単位のデータが区分内で混在し，物理削除しにくい。
4. よって「ハッシュ区分は採用しない」→35字以内の理由文につながる。

誤りやすいポイント

• 「全行再分配」は区分追加・削除の両方で起こることを忘れる。
• レンジ区分でも行が均等にばらけると誤解し，メリットを過小評価する。
• 圃場単位の管理しやすさ（混在しない）よりもアクセス性能のみで判断する。

FAQ

模範解答

解説

解答の論理構成

1. 区分化後の表領域条件を確認
   • 【問題文 表6】ページ当たりの行数（ページ長）「16行（16,000バイト）」
   • 同じ表で読込み行数「144,000行」
2. 必要ページ数を計算
   • 式：必要ページ数 ＝ 読込み行数 ÷ 1ページ当たり行数
   • 代入：144,000 ÷ 16 ＝ 9,000
3. 端数チェック
   • 144,000は16の倍数のため端数なし。
4. 結果として（ア）に入る値は 9,000ページ

誤りやすいポイント

• 区分化前の数値（4行／ページ）と混同する。
• 端数が出た場合の切上げ有無を考えずにそのまま整数部分だけを書く。
• ページ当たり行数ではなくページ長の「16,000バイト」で計算を始めてしまう。

FAQ

模範解答

解説

解答の論理構成

1. ページ構成とクラスタリング
   【問題文】には「ページ当たり行数：4行／ページ」とあります。また主索引列の定義順は「観測日付，観測時分，圃場 ID」です。主索引でクラスタリングされるため，同一の (観測日付, 観測時分) が先に並び，その下位に 1,000 個の圃場 ID が続きます。
2. 行挿入の挙動
   行は「表領域の最後のページに行を格納する」と明記されています。1 分ごとに 1,000 行（1,000 圃場）が到着するので，最後のページの 4 行が埋まるたびに新しいページが割り当てられます。
3. ページ内の圃場分布
   1 ページに 4 行しか入らず，圃場 ID はクラスタリング順で 4 つとも異なるため，同じ圃場 ID の行は別ページに散在します。
4. 副次索引走査時の I/O
   副次索引「圃場 ID」から取得するレコードは，ページ毎に 1 行しか含まれないので，行を 1 件読むごとに必ずデータページを 1 枚読み込むことになります。

誤りやすいポイント

• 「4 行／ページだから 4 行読める」と思い込み，同一圃場の行も 1 ページに 4 行入ると誤解する
• 挿入順仕様を見落とし，ランダム分散されると考えてしまう
• 主索引と副次索引の違い，特にクラスタリング効果を軽視する

FAQ

模範解答

解説

解答の論理構成

1. 農事日付の範囲
   • 引用：「日出時刻から翌日の日出時刻の1分前までとする日付を, 農事日付という。」
   • したがって12月31日の日出～１月１日の日出直前が同じ農事日付。
2. 年末処理で削除対象とする「５年以上前」は農事日付単位
   • 標準日付で区切ると，12月31日24時～１月１日の日出までのデータが欠落する。
3. 区分化後はパーティション単位で切り離し・削除
   • 区切りを誤ると不要データが残る／必要データを失うリスク。
4. 以上より，安全に前年パーティションを切り離す最終時刻は「元旦の日出時刻」となる。

誤りやすいポイント

• 0時＝日付境界と思い込んで農事日付の延長分を見落とす。
• 圃場ごとに日出時刻が異なる事実を忘れ，一律タイミングで削除してしまう。
• 「区分化＝自動で古いデータが消える」と誤解し，切り離し手順を怠る。

FAQ

模範解答

解説

解答の論理構成

1. 【問題文】の制約
   • 外部キー制約は「ON DELETE CASCADE」。よって“圃場カレンダ”の行を削除すれば“観測”の該当行も自動削除される。
2. 区分化前（非区分）
   • 手順2でまず①“圃場カレンダ” から古い行を削除 → “観測”の古い行も連鎖削除。
   • 手順3はすでに記載「“圃場カレンダー” を再編成」。
   • 連鎖削除により“観測”表領域に空きが生じるので④“観測” を再編成してスペース回収。
3. 区分化後（レンジ区分）
   • 区分化では【区分を切り離すこともできる】という仕様。
   • 古いデータは年度単位の区分ごと存在するため、手順3で⑤“観測” から古い区分を切り離すのが最速。ログも最小。
   • “圃場カレンダ”にはまだ古い行が残るので、手順4で①“圃場カレンダ” から古い行を削除。
   • 手順5で②“圃場カレンダ” を再編成し空領域を回収。

誤りやすいポイント

• 「③“観測”から古い行を削除する」を選択すると、行数が膨大で処理時間・ログ量ともに増大する。外部キーの連鎖削除を見落とさない。
• 区分化後も“圃場カレンダ”は区分化していない点を混同し、再編成②を忘れがち。
• 切り離し⑤と再編成④/②の順序を逆にすると、不要な領域を含むまま再編成してしまう。

FAQ