【応用情報技術者試験 平成28年春期】問6 データベース, SQL問題解説

応用情報技術者試験 平成28年春期　問6 データベース

応用情報技術者試験 平成28年春期　問6 データベースよりSQL問題をピックアップして解説します。

本記事中では問題文の全ての記載は省いて解説します。
問題文は下記IPA Webサイトより
https://www.jitec.ipa.go.jp/1_04hanni_sukiru/mondai_kaitou_2016h28_1/2016h28h_ap_pm_qs.pdf

設問2 : 複雑なSQL文の穴埋め

（問題に登場するER図とSQL）

設問2: 図2中のd～fに入れる適切な字句又は式を答えよ。なお，表の列名には必ずその表の別名を付けて答えよ。

引用元：IPA 独立行政法人
情報処理推進機構：平成28年度春期 試験　問題冊子・解答例・採点講評・配点割合（PDF）

解説

コンビニエンスストアの店舗ごとの売上分析のためのデータウェアハウス構築を題材に、要件にあったデータ抽出を行うためのSQL文の完成が問われています。

問題文「売上ファクト表に挿入するデータを抽出するSQL文を図2に示す」の通り、相応しいデータとなるよう図2の穴埋めを行います。

SQL穴埋め完成手順

1. 売上ファクト表の目的を考える

穴埋めを行う前に、抽出するデータの挿入先である「売上ファクト表」について考える必要があります。

問題文冒頭 [売上ファクト表の作成] の箇所、
販売実績と在庫実績のデータを1日単位で集計して売上ファクト表を作成する
の記載より販売表と在庫表を利用することがわかります。

2. SQLは括弧の内側から読み解く

設問のSQLで複雑な点は主に、最初のFROM区の後に括弧で記載されたSELECT文（αとβ）が2つあることですが、こちらはSQLの中で本体とは別なSELECT文を用いる副問合せで、この括弧で括られているSELECTの結果を本体のFROM句で指定する表に見立てる記述となっています。

副問合せがある場合は本体のSQLよりも先に実行されるため、はじめに着目していきます。

まず図のαの印で括られている一つ目の括弧のSELECT文ですが、こちらは在庫表から年月日別、店舗別、商品別の在庫の数量を取得しています。
在庫表は「確認時分秒」の属性も存在するのでAVG関数を使ってその日毎の在庫数の平均を集計しているようです。

また、結果に対して「ST」と表の別名が付けられています。

αの抽出結果（例）

+--------------------+-------------+-------------+--------------------+
| SC.確認年月日      | SC.店舗ID   | SC.商品ID   | 日間在庫数量       |
+--------------------+-------------+-------------+--------------------+
| 2022-02-27         |           1 |           1 |             6.0000 |
| 2022-02-27         |           1 |           2 |             6.0000 |
| 2022-02-27         |           1 |           3 |             5.0000 |
| 2022-02-27         |           2 |           1 |             6.0000 |
| 2022-02-27         |           2 |           2 |             5.0000 |
| 2022-02-27         |           2 |           3 |             6.0000 |
| 2022-02-27         |           1 |           4 |            10.0000 |
| 2022-02-27         |           2 |           6 |            10.0000 |
+--------------------+-------------+-------------+--------------------+

次に空欄dを挟んで、βで印が付けられている二つ目の括弧のSELECT文は販売表と販売詳細表を内部結合し、年月日別、店舗　別品別にSUM関数を使用して販売数量の合計を求めていることがわかります。

βの抽出結果（例）

+--------------------+-------------+-------------+--------------------+
| SL.販売年月日      | SL.店舗ID   | SD.商品ID   | 日間販売数量       |
+--------------------+-------------+-------------+--------------------+
| 2022-02-27         |           1 |           1 |                 10 |
| 2022-02-27         |           1 |           2 |                  9 |
| 2022-02-27         |           1 |           3 |                  8 |
| 2022-02-27         |           2 |           1 |                  4 |
| 2022-02-27         |           2 |           2 |                  5 |
| 2022-02-27         |           2 |           3 |                  2 |
+--------------------+-------------+-------------+--------------------+

