在 2 個不同的 where 子句中獲得前 5 名

在 postgres 上，我有如下數據：

id | device_model | device_type
1  | "samsung"    | 1
2  | "iphone 4"   | 1
3  | "samsung"    | 1
4  | "ipad"       | 0
5  | "ipad"       | 0

我希望每個 device_type 獲得前 5 個 device_model。

目前我正在做：

SELECT device_model, COUNT(*)
FROM devices
GROUP BY device_model  
ORDER BY COUNT(*) DESC
limit 5;

但它不會為每個 device_type 呈現 top5。我可以用什麼來做到這一點？看來我需要做一個聯合。

我可以做類似的事情：

SELECT device_model, COUNT(*)
FROM devices
WHERE device_type = 1
GROUP BY device_model  
ORDER BY COUNT(*) DESC
LIMIT 5 
UNION
SELECT device_model, COUNT(*)
FROM devices
WHERE device_type = 0
GROUP BY device_model  
ORDER BY COUNT(*) DESC
LIMIT 5;

ERROR:  syntax error at or near "UNION"
LINE 7: UNION ALL
       ^
Query failed
PostgreSQL said: syntax error at or near "UNION"

這是正確的查詢：

WITH counts AS (
   SELECT device_model, device_type, COUNT(id) AS cnt, rank() OVER (PARTITION BY device_type ORDER BY COUNT(id) DESC) AS model_rank
   FROM devices
   GROUP BY device_model, device_type
)
SELECT device_model, device_type, cnt, model_rank
FROM counts
WHERE model_rank <= 5
ORDER BY device_type, model_rank

它處理具有相同等級的模型，以防萬一，每種設備類型顯示超過 5 行。

為了測試它，創建一個表並用數據填充它：

CREATE TABLE devices (
   id serial,
   device_model text,
   device_type integer
);

INSERT INTO devices (device_model, device_type)
   VALUES
       ('samsung', 1),
       ('iphone4', 1),
       ('samsung', 1),
       ('iphone4', 1),
       ('samsung', 1),
       ('samsung', 1),
       ('samsung', 1),
       ('samsung', 1),
       ('ipad', 0),
       ('ipad', 0),
       ('ipad', 0),
       ('ipad', 0),
       ('ipad', 0),
       ('iphone6', 1),
       ('iphone3', 1),
       ('iphone3', 1),
       ('iphone3', 1),
       ('iphone2', 1),
       ('iphone1', 1),
       ('iphone1', 1),
       ('iphone1', 1),
       ('iphone1', 1),
       ('iphone1', 1),
       ('iphone1', 1),
       ('kindle', 0),
       ('kindle', 0),
       ('kindle', 0),
       ('kindle', 0)
;

初始查詢的輸出如下：

device_model | device_type | cnt | model_rank 
--------------+-------------+-----+------------
ipad         |           0 |   5 |          1
kindle       |           0 |   4 |          2
iphone1      |           1 |   6 |          1
samsung      |           1 |   6 |          1
iphone3      |           1 |   3 |          3
iphone4      |           1 |   2 |          4
iphone6      |           1 |   1 |          5
iphone2      |           1 |   1 |          5
(8 rows)

隨意詢問有關此解決方案背後的邏輯的問題。

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