獲取每個 ID 的最後 5 個不同值

我正在使用 PostgreSQL 9.4。

我有一個包含以下條目的表：

id | postcode | date_created
---+----------+-----------------
14 | al2 2qp  | 2015-09-23 14:46:57
14 | al2 2qp  | 2015-09-23 14:51:07
14 | sp2 8ag  | 2015-09-23 14:56:11
14 | se4      | 2015-09-23 16:12:05
17 | e2       | 2015-09-23 16:15:35
17 | fk20 8ru | 2015-09-23 16:28:35
17 | fk20 8ru | 2015-09-23 16:35:51
17 | se2      | 2015-09-23 16:36:17
17 | fk20 8ru | 2015-09-23 16:36:22
17 | fk20 8ru | 2015-09-23 16:37:04
17 | se1      | 2015-09-23 16:37:11
17 | fk20 8ru | 2015-09-23 16:37:15
17 | se1 8ga  | 2015-09-24 09:52:46
17 | se1      | 2015-09-24 10:01:19
17 | hp27 9rz | 2015-09-24 10:05:27
17 | hp27 9rz | 2015-09-24 10:05:29
17 | se1      | 2015-09-24 10:19:46
14 | tn21 8qb | 2015-09-24 14:49:05
14 | tn21 8qb | 2015-09-24 15:42:45
14 | tn21 8qb | 2015-09-24 17:38:06
14 | n4 1ny   | 2015-09-25 14:49:10

我想要實現的是一個查詢，它為每個 id返回 5 個最近的唯一郵政編碼記錄：

id | postcode
---+---------
14 | n4 1ny
14 | tn21 8qb
14 | se4
14 | sp2 8ag
14 | al2 2qp
17 | se1
17 | hp27 9rz
17 | se1 8ga
17 | fk20 8ru
17 | se2

實現這一目標的最佳方法是什麼？DISTINCT我一直在玩弄子查詢，但是在執行and時在訂購它們時總是碰壁GROUP BY。

可能有很多方法可以做到這一點。首先想到的是使用視窗函式：

SELECT 
   id, postcode
FROM
 ( SELECT id, postcode, 
          ROW_NUMBER() OVER (PARTITION BY id
                             ORDER BY MAX(date_created) DESC
                            ) AS rn
   FROM tablename
   GROUP BY id, postcode
 ) AS t
WHERE
   rn <= 5
ORDER BY 
   id, rn ;

在SQLfiddle進行測試。

如果有平局，比如第 5、6 和 7postcode個id有相同的date_created，結果中只有一個（選擇是任意的）。如果您在這些情況下想要所有綁定的郵政編碼，請使用RANK()而不是ROW_NUMBER().

---

另一種選擇是使用LATERAL語法。我不確定哪個會更有效，它可能取決於兩列 (id和postcode) 的值分佈，即整個表中有多少不同的 id，每個 id 有多少不同的郵政編碼以及每個 (id) 有多少行, 郵政編碼）組合。

SELECT 
   t.id, ti.postcode
FROM
   ( SELECT DISTINCT id
     FROM tablename
   ) AS t
 CROSS JOIN LATERAL
   ( SELECT tt.postcode,
            MAX(tt.date_created) AS date_created
     FROM tablename AS tt
     WHERE tt.id = t.id
     GROUP BY tt.postcode
     ORDER BY date_created DESC
     LIMIT 5
   ) AS ti 
ORDER BY 
   t.id, ti.date_created DESC;

在 on 上添加索引(id, postcode, date_created)也是一個好主意 - 或 on (id, postcode, date_created DESC)。

引用自：https://dba.stackexchange.com/questions/116142