Mysql – Average price for top 10% cheapest volume (algorithm help)

MySQLselect

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++:

Sort table by price (lowest first).
Calculate total volume and store 10% of that value in a variable named 'threshold'.
Declare a variable 'sum' with value 0 and start a loop that will iterate through the aforementioned sorted table.
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.
Repeat step 4 until condition is false. When false, it should break the loop and return the list.
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.

Best Answer

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

Related Solutions

Mysql – SELECT TOP in MySQL

There is no TOP n PERCENT syntax in MySQL.

You will have to emulate it as follows

First here is a sample table

mysql> use test
Database changed
mysql> drop table if exists mytable;
Query OK, 0 rows affected (0.06 sec)

mysql> create table mytable (id int not null auto_increment primary key);
Query OK, 0 rows affected (0.06 sec)

mysql> insert into mytable values (),(),(),(),(),(),(),(),(),();
Query OK, 10 rows affected (0.07 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> select * from mytable;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
| 10 |
+----+
10 rows in set (0.01 sec)

mysql>

You emulate it with this code:

set @percent = 50;
select floor(count(1) * @percent / 100.0) into @pct from mytable;
set @sqlstmt = concat('select * from mytable limit ',@pct);
prepare stmt from @sqlstmt;
execute stmt;
deallocate prepare stmt;

Here is that code executed:

mysql> set @percent = 50;
Query OK, 0 rows affected (0.00 sec)

mysql> select floor(count(1) * @percent / 100.0) into @pct from mytable;
Query OK, 1 row affected (0.00 sec)

mysql> set @sqlstmt = concat('select * from mytable limit ',@pct);
Query OK, 0 rows affected (0.00 sec)

mysql> prepare stmt from @sqlstmt;
Query OK, 0 rows affected (0.01 sec)
Statement prepared

mysql> execute stmt;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+
5 rows in set (0.00 sec)

mysql> deallocate prepare stmt;
Query OK, 0 rows affected (0.00 sec)

mysql>

Give it a Try !!!

Mysql – How to calculate average of list of qualifications of each pupil MYSQL

Your current subquery is returning the avg(qualification) for all pupils but it is not relating each row back to a single pupil. You need to make this a correlated subquery which will query your qualifications while using the pupils from your outer query. I would suggest rewriting the subquery query the following way:

(select avg(q.qualification) 
from qualifications q
where type_qualification='exam'
  and p.id_pupil = q.id_pupil
  and q.id_trimester=1 
group by q.id_pupil) as exam_average

You will notice that this subquery doesn't have the JOIN written inside of it but it is referencing your pupils table from the main ares of your query. The full query would be written:

SELECT 
  p.id_pupil,
  p.name,
  p.surname,
  IFNULL(qua.media, 0) media,
  IFNULL(qua.count, 0) number_class_qualifications,
  (select avg(q.qualification) 
   from qualifications q
   where type_qualification='exam'
     and p.id_pupil = q.id_pupil
     and q.id_trimester=1 
   group by q.id_pupil) as exam_average,
  sum(case when i.type_incident='miss' and i.id_trimester=1 then 1 else 0 end) as misses,
  sum(case when i.type_incident='delay' and i.id_trimester=1 then 1 else 0 end) as delays,
  sum(case when i.type_incident='attitude' and i.id_trimester=1 then 1 else 0 end) as attitude,
  sum(case when i.type_incident='miss_justif' and i.id_trimester=1 then 1 else 0 end) as misses_justificadas 
FROM pupils p
LEFT JOIN incidents i
  ON i.id_pupil=p.id_pupil
LEFT JOIN
(
  select id_pupil, id_trimester, 
   count(qualification) count, 
     round(avg(qualifications.qualification),2) media
  from qualifications 
  where type_qualification='class'
  group by id_pupil, id_trimester 
) as qua
  ON qua.id_pupil = p.id_pupil
WHERE p.level=1
  and p.class='A' 
  and qua.id_trimester=1 
GROUP BY p.id_pupil, p.name, p.surname, qua.media, qua.count;

See SQL Fiddle with Demo

This could also be written without using the correlated subquery and you would JOIN on the subquery similar to:

SELECT 
  p.id_pupil,
  p.name,
  p.surname,
  IFNULL(qua.media, 0) media,
  IFNULL(qua.count, 0) number_class_qualifications,
  IFNULL(q.exam_average, 0) exam_average,
  sum(case when i.type_incident='miss' and i.id_trimester=1 then 1 else 0 end) as misses,
  sum(case when i.type_incident='delay' and i.id_trimester=1 then 1 else 0 end) as delays,
  sum(case when i.type_incident='attitude' and i.id_trimester=1 then 1 else 0 end) as attitude,
  sum(case when i.type_incident='miss_justif' and i.id_trimester=1 then 1 else 0 end) as misses_justificadas 
FROM pupils p
LEFT JOIN incidents i
  ON i.id_pupil=p.id_pupil
LEFT JOIN
(
  select id_pupil, id_trimester, 
   count(qualification) count, 
     round(avg(qualifications.qualification),2) media
  from qualifications 
  where type_qualification='class'
  group by id_pupil, id_trimester 
) as qua
  ON qua.id_pupil = p.id_pupil
LEFT JOIN
(
  select avg(q.qualification) as exam_average, q.id_pupil
  from qualifications q
  where q.type_qualification='exam'
    and q.id_trimester=1 
  group by q.id_pupil
) q
  on p.id_pupil = q.id_pupil
WHERE p.level=1
  and p.class='A' 
  and qua.id_trimester=1 
GROUP BY p.id_pupil, p.name, p.surname, qua.media, qua.count, q.exam_average;

See SQL Fiddle with Demo. Both will generate the result:

| ID_PUPIL |         NAME |          SURNAME | MEDIA | NUMBER_CLASS_QUALIFICATIONS |   EXAM_AVERAGE | MISSES | DELAYS | ATTITUDE | MISSES_JUSTIFICADAS |
|----------|--------------|------------------|-------|-----------------------------|----------------|--------|--------|----------|---------------------|
|      140 |        María |     Adámez Nieto |     4 |                           2 | 4.733333269755 |      2 |      1 |        1 |                   2 |
|      141 |       Daniel | Alonso Fernández |     6 |                           3 | 8.400000095367 |      0 |      0 |        0 |                   0 |
|      142 |        Rocío |   Anos Población |     4 |                           1 | 6.366666634878 |      1 |      1 |        0 |                   1 |
|      143 |       Teresa |  Arribas Miranda |   3.5 |                           2 | 6.766666650772 |      0 |      0 |        0 |                   0 |
|      144 | Isabel María |    Barroso Suero |   5.5 |                           2 |              4 |      1 |      0 |        0 |                   1 |

Best Answer

Related Solutions

Mysql – SELECT TOP in MySQL

Mysql – How to calculate average of list of qualifications of each pupil MYSQL

Related Question