Shulou(Shulou.com)06/01 Report--

Refer to the official documentation and learn the Oracle SQL Model syntax.

Excerpt from Pro Oracle SQL:

With the MODEL clause, you build matrixes (or a model) of data with a variable number of dimensions. The model

Uses a subset of the available columns from the tables in your FROM clause and has to contain at least one dimension

At least one measure, and, optionally, one or more partitions. You can think of a model as a spreadsheet file containing

Separate worksheets for each calculated value (measures). A worksheet has an x-and a y-axis (two dimensions), and

You can imagine having your worksheets split up in several identical areas, each for a different attribute (partition).

Official document address: http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/10g/r2/prod/bidw/sqlmodel/sqlmodel_otn.htm

Now record the process here:

1. Prepare the process, connect to the Oracle native schema SH (sales history), and create a data source:

CREATE VIEW sales_view AS

SELECT country_name country, prod_name prod, calendar_year year

SUM (amount_sold) sale, COUNT (amount_sold) cnt

FROM sales, times, customers, countries, products

WHERE sales.time_id = times.time_id AND

Sales.prod_id = products.prod_id

AND sales.cust_id = customers.cust_id

AND customers.country_id = countries.country_id

GROUP BY country_name, prod_name, calendar_year

2. Model syntax is divided into three parts, partition, dimension and measures.

Partition, like the partition of an analysis function, divides the result set into several logical blocks. Model's rules is applied to each partition's cells.

Original: Partitions define logical blocks of the result set in a way similar to the partitions of the analytical functions (described in the chapter titled "SQL for Analysis in Data Warehouses" in the Data Warehousing Guide). MODEL rules are applied to the cells of each partition.

Dimension is used to distinguish the cells of each partition within each measure. It is somewhat similar to rows and columns in excel. For example, (A1, B1) defines a cell.

Original text: Dimensions identify each measure cell within a partition. These columns identify characteristics such as date, region, and product name.

Measures: Measures is similar to the fact table in the star model. They typically contain values, such as sales units or costs. Each cell is accessed within its partition by specifying all the dimensions.

Original text: Measures are analogous to the measures of a fact table in a star schema. They typically contain numeric values such as sales units or cost. Each cell is accessed within its partition by specifying its full combination of dimensions.

3. Example:

Example 1: SELECT SUBSTR (country,1,20) country

SUBSTR (prod,1,15) prod, year, sales

FROM sales_view

WHERE country IN ('Italy','Japan')

MODEL RETURN UPDATED ROWS

PARTITION BY (country)

DIMENSION BY (prod, year)

MEASURES (sale sales)

RULES (

Sales ['Bounce', 2002] = sales [' Bounce', 2001] + sales ['Bounce', 2000]

Sales ['Y Box', 2002] = sales ['Y Box', 2001]

Sales ['2products products, 2002] = sales [' Bounce', 2002] + sales ['Y Box', 2002])

ORDER BY country, prod, year

This SQL creates several new lines, for example, sales ['Y Box', 2002] uses the sales amount of sales ['Y Box', 2001], and sales ['2_Products', 2002] is the sum of the sales of the other two products in 2002

Example 2:

SELECT SUBSTR (country,1,20) country

SUBSTR (prod,1,15) prod, year, sales

FROM sales_view

WHERE country IN ('Italy','Japan')

MODEL RETURN UPDATED ROWS

PARTITION BY (country)

DIMENSION BY (prod, year)

MEASURES (sale sales)

RULES (

Sales ['Bounce', 2002] = sales [' Bounce', 2001] + sales ['Bounce', 2000]

Sales ['Y Box', 2002] = sales ['Y Box', 2001]

Sales ['Bounce', 2003] = sum (sales) [' Bounce',year

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.