目的
SQL言語とは何か、改めて理解を深めるために
SQLの構文から自分が欲しいところだけ抜粋。
SQL
・大文字小文字は気にしない（常に小文字で解釈）
・引用符(")を付ける事で大文字小文字を区別し、空白や&などを含むテーブルなどを作れる
・エスケープ文字はバックスラッシュ。UNESCAPEで変更可能。
・引用符付き文字列を使って、ユニコード文字を入れられる
※ただし、サーバーがUTF-8である事
U&"d!0061t!+000061" UESCAPE '!'
定数
・文字列は単一引用符(')を使う
・シングルクォートを含みたい場合は2個並べる
'Dianne''s horse'
$引用符
※postgreSQL独自の仕様※
・シングルクォートの代わりに、$を使うとエスケープしなくてよくなる
・$function$～$function$のように囲うと、タグとして扱われる。
・タグの中では、さらに$で入れ子にできる。
$$Dianne's horse$$
$SomeTag$Dianne's horse$SomeTag$
$function$
BEGIN
RETURN ($1 ~ $q$[\t\r\n\v\\]$q$);
END;
$function$
ビット文字列定数
・B+引用符でビット文字列定数となる
・もしくはX+16進表現
B'1001'
X'1FF'
数値定数
42
3.5
4.
.001
5e2
1.925e-3
キャスト
CAST ( '100' AS int )
演算子
-
-
- / < > = ~ ! @ # % ^ & | ` ?
-
特殊文字
コメント
-- コメント
/*コメント
* あ /*ネストコメント*/
*/
演算子の優先順位
評価式
列の参照
SELECT mg.goods_id FROM m_goods AS mg;
位置パラメータ
・プリペアドやパラメタ定義に使う。$1とか$2とか
CREATE FUNCTION dept(text) RETURNS dept
AS $$ SELECT * FROM dept WHERE name = $1 $$
LANGUAGE SQL;
添字
・配列のインデックス
m_goods.sub_goods_ids[3]
m_goods.sub_goods_ids[2:5]
フィールド選択
・selectの列指定とか
SELECT m_goods.* FROM m_goods;
演算子呼び出し
-- 二項中置演算子
a > 0 AND b < 0
-- 単項前置演算子
!! 3
-- 単項後置演算子
5 !
関数呼び出し
sqrt(2)
集約式
・DISTINCT,FILTER,ALL,WITHIN GROUPなどなど
・集約式はSELECTの結果またはHAVING内でのみ記述可能
・WHERE句では使用不可。WHEREは集約処理の前に適用される処理だから
・集計関数は基本的に順序関係ないが、順序が関係するものもある
SELECT array_agg(a ORDER BY b DESC) FROM table;
・count(*)は入力行の合計数を求めるが、NULLを無視する。
※postgreSQL独自の仕様※
・DISTINCTとORDER BYを両方指定可能
・FILTERの例
SELECT
count(*) AS unfiltered,
count(*) FILTER (WHERE i < 5) AS filtered
FROM generate_series(1,10) AS s(i);
unfiltered | filtered
------------+----------
10 | 4
(1 row)
ウィンドウ関数
・テーブルを区間ごとに集計する
※公式の説明だと理解できんかった…
Let's postgress
-- ウィンドウ関数
SELECT id, prev_id
FROM (SELECT id,
lag(id, 1, 0) OVER (ORDER BY id) AS prev_id
FROM tbl) AS t
-- ウィンドウ関数使わないと、自己結合の激コスト
SELECT id FROM tbl WHERE NOT EXISTS
(SELECT 1 FROM tbl AS tbl_prev WHERE tbl.id = tbl_prev.id + 1);