【Hive】【SQL】Window関数 ~ ROW_NUMBER / RANK + PARTITION BY ~

■ はじめに

 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