分析環境の作り方:MySQL編

MySQL

この記事ではMySQLを導入するところからスタートして、簡単なデータ分析を行うところまで書いていきます。

この記事の対象者

  • これまでSQLに触れたことがない
  • Excelではデータ集計したり分析したりしてみてるけどファイルが重くて面倒
  • 分析ってよくわからないけど興味ある

このような初学者を対象としています。

一部、少々冗長な記述だったり説明画像だらけの箇所もありますが、不要であれば適宜読み飛ばしていただくようお願いいたします。

なぜMySQLなのか

分析することがゴールなのではなく「手段の一つとしてツールを検討する」のであれば、比較的簡単に大量のデータを扱うことができるSQLは、少々学習にコストをかけてでも習得しておきたいものの一つだと考えます。

もちろんデータの量や種類によって適切に考えるべきなので、必ず、というわけでもありませんが、SQLという言語を習得する際の考え方はExcelやその後RやPythonを学ぶとしても有効です。

ただし、実は分析という面においてはPostgreSQLのほうが優秀と言われています(ウィンドウ関数使えたり、JOINのアルゴリズムが複数あったり、サブクエリが早い…というかMySQLが遅い)。

筆者が仕事を始めた時の環境がMySQL5.6だったことと、Google検索でヒットする件数も多い印象だったのでそのまま使い続けているのですが、あくまでSQLと考えればそこまで大きく考え方が変わるものではありません。

参考:Googleトレンドでの直近の動向(2020年8月末時点)

とはいえ、MySQL8.0からウィンドウ関数が使えるようになり、垣根が低くなっては来ているのかなとは感じているので、まずやってみるという段階であればどちらでもよいかとは思っています。

環境について

ここでは公式サイトから必要なものを取得して環境を作っていきます。

詳細は割愛しますが、XAMPPというフリーソフトをパッケージとしてまとめたものにもMySQL(厳密にはMariaDB)がパッケージングされており、そちらを利用しても差し支えないと思います。

MySQLには商用版(Enterprise Edition)とGPLライセンス版(Community Edition)があり、ここでは後者を選択します。https://www.mysql.com/downloads/

Error 403 (Forbidden)|「分かりそう」で「分からない」でも「分かった」気になれるIT用語辞典
403エラーページです。用語の意味を「ざっくりと」理解するためのIT用語辞典です。

インストールの流れ_ver8.0.21

少しわかりにくいので画像を添付しています。画像通りに進めば問題ないと思います。

Windows版を表示していますが、それぞれお使いのOSに読み替えていただければ。

ここまでくるとインストーラのダウンロードが始まります。

不要なものもあると思いますが、よくわからなければDeveloper Defaultを選択しておけば問題ありません。

画像がない箇所についてはそのままNext、Execute、Finishボタンから先へ進んでください

時間はかかりますが気長に待ちましょう。

rootユーザー:管理者アカウントのパスワードを設定してください。

すでにMySQLの導入を行ったことがある場合、Apply Configurationでエラーを起こすことがあります。
その時は一度関連ファイルなどが残ってないか調べてみてください。
筆者の場合、一行目で止まったのでProgramDataという不可視ファイル内のMySQLフォルダを削除することで動きました。

MySQLWorkbenchが起動するはずです。

お疲れさまでした。
しかし、まだこれで終わりではありません。次はデータを準備していきます。

データのインポート

分析してみたいデータがすでに手元にあるならばそちらをご利用ください。

おそらくここまで同様にインストールを行ってきた場合、「sakila」というデータベースが存在しているはずなので、それを使うのもよいと思います。

ここではダミーで分析用データを準備しました。

以下のサイトがダミーデータを作りやすかったので使用させていただきました。

Mockaroo - Random Data Generator and API Mocking Tool | JSON / CSV / SQL / Excel
A free test data generator and API mocking tool - Mockaroo lets you create custom CSV, JSON, SQL, and Excel datasets to test and demo your software.

さて、ここでは次の簡単なデータを作成しました。

  • ユーザーテーブル

ここまで長い手順を踏んできたので、まずは1テーブルだけを作成しましょう。

次に、MySQLWorkbenchを使ってインポートするために次の流れを行っていきます。

データベースを作成する→テーブルを作成する→データをインポートする

データベース作成

Connectionsから接続し、画面からデータベース(スキーマ)を作成します。
ここでは「analysis」という名前にします。以下のようにデータベースの下に作られるのがテーブルになります。次のような構造になるイメージです。

