DB 令和5年度秋期午後Ⅰ 問3

農業用機器メーカーによる観測データ分析システムのSQL設計，性能，運用に関する次の記述を読んで，設問に答えよ。

　ハウス栽培農家向けの農業用機器を製造・販売するB社は，農家のDXを支援する目的で，RDBMSを用いたハウス栽培のための観測データ分析システム（以下，分析システムという）を構築することになり，運用部門のCさんが実装を担当した。

〔業務の概要〕

1.顧客，圃場，農事日付

（1）顧客は，ハウス栽培を行う農家であり，顧客IDで識別する。

（2）圃場は，農家が農作物を育てる場所の単位で，圃場IDで識別する。圃場には一つの農業用ハウス（以下，ハウスという）が設置され，トマト，イチゴなどの農作物が1種類栽培される。

（3）圃場の日出時刻と日没時刻は，圃場の経度，緯度，標高によって日ごとに変わるが，あらかじめ計算で求めることができる。

（4）日出時刻から翌日の日出時刻の1分前までとする日付を，農事日付という。農家は，農事日付に基づいて作業を行うことがある。

2.制御機器・センサー機器，統合機器，観測データ，積算温度

（1）圃場のハウスには，ハウスの天窓の開閉，カーテン，暖房，潅水などを制御する制御機器，及び温度（気温），湿度，水温，地温，日照時間，炭酸ガス濃度などを計測するセンサー機器が設置される。

（2）顧客は，圃場の一角に設置したB社の統合環境制御機器（以下，統合機器という）を用いて，ハウス内の各機器を監視し，操作する。もし統合機器が何か異常を検知すれば，顧客のスマートフォンにその異常を直ちに通知する。

（3）統合機器は，各機器の設定値と各センサー機器が毎分計測した値を併せて記録した1件のレコードを，B社の分析システムに送り，蓄積する。分析システムは，蓄積されたレコードを観測データとして分析しやすい形式に変換し，計測された日付ごと時分ごと圃場ごとに1行を“観測”テーブルに登録する。

（4）農家が重視する積算温度は，1日の平均温度をある期間にわたって合計したもので，生育の進展を示す指標として利用される。例えば，トマトが開花してから完熟するまでに必要な積算温度は，1,000〜1,100°Cといわれている。

（5）分析システムの目標は，対象にする圃場を現状の100圃場から段階的に増やし，将来1,000圃場で最長5年間の観測データを分析できることである。

〔分析システムの主なテーブル〕
　Cさんが設計した主なテーブル構造を図1に，主な列の意味制約を表1に示す。また，“観測”テーブルの主な列統計，索引定義，制約，表領域の設定を表2に示す。

図1　テーブル構造（一部省略）

表1　主な列の意味制約

表2　“観測”テーブルの主な列統計，索引定義，制約，表領域の設定（一部省略）

〔RDBMSの主な仕様〕

1.行の挿入・削除，再編成

（1）行を挿入するとき表領域の最後のページに行を格納する。最後のページに空き領域がなければ，新しいページを表領域の最後に追加し，行を格納する。

（2）最後のページを除き，行を削除してできた領域は，行の挿入に使われない。

（3）再編成では，削除されていない全行をファイルにアンロードした後，初期化した表領域にその全行を再ロードし，併せて索引を再作成する。

2.区分化

（1）テーブルごとに一つ又は複数の列を区分キーとし，区分キーの値に基づいて表領域を物理的に分割することを，区分化という。

（2）区分方法には次の2種類がある。

　・レンジ区分:区分キーの値の範囲によって行を区分に分配する。

　・ハッシュ区分:区分キーの値に基づき，RDBMSが生成するハッシュ値によって行を一定数の区分に分配する。区分数を変更する場合，全行を再分配する。

（3）レンジ区分では，区分キーの値の範囲が既存の区分と重複しなければ区分を追加でき，任意の区分を切り離すこともできる。区分の追加，切離しのとき，区分内の行のログがログファイルに記録されることはない。

（4）区分ごとに物理的に分割される索引（以下，分割索引という）を定義できる。区分を追加したとき，当該区分に分割索引が追加され，また，区分を切り離したとき，当該区分の分割索引も切り離される。

〔観測データの分析〕

1.観測データの分析
　　分析システムは，農家の要望に応じて様々な観点から観測データを分析し，その結果を農家のスマートフォンに表示する予定である。Cさんが設計した観測データを分析するSQL文の例を表3のSQL1に，結果行の一部を後述する図2に示す。

