この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
はじめに
テーブルを作成する際に、参照したい別のテーブルの状態が理想的な形になっていない場合、そのままではテーブルを作成できない事があります。そこで、ある状況下でどうしたら理想的な形にするかを紹介します。
環境
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のレコードのみを抽出しています。
さいごに
実際の案件でも似たような状況で使用し、便利だと感じました。