SQL学び直し13
第八章:SQLで高度な処理を行う
8-1:ウインドウ関数
- ウインドウ関数とはOLAP(OnLine Analytical Processing)関数とも呼ばれる
- OLAPとはリアルタイムにデータ分析を行う処理のこと
- 構文:ウインドウ関数 OVER ( PARTITION BY <列リスト> ORDER BY <ソート用列リスト>)
- rank over (partition by A orderby B) as ranking from table といった形で使う、A列毎にB列順にrankingカラムでランクを付けてくれる
- ウインドウ関数は集約関数をウインドウ関数として使う、ウインドウ専用関数を使う、の2種類に分けられることができる
- partition byによって区切られたレコードの集合をウインドウと呼ぶ、窓ではなく範囲を表わす
- partition byを使わない場合、テーブル全体が1つのウインドウとしてみなされる(order byのカラム基準になる)
- 以下、各ウインドウ専用関数の違い
- rank()の場合に1位が3つある場合、1位、1位、1位、4位と飛ぶ
- dense_rank()の場合に1位が3つある場合は1位、1位、1位、2位と飛ばない、デンスランクと読む
- row_number()の場合に1位が3つある場合は1位、2位、3位、4位と同じ値でも2位と3位になる、同じ値の場合はDBMSが適当に値を振る
- ウインドウ専用関数を使う時は()の中は空のまま使う、これは全てのウインドウ専用関数で同じ、引数を取らない
- ウインドウ関数h原則としてSELECT句のみで使える、AVGやSUMを利用すると降順・昇順でのその時点での累計や累計の平均を出力出来る
- 移動平均を出す場合
- avg(カラムA) over(order by カラムB rows 直近の何行か数値を入れる preceding) as ~
- その値の直近3行分の平均値をasの中に入れられる、precedingは前のという意味
- カレントレコードから後ろを対象にするには、following(後の)を使う
- 前後を対象にする場合は(order by A rows between 前の分の数値 preceding and 後ろの分の数値 following) as ~とする
- OVER句の中のorder byはウインドウ関数がどういった順序で計算数かを決めるだけの役割しか持っていない、結果の並び替えには影響しない
8-2:GROUPING演算子
- グループ化した結果の合計を求めることができる、ROLLUP、CUBE、GROUPING SETSの3種類がある
- と思ったら、postgresqlではサポートされていないらしい
今日はここまで、長かったけど終わった。
とても勉強になったし、実務でも役立ったし、良い本でした。
SQL学び直し12
第七章:集合演算
7-2:結合(テーブルを列方向に連結する)
- 結合の基本は内部結合と外部結合
- 内部結合:select ... from A inner join B on A.x = B.y というような構文
- 外部結合:片方のテーブルの情報がすべて出力される
- from A left outer join B on A.x =B.y という場合はテーブルAのデータが全て出力される
- from A right outer join B on A.x =B.y という場合はテーブルBのデータが全て出力される
- クロス結合:select ... from A cross join B というような構文
- すべての組み合わせ(直積)結果を出力する
- 外部結合、クロス結合はあまり使わない
- NULLを他の値に置き換える関数「COALESCE」を併用することが多い
今日はここまで。
SQL学び直し11
第六章:関数、術語、CASE式
6-3:CASE式
- 単純CASE式と検索CASE式の2種類がある、検索CASE式は単純CASE式の機能を全て含む
- 検索CASE式は以下のような式になる
CASE WHEN <評価式> THEN <式> WHEN <評価式> THEN <式>... ELSE <式> END
- 最後のENDは必須、ELSEは省略しても良いが可読性が落ちるので省略しない
第七章:集合演算
7-1:テーブルの足し算と引き算
- UNION、テーブルの足し算
- select AAA from B union select CCC from D; というような感じになる、重複行を排除して各テーブルが合体されたものが表示される
- UNION ALLで重複行も表示される
- INTERSECT、テーブルの共通部分のみ抽出
- select AAA from B intersect select CCC from D; というような感じになる、共通しているレコードのみ出力される
- EXCEPT、レコードの引き算
- select AAA from B except select CCC from D; というような感じになる、テーブルBからテーブルDと重複したものを排除して出力される
今日はここまで。
SQL学び直し10
第六章:関数、術語、CASE式
6-2:術語
- 術語とは戻り値が真理値(TRUE/FALSE/UNKNOWN)のいずれかになるもの
- LIKE検索にて、%は0文字以上の任意の文字列、_は任意の一文字を現す(__だと任意の2文字)
- BETWEENは範囲検索
- IS NULLはnullを検索、IS NOT NULLはnullを外して検索
- INはORを省略したもの、IN(A,B,C)というように使う、AまたはBまたはCとなる
- NOT INはINの否定、NOT IN(A,B,C)というように使う、AでもなくBでもなくCでもないものとなる
- INにはサブクエリを利用することができる
- IN( SELECT...)と記述する、括弧内の結果が複数行であっても全てORで検索対象にしてくれる
- サブクエリを使ってSQLを組んでおけば、メンテナンスフリーの度合いが強まる
- EXISTS術語は「ある条件に合致するレコードの存在有無」を調べる、あればTRUE、なければFALSEを返す
- EXISTSの引数は常に相関サブクエリを指定する、常にSELECT *を使う
今日はここまで。
SQL学び直し9
第六章:関数、術後、CASE式
6-1:いろいろな関数
- 関数は数が多いので代表的なものだけ覚える、他は使う時にリファレンスを見るようにする
- COUNT、SUM、AVGなども関数の一つ(集約関数)
- 文字列を結合する関数はパイプ、カラム1 || カラム2 from…という感じになる、3つでもいける
- EXTRACTで日付の要素を切り出すことが出来る、切り出した後は日付型ではなく数値型になる
- こんな使い方
SELECT CURRENT_TIMESTAMP, EXTRACT(YEAR FROM CURRENT_TIMESTAMP) AS year, EXTRACT(MONTH FROM CURRENT_TIMESTAMP) AS month, EXTRACT(DAY FROM CURRENT_TIMESTAMP) AS day, EXTRACT(HOUR FROM CURRENT_TIMESTAMP) AS hour, EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) AS minute, EXTRACT(SECOND FROM CURRENT_TIMESTAMP) AS second;
- nullを値に変換する際はCOALESCE関数を使う
SQL学び直し8
第五章:複雑な問い合わせ
5-2:サブクエリ
- サブクエリとは使い捨てのビュー
- select ... from (select文を書く) as ビュー名、このビューは保存されない
- fromの中のselect文が実行された後、外側のselect文が実行される
- select ... from (asの後の)ビュー名となる
- サブクエリの中にサブクエリを作って入れ子にすることも可能、内側のサブクエリから実行される
- スカラ・サブクエリとは、戻り値が単一の値(単一行)になるサブクエリのこと
- whereの=や<>の条件などと一緒に使用する
- スカラ・サブクエリはwhere句以外の場所でも利用できる
- スカラ・サブクエリが複数行を返すと、それはスカラ・サブクエリではなくただのサブクエリである
5-3:相関サブクエリ
- 相関サブクエリは小分けにしたグループ内での比較をする時に使う
- 相関サブクエリの結合条件はサブクエリの中に書かないとエラーになる
- 結合条件はサブクエリの中に記述する
今日はここまで。
SQL学び直し7
第四章:データの更新
第五章:複雑な問い合わせ
5-1:ビュー
- ビューを"作る際"order byは使えない、ビューを更新はできなくないが制限がある
- ビューとテーブルはSELECTするだけであれば特に気にする必要が無い
- テーブルはデータを保存・保持している、ビューはデータを保存・保持していない代わりにSELECT文を保持している
- ビューは参照される度にSELECT文を実行し、一時的に仮想のテーブルを作成する
- ビューの実行順序はビューに保持されたSELECT文が実行され、その後にFROMにビューを指定したSELECT文の内容が実行される
- ビューを元にビューを作る、多段ビューも作成できるが、なるべくやらない。パフォーマンスが低下する。
- postgres9.3だけど参考
今日はここまで。
SQL学び直し6
第四章:データの更新
4-2:データの削除(DELETE文の使い方)
- DELETE文は行を削除、TRUNCATE文はテーブル全行を削除
- `DELTE FROM テーブル名`よりも`TRUNCATE テーブル名`の方が処理が高速である
4-3:データの更新(UPDATE文の使い方)
- UPDATE文を使えば、制約に引っかからない場合はNULLへ更新することも可能
- `UPDATE テーブル名 SET 更新内容1,更新内容2...`とすることで、複数列の更新も可能、カンマで区切る
- `UPDATE テーブル名 SET (更新列名1,更新列名2)=(更新データ1,更新データ2)...`とすることで、複数列の更新も可能、カンマと括弧使う
やっぱりUSキーボードよりJISキーボードの方が使いやすい。
慣れの問題もあるんだろうけど、使い慣れたモノを使うのが一番だと思う。
自宅の環境としては、
という無駄に変な環境なので、次期iMacが出たらiMacと液晶1台にして、WinはParalellsとかでまとめたいなぁ。
って夢をメモしておきます。
今日はここまで。