SQL 便利な関数

Home Avintonアカデミー 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;

パーセンタイル

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

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

SQL 便利な関数

WITH 構文

WITH RECURSIVE 構文

GROUPING SETS 構文

WINDOW 関数

NULL値のソート順制御

FILTER 句

パーセンタイル

FETCH FIRST

OFFSETは非推奨

OVER

採用情報

Avinton SDGs

Search

SQL 便利な関数

WITH 構文

WITH RECURSIVE 構文

GROUPING SETS 構文

WINDOW 関数

NULL値のソート順制御

FILTER 句

パーセンタイル

FETCH FIRST

OFFSETは非推奨

OVER

採用情報

Avinton SDGs

Search

タグ