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

キーワード・論点整理

• 集約関数と GROUP BY
  SQL1 では「日平均温度」と「行数」を求めるために、AVG(分平均温度) と COUNT(*) を使い、グルーピングのキーとして 圃場ID，農事日付 を指定している。
• WITH句の列エイリアス

  WITH R (圃場ID, 農事日付, 日平均温度, 行数) AS (
    SELECT ＜a＞, COUNT(*) FROM 観測 GROUP BY ＜b＞
  )
  

  の <a> に SELECT 句の出力カラム、<b> に GROUP BY のキーを対応させる。

解答のポイント

(a) に入るべき字句

(圃場ID, 農事日付, 日平均温度, 行数)

• 圃場ID
• 農事日付
• 日平均温度を求める集約関数：AVG(分平均温度)

<a>：圃場ID, 農事日付, AVG(分平均温度)

(b) に入るべき字句

<b>：圃場ID, 農事日付

なぜこの解答になるか

1. 問題文で「圃場ごと農事日付ごとに1日の平均温度と行数を調べる」とある。
2. 「1日の平均温度」は各分平均温度の平均なので、AVG(分平均温度) を使う（問題文〔2〕(3),(4)参照）。
3. 「行数」は単に**COUNT(*)** で得る。
4. 集約関数を用いる場合、GROUP BY に集約対象外の列（圃場ID, 農事日付）を指定しなければならない（SQLの基本ルール）。

誤りやすいポイント

• GROUP BY に誤って 日平均温度 や COUNT(*) を入れる
  → 集約関数の結果は GROUP BY できない。
• AVG(分平均温度) を忘れて 日平均温度 とだけ書く
  → 「1日の平均温度」を求めるためには必ず AVG が必要。
• WITH句の列リストと SELECT 列の順序をそろえない
  → 順序が合わないとエラーや異なる結果を招く。

試験対策ポイント

• 「GROUP BY」に列を指定するルール
  → SELECT 句で集約関数を使わない列を列挙する。
• 集約関数の使い分け
  → 合計：SUM、平均：AVG、件数：COUNT、最大値・最小値：MAX/MIN
• WITH句（Common Table Expression）では、列リストと SELECT 列の順序・数を一致させること

キーワード・論点整理

• 「農事日付」
  表1より、

  「農事日付」は「日出時刻から翌日の日出時刻の1分前までとする日付」

• SQL1 の COUNT(*) は、
  観測テーブルの各「農事日付」期間中のレコード件数を表す
• 日出時刻は日々・圃場ごとに異なるため、期間の分数（＝行数）は変動する

解答の理由

「日出時刻から翌日の日出時刻の1分前までとする日付」（表1）
と定義され、日出時刻は経度・緯度・標高などで日々変化します。
したがって「農事日付」の期間の分数は必ずしも1,440分（24時間）ではなく、
COUNT(*) が1,440行とは限りません。

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

• 「1日＝24時間＝1,440分」と固定で考え、標準日付の分数と混同しやすい
• 問われているのは「農事日付」ベースの集計である点を見落としがち

試験対策

• 農事日付の定義を正確に押さえる
• 集計対象の日付が「標準日付」か「農事日付」かを問題文で確認する
• 日々変動する基準（ここでは日出時刻）が集計範囲に影響を与える点に注意する

問題の論点整理

模範解答（c～e の値）

なぜこの値になるか

1. 「ROWS BETWEEN 2 PRECEDING AND CURRENT ROW」は、
   • 当該行(今日)
   • 直前１行(昨日)
   • 直前２行(一昨日)
     の計 3 行を範囲とする。
2. 図2 の「日平均温度」（小数第1位まで表示）から、対象日付を抽出：

   農事日付	日平均温度
   2023-02-01	9.0
   2023-02-02	14.0
   2023-02-03	10.0
   2023-02-04	12.0
   2023-02-05	20.0
   2023-02-06	10.0
   2023-02-07	15.0
   2023-02-08	14.0
   2023-02-09	19.0
   2023-02-10	18.0

3. c（2023-02-05）の場合：一昨日～当日＝02-03～02-05 の値
   (10.0 + 12.0 + 20.0) ÷ 3 = 14.0
