Comments
| ROLLUP, CUBE, GROUPING Functions and GROUPING SETS - An overview of some functionality available for aggregation in data warehouses. |
lesio said... Very good and precise explanation :) |
DongHang said... Thanks for details explanation. |
Raghu said... Hi Tim,Thanks for the elaborated explanation on ROLLUP. However I don't understand your point {quote}If "n" is the number of columns listed in the ROLLUP, there will be n+1 levels of subtotals.{quote} How the levels of subtotals could only depend on "columns list inside rollup", I believe it also depends on the no.of distinct values in each of those columns? |
Raghu said... I wrote a small pseudo code that would return no.of “subtotals” given 'n' is number of columns inside rollup. Please correct me if I misread your point.for x in 1..n-1 loop temp := null; for i in 1..x loop temp := (nvl(temp,1) * distinct(count(col(i)))) end loop; subtotals := nvl(subtotals,0)+temp; end loop; |
Tim... said... Hi."n+1 levels of subtotals". **levels** is the important word. This means "types" of subtotals, not the total number of each type of subtotals. Cheers Tim... PS. Questions in FORUM!!! |
AAA said... explanation could have been more elaborate....very confusing for me :/ |
Tim... said... Hi.Like all my articles, it is intentionally brief. if you need more details, you should read the Oracle documentation linked to at the end of the article. Cheers Tim... |
Max said... Hi Tim,I just read your explanation on the ROLLUP statement, quoting "If "n" is the number of columns listed in the ROLLUP, there will be n+1 levels of subtotals." Your query has two columns inside the ROLLUP statement: SELECT fact_1_id, fact_2_id, SUM(sales_value) AS sales_value FROM dimension_tab GROUP BY ROLLUP (fact_1_id, fact_2_id) ORDER BY fact_1_id, fact_2_id; |
Max said... And in the results you only have 2 levels of ROLLUP, totals when fact_2_id is null and the big total when both ROLLUP columns are null. |
Max said... That, of course, if you don't count the SUM value for single values of columns 1 and 2 as a level. If you do count it as a level, then you do have n+1 levels.Aside that, big help. |
Tim... said... Hi.I do count that. :) As do Oracle. See this paragraph in the documents. http://docs.oracle.com/cd/E11882_01/server.112/e16579/aggreg.htm#i1007413 Cheers Tim... |
DO NOT ask technical questions here, that's what my forum is for!
These comments should relate to the contents of a specific article. Constructive criticism is good. Advertising and offensive comments are bad and will be deleted!