*** 36,92 ****
START WITH <conditions> CONNECT BY [PRIOR]<conditions> (ORACLE)
! *: Stored Procedures
! *: Rollup and Cube - _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.
*: CREATE DATABASE, DROP DATABASE - _Does not seem meaningful for an
embedded database engine like SQLite. To create a new database, just
--- 36,44 ----
START WITH <conditions> CONNECT BY [PRIOR]<conditions> (ORACLE)
! *: 2003.07.28 : Stored Procedures
! *: 2003.07.28 : Rollup and Cube -> UnsupportedSqlRollupAndCube
*: CREATE DATABASE, DROP DATABASE - _Does not seem meaningful for an
embedded database engine like SQLite. To create a new database, just
Small. Fast. Reliable.
Choose any three.
Choose any three.
|
|