COALESCE句｜科目A-1（応用情報技術者） 令和6年 春期午前試験 問26
出典：令和6年春期 午前 問26
分野：データベース ／ データ操作
"部品"表及び"在庫"表に対し，SQL文を実行して結果を得た。SQL文のaに入れる字句はどれか。
- ア：COALESCE(MIN(在庫.在庫数)，0)
- イ：COALESCE(MIN(在庫.在庫数)，NULL)
- ウ：COALESCE(SUM(在庫.在庫数)，0)
- エ：COALESCE(SUM(在庫.在庫数)，0)
TSUNAGARU-ADVICE
まず押さえたいこと
在庫数を部品IDごとにまとめて判定する場合は、各倉庫の在庫数を合計して、その合計在庫と発注点を比較するのが基本です。GROUP BYによって部品単位に集約されるため、集約関数としてSUMを使うかどうかに注目します。
迷ったときの判断軸
LEFT OUTER JOINでは、在庫表に該当行がない部品も結果に残ります。このときSUMの結果はNULLになるため、COALESCEを使ってNULLを0として扱う必要があります。MINでは最小の在庫数しか見ないため、複数倉庫の在庫総数を判定できません。
科目Bにつなげるために
特にプロフェッショナルデジタルスキル（データ・AI）試験合格を目指す方は、この問題を外部結合・GROUP BY・集約関数？NULL処理の組合せとして整理しましょう。実務でも、在庫や売上などを複数行から集計し、欠損データを適切に扱って判定するSQLは頻繁に使われます。
このSQL文では、部品ごとの「発注点」と「在庫数」を比較し、発注点の方が大きければ「必要」、そうでなければ「不要」と判定しています。
ここで重要なのは、同じ部品が複数の倉庫に保管されている場合には、各倉庫の在庫数を合計して判定する必要があることです。
P01は、W01とW02にそれぞれ90個ずつ在庫があるので、在庫数の合計は、
90 + 90 = 180個
です。発注点は100個なので、
100 > 180
は偽となり、「不要」です。
P02は在庫数が150個、発注点も150個なので、
150 > 150
は偽となり、「不要」です。
P03は「在庫」表に該当する行がありません。LEFT OUTER JOINによってP03自体は結果に残りますが、SUM(在庫.在庫数)の結果はNULLになります。
そこでCOALESCEを使い、NULLの場合には0として扱います。
COALESCE(SUM(在庫.在庫数), 0)
とすれば、P03については、
100 > 0
となるので、「必要」と判定されます。
実行結果は、P01が「不要」、P02が「不要」、P03が「必要」となり、設問の結果と一致します。
したがって、ウが適切です。
❌他選択肢が誤りの理由ア：COALESCE(MIN(在庫.在庫数), 0)
⇒MINを使うと、各部品について在庫数の合計ではなく、最も少ない倉庫の在庫数と発注点を比較してしまいます。
P01：MIN(90, 90) = 90なので、100 > 90となり「必要」
P02：MIN(150) = 150なので、150 > 150は偽となり「不要」
P03：在庫がないのでCOALESCEによって0となり、100 > 0で「必要」
実行結果は「P01：必要、P02：不要、P03：必要」となり、P01が設問の結果と一致しません。
イ：COALESCE(MIN(在庫.在庫数), NULL)
⇒MINを使っているので、P01では在庫合計180個ではなく最小値90個が使われます。また、COALESCEの代替値もNULLなので、P03のNULLを0に置き換えることができません。
P01：MIN(90, 90) = 90なので、100 > 90となり「必要」
P02：MIN(150) = 150なので、150 > 150は偽となり「不要」
P03：MINの結果がNULLのままなので、100 > NULLの結果はUNKNOWNとなり、CASE WHENの条件を満たさないため「不要」
実行結果は「P01：必要、P02：不要、P03：不要」となります。
エ：COALESCE(SUM(在庫.在庫数), NULL)
⇒SUMを使う点は適切なので、P01とP02は正しく判定できます。しかし、P03ではSUMの結果がNULLとなり、COALESCEの代替値もNULLなので、在庫数を0として扱えません。
P01：SUM(90, 90) = 180なので、100 > 180は偽となり「不要」
P02：SUM(150) = 150なので、150 > 150は偽となり「不要」
P03：SUMの結果がNULLのままなので、100 > NULLの結果はUNKNOWNとなり、CASE WHENの条件を満たさないため「不要」
実行結果は「P01：不要、P02：不要、P03：不要」となり、P03が設問の結果と一致しません。