Re: Calculating Percentiles, Quartiles, Median
Hi,
I am not sure about finding these values in an unordered
(yet to be sorted) table.
But You can create a new table and copy the sorted contents(selected values )...
Then JOIN that table with itself (using LEFT OUTER JOIN)
ON condition that ID(PRIMARY KEY) differ by one
Say a table Percentile contains
CREATE TABLE
(ID INTEGER NOT NULL AUTO_INCRMENT PRIMARY KEY
Value INTEGER);
Percentile
ID Value
1 1
2 5
3 9
4 6
5 7
6 7
7 4
8 3
9 5
10 2
11 9
12 9
Now create a table NewPercentile
mysql>DROP TABLE IF EXISTS NewPercentile;
CREATE TABLE NewPercentile
(ID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY)
SELECT Value FROM Percentile
ORDER BY Value;
Store no of records in NewPercentile in a variable, say @count
SELECT @count=COUNT(*) FROM NewPercentile;
SELECT X.ID,Y.ID,X.Value,Y.Value,(X.Value+Y.Value)/2
FROM
NewPercentile as X
LEFT OUTER JOIN
NewPercentile as Y
ON X.ID=Y.ID+1
WHERE
X.ID=(@count/4)+1
OR
X.ID=(@count/2)+1
OR
X.ID=(3*@count/4)+1;
+----+------+-------+-------+---------------------+
| ID | ID | Value | Value | (X.value+Y.value)/2 |
+----+------+-------+-------+---------------------+
| 4 | 3 | 4 | 3 | 3.50 |
| 7 | 6 | 6 | 5 | 5.50 |
| 10 | 9 | 9 | 7 | 8.00 |
+----+------+-------+-------+---------------------+
3 rows in set (0.01 sec)
mysql>
Hope this helps you ...
Regards,
Ram.
P.S:
This works regardless of whether no of rows is ODD/EVEN
We Learn the Most When we have to Invent
Subject
Written By
Posted
September 09, 2004 10:19AM
Re: Calculating Percentiles, Quartiles, Median
September 10, 2004 03:01AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.