Cut 13% of the top and bottom of MySQL Query?

Question

I'm currently trying to optimize several queries and scripts, and I wonder if there is a way to cut a certain percentage from the MySQL result set?

for example, I have a query like this:

SELECT TrackingID, OpenTime, FirstPoint
FROM PointsTable

I need an “average” of 74%, sorted by the difference between FirstPointGMT and OpenTimeGMT, which means: I need to disable the top and bottom 13%.

For the time being, Script will just get all the rows, calculate and sort by duration, and then turn them off, but I would like to do this in SQL Query, if possible to do a Script clearer.

I know about the LIMIT section, but it doesn't seem to support relative values ​​such as "13 percent."

Can someone say if a) if it is really possible only with SQL and b) how could this be achieved?

MySQL 5.0.45.

PS: And yes, I know that 13% seem like a very strange number, but this is part of a larger process.

Answer 1

You need to LIMIT the data you return by the percentiles of the rows returned in the result set.

Try the following:

SELECT TrackingID, OpenTime, FirstPoint
FROM PointsTable
ORDER BY FirstPointGMT - OpenTimeGMT
LIMIT (13*((SELECT COUNT(*) FROM PointsTable) +1) / 100), 
((SELECT COUNT(*) FROM PointsTable)
 -
 (26*((SELECT COUNT(*) FROM PointsTable) +1) / 100)
)

Explanation above

The only tricky thing here is the Limit clause and its syntax:

> LIMIT start_row,number_of_rows_to_return

Our start_row should be the first element after the first record 13 / 100th (13% of the way) in the result set. To do this, we use this formula:

rownumber_or_number_of_rows_in_resultset_Npercent_in = 
( Npercent * ( number_of_rows_in_resultset + 1) / 100)

which is equal (13*((SELECT COUNT(*) FROM PointsTable) +1) / 100).

Our number_of_rows_to_return should be the total number of lines, minus 26% of the lines (13% of the bottom and 13% of the top) or

(total_number_of_rows - number_of_rows_26percent_of_the_way_in)

, :

( (SELECT COUNT(*) FROM PointsTable)
  - 
  (26*((SELECT COUNT(*) FROM PointsTable) +1) / 100) 
)

---

EDIT: Niyaz , :

SELECT TrackingID, OpenTime, FirstPoint
FROM PointsTable
ORDER BY FirstPointGMT - OpenTimeGMT
LIMIT (13*((SELECT COUNT(*) FROM PointsTable) +1) / 100), 
 (74*((SELECT COUNT(*) FROM PointsTable) +1) / 100)

, . , 13% 74% .

Answer 2

.

-:

SELECT count(*) as c 
FROM PointsTable;

php :

a = c * 13 / 100
b = c * 74 / 100

-:

SELECT TrackingID, OpenTime, FirstPoint 
FROM PointsTable
LIMIT a,b;

a, b .

, .

Answer 3

, , .

/, , .

Answer 4

. 3:

select sql_calc_found_rows fields from table where conditions limit 0;
select @cnt := found_rows();
select fields from table where conditions limit @cnt*0.13,@cnt-@cnt*0.13

sql calc MySQL , , , found_rows(), , 74%.