この記事は「データベーススペシャリスト資格に興味はあるが、どのようなものか？どう学ぶのか？」という方向けに、具体的な内容と私自身の挑戦ログをお伝えする。学び中の方や、これから学ぼうとされる方の参考になれば幸いだ。
今回は知識（データモデルと専門用語）およびSQL（確認ドリル）について書く。
※この記事のSQLはGoogle Big Queryに準拠
知識（学習のしかた）
前回おさらい
- データベーススペシャリスト資格について「そもそも何？」「大事なポイントは？」「何の役に立つの？」は過去記事にてまとめ済
- 本試験について過去問ベースの学習を進めていく。
データベース設計のやり方
- 現代において業務を改善するには、データベース（データのまとまり）が必要。なぜなら、扱うデータが大量・多様なので。
- データベースを設計するには、対象世界（業務の流れと扱うデータの全量）をよく見て、「モデリングによる見える化」が必要。なぜなら、漏れを防ぎ、整合性を保ち、情報を共有するため
- モデリングによる見える化は、まず概念レベルでおこなう。ここで出来上がるものを概念データモデルと呼ぶ。表記手法でもっとも一般的なのがER図だ。試験でもこれが用いられる。ここまでの工程を論理設計と言う。
- なお、概念データモデルをリレーショナルデータベース（行と列からなる表で管理するデータベース）で実際に実装する際の設計図を論理データモデルと呼ぶ。それに基づき実際にハードウェア構成など決めることを物理設計と言う。
- ここで大事なのは、「対象業務⇒論理設計⇒物理設計」というデータベース設計のやり方と、試験ではまず論理設計はしっかり抑える必要があること。
専門用語の対処法
- データベース試験では専門用語が多様される。たとえば「関係」「属性」「射影」など。
- 理由は大きく２つ。まず、そもそもデータベースの世界が抽象概念で、言葉で表すのは非常に難しく、ゆえに数学的な概念（集合など）を用いるから。次に、試験の目的が専門家の資質を見るものなので、あえて一般にはわかりにくい専門用語で知識を測りたいから。
- ただ、抽象的過ぎては使えないので、具体的に分かりやすくする必要がある。たとえば「関係」をわかりやすくしたものが「行と列からなる表」である。
- つまり、専門用語の本質を数学的に理解する必要はなく、試験においては「関係Aに属性a,b,c」があり…と出てきたら、「関係とは表のことだな」「属性とは列のことだな」と読み替えできれば良い。
- これは学習効率という意味で、極めて大事なポイント。たとえばSQLのselectは射影と呼ぶが、数学的には凄く奥深い概念。だがこと試験においては、この概念の深堀は不要で、ただ「射影とはselectのことだ」でok。
関係データモデル
- データのまとまりは行と列からなる表形式のテーブルで管理する。テーブルは関係または表とも呼ぶ。
- 縦の列は属性とも、横の行はタプルとも、一つのセルの値はドメインとも呼ぶ。
- なお、表形式の内容を全て記述すると煩雑なので、表名と列名のみ記した表の骨格を関係スキーマと言う。たとえば「商品（商品番号、商品名、単価）」のように。
- なお、関係スキーマには試験の記述ルールがあり、これは事前に抑えておき、体になじませる必要がある。問題冊子の「関係スキーマの表記ルール」参照。
- これを少しでも雑に書くと試験で点はもらえない。「関係名が無い」「属性を（）で括ってない」「属性をカンマで区切ってない」
- インプットだけでは体得しづらいので、アウトプットを有効活用するとよい。たとえば第三者サービスによるテストなどだ。（実力テストや模試）
試験問題イメージ
- 3層スキーマ構造について。利用者から見える外部スキーマ、論理データモデルをDBMSに沿って記述した概念スキーマ、物理的な枠組みを示した内部スキーマがある。要点は、論理設計を「概念スキーマ」として物理設計と切り離しつつ、ER図など用いてわかりやすく記述する点。（H29問1）
- 対象世界を抽象化したものが概念データモデル（ER図等）。それをDBMS向けにしたものが論理データモデル（階層、ネットワーク、関係等）。これらはプロセスでなく、データに着目したアプローチ。要点は、概念データモデルの目的と位置づけ。（H26問1）
9月22日復習時追記
- 午前を中心に、ここに記載の対策は完了している。
- 概念データモデルや論理設計の「考え方そのもの」の出題は午前2で数問レベルであり、対策済。
- 「専門用語への慣れ」はアウトプット練習により、定着済。
問題036（SQL確認ドリル）
問題文
- 「sales」テーブルから、もっとも高額な「revenue」が記録されているレコードの
- 「order_id」「user_id」「revenue」を取得してください。
- （なお、もっとも高額なrevenueのデータは一つのみの前提とします）
order by,limit,selectのおさらい
- order byは並び替えをおこなう。ascが昇順、descが降順。limitは表示数を制限する。
- selectでは取得したい列を指定する。
- 今回はこれらを組み合わせ、以下のとおり。
問題036の別パターン
きっかけ
- たいへんありがたいことに、twitterのフォロワー様からお題をいただいたため、追記
- 以下、太字部分が追加のお題（条件）
問題文
- 「sales」テーブルから、もっとも高額な「revenue」が記録されているレコードの
- 「order_id」「user_id」「revenue」を取得してください。
- なお、最も高額なレコードが2レコード以上ある場合でも、それを1回で取得するよう記述してください。（色々書き方はありますが、分析関数を使って書けたらなお良いかと）
サブクエリを用いる場合
- いったんもっとも高額な値を取り出し、これを1行1列のリストとしてサブクエリにする。（以下、赤枠）
- これと同額なデータ（行）をwhere句で取得するSQLにする。結果は以下のとおり。
ウィンドウ関数について
ウィンドウ関数とは
- 分析関数やOLAP関数とも呼ぶ。
- 元の表を保持したまま、柔軟な集計ができるのが最大の特徴。
- たとえば以下のとおりgroup byとmax関数を用いて、商品カテゴリごとの最大販売額を集計したとする。結果は以下のとおり、商品カテゴリで束ねられた表になる。
- つまり単純なグループ化と集計だと、元の表は残らない。
- いっぽう、ウィンドウ関数を用いると（以下、赤枠部分）、「商品カテゴリごとの最大販売額」を求めたうえで、元の表の形を維持したたま集計結果が追加されているのがわかる。
- たとえばユーザid 10059の人（行1～3）につき、商品カテゴリ1の最大販売額8,000円、カテゴリ2の最大販売額8,800円などがくっついている（以下、青枠部分）のがわかる。
ウィンドウ関数の記述
- 最も基本なのは「関数名 over (partition by フィールド名）」。上の例では、”関数名”にmax関数、”フィールド名”に商品カテゴリ（product_id）を指定している。
- partitionはパーティションと読む。「何ごとに集計したいのか？」に該当する部分
- このほか、（）内にはorder byやwindowフレームなど指定可（詳細は後述）
ウィンドウ関数の例 rank関数
- rank関数はその名のとおりランキングを付けられる。
- 記述例は「rank() over (partition by 集計基準項目 order by 並び替え基準項目)」
- たとえば単純に「販売金額の高い順ランキング」にしたい場合。まず”partition by 集計基準項目”は省略（集計しない、素の販売金額ランキングなので）。次に”order by 並び替え基準項目”には販売金額（revenue）を指定し、高い順なので降順（desc）を指定する。
- 結果は以下のとおり。rank関数（赤枠）を用いた結果、たとえばユーザid 10059の人（青枠）の販売金額ランキングがわかる（緑枠）。
問題036の別解
- たとえばrank関数を用いて、問題036（最も高額なrevenueレコード取得。複数ある場合も1回のSQLで取得）を解いてみる。
- 考え方としては、まずrank関数でランキングをつけたあとで、「ランキング1位のデータのみ取得」とすればよい。結果は以下のとおり。
- rank関数を用いて（赤枠）、ランキング1位のデータのみ抽出（青枠）できた。
- なお、この表記で「同一販売金額が複数あるとき取得できるか」を試すため、ランキング2位のデータを取得してみる。
- 以下のとおり、ランキング2位の販売金額は10,800円で2データあるが、2つとも取得できた。
考察
- データベーススペシャリスト資格について。今回はデータモデルと専門用語の対処法について復習した。特に専門用語はわかりやすい表現への置き換えが重要だ。また、SQLの確認ドリルを解いた。お題はデータの並び替え、指定列の取得だ。
- 考察。私は、自分なりの価値観と目標を定め、コツコツ積み上げるのが好きだ。日々の安らぎと、身近な人とのふれあいも大切にしたい。例えるなら、どこに向かうのかわからないジェットコースターに飛び乗るのでなく、自分の足で、ゆっくり、景色を楽しみながら歩くようなものだろうか。
考察のシンプル化と英訳（練習中）
- I walk on my own foot with a wonderful views. itself is my happiness and my life.
- (私は素晴らしい景色を眺めながら、自分の足で歩きます。それ自体が私の幸せであり、人生です)
参考資料
- 2022年度版 ALL IN ONE パーフェクトマスター データベーススペシャリスト/TAC
- 集中演習 SQL入門/木田和廣/株式会社インプレス（SQL確認ドリル）
コメント