• Status: Solved
• Priority: Medium
• Security: Public
• Views: 1331

# 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
0
studioEtc
1 Solution

Commented:
Can you exactly post your requirement, what do you want to do?
0

Commented:
0

Commented:
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?
0

Commented:
quick change:

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

Author Commented:
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
0

Author Commented:
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?
0

Billing EngineerCommented:
it is not possible in 1 single query, with mysql.

the logic is this:
if you have N rows (for example, N=100), and a "Tile" T = 5, meaning you want 5 tiles, you have to get N/T rows into each tile.
the problem is the rounding for example if you have N=98 and T=5, you want to have 3x20 and 2x19, and not 4x20 and 1x18

so, what you need is:
1) the row count from the table (applying any filters). must be done with a dedicated row count.
2) the unrounded number of rows per tile N/T. just the result of 1) + the value of T given.
3) a row number for each row. this part is possible, with a trick:

``````select @num := @num + 1 as row_number
, other_columns_go_here
from your_table;
``````
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.