動機

joinやorderbyやら、概念はわかるけどちゃんと呼吸するように書くことはできない
しかも普段ORMで雰囲気でやっているので、もっとSQLの特にqueryの書き方の知識を深めたかった

特にわかっていないキーワードを羅列しておく。読み終わった後、これについて説明できるようになっているとよい

• サブクエリ
• join

メモ

• selectでは存在しないcolumn名を指定できる

  • select 100 from XXX とかやると、レコードの数だけに100の入った結果が得られる。f0_とか表示されてた

• selectでは、columnの値で演算できる

  • select quantity, revenue, revenue / quantity のような指定もできる

• selectのasでつけた別名はorder byでは参照できるが、whereでは参照できない

• booleanカラムにis not trueとかやるとnullも帰ってくるので注意

• except(カラム名)でselectで取得しないという処理ができる

• 分析のためにはグループ化の概念が大事

  • 集めた基準ごとにデータの平均をとったり合計取ったりして他と比べることで分析を行う
  • group byにselectで指定した内容と同じものを投げるとまとめることができる
  • グループ化の対象にしないカラムをselectに指定してはいけない
    • selectで逆にグループ化対象のカラムを指定しない場合、結果には表示されないがqueryは実行できる
  • 金額や販売金額といった量的なデータは集計関数で集計しないといけない
  • group byに複数指定した場合は、取りうる組み合わせ分のrecordが帰る

• 集計関数

  • COUNT
    • 個数を数える
    • COUNT(DISTINCT column名)とかもいける
      • 重複を排除した個数を調べる
    • nullはカウントしない
  • SUM, AVG, MAX, MINは名前のまんま
  • 標準偏差を求める、STDDEV_POPや募集号の一部から標準偏差を求めるSTDDEV_SAMPなんかもある
    • 統計集計関数というんですって

• 集計結果の絞り込み、HAVING

  • where句はorder byより先に来るので、order byでグループ化した後には使えない
  • この時にhavingで条件を指定して絞り込む
  • from > where > group by > having > select > order by > limitの順で実行される
    • limitは表示するレコードを決めているだけで、処理が速くなるとかはなさそうだ

• より柔軟なグループ化

  • CASEとIFを覚える
  • IF
    • IF (cond, true case, false case)
    • condにはorやandも使える
  • IFをif elseのように複数使いたいときに使うのがCASE文
  • CASE WHEN cond THEN ... END
    • ELSEでいずれにも一致しなかった分岐
    • 大小比較も、IN、BETWEENも使える

sqlの例。10回以上pageviewのあるユーザ絞り込み
recordとpageviewsが1:1なのでこの場合はcountそのままでよい（重複を消すときはdistinctをつける）

select user_id, count(*) as pageviews
from sample.web_log
where media = "email" 
and user_id is not null
group by user_id
having pageviews >= 10

• テーブルの結合

  • JOINはご存じテーブルをつないで横に伸ばすあれ
  • 一方UNIONは縦につなぐらしい（使ったことない）

• join四種類

  • INNER JOIN 内部結合
    • よく使うのがこれ
    • 二つのテーブルが共通して持つカラムの値が重なっているレコードのみを残す
  • LEFT OUTER JOIN 左外部結合
    • 先に指定したテーブルを左側にする
    • 右側は左側と値が重なってるレコードのみ表示
  • RIGHT OUTER JOIN 右外部結合
    • 先に指定したテーブルを左側にする
    • 右側が全部。左側は重なってるレコードのみ
  • FULL OUTER JOIN
    • 二つのテーブルにある全レコードを残す

基本的な構文は以下のような感じ

select * from tbl_a inner join tbl_b on tbl_a.id = tbl_b.id

onの代わりにusing(id)のような書き方もできる
onのときは各テーブルのカラムが残るが、usingの場合カラムが一つにまとめられる

on の条件はandで複数指定することができる。ここをさぼるとレコードが増えたりするので、クエリの実行後の件数には気を配ったほうが良いらしい

• self join

  • 同一テーブルに年度ごとの売り上げがあるとき、前の年との売り上げの比較などをしたいときに使う
  • ただしself joinという構文はなく、inner join の対象がどっちも同じtableになる
  • onで算術演算子が使えるので tbl.year = tbl.year - 1 みたいに書ける
  • そのままだといっぱい出てくるので、where句で左側の条件を絞るとすっきりする

• IFNULL関数

  • select句で使える。大量のカラムがnullだった時に別の値を入れることができる

• COALESCE関数

  • 置き換え対象を固定値ではなく、指定したカラムの値が設定される

• unionは結合ではなく集合演算

  • 集合同士の足し算
  • 集合演算なので差分をとるEXCEPT、重複をとるINTERSECTもある
  • 同じスキーマのテーブルで使うが、ビッグデータでは月ごとにテーブルを切ることがある
  • そういう時に先月も今月もあったレコード、とか先月は売れたが今月は売れてないレコードといったものを探す
  • 構文
    • selct * from tbl1 union distinct(もしくはunion all) select * from tbl2
    • distinctかallかは、双方に重複がないときは結果が変わらない
      • ちなみに重複はselectのパラメタの内容すべてに対してである。selectに指定しなかった内容は考慮されない
    • field名は一致が必要

• 仮想テーブル

  • なにがしかのSQLの結果をテーブルとして扱い、それに対してクエリを投げることができる
  • with 仮想テーブル名 as (仮想テーブルを作るSQL)で仮想テーブルを作成できる
  • withの後ろの仮想テーブル名 as (SQL) の組み合わせは複数作れる

• cross join

  • テーブルのレコードの組み合わせすべて分のレコードを生成するjoin
  • 平均値のみを出力するSQLをwithにたたいて、それをcross joinすると対象のテーブルのレコードに平均値カラムを追加した結果が得られる

select product_id from qty_jul_sep
except distinct
select product_id from qty_oct_dec

これで差集合演算, distinctをつけると重複が消える

• サブクエリ

• 仮想テーブルを使うWITH以外の方法

  • WHERE句でもHAVING句でも、SELECTの対象指定でも使える
  • WHERE inの対象にしたり、集計関数で値にしてから比較演算に使ったり

• 縦持ちと横持ち

  • この変換はサブクエリの用途として多いらしい
  • 縦持ちテーブルはよくあるrecord
    • 東京、H29, 986円
    • 東京、H30, 1094円
    • みたいな感じ
    • 年が増えるとレコードが増える
    • 一般的なのはこっち
  • 横持ちテーブルは、縦持ちで重複のあるカラムをまとめて集計したもの
    • 東京, 958(H29の最低賃金), 985(H30の最低賃金),998(H31の最低賃金)
    • みたいな感じ
    • 年が増えるとカラムが増える
  • 横持ちテーブルへのselectとunionを使うと縦持ちにできる
  • table構造を変えることで、集計などが行いやすくなる

• メモ

  • レコード一つの仮想テーブルでも、計算とかで扱いたいときはmaxを通す必要がある（minでもいけそう）。要するにscalaにしないとだめ
  • [val]かvalかってこっちゃな
  • having句でも算術演算はできるし、集計関数も使える
  • group byでグループ化するとき、対象のカラムは集計関数に投げる必要がある
    • record一つの場合は、maxやavgを通す必要がある(1つを集計する)
    • 複数レコードの同一の値をまとめるという意味もある