■ はじめに

　Hive でデータが積み重なるようなテーブル
（例えば、ユーザ情報で入会・退会するようなデータ）に対して
ユーザの最新/過去直近情報を取得するのに、
ROW_NUMBER + PARTITION BY を使ったので、メモする。

他にも応用が利きそう。

なお、以下の関連記事も併せて読んでおいた方がいいかも。

Window関数 / 分析関数～ LAG / LEAD ～
https://dk521123.hatenablog.com/entry/2021/09/10/092850

今回の方法以外で過去直近情報を取得する方法については
以下の関連記事を参照のこと。

過去直近データを取得するには
https://dk521123.hatenablog.com/entry/2016/01/05/234938

【１】ROW_NUMBER + PARTITION BY

１）ROW_NUMBER

* 結果セットにシーケンス番号を振ってくれる関数
 => 詳細は以下の関連記事を参照の事

Window関数～ ROW_NUMBER ～
https://dk521123.hatenablog.com/entry/2012/08/19/201728

【２】構文

ROW_NUMBER( ) OVER (
  -- PARTITION BY でグループ化したい項目を指定
  PARTITION BY [partition1], ... 
  -- グループ化したデータに対して、順位が付けられる項目を指定する
  ORDER BY [item1], ... [DESC|ACS]) (NULLS { FIRST | LAST })

例

-- 項目「category」に対して、PARTITION BYして
-- 項目 date1, date2 と 複数指定した場合
ROW_NUMBER( ) OVER (
  PARTITION BY category
  ORDER BY date1 DESC, date2 DESC NULLS LAST)

１）Tips：NULLS { FIRST | LAST }

* 値がNullだった場合に「NULLS { FIRST | LAST }」を使うといい

NULLS { FIRST | LAST }とは？

* NULL値を非NULL値の前にするか後にするかを決定する
* DB によって非対応なDBがあるらしい
 => 詳細は以下の関連記事を参照の事

SQL ～ ORDER BY句～
https://dk521123.hatenablog.com/entry/2010/03/18/220719

【３】使いどころ

* 最新/過去直近情報を取得する場合

【４】サンプル

* 今回、環境は、PostgreSQL。

https://www.db-fiddle.com

例１：最新日付のデータを取得する

* 以下のデータ構造だとする
 + データIDは、user_id
 + 最新日付は、updated_date
 + updated_date = NULLだったら最新か分からないので入っているものを優先
  => NULLS LAST を使う
* ROW_NUMBER() OVER(
　PARTITION BY user_id ORDER BY updated_date DESC NULLS LAST)
　を使う。

データ準備

-- 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'), -- #2 '2020-11-01'
    (3, 1, '2020-12-01'), -- #3 '2020-12-01'
    (4, 1, '2020-12-01'),
    (5, 1, '2020-12-01'),
    (2, 2, '2021-01-01'), -- #2 '2021-01-01' <-
    (3, 2, '2021-01-01'), -- #3 '2021-01-01' <-
    (6, 1, '2021-01-01'),
    (7, 1, '2021-01-01'), -- #7 '2021-01-01'
    (8, 1, '2021-01-01'), -- #8 '2021-01-01'
    (7, 2, '2021-02-01'), -- #7 '2021-02-01'
    (9, 1, '2021-02-01'),
    (10, 1, '2021-03-01'),
    (8, 2, '2021-03-01'), -- #8 '2021-03-01' <-
    (7, 2, '2021-03-01'), -- #7 '2021-03-01' <-
    (2, 1, NULL); -- #2 NULL

SQL文

SELECT
 user_id,
 user_status,
 updated_date
FROM
(
SELECT
  ROW_NUMBER() OVER(
  PARTITION BY user_id ORDER BY updated_date DESC
  NULLS LAST) AS row_num,
  *
FROM
  user_history AS a
) AS main
WHERE
 main.row_num = 1
ORDER BY
  user_id;