Oracle SQL Model Clause
參考官方文件,學習了一下Oracle SQL Model語法。
摘自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).
官方文件地址:http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/10g/r2/prod/bidw/sqlmodel/sqlmodel_otn.htm
現將過程記錄在這裡:
1. 準備過程, 連線到Oracle自帶schema SH(sales history),建立資料來源:
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語法分為3個部分,partition, dimension, measures。
Partition與分析函式的partition類似,將結果集分成了若干邏輯塊。Model的rules被應用於每個partition的cells。
原文:
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用於在每一個partition內區分每個measure的單元格。有點類似於excel中的行與列。如(A1, B1)就定義了一個單元格
原文:
Dimensions identify each measure cell within a partition. These columns identify characteristics such as date, region, and product name.
Measures:
Measures近似於星形模型中的事實表。它們典型包含數值,如銷售單位或成本。每一個單元格都通過指定全部的維度在它的partition內訪問。
原文:
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. 例項:
例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['2_Products', 2002] = sales['Bounce', 2002] + sales['Y Box', 2002])
ORDER BY country, prod, year;
這個SQL建立了幾個新行,比如sales['Y Box', 2002]使用的是
sales['Y Box
', 2001
]的銷售金額,
sales['2_Products
', 2002
]
是另外兩個產品2002年的銷售金額之和
例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 <= 2002],
sales['Y Box', 2003] = sum(sales)['Y Box',year <= 2002],
sales['2_Products', 2002] = sales['Bounce', 2002] + sales['Y Box', 2002])
ORDER BY country, prod, year;
這個例子中,引入了新的rules,
sales['Bounce', 2003] = sum(sales)['Bounce',year <= 2002]表示
產品Bounce在2003年的收入金額是2002年以及2002年前的銷售金額之和
例項3:算累加
with t as (
select rownum rn from dual connect by rownum <= 100
)
select rn, total from t
model return updated rows
dimension by (rn)
measures(0 total)
rules(
total[rn] = cv(rn)+ nvl(total[cv(rn) - 1], 0)
)
求出1到100的和, 這裡用了函式CV(current value)去引用其他單元格資料
另一個求累加的:
with t as (
select 1 rn from dual
)
select rn,total from t
model return updated rows
dimension by (rn)
measures(0 total)
rules iterate(100)(
total[1]= nvl(total[1], 0) + (ITERATION_NUMBER +1)
)
rules iterate(100)代表的是將規則迭代100次。
ITERATION_NUMBER是迴圈計數,注意是從0開始
參考:
http://www.itpub.net/thread-1904347-4-1.html
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/8520577/viewspace-2163006/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle Model ClauseOracle
- About Oracle WITH clauseOracle
- MS-SQL 錯誤: The offset specified in a OFFSET clause may not be negativeSQL
- ANALYZE INDEX clauseIndex
- ORA-01788: CONNECT BY clause required in this query block For ORACLE 10GUIBloCOracle 10g
- [Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains 的問題 MySQLExpressAIMySql
- [Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated cExpressAI
- ALTER SESSION ADVISE ClauseSession
- ALTER SYSTEM DISCONNECT SESSION ClauseSession
- SQL Server Service Pack Model and Naming RuleSQLServer
- Oracle Reporting 6 - ModelOracle
- sql for modeling part1SQL
- Oracle Reporting 7 - Model ExamplesOracle
- In Oracle 10g,What is Time Model?Oracle 10g
- mysql主給備賦予許可權時報錯,MySQL [Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clauseMySqlExpress
- Why NoSQL Should Be Called "SQL with Alternative Storage Models"SQL
- 【SQL】Oracle SQL monitorSQLOracle
- SQL in ORACLE and SQL ServerSQLOracleServer
- oracle data Format Models---二(轉)OracleORM
- oracle 11g model子句的用法Oracle
- UNRECOVERABLE DATAFILE clause in the ALTER DATABASE CLEAR LOGFILE statementDatabase
- SQL Server的幾種恢復模式(recovery models)SQLServer模式
- Oracle Database 10g Enhanced wait modelOracleDatabaseAI
- (轉載)ORACLE MODEL子句學習筆記Oracle筆記
- AutoPeftModel、AutoModel、PeftModel
- You can‘t specify target table ‘Person‘ for update in FROM clause
- ORA-01788: CONNECT BY clause required in this query blockUIBloC
- 認識SQL Server2000 model資料庫SQLServer資料庫
- ORACLE TOP SQLOracleSQL
- Oracle樹 sqlOracleSQL
- Oracle 常用SQLOracleSQL
- oracle SQL with 子句OracleSQL
- Oracle SQL JOINOracleSQL
- first oracle sqlOracleSQL
- Oracle SQL optimizationOracleSQL
- Oracle PL/SQLOracleSQL
- list is not in GROUP BY clause and contains nonaggre的問題AI
- 【Oracle】--PL/SQL匯入Oracle sql指令碼"傻瓜教程"OracleSQL指令碼