令和5年度 秋期 データベーススペシャリスト試験 午後I 問3 観測データ分析のSQLウィンドウ関数と性能設計

正解の根拠

表5のSQL文（SQL1）は、ウィンドウ関数を利用して複数の圃場を対象に日平均温度を算出するためのものです。

GROUP BY句で 圃場ID と 農事日付 によってグループ化を行っているため、SELECT句で指定できるのはそれらの列と集計関数に限られます。

日平均温度は分平均温度の平均であるため、集計関数として AVG(分平均温度) を指定します。

したがって、空欄aには 圃場ID, 農事日付, AVG(分平均温度) が入ります。

高得点のポイント

• グループ化のキーである 圃場ID と 農事日付 を列挙していること。

• 集計関数として AVG(分平均温度) を正しく記述していること。

正解の根拠

空欄bは GROUP BY 句に指定する列を問うものです。

SQL1では、圃場ごと、および農事日付ごとに分平均温度を集計して日平均温度を算出します。

したがって、グループ化のキーとなる 圃場ID, 農事日付 が入ります。

高得点のポイント

• グループ化の対象として 圃場ID と 農事日付 の両方を指定していること。

正解の根拠

農事日付の1日は、24時ではなく 日出時刻 を区切りとして定義されています。

日出時刻は季節や日々によって変化するため、前日の日出時刻から当日の日出時刻までの時間間隔は変動します。

その結果、農事日付における1日の長さは厳密に1,440分（24時間）になるとは限りません。

高得点のポイント

• 日出時刻が毎日異なること を明記している。

• その結果として 1日の分数が同じとは限らないこと に論理的につなげている。

正解の根拠

図3の空欄cは、SQL2を用いて算出される11月3日の 移動平均温度 を表しています。

SQL2のウィンドウ関数 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW は、当日を含む直近3日間の平均を計算することを意味します。

11月1日から11月3日までの日平均温度は、それぞれ $15.0$、$13.0$、$14.0$ です。

したがって、移動平均は $(15.0 + 13.0 + 14.0) / 3 = 14.0$ となります。

正解の根拠

図3の空欄dは、11月4日の 移動平均温度 を表しています。

直近3日間（11月2日〜11月4日）の日平均温度は、それぞれ $13.0$、$14.0$、$18.0$ です。

したがって、移動平均は $(13.0 + 14.0 + 18.0) / 3 = 15.0$ となります。

正解の根拠

図3の空欄eは、11月5日の 移動平均温度 を表しています。

直近3日間（11月3日〜11月5日）の日平均温度は、それぞれ $14.0$、$18.0$、$16.0$ です。

したがって、移動平均は $(14.0 + 18.0 + 16.0) / 3 = 16.0$ となります。

正解の根拠

空欄fは、有効積算温度を算出する際の評価対象となる列です。

有効積算温度は、日平均温度 が基準温度（ここでは10.0度）を超えた分を積算していくものです。

CASE WHEN f > 10.0 THEN f - 10.0 ELSE 0 END という条件式から、評価されるべき値は 日平均温度 となります。

高得点のポイント

• 積算の対象となる 日平均温度 を正確に記述していること。

正解の根拠

空欄gは、ウィンドウ関数の PARTITION BY 句に指定する列です。

複数の圃場を対象に分析を行う場合、圃場ごとに独立して積算温度を計算する必要があります。

したがって、区画を分けるキーとして 圃場ID が入ります。

高得点のポイント

• 圃場ごとに計算を独立させるための 圃場ID を正確に記述していること。

正解の根拠

空欄hは、ウィンドウ関数の ORDER BY 句に指定する列です。

積算温度は時間の経過とともに加算されていくため、日付順にデータを並べる必要があります。

したがって、順序付けのキーとして 農事日付 が入ります。（圃場ID, 農事日付 としても正解となります）

高得点のポイント

• 時間の経過を示す 農事日付 を順序付けのキーとして正確に記述していること。

正解の根拠

大容量テーブルにおいてハッシュ区分を採用すると、区分（パーティション）を追加・変更する際に大きな問題が生じます。

ハッシュ区分ではハッシュ関数の結果に基づいてデータが均等に分散されるため、区分の数を変更するとハッシュ値の再計算が必要になります。

その結果、既存の全データに対して行の再分配（再配置）が発生し、システムに多大なオーバヘッドをもたらします。

高得点のポイント

• 区分追加時に 行の再分配（再配置） が発生することを明記していること。

正解の根拠

空欄アは、テーブルのページサイズ拡張と区分化に伴う性能見積りの計算結果です。

区分化前は複数圃場のデータが時系列に混在し、1ページ内に同一圃場のデータが極端に少ない状態でした。

