Link to home
Start Free TrialLog in
Avatar of Jim Singelis
Jim Singelis

asked on

nTile()

Can anybody give me a mySQL query that would be the equivalent of the SQL Server query:

SELECT aa, NTILE(12) OVER (ORDER BY avgWksWork) AS work_nTile  FROM avgWk
Avatar of nkhelashvili
nkhelashvili

Can you exactly post your requirement, what do you want to do?
so, Im by no means a mysql expert, but NTILE in SQL Server just splits your result set into 12 segments or "tiles"....so, essentially, if you can assign a sequential increasing number to each record in your resultset, you can do something like this:


select ceiling(increasingnumber /12), * as tile
from yourtable

where the increasing number is divided by 12, and the result is your tile...  ceiling(1/12) = 1, 13/12 = 2, et al.

Does that make sense?
quick change:

select ceiling(increasingnumber /12) as tile, *
from yourtable
Avatar of Jim Singelis

ASKER

ceiling(num/12) won't work: ex (1432/12) = 119.  I thought maybe  num mod 12, but it won't work either.

Here's what SQL Server gives me --to make a shorter sample I used NTILE(5) instead of NTILE(12).

SELECT emp, avgwkwk,  NTILE(5) OVER (ORDER BY avgwkwk) AS QUINTILE  FROM avgWk

 EMP    AVGWKWK   QUINTILE
ggg         2.46            1
ddd         8.75            1
fff          10.46            1
aaa       11.97            2
lll           12.46            2
hhh       19.24            2
ccc        32.56           3
iii           49.72           3
mmm    50.25           3
jjj           60.02           4
kkk        69.74           4
bbb       70.13           5
eee       80.21           5
I ended up using a loop, stepping thru each row of the table - yuck.  but at least it worked.  I ended up writing it in php, but it could have just as easily been a stored procedure.  Here the gist of the logic:

If your are doing nTile(5) and the table has 500 rows then it's pretty straight forward.  Order the table by the column that contains the metric you are ranking and each quintile will contain 100 rows.

The problem is when numRows mod 5 <> 0, as when your table has 503 rows.
503 mod 5 = 3 tells me that I have to disperse 3 extra rows.  - So the 1st, 2nd and 3rd quintiles will contain 101 rows and the 4th and 5th quintile will contain 100 rows. (it could also be 1st and 2nd quintile contain 100 rows and 3rd, 4th and 5th contain 101)

If you're working with nTile(24) and have 119 rows then the first 23 percentiles will contain 5 rows each and the 24th percentile will contain 4 (ie floor(119/24) )


Can anybody figure out how to do this just using just SQL without a while loop?
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial