3

My question is very similar to this but not the same. The table is the same: rows with ID, volume, and price. In short, I need an SQL query that will give me the rows with the lowest prices whose sum of volumes is less or equal to 10% of total volume. I need the average price of those rows. In even shorter terms, average price for the 10% cheapest volume. For a better explanation, I've written an algorithm in pseudocode on how I would have done it in C++:

  1. Sort table by price (lowest first).
  2. Calculate total volume and store 10% of that value in a variable named 'threshold'.
  3. Declare a variable 'sum' with value 0 and start a loop that will iterate through the aforementioned sorted table.
  4. Check if 'sum' is less than 'threshold'. If true, add the row's volume value to 'sum', and add the examined row to a list. Increase iterator by 1.
  5. Repeat step 4 until condition is false. When false, it should break the loop and return the list.
  6. Make an arithmetic average of the returned row's prices.

I've written the entire thing with three explanations as to make sure people understand and hopefully provide their own insight on how to solve this in SQL. There might be a single function, or a very simple query that can solve this entire thing, so I gave a detailed context to the problem.

3

If you are using a older MySQL versions like 5.1 - 5.7 , you need to use MySQL user variables as these MySQL versions does not support window functions and or common table expressions ..

I ported the answers of @Akina and @mustaccio and the fiddle of Akina to a MySQL 5.1+ working query.

Warning as MySQL 5.1 to MySQL 5.7 does not use window functions and or common table expressions you have to write tricky MySQL code like this.
Note if you are using MySQL 8 use one off those answers.

Query

SELECT 
 AVG(price)
FROM (

  SELECT 
     t.price 
   , (@RUNNING_SUM := @RUNNING_SUM + t.volume) AS running_sum
  FROM 
   t
  CROSS JOIN (SELECT @RUNNING_SUM := 0) AS init_user_param
  ORDER BY 
   t.price ASC  
) AS alias
WHERE
 alias.running_sum <= (0.1 * (
                         SELECT 
                            MAX(running_sum)
                         FROM (                      
                           SELECT 
                                t.price 
                             , (@RUNNING_SUM_1 := @RUNNING_SUM_1 + t.volume) AS running_sum
                            FROM 
                           t
                           CROSS JOIN (SELECT @RUNNING_SUM_1 := 0) AS init_user_param
                         ORDER BY 
                         t.price ASC
                      ) AS a
                ))

see demo

  • Thank you very much, I'm extremely grateful for the help. It works except in one case where I get NULL. That one case is when the row with the lowest price consists more than 10% of the total volume. In that case, it should just return (the price of) that row. Have any ideas on how to go about doing that? – Branimir Oct 22 at 19:50
  • "It works except in one case where I get NULL. That one case is when the row with the lowest price consists more than 10% of the total volume. In that case, it should just return (the price of) that row." i need to see a example http:www.db-fiddle.com as it is not clear what you mean as i have just ported to this query @Branimir .. The only reasons which i can think off which can have NULL is when you use this on a small dataset like 1 / 2 or 3 records or and on values with low volume numbers.. – Raymond Nijland Oct 23 at 10:25
  • @Branimir So test SELECT ( CASE WHEN AVG(price) IS NULL THEN (SELECT MAX(price) FROM t) ELSE AVG(price) END ) AS "AVG(price)" as patch to replace SELECT AVG(price) ... Not ideal but one smaller datasets it should not be a problem performance wise .. – Raymond Nijland Oct 23 at 10:28
2

the rows with the lowest prices whose sum of volumes is less or equal to 10% of total volume. I need the average price of those rows

Test:

WITH cte AS ( SELECT price, SUM(volume) OVER (ORDER BY price ASC) summ 
              FROM sourcetable )
SELECT AVG(price)
FROM cte
WHERE summ <= 0.1 * ( SELECT MAX(summ)
                      FROM cte )

fiddle

2

Depending on the size of the table, this variant of Akina's solution might perform a little better by calculating both running total and full total of volume in one go:

WITH cte AS ( 
  SELECT 
    price, 
    SUM(volume) OVER (ORDER BY price ASC) running, 
    SUM(volume) OVER (ORDER BY price ASC 
                      RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) total, 
  FROM sourcetable )
SELECT AVG(price)
FROM cte
WHERE running <= 0.1 * total

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.