tag:blogger.com,1999:blog-4670514573534452370.post7542324487127674772..comments2019-07-19T04:47:28.946-07:00Comments on Monty Latiolais: NTILE vs WIDTH_BUCKET and the Winner is...PIVOTMonty Latiolaishttp://www.blogger.com/profile/01051714570698862872noreply@blogger.comBlogger3125tag:blogger.com,1999:blog-4670514573534452370.post-55733458135197122802019-07-19T04:47:28.946-07:002019-07-19T04:47:28.946-07:00Is using PIVOT or MODEL faster than NTILE/WIDTH_BU...Is using PIVOT or MODEL faster than NTILE/WIDTH_BUCKET though?Martin Rosehttps://www.blogger.com/profile/15990246084222003590noreply@blogger.comtag:blogger.com,1999:blog-4670514573534452370.post-88695021846735436962015-07-10T07:42:34.942-07:002015-07-10T07:42:34.942-07:00Thanks, Kim for taking the time to explain the oth...Thanks, Kim for taking the time to explain the other options. You're [pre]awesome[/pre].Monty Latiolaishttps://www.blogger.com/profile/01051714570698862872noreply@blogger.comtag:blogger.com,1999:blog-4670514573534452370.post-81367885030155248512015-07-10T00:17:21.583-07:002015-07-10T00:17:21.583-07:00I think you found a very nice and easy way, Monty ...I think you found a very nice and easy way, Monty :-)<br /><br />The first question I would ask myself when given the task to split into columns alphabetically is, whether the alphabetic sorting should be "down first, then across" or "across first, then down". If you want "down first, then across", then NTILE would be the easy way:<br /><br />select col_1, col_2, col_3<br /> from (<br /> select ename<br /> , col_num<br /> , row_number() over (partition by col_num order by ename) row_num<br /> from (<br /> select ename<br /> , ntile(3) over (order by ename) col_num<br /> from emp<br /> )<br /> )<br /> pivot (<br /> max(ename)<br /> for col_num in (<br /> 1 as col_1<br /> , 2 as col_2<br /> , 3 as col_3<br /> )<br /> )<br /> order by row_num<br />/<br /><br />COL_1 COL_2 COL_3<br />---------- ---------- ----------<br />ADAMS JAMES SCOTT<br />ALLEN JONES SMITH<br />BLAKE KING TURNER<br />CLARK MARTIN WARD<br />FORD MILLER<br /><br />Split the data ordered alphabetically into three buckets of equal height. Then the buckets become your columns.<br /><br />WIDTH_BUCKET making buckets of equal width could potentially (with some manipulation to make a numeric value) create columns, where col_1 was A-F, col_2 was G-L, and so on. Then col_1 might have 5 rows, col_2 only 3 rows, etc. It would be more tricky, but such a solution would be what WIDTH_BUCKET could be useful for.<br /><br />If you want "across first, then down", then to use NTILE you would have to make NTILE create rows instead of columns, which would require you to first find out how many buckets you need. It can be done, but it's a kind of "hack":<br /><br />select col_1, col_2, col_3<br /> from (<br /> select ename<br /> , row_num<br /> , row_number() over (partition by row_num order by ename) col_num<br /> from (<br /> select ename<br /> , ntile(buckets) over (partition by buckets order by ename) row_num<br /> from (<br /> select ename<br /> , ceil(count(*) over () / 3) buckets<br /> from emp<br /> )<br /> )<br /> )<br /> pivot (<br /> max(ename)<br /> for col_num in (<br /> 1 as col_1<br /> , 2 as col_2<br /> , 3 as col_3<br /> )<br /> )<br /> order by row_num<br />/<br /><br />COL_1 COL_2 COL_3<br />---------- ---------- ----------<br />ADAMS ALLEN BLAKE<br />CLARK FORD JAMES<br />JONES KING MARTIN<br />MILLER SCOTT SMITH<br />TURNER WARD<br /><br />If you are not using a literal constant for number of buckets in NTILE, it needs to be an expression that the database can be sure is constant within each partition. PARTITION BY buckets meet that requirement, and as I know buckets is a constant identical in all rows, I just get one big partition of all rows. It's kind of a hack, but doable.<br /><br />But I wouldn't use NTILE that way, I'd use your own ROW_NUMBER / PIVOT solution. If I'm on an old version that doesn't support PIVOT, I could do an "oldfashioned" pivoting:<br /><br />select max(case col_num when 0 then ename end) col_1<br /> , max(case col_num when 1 then ename end) col_2<br /> , max(case col_num when 2 then ename end) col_3<br /> from (<br /> select ename<br /> , trunc ( ( row_number() over ( order by ename ) - 1 ) / 3 ) as row_num<br /> , mod ( row_number() over ( order by ename ) - 1, 3 ) as col_num <br /> from emp<br /> )<br /> group by row_num<br /> order by row_num<br />/<br /><br />COL_1 COL_2 COL_3<br />---------- ---------- ----------<br />ADAMS ALLEN BLAKE<br />CLARK FORD JAMES<br />JONES KING MARTIN<br />MILLER SCOTT SMITH<br />TURNER WARD<br /><br />But the only reason for that would be old database version - PIVOT does the same thing ;-)<br /><br />(Blogger doesn't allow me to put PRE tags in the comment, so sorry about the code looking bad...)Kim Berg Hansenhttps://www.blogger.com/profile/06491635470794828550noreply@blogger.com