Cube Rollup Grouping Sets (1)
Originally from: -) Night (- - liter) --High Above The Ionosphere - - Night Run - Aggregation is the basis of the data warehouse. In order to improve the performance of aggregation. Oracle provided GROUP BY Terms Extension. 3 Grouping Functions 3. Grouping SET Expansion Cube Rollup Grouping Sets The extension of SQL makes queries and reports simply and quickly. ROLLUP calculates a function such as SUM Count Max Min AVG, increasing the level of aggregate. Cube is an extension similar to ROLLUP, making it possible to calculate all possible aggregates with a statement. Cube can generate Cross-Tabulations reported by single strip. Information. Cube, Rollup, Grouping Sets extension allows you to accurately play for the Group By Terms you are interested in. Do not run Cube operations can also be analyzed from multiple dimensions. Calculating a Full Cube will bring high Loads, so replace CUBE for Grouping Sets, can significantly improve performance. Cube rollup grouping set can generate a single result set, equivalent to Union All. In order to improve performance, Cube, Rollup, and Grouping Sets can be parallel: Multi-process Computing all statements. This feature makes aggregation calculations more efficient, thus improving database performance and measuring. From multiple perspectives to investigate. DEMENSION, numerous dimensions are generally time, space, products, sectors, channels, and enterprises have a variety of dimensions. Events and entities-specific dimensions are so-called FACTS. These so-called Facts can be sales, cash, profits, customer accounts, or some other worth recorded stuff. The following is an example of some multi-dimensional demands 1. Show 1999 and 2000, total sales, aggregate levels for the regional dimension, from state to national to regional .2. Creating a cross-report analysis the cost range of 1999 and South America in 2000, including all the little note all requirements contain a lot of multi-dimensional multi-dimensional problem. Need data aggregation, comparison of data sets, usually leaping time, geographic. Optimized Performance odtimized performance Strong polymerization is good. The processing of the transaction, the financial manufacturing system - all of these have a large number of production reports require a lot of system resources. Create these reports to reduce system loads and enhance system performance. In fact, the computer processing plexus to high-level aggregated data can be beneficial by optimizing aggregation performance. Oracle9i provides expansion aggregation function, which brings a lot of benefits. Includes: 1. Simplified code programming 2. Fast and efficient query processing 3. Reduce client loads and network loads. 4. Due to simple queries can buffer aggregation
Current Rollup Cube Grouping Sets (2)
ROLLUP EXTENSION TO GROUP BYROLLUP can make the SELECT statement based on Group's dimensional countermeters. He can also calculate the total. Rollup is a simple extension of Group By, so his syntax is very simple. The Rollup extension is very efficient, and there is very little overhead of a query. Rollup's behavior is very straightforward, it creates a small count according to the Rollup terms of Grouping List. It re-aggregates from right left. First, it calculates the standard aggregation of the Grouping Terms. It is then, which is a higher layer from the right left order in accordance with the Grouping List. Finally, create a total of N 1 layers. N = number of grouping columns. For example, a query uses Rollup lists Time, Region, Department, and the result set consists of 4 aggregation hierarchies. Using ROLLUP can compress data. This is useful for situations where older partition is less. When to use rollup generally contains a small tap in the task to use ROLLUP extensions. Rollup syntax ?
His form is: Select ... Group by Rollup (grouping_column_reference_list) Select channel_desc, calendar_month_desc, contry_id, sum (amount_sold) SALES $ From sales, customers, times, channelsWhere sales .time_id = times.time_id And sales.cust_id = customer.cust_id And sales.channel_id = channels.channel_id And channels.channel_desc IN ( 'direct sales', 'Internet') And times.calendar_month_desc IN ('2000-09', '2000-10') -------------------------- -------------- DIRECT SALES 2000-09 UK 1,378,126Direct Sales 2000-09 US 2,835,557Direct Sales 2000-09 4,213,683Direct Sales 2000-10 UK 1,388,051Direct Sales 2000-10 US 2,908,706Direct Sales 2000-10 4,296,757Direct Sales 8,510,440 intenet 2000-09 butternet 2000-09 US 1,732, 240,979,internet 2000-10 UK 876, 571INTERNET 2000-10 US 1,893,753,internet 2000-10 2,770,32 4 inNTERNET 5, 414, 3033, 924, 743th Query Returns The Following Sets of Rows: This query returns the following line: 1. GROUP BY standards specified by the aggregation 2. Small Types Based on Country_ID Layer. 2-layer aggregation based on Calendar_Month_Desc and Country_ID 4. Total rollup section rollup You can also use ROLLUP to include a limited number of small gauges. Some rollup syntax is to GROUP BY EXPR1, ROLLUP (EXPR2, EXPR3) In this case, the GROUP BY clause creates a 2 1 layer of small remember.
level is (expr1, expr2, expr3) (expr1, expr2) (expr1) select channel_desc, calendar_month_desc, country_id, sum (amount_sold) sales $ from sales, customers, times , channelsWhere sales.time_id = times.time_id And sales.cust_id = customer.cust_id And sales.channel_id = channels.channel_id And channels.channel_desc IN ( 'direct sales', 'Internet') And Times.calendar_Month_Desc in ('2000-09', '2000-10') Co Sales $ -------------------------- -------------- 4,296,757 Direct Sales 8,510,440,1739Internet 2000-09 US 1,732,240,3, 979Internet 2000-10 UK 876, OnetNet 2000-10 UK 876, 571Internet 2000-10 US 1,893,753ingerNet 2000- 10 2, 770, 324Internet 5,414 303 this query returns the following line. GROUP BY standards specified by the aggregation 2. Small Types Based on Country_ID Layer. 2-layer aggregation based on Calendar_Month_Desc and Country_ID 4. No calculation totals: A96520.pdf matters Rollup Cube GroupingSets (3)
Cube extension to group by Cube, a Grouping specified in the Grouping column, creating all possible small tits. In a multi-dimensional analysis, CUBE generates all the possible calculated small records of the specified dimension of the CUBE data. If the Cube (Time, Region, Department) is specified, the result set will include all values of the corresponding Rollup plus additional mergers. If there is a N class to specify in CUBE, then there will be 2 N-movies to return. When to use cube need CUBE where you need to cross the report. The data generation of cross-reports can be implemented by simple SELECT query statements with CUBE. Like Rollup, Cube is very useful in summary reports. Cube compares a multi-layer multi-layer, more useful in multidimensional conditions. Cube syntax Cube appears in the GROUP BY terms of the SELECT statement.
Its form is: Select ... group by CUBE (group_column_reference_list) Example: select channel_desc, calendar_month_desc, country_id, sum (amount_sold) sales $ from sales, customers, times, channels Where sales.time_id = times.time_id And sales.cust_id = customer.cust_id And sales.channel_id = channels.channel_id And channels.channel_desc IN ( 'direct sales', 'Internet') And Times.calendar_Month_Desc in ('2000-09', '2000-10') $ ------------------------------ d Sales 2000-09 Direct Sales 2000-09 USS 2,835,557 Direct Sales 2000-09 4,213,683 Direct Sales 2000-10 UK 1,388,051 Direct Sales 2000-10 US 2,908,706 Direct Sales 2000-10 4,296,757 网Ternet 2000-10 US 1,893,753,internet 2000-10 2,770,324,internet UK 1,788,310,internet US 3,625,993,internet 5,414,3032000-09 UK 2,289,8652000-09 US 4,567,7972000-09 6,857,6622000 -10 UK 2,264,6222000-10 US 4,802,4592000-10 7,067,081uk 4,554,487us 9,370,25613,924,743partial cube part cube You can also put some columns outside CUBE. In this case, all possible subtots are limited to the CUBE list. portion CUBE syntax GROUP BY expr1, CUBE (expr2, expr3) calculated Subtotal 4: n (expr1, expr2, expr3) n (expr1, expr2) n (expr1, expr3 like Rollup, multiple SELECT statements can be provided with the same information as CUBE. However, there is a need for multiple SELECT statements. For N-dimensional CUBE, you need 2 N times SELECT query.
In the 3D example, this means that these SELECT statements need to be coupled through the UNION ALL. So many SELECT statements will make the processing not efficient enough and extend the SQL statement. # # first, how do you let the program know the result set represents the section, and how to know the exact polymerization level for a given section? If the query result appears NULL and the NULL generated by Cube Rollup, how do you distinguish between States Function Use a column as a parameter. Grouping Returns 1 when Rollup and Cube generate NULL values.
That is, if this column is a section, grouping returns 1; otherwise returning 0 g Example 1, 'All Countries', country_id) AS Country, TO_CHAR (SUM (amount_sold), '9,999,999,999') SALES $ FROM sales, customers, times, channelsWHERE sales.time_id = times.time_id AND Sales.cust_id = Customers.cust_id and Sales.Channel_ID = CHANNELS.CHANNEL_ID AND Channels.Channel_Desc in ('Direct Sales',' Internet ') andtTimes.calendar_Month_Desc =' 2000-09' 2000-09 And country_id in ('UK', 'US') Channel Country Sales $ # ---------------------------------------------------------------------------------------- - - -------------------------- Direct Sales UK 1,378, 126 Direct Sales US 2,835,557 Direct Sales All Countries 4,213,683 Internet UK 911, 739Internet US 1,732, 240Internet All Countries 2,643,979 All Channels UK 2,289,865 All Channels US 4,567,797All Channels All Countries 6,857,662 used to restrict the having clause HAVING (GROUPING (channel_desc) = 1 AND GROUPING (calendar_month_desc) = 1 ANDGROUPING (country_id) = 1 ) = 1) = 1) OR (country_id) = 1 and grouping (Calendar_Month_Desc) = 1); this is all all aggregated data. Grouper sets expression Via Group Sets, you can selectively set the Grouping you want.
