主页 / dba / 问题 / 151483
Accepted
Mio
Asked: 2016-10-06 05:56:52 +0800 CST

在 2 个不同的 where 子句中获得前 5 名

  • 772

在 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。我可以用什么来做到这一点？看来我需要做一个 UNION。

我可以做类似的事情吗：

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"
postgresql group-by

2 个回答

  1. Best Answer

    这是正确的查询：

    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)

    随时询问有关此解决方案背后的逻辑的问题。

    • 2

  2. 目前我正在做：

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

    它正在工作，但也许可以重构或更改。

    • 0

相关问题