Calculation of a '95th percentile'

Hi everyone,

For our needs, we have to integrate on a Report MYSQL, a calculation of a ‘95th percentile’ average for a counter (in place of a ‘normal’ average).

In another words, we have to automatically substract the 5th ‘highest values’ of the total of the value, and so only make the average on the remaining 95th of the value.

And this treatment should appear as a function, that we could integrate on the Report.

Does this function already exist ?
Does anyone have any idea to help me to find how to do ?

Tks a lot to everybody.
Chevy

This is the solution to my problem, now match the ‘90th percentile’ in mysql and excel.
For all SitePoint members :wink: :


 
SELECT SUM(g1.r) sr,
g2.O2_PPM l,
SUM(g1.r)/(SELECT COUNT(*) FROM cm714.listfiles) p
FROM (SELECT COUNT(*) r, O2_PPM
FROM cm714.listfiles GROUP BY O2_PPM) g1
JOIN (SELECT COUNT(*) r, O2_PPM FROM cm714.listfiles 
GROUP BY O2_PPM) g2
ON g1.O2_PPM <= g2.O2_PPM
GROUP BY g2.O2_PPM
HAVING p >= 0.9
ORDER BY p
LIMIT 1

Hi everyone,

I try this query for calculation of a ‘90th percentile’:
Credits:

SELECT SUM(g1.r) sr,        
g2.O2_PPM l,        
SUM(g1.r)/(SELECT COUNT(*) FROM listfiles) p
FROM (SELECT COUNT(*) r, O2_PPM 
FROM listfiles GROUP BY O2_PPM) g1
JOIN (SELECT COUNT(*) r, O2_PPM FROM listfiles GROUP BY O2_PPM) g2
ON  g1.O2_PPM < g2.O2_PPM
GROUP BY g2.O2_PPM
HAVING p > 0.9
ORDER BY p
LIMIT 1

And the ouptus is:


sr	l	p
46	27787	0,92

I need calculating the 90th percentile in my table mysql for this columns:
O2_PPM, N2_PPM, CO_PPM, CO2_PPM, H2_PPM, CH4_PPM, C2H6_PPM, C2H4_PPM, C2H2_PPM, C2H2_C2H4_PPM, CH4_H2_PPM, C2H4_C2H6_PPM, H20_PPM

I need 13 differents queries for calculating the ‘90th percentile’ ?

And I have another problem:
If I use Microsoft Excel for calculation of a ‘90th percentile’ the output for the column O2_PPM is 22559,5… in mysql 27787, what’s the difference?

This is th table MySQL:


# MySQL-Front 5.0  (Build 1.183)

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE */;
/*!40101 SET SQL_MODE='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES */;
/*!40103 SET SQL_NOTES='ON' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS */;
/*!40014 SET FOREIGN_KEY_CHECKS=0 */;


# Host: localhost    Database: cm714
# ------------------------------------------------------
# Server version 5.1.44-community

USE `cm714`;

#
# Table Objects for table listfiles
#

