[SQL] 如何從單一資料表取得每個 key 前 n 筆資料

最近專案需求需要實現單筆資料的版本控制，所以會有一張表 (foo) 專門儲存 key 資料，而有另外一張表 (bar) 專門存 Data 資料，那在 bar 這張表怎麼拿到全部 key 的最新版本資料？底下先看看 schema 範例

-- foo table
DROP TABLE IF EXISTS "foo";
CREATE TABLE `foo` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  `name` TEXT NULL,
  `key` TEXT NULL,
  `created_at` DATETIME NULL,
  `updated_at` DATETIME NULL
);

-- bar table
CREATE TABLE `bar` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 
  `foo_id` INTEGER NULL, 
  `is_deleted` INTEGER NULL, 
  `timestamp` INTEGER NULL, 
  `created_at` DATETIME NULL, 
  `data` TEXT NULL, 
  `memo` TEXT NULL
)

其中 foo 資料表內的 name + key 是唯一值，所以會是一對多狀態，一把 key 會對應到 bar 內的多組資料。而 bar 內的 timestamp 則是用來處理版本控制，每一次的修改就會多出一組新的 timestamp 資料。底下會來介紹該如何取得每一把 key 的前幾筆 data 資料。

使用 UNION 方式

先講資料不多的時候可以透過 UNION 方式解決，如下:

select * from bar where foo_id=1 order by timestamp desc limit 3
UNION
select * from bar where foo_id=2 order by timestamp desc limit 3
UNION
select * from bar where foo_id=3 order by timestamp desc limit 3
.
.
.
select * from bar where foo_id=n order by timestamp desc limit 3

這個做法其實還不預期可以解決版本控制的問題，假設同一筆 foo_id 的資料在每一個 timestamp 版本筆數不一樣，這樣就會噴錯

foo_id	timestamp	data
1	100	test_01
1	100	test_02
1	100	test_03
1	101	test_01
1	101	test_02
1	101	test_03
1	101	test_04

如果只透過 limit 方式根本拿不到 timestamp 為 101 的資料 (因為有四筆，透過 limit 只能拿到 3 筆)。所以這個解法完全不適合。

使用 rank() 方式

rank() 方式可以在 MySQL, SQLite 或 Postgres 都支援，由於目前我開發模式都是本機使用 SQLite，Production 環境則用 Postgres，所以在寫 SQL 同時都會兼顧是否三者都能並行 (執行開源專案養成的 XD)，這時候來實驗看看用 rank 來標記 timestamp:

1
2
3

SELECT bar.*, 
  rank() OVER (PARTITION BY foo_id ORDER BY "timestamp" DESC) as rank
  FROM bar

就會拿到底下資料

foo_id	timestamp	data	rank
1	101	test_01	1
1	101	test_02	1
1	101	test_03	1
1	101	test_04	1
1	100	test_01	2
1	100	test_02	2
1	100	test_03	2

這時候我們要拿 foo_id 為 1 時的資料，就可以透過 rank = 1 方式解決 limit 的問題。接下來需要處理如何拿每一個 foo_id 的最新版本 (timestamp) 資料。假設資料如下:

foo_id	timestamp	data
1	100	1_test_01
1	101	1_test_01
1	101	1_test_02
2	100	2_test_01
2	101	2_test_02
2	102	2_test_03
3	100	3_test_01
3	103	3_test_02
3	104	3_test_03
3	105	3_test_04

我們需要拿到最新的版本

foo_id 為 1 時的 101 版本
foo_id 為 2 時的 102 版本
foo_id 為 3 時的 105 版本

select bar.* from 
(SELECT bar.*, 
  rank() OVER (PARTITION BY foo_id ORDER BY "timestamp" DESC) as rank
  FROM bar) bar
  where rank = 1

資料如下:

foo_id	timestamp	data	rank
1	101	1_test_01	1
1	101	1_test_02	1
2	102	2_test_03	1
3	105	3_test_04	1

透過 rank = 1 就可以拿到每一筆 foo 的最新版本。接著假設我們想拿到 timestamp 為 102 的版本該如何處理，這時候我們就需要找尋每一筆 foo 的版本為最接近 102。

foo_id 為 1 時的 101 版本
foo_id 為 2 時的 102 版本
foo_id 為 3 時的 100 版本 (100 最今近 102)

select bar.* from 
(SELECT bar.*, 
  rank() OVER (PARTITION BY foo_id ORDER BY "timestamp" DESC) as rank
  FROM bar where "timestamp" <= 102) bar
  where rank = 1

資料如下:

foo_id	timestamp	data	rank
1	101	1_test_01	1
1	101	1_test_02	1
2	102	2_test_03	1
3	100	3_test_01	1

以上就是透過 rank() 來解決資料版本控制問題。如果大家有更好的解法或建議，歡迎在底下留言。

使用 UNION 方式

使用 rank() 方式

See also