■ はじめに
Hive でデータが積み重なるようなテーブル (例えば、ユーザ情報で入会・退会するようなデータ)に対して ユーザの最新/過去直近情報を取得するのに、 ROW_NUMBER + PARTITION BY を使ったので、メモする。 ついでに、RANK + PARTITION BY も載せておく。 他にも応用が利きそう。 なお、以下の関連記事も併せて読んでおいた方がいいかも。
Window関数 / 分析関数 ~ LAG / LEAD ~
https://dk521123.hatenablog.com/entry/2021/09/10/092850
今回の方法以外で過去直近情報を取得する方法については 以下の関連記事を参照のこと。
過去直近データを取得するには
https://dk521123.hatenablog.com/entry/2016/01/05/234938
目次
【1】ROW_NUMBER + PARTITION BY 1)構文 2)サンプル a) データ準備 b) SQL文 【2】RANK + PARTITION BY 1)構文 2)サンプル
【1】ROW_NUMBER + PARTITION BY
ROW_NUMBER
* 結果セットにシーケンス番号を振ってくれる関数
1)構文
ROW_NUMBER( ) OVER (PARTITION BY [partition1], ... ORDER BY [item1], ... [DESC|ACS])
例
-- 項目「category」に対して、PARTITION BYして -- 項目 date1, date2 と 複数指定した場合 ROW_NUMBER( ) OVER (PARTITION BY category ORDER BY date1 DESC, date2 DESC)
2)サンプル
ROW_NUMBER() OVER(PARTITION BY xxxx ORDER BY yyyyy DESC) を使う。 今回、環境は、PostgreSQL。
a) データ準備
-- CREATE TABLE CREATE TABLE "user_history" ( "user_id" BIGINT NULL DEFAULT NULL, "user_status" SMALLINT NULL DEFAULT 1, "updated_date" DATE NULL DEFAULT NULL ) ; COMMENT ON COLUMN "user_history"."user_id" IS 'user id'; COMMENT ON COLUMN "user_history"."user_status" IS '1:active, 2:removed'; COMMENT ON COLUMN "user_history"."updated_date" IS 'e.g. 2021-03-01'; -- INSERT INSERT INTO "user_history" ("user_id", "user_status", "updated_date") VALUES (1, 1, '2020-11-01'), (2, 1, '2020-11-01'), (3, 1, '2020-12-01'), (4, 1, '2020-12-01'), (5, 1, '2020-12-01'), (2, 2, '2021-01-01'), (3, 2, '2021-01-01'), (6, 1, '2021-01-01'), (7, 1, '2021-01-01'), (8, 1, '2021-01-01'), (7, 2, '2021-02-01'), (9, 1, '2021-02-01'), (10, 1, '2021-03-01'), (8, 2, '2021-03-01'), (7, 2, '2021-03-01');
b) SQL文
SELECT user_id, user_status, updated_date FROM ( SELECT ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY updated_date DESC) AS row_num, * FROM user_history AS a ) AS main WHERE main.row_num = 1 ORDER BY user_id;
出力結果
user_id user_status updated_date "1" "1" "2020-11-01" "2" "2" "2021-01-01" "3" "2" "2021-01-01" "4" "1" "2020-12-01" "5" "1" "2020-12-01" "6" "1" "2021-01-01" "7" "2" "2021-03-01" "8" "2" "2021-03-01" "9" "1" "2021-02-01" "10" "1" "2021-03-01"
【2】RANK + PARTITION BY
RANK
* 以下の関連記事を参照のこと。
https://dk521123.hatenablog.com/entry/2012/08/15/225233
1)構文
RANK( ) OVER (PARTITION BY [partition1], ... ORDER BY [item1], ... [DESC|ACS])
2)サンプル
以下の関連記事を参照のこと。 (「【2】PostgreSQL」の 「例2:その年の最新実行されたデータを一覧表示する」の 「SQL文 - その2(RANK OVER PARTITION BYを使ったやり方)」を参照)
グループ内のMAXデータを取得する
https://dk521123.hatenablog.com/entry/2018/10/06/230841
関連記事
Window関数 ~ 入門編 ~
https://dk521123.hatenablog.com/entry/2023/02/22/000000
Window関数 ~ ROW_NUMBER ~
https://dk521123.hatenablog.com/entry/2021/09/10/092850
Window関数 ~ LAG / LEAD ~
https://dk521123.hatenablog.com/entry/2021/09/10/092850
Window関数 ~ RANK / DENSE_RANK ~
https://dk521123.hatenablog.com/entry/2012/08/15/225233
過去直近データを取得するには
https://dk521123.hatenablog.com/entry/2016/01/05/234938
グループ内のMAXデータを取得する
https://dk521123.hatenablog.com/entry/2018/10/06/230841