DROP TABLE IF EXISTS `listfiles`;
CREATE TABLE `listfiles` (
  `ID` int(10) NOT NULL AUTO_INCREMENT,
  `O2_PPM` decimal(10,2) DEFAULT NULL,
  `N2_PPM` decimal(10,2) DEFAULT NULL,
  `CO_PPM` decimal(10,2) DEFAULT NULL,
  `CO2_PPM` decimal(10,2) DEFAULT NULL,
  `H2_PPM` decimal(10,2) DEFAULT NULL,
  `CH4_PPM` decimal(10,2) DEFAULT NULL,
  `C2H6_PPM` decimal(10,2) DEFAULT NULL,
  `C2H4_PPM` decimal(10,2) DEFAULT NULL,
  `C2H2_PPM` decimal(10,2) DEFAULT NULL,
  `C2H2_C2H4_PPM` decimal(10,2) DEFAULT NULL,
  `CH4_H2_PPM` decimal(10,2) DEFAULT NULL,
  `C2H4_C2H6_PPM` decimal(10,2) DEFAULT NULL,
  `H20_PPM` decimal(10,2) DEFAULT NULL,
  `sSTATE` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=5912 DEFAULT CHARSET=latin1;

#
# Dumping data for table listfiles
#
LOCK TABLES `listfiles` WRITE;
/*!40000 ALTER TABLE `listfiles` DISABLE KEYS */;

INSERT INTO `listfiles` VALUES (1,11477,65991,185,2232,9,5,2,3,1,0,1,1,8,'E');
INSERT INTO `listfiles` VALUES (2,2253,73744,345,2399,14,13,5,6,1,0,1,1,8,'E');
INSERT INTO `listfiles` VALUES (3,3749,71690,602,3844,28,9,2,6,1,0,0,3,10,'E');
INSERT INTO `listfiles` VALUES (4,7418,63711,404,7686,42,32,11,45,13,0,1,4,14,'E');
INSERT INTO `listfiles` VALUES (5,6167,61773,366,6332,80,5,7,0,2,0,0,0,43,'E');
INSERT INTO `listfiles` VALUES (6,22400,65764,278,2613,27,4,2,2,0,0,0,1,23,'E');
INSERT INTO `listfiles` VALUES (7,13338,58453,310,2963,15,14,10,6,0,0,1,1,10,'E');
INSERT INTO `listfiles` VALUES (8,224,56688,204,1813,86,13,18,15,24,2,0,1,8,'E');
INSERT INTO `listfiles` VALUES (9,1477,63212,705,4170,132,46,17,42,76,2,0,2,8,'E');
INSERT INTO `listfiles` VALUES (10,27889,64781,49,371,6,2,3,0,6,0,0,0,0,'E');
INSERT INTO `listfiles` VALUES (11,21912,61208,99,460,10,5,15,4,0,0,0,0,0,'E');
INSERT INTO `listfiles` VALUES (12,365,58779,351,3656,61,47,51,6,0,0,1,0,25,'E');
INSERT INTO `listfiles` VALUES (13,5625,71446,1047,9944,30,15,7,22,32,1,0,3,23,'E');
INSERT INTO `listfiles` VALUES (14,23995,67130,167,2714,19,5,3,6,32,5,0,2,4,'E');
INSERT INTO `listfiles` VALUES (15,14503,66305,287,7298,62,12,26,11,0,0,0,0,14,'E');
INSERT INTO `listfiles` VALUES (16,15688,72239,449,4922,63,21,6,49,310,6,0,8,6,'E');
INSERT INTO `listfiles` VALUES (17,571,65256,703,4403,253,48,23,49,179,4,0,2,16,'E');
INSERT INTO `listfiles` VALUES (18,446,61688,691,3879,31,16,5,8,1,0,1,2,10,'E');
INSERT INTO `listfiles` VALUES (19,31998,67458,34,774,4,2,1,1,1,1,0,1,9,'E');
INSERT INTO `listfiles` VALUES (20,30852,66030,44,846,4,2,1,1,1,1,0,1,8,'E');
INSERT INTO `listfiles` VALUES (21,17365,50478,15,453,1,1,1,1,1,1,1,1,0,'E');
INSERT INTO `listfiles` VALUES (22,27787,71413,187,1526,4,2,1,1,1,1,0,1,5,'E');
INSERT INTO `listfiles` VALUES (23,17099,73367,271,1746,19,2,5,1,1,1,0,0,13,'E');
INSERT INTO `listfiles` VALUES (24,418,63638,668,3858,115,53,27,45,35,1,0,2,23,'E');
INSERT INTO `listfiles` VALUES (25,5581,66242,511,3504,90,7,3,13,39,3,0,4,13,'E');
INSERT INTO `listfiles` VALUES (26,432,65477,491,2781,38,11,4,6,2,0,0,1,16,'E');
INSERT INTO `listfiles` VALUES (27,5799,68110,438,3715,20,10,7,7,0,0,0,1,16,'E');
INSERT INTO `listfiles` VALUES (28,7472,61134,428,5331,112,2,3,1,16,16,0,0,26,'E');
INSERT INTO `listfiles` VALUES (29,16642,52221,108,1913,1319,62,4,62,630,10,0,15,9,'E');
INSERT INTO `listfiles` VALUES (30,367,51895,159,1868,1147,57,8,54,490,9,0,7,14,'E');
INSERT INTO `listfiles` VALUES (31,298,59302,498,3424,251,44,26,23,79,3,0,1,16,'E');
INSERT INTO `listfiles` VALUES (32,949,62657,511,4172,754,88,67,92,648,7,0,1,14,'E');
INSERT INTO `listfiles` VALUES (33,168,61496,545,3010,203,39,37,16,28,2,0,0,9,'E');
INSERT INTO `listfiles` VALUES (34,192,65178,526,3419,196,75,83,38,116,3,0,0,14,'E');
INSERT INTO `listfiles` VALUES (35,195,66234,469,2750,76,107,156,15,1,0,1,0,10,'E');
INSERT INTO `listfiles` VALUES (36,892,65824,767,4051,225,2,4,4,0,0,0,1,11,'E');
INSERT INTO `listfiles` VALUES (37,302,65117,416,2622,224,33,14,17,37,2,0,1,16,'E');
INSERT INTO `listfiles` VALUES (38,5155,67798,718,4460,399,29,6,50,464,9,0,8,21,'E');
INSERT INTO `listfiles` VALUES (39,9217,77003,561,3299,57,9,3,7,3,0,0,2,9,'E');
INSERT INTO `listfiles` VALUES (40,11369,61575,366,2373,67,6,3,7,46,7,0,2,4,'E');
INSERT INTO `listfiles` VALUES (41,323,59140,340,1829,87,37,26,16,43,3,0,1,11,'E');
INSERT INTO `listfiles` VALUES (42,7944,64633,712,5302,22,1,7,3,1,0,0,0,9,'E');
INSERT INTO `listfiles` VALUES (43,14256,71237,400,3434,26,5,2,4,0,0,0,2,44,'E');
INSERT INTO `listfiles` VALUES (44,4322,60646,387,1896,61,10,13,3,0,0,0,0,5,'E');
INSERT INTO `listfiles` VALUES (45,8868,77831,578,2460,16,15,3,4,0,0,1,1,6,'E');
INSERT INTO `listfiles` VALUES (46,1856,66992,716,3368,17,21,2,5,0,0,1,2,8,'E');
INSERT INTO `listfiles` VALUES (47,5026,56920,488,4617,349,27,8,28,196,7,0,3,17,'E');
INSERT INTO `listfiles` VALUES (48,2334,56587,203,1171,146,22,17,26,141,5,0,2,5,'E');
INSERT INTO `listfiles` VALUES (49,1157,57012,566,2873,314,44,21,28,154,5,0,1,13,'E');
INSERT INTO `listfiles` VALUES (50,508,61658,578,2548,207,96,89,51,80,2,0,1,25,'E');
/*!40000 ALTER TABLE `listfiles` ENABLE KEYS */;
UNLOCK TABLES;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;