※本記事はSQLの入門を一通り勉強し終えた方向けとなります。
環境
Windows10 64bit
PostgreSQL 10.6
※環境はPostgresql/Redshiftを想定しているため、BigQuery 等では使用できないかもしれません。
知っていると役に立つかもしれない SQL - Tips集
## そうは言っても基本編
#####■SQLの評価順序
・FROM > WHERE > GROUP BY > HAVING > SELECT > ORDER BY > LIMIT
#####■countでnullも含めて数を数えたいとき(通常、countのみだとnullの数はカウントされません)
SELECT
COUNT(*)
FROM
(SELECT * FROM テーブル名 )as q
WHERE
カラム名 is null
#####■SQLでは、「NOT IN」はnullがあると使えない
#####■検索時に「__(ｱﾝﾀﾞｰﾊﾞｰ)」で文字数を指定できる。(ex.「＿ ＿ご」という綴りのものを検索したい)
SELECT
*
FROM
テーブル名
WHERE
item LIKE '__ご'
#####■「ORDER BY カラム名 DESC」で高い順に並び替え
#####■「RANK() OVER (ORDER BY カラム名 DESC)」：上から順位をつける(順位を飛ばす)
#####■「DENSE_RANK() OVER (ORDER BY カラム名 DESC)」：上から順位をつける(順位を飛ばさない)
#####■「RANK() OVER(PARTITION BY グループ分けしたいカラム名 ORDER BY カラム名)」：グループ化したものを上から順位をつける(順位を飛ばす)
#####■感覚的に知っておきたいサブクエリの使用場所
-SELECT句でサブクエリを使う時＝特定の列の集計を行ったり、集計関数を使ったり、一件だけ呼び出したいとき
-FROM句でサブクエリを使う時＝名前を付けて改めてSELECT句で使いたい場合。テーブルをどんどん絞っていくとき、もしくはカラム名を作成し、そのコード内で使用したいとき
-WHERE句でサブクエリを使う時＝検索条件を補完したい時や、他の更新可能性のあるテーブルを引っ張ってきたい時(＝他所のテーブルに合わせて都度更新したくない時)、JOINが使えないとき
## チョイと便利なお役立ち構文編
#####■FIRST_VALUE(カラム名)OVER()：ランキング最上位を抽出
#####■LAST_VALUE(カラム名)OVER()：ランキング最下位を抽出
#####■ROWS BETWEEN 開始値 and 終了値: 内の条件で、連番を振る
-CURRENT ROW(現在の行)
-n RECEDING(n行前)
-n FOLLOWING(n行後)
-UNBOUNDED PRECEDING(前の行すべて)
-UNBOUNDED FOLLOWING(後の行すべて)
rows between unbounded preceding and current row
unbounded preceding = 「グループの最初の行をウィンドウの開始点とする」
current_row = 「ウィンドウの終了点を常にカレント行とする」
#####■動的疑似テーブル作成
-WITH 疑似テーブル名(疑似カラム名、疑似カラム名) as (
VALUES
('単語'、'単語')
('単語'、'単語')
('単語'、'単語')
) select * from 疑似テーブル名
#####■ROLLUP(カラム名1、カラム名2)：カラム別(カテゴリ別)での小計を抽出
## チョイと便利な分析構文編
#####■ntile(数字)：任意の間隔にグルーピング
ex.デシル分析にて：ntile(10) over(partition by カラム名 order by カラム名2 desc)･･･全体を10分割して各行に1～10の値をつける
#####■random() < 0.001 ：ランダムサンプリング。「どの行でも良いからランダムに0.1%(=1000分の1)くらいの量が欲しい」
#####■var_pop(column)=columnの母分散
#####■var_samp(column)=columnの標本分散
#####■stddev_pop(column)=columnの#####標準偏差
#####■stddev_samp(column)=columnの標本標準偏差
Tipsというよりただの構文集になってしまった感は否めませんが、どこかでお役立て頂ければ幸いです。