[Redshift] ウィンドウ関数：ROW_NUMBERを使用してグループごとの最新日のみを抽出する

はじめに

テーブルを作成する際に、参照したい別のテーブルの状態が理想的な形になっていない場合、そのままではテーブルを作成できない事があります。そこで、ある状況下でどうしたら理想的な形にするかを紹介します。

環境

MacOSX 10.10.5 Yosemite
Redshift 1.0.1125

テーブルの準備

商品の販売日リスト：item_sale_update

CREATE SCHEMA IF EXISTS blog;

DROP TABLE IF EXISTS blog.item_sale_update;

CREATE TABLE blog.item_sale_update (
  id INTEGER
  , item_name VARCHAR(8)
  , group_id INTEGER
  , on_off INTEGER
  , update_date DATE
);

INSERT INTO blog.item_sale_update
VALUES
  (1,'AAA',1,0,'2016-01-01')
 ,(2,'BBB',1,1,'2016-02-01')
 ,(3,'CCC',1,0,'2016-03-01')
 ,(4,'DDD',1,0,'2016-04-01')
 ,(5,'EEE',2,1,'2016-05-01')
 ,(6,'FFF',2,1,'2016-06-01')
 ,(7,'GGG',2,0,'2016-07-01')
 ,(8,'HHH',2,0,'2016-08-01')
 ,(9,'III',2,0,'2016-09-01')
 ,(10,'JJJ',3,1,'2016-10-01')
 ,(11,'KKK',3,1,'2016-11-01')
 ,(12,'LLL',3,0,'2016-12-01');

testdb=# SELECT * FROM blog.item_sale_update ORDER BY id;
 id | item_name | group_id | on_off | update_date 
----+-----------+----------+--------+-------------
  1 | AAA       |        1 |      0 | 2016-01-01
  2 | BBB       |        1 |      1 | 2016-02-01
  3 | CCC       |        1 |      0 | 2016-03-01
  4 | DDD       |        1 |      0 | 2016-04-01
  5 | EEE       |        2 |      1 | 2016-05-01
  6 | FFF       |        2 |      1 | 2016-06-01
  7 | GGG       |        2 |      0 | 2016-07-01
  8 | HHH       |        2 |      0 | 2016-08-01
  9 | III       |        2 |      0 | 2016-09-01
 10 | JJJ       |        3 |      1 | 2016-10-01
 11 | KKK       |        3 |      1 | 2016-11-01
 12 | LLL       |        3 |      0 | 2016-12-01
(12 rows)

カラムの意味
・id = 登録ID
・item_name = 商品名
・group_id = 商品グループID
・on_off = ある条件を満たすかどうか
・update_date = 販売日

構文

Amazon Redshift | ROW_NUMBER ウィンドウ関数

ROW_NUMBER () OVER([ PARTITION BY expr_list ]
                   [ ORDER BY order_list ])

簡単な使い方

実行目的

group_idごとのupdate_date順（昇順）にランクを付けて表示する。

SQL

SELECT
  group_id
  , update_date
  , ROW_NUMBER() OVER(PARTITION BY group_id ORDER BY update_date)
FROM
  blog.item_sale_update
ORDER BY
  group_id, update_date;

実行結果

.
 group_id | update_date | row_number 
----------+-------------+------------
        1 | 2016-01-01  |          1
        1 | 2016-02-01  |          2
        1 | 2016-03-01  |          3
        1 | 2016-04-01  |          4
        2 | 2016-05-01  |          1
        2 | 2016-06-01  |          2
        2 | 2016-07-01  |          3
        2 | 2016-08-01  |          4
        2 | 2016-09-01  |          5
        3 | 2016-10-01  |          1
        3 | 2016-11-01  |          2
        3 | 2016-12-01  |          3
(12 rows)

応用してみる

目的

on_off=0の中から、group_idごとにupdate_dateの最新日のみを表示する。

SQL

SELECT
  *
FROM
  (
   SELECT
     id
     , item_name
     , group_id
     , on_off
     , update_date
     , ROW_NUMBER() OVER(PARTITION BY group_id ORDER BY update_date DESC) AS rank
   FROM
     blog.item_sale_update
   WHERE on_off = 0
  )
WHERE rank = 1
ORDER BY group_id;

実行結果

.
 id | item_name | group_id | on_off | update_date | rank 
----+-----------+----------+--------+-------------+------
  4 | DDD       |        1 |      0 | 2016-04-01  |    1
  9 | III       |        2 |      0 | 2016-09-01  |    1
 12 | LLL       |        3 |      0 | 2016-12-01  |    1
(3 rows)

解説

まず、5〜14行目のサブクエリで下記のテーブルを作成します。
rankはupdate_dateの降順です。

.
 id | item_name | group_id | on_off | update_date | rank 
----+-----------+----------+--------+-------------+------
  1 | AAA       |        1 |      0 | 2016-01-01  |    3
  3 | CCC       |        1 |      0 | 2016-03-01  |    2
  4 | DDD       |        1 |      0 | 2016-04-01  |    1
  7 | GGG       |        2 |      0 | 2016-07-01  |    3
  8 | HHH       |        2 |      0 | 2016-08-01  |    2
  9 | III       |        2 |      0 | 2016-09-01  |    1
 12 | LLL       |        3 |      0 | 2016-12-01  |    1
(7 rows)

その後、rank=1のレコードのみを抽出しています。

さいごに

実際の案件でも似たような状況で使用し、便利だと感じました。