postgresql top value multiple join

1
votes

Lets say i have this dummy table

CREATE TABLE dim_product (
    product_id int4 null primary key,
    product_name varchar(100) NULL
);
CREATE TABLE dim_territory (
    territory_id int4 null primary key,
    region varchar(30) NULL
);    
CREATE TABLE fact_sales (
    order_detail_id varchar(30) null primary key,
    product_id int4 NULL,
    territory_id int4 NULL,
    quantity int4 NULL,
    foreign key(product_id) references dim_product(product_id),
    foreign key(territory_id) references dim_territory(territory_id)
);

insert into dim_product 
(product_id, product_name)
values
(1, 'patch kit'), 
(2, 'mountain bike'), 
(3, 'logo');        
insert into dim_territory 
(territory_id, region)
values
(1, 'AUS'),
(2, 'FRN'),
(3, 'GRMN');    
insert into fact_sales
(order_detail_id, product_id, territory_id, quantity)
values
('z1', 1, 1, 1),
('z2', 1, 1, 3),
('z3', 2, 1, 4),
('z4', 3, 2, 4),
('z5', 3, 2, 2),
('y1', 2, 1, 1),
('y2', 1, 3, 4),
('y3', 2, 3, 5),
('x4', 3, 3, 1),
('x5', 3, 2, 4);

I want to know top 2 product in each region based on quantity

I've tried with this code, but it make me confused lol :D

select DT.region, DP.product_name, sum(FS.quantity) as quantity
from fact_sales FS 
     join dim_territory DT
     on FS.territory_id=DT.territory_id
     join dim_product DP
     on FS.territory_id=DT.territory_id
group by product_name, region
order by quantity desc

the outcome of that code is like this, this is so wrong

region product_name quantity
GRMN patch kit 10
FRN logo 10
GRMN logo 10
FRN mountain bike 10
GRMN mountain bike 10
FRN patch kit 10
AUS mountain bike 9
AUS patch kit 9
AUS logo 9

how to I fix that

2
postgresqljoin

2 Answers

1
votes

You have an error on the second join with the variables you are using.

Try this:

    select DT.region, DP.product_name, sum(FS.quantity) as quantity
from fact_sales FS 
     join dim_territory DT
     on FS.territory_id=DT.territory_id
     join dim_product DP
     on FS.product_id=DP.product_id
group by product_name, region
order by quantity desc
1
votes

Take a look at window functions, specially at rank(), dense_rank() and row_number(), e.g.

SELECT *, ROW_NUMBER() OVER w AS rank
FROM (
  SELECT DT.region, DP.product_name, sum(FS.quantity) AS quantity
  FROM fact_sales FS 
  JOIN dim_territory DT ON FS.territory_id=DT.territory_id
  JOIN dim_product DP ON FS.product_id=DP.product_id
  GROUP BY product_name, region
) j 
WINDOW w AS (PARTITION BY region ORDER BY quantity
             RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)