4. d（2023-02-07）の場合：02-05～02-07 の値
   (20.0 + 10.0 + 15.0) ÷ 3 = 15.0
5. e（2023-02-09）の場合：02-07～02-09 の値
   (15.0 + 14.0 + 19.0) ÷ 3 = 16.0

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

• 「2 PRECEDING」は“直前２行まで”を指し、「3 行分」ではないと誤解しがち
• 範囲に行数指定がないときの動作（UNBOUNDED PRECEDINGなど）と混同する
• 平均の分母はデータ件数（ここでは常に 3）である点
• 小数第1位まで丸める点を忘れると表示と一致しない

試験対策ポイント

• ウィンドウ関数の OVER(...) と ROWS BETWEEN の意味を正確に理解する
• 移動平均や累積計算では、範囲（PARTITION, ORDER BY, ROWS）を意識する
• 平均値の分母、NULL 行の扱い、表示フォーマット（小数位）を押さえる
• 図表から数値を読み取る演習を繰り返し、手計算でもすばやく答えを導けるようにする

模範解答のキーワード整理

なぜその解答になるのか

1. 「日平均温度」＝f
   SQL3 の SUM 部分は，積算温度を“日平均温度を合計”する処理です。問題文でも

   「積算温度は，1日の平均温度をある期間にわたって合計したもの」
   とあり，“合計する対象”としてふさわしいのは「日平均温度」です。

2. 「圃場ID」＝g
   積算温度は圃場ごとに求めるため，ウィンドウ関数では同じ圃場内での累計を計算しなければなりません。
   SQL3 の OVER 句にある

   PARTITION BY □g□
   

   の指定列には「圃場ID」を入れます。