analysis(データベース)
┣users ←
┣items
┣orders
┣detail

テーブル作成

次はテーブルを作成していきます。まずは下記画像のように①Schemaタブを選択②analysisをダブルクリックして太字になったら、このデータベースに対して操作する準備が整いました。

続いてテーブルの中身を定義していきます。

①のアイコンを選択後、②で任意のテーブル名を入力、③で列の名前とその中身が数字なのか文字なのかの型情報を入れていきます。データの元の型が判明しているのであればそれを使いたいところですが、今回はデータのみ渡された想定で特に情報がないと仮定し、ざっと見て大丈夫だと思われる範囲で設定します。数値はint、文字列はverchar(30)、日付はdateやdatetime、わからなかったらとりあえずvarchar()と設定します。()の中身はデータが収まりそうな文字数に読み替えてください。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 11 データ型

ただ、もちろん正しい型にデータを入れるのがスマートですが、必ずしも自身の思った通りにデータが手に入るわけではなく、型を変更したりデータを加工したりすることもあります。リアルタイムで動くシステムではなく、分析ツールとしていろいろ試せるのであればまず手を動かしてみて、こういう時はうまくいかないのかなど細かく実験を繰り返すのが一番理解が深まるのではないかと考えます。

サンプル ユーザーIDは一意の値だったのでPKとNNにチェックしてますが、この場はチェックせず後でも設定可能です

データのインポート

作ったテーブルにデータを入れていきます。

インポートできるのはCSVかJSONとなります。

今さら聞けないIT用語:CSVファイルとExcelファイルって何が違うの? 使い分け方法や注意点も含めて解説します! | データのじかん
データのやり取りで頻繁に使われるCSVファイル。普段何気なく使っているもののExcelファイルとどう違うのかと疑問に思う方も少なくないのではないでしょうか。実はCSVファイルとExcelファイルでは性質やできることが異な …
JSONってなにもの?
JSONとは何か?JSONとはJavaScript Object Notationの略で、XMLなどと同様のテキストベースのデータフォーマットです。その名前の由来の通りJSONはJavaScriptのオブジェクト表記構文のサブセットとなっており、XMLと比べると簡潔に構造化されたデータを記述すること

作成したテーブルで右クリック→Table Data Import Wizardを選択します。

インポートしたいファイルのパスを選択してインポートを行います。問題がなければNextを押していけば完了するはずです。

データのインポート時にCan’t analyze file.Please try to change encoding type.と表示される場合、①ファイルの文字コードがUTF-8以外②インポートしたいデータのカラム名が英語
このいずれかが該当すると思うので、①UTF-8に変換②英語に直す

データの確認

データがうまく取り込めたか確認します。

テーブルにカーソルを合わせると、表に雷マークがついたアイコンが表示されるので、それをクリックします。

画面真ん中にデータが表示されれば完了です。その調子でほかのテーブルがあれば作成していきましょう。

データの分析について

さて、本題はここからです。

分析とはなんぞや、的な話はここでは割愛します。

まずはデータを軽く眺めていきます。

それだけで何か仮説を立てたり立証したりできるならば素晴らしい観察力の持ち主ですが、なかなかそうはいかないと思います。

ここからは事前に説明したユーザーデータをベースに考えますが、なんとなく見ていると

  • 男性と女性はどちらのほうが多いのだろう?
  • 都道府県で多いのはどこだろう?
  • 年代で多いのはどの世代だろう?
  • 組み合わせで見てみると何が見えるだろう?

などなど、簡単に眺めるだけでもいろいろな疑問が浮かんできませんか?

まずは簡単に集計をしていきます。性別で見ていきましょう。

select * from ユーザーテーブル
where 性別 = "M";

このようにエディタ内に記述をして、上にある雷アイコンをクリックすると実行されます。
スペースは半角で入力してください。

さて、ここでは性別がM=男性である情報をすべて(「*」)表示する
といった書き方になります。

もし、中身ではなく、何件データがあるのか知りたいのであれば

select count(*) from ユーザーテーブル
where 性別 = "M";

これで何件データがあるのか表示されます。

もし、性別を限定したくない場合は

select 性別,count(*) from ユーザーテーブル
group by 性別;

where = “性別”の部分を削除し、代わりにgroup by 性別と書くと、性別列の中身を重複を省く=グルーピングします。グルーピングしたものの中味が何件あるのかをcountする、という文です。

性別は男女で半々くらいというところはわかったので、一旦置いておいて年齢を見ていきましょう。

