Sort by frequency of rows in associated table

Question

I have 2 tables. Products and Stores. A product belongs to a store.

I would like to order stores by the amount of products that belong to them in descending order that were created in the last month. So far I have...

Product.where("created_at > ?", 1.month.ago).group_by { |p| p.store }.sort_by { |k,v| v.count }.reverse.map{|a| a[0]}

But this is very slow as the group_by function. I should note that I am using PostgreSQL, which has meant that some solutions I have found on the internet have not worked for me.

Created in the last month? The store? The products? Only count products that were created last month? As always, table definitions and your version of Postgres would be helpful. — Erwin Brandstetter, Dec 22 '14 at 16:15
Products that have been created in the last month. My version of Postgres is 9.2.4. — tob88, Dec 22 '14 at 16:27

score 0 · Answer 1 · edited May 23 '17 at 10:33

Assuming you want to only count products created in the "last month" (counting back from "now"):

SELECT store_id 
FROM   product
WHERE  created_at > now() - interval '1 month'
GROUP  BY store_id 
ORDER  BY count(*) DESC;

I didn't even include the store table. You only get the store_id and no stores without any qualifying products. This is as fast as it gets.
An index on created_at would be instrumental. A multicolumn index on (created_at, store_id) would optimize further. More expensive, more specialized, but faster for the query at hand. In pg 9.2+ you could get index-only scans out of it.

To include columns of the store table as well as stores with 0 qualifying products:

SELECT s.*
FROM   store s
LEFT   JOIN (
   SELECT store_id, count(*) AS ct_prod
   FROM   product
   WHERE  created_at > now() - interval '1 month'
   GROUP  BY store_id
  ) p ON s.store_id = p.store_id
ORDER  BY p.ct_prod DESC NULL LAST, s.store_id;

NULLS LAST is essential to sort rows without any matches last.
I added s.store_id as arbitrary tie breaker to get a consistent sort order for stores with the same ct_prod.

Sort by frequency of rows in associated table

1 Answers1