データベーススペシャリスト試験 2015年午前2 問11

庭に訪れた野鳥の数を記録する“観測”表がある。観測のたびに通番を振り, 鳥名と観測数を記録している。AVG関数を用いて鳥名別に野鳥の観測数の平均値を得るために, 一度でも訪れた野鳥については, 観測されなかったときの観測数を0とするデータを明示的に挿入する。SQL文のaに入る字句はどれか。ここで, 通番は初回を1として、観測のタイミングごとにカウントアップされる。　

CREATE TABLE 観測 (
  通番      INTEGER,
  鳥名      CHAR(20),
  観測数    INTEGER,
  PRIMARY KEY (通番, 鳥名))

INSERT INTO 観測
SELECT DISTINCT obs1.通番, obs2.通番, obs2.鳥名, 0
   FROM 観測 AS obs1, 観測 AS obs2
WHERE NOT EXISTS (
   SELECT * FROM 観測 AS obs3
    WHERE (a)
      AND obs2.鳥名 = obs3.鳥名)

ア：obs1.通番 = obs1. 通番

イ：obs1. 通番= obs2.通番

ウ：obs1.通番 = obs3. 通番（正解）

エ：obs2.通番= obs3.通番

解説

庭に訪れた野鳥の観測数平均を求めるSQL文の空欄補充【午前2 解説】

要点まとめ

結論：空欄aには「obs1.通番 = obs3.通番」を指定し、同じ観測タイミングのデータを比較します。
根拠：通番は観測のタイミングを示すため、同じ通番で鳥名の有無をチェックし、未観測の鳥に0を挿入する必要があります。
差がつくポイント：NOT EXISTS句内で通番を正しく結合しないと、誤ったデータが挿入され平均値が狂うため、通番の結合条件が重要です。

正解の理由

正解はウ: obs1.通番 = obs3.通番です。
このSQLは、観測タイミング（通番）ごとに鳥名が観測されなかった場合に0を挿入するため、NOT EXISTS句で「同じ通番かつ同じ鳥名の観測データが存在しない」ことを判定します。
ここで、obs1は全通番を、obs3はNOT EXISTS内の比較対象であり、通番を一致させることで「同じ観測タイミングでの観測有無」を正確に判定できます。

よくある誤解

通番を結合条件に含めず鳥名だけで判定すると、異なる観測タイミングのデータを誤って参照し、0挿入の判定が不正確になります。
また、obs2とobs3の通番を結合すると、挿入対象の通番と比較対象の通番がずれてしまいます。

解法ステップ

観測テーブルの構造と通番の意味を理解する（通番＝観測タイミング）
AVG関数で鳥名別平均を求める際、未観測時は0を明示的に挿入する必要があると把握する
INSERT文のSELECT句で全通番×全鳥名の組み合わせを作成し、未観測の組み合わせを抽出する意図を理解する
NOT EXISTS句で「同じ通番かつ同じ鳥名の観測データが存在しない」ことを判定するための結合条件を考える
通番をobs1とobs3で結合することで、同じ観測タイミングのデータを正しく比較できると判断する

選択肢別の誤答解説

ア: obs1.通番 = obs1.通番
→ 常に真となり意味のない条件。通番の比較対象が同じテーブルの同じ行で自己比較しているだけ。
イ: obs1.通番 = obs2.通番
→ obs2は挿入対象の鳥名・通番の組み合わせ。NOT EXISTS内の比較対象はobs3なので、ここで結合しても意味がない。
ウ: obs1.通番 = obs3.通番
→ 正解。obs1の通番（全通番）とobs3の通番（NOT EXISTS内の観測データ）を結合し、同じ観測タイミングの有無を判定。
エ: obs2.通番 = obs3.通番
→ obs2は挿入対象、obs3は比較対象。NOT EXISTS内でobs2とobs3を結合すると、挿入対象の通番と比較対象の通番がずれてしまい誤判定になる。

補足コラム

SQLで欠損データを補完する際、LEFT JOINやNOT EXISTSを使って存在しない組み合わせを検出し、0などのデフォルト値を挿入する手法はよく使われます。
特に時系列や観測データのように「全組み合わせを網羅したい」場合は、基準となるキー（ここでは通番）を正しく結合条件に含めることが重要です。

FAQ

Q: なぜ通番を結合条件に含める必要があるのですか？
A: 通番は観測のタイミングを示すため、同じタイミングで観測されなかった鳥を正確に判定するために必要です。

Q: NOT EXISTS句の役割は何ですか？
A: 指定した条件に合致する行が存在しない場合に真となり、未観測の組み合わせを抽出するために使います。

Q: AVG関数で平均を求める際、なぜ0を挿入するのですか？
A: 観測されなかったタイミングも0として扱うことで、平均値が正確に計算され、偏りを防げます。

関連キーワード: SQL, AVG関数, NOT EXISTS, データ補完, 観測データ, 集計, NULL処理, データベース設計

← 前の問題へ次の問題へ →

データベーススペシャリスト試験 2015年 午前2 問11

解説