日本語
- English (英語)

SQL 便利な関数

Home Academy 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;

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

id	price
1	100
2	100
2	100
3	100
3	100
3	100
3	100

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

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句を使います。

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つとしては、スカラサブクエリを用いることです。

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関数を用いる方法があります。結果は同じです。

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)句を使うことで、順序を制御できます。

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	…	…	…	…

を作成したい場合、

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関数があります。