怖がらずに触ってみよう!SQLの世界

MySQL

みなさんExcel使ってますか?私はバリバリ使ってます。

ただいくつか不満に思うこともあったりするのです。

  • データが大量にあると動作が重い
  • 関数が大量に入ってると動作が重い
  • かなり凝ったものができて、しばらく経った後にメンテする時に「あれ、どこ触ったらいいんだっけ?」

などなど。

また、私の場合は取り扱うデータサイズが大きいものが多く、Excelの最大行数を越えることがあったため必然的に別の環境を選択することが増えました。

Excel の仕様と制限
Excel 2010 では、ワークシートの最大サイズは16384列あたり1048576行です。 この記事には、ブック、ワークシート、機能に関するすべての仕様と制限が記載されています。

別にAccessでもいいんじゃない?と検討したところ、こちらも1テーブルの容量に制限があるため、対象から除外しました。

Access の仕様
最大ファイル サイズ、テーブルあたりの最大フィールド数など、Access 2016 データベース ファイルおよびオブジェクトの制限と仕様について確認します。

というわけでRDBS(リレーショナルデータベースシステム)の世界へ飛び込むことになりました。

実際に触って見ると難しいと思う反面、実は集計もこなせるので次第にデータの集計はSQLで、グラフなどの表示はExcelで、という使い方になっていきました。

最初に覚えたこと

まずは人が書いたものをみて実際に動かして見て、をひたすら繰り返しました。

最初は何を実行するのも「壊れないかな、大丈夫かな」と思いながら動かしていましたが、どうやら次の文章から始まるものはどう動かしてもおそらく大丈夫そうだということはわかりました。

select   #この文章から書きだす

この「select」を文の頭につけて書くと、データベースという箱の中から何かしらを表示できます。逆にいうと表示しかできないので、更新したり削除したりはできません。

また、書き方としては大きく

  • 全部表示するか
  • 一部だけ選んで表示するか
  • 自分で定義したものを表示するか

ができることがわかりました。具体的には次のような感じです。

select * from テーブル名;#全部の列を表示
select 列名1,列名2,列名28  from テーブル名;#任意の列を表示
select "文字1","文字2" ;#そもそも存在しないものを表示したり

それがわかると怖さもだいぶ和らぎましたので、ひたすら表示を繰り返して眺めていました。

次に試したこと

流石にただ表示させるだけではすぐに飽きがきてしまいました。

次はwhereをつかってみました。これは言うなればExcelのフィルタのようなもので、条件を入力することで必要なものを表示したり、逆に除外したりできるものです。

これが案外奥が深く、しばらく実験を繰り返していました。例えば次のようなデータがあったとして

この中のidが1と4の行を拾ってこようとすると

select * from test
where id_数字 = 1 and id_数字 = 4;

この書き方では上手くいきません。1と4を拾ってこようとしたのに。
正しくはこんな感じで

select * from test
where id_数字 = 1 or id_数字 = 4;
#とか
select * from test
where id_数字 in (1,4);

andを指定すると、1「かつ」4の条件に合うものを探そうとするので、そんなものはないから、と表示されません。つまりフィルタの設定ミスです。

この場合、orを書くと1「または」4の条件に合うものを探すので表示されます。

論理演算とは:集合は、ベン図で理解しろ|データ分析用語を解説 - GiXo Ltd.
本記事は、株式会社ギックスの運営していた分析情報サイト graffe/グラーフ より移設されました(2019/7/1) 目次1 論理演算を理解して、矛盾のないデータ分析を...

なんとなくこの時はandは列をまたいだ条件を指定するものなんだなと理解しました。

今度は逆に特定のもの以外を表示したいと思いました。一致しないものを表示する時は

select * from test
where id_数字 != 1;

これで1以外の行が表示されました。

今度は1と4以外のデータを取得しようとしました。

select * from test
where id_数字 != 1 or id_数字 != 4;

先ほど1と4の行を表示した時の文をベースに「!」をつけましたが、これでは上手くいきません。

1以外のもの「もしくは」4以外のものを指定しているので、結果としては1以外を指定した時に2から4が、4以外を指定した時に1から3が、つまり全部表示されてしまいました。

select * from test
where id_数字 != 1 and id_数字 != 4;

こういう時はandを使うと正しく表示されます。

ただ、この辺の書き方は油断してると慣れてきてもうっかりしてしまうので、

select * from test
where id_数字 not in (1,4);

このように書くほうがわかりやすくていいと思います。

詳細は割愛しますが、照合順序というものが理解できておらず、例えば「ぱ」という文字を検索したら「は」という文字が一致したり(逆もあり)意味がわからないこともたくさんありました。

最初の頃はできるだけミスしないように、小さいところから徐々に条件を加えて確認を繰り返していました。

今度は集計にチャレンジ

Excelのようにsumとかcountなどが使えることがわかったので、集計をおこなっていくことにしました。

実際、分析などを行う時はselect と where だけで表示するだけで終わり、ということはあまりなく、大部分が集計を行なうことになります。

まずは先ほどのテーブルから

数値の列のデータを合計します。

select sum(id_数字) from test;

合計10件になりました。
次は特定のグループごとの金額を合計したいと考えました。

例えばこのようなデータがあったとして、ユーザーごとに合計の金額を出したい時。
Excelであればsumif関数があればできそうです。そのためにはユーザー名の重複を排除したものを用意して、関数を書いて…と少ないデータ量であれば簡単ですが若干手間がかかります。

select ユーザー名,sum(金額) from test2
思った通りに表示されていない

正しくはユーザーAからCまでが並んでそれぞれの合計を見たいところですが、このような書き方では上手くいきません。sumなどの集計関数は特に条件を指定しなければ全部ひとまとめにしてしまうのです。

select ユーザー名,sum(金額) from test2
group by ユーザー名

このようにgroup byを書いてグループにしたい列名を指定してあげれば、簡単に表示することができます。

また、ユーザー名とより細かい条件、例えば日付などで年を分けたい場合など

select ユーザー名,substring(日付,1,4),sum(金額) from test2
group by ユーザー名,substring(日付,1,4)

少々複雑になってきましたが、substringはある文字からある文字の間を切り出すものです。

このようにgroup byを「,」で区切って複数指定すると、グルーピングの条件を細かく指定できます。

select ユーザー名,substring(日付,1,4),sum(金額) from test2
where 金額 >1000
group by ユーザー名,substring(日付,1,4)

where との組み合わせで、1000円より大きな金額を持ったデータでグルーピングを行なうなどもできます。

先にwhere の条件が優先され、そのあとグルーピングされたのちにsum関数が動く、といった流れになります。この流れ、実はかなり重要な考え方なのですが、ひとまずここでは割愛します。

group byが使えると、かなりいろんな条件での集計が可能になるのでなんども繰り返して試して見ると良いと思います。

まとめ

私が勉強を始めた時、そもそも英語を書くことにすら抵抗があり、周りに聞ける人もいなかったので最初はかなりしんどい思いをしましたが、人間慣れるもので、今となってはこのようなデータベースシステムなしでデータ取り扱うなんて考えられません。

勉強を始めたばかりの人に何か伝えられるものがあればと思い書いてみました。
もし何か少しでもお役に立てれば幸いです。

ちょっとしたことで、誰かに聞けずに困っていたらぜひ質問などいただければ!
わかる範囲で答えさせていただきます。

それでは!

コメント

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