SQL (postgres) to join over a variable number of subselects

Question

I have 3 tables:

• shop {'ID','NAME'}
• product {'ID','NAME'}
• orders {'SHOP_ID','PRODUCT_ID','QTY'}

The orders table is referencing to product.id for sold products at shop with shop.id.

I would like to create a an sql view that selects top 10 products per shop. Thus I wish result table to have 10 rows (one per rank) and as many columns as there exist shop ids in the shop table, with the to 10 sellers for the shop in each column.

Getting the top ten products for each shop is easy. Its also easy to get this for a fixed number of n shops by repeating and joining the selects to get this from a single shop, but I don't know how to do this for a variable number of shops. I searched for similar examples and my feeling is, that this should be possible with a common table expression but I don't get it.

So the question is:

How to join a variable number of sub selects in a loop or similar construct

Example how the result should look like:

'RANK' 'Berlin' 'Hamburg' 'München'
  1        2        3         4
  2        .        .         .
  .        .        .         .
  .        .        .         .
  9        .        .         .
 10        .        .         x 

Where the numbers are product IDs of the top 10 sellers. I.e. the columns are created like xx.product_id as 'Berlin'.

Answer 1

The following produces the transpose of what you are looking for:

    select shop_id, 
    max(case when r = 1 then product_id else 0 end) as p_1,
    max(case when r = 2 then product_id else 0 end) as p_2,
    max(case when r = 3 then product_id else 0 end) as p_3,
    max(case when r = 4 then product_id else 0 end) as p_4,
    max(case when r = 5 then product_id else 0 end) as p_5,
    max(case when r = 6 then product_id else 0 end) as p_6,
    max(case when r = 7 then product_id else 0 end) as p_7,
    max(case when r = 8 then product_id else 0 end) as p_8,
    max(case when r = 9 then product_id else 0 end) as p_9,
    max(case when r = 10 then product_id else 0 end) as p_10
    from
    (
        select shop_id, product_id, sum(qty) as sales, 
        row_number() over (partition by shop_id order by sum(qty) desc) as r
        from orders
        group by shop_id, product_id
    )group by shop_id

To transpose this you can use crosstab, but this requires you to know the number of shops in advance. Databases aren't generally designed to have tables with an unknown number of columns. A possible approach would be to create a function similar to here.

Answer 2

I think you just want row_number() with an aggregation:

select s.*, o.product_id
from shops s join
     (select shop_id, product_id, sum(qty) as qty,
             row_number() over (partition by shop_id order by sum(qty) desc) as rnk
      from orders o
      group by shop_id, product_id
     ) o
     on s.shop_id = o.shop_id
where rnk <= 10;

If you want more information for the products (more than the product id), then you can join in the products table.

Answer 3

You can take a look in crosstab function to create pivot tables. AFAIK it isn't possible to create dynamic columns, but give a try.