まさ＠ブログ書き込み中

自由に生きるための英語、プログラミング、考えごとについて色々書いています。

『SQL ゼロからはじめるデーターベース操作』を読んで

ISUCON

 

こんばんは。まさです。

 

さきほど『SQL ゼロからはじめるデータベース操作』を読み終えました。

CD付 SQL ゼロからはじめるデータベース操作 (プログラミング学習シリーズ)

CD付 SQL ゼロからはじめるデータベース操作 (プログラミング学習シリーズ)

 

 

本書の内容についてつらつらとまとめたり、語ったりしていきます。

 

 

「SQLマスターになりましたわ」とはならないよ

本書では丁寧にSQLについて教えてくれますが、別にこれを読み終えたからと言って「俺は色んなことができるようになるぞ！」と強く感じるわけではないです。

 

僕がそのように感じたのは①SQLはアプリケーションを書くためのプログラミング言語と比べてその対象がDB操作という意味で狭まっているから、②僕が今回習ったいくつかのSQLがどれだけ便利かイメージできていないことに原因があると思います。

 

 

体系的に学べたのは良かった

僕はこのような本を今まで読んでいなかったのでざっくりとSQLを使っていたわけです。ググって使うって感じ。

 

それはそれで良いと思うのですが「SUMを使う」みたいな感覚から「集約関数であるSUM関数を使う」と頭の中で整理できるようになった、つまり体系的にSQLについて学べたのは本書を学ぶことの大きなメリットでした。

 

また、集約関数の中でCOUNT（ * ）のみNULLを含めた全行を対象とするとか、それぞれの句に書いてはいけない注意点など細かいことも知ることができてよかったです。

 

では、具体的な内容についてまとめていきます。 

 

 

本書前半（第1章〜第4章）で学んだSQL

補足説明と共にざっくり列挙すると以下にまとめられます。

  • CREATE DATABASE
  • CREATE(DROP) TABLE
  • ALTER TABLE
  • SELECT AS
  • WHERE
    条件にNULLを使うならIS NULLまたはIS NOT NULL
  • 集約関数（COUNT, SUM, AVG, MAX, MIN）
    COUNT( * )以外はNULLを除外
  • GROUP BY
    集約キー（GROUP BYに指定する列）以外の列名をSELECT句に書いてはいけない
  • HAVING
    WHEREが行に対する条件指定なのに対し、HAVINGは集約結果（グループ）に対する条件指定
  • ORDER BY
    SELECTの後にORDERされるのでSELECT句でつけた別名が使える
  • INSERT
    列リスト省略可能、デフォルト値を与えている列には列リストとVALUESの省略が可能
    INSERT SELECTでほかのテーブルからデータをコピーすることが可能
  • DELETE
    DELETE文の削除対象はテーブルや列ではなくレコード、TRUNCATEというのもある
  • UPDATE SET
    SET句は列名＝式

 

データベースやテーブルを作成したり削除したり、データに関するCRUD操作など、基本的な構文をしっかり教えてもらいました。

 

 

本書後半（第5章〜第7章）で学んだSQL

後半で学んだ内容は少し複雑なものが多かったです。それぞれの補足説明を少し増やしまとめます。

  • ビュー
    SELECT文が保存されたもの。CREATE VIEW文でつくれる。使う際はSELECT文のFROM句に書く。よって、複数のテーブルにまたがる操作が楽になる。また、ビューに対する更新には厳しい制限がある。
  • サブクエリ
    使い捨てのビュー（SELECT文）、FROM句に書く。ASで名前をつけることが必須。返り値（戻り値）が単一の値になるスカラ・サブクエリもある。相関サブクエリはサブクエリ内でWHERE句を使うことによって全体から小分けにしたグループ内での比較をすることができる。
  • 関数
    算術関数：四則演算、ABS（絶対値）、MOD（剰余）、ROUND（四捨五入）。ROUND関数は引数を二つ取ることに注意。
    文字列関数：||（連結）、LENGTH（文字列長）、LOWER（小文字化）、UPPER（大文字化）、REPLACE（置換）、SUBSTRING（切り出し）
    日付関数：CURRENT_DATE、CURRENT_TIME、CURRENT_TIMESTAMP、EXTARCT（日付要素の切り出し）
    変換変数：CAST（型変換）、COALESCE（NULLを変換）
  • 述語
    戻り値が真理値（TRUE/FALSE/UNKNOWN）になる関数のこと。LIKEは部分一致検索を行い、前方・中間・後方一致が可能。BETWEENは範囲検索ができ、IS NULL、IS NOT NULLも述語の一つ。IN術後はORの便利な省略形。 また、サブクエリを引数に取れる。EXISTS述語の引数は常に相関サブクエリを指定する。
  • CASE式
    CASE WHEN ~ THENと書いて行く検索CASE式とCASE <式> WHEN ~ THENと書く単純CASE式がある。
  • UNION
    テーブル同士の和集合。重複行を残すにはALLオプションをつければいい。
  • INTERSESCT
    複数のテーブルの共通部分を選択する。
  • EXCEPT
    レコード同士の引き算が行われる。
  • INNER JOIN
    内部結合。FROM句に2つのテーブルを記述し、結合キーを使って結びつける。
  • OUTER JOIN
    外部結合。FROM句に複数のテーブルを記述し、2つの場合はLEFTやRIGHTというキーワードでマスタを指定する。マスタに指定されたテーブルの情報がすべて出てくる。
  • CROSS JOIN
    2つのテーブルのレコードについて、全ての組み合わせを作る結合方法。ぶっちゃけ使うことは滅多にないらしい。

 

