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