※本記事は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というよりただの構文集になってしまった感は否めませんが、どこかでお役立て頂ければ幸いです。