基本情報技術者 2017年 秋期 午後 問03

解答の導き方

• 「2016年の1月1日から12月31日まで」と「分類がコーヒー」は、集計に含める購入を絞る条件です。販売日の条件は販売表から、分類の条件は商品表から判定します。
• 「購入額の合計が10,000円以上」と「5回以上」は、絞り込んだ購入を会員ごとに集計してから判定する条件です。

誤りやすいポイント

• 1行ごとの購入額が10,000円以上かを調べるのではありません。複数の購入額を合計して10,000円以上かを調べます。そのため、単価と個数の積をWHERE句で判定するアは適切ではありません。
• 「分類がコーヒー」は集計する行を選ぶ条件です。集計後にHAVING句で判定するのではなく、WHERE句で指定します。したがって、分類の条件をHAVING句に置くウは適切ではありません。
• 販売表の販売額は、伝票全体の販売額です。コーヒー以外の商品を含む可能性があり、コーヒー商品の購入額の合計とは一致しません。また、集計対象の明細行ごとに伝票の販売額を足すと、同じ伝票の販売額を重複して数えることもあります。そのため、販売額を合計するエは適切ではありません。
• COUNT(*)が数えるのは、WHERE句で選ばれた後の行です。期間外やコーヒー以外の商品を除いたうえで、会員ごとの行数を数えます。

FAQ

解答の導き方

誤りやすいポイント

• 販売時点の単価を記録することで、全商品の単価変更履歴が残ると考えてしまう点です。販売明細表に記録されるのは、実際に販売された商品です。
• 販売時点の単価が分かれば、次の単価変更との差も必ず分かると考えてしまう点です。単価変更後に商品が売れなければ、その変更後の単価は販売明細表に残りません。
• 商品表の単価と販売明細表の単価を混同する点です。商品表には変更後の単価が保存されますが、販売明細表に追加する単価は、その販売が行われた時点の単価です。

FAQ

解答の導き方

1. 商品1種類ごとの販売額を求めます。
   図2の販売明細表には、「販売時単価」と「個数」があります。商品表の単価を後から変更できるようになると、商品表にある単価は販売時の単価と異なる可能性があります。そのため、販売したときの実際の金額を求めるには、販売明細表の販売時単価を使います。
   商品1種類ごとの販売額は、次の計算です。
   販売時単価 × 個数
2. b2では、集計に必要な情報を内側のSELECT文で取り出します。
   内側のSELECT文では、年齢と分類に加えて、販売時単価と個数を取り出す必要があります。年齢は問題文の指定どおり、2017から生年を引いて求めます。
   したがって、b2には「販売明細表.販売時単価, 販売明細表.個数」が入ります。商品表から現在の単価を取り出すのではなく、販売明細表に記録された販売時単価を使う点が重要です。
3. b1では、販売額を合計します。
   内側のSELECT文で取り出した販売時単価と個数を掛けて、商品1種類ごとの販売額を求めます。それを年齢と分類の組合せごとに合計するため、b1には「SUM(販売時単価 * 個数)」が入ります。SUMは、複数の値を足し合わせるための集約関数です。
   販売表の販売額は伝票全体の金額です。1枚の伝票に複数の分類の商品が含まれる場合、その金額だけでは分類ごとの販売額を分けられません。そのため、販売明細表の商品ごとの販売額を計算して合計します。
4. b3では、年齢と分類でまとめます。
   同じ年齢で同じ分類の商品を買った販売明細をまとめて、その販売額を合計します。このように、どの項目の値が同じ行をまとめるか指定するのがGROUP BYです。
   よって、b3には「年齢, 分類」が入ります。合計販売額は、まとめた後に計算される値なので、GROUP BYの対象には含めません。

誤りやすいポイント

• 商品表.単価を使ってしまう
  商品表の単価は変更されることがあります。過去の販売額を正しく求めるには、販売時点の単価を記録した販売明細表.販売時単価を使います。
• 販売表.販売額を分類ごとに合計してしまう
  販売表.販売額は伝票全体の金額です。商品分類ごとの販売額を求めるには、商品ごとの販売明細から金額を計算する必要があります。
• GROUP BYに合計販売額を含めてしまう
  合計販売額は集計して求める値です。集計のまとまりを作る項目は年齢と分類なので、GROUP BYには「年齢, 分類」を指定します。

FAQ

解答の導き方

1. ビュー販売集計表を作るための表を考えます。
   図4のビュー販売集計表には、「商品番号」と「販売総数」があります。図1の販売明細表には、商品ごとの「商品番号」と販売した「個数」が記録されています。したがって、商品番号ごとに個数を合計すれば、販売総数を求められます。
   この集計に必要なのは販売明細表だけです。販売表には伝票ごとの販売日や販売額などはありますが、商品ごとの個数はありません。また、商品表の商品名や単価も、販売個数の集計には使いません。
   そのため、ビュー販売集計表を作るための必要最小限の表の数、c1は1です。
2. ビュー在庫表を作るための表を考えます。
   図3のビュー入荷集計表には、商品ごとの入荷総数があります。先ほどのビュー販売集計表には、商品ごとの販売総数があります。在庫数は、入荷総数から販売総数を差し引いて求めます。
   在庫数 ＝ 入荷総数 − 販売総数
   ただし、問題文には「一度でも入荷した商品は在庫数ゼロでも表示する」とあります。そこで、入荷集計側の商品を基準にして、販売集計側の情報を商品番号で結び付けます。片方に対応する行がなくても、基準側の行を残す結合を外部結合といいます。ここでは、入荷集計側のすべての商品を残す必要があります。
   販売集計側に商品がない場合は、その商品の販売総数を0として扱います。この場合、在庫数は入荷総数と同じです。販売記録がないことだけでは、在庫数が0とはいえません。在庫数が0になるのは、販売総数が入荷総数と等しい場合です。
   ビュー在庫表の作成には、ビュー入荷集計表とビュー販売集計表の2つが必要です。問題文ではc2にビュー販売集計表も含めるので、c2は2です。

誤りやすいポイント

• 販売表だけを使おうとしないようにします。商品ごとの販売個数は、販売明細表にあります。
• 商品表も必須ではありません。商品名などを表示する設問ではありません。
• 販売記録がない商品を、在庫数0と考えないようにします。販売記録がないときの販売総数は0であり、在庫数は入荷総数です。
• 入荷した商品をすべて表示するには、販売集計側だけを基準にしたり、両方に行がある場合だけ残す結合にしたりしてはいけません。入荷集計側の商品を残す必要があります。

FAQ