StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I am not sure about getting the percentiles. I am using a subquery based on <a href="https://stackoverflow.com/questions/3065855/select-nth-percentile-from-mysql?rq=1">select nth percentile from mysql</a> below, but I'm not quite sure I have modified it correctly. The point of my answer is in the combining of the subqueries. </p> <p>The following query would be slow and get exponentially slower as the table increases in size, but it should do what you're looking for:</p> <pre><code>SELECT p10.Date, Pct10, Pct90 FROM ( SELECT Date, count(Value) AS Pct10 FROM mydata GROUP BY Date, Value ORDER BY ABS(0.1-(count(Value)/(select count(*) from mydata))) LIMIT 1) AS p10 INNER JOIN ( SELECT Date, count(Value) AS Pct9 FROM mydata GROUP BY Date, Value ORDER BY ABS(0.9-(count(Value)/(select count(*) from mydata))) LIMIT 1) AS p90 ON p10.Date = p90.Date GROUP BY p1.Date </code></pre> <p>Here is my second idea. If it works, it would be faster and more efficient than the first one I listed, but still be pretty slow for larger tables. </p> <pre><code>SELECT p10.Date, count(Value) AS Pct10, Pct90 FROM mydata p10 INNER JOIN ( SELECT Date, count(Value) AS Pct90 FROM mydata GROUP BY Date, Value ORDER BY ABS(0.9-(count(Value)/(select count(*) from mydata))) LIMIT 1) AS p90 ON p10.Date = p90.Date GROUP BY Date, Value ORDER BY ABS(0.1-(count(Value)/(select count(*) from mydata))) LIMIT 1 </code></pre> <p><strong>EDIT</strong></p> <p>OK, brainstorming time. Given that this is a subquery for a percentile for one date (I am not even sure this is how it works):</p> <pre><code> SELECT Date, count(Value) AS Pct90 FROM mydata WHERE Date = ? GROUP BY Value ORDER BY ABS(0.9-(count(Value)/(select count(*) from mydata WHERE Date = ?))) LIMIT 1 </code></pre> <p>Then let's try to fix the ORDER BY:</p> <pre><code> SELECT Date, count(Value) as Pct90 FROM mydata INNER JOIN (SELECT Date, COUNT(*) AS DateTotal FROM mydata GROUP BY Date) AS d ON d.Date = mydata.Date GROUP BY Date, Value ORDER BY (ABS(0.9-(COUNT(Value)/d.DateTotal))) LIMIT 1 </code></pre> <p>If you use this pattern in my previous examples, maybe it will work.</p> <p><strong>EDIT 2</strong></p> <p>So, here we go again because we can't use LIMIT 1 (which I should have realized before). I actually tested the following on my own database (hopefully, I changed all the field and table names back how they should be!) and it seems to work. You'll have to do this again for p10 and combine the two.</p> <pre><code>--- removed due to typos --- </code></pre> <p><strong>EDIT 3</strong></p> <p>I found some errors in Edit 2, so I deleted it. Here is the whole percentage query. This query works on my database (using different fields and tables), as far as I can tell.</p> <pre><code>SELECT n.Date, n.Pct AS Pct10, n.Value AS Pct10Value, q.Pct AS Pct90, q.Value AS Pct90Value FROM ( SELECT p.Date, p.Pct, p.Value, m.Selector FROM ( SELECT mydata.Date, Value, COUNT(Value) as Pct, (ABS(0.1-(COUNT(Value)/d.DateTotal))) AS Abs10 FROM mydata INNER JOIN (SELECT Date, COUNT(*) AS DateTotal FROM mydata GROUP BY Date) AS d ON d.Date = mydata.Date GROUP BY Date, Value ) p INNER JOIN ( SELECT Date, MIN(Abs10) AS Selector FROM ( SELECT mydata.Date, Value, COUNT(Value) as Pct, (ABS(0.1-(COUNT(Value)/d.DateTotal))) AS Abs10 FROM mydata INNER JOIN (SELECT Date, COUNT(*) AS DateTotal FROM mydata GROUP BY Date) AS d ON d.Date = mydata.Date GROUP BY Date, Value ) x GROUP BY Date ) AS m ON m.Selector = p.Abs10 GROUP BY p.Date) n INNER JOIN ( SELECT p.Date, p.Pct, p.Value, m.Selector FROM ( SELECT mydata.Date, Value, COUNT(Value) as Pct, (ABS(0.9-(COUNT(Value)/d.DateTotal))) AS Abs90 FROM mydata INNER JOIN (SELECT Date, COUNT(*) AS DateTotal FROM mydata GROUP BY Date) AS d ON d.Date = mydata.Date GROUP BY Date, Value ) p INNER JOIN ( SELECT Date, MIN(Abs90) AS Selector FROM ( SELECT mydata.Date, Value, COUNT(Value) as Pct, (ABS(0.9-(COUNT(Value)/d.DateTotal))) AS Abs90 FROM mydata INNER JOIN (SELECT Date, COUNT(*) AS DateTotal FROM mydata GROUP BY Date) AS d ON d.Date = mydata.Date GROUP BY Date, Value ) x GROUP BY Date ) AS m ON m.Selector = p.Abs90 GROUP BY p.Date) q ON q.Date = n.Date </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload