先日、とあるきっかけでSQL に触ることになった。
初めてやってみて、つまずいたことや触ってみた実感を書き残しておく。
こういう系の技術ブろグは Qiita に載せるべきなのであろうが、まずはアウトプット欲が冷めないうちに、体裁を気にせず自分のサイトに書き残しておきたい。
Excel の経験があったので、SQLの概念は比較的スムーズに入ってきた
Excelは、いろいろなデータを縦と横の二次元の形で整理し、時には表やグラフとして可視化するソフトだ。
たくさんあるExcel のマスは、行番号と列番号で区別される。
「行」は横方向に走るラインのこと、列は縦方向のラインのこと、というのは基本だ。
Excelなどを扱う中で、データ構造の基本を理解していない状態で SQL でデータを操作するのはハードルが高いように思われる。
例えば、クラスの出席表を考える。
出席表の一番上の行は、各列にどんな種類のデータが入っているのかを示す「見出し」の列だ。
出席表に乗っている情報として、出席番号、苗字、名前、誕生日を考える。
| 出席番号 | 苗字 | 名前 | 誕生日 |
| 1 | 青井 | 太郎 | 12.23 |
| 2 | 石田 | 二郎 | 7.8 |
| 3 | 上野 | 花子 | 9.3 |
上から順に、1,青木,太郎,12.23生まれ…みたいに名簿形式でデータが入っている。
この出席表では、一つの行は同じ人に紐づく情報を表している。
例えば誰か転校生がやってきたとしたら、「行を追加する」という操作になる。
名簿に性別の情報を付けくわえたいときは、性別に関する「列を追加する」という操作になる。
データには多くの場合、「ID」がつけられている。IDは、データセットの中で重複することはなく、IDさえ分かれば、他の情報が1通りに決まる。
クラスの出席名簿の中でのIDは、ずばり出席番号だ。
「名前で区別できる」と思われるかもしれないが、同姓同名の人がクラスに複数人いる可能性もある。
それに、データを区別する上で、「人名」という形式は非常に扱いづらい。
であるので、IDには多く場合、アルファベットや数字が連続して振られるケースが多い。
以上が、データに関する基本的な概念だ。この部分はデータを操作する上での基本であり、この部分の理解なくしてSQLの理解はない。
どんな状況でSQLを触ることになったか。+作業環境
「SQL やデータべース周りに強くなりたいのだが、データを扱う機会がない。どうすれば、そんな仕事をする機会が巡ってくるのか？」と気になる人もいるかもしれないので、自分自身がSQLを触ることになったきっかけについて触れておきたい。
web広告のインターン（当方は大学生です）
僕がSQLを触ったのは、web広告のデータを扱う業務の一環だ。
Google とか yahoo とかで検索したときに、サイトの結果表示の上の方に違った色で表示されるweb広告（検索広告）。
そういう「一定の予算で検索広告を打つことで、webから集客したい」というお客様のために、web広告についての専門知識を生かしてweb広告を運用するのが僕がインターンで関わっている仕事だ。
広告の予算は無限にあるわけではないので、「一定の予算で成果を最大化」するために色々分析する必要がある。
例えば、何回広告が表示されて、そのうち何回クリックされたかという「クリック率」など。
web広告に関する様々なデータを可視化し、お客様に毎月成果を報告している。その業務を一部自動化するために、今回SQLを触ることになった。
BIg Queryにデータをストック
上記のような流れでSQLを触ってデータをいじるのは割とレアな方で、たいていはwebサイトのデータの出し入れとかでSQLを触ることが多いのかもしれない。（第一線のエンジニアではないので、見当はずれかもしれないが）
今回のSQLは、Googleの「Big Query」というサービス上で動かした。
BIg Query とはGoogle のデータベースサービスで、容量の大きなビッグデータを高速で解析できるのが特徴。
今回の業務では、毎分、毎時間、毎日動いているweb広告の成果に関するデータがGoogle のBig Query上に吐き出されるようになっていて、そのデータから広告レポートに必要なものを、予め書いておいたSQL文で自動で抽出してレポート化するという仕組みを作ろうとしている。
（google検索広告, yahoo広告, facebook広告, LINE広告 など、様々な媒体にまたがるweb広告のデータを、比較可能な形でBIg Queryに吐き出すために、とあるサービスを利用している。そして、BIg Query のデータベースにストックされたデータは、「Google データポータル」というBI ツールを使ってデータを可視化している」）
ここからは、初心者がSQLを触りながらとりあえず覚えたことについて書いていきたい。
Select 文はsqlの基本でありながらも、意外と難しい
SQLを学ぶにあたって、「キホンのキ」になるのが select 文だ。
初めてSQLを触るにあたって、select 文を使った初心者向けのサイトをいくつか読んできた。
説明文にあるような具体的な状況で、手を動かしながらselect 文を「分かった気」になったけれど、実はその本質に迫るのは意外と難しいのが、select 文なんじゃないかと感じた。
数値が入った列A（例えば、商品単価）と列B （例えば、販売数）があって、それらの数値をかけ算して列C（売上金額の列）を作るとする。
このときに使うのも「select」文だ。このようにselect文は、既存のデータにない新たな列を「追加」するためにも使ったりする。
列の追加なら「add」文とか考えそうになるけれど、使うはあくまでもselect文だ。
sql文を色々さわりながら、僕はselect文を次のように理解した。
まず、データベースがあるとする。
このデータベースには、知りたい数値（あるいは、テキスト列）から、分析にはそんなに必要のない列までまぜこぜに入っている。
普段、sql を使って向き合うのは、こういった「必要なものも不要なものも混ざったデータベース」だ。
ここから、必要な列だけを「select」して取り出し、select した列のデータだけから成る表を作るための命令が「select」文なのだ。
そして select 文は、一つあるいは複数の既存のデータを一定の規則で処理して、一つの新たな列として選択して取り出すという機能もある。
これが、「既存の列から新しい列を作るときに、select 文を使う」ということだ。
select した列に「As」で名前を付ける
例えば元データに、「A」と「B」という名前が付けられたカラム（列）があったとする。
select A + B 見たいなsql文を書いて、A列とB列の数値を足し合わせた新しい列を作ったとする。
この時、あたしく作られた列の名前は「A + B」というそのままの名前が自動でつけられる。
元データに対してselect 文をかけて実際に表を作る場面では、新しく作った列の名前を自分で分かりやすくつける方が便利だ。
そんな時に「As (つけたい名前)」 とすることで、新しく生成した列に自分でつけた名前が与えられる。
＊で全部を選べる
なんだかんだで、元データにある列をそのまま全部使いたいこともあるはずだ。
そんな時に、すべての列の名前をいちいち入力していては、日が暮れてしまう。
そんな時に、「Select *」とすれば、元データのすべての列を select することができる。
同じ文字列が入った列を追加
「データの統合」で後々必要になるが、今の自分の持っているデータに、同じ文字列が入った列を追加したいこともあるはずだ。
例えば、複数のクラスの名簿のデータがあって、「●組」という情報が入った列はもともと存在していないとする。
この時、「Select “1組” As クラス名」とすることで、「クラス名」という列が追加され、この全員に「1組」という情報を持たせることができる。
FROMで、元データを選択する
ただ select 文を書いたとしても、どれが元データか分からない。
そんな時に、 Select ~ From Class_meibo とすることで、「Class_meibo」(クラス名簿)という元データから自分のほしいカラム（列）を取り出すことができる。
不要なカンマは入れない、大文字や改行は関係ない
Select ~ As ~ From と書いていると、プログラミングにこなれた人ならそれぞれの区切りにカンマ「,」を入れたくなるかもしれない。
自分自身も、不要なカンマを入れてしまった何度かエラーを食らった。
けれども、複数の列を select するときなど、並列する要素を列挙するときはカンマで区切らないといけない。
python だったら、不用意なインデントでエラーを食らうこともある。しかしながら、sql の各要素は特に改行したりせず、スペースを空けながら一気に書くこともできる。
スペースだって、いくつ空けるかは（たしか）処理に影響しないはずだ。
それでも、後にコードをいじるときに分かりやすく読めるための工夫として、適宜改行やインデントを付けて見やすいコードを書くようにしたい。
マージとユニオンの違い
データを扱う際仕事には、出所な別々のデータを統合するという作業が発生することもある。
「データを統合する」と一言に行っても、実は大きく分けて2種類ある。それは、「マージ」と「ユニオン」だ。
データに列を追加する統合が「マージ」
データを統合すること全般を「マージする」という人がいるけれども、データをマージするとは、複数の表の情報を突き合わせて、列についての情報を増やすことだ。
例えば、「表 A : 商品ID. 商品の売り上げ」、「表B : 商品名,商品ID」という二つの表を持っていたとする。
これらを「マージ」することで新たな表Cを作る際ると、「商品ID, 商品名, 商品の売り上げ」という3つの列を持った表が誕生することになる。（表Cは、表Aに商品名の列を追加するイメージ）
このとき、データの行は増えたりはしない。なぜなら、表Bは 表Aの商品IDについての情報を詳しくするためのデータであり、未知だった商品の売り上げデータが追加されたわけではないのだ。
データに行を追加する統合が「ユニオン」
列を追加したならば、行を追加するという操作もある。これが「ユニオン」という操作だ。
先ほど少しふれたように、「各クラスの名簿を統合して、学年全体の名簿をつくる」というのがユニオンだ。
この時、ユニオンしたい各データの列の名前や順序、列の値の形式はそろっている必要がある。
新たに列が増えるわけではないが、行が増えて名簿に載っている人間の数が増える。これがユニオンだ。
データの統合、行を追加する : UNION ALL
上記の操作をsql で行う時に使うのが、「UNION ALL」という命令だ。
（ALL がつかない単なる UNION は、重複する行で不要な方が削除されてしまう。UNION ALL は、重複するデータもとりあえずユニオンしてしまう）
僕は初めてsql を触る際に、UNION ALL が一番役に立ったと思う。
僕の業務では、「Google 検索広告」「Yahoo 検索広告」についてのデータをユニオンして、検索媒体ごとの成果の違いを可視化したり、全媒体の合計クリック数を出したりした。
UNION ALL でエラーが出た時の対処、そのたつまずいたところ
select するカラムの順序は同じにする
UNION ALL は、統合するすべてのデータの列の名前、列の順序などがすべて同じでないと、データを統合できない。
select 文では、最初から書いた列から先に選択されるので、元データからselect する → UNION ALL する 時は、各表に対する select の順序を同じにしないといけない。
日本語の名を付けたらエラー
先ほど Select ~ As … みたいな構文を紹介したが、As の後に日本語の名前を付けたらエラーになった気がする。（実行環境は、google データポータルのデータソースのsql 画面）
sqlに限らず、プログラミング全般でオブジェクトの名前を設定するときは、半角英数字が無難だ。スペースが入った名前もやめておいた方がいいだろう。
select の後にcase文を書いて、条件に応じた値が入った新しい列を生成した
そういえばcase文も書いたので、そのことにも触れておきたい。
case文の正しい書き方の詳細は他のサイトなどで見てほしいが、今回はつまずいた部分や印象に残った部分を書いておきたい。
nullについて
else を明示しなければ、null が記入される。Else THEN “null” と記入する場合は注意。
“null” (文字列リテラル) とnull (null型) は見た目が同じでも、型が違うから違うものなので同じものとして認識されないことにも注意したい。
カッコを付けて、case とunion , select の計算の順序を明示する
select で既存の列を条件分岐で生成したいときに case 文を使い、それらを UNION ALL で合わせるという作業をした。
これをやろうと思って、カッコを付けていないプログラムをかいてそのまま実行してみると、エラーが生じた。
これはおそらく、計算の順序が明示されていないので、上から順に処理されてしまい、列がそろっていない状態で UNION ALL の処理が行われたためだと思われる。
そこで、カッコを付けて処理のブロックを明確にし、計算の優先順序を明示することが必要だ。
select文で生成した表をfrom で選ぶ
カッコを付けただけでも、計算の順序がうまくいかないこともあった。
そこで、次のようなsql 文をかいた。
Select *
From (Select *, case文での処理 From データ1)
UNION ALL
Select *
From (Select *, case文での処理 From データ2)
これによって、データ1と2に対して同じような方法で列を追加し、列の並びをそろえた状態でUNION ALL することで、思った通りの処理が実現できた。
