DBデータベーススペシャリスト試験午後Ⅰ

令和5年度秋期データベーススペシャリスト午後Ⅰ 問3

事例を読んで設問の答えを考えてから、解説のポイントとIPA公式の解答例・採点講評で確認しましょう。

問題

問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℃といわれている。 (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に整理して比較した。表6 区分化前と区分化後の読込みに必要な表領域のページ数の比較（未完成） 3．年末処理の見直し 5年以上前の不要な行を効率よく削除し，表領域を有効に利用するための年末処理の主な手順を，区分化前と区分化後のそれぞれについて検討し，表7に整理した。表7 区分化前と区分化後の年末処理の主な手順の比較（未完成）設問1 〔観測データの分析〕について答えよ。 (1) 表3中の［a］，［b］に入れる適切な字句を答えよ。 (2) SQL1の結果について，1日の行数は，1、440行とは限らない。その理由を30字以内で答えよ。ただし，何らかの不具合によって分析システムにレコードが送られない事象は考慮しなくてよい。 (3) 図3中の［c］～［e］に入れる適切な数値を答えよ。 (4) 表5中の［f］～［h］に入れる適切な字句を答えよ。設問2 〔“観測”テーブルの区分化〕について答えよ。 (1) Cさんは，区分方法としてハッシュ区分を採用しなかった。その理由を35字以内で答えよ。 (2) 表6中の［ア］に入れる適切な数値を答えよ。 (3) 区分化前では，副次索引から1行を読み込むごとに，なぜ表領域の1ページを読み込む必要があるか。その理由を30字以内で答えよ。ただし，副次索引の索引ページの読込みについては考慮しなくてよい。 (4) 区分化後の年末処理の期限は，なぜ12月31日の24時ではなく元日の日出時刻なのか。その理由を35字以内で答えよ。 (5) 表7中の［イ］～［カ］に入れる手順を，それぞれ次の①～⑤の中から一つ選べ。①～⑤が全て使われるとは限らない。ただし，バックアップの取得と索引の保守については考慮しなくてよい。 ① “圃場カレンダ”から古い行を削除する。 ② “圃場カレンダ”を再編成する。 ③ “観測”から古い行を削除する。 ④ “観測”を再編成する。 ⑤ “観測”から古い区分を切り離す。

図1 テーブル構造（一部省略）。枠で囲んだ関係スキーマ。顧客（顧客ID，顧客名，連絡先情報，…），圃場（圃場ID，圃場名，顧客ID，緯度，経度，標高，…），圃場カレンダ（標準日付，圃場ID，日出時刻，日没時刻，日出方位角，日没方位角），観測（観測日付，観測時分，圃場ID，農事日付，分平均温度，分日照時間，機器設定情報，…）。主キーは実線下線，圃場の顧客IDは破線下線。

表1 主な列の意味・制約。列名と意味・制約の表。標準日付は0時0分0秒から23時59分59秒までを1日とする日付。観測日付・観測時分はセンサーが計測したときの標準日付と1分単位の時分。農事日付は日出時刻から翌日の日出時刻の1分前までを1日とする日付。分平均温度はハウス内温度の1分間の平均値。

表2 “観測”テーブルの主な列統計，索引定義，制約，表領域の設定（一部省略）。観測日付・観測時分（1,440）・圃場ID（1,000）・農事日付の列値個数と索引定義の表。主索引は観測日付，観測時分，圃場IDの順，副次索引は圃場ID，農事日付の順。表領域はページ長4,000バイト，4行/ページ。圃場カレンダ（標準日付，圃場ID）への外部キー制約（ON DELETE CASCADE）。網掛けの注記あり。

表3 観測データを分析するSQL文の例（未完成）。SQL1の目的と構文の表。目的は圃場ごと・農事日付ごとに1日の平均温度と行数を調べること。WITH R（圃場ID，農事日付，日平均温度，行数）AS（SELECT［a］，COUNT(*) FROM 観測 GROUP BY［b］）SELECT * FROM R。空欄はaとb。

表4 改良したSQL文。SQL2の目的と構文の表。指定した圃場と期間の日平均温度の変動傾向を調べるため，AVG(日平均温度) OVER(ORDER BY 農事日付 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS X を使う。ホスト変数h1〜h3と網掛け部分に関する注記がある。

図2 SQL1の結果行の一部。圃場ID，農事日付，日平均温度，…の結果行の表。2023-02-01〜02-10の日平均温度は順に9.0，14.0，10.0，12.0，20.0，10.0，15.0，14.0，19.0，18.0。値は小数第1位まで表示したという注記がある。

図3 SQL2の結果行（未完成）。農事日付とXの結果行の表。2023-02-01と02-02は網掛け。Xは02-03から順に11.0，12.0，［c］，14.0，［d］，13.0，［e］，17.0。Xの表示桁数と網掛けに関する注記がある。

表5 積算温度を調べるSQL文（未完成）。SQL3の目的と構文の表。圃場ごと・農事日付ごとの積算温度を SUM(［f］) OVER(PARTITION BY［g］ ORDER BY［h］ ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) で求め，農事日付の期間で絞り込む。ホスト変数と網掛け部分の注記あり。

表6 区分化前と区分化後の読込みに必要な表領域のページ数の比較（未完成）。区分化前はページ当たり4行（4,000バイト）で144,000行を読み込み144,000ページ。区分化後はページ当たり16行（16,000バイト）で144,000行を読み込み，読込みページ数は空欄［ア］ページ。

表7 区分化前と区分化後の年末処理の主な手順の比較（未完成）。区分化前は期限が特になし。手順は1.“圃場カレンダ”に翌年の行を追加，2.［イ］，3.“圃場カレンダ”を再編成，4.［ウ］。区分化後は期限が元日の日出時刻。手順は1.翌年の行を追加，2.“観測”に翌年度の区分を追加，3.［エ］，4.［オ］，5.［カ］。

解き方のヒントを見る

前提条件を根拠に設計判断を検証する

この種の問題では、一般的な知識だけで判断せず、問題に示されたデータの性質や業務上の要件を根拠として読み取ることが重要です。各判断がどの前提に基づいているかを確認し、前提と矛盾しないかを一つずつ照らし合わせて検討しましょう。

この問題で問われていること

確認すべき観点は一つです。問題の記述から、データがどのように発生し蓄積されるか、どのような目的で参照・保守されるかという前提を抜き出し、検討対象の判断がその前提に照らして妥当かを自分の言葉で説明できるかを確かめてください。

視点

データベースの問答や設計の検討では、同じ手法でも前提となるデータの特性や利用目的によって評価が変わります。そのため、一般論として良い・悪いと決めつけるのではなく、問題に書かれた条件を判断の根拠に置くことが大切です。記述のどの部分が根拠になるかを意識して読み、自分の判断がその根拠と結び付いているかを確かめる習慣をつけましょう。

確認ポイント

問題の記述の中で、データの発生・蓄積・参照・保守に関する条件がどこに書かれているかを確認したか
自分の判断が、一般論ではなく問題に示された条件のどれに基づいているかを説明できるか
判断の根拠とした条件を読み落としたり、書かれていない条件を補って考えたりしていないか

正解と解説を見る

解説

農業用ハウスの観測データ分析システムを題材に、SQLのウィンドウ関数を使った集計、農事日付という業務上の日付の扱い、大容量テーブルのレンジ区分化による性能改善と年末処理の効率化を問う問題です。本文にあるRDBMS仕様（行の挿入位置、再編成、区分の追加・切離し）と外部キー制約（ON DELETE CASCADE）を組み合わせて考えることが求められます。

問題の全体像

B社は、統合機器から毎分送られるレコードを“観測”テーブルに蓄積し、農家向けに分析する分析システムを構築します。行は観測日付・観測時分・圃場IDごとに1行で、将来は1、000圃場・最長5年分に達する大容量テーブルになります。農家は日出時刻を区切りとする農事日付を使うため、標準日付とは異なる1日の区切りを意識する必要があります。出題趣旨では、農作業の特徴を踏まえたテーブル構造を理解したうえで、SQLのウィンドウ関数を使った分析、表領域の区分化とその運用、性能見積りの能力を問うとしています。設問1はSQL（集約、移動平均、累積和）、設問2は区分化による読込みページ数の変化と年末処理の手順を扱います。

設問1(1)a

SQL1の目的は「圃場ごと農事日付ごとに1日の平均温度と行数を調べる」ことです。WITH句で定義するRの列は（圃場ID、農事日付、日平均温度、行数）で、行数はCOUNT(*)が受け持ちます。そのため、空欄aにはCOUNT(*)より前の3列を並べます。日平均温度は、1分間の平均値である分平均温度を1日分平均したものなので、AVG(分平均温度)になります。公式解答例では「圃場ID、農事日付、 AVG(分平均温度)」です。

設問1(1)b

集計の単位は「圃場ごと農事日付ごと」なので、GROUP BYには集約関数以外でSELECTする列、つまり圃場IDと農事日付を指定します。標準日付である観測日付でグループ化すると、農事日付の1日とは区切りがずれるため目的に合いません。公式解答例では「圃場ID、農事日付」です。

設問1(2)

表1では、農事日付は「圃場の日出時刻から翌日の日出時刻の1分前まで」と定義されています。業務の概要1.(3)にあるとおり、日出時刻は日ごとに変わります。そのため、ある日の日出から翌日の日出までの分数は1、440分（24時間）より多い日も少ない日もあり、毎分1行登録される“観測”の行数も農事日付単位では1、440行になりません。公式解答例では「日出時刻が日々異なり1日の分数が同じとは限らないから」「農事日付の1日は1、440分とは限らないから」です。採点講評では、この設問の正答率はやや低く、農事日付の1日の区切りが日出時刻であるという説明から理由を理解してほしいと述べています。30字以内に収めるには「日出時刻が日ごとに変わる」「農事日付の1日の分数が一定でない」の2点を押さえます。

設問1(3)c

SQL2のXは、AVG(日平均温度) OVER (ORDER BY 農事日付 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)です。つまり、その日と前2日を合わせた3日間の移動平均です。検算として、02-03のXは(9.0+14.0+10.0)/3=11.0、02-04は(14.0+10.0+12.0)/3=12.0となり、図3の値と一致します。02-05のcは、02-03〜02-05の10.0、12.0、20.0の平均で42.0/3=14.0です。公式解答例も14.0です。

設問1(3)d

02-07のdは、02-05〜02-07の3日分（20.0、10.0、15.0）の平均で、45.0/3=15.0です。公式解答例も15.0です。前後の既知の値である02-06=14.0（(12+20+10)/3）、02-08=13.0（(10+15+14)/3）でも同じ計算方法が成り立つことを確認できます。

設問1(3)e

02-09のeは、02-07〜02-09の3日分（15.0、14.0、19.0）の平均で、48.0/3=16.0です。公式解答例も16.0です。02-10の17.0も(14.0+19.0+18.0)/3で一致します。

設問1(4)f

業務の概要2.(4)にあるとおり、積算温度は1日の平均温度をある期間にわたって合計したものです。RのうちSUMで累積する対象は日平均温度になります。ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWで、先頭行から現在行までの累積和を求めます。公式解答例では「日平均温度」です。

設問1(4)g

SQL3は、SQL2と違って圃場IDで絞り込まず、「圃場ごと農事日付ごと」の積算温度を求めます。複数の圃場の行が混ざるので、ウィンドウを圃場IDで区画に分けないと、別の圃場の日平均温度まで合計されてしまいます。したがってPARTITION BYには圃場IDを指定します。公式解答例では「圃場ID」です。採点講評では、gの正答率がやや低く、農事日付とする誤答が散見されたと述べています。分析対象が複数の圃場である場合に、ウィンドウを圃場IDで区画に分け、区画ごとに日平均温度を農事日付順に集計するという役割を理解することが求められています。

設問1(4)h

積算温度は日付の古い順に累積していくため、ORDER BYには農事日付を指定します。PARTITION BY 圃場IDで区画がすでに分かれているので、農事日付だけで順序は決まります。公式解答例では「農事日付」のほか「圃場ID、農事日付」も挙げられています。

設問2(1)

RDBMS仕様2.(2)では、ハッシュ区分は区分数を変更する場合に全行を再分配するとされています。一方、物理設計の変更(3)では、新たな圃場を追加する都度、区分を1個追加する方針です。ハッシュ区分では、区分を追加するたびに大容量の全行を再分配することになります。また、ハッシュ値で分配すると、一つの区分に複数の圃場の行が混在する可能性があり、圃場単位・年度単位でまとめるという狙いにも合いません。公式解答例では「区分を追加する都度，全体の行の再分配が必要になるから」「同じ圃場に異なる圃場の観測データが混在する可能性があるから」「レンジ区分でも区分の行数をほぼ同じにする利点が得られるから」の3つが示されています。35字以内では、このうち一つの観点を簡潔に書きます。

設問2(2)ア

区分化後は、圃場IDごと・年度ごとの区分に行が格納されます。そのため、一つの区分（表領域）には同じ圃場の行だけが、挿入順（時刻順）に連続して並びます。ページ長を16、000バイトにすると1ページに16行が入るので、ある圃場の100日分144、000行は連続した144、000/16=9、000ページに収まります。公式解答例では9、000です。採点講評では、この設問の正答率が低く、圃場ごと年度ごとに区分化することで同じページ内に別の圃場の観測データが混ざらないことを読み取ってほしい、ページ長を大きくしたことに加えて区分化によって大きな改善効果を得ていることに着目してほしいと述べています。

設問2(3)

RDBMS仕様1.(1)にあるとおり、行は表領域の最後のページに順に格納されます。区分化前は、各圃場の統合機器が毎分レコードを送るため、同じ時分の行が圃場の数だけ続けて挿入されます。その結果、1ページ（4行）に入る行はすべて異なる圃場の行になり、特定の圃場の行を副次索引でたどると、1行ごとに別のページを読むことになります。これが表6の区分化前144、000ページの根拠です。公式解答例では「同じ圃場の行は，1ページに1行しか格納できないから」です。採点講評では、(2)アと同様に正答率が低かったとしています。30字以内で「同じ圃場の行が1ページに1行しかない」ことを示します。

設問2(4)

区分キーの年度は、農事日付の1月1日から12月31日の範囲です。12月31日24時（元日0時）から元日の日出時刻の1分前までの観測データは、農事日付では12月31日に属するため、前年度の区分に格納されます。翌年度の区分が必要になるのは、農事日付が1月1日になる元日の日出時刻からです。そのため、翌年度の区分の追加などの年末処理はそれまでに済ませればよいことになります。公式解答例では「元日の日出時刻までのデータは前日の農事日付に含まれるから」です。35字以内で「元日の日出前のデータは前日（前年度）の農事日付に属する」ことを書きます。

設問2(5)イ

区分化前の手順では、3.で“圃場カレンダ”を再編成しているため、その前に“圃場カレンダ”から古い行を削除しておく必要があります。表2の外部キー制約はON DELETE CASCADEなので、“圃場カレンダ”の古い行を削除すると、それを参照する“観測”の古い行も連鎖して削除されます。そのため、“観測”から個別に削除する③は不要です。公式解答例では①です。

設問2(5)ウ

RDBMS仕様1.(2)にあるとおり、行を削除してできた領域は（最後のページを除き）挿入に再利用されません。連鎖削除で“観測”に生じた空き領域を回収し、表領域を有効に利用するには、“観測”も再編成する必要があります。公式解答例では④です。

設問2(5)エ

区分化後は、5年以上前の年度の区分を切り離すことで、古い“観測”の行をまとめて除去できます。RDBMS仕様2.(3)にあるとおり、切離しでは区分内の行のログが記録されないため効率的です。この手順を“圃場カレンダ”の削除より前に行うことが重要です。先に“圃場カレンダ”を削除すると、ON DELETE CASCADEによって“観測”の大量の行が1行ずつ削除されてしまいます。公式解答例では⑤です。

設問2(5)オ

古い区分を切り離した後で、“圃場カレンダ”から古い行を削除します。この時点では参照している“観測”の行はすでに切り離されているので、連鎖削除の負荷は生じません。公式解答例では①です。

設問2(5)カ

“圃場カレンダ”で削除によって生じた空き領域は再利用されないため、最後に“圃場カレンダ”を再編成します。“観測”は区分を切り離しただけで、行を削除したわけではないため、再編成（④）は不要です。公式解答例では②です。

学習のポイント

ウィンドウ関数（PARTITION BY、ORDER BY、ROWSによる枠指定）の意味と計算方法、行の格納順序から読込みページ数を見積もる方法、レンジ区分の切離しと連鎖削除を組み合わせた運用手順を押さえましょう。

出典：独立行政法人情報処理推進機構（IPA）令和5年度秋期データベーススペシャリスト試験午後Ⅰ 問3（公式資料：問題冊子（PDF）／解答例／採点講評）

令和5年度秋期 データベーススペシャリスト 午後Ⅰ 問3

問題