この記事は「データベーススペシャリスト資格に興味はあるが、どのようなものか？どう学ぶのか？」という方向けに、具体的な内容と私自身の挑戦ログをお伝えする。学び中の方や、これから学ぼうとされる方の参考になれば幸いだ。
今回はSQLによる仮想テーブルの作成（復習・確認ドリル）について書く。
※注意点として、SQLはDBMSによって作法が異なる。この記事はGoogle Big Queryに準拠するものであることをお含みおき願いたい。
確認ドリル 問題013
前回振り返り
- SQLについて。今までは「実態のあるテーブル」の操作（列、行、グループ化、複数テーブルの結合と集合演算）を想定してきたが、現在は「仮想テーブル」を扱う操作を学んでいる。前回は復習として確認ドリルを行った。
- 今回も復習の確認ドリルを行う。その際は小さな部分から順にクリアする。なぜならこの数学的手法は堅実で楽しいからだ。
問題文
- [sales]テーブル、[products]テーブル、[customers]テーブルを結合して、
- 性別（gender）および商品カテゴリ（product_category）ごとの販売金額の合計を取得してください。
- 性別（gender）は「１」場合に「男性」、「２」の場合に「女性」、それ以外は「不明」とします。
- 並び替えは、性別（昇順）、販売金額の合計（降順）として、
- 結果テーブルは、性別（customer_gender）、商品カテゴリ（product_category）、販売金額の合計（sum_revenue）の３カラムとしてください。
概要の把握
- テーブル構造は、salesテーブルの下に、productsテーブル・customerテーブルがそれぞれぶら下がってるイメージ
- 分析の目的は、ターゲットを検討するため、性別・カテゴリ別の販売合計金額を取得するもの
- 流れとしては、３つのテーブルを横に結合し、
- そのテーブルについてグループ化対象列を２つ指定する。
SQLの実践1
- まず、[sales]テーブル、[products]テーブル、[customers]テーブルを結合する。
- テーブル構造は上述のとおりなので、それぞれの外部キーを用いて単純に”横”の結合が可能。
SQLの実践2,3
- 次に、「性別（gender）および商品カテゴリ（product_category）ごとの販売金額の合計を取得してください。」とある。
- これは束ねる列を2つ指定してgroup byすればよい。
- ただ、問題文には思わせぶりに「性別（gender）は「１」場合に「男性」、「２」の場合に「女性」、それ以外は「不明」とします。」と書いてある。
- よって、「柔軟なグループ化」としてcase句を利用する（詳細は別記事）
- 具体的には「case when gender=1 then “男性” when gender=2 then “女性” else “不明” end as 列名」のように指定すればよい。
SQLの実践4,5
- 次に「並び替えは、性別（昇順）、販売金額の合計（降順）」とあるのでorder byで指定
- また「結果テーブルは、性別（customer_gender）、商品カテゴリ（product_category）、販売金額の合計（sum_revenue）の３カラム」とあるので列名をas句で指定
- 結果は以下のとおり
確認ドリル 問題014
問題文
- [sales]テーブルを利用して、2019年7～9月には10個以上販売されていたのに、
- 2019年10～12月には10個以上売れなくなってしまった商品のリストを取得してください。
- 結果テーブルは[product_id]だけの1カラムとします。
SQLの実践1
- [sales]テーブルを利用して、とあるので「from sample.sales」と記述
- 次に「2019年7～9月には」、とあるのでwhere句とbetween句を使い絞る
- 一旦、この期間内に10個以上販売されていたものを、以下のとおり求める。
- なお、date_timeの管理値は時刻まで含むが、between句はそのうち日付まで指定で絞ることもできる。
SQLの実践2,3
- さて、同じようにして2019年10月～12月の販売個数を調べ、目で見比べることはできる。
- ただ、今回はテーブルの結合・集合演算・仮想テーブルの復習なのでこれらを用いたい。
- 単純には、「2019年7月～9月に10個以上売れた商品」と「2019年10月～12月に10個以上売れた商品」をそれぞれ仮想テーブルで保管し、差集合を取れば良い。
確認ドリル 問題015
問題文
- [sales]テーブルと[product]テーブルを結合して、
- 商品名（product_name）別に利益の大きい順に３つの商品を取り出してください。
- 結果テーブルは、商品名（product_name）、販売個数の合計（sum_quantity）、販売金額の合計（sum_revenue）、コストの合計（sum_cost）、利益の合計（profit）の5カラムとします。
SQLの実践1,2
- ２つのテーブルの結合は容易。inner join句で外部キー（この場合はproduct_id）で結合するのみ。
- 「product_neme別に」はgroup byで良い。
- ここでのポイントは、データ管理上、[sales]テーブル側に販売金額合計を持ち、[product]テーブル側に単価を保有している点。
- このため、以下のようにテーブルを単純結合すると、利益を求めるには各行ごとに「販売金額－単価×販売数」を計算する必要があると分かる。
SQLの実践3
- 以上をふまえたSQLは以下のとおり。
- なお、group byは集約関数を用いる必要があるので、単価を取得するにはmax（cost）のように記述する必要があることに注意。
考察
- SQLについて。今までは「実態のあるテーブル」の操作（列、行、グループ化、複数テーブルの結合と集合演算）を想定してきたが、現在は「仮想テーブル」を扱う操作を学んでいる。今回も復習としての確認ドリルを解いた。
- 考察。日々少しづつ学び、自分の望む方向に、周りに気を使わず、アウトプットを着実に積み重ねることは、精神衛生上とても良いと感じる。実社会においてはその逆が多い（気を使う合議制）からかもしれないが。ただ、心身の健康と成長の実感は大事なので、学びと発信はぜひ継続したい。
抽象化×英語学習×AI,IT活用
取り組み概要
- 目的は、「IT,AIを実際に使いこなし」かつ「英語を学び、視野を広げる」こと。それにより選択肢を増やし、自分と家族を守ること。
- ポイントは、学びを楽に、楽しく、続けられる工夫をすること。抽象化で本質を学び、Google翻訳でスピーディなインプットとアウトプットを実現し、ブログの１文のみと低いハードルで習慣化する。
- 詳細は別の記事に整理。
（実践①）日本語のプリエディット
- 基本。日本語は特殊なので直訳困難。下準備（加工）が必要。
- コツ１：文は短くシンプルに。長い文は区切る。敬語は使わない。
- コツ２：省略は避けてハッキリと。『誰が・誰の・何を・いつ』を補う。『略語・略称・比喩表現』は使わない。
- コツ３：翻訳しやすい表記に。ひらがなより漢字（例：あめ⇒雨）。漢数字よりアラビア数字（例：百⇒100。数え方は個で統一。特殊記号は使わない。
- 原文例。「日々少しづつ学び、自分の望む方向に、周りに気を使わず、アウトプットを着実に積み重ねることは、精神衛生上とても良いと感じる。実社会においてはその逆が多い（気を使う合議制）からかもしれないが。ただ、心身の健康と成長の実感は大事なので、学びと発信はぜひ継続したい。」
- プリエディット例。一旦そのままの表記にする。
（実践②）自力での英訳
- 基本。プリエディットをもう１歩進めて「日本語を並び替える」と、自力の英訳が可能になる。
- コツ１：短文の連続にする（意味が伝われば良い）。
- コツ２：語順の”型”は絞る。具体的には基本の３つ。「A=B（例：I am happy）」「主＋動＋A=B（例：I feel you happy）」「主＋動＋A＋B（例：I give you present）」
- コツ３：使う単語も絞る。日本語側の表現を変えれば手持ちの単語で対応できる。さらに単語を増やす工夫を使う。「in・un・disをつけて反対の意味（例：unhappy）」「動詞にing・edをつけて形容詞を作る（例：exciting）」「前置詞＋名詞で形容詞を作る（例：on business trip）」
- 日本語の並び替え例（カッコ内は用いた”型”）。「私は、感じる、日々の学び、自分自身のために、大事、心の健康に」「なぜなら、たぶん、実社会は、逆」「いずれにせよ、私は、続ける、学びとアウトプットを。心身の健康と成長の実感のため」
- 自力で英訳。「I feel daily study for myself is important for my mental health. 」「because, probably real society is reverse. 」「however, I try to continue study and output for my mental health and certain of growth. 」
（実践③）Google翻訳での添削
- Google翻訳での和訳のコツ。「長い文は区切る」「コンマや5つの接続詞（before,after,but,when,if）前で区切る」「英文の文末にはピリオドとスペースを入れる」
- Google翻訳で「自力の英訳」を和訳。「メンタルヘルスには、日々の勉強が大切だと感じています。」「なぜなら、おそらく実際の社会は逆です。」「しかし、私はメンタルヘルスと確実な成長のために研究と成果を継続しようとしています。」
- 振り返り（意図した意味になっているか）。全体的に少しニュアンスが異なる。もう少し日本語をシンプルに入れ替えたい。
（実践④）google翻訳との対比
- 次に、実践①でプリエディットした日本語をGoogle翻訳で英訳する。
- Reading and writing SQL is seemingly difficult.
- However, you can lead to the correct answer by dividing the problem into smaller pieces, working from the simplest ones, eliminating each mistake, and accumulating them.
- I find this mathematical process very enjoyable.
- I can continue to do what I like, so I want to cherish this feeling.
- 振り返り。自力英訳と比較し、汎用的に使えそうな単語は意識しておく。
（実践⑤）よりシンプルな表現に整理する
- twitterで練習として投稿することも考えると、より抽象的でシンプルな表現が良い。
- 日本語を再整理。私は学ぶ。私は積み上げる。それは自身になり、世の中を良く変えていく。
- I will stack my learning in the direction I want. it makes me strong and changes the world to good.
参考書籍
- 集中演習 SQL入門/木田和廣/株式会社インプレス
- データベーススペシャリスト2022年版/三好康之/翔泳社
- おうちで学べるデータベースのきほん/ミック,木村明治/翔泳社
コメント