この記事は「データベーススペシャリスト資格に興味はあるが、どのようなものか？どう学ぶのか？」という方向けに、具体的な内容と私自身の挑戦ログをお伝えする。学び中の方や、これから学ぼうとされる方の参考になれば幸いだ。
今回はSQLによるテーブル構造の変換②（サブクエリの応用）について書く。
※注意点として、SQLはDBMSによって作法が異なる。この記事はGoogle Big Queryに準拠するものであることをお含みおき願いたい。
注文履歴をユーザー単位のテーブルに変換する
前回振り返り
- SQLについて。現在は「仮想テーブル」を扱う操作のうち、最も柔軟に利用できる「サブクエリ」について学んでいる。前回はサブクエリの応用として、テーブル構造の変換（横持ちから縦持ち）について学んだ。
- 今回はサブクエリの応用として、テーブル構造の変換（ユーザー単位のテーブル）について書く。
サンプルの紹介（注文履歴）
- 以下のように、注文履歴がまとまったテーブル（small_order_date）を対象にする。
- ある顧客（user_id）が、１回の注文（order_id）および注文日（order_date）において、どのようなカテゴリ（item_cat）をいくらで（revenue）購入したかを管理している。
テーブル構造の変換
- 実際のデータベースも上記のように、注文履歴で管理するのが一般的だろう。
- さて、ここで顧客の特性に応じた施策を検討するため、注文履歴をユーザー単位のテーブルに変換することを考えてみる。
- SQLを用いてデータをより分析しやすい形に加工する練習のため、今まで学んだことも活用し別テーブルを作成する。
SQL実践（初回購入日・初回購入回数）
- まず新テーブルはユーザー単位に管理したいので、主キーはuser_idとする。
- 次に初回購入日（first_purchase_date）を取得。1ユーザが複数購入日を持っている場合に最も小さい日付を管理するもの。これはgroup byでuser_idを束ね、min関数でorder_dateを取得すればよい。
- 以下は、とりあえず初回購入日だけ取得してみたSQLの実践。
- あらためてSQLは、このように小分けにしてコツコツ試せるのが本当に楽しい。
- また、Big Queryはタブやコピペが直感的に使いやすく、トライ＆エラーがやりやすい。
- 次に購入回数（orders）を取得。ユニークなorder_idの個数を管理するもの。group byかつcount関数を用いる。
SQL実践（購入金額合計）
- 次に購入金額合計を取得。総額（sum_revenue）、ファッション（fashion_revenue）、雑貨（zakka_revenue）、グルメ（gourumet_revenue）を取得。
- なお、各カテゴリごとの合計は、以下のようにwhere句で行を絞り込んで集計できる。
- ただし、この方法だと後からテーブルの結合が必要になってしまう。
- そこで用いるのが、過去に学んだif句だ。（過去の記事はこちら）
- if句の構文は「if（条件式,trueの場合の値,falseの場合の値）」であり、以前は柔軟なグループ化に用いた。
- このif句は、集計関数の中で用いることもできる。例えば「sum(if…）」のように。
- これを利用して以下のようにすれば、where句を用いずに総額、各カテゴリごと合計が求められる。
- 集計関数とif句のこのような組み合わせは、SQLをスマートにできるため覚えておきたい。
SQL実践（ユーザーカテゴリ）
- ユーザーを４つのカテゴリに分類する。特定カテゴリの購入金額が、そのユーザーの購入金額の50%以上を占める場合は「ファッションユーザー」「雑貨ユーザー」「グルメユーザー」のいずれか、どのカテゴリも50%より少ない場合は「バランスユーザー」の文字列を格納。
- 分岐が多いので、この場合はcase句を用いる。かつ、上記で求めた各カテゴリの合計金額を使いたいので、それをサブクエリとして統合し、利用できるようにする。
- SQLは以下のとおり。サブクエリによる仮想テーブルの取得とcase句による条件分岐。これもSQLをスマートにするには覚えておきたい組み合わせ。
SQL実践（全ての列の統合）
- 最後に、今まで求めた要素を全て一つのSQLに統合し、一つのテーブルを生成する。
SQLにおいて重要と感じること
- 一つ一つの要素はシンプルかつ簡単。例えば上で実践したSQLも最後だけ見るとよく分からないが、過程の積み重ねを見れば一つ一つは簡単であることが分かる。
- 実際に手を動かすことが大事。初めてSQLを学んだときは「SQLは言語だから」くらいの理由で納得していた。しかし手を動かすことの真意は「作っていて楽しく、その楽しさが継続につながる」からだと、今ならわかる。
- 学びやすい環境を選ぶこと。あらためてテーブルやクエリを直感的に管理できるBig Queryの便利さを痛感している。例えば上で実践したSQLも、個々用意したクエリをコピペで最後に統合しただけ。
- あらためてBig queryのメリットは、「テーブル管理と閲覧が簡単（以下緑枠）」「クエリを複数シートで記述できる（以下赤枠）」「クエリの内容と結果が見やすくコピペもしやすい（以下青枠）」などだ。
考察
- SQLについて。現在は「仮想テーブル」を扱う操作のうち、最も柔軟に利用できる「サブクエリ」について学んでいる。今回はサブクエリの応用として、テーブル構造の変換（ユーザー単位のテーブル）について学んだ。
- 考察。「自由度」は仕事の幸福を決める大事な要素。たとえば他責の炎上案件により労働時間やタスクがコントロール不能なとき、「仕事が楽しい」と言える人は少ないだろう。私はこのような時ほど、自己研鑽の学びを求める傾向にある。それは「自由度」や「達成感」の代償行為とも言える。
- しかし、学びは事態を好転させる手段ではあるが、仕事の不自由さは根が深く即時解決は難しい。だから仕事から離れ、視野を広げ、選択肢を増やし、できるだけ思い込みを取り除き、家族と相談する時間が必要だ。このため、私は心身や家庭の事情による休暇は良い転機と捉えるようにしている。
考察のシンプル化と英訳（練習中）
- if my neighbor smokes cigarette, I try to escape or change my position. the case of uncontrollable job is same too.
- (隣人がタバコを吸うと、私は逃げるか、自分の位置を変えようとします。制御不能な仕事の場合も同じです。)
参考書籍
- 集中演習 SQL入門/木田和廣/株式会社インプレス
- 科学的な適職/鈴木 祐/株式会社クロスメディア・パブリッシング
コメント