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
SELECT aa, NTILE(12) OVER (ORDER BY avgWksWork) AS work_nTile FROM avgWk
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?
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
select ceiling(increasingnumber /12) as tile, *
from yourtable
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
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
ASKER
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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.