--登录sh用户
SQL> conn sh/sh;
--建立sales_view视图
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
/
-- 例子一
-- 先看一个正常的例子，分组统计操作
SELECT SUBSTR(country,1,20) country,
SUBSTR(prod,1,15) prod, year, sale
FROM sales_view
WHERE country IN ('United States of America','Japan')
ORDER BY country, prod, year;
--共查询到3446行数据。
--下面运用行间运算，统计1999年和2000年，产品Simi Girls Dress和Kenny Cool Bye-Bye Birdy的和，并作为2001年预计产量。
SELECT SUBSTR(country,1,20) country,
SUBSTR(prod,1,15) prod, year, sales
FROM sales_view
WHERE country IN ('United States of America','Japan')
MODEL RETURN All ROWS
PARTITION BY (country) --分区
DIMENSION BY (prod, year) --维度
MEASURES (sale sales)
RULES (
sales['Simi Girls Dress', 2001] = sales['Simi Girls Dress', 2000] + sales['Simi Girls Dress', 1999],
sales['Kenny Cool Bye-Bye Birdy', 2001] = sales['Kenny Cool Bye-Bye Birdy', 2000]+ sales['Kenny Cool Bye-Bye Birdy', 1999])
ORDER BY country, prod, year
--共查询到3450行数据数据。
--把All改为updated关键字，只检索先产生的数据
SELECT SUBSTR(country,1,20) country,
SUBSTR(prod,1,15) prod, year, sales
FROM sales_view
WHERE country IN ('United States of America','Japan')
MODEL RETURN updated ROWS
PARTITION BY (country) --分区
DIMENSION BY (prod, year) --维度
MEASURES (sale sales)
RULES (
sales['Simi Girls Dress', 2001] = sales['Simi Girls Dress', 2000] + sales['Simi Girls Dress', 1999],
sales['Kenny Cool Bye-Bye Birdy', 2001] = sales['Kenny Cool Bye-Bye Birdy', 2000]+ sales['Kenny Cool Bye-Bye Birdy', 1999])
ORDER BY country, prod, year
--检索到4行数据。
-- 例子二
--1.
SELECT SUBSTR(country, 1, 20) country,
SUBSTR(prod, 1, 15) prod,
year,
sale
FROM sales_view
WHERE country = 'Japan'
and prod = 'Simi Girls Dress'
ORDER BY country, prod, year;
--查询结果：
--1 Japan Simi Girls Dres 1998 40854.9
--2 Japan Simi Girls Dres 1999 28503.9
--3 Japan Simi Girls Dres 2000 25626.6
--2.
SELECT SUBSTR(country,1,20) country,
SUBSTR(prod,1,15) prod, year, sales
FROM sales_view
WHERE country='Japan'
and prod = 'Simi Girls Dress'
MODEL RETURN ALL ROWS
PARTITION BY (country)
DIMENSION BY (prod, year)
MEASURES (sale sales)
RULES (
sales['Simi Girls Dress', 2000] = 10 )
ORDER BY country, prod, year;
--结果：
--1 Japan Simi Girls Dres 1998 40854.9
--2 Japan Simi Girls Dres 1999 28503.9
--3 Japan Simi Girls Dres 2000 10
--3.
SELECT SUBSTR(country,1,20) country,
SUBSTR(prod,1,15) prod, year, sales
FROM sales_view
WHERE country='Japan'
and prod = 'Simi Girls Dress'
MODEL RETURN UPDATED ROWS
PARTITION BY (country)
DIMENSION BY (prod, year)
MEASURES (sale sales)
RULES (
sales['Simi Girls Dress', 2000] = 10 )
ORDER BY country, prod, year;
--结果：Japan Simi Girls Dres 2000 10
--结论：查询出来的结果，产品Simi Girls Dress2000年的销量变为了10.
-- 例子三
--1.
SELECT SUBSTR(country, 1, 20) country,
SUBSTR(prod, 1, 15) prod,
year,
sale
FROM sales_view
WHERE country = 'Japan'
and prod = 'Simi Girls Dress'
ORDER BY country, prod, year;
--查询结果：
--1 Japan Simi Girls Dres 1998 40854.9
--2 Japan Simi Girls Dres 1999 28503.9
--3 Japan Simi Girls Dres 2000 25626.6
--2.
SELECT SUBSTR(country,1,20) country,
SUBSTR(prod,1,15) prod, year, sales
FROM sales_view
WHERE country='Japan'
and prod = 'Simi Girls Dress'
MODEL RETURN ALL ROWS
PARTITION BY (country)
DIMENSION BY (prod, year)
MEASURES (sale sales)
RULES (
sales['Simi Girls Dress', 2005] = 20 )
ORDER BY country, prod, year;
--结果：
--1 Japan Simi Girls Dres 1998 40854.9
--2 Japan Simi Girls Dres 1999 28503.9
--3 Japan Simi Girls Dres 2000 25626.6
--4 Japan Simi Girls Dres 2005 20
--3.
SELECT SUBSTR(country,1,20) country,
SUBSTR(prod,1,15) prod, year, sales
FROM sales_view
WHERE country='Japan'
and prod = 'Simi Girls Dress'
MODEL RETURN UPDATED ROWS
PARTITION BY (country)
DIMENSION BY (prod, year)
MEASURES (sale sales)
RULES (
sales['Simi Girls Dress', 2005] = 20 )
ORDER BY country, prod, year;
--结果：Japan Simi Girls Dres 2005 20
--结论：新产生一条 产品Simi Girls Dress2005年的销量20.
-- 例子四
--1.
SELECT SUBSTR(country, 1, 20) country,
SUBSTR(prod, 1, 15) prod,
year,
sale
FROM sales_view
WHERE country = 'Japan'
and prod = 'Simi Girls Dress'
ORDER BY country, prod, year;
--查询结果：
--1 Japan Simi Girls Dres 1998 40854.9
--2 Japan Simi Girls Dres 1999 28503.9
--3 Japan Simi Girls Dres 2000 25626.6
--2.
SELECT SUBSTR(country,1,20) country,
SUBSTR(prod,1,15) prod, year, sales
FROM sales_view
WHERE country='Japan'
and prod = 'Simi Girls Dress'
MODEL RETURN ALL ROWS
PARTITION BY (country)
DIMENSION BY (prod, year)
MEASURES (sale sales)
RULES (
sales[prod='Simi Girls Dress', year >1998 ] = 20 )
ORDER BY country, prod, year;
--结果：
--1 Japan Simi Girls Dres 1998 40854.9
--2 Japan Simi Girls Dres 1999 20
--3 Japan Simi Girls Dres 2000 20
--3.
SELECT SUBSTR(country,1,20) country,
SUBSTR(prod,1,15) prod, year, sales
FROM sales_view
WHERE country='Japan'
and prod = 'Simi Girls Dress'
MODEL RETURN UPDATED ROWS
PARTITION BY (country)
DIMENSION BY (prod, year)
MEASURES (sale sales)
RULES (
sales[prod='Simi Girls Dress', year >1998 ] = 20 )
ORDER BY country, prod, year;
--结果：
--1 Japan Simi Girls Dres 1999 20
--2 Japan Simi Girls Dres 2000 20
--例五
SELECT SUBSTR(country,1,20) country,
SUBSTR(prod,1,15) prod, year, sales
FROM sales_view
WHERE country='Japan'
and prod = 'Simi Girls Dress'
MODEL RETURN UPDATED ROWS
PARTITION BY (country)
DIMENSION BY (prod, year)
MEASURES (sale sales)
RULES (
sales[prod='Simi Girls Dress', year =1998 ] = 10,
sales[prod='Simi Girls Dress', year >1998 ] = 20 )
ORDER BY country, prod, year;
--1 Japan Simi Girls Dres 1998 10
--2 Japan Simi Girls Dres 1999 20
--3 Japan Simi Girls Dres 2000 20
--例六
SELECT SUBSTR(country,1,20) country,
SUBSTR(prod,1,15) prod, year, sales
FROM sales_view
WHERE country='Japan'
MODEL RETURN UPDATED ROWS
PARTITION BY (country)
DIMENSION BY (prod, year)
MEASURES (sale sales)
RULES ( sales['Simi Girls Dress', 2005] =
100 + max(sales)['Simi Girls Dress', year BETWEEN 1998 AND 2002] )
ORDER BY country, prod, year
--例七
--The CV() function is a very powerful tool that makes rule creation highly productive.
--CV() is used on the right side of rules to copy the current value of a dimension specified on the left side.
--It is helpful wherever the left side specifications refer to multiple cells.
--In terms of relational database concepts, it acts like a join operation.
--CV() allows for very flexible expressions.
-- For instance, by subtracting from the CV(year) value you can refer to other rows in the data set.
--If you have the expression 'CV(year) -2' in a cell reference, you can access data from two years earlier.
--CV() functions are most commonly used as part of a cell reference,
--but they can also be used outside a cell reference as freestanding elements of an expression.
-- You want to update the sales values for Bounce in Italy for multiple years,
-- using a rule where each year's sales is the sum of Mouse Pad sales for that year,
-- plus 20% of the Y Box sales for that year. From your SQL*Plus session, execute the following script.:
--1.
SELECT SUBSTR(country, 1, 20) country,
SUBSTR(prod, 1, 15) prod,
year,
sale
FROM sales_view
WHERE country = 'Japan'
AND prod = 'Simi Girls Dress';
--2.
SELECT SUBSTR(country,1,20) country,
SUBSTR(prod,1,15) prod, year, sales
FROM sales_view
WHERE country='Japan'
MODEL RETURN UPDATED ROWS
PARTITION BY (country)
DIMENSION BY (prod, year)
MEASURES (sale sales)
RULES ( sales[prod, 2000] = sales[cv(),cv()] )
ORDER BY country, prod, year;
--例七 添加一个伪列
SELECT SUBSTR(country,1,20) country,
SUBSTR(prod,1,15) prod, year, sales,growth
FROM sales_view
WHERE country='Japan'
MODEL RETURN UPDATED ROWS
PARTITION BY (country)
DIMENSION BY (prod, year)
MEASURES (sale sales,0 growth)
RULES (growth['Simi Girls Dress',year between 1998 and 2000] =
(sales[cv(prod),cv(year)]- sales[cv(prod), cv(year)-1] ) / sales[cv(prod), cv(year) -1])
ORDER BY country, prod, year;
--例八 any关键字
SELECT SUBSTR(country,1,20) country,
SUBSTR(prod,1,15) prod, year, sales,growth
FROM sales_view
WHERE country='Japan'
MODEL RETURN UPDATED ROWS
PARTITION BY (country)
DIMENSION BY (prod, year)
MEASURES (sale sales,0 growth)
RULES (growth['Simi Girls Dress',any] =
(sales[cv(prod),cv(year)]- sales[cv(prod), cv(year)-1] ) / sales[cv(prod), cv(year) -1])
ORDER BY country, prod, year;
--例九 循环操作
SELECT SUBSTR(country,1,20) country,
SUBSTR(prod,1,15) prod, year, sales
FROM sales_view
WHERE country='Japan'
MODEL RETURN UPDATED ROWS
PARTITION BY (country)
DIMENSION BY (prod, year)
MEASURES (sale sales)
RULES (sales['Simi Girls Dress',for year in(1998,1999,2000)] = sales[cv(),cv()] )
ORDER BY country, prod, year;
SELECT SUBSTR(country,1,20) country,
SUBSTR(prod,1,15) prod, year, sales
FROM sales_view
WHERE country='Japan'
MODEL RETURN UPDATED ROWS
PARTITION BY (country)
DIMENSION BY (prod, year)
MEASURES (sale sales)
RULES (sales['Simi Girls Dress',FOR year FROM 1998 TO 2012 INCREMENT 1] = nvl(sales[cv(),cv()],0) )
ORDER BY country, prod, year;
--例十 忽略空值
--0 for numeric data
--Empty string for character/string data
--01-JAN-2001 for date type data
--NULL for all other data types
SELECT SUBSTR(country,1,20) country,
SUBSTR(prod,1,15) prod, year, sales
FROM sales_view
WHERE country='Japan'
MODEL ignore nav RETURN UPDATED ROWS
PARTITION BY (country)
DIMENSION BY (prod, year)
MEASURES (sale sales)
RULES (sales['Simi Girls Dress',FOR year FROM 1998 TO 2012 INCREMENT 1] = sales[cv(),cv()] )
ORDER BY country, prod, year;
--例十 参考模型
CREATE TABLE dollar_conv(country VARCHAR2(30), exchange_rate NUMBER);
SELECT SUBSTR(country,1,20) country,
year,
sales,
localsales,
dollarsales
FROM sales_view
WHERE country IN ( 'Canada', 'Brazil')
GROUP BY country, year
MODEL RETURN UPDATED ROWS
REFERENCE conv_refmodel ON (SELECT country, exchange_rate AS er FROM dollar_conv)
DIMENSION BY (country)
MEASURES (er) IGNORE NAV
MAIN main_model
DIMENSION BY (country, year)
MEASURES (SUM(sale) sales, 0 localsales, 0 dollarsales)
IGNORE NAV
RULES(
/* assuming that sales in Canada grow by 22% */
localsales['Canada', 2001] = sales[cv(country), 2000] * 1.22,
dollarsales['Canada', 2001] = sales[cv(country), 2000] * 1.22 * conv_refmodel.er['Canada'],
/* assuming that economy in Brazil grows by 34% */
localsales['Brazil', 2001] = sales[cv(country), 2000] * 1.34,
dollarsales['Brazil', 2001] = sales['Brazil', 2000] * 1.34 * er['Brazil']);
--例十一 2个参考模型
CREATE TABLE growth_rate(country VARCHAR2(30), year NUMBER,growth_rate NUMBER);
INSERT INTO growth_rate VALUES('Brazil', 2000, 2.5);
INSERT INTO growth_rate VALUES('Brazil', 1999, 5);
INSERT INTO growth_rate VALUES('Canada', 1999, 3);
INSERT INTO growth_rate VALUES('Canada', 2000, 2.5);
commit;
SELECT SUBSTR(country,1,20) country, year, localsales, dollarsales
FROM sales_view
WHERE country IN ('Canada','Brazil')
GROUP BY country, year
MODEL RETURN UPDATED ROWS
REFERENCE conv_refmodel ON (SELECT country, exchange_rate FROM dollar_conv)
DIMENSION BY (country c)
MEASURES (exchange_rate er) IGNORE NAV
REFERENCE growth_refmodel ON (SELECT country, year, growth_rate FROM growth_rate)
DIMENSION BY (country c, year y)
MEASURES (growth_rate gr) IGNORE NAV
MAIN main_model DIMENSION BY (country, year)
MEASURES (SUM(sale) sales, 0 localsales, 0 dollarsales)
IGNORE NAV
RULES ( localsales[FOR country IN ('Brazil','Canada'),2000]=sales[cv(country), 2000]* (100+gr[cv(country), cv(year)])/100 ,
dollarsales[FOR country IN ('Brazil','Canada'),2000] = sales[cv(country), 2000] * (100 + gr[cv(country), cv(year)])/100 * er[cv(country)])
--例十一 重复几次 inteate
CREATE TABLE ledger (account VARCHAR2(20), balance NUMBER(10,2) );
INSERT INTO ledger VALUES ('Salary', 100000);
INSERT INTO ledger VALUES ('Capital_gains', 15000);
INSERT INTO ledger VALUES ('Net', 0);
INSERT INTO ledger VALUES ('Tax', 0);
INSERT INTO ledger VALUES ('Interest', 0);
commit;
SELECT b, account
FROM ledger
MODEL IGNORE NAV
DIMENSION BY (account)
MEASURES (balance b)
RULES ITERATE (2) ( b['Net'] = b['Salary'] - b['Interest'] - b['Tax'],
b['Tax'] = (b['Salary'] - b['Interest']) * 0.38 +
b['Capital_gains'] *0.28,
b['Interest'] = b['Net'] * 0.30 );
--例十二 排序
SELECT year, sales
FROM sales_view
WHERE country='Japan' AND prod='Shorts'
MODEL ignore nav
DIMENSION BY (year )
MEASURES (sale sales)
RULES SEQUENTIAL ORDER (
sales[for year in(2000,1998,1999)] ORDER BY year DESC = sales[(CV(year)) - 1 ]
)
ORDER BY year;
SELECT year, sales
FROM sales_view
WHERE country='Italy'
AND prod='Bounce'
MODEL
DIMENSION BY (year )
MEASURES ( sale sales)
RULES SEQUENTIAL ORDER (sales[ANY] ORDER BY year DESC = sales[cv(year)-1] )
ORDER BY year;
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7204674/viewspace-630484/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/7204674/viewspace-630484/
扫一扫
865
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
