Re: Calculation of a '95th percentile'

[Rick James]

mysql> CREATE TEMPORARY TABLE PCT_O2_PPM (seq INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY) SELECT O2_PPM FROM listfiles ORDER BY O2_PPM;
Query OK, 50 rows affected (0.36 sec)
Records: 50  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM PCT_O2_PPM ORDER BY seq DESC LIMIT 7;
+-----+----------+
| seq | O2_PPM   |
+-----+----------+
|  50 | 31998.00 |
|  49 | 30852.00 |
|  48 | 27889.00 |
|  47 | 27787.00 |  <--- this feels wrong
|  46 | 23995.00 |  <--- maybe here
|  45 | 22400.00 |  <--- maybe here
|  44 | 21912.00 |
+-----+----------+
7 rows in set (0.00 sec)

mysql> SELECT @ct := COUNT(*) FROM PCT_O2_PPM;
+-----------------+
| @ct := COUNT(*) |
+-----------------+
|              50 |
+-----------------+
1 row in set (0.06 sec)

mysql> SELECT O2_PPM FROM PCT_O2_PPM WHERE seq >= 0.90 * @ct ORDER BY seq LIMIT
1;
+----------+
| O2_PPM   |
+----------+
| 22400.00 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT O2_PPM FROM PCT_O2_PPM WHERE seq > 0.90 * @ct ORDER BY seq LIMIT 1
;
+----------+
| O2_PPM   |
+----------+
| 23995.00 |
+----------+
1 row in set (0.00 sec)

Yeah, it would take 13 temp tables, etc.

No, this code does not do arithmetic to figure out where between those two the proper 90%ile should be.