表3　観測データを分析するSQL文の例（未完成）

2.SQL文の改良
　　顧客に表3のSQL1の日平均温度を折れ線グラフにして見せたところ，知りたいのは日々の温度の細かい変動ではなく，変動の傾向であると言われた。そこでCさんは，折れ線グラフを滑らかにするため，表4のSQL2のように改良した。SQL2が利用した表3のSQL1の結果行の一部を図2に，SQL2の結果行を図3に示す。

表4　改良したSQL文

図2　SQL1の結果行の一部

図3　SQL2の結果行（未完成）

3.積算温度を調べるSQL文
　　農家は，栽培している農作物の出荷時期を予測するために積算温度を利用する。

　Cさんが設計した積算温度を調べるSQL文を，表5のSQL3に示す。

表5　積算温度を調べるSQL文（未完成）

〔“観測”テーブルの区分化〕

1.物理設計の変更
　　Cさんは，大容量になる“観測”テーブルの性能と運用に懸念をもったので，次のようにテーブルの物理設計を変更し，性能見積りと年末処理の見直しを行った。

（1）表領域のページ長を大きくすることで1ページに格納できる行数を増やす。

（2）圃場IDごとに農事日付の1月1日から12月31日の値の範囲を年度としてその年度を区分キーとするレンジ区分によって区分化する。

（3）新たな圃場を追加する都度，当該圃場に対してそのときの年度の区分を1個追加する。

2.性能見積り
　　表5のSQL3について，表2に示した副次索引から100日間の観測データ144,000行を読み込むことを仮定した場合の読込みに必要な表領域のページ数を，区分化前と区分化後のそれぞれに分けて見積もり表に整理して比較した。

表6　区分化前と区分化後の読込みに必要な表領域のページ数の比較（未完成）

3.年末処理の見直し
　　5年以上前の不要な行を効率よく削除し，表領域を有効に利用するための年末処理の主な手順を，区分化前と区分化後のそれぞれについて検討し，表7に整理した。

表7　区分化前と区分化後の年末処理の主な手順の比較（未完成）

　ー

　ー

IPA公開情報

出題趣旨

　近年，日本では農業構造の変化に対応するべく，持続可能かつ生産効率が高いスマート農業を実現するためにデジタル技術を活用する取組が進められている。データベーススペシャリストには，最終利用者である農家の要望を理解して，協業するデータ分析者又はデータサイエンティストに適切なデータを迅速かつ効率良く提供することが求められている。
　本問では，農業用ハウスの機器から送られる大量の観測データを，データベースに蓄積する観測データ分析データシステムを題材として，農作業の特徴を考慮して設計されたテーブル構造を理解した上で，農産物の生育状況を SQL のウィンドウ関数を利用して効果的に分析する能力，テーブルが大容量になることから表領域を適切に区分化して運用する能力，さらに，実装に不可欠な性能見積りを行う能力を問う。

採点講評

　問 3 では，農業用ハウスのための観測データ分析データシステムを題材に，ウィンドウ関数を用いた SQL 設計，大容量のテーブルの区分化，及び性能見積りについて出題した。全体として正答率は平均的であった。
　設問 1 では，(2)の正答率がやや低かった。〔業務の概要〕1.(4)及び表 1 において，農事日付の 1 日の区切りが日出時刻であるという説明から，農事日付の 1 日が 1,440 分とは限らない理由を正しく理解し，正答を導き出してほしい。また，(4)g の正答率がやや低かった。g を，圃場 ID ではなく農事日付とする誤答が散見された。表 5 の SQL 文の目的は，分析の対象が複数の圃場である場合，分析のウィンドウを圃場 ID で区画に分け，そのウィンドウ区画ごとに当該圃場の日平均温度を農事日付順に集計して積算温度を求めることである。 SQL のウィンドウ関数は，試行錯誤が容易な強力なツールであるだけでなく，ウィンドウ区画の役割を理解して活用すれば，観測データを一層多角的に分析ができるようになる。
　設問 2 では，(2)ア，(3)ともに正答率が低かった。圃場ごと年度ごとに区分化することで同じページ内に別の圃場の観測データが混ざらないことを読み取り，正答を導き出してほしい。ページ長を大きくしたことに加えて，区分化によって大きな改善効果を得ていることに着目してほしい。
　大容量のテーブルでは，性能と運用の効率化のために表領域の区分化を設計することが多いので，その設計の妥当性を評価するために性能見積りを行う技術を，是非，身に付けてほしい。

前問ナビ次問