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

postgres

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
-- 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 方式解決,如下:

1
2
3
4
5
6
7
8
9
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_idtimestampdata
1100test_01
1100test_02
1100test_03
1101test_01
1101test_02
1101test_03
1101test_04

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

使用 rank() 方式

rank() 方式可以在 MySQL, SQLitePostgres 都支援,由於目前我開發模式都是本機使用 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_idtimestampdatarank
1101test_011
1101test_021
1101test_031
1101test_041
1100test_012
1100test_022
1100test_032

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

foo_idtimestampdata
11001_test_01
11011_test_01
11011_test_02
21002_test_01
21012_test_02
21022_test_03
31003_test_01
31033_test_02
31043_test_03
31053_test_04

我們需要拿到最新的版本

  • foo_id 為 1 時的 101 版本
  • foo_id 為 2 時的 102 版本
  • foo_id 為 3 時的 105 版本
1
2
3
4
5
select bar.* from 
(SELECT bar.*, 
  rank() OVER (PARTITION BY foo_id ORDER BY "timestamp" DESC) as rank
  FROM bar) bar
  where rank = 1

資料如下:

foo_idtimestampdatarank
11011_test_011
11011_test_021
21022_test_031
31053_test_041

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

  • foo_id 為 1 時的 101 版本
  • foo_id 為 2 時的 102 版本
  • foo_id 為 3 時的 100 版本 (100 最今近 102)
1
2
3
4
5
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_idtimestampdatarank
11011_test_011
11011_test_021
21022_test_031
31003_test_011

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


See also