こちらの結果は「SS」として表の別名が付けられています。

この二つの結果を使用して冒頭の問題文、「販売実績と在庫実績のデータを1日単位で集計」を満たすためのデータを取得していることがわかります。

3. 空欄dについて

先の括弧の二つのSELECTはそれぞれ、本体のFROM句で使用するための別表として見立てられ、本体のSELECTに指定している列にも以下のように

SELECT ST.確認年月日, ST.店舗 ID, ST.商品 ID, COALESCE（SS.日間販売数量, 0） , ST.日間在庫数量〜

とあるように「ST」「SS」からそれぞれ必要な列を選択していることがわかりますので空欄dに記述する解答はこの二つの表「ST」「SS」を結合するためのものと特定できます。

結合の種類

二つの表の結合の方法を内部結合か外部結合のいずれかにする必要がありますが、こちらは問題文の以下の部分から考えることができます。

なお，店舗に在庫はあるが販売実績がない商品は日間販売数量を 0 とする。関数 COALESCE(A，B) は，AがNULLでないときはAを，AがNULLのときはBを返す。

引用元：IPA 独立行政法人
情報処理推進機構：平成28年度春期 試験　問題冊子・解答例・採点講評・配点割合（PDF）

「店舗に在庫はあるが販売実績がない商品」の記述から在庫表の全行を結果として抽出する必要があるため、結合種類としては外部結合（OUTER JOIN）を記述することがわかります。在庫表をもとに作成した「ST」は結合元（JOINの左側）になるので空欄dに入るSQL文はLEFT OUTER JOINとなります。

ST, SSを外部結合した結果

+--------------------+--------------+--------------+-----------------------+-----------------------+
| ST.確認年月日      | ST.店舗 ID   | ST.商品 ID   | SS.日間販売数量       | ST.日間在庫数量       |
+--------------------+--------------+--------------+-----------------------+-----------------------+
| 2022-02-27         |            1 |            1 |                    10 |                6.0000 |
| 2022-02-27         |            1 |            2 |                     9 |                6.0000 |
| 2022-02-27         |            1 |            3 |                     8 |                5.0000 |
| 2022-02-27         |            2 |            1 |                     4 |                6.0000 |
| 2022-02-27         |            2 |            2 |                     5 |                5.0000 |
| 2022-02-27         |            2 |            3 |                     2 |                6.0000 |
| 2022-02-27         |            1 |            4 |                     0 |               10.0000 |
| 2022-02-27         |            2 |            6 |                     0 |               10.0000 |
+--------------------+--------------+--------------+-----------------------+-----------------------+

関数 COALESCEによって結合先の販売実績「SS」に含まれる販売数量のないNULLの値が0に書き換えられています。

内部結合にした場合の結果

+--------------------+--------------+--------------+-----------------------+-----------------------+
| ST.確認年月日      | ST.店舗 ID   | ST.商品 ID   | SS.日間販売数量       | ST.日間在庫数量       |
+--------------------+--------------+--------------+-----------------------+-----------------------+
| 2022-02-27         |            1 |            1 |                    10 |                6.0000 |
| 2022-02-27         |            1 |            2 |                     9 |                6.0000 |
| 2022-02-27         |            1 |            3 |                     8 |                5.0000 |
| 2022-02-27         |            2 |            1 |                     4 |                6.0000 |
| 2022-02-27         |            2 |            2 |                     5 |                5.0000 |
| 2022-02-27         |            2 |            3 |                     2 |                6.0000 |
+--------------------+--------------+--------------+-----------------------+-----------------------+

（先の外部結合で抽出できていた日間販売数量が0の行を抽出できなくなります）

4. 空欄e, f

空欄e,fはON区の後ろにあるため、結合のための条件だとわかります。

既にひとつ、結合条件「ST.確認年月日 = SS.販売年月日」と指定されているのでANDのあとに続けて、表STと表SSの共通の行を同様に結合条件として記載すればOKです。

空欄e : ST.店舗 ID = SS.店舗 ID
空欄d : ST.商品 ID = SS.商品 ID
（空欄e,f の解答は順不同）

（解答まとめ）

Follow me!