有沒有辦法 SELECT n ON（比如 DISTINCT ON，但每個都不止一個）

我有一個us_customers看起來像這樣的表（有數十萬行）：

+----------+----------+
|    id    | us_state |
+----------+----------+
| 12345678 | MA       |
| 23456781 | AL       |
| 34567812 | GA       |
| 45678123 | FL       |
| 56781234 | AZ       |
| 67812345 | MA       |
| 78123456 | CO       |
| 81234567 | FL       |
+----------+----------+

…我想n從每個客戶中選擇一個樣本us_state。

有沒有辦法在 PostgreSQL 9.3 中乾淨地做到這一點？

us_state我可以通過以下方式輕鬆地從每個客戶那裡獲得一個客戶：

SELECT DISTINCT ON (us_state) id
FROM us_customers
ORDER BY us_state;

但是，如果我想要來自每個州的三個客戶，有沒有一種方法可以在不多次執行相同查詢的情況下做到這一點？

您可以使用視窗函式進行編號和排序id，並且只保留第一個值：us_state``ROW_NUMBER()``n

SELECT * 
FROM (
 SELECT *
   , ROW_NUMBER() OVER(PARTITION BY us_state ORDER BY id) as n
 FROM data
) as ord
WHERE n <= 2
ORDER BY us_state
;

或者您可以使用子查詢進行 CROSS JOIN：

SELECT l.*
FROM (
 SELECT DISTINCT us_state FROM data
) as s
CROSS JOIN LATERAL (
 SELECT * 
 FROM data d
 WHERE d.us_state = s.us_state
 ORDER BY id
 LIMIT 2
) as l
ORDER BY l.us_state
;

• 範例 SQL Fiddle在這裡
• 我使用了每個州 1 到 3 行的小樣本。因此，我只限制為 2 個值
• 我訂購了它，ids但您可以更改它並按最適合您的方式訂購

用我的小樣本輸出：

      id | us_state | n
     123 |       AL | 1 
     456 |       AL | 2 
56781234 |       AZ | 1 
78123456 |       CO | 1 
45678123 |       FL | 1 
81234567 |       FL | 2 
34567812 |       GA | 1 
     123 |       MA | 1 
     456 |       MA | 2 

請注意，n 是 ROW_NUMBER 的結果，在第二個查詢中不存在。在大表上，分區 (us-state) 和 order (id here) 列上的索引會有所幫助。

使用的樣本：

CREATE TABLE data
   ("id" int, "us_state" varchar(2))
;

INSERT INTO data
   ("id", "us_state")
VALUES
   (12345678, 'MA'),
   (123, 'MA'),
   (456, 'MA'),
   (23456781, 'AL'),
   (123, 'AL'),
   (456, 'AL'),
   (34567812, 'GA'),
   (45678123, 'FL'),
   (56781234, 'AZ'),
   (67812345, 'MA'),
   (78123456, 'CO'),
   (81234567, 'FL')
;

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