[Redshift] ウィンドウ関数:FIRST_VALUE、LAST_VALUEで文字列の最小値・最大値を取得

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

はじめに

最小値と最大値を取得する関数にはMINとMAXが存在しますが、文字列で実現するにはFIRST_VALUEとLAST_VALUEを使用します。

環境

MacOSX 10.10.5 Yosemite
Redshift 1.0.1125

テーブルの準備

ミュージシャンのツアースケジュール:tour_schedule

CREATE SCHEMA IF NOT EXISTS blog;

CREATE TABLE IF NOT EXISTS blog.tour_schedule (
  id INTEGER
  , artist VARCHAR(32)
  , day DATE
  , Country VARCHAR(16)
  , City VARCHAR(16)
  , Venue VARCHAR(24)
  , PRIMARY KEY(id)
);
INSERT INTO
  blog.tour_schedule 
VALUES
    (1,'Dimitri From Paris','2016-12-02','Kanada','Tronto','NEST')
  , (2,'Dimitri From Paris','2016-12-03','USA','New York','House Of Yes')
  , (3,'Timmy Regisford','2016-12-04','USA','New York','Output')
  , (4,'Dimitri From Paris','2016-12-17','Japan','Tokyo','Sound Museum Vision')
  , (5,'Timmy Regisford','2016-12-22','Japan','Kansai','Club Move')
  , (6,'Timmy Regisford','2016-12-24','Japan','Hokkaido','Precious Hall')
  , (7,'Timmy Regisford','2016-12-30','Japan','Chubu','Club JB''s')
  , (8,'Dimitri From Paris','2017-01-13','UK','London','The Nest');
testdb=# SELECT * FROM blog.tour_schedule ORDER BY id;
 id |       artist       |    day     | country |   city   |        venue        
----+--------------------+------------+---------+----------+---------------------
  1 | Dimitri From Paris | 2016-12-02 | Kanada  | Tronto   | NEST
  2 | Dimitri From Paris | 2016-12-03 | USA     | New York | House Of Yes
  3 | Timmy Regisford    | 2016-12-04 | USA     | New York | Output
  4 | Dimitri From Paris | 2016-12-17 | Japan   | Tokyo    | Sound Museum Vision
  5 | Timmy Regisford    | 2016-12-22 | Japan   | Kansai   | Club Move
  6 | Timmy Regisford    | 2016-12-24 | Japan   | Hokkaido | Precious Hall
  7 | Timmy Regisford    | 2016-12-30 | Japan   | Chubu    | Club JB's
  8 | Dimitri From Paris | 2017-01-13 | UK      | London   | The Nest
(8 rows)

構文

Amazon Redshift | FIRST_VALUE および LAST_VALUE ウィンドウ関数
ウィンドウ関数の構文の概要

FIRST_VALUE | LAST_VALUE
( expression [ IGNORE NULLS | RESPECT NULLS ] ) OVER([ PARTITION BY expr_list ]
                                                     [ ORDER BY order_list frame_clause ])

frame_clauseの記述は任意です。

MIN/MAXとの比較

目的

アーティスト名ごとの日程順に並び替えして、最初と最後の場所を表示する。

MIN/MAXの場合

SELECT 
  artist
  , "day"
  , venue
  , MIN(venue) OVER(PARTITION BY artist ORDER BY "day" 
                    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) 
  , MAX(venue) OVER(PARTITION BY artist ORDER BY "day" 
                    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) 
FROM
  blog.tour_schedule
ORDER BY
  artist, "day";
.
       artist       |    day     |        venue        |     min      |      max      
--------------------+------------+---------------------+--------------+---------------
 Dimitri From Paris | 2016-12-02 | NEST                | House Of Yes | The Nest
 Dimitri From Paris | 2016-12-03 | House Of Yes        | House Of Yes | The Nest
 Dimitri From Paris | 2016-12-17 | Sound Museum Vision | House Of Yes | The Nest
 Dimitri From Paris | 2017-01-13 | The Nest            | House Of Yes | The Nest
 Timmy Regisford    | 2016-12-04 | Output              | Club JB's    | Precious Hall
 Timmy Regisford    | 2016-12-22 | Club Move           | Club JB's    | Precious Hall
 Timmy Regisford    | 2016-12-24 | Precious Hall       | Club JB's    | Precious Hall
 Timmy Regisford    | 2016-12-30 | Club JB's           | Club JB's    | Precious Hall
(8 rows)

何を基準としているのか分かりませんが、最大値と最小値が一致していません。

FIRST_VALUE/LAST_VALUEの場合

SELECT
  artist
  , "day"
  , venue
  , FIRST_VALUE(venue) OVER(PARTITION BY artist ORDER BY day
                            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
  ,  LAST_VALUE(venue) OVER(PARTITION BY artist ORDER BY day
                            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM blog.tour_schedule
ORDER BY artist, "day";
.
       artist       |    day     |        venue        | first_value | last_value 
--------------------+------------+---------------------+-------------+------------
 Dimitri From Paris | 2016-12-02 | NEST                | NEST        | The Nest
 Dimitri From Paris | 2016-12-03 | House Of Yes        | NEST        | The Nest
 Dimitri From Paris | 2016-12-17 | Sound Museum Vision | NEST        | The Nest
 Dimitri From Paris | 2017-01-13 | The Nest            | NEST        | The Nest
 Timmy Regisford    | 2016-12-04 | Output              | Output      | Club JB's
 Timmy Regisford    | 2016-12-22 | Club Move           | Output      | Club JB's
 Timmy Regisford    | 2016-12-24 | Precious Hall       | Output      | Club JB's
 Timmy Regisford    | 2016-12-30 | Club JB's           | Output      | Club JB's
(8 rows)

SQLはMIN/MAXとほぼ同じですが、結果が正しく表示されました。
PARTITION BY でアーティスト名で仕切り、
ORER BY で並び替えの対象カラムに日付を指定、
ROWS でレコード全体を範囲として指定しています。

応用してみる

目的

2016年12月15日〜2016日12月24までの9日間の日程と次の予定日と場所を表示する。

クエリ

SELECT *
FROM 
(
 SELECT
   artist
   , "day"
   , venue
   , LAST_VALUE("day") OVER(PARTITION BY artist ORDER BY "day" 
                            ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS next_day
   , LAST_VALUE(venue) OVER(PARTITION BY artist ORDER BY "day" 
                            ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS next_venue
 FROM
   blog.tour_schedule
 ORDER BY artist, "day"
)
WHERE
  "day" BETWEEN '2016-12-15' AND '2016-12-24';
.
       artist       |    day     |        venue        |  next_day  |  next_venue   
--------------------+------------+---------------------+------------+---------------
 Dimitri From Paris | 2016-12-17 | Sound Museum Vision | 2017-01-13 | The Nest
 Timmy Regisford    | 2016-12-22 | Club Move           | 2016-12-24 | Precious Hall
 Timmy Regisford    | 2016-12-24 | Precious Hall       | 2016-12-30 | Club JB's
(3 rows)

OVER 句の frame_clause で現在の行〜次の行を指定しています。
また、4行目からのSELECTのWHEREで
"day" BETWEEN '2016-12-15' AND '2016-12-24' と指定するとその範囲でしかLAST_VALUEの値が取得できないので、一度全体のレコードから作成してから、WHEREで範囲を指定しています。

さいごに

基本的な使い方だけではなく、自分なりに応用的なクエリを作成してみると挙動が良く分かると思います。