*** 2,51 **** _Who can tell me what this means?_ ! _::: Rollup and Cube are OLAP terms. See for example ! http://en.wikipedia.org/wiki/OLAP_cube ! _::: I don't know much about it myself, but a quick google on the subject gives ! me... http:// ! www.winnetmag.com/SQLServer/Article/ArticleID/5104/5104.html and ! http://databases.about.com/ ! library/weekly/aa070101a.htm ! ! _::: both of these imply that the CUBE operator causes new rows to be generated ! to give a wildcard ! value to non-numeric columns and summing the numeric columns which match those ! wildcards. The ! potential for generating a huge amount of data with cube is implicit, I think - ! hence its name. ROLLUP ! appears to be related but removes some of the wildcards; I couldn't determine ! what from the limited information in the articles. I could not find, on brief examination any more ! definitive reference. Anyone ! got something more definitive than those articles ? It seems to me that you ! can do with sum() ! everything you can do with CUBE. ! ! _::: CUBE an ROLLUP provide addition subtotal rows. Lets say you are doing a ! query "SELECT x, y, ! SUM(z) FROM t GROUP BY x, y" lets also say x and y each have two values. This ! query will give you the ! sums for all records with x1 y1, x1 y2, x2 y1, and x2 y2. Rollup and cube both ! provide addition ! subtotals. Rollup adds 3 new sums: for all x1, for all x2, and the grand ! total. You can imagine that the ! GROUP BY list is being rolled up, so that it goes from being x, y; to being ! just x; to being empty. The ! result of the select for the column that is rolled up becomes NULL. CUBE will ! do all combinations of sums in the group by list: sum of all x1, all x2, all y1, all y2, and grand ! total. No idea what that has to ! do with a cube, though I do sort of picture a hyper-cube in my mind for no good ! reason. If you ever ! add ROLLUP and CUBE, I also recommend adding the GROUPING() function so that ! you can filter out the ! additional computations you don't want, or do somthing like SELECT CASE WHEN ! GROUPING(name) ! THEN ! 'Total' ELSE name END, hours FROM timesheets GROUP BY name. I've used the ! feature plenty doing ! reports, but then I'm a chronic SQL abuser. --- 2,39 ---- _Who can tell me what this means?_ ! =ROLLUP= and =CUBE= are OLAP terms. See for example http://en.wikipedia.org/wiki/OLAP_cube ! ---- ! ! I don't know much about it myself, but a quick google on the subject gives ! me... http://www.winnetmag.com/SQLServer/Article/ArticleID/5104/5104.html and ! http://databases.about.com/library/weekly/aa070101a.htm ! ! Both of these imply that the =CUBE= operator causes new rows to be generated ! to give a wildcard value to non-numeric columns and summing the numeric columns which match those wildcards. The potential for generating a huge amount of data with cube is implicit, I think - hence its name. =ROLLUP= appears to be related but removes some of the wildcards; I couldn't determine what from the limited information in the articles. I could not find, on brief examination any more ! definitive reference. Anyone got something more definitive than those articles? It seems to me that you can do with =SUM= everything you can do with =CUBE=. ! ! ---- ! ! =CUBE= and =ROLLUP= provide addition subtotal rows. Let's say you are doing a query ! ! SELECT x, y, SUM(z) FROM t GROUP BY x, y ! ! Let's also say x and y each have two values. This query will give you the ! sums for all records with x1 y1, x1 y2, x2 y1, and x2 y2. =ROLLUP= and =CUBE= both provide addition subtotals. =ROLLUP= adds 3 new sums: for all x1, for all x2, and the grand total. ! ! You can imagine that the =GROUP BY= list is being rolled up, so that it goes from being x, y; to being just x; to being empty. The result of the select for the column that is rolled up becomes NULL. =CUBE= will do all combinations of sums in the group by list: sum of all x1, all x2, all y1, all y2, and grand ! total. No idea what that has to do with a cube, though I do sort of picture a hyper-cube in my mind for no good reason. ! ! If you ever add =ROLLUP= and =CUBE=, I also recommend adding the =GROUPING()= function so that you can filter out the additional computations you don't want, or do something like ! ! SELECT CASE WHEN GROUPING(name) ! THEN 'Total' ELSE name END, ! hours ! FROM timesheets ! GROUP BY name; ! ! I've used the feature plenty doing reports, but then I'm a chronic SQL abuser.
Small. Fast. Reliable.
Choose any three.
Choose any three.
|
|