HELP.....Percentiles in mysql.....

[clearstatcache]

just wanna ask if there are functions in mysql to get the percentiles of certain  set of data....just like how avg, sum works....

if none..any idea how can i get the percentiles of a set of data ....

any help and suggestions are very much appreciated....tnx...

[Barand]

I was never hot at stats, but this will give each value and the percentage of records with values less than or equal to it. Is that what you mean? Avoid large data sets - this completely tied up my server for 7 mins for 10,917  records.

 

SELECT DISTINCT s.salesvalue,
(SELECT COUNT(*)/109.17 FROM sales b  WHERE b.salesvalue <= s.salesvalue) as pcent
FROM `sales` s
ORDER BY salesvalue

[clearstatcache]

I am trying to calculate 25th percentile, 50th percentile (median), and 75th percentile from a list of unordered data in MySQL.

 

[Barand]

You could loop through the results of that query and pull those with pcent values closest to 25, 50 and 75

[Barand]

This does the job in less than 1 second instead of the 7 mins taken by the previous query. Substitute your value column and table names

<?php

$sql = "SELECT COUNT(*) FROM sales";
$res = mysql_query($sql);
$total = mysql_result($res,0);

$sql = "SELECT salesvalue, COUNT(*) as cnt FROM sales
        GROUP BY salesvalue
        ORDER BY salesvalue";
$res = mysql_query($sql);
$cum = 0;
$pctiles =  array ();
while (list($v, $c) = mysql_fetch_row($res))
{
    $cum += $c;
    $pc = round($cum * 100 / $total);
    if ($pc % 25 == 0 && !isset($pctiles[$pc])) $pctiles[$pc] = $v;
}

echo '<pre>', print_r($pctiles, true), '</pre>';
?>

[fenway]

You could accomplish the same task by calculating the "count" part and storing the results in a temporary table, then issuing secondary queries approriately.

[clearstatcache]

how would i do dat...please help...i need some guidance...

assuming i have a table like this..

 

+--------------+-------+-------------+

| Percentile_Key| Value  | Type        |

+----------------+-------+-------------+

|                  1 |      1 | Arrived    |

|                  2 |      2 | Arrived    |

|                  3 |      3 | Arrived    |

|                  4 |      3 | Arrived    |

|                  5 |      4 | Arrived    |

|                  6 |      5 | Arrived    |

|                  7 |      6 | Arrived    |

|                  8 |      7 | Arrived    |

|                  9 |      8 | Arrived    |

|                10 |      9 | Arrived    |

|                11 |    10 | Arrived    |

|                12 |    11 | Transferred |

|                13 |    12 | Transferred |

|                14 |    13 | Transferred |

|                15 |    14 | Transferred |

|                16 |    15 | Transferred |

|                17 |    16 | Transferred |

|                18 |    17 | Transferred |

|                19 |    18 | Transferred |

|                20 |    19 | Transferred |

|                21 |    20 | Transferred |

+--------------+-------+-----------+

 

i have a query :

 

select

    sum(Value),

    avg(Value)

from percentile

group by Type

order by Value

 

to get the sum and avg of the values by type...now i also want to get the 5th, 10,75th percentile of the values by type...

any idea how would i do that....

any help or idea is very much appreciated....

[fenway]

Take a look at this example.