SQL 勉強メモ

SQLを勉強していて気になったことをメモとして垂れ流しておきます。

GROUP BY GROUP BYは集約関数とセットで使うもの。 集約した結果を求めたい、という時に使うものとして考えられている。

※集約関数 　COUNT,SUM,AVG,MAX,MIN

単に重複していないリストが欲しいのであれば、DISTINCTを使う。

GROUP BYで間違えやすいこと。

GROUP BY句ではSELECTでつけた列の別名は使えない

GROUP BY句は結果の順序をソートしない 　同じGROUP BYを実行しても結果の順序が変わることはありえる。

HAVING WHERE ：行に対する条件指定 HAVING：グループに対する条件指定

グループに対する条件指定とは 例えば、含まれる行数が２行、平均値が500など

HAVING句にかける要素には制限がある。 ・定数 ・集約関数 ・GROUP BY句で指定した列名（＝集約キー）

ORDER BY

SELECTで指定した列の別名が使える。

NULLは先頭か末尾のどちらかにまとめられる。どちらかは特に決まっていない。 DBMSによって、先頭か末尾かを選択することのできるものもある。

ORDER BY句では、SELECT句に含まれていない列や集約関数も使える

なぜGROUP BYでは列の別名が使えないのにORDER BYなら使えるのか↓

SELECT文の内部的な実行順序

FROM→WHERE→GROUP BY→HAVING→SELECT→ORDER BY

ビュー SQLの観点から見るとビューは「テーブルと同じもの」である。 違いは、テーブルには実際のデータが登録されているが、ビューはデータを持たずSELECT文を保持しているという点。

サブクエリ

サブクエリ＝使い捨てのビュー

スカラ・サブクエリ

「必ず１行１列だけの戻り値を返す」という制限をつけたサブクエリ

１行１列だけ＝ただ１つの値　「10」「東京都」など

戻り値が単一のため、比較演算子を利用することが可能となる

相関サブクエリ 小分けにしたグループ内での比較をするときに使う

スカラ・サブクエリでは単体の値を返さないといけない。 Aのパターンではこの値、Bのパターンではこの値を返す、みたいなときに使える。

SELECT shohin_bunrui, shohin_mei, hanban_tanka FROM Shohin AS S1 WHERE hanbai_tanka > (SELECT AVG(hanban_tanka) FROM Shohin as S2 WHERE S1.shohin_bunrui = S2.shohin_bunrui);

テーブルの足し算と引き算 UNION（和）・INTERSECT・（交差）EXCEPT（差）

いずれも、重複行は排除される。重複行も残すにはALLというオプションを指定する。

ウィンドウ関数（OLAP関数）

OLAP ＝ Online Analytical Processing データベースを使ってリアルタイムにデータ分析を行う処理のこと

ウィンドウ関数の基本的な構文

<ウィンドウ関数> OVER ([PARTITION BY <列リスト>] ORDER BY <ソート用列リスト>)

ウィンドウ関数として使える関数 1．集約関数（SUM,AVG,COUNT,MAX,MIN）をウィンドウ関数として使う 2．RANK,DENSE_RANK、ROW_NUMBERなどのウィンドウ専用関数

RANK関数の利用

RANK　レコードのランキング（順位）を算出する関数

例えば、商品分類別に、販売単価の安い順で並べたランキング表を作る

SELECT shohin_mei, shohin_bunrui, hanbai_tanka, RANK () OVER (PARTITION BY shoshin_bunrui ORDER BY hanbai_tanka) AS ranking FROM Shohin;

PARTITION BYは順位をつける対象の範囲を設定している PARTITION BYによって区切られた部分集合をウィンドウと呼ぶ

ウィンドウ関数はカットと順序づけの両方の機能を持っている

ウィンドウ関数はSELECT句でしか使えない。 （SELECT句以外で使えてもランキングに変動があると意味がなくなるため。）

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

SUMの場合　累計

SELECT shohin_id, shohin_mei, hanbai_tanka, SUM(hanbai_tanka) OVER (ORDER BY shohin_id) AS current_sum FROM Shohin;

AVGの場合　現在平均

SELECT shohin_id, shohin_mei, hanbai_tanka, AVG(hanbai_tanka) OVER (ORDER BY shohin_id) AS current_avg FROM Shohin;

集計範囲を限定するフレーム

フレームというオプションを利用することで集計範囲を限定することも可能。 これを利用することで移動平均を計算することも可能

▼直前３行までを使う場合 SELECT shohin_id, shohin_mei, hanbai_tanka, AVG(hanbai_tanka) OVER (ORDER BY shohin_id ROWS 2 PRECEDING) AS moving_avg FROM Shohin;

▼直後３行までを使う場合 SELECT shohin_id, shohin_mei, hanbai_tanka, AVG(hanbai_tanka) OVER (ORDER BY shohin_id ROWS 2 FOLLOWING) AS moving_avg FROM Shohin;

▼前後１行までを使う場合 SELECT shohin_id, shohin_mei, hanbai_tanka, AVG(hanbai_tanka) OVER (ORDER BY shohin_id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg FROM Shohin;