SQL 便利な関数

WITH 構文

入れ子構造はSQL文では頻出ですが、基本的に読みづらいです。

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文の先頭から(自然な流れで)中身を追うことができます。

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文は以下のように書けます。

WITH RECURSIVE seq(i) AS (
SELECT 1
UNION ALL
SELECT i + 1 FROM seq WHERE i < 10
)
SELECT i FROM seq;

使いどころとしては、階層構造を持ったテーブルを扱うケースが考えられます。

GROUPING SETS 構文

例えば、各部署において日次売り上げデータを格納したテーブルがあり、会社全体の日ごとのトータルの売り上げと、月ごとのトータルの売り上げを同時に1つのテーブルで表現したい場合、

SELECT
month,
day,
sum(sales)
FROM t1
GROUP BY
GROUPING SETS (
(month, day),
(month)
)

のように書くことができます。

WINDOW 関数

同じキーを持つレコードをマージするには、
・GROUP BY
・DISTINCT
の2つの方法が挙げられます。
集約関数(sum, maxなど)を使うにはGROUP BYでキーを集約させる必要があります。
キーをマージさせずに、集約させるにはどうすれば良いでしょうか。
1つの方法としては、サブクエリを用いて元のテーブルと集約させたテーブルを結合させる方法があります。

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 関数を用いてみましょう。

SELECT
customer_id,
amount,
SUM(amount) OVER(PARTITION BY customer_id) AS total_amount
FROM payment
ORDER BY customer_id, amount;

もう少し簡単な例を見てみましょう。

上記のテーブルt1に対し、priceの総和を含む列を追加します。

SELECT
id,
price,
SUM(price) OVER() AS total
FROM t1

各idごとの総和を含む列を追加するためには、PARTITION BY句 を使います。

SELECT
id,
price,
SUM(price) OVER(PARTITION BY id) AS total
FROM t1

次にORDER BY句を使った別の例を見てみましょう。

上記のテーブルt1において、価格(price)の日(day)ごとの推移を含む列を追加するとします。方法の1つとしては、スカラサブクエリを用いることです。

SELECT
day,
price,
(SELECT SUM(price) FROM t1 AS t2 WHERE t2.day <= t1.day) AS balance
FROM t1

さらに、WINDOW関数を用いる方法があります。結果は同じです。

SELECT
day,
price,
SUM(price) OVER (
ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
FROM t1

OVER (ORDER BY)句で使える関数には、様々な種類があります。
ここで順位を取得する関数いくつかの例を示します。

列iに対し、その他の列は順位を示しています。使用する関数により、順位のつき方が異なります。

OVER句は
1. 移動平均
2. ランキング
3. 自己結合の回避(LAG関数など)
など、様々な実用例が考えられます。

NULL値のソート順制御

デフォルトでは、NULL値はソートの先頭(DBによっては末尾)に来ますが、NULLS FIRST (LAST)句を使うことで、順序を制御できます。

SELECT ...
FROM ...
ORDER BY nullable NULLS FIRST

この場合、NULL値があれば、ソートの先頭に来ます。

FILTER 句

列yearに年、列monthに月を持つテーブルt1を考えます。

このテーブルを変形し、行方向に年、列方向に月を持つピボットテーブル

を作成したい場合、

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句を使用することで書き換えることができます。

SELECT
year,
SUM(sales) FILTER (WHERE month = 1) Jan,
SUM(sales) FILTER (WHERE month = 2) Feb,
...
FROM t1
GROUP BY year;

パーセンタイル

ある列の中央値を取得する場合、テーブルを自己結合させることによって求める方法がありますが、現在では、パーセンタイル関数を使用することにより簡単に求めることができます。

SELECT PERCENTILE_DISC (0.5) WITHIN GROUP (ORDER BY sales) FROM t1

FETCH FIRST

SQL標準の「最初のN行のみ選択するクエリ」です。LIMITやTOP句のような働きをします。

SELECT *
FROM t1
ORDER BY sales
FETCH FIRST 10 ROWS ONLY;

この場合、最初の10行のみを取得します。

OFFSETは非推奨

ページネーションを考える際、次ページ以降を取り出す方法は２つ考えられます。
1. OFFSETを用いて対象ページより前のページをスキップして取得
2. 最後のエントリの１つあとに来るべき値を用いて取得
1の方法のデメリットは２つあり、
(i)新しいレコードが挿入された場合、ページがずれること
(ii)後ろのページを検索するほど、処理時間が長くなること
です。
2の方法では、where句を用いてシンプルに実現できます。

SELECT *
FROM sales
WHERE sale_date < [target_date]
ORDER BY sale_date DESC
FETCH FIRST 10 ROWS ONLY

OVER

例えば直前の行との差を取る場合、自己結合を用いる方法がありますが、現在ではLAG関数によりシンプルに実現可能です。

SELECT
*,
amount - COALESCE(
LAG(amount) OVER(ORDER BY payment_id)
, 0)
FROM payment

他にも、1つ先の行を取得するLEADや、最初と最後の値を取得するFIRST_VALUE/LAST_VALUE、任意のn番目の行を取得するNTH_VALUE関数があります。