ここからはいよいよ僕が知らない概念や関数、クエリがたくさん出てきました。これらをうまく使えるようになればデータベースを柔軟に利用できそうです。 

 

ここまでくるとRubyでActiveRecordの関連づけによって、関連のある複数のテーブルにまたがるデータの検索は裏ではサブクエリが動いていそうだとかイメージができますね。

 

 

最終章（第8章）で学んだこと

最終章ではSQLで高度な集計処理を行うための機能について学びました。

 

ウィンドウ関数

ウィンドウ関数とは、ランキングや連番作成などの操作を行います。別名OLAP（OnLine Analytical Processing）関数とも呼ばれているらしい。

 

基本構文は 

<ウィンドウ関数> OVER ([PARTITION BY <列リスト>]

                                                 ORDER BY <ソート用リスト>)  

[ ]の部分は省略可能

です。

 

ウィンドウ関数として使える関数には

  • 集合関数
  • RANK、DENSE_RANK、ROW_NUMBERなどのウィンドウ専用関数

があります。また、ウィンドウ関数はSELECT句でのみ使うと最初のうちは覚えておいたほうがいいみたいです。

 

RANK関数

RANKはレコードのランキング（順位）を算出する関数です。PARTITION BYは、順位をつける対象の範囲を設定します。ORDER BYはどの列を、どんな順序で順位をつけるかを指定します。

 

PARTITION BYとGROUP BYとは違って、集約する機能はありません。あくまでレコードの集合を区切るだけで、区切られたレコードの集合を「ウィンドウ」と呼びます。

 

集合関数をウィンドウ関数として使う

少しややこしいですが、集合関数をウィンドウ関数として使った場合、ただの順位づけをした時とは違う点について注意しなければなりません。

 

集合関数をウィンドウ関数として使うと、一般的には「累計」と呼ばれるタイプの集計方法で計算されるのです。

 

例えば 

SELECT shohin_id, shohin_mei, hanbai_tanka,

            SUM(hanbai_tanka) OVER (ORDER BY shohin_id)

FROM Shohin;

とすると、商品ID（shohin_id)の昇順で並べて、自分よりも小さい商品IDを持つ商品の販売単価を合計した結果がSUM(hanbai_tanka)となります。

 

また、ここは本書において矛盾しているような説明がされているポイントなのですが、ウィンドウ関数を使った結果のレコードの並び順にはOVER句内のORDER BYは影響を与えないみたいです。OVER句内のORDER BYは、あくまでウィンドウ関数がどういう順序で計算するかを決めるだけの役割しかないみたいです。

 

さきほどのSUM(hanbai_tanka) OVER (ORDER BY shohin_id)の結果の並び順は商品IDが昇順に並べられる結果になる（とサンプルはいっている）のですが、これは商品IDが昇順に並べられたというよりウィンドウ関数としてのSUMが計算された順がそのまま表示される形のSQLだったからこうなっているのだと言えそうです。

 

GROUPING演算子

GROUPING演算子は、小計・合計を同時に求めることができる機能です。

 

集約関数とGROUP BY句ではグループ毎の合計しか求められませんでした。しかしGROUPING演算子（ROLLUP、CUBE、GROUPING SETS）を使えば合計も出せます。

 

ROLLUP演算子の場合、GROUP BY句の集約キーリストにROLLUP (<列1>, <列2>, ...)のように使用し集約キーの組み合わせが異なる結果を一度に計算します。

これで集約キー（商品分類）ごとの販売単価の合計と、集約キーの組み合わせが異なる（全ての商品）の販売単価の合計が出る

SELECT shohin_bunrui, SUM(hanbai_tanka) AS sum_tanka

FROM Shohin

GROUP BY ROLLUP(shohin_bunrui); 

上のクエリの例にもコメントとして書きましたが、GROUP BY ROLLUP(shohin_bunrui)とすることで

  • GROUP BY( )
  • GROUP BY(shohin_bunrui)

という二つのグルーピングをしたときのSUM(hanbai_tanka)の結果が得られます。一つ目のGROUP BY( )の句がないときと同様の場合の合計行のレコードを超集合行というみたいです。

 

また、先ほどの例でROLLUPで引数として取る列を二つにする場合、例えばGROUP BY ROLLUP(shohin_bunrui, torokubi)とする場合は、

  • GROUP BY( )
  • GROUP BY(shohin_bunrui)
  • GROUP BY(shohin_bunrui, hanbai_tanka)

という3パターンの集合レベルの異な結果を繋げた形になります。つまり超集合行（全体の販売単価の合計）、商品分類ごとの販売単価の合計、商品分類と登録日でグループ分けした商品の販売単価の合計が出ることになるわけですね。

 

GROUPING関数

CUBEとGROUPING SETS演算子の説明は割愛して、最後にGROUPING関数について説明します。

 

SQLは超集合行のNULLを判別するための特別な関数としてGROUPING関数を用意しています。超集合行を出力する際に、合計が表示されている列やGROUPING演算子で指定した列名以外はNULLになってしまっています。

 

そのNULLが超集合行のために生じたNULLだと1を、それ以外のNULLななら0を返します。使い方としてはGROUPING(torokubi)のように使います。

 

 

少し長くなりましたが、これで本書のまとめは終了です。

ISUCON予選まであと1ヶ月なので、これからはISUCONの過去問題を解きながら同時に新しい知識をつけていく形を取っていこうかと思っています。

 

ではまた。