ちなみに実行する時に雷アイコンが二つならんでいると思いますが、左側は書いてある文を上から下まですべて実行する、もしくは選択している範囲だけを実行するもの
右側はカーソルが当たっている部分の1件だけを実行するものになります。状況に合わせて使い分けをしてください。

select 年齢,count(*) from ユーザーテーブル
group by 年齢;

同じようにグルーピングをして件数を出していますが、なんだか見づらいですね。まずはきれいに並べてみましょう。

select 年齢,count(*) from ユーザーテーブル
group by 年齢 order by 年齢 asc;

order by にソートしたい列名と小さいほうから並べるascを記述します。ただ、これでは一歳刻みなので見ずらいですね。年齢を10歳刻みで出してみましょう。ちょっと長いです。

select
case
 when 年齢 between 20 and 30 then "20代"
 when 年齢 between 30 and 40 then "30代"
 when 年齢 between 40 and 50 then "40代"
 when 年齢 between 50 and 60 then "50代"
 when 年齢 between 60 and 70 then "60代"
 when 年齢 between 70 and 80 then "70代"
 when 年齢 between 80 and 90 then "80代"
else null end as 年代
,count(*) 
from ユーザーテーブル
group by 
case
 when 年齢 between 20 and 30 then "20代"
 when 年齢 between 30 and 40 then "30代"
 when 年齢 between 40 and 50 then "40代"
 when 年齢 between 50 and 60 then "50代"
 when 年齢 between 60 and 70 then "60代"
 when 年齢 between 70 and 80 then "70代"
 when 年齢 between 80 and 90 then "80代"
else null end
order by 年齢 asc;

case when 「この条件に当てはまるデータがあったら」then 「こう定義して扱う」
else 「一致しなければこう定義する」 end

最初は書くのが面倒だということもわかりますし、これくらいだったらExcelでやったほうが早いのでは?とも思うかもしれませんが、セルによって条件を変えたり参照セルを変えるのが地味に面倒だと思う方はSQLをお勧めします。

年代と性別のクロス集計です。count(*)部分を書き換えていきます。

select
case
when 年齢 between 20 and 30 then "20代"
when 年齢 between 30 and 40 then "30代"
when 年齢 between 40 and 50 then "40代"
when 年齢 between 50 and 60 then "50代"
when 年齢 between 60 and 70 then "60代"
when 年齢 between 70 and 80 then "70代"
when 年齢 between 80 and 90 then "80代"
else null end as 年代
,count(case when 性別 = "M" then "男性" else null end)as 男性
,count(case when 性別 = "F" then "女性" else null end)as 女性
from ユーザーテーブル
group by case
when 年齢 between 20 and 30 then "20代"
when 年齢 between 30 and 40 then "30代"
when 年齢 between 40 and 50 then "40代"
when 年齢 between 50 and 60 then "50代"
when 年齢 between 60 and 70 then "60代"
when 年齢 between 70 and 80 then "70代"
when 年齢 between 80 and 90 then "80代"
else null end
order by 年齢 asc;

だんだん難しくなってきますがcountの中にもcase whenが書けるので、そこで条件指定していきます。

ここまで来たら、だいぶデータが整理できてきたのでExcelやスプレッドシートなどに張り付けてグラフにでもして眺めてみましょう。表示されたデータ部分で右クリックを行い、Copy Rowでコピーして貼り付けてみましょう。ここで何かわかることがあれば掘り下げたり、ほかのデータを見てみるなどを行います。

これまでに記載した方法は、データから「何か見えるものはないか?」を探ろうとしました。
しかし、特に言えることがない時「どこまで掘り下げる?」が付いて回り、見つかるまでのループに陥ることがあります。
情報がない状態でのスタートであればまずは眺めたり集計したりすることは有効ですが、
できれば常に「解決しなければいけない問題は何か」を考え、
それを解決するために「仮説を立て、検証するためにデータを見る」という流れが理想的です。
これにより「どこまでやるの?」→「仮説が正しい」or「間違っている」
と判断できるものがそろえばOK、がゴールになります。

まとめ

MySQLの導入と簡単に分析について説明しました。

結局のところはどんなツールでも使いやすいもの、要件に合うものを選択するのがよいのですが、差し迫った事情がないのであれば少し背伸びしてでも扱ってみることが経験値になり、どこかで役に立つかもしれません。

このサイトでは、一つの物事にこだわらずいろんな視点で発信していきますのでよろしくお願いします。

コメント

タイトルとURLをコピーしました