2

On postgres I have data like:

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

And I'm looking to get the top 5 of device_model per device_type.

for the moment I'm doing :

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

But it doesn't render top5 for each device_type. What can I use to do this? Seems I need to do a UNION.

Can I do something like :

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"
5
  • What order constitutes "top" ?
    – Philᵀᴹ
    Oct 5 '16 at 14:12
  • number of times device_model is present. In my case "samsung" 2, "iphone 4" for device_type 1, "ipad" 2 for device_type 0.
    – Mio
    Oct 5 '16 at 14:14
  • device_model and device_type are independent and represent two grouping variables?
    – pietrop
    Oct 14 '16 at 10:01
  • sorry @pietrop I'm not sure I understand your question. The final data looks like gist.github.com/anonymous/35d9a52ec3ea78024abc48e80f1a1314
    – Mio
    Oct 14 '16 at 13:18
  • @BeniMio Your solution doesn't handle devices that have the same rank. Take a look at my answer. Hope it helps :)
    – pietrop
    Oct 14 '16 at 13:43
2

This is the right query:

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

It handles models that has the same rank, displaying more than 5 rows per device type just in case.

In order to test it, create a table and fill it with data:

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)
;

The output of the initial query is the following:

 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)

Feel free to ask questions about the logic behind this solution.

2
  • 1
    Wow @pietrop it's work. I will dig to documentation do understand properly rank(), OVER and PARTITION.
    – Mio
    Oct 14 '16 at 15:33
  • 1
    @BeniMio they are used to windowing over a subset of rows related to the currently evaluated row. OVER creates a window, rank() returns the ordering number for each rows of the subset and PARTITION behaves like GROUP BY. You can't use the default GROUP BY inside a window, so they invented PARTITION. Here PARTITION is used to apply rank() to each "partition" (or "group") of the window and the partitioning criteria is device_type. ORDER BY orders the subset of rows inside each partition, so you get the rank() of each row of the subset ordered by COUNT(id) desc.
    – pietrop
    Oct 14 '16 at 15:59
0

For the moment I'm doing :

(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;

It's working but maybe it can be refactored or changed.

2
  • Why are you using UNION?
    – pietrop
    Oct 14 '16 at 9:59
  • Because the two request are different. Didn't found a way to do it without
    – Mio
    Oct 14 '16 at 13:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.