[Redshift] ウィンドウ関数：LAG、LEADを使ってスケジュールの日程間隔などを表示

はじめに

既存のテーブルの情報からスケジュールの日程間隔を調べたい場合に都合の良い関数が有ると言う事で調べて使ってみました。

環境

Mac OSX 10.10.5
Redshift 1.0.1125

準備

テーブル

２人のアーティストのツアー日程表です。

CREATE SCHEMA IF NOT EXISTS blog;

CREATE TABLE 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');

postgres=# SELECT * FROM blog.tour_schedule;
 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)

ブッキング順に登録した結果、バラバラで見辛い、みたいなイメージの表です。

ウィンドウ関数の簡単な使い方

LAG

現在の行より前を範囲に指定できます。

SELECT
  id
  , LAG(id,1) OVER(ORDER BY id) 
FROM
  blog.tour_schedule
LIMIT 4;

 id | lag 
----+-----
  1 |    
  2 |   1
  3 |   2
  4 |   3
(4 rows)

OVER(ORDER BY id)でid順と指定。
LAG(id,1)で、idの一つ前を指定。
1の前は無いのでNULLになります。

LEAD

現在の行より後を範囲に指定できます。

SELECT
  id
  , LEAD(id,1) OVER(ORDER BY id)
FROM
  blog.tour_schedule
LIMIT 4;

 id | lead 
----+------
  1 |    2
  2 |    3
  3 |    4
  4 |    5
(4 rows)

LAGの逆。
見た目上、idは4までですが、参照元のテーブルには5以上も存在するので、5が表示されています。

応用

目標

アーティストごとに、イベントの前後の日付と日数の差を見たい。

SQL

SELECT 
  *
  , LAG(day, 1)       OVER(PARTITION BY artist ORDER BY day) AS previous_day
  , day - LAG(day, 1) OVER(PARTITION BY artist ORDER BY day) AS previous_diff
  , LEAD(day, 1)      OVER(PARTITION BY artist ORDER BY day) AS next_day
  , LEAD(day, 1)      OVER(PARTITION BY artist ORDER BY day) - day AS next_diff
  , LEAD(venue, 1)    OVER(PARTITION BY artist ORDER BY day) AS next_venue
FROM
  (SELECT * FROM blog.tour_schedule ORDER BY artist, day)

「PARTITION BY」でアーティスト毎にしています。
previous_day : 一つ前の公演日。
previous_diff : 一つ前の公演日との日数の差。
next_day : 一つ後の公演日。
next_diff : 一つ後の公演日との日数の差。
next_venue : 一つ後の公演日の場所。

実行結果

 id |       artist       |    day     | country |   city   |        venue        | previous_day | previous_diff |  next_day  | next_diff |     next_venue      
----+--------------------+------------+---------+----------+---------------------+--------------+---------------+------------+-----------+---------------------
  1 | Dimitri From Paris | 2016-12-02 | Kanada  | Tronto   | NEST                |              |               | 2016-12-03 |         1 | House Of Yes
  2 | Dimitri From Paris | 2016-12-03 | USA     | New York | House Of Yes        | 2016-12-02   |             1 | 2016-12-17 |        14 | Sound Museum Vision
  4 | Dimitri From Paris | 2016-12-17 | Japan   | Tokyo    | Sound Museum Vision | 2016-12-03   |            14 | 2017-01-13 |        27 | The Nest
  8 | Dimitri From Paris | 2017-01-13 | UK      | London   | The Nest            | 2016-12-17   |            27 |            |           | 
  3 | Timmy Regisford    | 2016-12-04 | USA     | New York | Output              |              |               | 2016-12-22 |        18 | Club Move
  5 | Timmy Regisford    | 2016-12-22 | Japan   | Kansai   | Club Move           | 2016-12-04   |            18 | 2016-12-24 |         2 | Precious Hall
  6 | Timmy Regisford    | 2016-12-24 | Japan   | Hokkaido | Precious Hall       | 2016-12-22   |             2 | 2016-12-30 |         6 | Club JB's
  7 | Timmy Regisford    | 2016-12-30 | Japan   | Chubu    | Club JB's           | 2016-12-24   |             6 |            |           | 
(8 rows)

さいごに

LAGとREADを使用せずに同様の事をやろうとするとかなりややこしくなりそうなので、こんなに簡単に値を取得できるのはありがたいですね。