PHP/MySQL Average based on contents of Array

Question

I have the following array:

array(
    a => 6.75,
    b => 5.45,
    c => 8.76,
    d => 7.76,
    e => 8.16,
    f => 2.76,
    g => 4.67,
    h => 9.01,
    i => 5.42,
)

What I am looking to achieve is to average the top quartile.

In essence I need to order the array in descending order, then calculate what the top quartile will be (i.e. 9 (the numbers of elements in the array) x 25% = 2.25 rounded down to 2, so it would be the first two numbers that need to be averaged).

It's this figure I'm looking for.

I'm struggling to figure out how to achieve this and whether it's best to do this within PHP or via a query to the MySQL database.

Any and all advice, feedback and suggestions welcomed.

rpbouman.blogspot.co.uk/2008/07/… ?

Strawberry
– Strawberry

2014-07-03 10:53:19 +00:00
Commented Jul 3, 2014 at 10:53 — Strawberry
– Strawberry, Commented Jul 3, 2014 at 10:53

Niet the Dark Absol · Accepted Answer · 2014-07-03 10:38:30Z

2

It's going to be tough to do this in MySQL, because you can't have a variable LIMIT directly.

However, it would certainly be most efficient to do it there, rather than transferring all of the data to PHP when you only need a bit of it.

So, with that in mind, try this algorithm:

SELECT COUNT(*) from your table to get the number of rows
Divide that number of rows by 4, call that $quartile_count

Now you need a nested query:

SELECT AVG(`column_name`)
FROM (
    SELECT `column_name`
    FROM `table_name`
    ORDER BY `column_name` DESC
    LIMIT $quartile_count
) `tmp`

This is because LIMIT must be applied first, before the aggregate AVG function.

answered Jul 3, 2014 at 10:38

Niet the Dark Absol

326k86 gold badges480 silver badges604 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Homer_J Over a year ago

Thanks for this, will test and come back shortly.

Erxyon · Accepted Answer · 2014-07-03 10:44:49Z

1

You can do this easily in PHP :

$array = array(
a => 6.75,
b => 5.45,
c => 8.76,
d => 7.76,
e => 8.16,
f => 2.76,
g => 4.67,
h => 9.01,
i => 5.42,
);

arsort($array); // Order the array in descending order
$top_quartile = floor(count($array)/4); // Calculate top quartile
$array = array_slice($array, 0, $top_quartile); // Keep only the top quartile values

$total = 0;
foreach($array as $value) {
    $total += $value;
}
$average = $total/$top_quartile;

echo $average;

answered Jul 3, 2014 at 10:44

Erxyon

2512 silver badges4 bronze badges

1 Comment

Homer_J Over a year ago

Appreciate the feedback, thanks - will review and come back to you.

Stalinko · Accepted Answer · 2014-07-03 10:37:50Z

1

$a = array(
    'a' => 6.75,
    'b' => 5.45,
    'c' => 8.76,
    'd' => 7.76,
    'e' => 8.16,
    'f' => 2.76,
    'g' => 4.67,
    'h' => 9.01,
    'i' => 5.42,
);
rsort($a);
$sum = 0;
for($i = 0; $i < count($a) / 4; ++$i){
    $sum += $a[$i];
}
$result = $sum / $i;
echo $result;

answered Jul 3, 2014 at 10:37

Stalinko

3,69434 silver badges38 bronze badges

1 Comment

Homer_J Over a year ago

Cheers, will review and test and post a reply shortly.

Kickstart · Accepted Answer · 2014-07-03 12:03:48Z

1

Done in a single piece of SQL:-

SELECT AVG(some_column)
FROM
(
    SELECT some_column, @seq:=@seq+1 AS seq
    FROM
    (
        SELECT some_column
        FROM some_values
        ORDER BY some_column DESC
    ) sub0
    CROSS JOIN (SELECT @seq:=0) sub1
) sub2
CROSS JOIN
(
    SELECT COUNT(*) AS all_count
    FROM some_values
) sub3
WHERE sub3.all_count / 4 > sub2.seq

This has one sub query to get the rows in descending order, then adds a sequence number to it, then cross joins the results of that with a sub query that gets the total number of records, has a WHERE clause to eliminate all but the top quarter and then uses AVG() to get the average.

More done out of interest than as a practical solution as I suspect it will perform poorly. Only really useful if you require it as a datasource that must be a single query.

answered Jul 3, 2014 at 12:03

Kickstart

21.5k2 gold badges26 silver badges33 bronze badges

1 Comment

Homer_J Over a year ago

Thanks for that @Kickstart - again, I'll try this out and see what is working best from a performance aspect. Great that it is all in SQL but as you say, not sure how it will perform. Really appreciated.

Pratik Gadoya · Accepted Answer · 2014-07-03 11:10:29Z

0

This is the complete code of your problem.

$rs = mysql_query("select count(column name) as total from table");
$result = mysql_fetch_row($rs);
$avg = floor($result[0]/4);

$rs = mysql_query("select column name from table order by column name desc limit 0,".$avg);
$total = 0;
while($result = mysql_fetch_array($rs))
{
     $total += $result['column name'];
}
echo $total/mysql_num_rows($rs);

answered Jul 3, 2014 at 11:10

Pratik Gadoya

1,4771 gold badge17 silver badges28 bronze badges

Collectives™ on Stack Overflow

PHP/MySQL Average based on contents of Array

5 Answers 5

1 Comment

1 Comment

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

1 Comment

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related