3. 「農事日付」または「圃場ID, 農事日付」＝h
   ウィンドウ関数の ORDER BY 句は「累積の並び順」を指定します。問題の SQL3 では

   OVER(... ORDER BY □h□ ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
   

   とあり，期間を日付単位で累積したいので「農事日付」を指定します。
   • PARTITION BY を使うなら，ORDER BY は「農事日付」だけでよい
   • PARTITION BY を使わないなら，ORDER BY で「圃場ID, 農事日付」とすることで同様に圃場単位・日付順の累計を実現できます。

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

• ウィンドウ関数の PARTITION BY と ORDER BY の役割を取り違える
  「同じ圃場内で計算を区切る」のは PARTITION BY，「その中を日付順に並べて累計する」のは ORDER BY です。
• f に「分平均温度」など別の列名を入れる
  問題文で「日平均温度を合計したものが積算温度」と明示されています（表5には WITH R（…日平均温度…）とあります）。
• ORDER BY で「圃場ID」を入れ忘れる
  PARTITION BY と ORDER BY を併用せずに「ORDER BY 農事日付」のみで済ませると，他圃場のデータが混ざりかねません。

試験対策ポイント

• ウィンドウ関数の書式：

  SUM(対象列) 
    OVER (
      PARTITION BY 区切り列
      ORDER BY 並び順列
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    )
  

  をしっかり覚える。
• 累計処理では
  ・PARTITION BY で「グルーピング」
  ・ORDER BY で「累積の順序」
  を明確に分けて使う。
• SQL3 の前段 WITH R は，表3 の SQL1 結果（圃場ID, 農事日付, 日平均温度, 行数）を受けていることを意識する。

模範解答のキーワード・論点

• 「全行を再分配が必要」：ハッシュ区分は区分数変更時に全行を再配分する
• 「観測データの混在」：同一圃場のデータが複数区分に分散される
• 「レンジ区分の利点」：年度ごとの区分追加で行数の偏りを抑えられる

解答となる理由

1. ハッシュ区分の場合

   「ハッシュ区分: 区分数を変更する場合, 全行を再分配する。」
   とあり、区分を追加するたびに既存のすべての行を再配置しなければなりません。
   これは、新たに圃場を追加するたびに大きな負荷と運用ダウンタイムを招きます。

2. データの混在リスク
   ハッシュ区分では、同じ圃場IDの値でもハッシュ計算結果により異なる区分に振り分けられるため、

   「同じ圃場に異なる圃場の観測データが混在する可能性がある」
   といった管理上の問題が生じます。

3. レンジ区分でも分散が可能
   レンジ区分では年度ごとに区分を切り替えられ、

   「レンジ区分では, 区分キーの値の範囲が既存の区分と重複しなければ区分を追加でき, 任意の区分を切り離すこともできる。」
   ため、運用時に行数の偏りを抑えつつ、履歴削除や新年度開始の処理が容易です。

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

• 「ハッシュ区分は均等分散される」の誤解
  → 分散自体は均等でも、区分追加時に既存行を再配置するコストを見落としやすい
• 「キーを複数指定すれば特定区分に集約できる」の誤解
  → 圃場IDをキーにしても、ハッシュとレンジでは運用特性が異なる

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

• 区分追加・削除時の「再分配」要否を必ず確認する
• データの「混在リスク」と「運用負荷」を切り分けて考える
• 試験では「性能要件」と「運用要件」の観点で区分方法を比較する問題が頻出

模範解答の論点整理

• 読込み行数（144,000行）は問題文「表6　…読込み行数」から得られる。
• 区分化前後で異なるのは ページ当たりの行数。
  • 区分化前：4行／ページ
  • 区分化後：16行／ページ
• 読込みページ数 は次式で求める：

  読込みページ数 ＝ 読込み行数 ÷ ページ当たりの行数

なぜ「ア＝9,000」となるか

1. 区分化後の ページ当たりの行数 は「16行」
2. 読込み行数は「144,000行」
3. よって

   読込みページ数 ＝ 144,000行 ÷ 16行／ページ ＝ 9,000ページ
   

4. したがって、アに入る数値は 9,000 となります。

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

• 「ページ当たりの行数」を逆に用いてしまい、
  4行で割ってしまう（144,000÷4＝36,000）
• 「ページ長（バイト数）」と混同して計算してしまう
• 割り切れない場合の切り上げ／切り捨ての扱いを忘れる
  • 本問では144,000÷16が整数なので問題になりませんが、実務では余りが出ると最終ページ分を＋1ページする必要があります。

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

1. ページ当たり行数 は表領域のページ長 ÷ １行の平均バイト数 で求める。
2. 読み込みページ数 は「読み込む行数」を「ページ当たり行数」で割って求める（余りは実際には＋1ページとなる）。
3. パーティショニング（区分化）によって、対象データだけを格納する領域が連続化され、「１ページに格納できる行数」も変化するため、I/O量が大幅に削減される。
4. 問題文中の表や条件は必ず正確に引用し、どの数値を使うべきかを見誤らないよう注意すること。

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

• キーワード
  • 「同じ圃場の行は，1ページに1行しか格納できない」
  • 主索引の並び順と表領域のページ当たり行数
  • 副次索引アクセス時の行→ページの１対１対応
• 論点
  1. 表領域のページ当たり行数は「4行／ページ」である。
  2. 主索引（クラスタ化索引）が (観測日付, 観測時分, 圃場ID) の順で格納されており、同一の圃場ID をまとめて格納できる位置にはならない。
  3. その結果、ある圃場ID のデータを副次索引で順次読み込んでも、目的行は必ず別々のページに散在し、１行取得につき１ページ読込が必要になる。

解答の論理的な説明

1. ページ当たり行数の確認

ページ当たり行数：4行／ページ
（ページ長：4,000バイト）

2. 主索引の並び順と格納順序

3. 副次索引アクセス時の行→ページ対応

• ある分刻み時刻における 1,000 圃場ID の行は最大で 1,000÷4 = 250 ページにまたがる
• その中から特定の圃場ID の行を得るには、その行が存在するページを１ページずつ読み込む必要がある

「同じ圃場の行は，1ページに1行しか格納できないから」

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

• 「4行／ページ」だから本来は4行まとめて読めるはずと考えがち
  → しかし主索引順が (観測日付, 観測時分, 圃場ID) で、目的の 圃場ID が連続する位置にはまとまっていない点に要注意。
• 副次索引からのアクセス効率を単純な行数÷行／ページで計算しないこと
  → 実際の読込みページ数は“行の物理分布”に依存します。

試験対策ポイント

• クラスタ化索引（主索引）のキー順序が物理配置に与える影響を理解する
• 非クラスタ化索引（副次索引）からのアクセスでは、目的行の物理的なページ位置を必ず確認する
• ページ当たり行数だけでアクセスコストを見積もらず、「行のまとまり（クラスタリング）」を考慮する
• 表領域・ページ分割、索引構造とアクセスパスの関係を定着させる

キーワードと論点整理

• 農事日付：
  「日出時刻から翌日の日出時刻の1分前までとする日付」を指す
• 年末処理の期限：
  区分化後の“観測”テーブルで不要データを削除するタイミング

解答に至る理由

「日出時刻から翌日の日出時刻の1分前までとする日付を, 農事日付という。」

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

• 「日付＝0時0分から23時59分」と誤解しがち
  → 本システムでは農事日付が日出基準で定義されている点を確認
• 年末処理の期限とカレンダー更新のタイミングを混同しやすい
  → 削除対象と次年度区分追加の順序を整理する

試験対策として押さえるべき知識

• 農事日付の定義（問題文〔1.(4)〕）
• 区分化後の年末処理手順（問題文 表7）
• RDBMSでの日付や期間を扱う際の区切り条件設定の重要性

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

• 外部キー制約のON DELETE CASCADE
  「FOREIGN KEY（観測日付, 圃場 ID）REFERENCES 圃場カレンダ（標準日付, 圃場 ID）ON DELETE CASCADE」
• 非区分化テーブルの削除→再編成
  「行を削除してできた領域は, 行の挿入に使われない」「再編成では, 削除されていない全行を…再ロード」
• 区分化テーブルのパーティション切り離し
  「レンジ区分…任意の区分を切り離すこともできる。区分を切り離したとき, 当該区分の分割索引も切り離される」
• 年末処理の対象
  5年以上前の不要な行を「効率よく削除し, 表領域を有効に利用する」

解答の理由

1) 非区分化（区分化前）の処理

