この記事は「データベーススペシャリスト資格に興味はあるが、どのようなものか？どう学ぶのか？」という方向けに、具体的な内容と私自身の挑戦ログをお伝えする。学び中の方や、これから学ぼうとされる方の参考になれば幸いだ。
今回はSQLにおける仮想テーブル利用（確認ドリル）について書く。
※注意点として、SQLはDBMSによって作法が異なる。この記事はGoogle Big Queryに準拠するものであることをお含みおき願いたい。
確認ドリル017
前回振り返り
- 「仮想テーブル」を扱う操作について「with句」「ビュー」「サブクエリ」を一通り学習できた。
- 今回も仮想テーブルの操作をまとめて復習するため、確認ドリルを解く。
問題
- [customers]テーブルから
- 4人以上のプレミアム顧客（is_premium=true）がいる都道府県における
- もっとも高齢の顧客の情報を
- 全カラム取り出してください
- 結果テーブルは、７カラム１レコードになります。
SQL実践（テーブルの確認）
- まず、「[customers]テーブルから」とあるのでこれを確認。
- 以下のとおり7つのフィールド（属性・列）を持つテーブルであることが分かった。
SQLの実践（if句）
- 次に、「4人以上のプレミアム顧客（is_premium=true）がいる都道府県における」について
- 都道府県で束ねて、プレミアム顧客の数を調べ、4人以上かカウントすればよい。
- ただ、プレミアム顧客の管理値はbool値なのでカウントし辛い。
- そこで、まず以下のとおりif句を用いてプレミアム顧客の場合1を立てるフラグを用意する。（赤枠部分）
SQLの実践（サブクエリ、group by）
- さて、上記のとおり「プレミアム顧客を判別するフラグを追加したテーブル」をサブクエリにして
- group by で都道府県別に束ねて、プレミアム顧客数をカウントしてみる。
- すると、都道府県別のプレミアム顧客数を管理するテーブルができた。
SQLの実践（絞り込み、サブクエリ、in句）
- 次は、とてもシンプルに考えて上記テーブル（都道府県別のプレミアム顧客数）をサブクエリとして利用して、プレミアム顧客数が4人以上の都道府県を絞り込む。
- そして、「該当する都道府県」の1列n行のリストを用意し、それを用いてin句で絞り込む。
- 最後に、「最も高齢の顧客」なので生年月日で並び替えて（nullは除く）、最初の1つめの情報を取得すれば良い。
- SQLは以下のとおり。サブクエリが複数使われているが、上でやってきた手順を愚直に囲んでいるだけなことが分かる。
SQLの実践（別解）
- 「4人以上のプレミアム顧客（is_premium=true）がいる都道府県」について
- group byのセットで用いる「集計関数の中にif句を入れられる（以下赤枠）」ことと、「having句でその結果を使って絞り込める（以下青枠）」ことを活用すると、以下のとおりシンプルにまとまる。
- また、この結果をin句の絞り込み条件としてサブクエリにし、合致するレコードを仮想テーブルとしてwith句で保管、という別解も勿論可能である。
考察
- SQLについて。「仮想テーブル」を扱う操作に関して「with句」「ビュー」「サブクエリ」を一通り学習できた。今回は復習のため確認ドリルを解いた。（if句でフラグ追加し、group byで集計し、結果をサブクエリで束ねてin句の絞り込み条件に使用）
- 考察。「日常の悩みを、先人の知恵を最大限使い、自分で考えて解決したい」方に「ふだんづかいの倫理学/平尾 昌宏/晶文社」はおすすめだ。倫理学の基礎と実用法のバランスが丁度良く、ユーモア溢れる文章で一気に読ませてくれるからだ。
考察のシンプル化と英訳（練習中）
- I believe the book with solid principles and practical method good. it’s better if it has humor.
- (確かな原則と実践方法を兼ね備えた本は良書だと思います。ユーモアもあればなお良いです）
参考書籍
- 集中演習 SQL入門/木田和廣/株式会社インプレス
- ふだんづかいの倫理学/平尾 昌宏/晶文社
コメント