Mysql – aggregate on join latest value

aggregatejoin;MySQL

This is a problem I often get stuck in and I hope there is a solution.

Let's build an example:

Table products: product_id, product_name
Table Prices: product_id, valid_since, Price

Lets say I would like to know the sum of all prices of all products (seems senseless, but it is just for illustrating the problem in an easy way)

Now you can imagine, prices will change with time, so the only price that matters is the price with the highest value of Prices.valid_since of course

SELECT SUM(Prices.price) 
FROM products 
INNER JOIN Prices
ON products.product_id=Prices.product_id

will result in something absolutely senseless, so how could it be possible?

Best Answer

As the way your schema current sits, you'll need two parts; the main select statement and (since you are using MySQL, which doesn't support common table expressions), a view in the where clause.

The view, priveView:

SELECT product_id
, MAX (valid_since) [validSince]
FROM prices
GROUP BY product_id

The select statement:

SELECT SUM (P.Prices)
FROM Prices AS P
INNER JOIN priceView as PV ON P.product_id = PV.Product_Id AND PV.valid_since = P.valid_since

If possible I would change the schema of your prices tables so that it also includes a column of when the price starts in additional when it ends/changes. Whatever the current price happens to be has it's "price_end" value set to null. You'd then be able to avoid the need for an intermediate view that gets the sub-section of rows that apply.

Related Solutions

Mysql – Slow performance of MySQL Join Query

Please provide SHOW CREATE TABLE; the explain is useless without it.

OR is a performance killer in many contexts.

( p.pricelist = "name_abc" AND p.iln = "sellerID_123" ) OR ( p.pricelist = "name_def" AND p.iln = "sellerID_456" ) OR ...

Turn that into

JOIN ( SELECT id FROM p WHERE 
( p.pricelist = "name_abc" AND p.iln = "sellerID_123" ) OR 
( p.pricelist = "name_def" AND p.iln = "sellerID_456" ) OR ... ) x ON x.id = foo.id

Also needed (on p):

INDEX(pricelist, iln, id)

(With the CREATEs, I could be more specific.)

The idea behind this "trick" is to move the costly work of the OR into a subquery that returns the necessary ids. Plus the INDEX makes it so that it can do all that work in the INDEX.

Mysql – Select all rows with multiple conditions in another table

You can get the result by using a combination of a WHERE, GROUP BY, and HAVING to return the products that have both the Book and Audio values.

select p.id, p.name
from products p
inner join properties t
  on p.id = t.product_id
where t.key = 'Format'
  and t.value in ('Book', 'Audio')
group by p.id, p.name
having count(distinct t.value) = 2;

See SQL Fiddle with Demo. Basically, you'll place the values that you want in the WHERE clause - this would be the Book and Audio. Then you'll GROUP BY the product.id and product.name these are unique to each product. Finally you will use the HAVING clause to count the distinct properties.value that are returned. This should equal the number of items that you have in your WHERE clause.

This could also be written using conditional aggregation inside of the HAVING:

select p.id, p.name
from products p
inner join properties t
  on p.id = t.product_id
group by p.id, p.name
having sum(case when t.key = 'Format' and t.value = 'Book' then 1 else 0 end) > 0
  and sum(case when t.key = 'Format' and t.value = 'Audio' then 1 else 0 end) > 0

See SQL Fiddle with Demo

If you want to include the Language key, then you can use conditional aggregation in the HAVING clause:

select p.id, p.name
from products p
inner join properties t
  on p.id = t.product_id
group by p.id, p.name
having 
  sum(case when t.key = 'Format' and t.value = 'Book' then 1 else 0 end) > 0
  and sum(case when t.key = 'Format' and t.value = 'Audio' then 1 else 0 end) > 0
  and sum(case when t.key = 'Language' and t.value = 'English' then 1 else 0 end) > 0

See Demo

Best Answer

Related Solutions

Mysql – Slow performance of MySQL Join Query

Mysql – Select all rows with multiple conditions in another table

Related Question