WITH 構文
入れ子構造はSQL文では頻出ですが、基本的に読みづらいです。
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT t1.title, t3.actor_name FROM film as t1 LEFT JOIN ( SELECT film_id, actor_id FROM film_actor) AS t2 ON t1.film_id = t2.film_id LEFT JOIN ( SELECT actor_id, first_name || ' ' || last_name AS actor_name FROM actor) AS t3 ON t2.actor_id = t3.actor_id ORDER BY t1.title, t3.actor_name |
WITH構文を使うことで、入れ子の中身(サブクエリ)をSQL文の先頭に持ってくることができます。これによって、SQL文の先頭から(自然な流れで)中身を追うことができます。
|
1 2 3 4 5 6 7 8 9 10 |
WITH t2 (film_id, actor_id) AS (SELECT film_id, actor_id FROM film_actor), t3 (actor_id, actor_name) AS (SELECT actor_id, first_name || ' ' || last_name AS actor_name FROM actor) SELECT title, actor_name FROM film as t1 LEFT JOIN t2 ON t1.film_id = t2.film_id LEFT JOIN t3 ON t2.actor_id = t3.actor_id ORDER BY t1.title, t3.actor_name |
WITH RECURSIVE 構文
再帰的にテーブルを結合することができます。
例えば、1から10までを昇順に表示させるSQL文は以下のように書けます。
|
1 2 3 4 5 6 |
WITH RECURSIVE seq(i) AS ( SELECT 1 UNION ALL SELECT i + 1 FROM seq WHERE i < 10 ) SELECT i FROM seq; |
使いどころとしては、階層構造を持ったテーブルを扱うケースが考えられます。
GROUPING SETS 構文
例えば、各部署において日次売り上げデータを格納したテーブルがあり、会社全体の日ごとのトータルの売り上げと、月ごとのトータルの売り上げを同時に1つのテーブルで表現したい場合、
|
1 2 3 4 5 6 7 8 9 10 |
SELECT month, day, sum(sales) FROM t1 GROUP BY GROUPING SETS ( (month, day), (month) ) |
のように書くことができます。
WINDOW 関数
同じキーを持つレコードをマージするには、
・GROUP BY
・DISTINCT
の2つの方法が挙げられます。
集約関数(sum, maxなど)を使うにはGROUP BYでキーを集約させる必要があります。
キーをマージさせずに、集約させるにはどうすれば良いでしょうか。
1つの方法としては、サブクエリを用いて元のテーブルと集約させたテーブルを結合させる方法があります。
|
1 2 3 4 5 6 |
WITH t1 (customer_id, total_amount) AS (SELECT customer_id, SUM(amount) FROM payment GROUP BY customer_id) SELECT payment.customer_id, amount, total_amount FROM payment JOIN t1 ON (payment.customer_id = t1.customer_id) ORDER BY payment.customer_id, amount; |
しかし、この方法は見づらく、メンテナンスしづらいのが問題です。
ここでWINDOW 関数を用いてみましょう。
|
1 2 3 4 5 6 |
SELECT customer_id, amount, SUM(amount) OVER(PARTITION BY customer_id) AS total_amount FROM payment ORDER BY customer_id, amount; |
もう少し簡単な例を見てみましょう。
| id | price |
|---|---|
| 1 | 100 |
| 2 | 100 |
| 2 | 100 |
| 3 | 100 |
| 3 | 100 |
| 3 | 100 |
| 3 | 100 |
上記のテーブルt1に対し、priceの総和を含む列を追加します。
|
1 2 3 4 5 |
SELECT id, price, SUM(price) OVER() AS total FROM t1 |
| id | price | total |
|---|---|---|
| 1 | 100 | 600 |
| 2 | 100 | 600 |
| 2 | 100 | 600 |
| 3 | 100 | 600 |
| 3 | 100 | 600 |
| 3 | 100 | 600 |
| 3 | 100 | 600 |
各idごとの総和を含む列を追加するためには、PARTITION BY句 を使います。
|
1 2 3 4 5 |
SELECT id, price, SUM(price) OVER(PARTITION BY id) AS total FROM t1 |
| id | price | total |
|---|---|---|
| 1 | 100 | 100 |
| 2 | 100 | 200 |
| 2 | 100 | 200 |
| 3 | 100 | 400 |
| 3 | 100 | 400 |
| 3 | 100 | 400 |
| 3 | 100 | 400 |
次にORDER BY句を使った別の例を見てみましょう。
| day | price |
|---|---|
| 1 | +30 |
| 2 | +50 |
| 3 | +20 |
| 4 | -10 |
| 5 | -80 |
| 6 | +70 |
| 7 | -40 |
上記のテーブルt1において、価格(price)の日(day)ごとの推移を含む列を追加するとします。方法の1つとしては、スカラサブクエリを用いることです。
|
1 2 3 4 5 |
SELECT day, price, (SELECT SUM(price) FROM t1 AS t2 WHERE t2.day <= t1.day) AS balance FROM t1 |
| day | price | balance |
|---|---|---|
| 1 | +30 | +30 |
| 2 | +50 | +80 |
| 3 | +20 | +100 |
| 4 | -10 | +90 |
| 5 | -80 | +10 |
| 6 | +70 | +80 |
| 7 | -40 | +40 |
さらに、WINDOW関数を用いる方法があります。結果は同じです。
|
1 2 3 4 5 6 7 |
SELECT day, price, SUM(price) OVER ( ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) FROM t1 |
OVER (ORDER BY)句で使える関数には、様々な種類があります。
ここで順位を取得する関数いくつかの例を示します。
| i | ROW_NUMBER | RANK | DENSE_RANK |
|---|---|---|---|
| 1 | 1 | 1 | 1 |
| 2 | 2 | 2 | 2 |
| 2 | 3 | 2 | 2 |
| 4 | 4 | 4 | 3 |
列iに対し、その他の列は順位を示しています。使用する関数により、順位のつき方が異なります。
OVER句は
1. 移動平均
2. ランキング
3. 自己結合の回避(LAG関数など)
など、様々な実用例が考えられます。
NULL値のソート順制御
デフォルトでは、NULL値はソートの先頭(DBによっては末尾)に来ますが、NULLS FIRST (LAST)句を使うことで、順序を制御できます。
|
1 2 3 |
SELECT ... FROM ... ORDER BY nullable NULLS FIRST |
この場合、NULL値があれば、ソートの先頭に来ます。
FILTER 句
列yearに年、列monthに月を持つテーブルt1を考えます。
| year | month | sales |
|---|---|---|
| 2018 | 10 | 200 |
| 2018 | 11 | 300 |
| 2018 | 12 | 800 |
| 2019 | 1 | 600 |
| 2019 | 2 | 400 |
| 2019 | 3 | 300 |
このテーブルを変形し、行方向に年、列方向に月を持つピボットテーブル
| year | Jan | Feb | Mar | … | Oct | Nov | Dec |
|---|---|---|---|---|---|---|---|
| 2018 | … | … | … | … | 200 | 300 | 800 |
| 2019 | 600 | 400 | 300 | … | … | … | … |
を作成したい場合、
|
1 2 3 4 5 6 7 |
SELECT year, SUM(CASE WHEN month = 1 THEN sales END) as Jan, SUM(CASE WHEN month = 2 THEN sales END) as Feb, ... FROM t1 GROUP BY year; |
のようにCASE句を駆使する方法があります。
これをFILTER句を使用することで書き換えることができます。
|
1 2 3 4 5 6 7 |
SELECT year, SUM(sales) FILTER (WHERE month = 1) Jan, SUM(sales) FILTER (WHERE month = 2) Feb, ... FROM t1 GROUP BY year; |
パーセンタイル
ある列の中央値を取得する場合、テーブルを自己結合させることによって求める方法がありますが、現在では、パーセンタイル関数を使用することにより簡単に求めることができます。
|
1 |
SELECT PERCENTILE_DISC (0.5) WITHIN GROUP (ORDER BY sales) FROM t1 |
FETCH FIRST
SQL標準の「最初のN行のみ選択するクエリ」です。LIMITやTOP句のような働きをします。
|
1 2 3 4 |
SELECT * FROM t1 ORDER BY sales FETCH FIRST 10 ROWS ONLY; |
この場合、最初の10行のみを取得します。
OFFSETは非推奨
ページネーションを考える際、次ページ以降を取り出す方法は２つ考えられます。
1. OFFSETを用いて対象ページより前のページをスキップして取得
2. 最後のエントリの１つあとに来るべき値を用いて取得
1の方法のデメリットは２つあり、
(i)新しいレコードが挿入された場合、ページがずれること
(ii)後ろのページを検索するほど、処理時間が長くなること
です。
2の方法では、where句を用いてシンプルに実現できます。
|
1 2 3 4 5 |
SELECT * FROM sales WHERE sale_date < [target_date] ORDER BY sale_date DESC FETCH FIRST 10 ROWS ONLY |
OVER
例えば直前の行との差を取る場合、自己結合を用いる方法がありますが、現在ではLAG関数によりシンプルに実現可能です。
|
1 2 3 4 5 6 |
SELECT *, amount - COALESCE( LAG(amount) OVER(ORDER BY payment_id) , 0) FROM payment |
他にも、1つ先の行を取得するLEADや、最初と最後の値を取得するFIRST_VALUE/LAST_VALUE、任意のn番目の行を取得するNTH_VALUE関数があります。
