よしたく blog

ほぼ週刊で記事を書いています

【SQL】FIRST_VALUE関数・LAST_VALUE関数の動きを確認する

SQL

SQL の分析関数である FIRST_VALUE と LAST_VALUE を使うと、順序が付けられた値のセットの中で最初の値と最後の値を返すことができる。 今回は FIRST_VALUE を使って、動きを確認していく。 FIRST_VALUE は最初の値、LAST_VALUE は最後の値という違いだけで、構文は 同じになるので、LAST_VALUE を知りたい場合は適宜置換してもらえると！

実行環境

テストの場所は、SQL Fiddleで試した。

sqlfiddle.com

エンジンのバージョンはPostgreSQL 9.6を使っている。

https://www.postgresql.jp/document/9.3/html/functions-window.html

今回はサッカーのポジションとゴール数のデータを投入して動きを確認する。

データ投入

今回使用するデータはposition,score の列からなっていて、11 行分のデータが入っている。

CREATE TABLE FIRST_VALUE_TEST
(
 position  CHAR(6) ,
 score INTEGER
);

BEGIN TRANSACTION;
INSERT INTO FIRST_VALUE_TEST VALUES ('FW', 10);
INSERT INTO FIRST_VALUE_TEST VALUES ('FW', 5);
INSERT INTO FIRST_VALUE_TEST VALUES ('MF', 4);
INSERT INTO FIRST_VALUE_TEST VALUES ('MF', 3);
INSERT INTO FIRST_VALUE_TEST VALUES ('MF', 3);
INSERT INTO FIRST_VALUE_TEST VALUES ('MF', 2);
INSERT INTO FIRST_VALUE_TEST VALUES ('DF', 3);
INSERT INTO FIRST_VALUE_TEST VALUES ('DF', 2);
INSERT INTO FIRST_VALUE_TEST VALUES ('DF', 1);
INSERT INTO FIRST_VALUE_TEST VALUES ('DF', 0);
INSERT INTO FIRST_VALUE_TEST VALUES ('GK', 0);
COMMIT;

シンプルな実行

SQL を組み立てて実行する。 FIRST_VALUE 関数は次の形で指定する。

FIRST_VALUE ( [scalar_expression ] )  [ IGNORE NULLS | RESPECT NULLS ]
    OVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] )

実際の SQL にすると次になる。 score の列を対象に、先頭の行を参照している。 並び順は score に従っている。

SELECT
  position,
  score,
  FIRST_VALUE(score) OVER (ORDER BY score DESC)

FROM
  FIRST_VALUE_TEST

全ての行で1人目のFWの10が適応されている。

| position | score | first_value |
|----------|-------|-------------|
|     FW   |    10 |          10 |
|     FW   |     5 |          10 |
|     MF   |     4 |          10 |
|     DF   |     3 |          10 |
|     MF   |     3 |          10 |
|     MF   |     3 |          10 |
|     MF   |     2 |          10 |
|     DF   |     2 |          10 |
|     DF   |     1 |          10 |
|     DF   |     0 |          10 |
|     GK   |     0 |          10 |

PARTITON BY

PARTITON BYを使うとGROUP BYと似たようなことができる。

PARTITION BYにpositionを指定して、ポジションごとにスコアを比較する。

SELECT
  position,
  score,
  FIRST_VALUE(score) OVER (partition by position ORDER BY score DESC)

FROM
  FIRST_VALUE_TEST

ポジションごとにまとめられ、その中でscoreでソート、その後比較がおこなわれているのがわかる。 DFはDFの中だけで最高得点者が、FWはFWの中だけで最高得点者が出されている。

position score first_value
DF 3 3
DF 2 3
DF 1 3
DF 0 3
FW 10 10
FW 5 10
GK 0 0
MF 4 4
MF 3 4
MF 3 4
MF 2 4

例

更に使い方の例として、最高得点者との差を算出するような場合は次のようになる。 これで今トップとどれぐらい差が離れているかがすぐに分かるかと思う。

SELECT
  position,
  score,
  FIRST_VALUE(score) OVER (partition by position ORDER BY score DESC),
  score - FIRST_VALUE(score) OVER (partition by position ORDER BY score DESC) as difference

FROM
  FIRST_VALUE_TEST
position score first_value difference
DF 3 3 0
DF 2 3 -1
DF 1 3 -2
DF 0 3 -3
FW 10 10 0
FW 5 10 -5
GK 0 0 0
MF 4 4 0
MF 3 4 -1
MF 3 4 -1
MF 2 4 -2

IGNORE NULLS と RESPECT NULLS

SQL標準ではIGNORE NULLS と RESPECT NULLSが変更できるが、PostgreSQLでは変更できないので確認ができなかった。 IGNORE NULLS と RESPECT NULLSはnullの扱いをどうするかの設定になる。 設定の変更はできないがPostgreSQLではRESPECT NULLSがデフォルトとなっているので、RESPECT NULLSの動きだけでも確認した。

例として、スコアを計算する必要のない、監督であるCOACHの行をnullで追加してみた。 データとしては次のようになる。

CREATE TABLE FIRST_VALUE_TEST
(
 position  CHAR(6) ,
 score INTEGER
);

BEGIN TRANSACTION;
INSERT INTO FIRST_VALUE_TEST VALUES ('FW', 10);
INSERT INTO FIRST_VALUE_TEST VALUES ('FW', 5);
INSERT INTO FIRST_VALUE_TEST VALUES ('MF', 4);
INSERT INTO FIRST_VALUE_TEST VALUES ('MF', 3);
INSERT INTO FIRST_VALUE_TEST VALUES ('MF', 3);
INSERT INTO FIRST_VALUE_TEST VALUES ('MF', 2);
INSERT INTO FIRST_VALUE_TEST VALUES ('DF', 3);
INSERT INTO FIRST_VALUE_TEST VALUES ('DF', 2);
INSERT INTO FIRST_VALUE_TEST VALUES ('DF', 1);
INSERT INTO FIRST_VALUE_TEST VALUES ('DF', 0);
INSERT INTO FIRST_VALUE_TEST VALUES ('GK', 0);
INSERT INTO FIRST_VALUE_TEST VALUES ('COACH', null);
COMMIT;

このときに全体を対象としたSQLを実行すると先頭行のnullが一番最初の値となり、すべての行に適応されている。 つまり、PostgreSQLではRESPECT NULLSがデフォルトとなっていることがわかった。

SELECT
  position,
  score,
  FIRST_VALUE(score) OVER (ORDER BY score DESC)

FROM
  FIRST_VALUE_TEST
position score first_value
COACH (null) (null)
FW 10 (null)
FW 5 (null)
MF 4 (null)
MF 3 (null)
DF 3 (null)
MF 3 (null)
DF 2 (null)
MF 2 (null)
DF 1 (null)
DF 0 (null)
GK 0 (null)

まとめ

FIRST_VALUE 関数を使いながら動きを確認した。 サッカーのポジションとゴール数を例にして、確認することが出来た。 基準から計算するようなことがあれば使ってみるといいかも。 そのまま手を動かせるものにしたので、実行して確認してもらえれば👌