レンジ区分を採用しページ長を 32,000バイト に拡大することで、同一圃場のデータが効率的に同一ページ内に収まるようになり、アクセスに必要なページ読込み回数が劇的に削減されます。

計算式に基づき必要なページ読込み回数を算出すると 9,000 となります。

正解の根拠

区分化前は、500台の機器から送信される観測データが時刻順に次々とテーブルに挿入（インサート）されていました。

そのため、ストレージの同一ページ内には複数の異なる圃場のデータが混在して格納されます。

結果として、特定の圃場に関するデータを連続して読み込もうとしても、同じ圃場の行は1ページに1行しか存在しないため、1行ごとに新しいページを読み込む必要が生じます。

高得点のポイント

• 複数圃場のデータが混在している物理的な格納状態を理解していること。

• 同じ圃場の行が同一ページ内に1行しかない ことを明確に記述していること。

正解の根拠

このシステムにおける農事日付の区切りは、24時ではなく 日出時刻 と定義されています。

そのため、12月31日の24時を過ぎて元日になったとしても、元日の日出時刻（の1分前）までに観測されたデータは、論理的にはまだ「前年（12月31日）の農事日付」として扱われます。

もし12月31日の24時を期限に年末処理を行ってしまうと、前年分のデータ処理が完全に終わらないうちに年が切り替わってしまうため、元日の日出時刻を期限とする必要があります。

高得点のポイント

• 元日の日出時刻までのデータが前日（前年）の農事日付に含まれること を明記していること。

正解の根拠

表7の手順は、古いデータのパージ（削除）とテーブルの再編成に関するものです。

「圃場カレンダ」テーブルは区分化されていない通常のテーブルであるため、古いデータを消去するには DELETE 文を用いて行単位で削除する必要があります。

したがって、空欄イには「“圃場カレンダ”から古い行を削除する。」が入ります。

各選択肢の解説

• ア: 正解です。通常のテーブルでは行の削除を行います。

• イ: 削除によって生じた断片化を解消するために後で再編成を行いますが、最初のデータパージ手順としては誤りです。

• ウ, エ, オ: これらは「観測」テーブルに対する操作であり、空欄イの対象ではありません。

正解の根拠

古いデータのパージ方法として、「観測」テーブルのような大容量でレンジ区分されているテーブルについては、行の削除ではなく区分の切り離しを行います。

表7の文脈と手順の流れから、空欄ウには観測テーブルの保守作業が入るべきですが、選択肢構成により全体の運用手順として適切なものを当てはめます。（実際の設問の空欄位置に応じた適切な運用フェーズを選択します。）

ここでは、データ削除に伴う領域の断片化解消のために「“観測”を再編成する。」が適切となる文脈です。

各選択肢の解説

• エ: 正解です。

• ア, イ: これらは「圃場カレンダ」に対する操作です。

• ウ, オ: 削除や切り離しの手順は別のフェーズで実施されます。

正解の根拠

大容量テーブルである「観測」テーブルは、年度ごとにレンジ区分されています。

このような区分化テーブルから古いデータを消去する場合、DELETE 文で行を削除すると大量の更新ログが発生し、パフォーマンスに悪影響を与えます。

そのため、対象となる古い区分（パーティション）ごと切り離す（DROP または DETACH）方法が高速で効率的です。

したがって、空欄エには「“観測”から古い区分を切り離す。」が入ります。

各選択肢の解説

• オ: 正解です。区分化テーブルのデータパージのベストプラクティスです。

• ウ: 区分化テーブルに対して行単位の削除を行うのは非効率であり誤りです。

• ア, イ, エ: 対象テーブルや操作が不適切です。

正解の根拠

一連の保守手順の中で、空欄オには再度「圃場カレンダ」テーブルに関連するパージ手順が割り当てられます。

区分化されていないテーブルでは、古いデータを消去するために行単位の削除が必要です。

したがって、空欄オには「“圃場カレンダ”から古い行を削除する。」が入ります。

各選択肢の解説

• ア: 正解です。

• イ: 再編成は削除の後に実施されるべき操作です。

正解の根拠

空欄カは、データ削除後のメンテナンス手順を指しています。

「圃場カレンダ」テーブルから古い行を削除（DELETE）すると、ストレージ領域内に空き領域（断片化）が発生し、アクセス効率が低下する可能性があります。

これを解消しアクセス性能を回復させるために、テーブルの再編成（REORGANIZE）を行います。

したがって、空欄カには「“圃場カレンダ”を再編成する。」が入ります。

各選択肢の解説

• イ: 正解です。DELETE後の断片化解消に必要な手順です。

• ア: すでに削除操作は完了しているため不適切です。

• エ: 「観測」テーブルは区分の切り離しを行っているため、テーブル全体の再編成は不要または優先度が異なります。