NB! This question is related to Mysql 5.x and answers requiring v8.x does not help me here :-)
I've found a way to calculate percentiles in php from an array of data (around 150.000 record), but it takes up alot of memory which makes php time out sometimes. I would like this calculation to happen through mysql 5.7 instead.
my table has a bunch of records with a date and a score (1000 records a day), and I would like to be able to find the top 10% value based on the scores from ie. one month.
My php calculation looks like this:
private function getPercentile($percentile, $array, $key) {
if(!array_key_exists($key, $array)) {
return null;
}
$array = $array[$key];
sort($array);
$index = ($percentile/100) * count($array);
if (floor($index) == $index) {
$result = ($array[$index-1] + $array[$index])/2;
}
else {
$result = $array[floor($index)];
}
return $result;
}
Where I just take my mysql result array and put into the function after execution.
Table example:
| requestTime<timestamp> | score <double> |
|---|---|
| 2021-08-10 07:44:58 | 0.96 |
| 2021-08-11 07:44:52 | 0.73 |
| 2021-08-12 07:44:51 | 0.78 |
can anyone transition this into a mysql query, then I would be truly thankful!