• （イ）で行うべきは，「5年以上前の不要な行を削除」することです。
  → 選択肢①「“圃場カレンダー”から古い行を削除する」を実行すると，
  • 「ON DELETE CASCADE」により，対応する“観測”の古い行も自動で削除されます。
• （ウ）で行うべきは，削除により生じた空き領域を回収して表領域を有効活用することです。
  → 選択肢④「“観測”を再編成する」を実行すると，「再編成では, 削除されていない全行を…再ロード」し，空き領域を消去できます。

2) 区分化後の処理

• （エ）で行うべきは，古い年度の区分を取り除くこと。
  → 選択肢⑤「“観測”から古い区分を切り離す」
• （オ）で行うべきは，パーティション切り離し後に残る“圃場カレンダー”の古い行を削除すること。
  → 選択肢①「“圃場カレンダー”から古い行を削除する」
• （カ）で，その“圃場カレンダー”表領域を再編成し，削除後の空き領域を回収します。
  → 選択肢②「“圃場カレンダー”を再編成する」

選択肢の注意点・誤りやすいポイント

• 「観測から古い行を削除する（③）」を選ぶと，非区分化時に直接“観測”へ DELETE を発行するため，削除した行の空き領域は最後のページ以外使われず，表領域を有効活用できません。
• 「圃場カレンダーを再編成する（②）」は，非区分化時には観測テーブルに適用できないため，ウには選べません。
• 区分化後の年末処理では，「観測を再編成する（④）」は不要です。むしろ「区分を切り離す（⑤）」が最も高速かつログレコードも出さない方法です。

試験対策のポイント

1. ON DELETE CASCADE の仕組み
   → 親テーブル（圃場カレンダー）の削除で子テーブル（観測）が自動的に削除される。
2. 再編成（REORG）の効果
   → 削除後に残る空き領域を詰めることで，表領域を再利用可能にする。
3. レンジ区分の運用
   → 区分の追加・切り離しはログを最小化し，特定期間のデータ保守が容易になる。
4. 年末処理設計
   → 非区分化なら DELETE＋REORG，区分化なら PARTITION の切り離し＋親テーブル整理，というパターンを押さえる。