この記事は「データベーススペシャリスト資格に興味はあるが、どのようなものか？どう学ぶのか？」という方向けに、具体的な内容と私自身の挑戦ログをお伝えする。学び中の方や、これから学ぼうとされる方の参考になれば幸いだ。
今回はSQLにおける仮想テーブル利用（確認ドリル）について書く。
※注意点として、SQLはDBMSによって作法が異なる。この記事はGoogle Big Queryに準拠するものであることをお含みおき願いたい。
確認ドリル016
前回振り返り
- 前回はサブクエリの実践として、データ調査の例題（作業記録とカレンダーという2つのテーブルを用いて、休業日を調査）を学んだ。
- 以上により、「仮想テーブル」を扱う操作について「with句」「ビュー」「サブクエリ」を一通り学習できた。
- 今回は仮想テーブルの操作をまとめて復習するため、確認ドリルを解く。
問題
- [sales]テーブルには、15種類の商品ID（product_id）が販売された記録が格納されています。
- 商品ID（product_id）別に平均単価を計算し、これを①とします。
- さらに、15種類の①について平均したものを②とし、[avg_unit_price_by_product]とします。
- そのうえで、[sales]テーブルにおける商品ID別の平均単価（avg_unit_pricd⇒①）が②より大きい商品IDに絞り込み、以下の5カラムの結果テーブルを取得してください。並び替えは[avg_unit_price]の大きい順とし、最低１か所でサブクエリを利用してください。
必要なカラム
- 商品ID（product_id）
- 販売個数合計（sum_quantity）
- 販売金額合計（sum_revenue）
- 商品ID別の単価（avg_unit_price）⇒①
- 商品ID別の平均単価の平均（avg_unit_price_by_product）⇒②
SQL実践（テーブルの確認）
- まず、「[sales]テーブルには、15種類の商品ID（product_id）が販売された記録が格納されています」とあるのでこれを確認。
- 以下のとおりsalesテーブルにはproduct_idがあり、1から15まであることを確認できた。
SQLの実践（group byと集計関数sum）
- 次に、「商品ID（product_id）別に平均単価を計算し、これを①とする」から行う。
- 「商品ID別に平均単価」とあるので、group byで商品IDごとに販売額合計（sum_revenue）と販売数合計（sum_quantity）を求め、割り算で平均単価を計算する。
- このとき、product_idで束ねる（group byを用いる）ため、select句のproduct_id以外には集計関数しか使えないことに注意。よって、割り算はsum(revenue)/sum(quantity)と計算する。（赤枠部分）
- SQLは以下のとおり
SQLの実践（with句と集計関数avg）
- 次に、「これを①とする」とあるので、仮想テーブルとして一時保存するためwith句を用いる。
- 具体的には以下赤枠の通り。独立した仮想テーブルを一時保存するのにwith句は最適。
- また、「15種類の①について平均したものを②とし[avg_unit_price_by_product]とします」とあるのでこれも計算する。具体的には以下青枠の通り。with句のテーブルでグループ化は済んでいるので、そのまま集計関数avgを利用できる。
SQLの実践（サブクエリと絞り込み条件）
- ここまで出来たら、あとは今まで作成してきた列を全て表示する。
- まずwith句のテーブルに殆どの要素は含まれるので、「select * from master」と指定。
- 次に、先ほどの②（総平均）を求める式をサブクエリとし、列の追加や絞り込み条件に使えるようにする。この確認ドリルはサブクエリによる値の抽出と、それを利用した絞り込みに関する事例。
考察
- SQLについて。「仮想テーブル」を扱う操作に関して「with句」「ビュー」「サブクエリ」を一通り学習できた。今回は仮想テーブルの操作をまとめて復習するため、確認ドリルを解いた。
- 考察。「他の人に迷惑をかけない限り、みんな幸せになる権利がある」（日本国憲法13条意訳）や「幸せの定義は各々自分で考える」などの「より良く生きるための原則」はあらゆる場所で応用できると考える。当たり前であるが故に忘れがちなので、倫理学の復習とアウトプットも定期的に行いたい。
考察のシンプル化と英訳（練習中）
- I try to relearn the ethics regularly. because, the wisdom to live well is useful.
- (私は定期的に倫理学を学び直します。なぜなら、良く生きるための知恵は役に立つからです)
参考書籍
- 集中演習 SQL入門/木田和廣/株式会社インプレス
コメント