Looping in Model:
select product, country, year, week, inventory, sale, receipts
from sales_fact
where country in ('Australia') and product ='Xtend Memory'
model return updated rows
partition by (product, country)
dimension by (year, week)
measures ( 0 inventory , sale, receipts)
rules automatic order(
inventory [2002, week ] = nvl(inventory [cv(year), cv(week)-1 ] ,0) - sale[cv(year), cv(week) ] + + receipts [cv(year), cv(week) ],
sale [2002, for week from 1 to 53 increment 1] = 0,
receipts [ 2002,for week from 1 to 53 increment 1] =0
)
order by product, country,year, week;
Aggregations in Model:
select product, country, year, week, inventory, avg_inventory, max_sale
from sales_fact
where country in ('Australia') and product ='Xtend Memory'
model return updated rows
partition by (product,country)
dimension by (year, week)
measures ( 0 inventory ,0 avg_inventory , 0 max_sale, sale, receipts)
rules automatic order(
inventory [year, week ] = nvl(inventory [cv(year), cv(week)-1 ] ,0) - sale[cv(year), cv(week) ] + + receipts [cv(year), cv(week) ],
avg_inventory [ year,ANY ] = avg (inventory) [ cv(year), week ],
max_Sale [ year, ANY ] = max( sale) [ cv(year), week ]
)
order by product, country,year, week;
Please note the difference between week and cv(week) in the cell reference. In avg(inventory)[cv(year), week], "week" refers to all weeks of cv(year).
Using Iteration to pivot:
select year, week,sale, sale_list
from sales_fact
where country in ('Australia') and product ='Xtend Memory'
model return updated rows
partition by (product, country)
dimension by (year, week)
measures ( cast(' ' as varchar2(50) ) sale_list, sale)
rules iterate (5) (
sale_list [ year, week ] order by year, week =
sale [cv(year), CV(week)-ITERATION_NUMBER +2 ] ||
case when iteration_number=0 then '' else ', ' end ||
sale_list [cv(year) ,cv(week)]
)
order by year, week;
Iteration_number starts from 0 to 4. Rule for sale_list is an ordered rule. It accesses cells in the order prescribed by ORDER BY and applies the right side computation.
Presentv and nulls:
select year, week,sale, sale_list
from sales_fact
where country in ('Australia') and product ='Xtend Memory'
model return updated rows
partition by (product, country)
dimension by (year, week)
measures ( cast(' ' as varchar2(120) ) sale_list, sale, 0 tmp)
rules iterate (5) (
sale_list [ year, week ] order by year, week =
presentv( sale [cv(year), CV(week)-iteration_number + 2 ],
sale [cv(year), CV(week)-iteration_number + 2 ] || case when iteration_number=0 then '' else ', ' end || sale_list [cv(year) ,cv(week)],
sale_list [cv(year) ,cv(week)])
)
order by year, week;
This query removes redendant commas.
Reference models - lookup table:
select year, week,sale, prod_list_price
from sales_fact
where country in ('Australia') and product ='Xtend Memory'
model return updated rows
REFERENCE ref_prod on (select prod_name, max(prod_list_price) prod_list_price from products group by prod_name)
dimension by (prod_name)
measures (prod_list_price)
MAIN main_section
partition by (product,country)
dimension by (year, week)
measures( sale, receipts, 0 prod_list_price )
rules (
prod_list_price[year,week] order by year, week = ref_prod.prod_list_price [ cv(product) ]
)
order by year, week;
Oracle supports multiple reference models.
This example is based on the post: http://www.orafaq.com/node/69
DEPARTMENT VARCHAR2 (30), commit;
CREATE TABLE FINANCIAL_STATEMENT (
REP_LINE_ID NUMBER,
REP_LINE_ITEM VARCHAR2 (30),
DEPARTMENT VARCHAR2 (30),
ACTUAL NUMBER (10,2),
BUDGET NUMBER (10,2),
VARIANCE NUMBER (10,2) );
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE )
VALUES ( 1, 'Net Sales', 'Retail', 5000, 5500, NULL);
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE )
VALUES ( 2, 'Gross Profit', 'Retail', 4000, 4750, NULL);
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE )
VALUES ( 3, 'Other Income', 'Retail', 900, 0, NULL);
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE )
VALUES ( 4, 'Total Net Income', 'Retail', NULL, NULL, NULL);
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE )
VALUES ( 5, 'Wage Costs', 'Retail', 3500, 3400, NULL);
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE )
VALUES ( 6, 'Rent Costs', 'Retail', 750, 750, NULL);
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE )
VALUES ( 7, 'Other Costs', 'Retail', 200, 0, NULL);
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE )
VALUES ( 8, 'Total Costs', 'Retail', NULL, NULL, NULL);
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE )
VALUES ( 9, 'Net Contribution', 'Retail', NULL, NULL, NULL);
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE )
VALUES ( 10, 'Gross Profit % of Net Sales', 'Retail', NULL, NULL, NULL);
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE )
VALUES ( 11, 'Wages % of Net Sales', 'Retail', NULL, NULL, NULL);
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE )
VALUES ( 12, 'Total Costs % of Net Sales', 'Retail', NULL, NULL, NULL);
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE )
VALUES ( 13, 'Net Contrib. % of Net Sales', 'Retail', NULL, NULL, NULL);
commit;
select rep_line_item,department, budget, actual actual, variance
from financial_statement
where department='Retail'
model
return all rows
partition by (department)
dimension by(rep_line_item)
measures (actual actual, budget budget, variance variance)
rules upsert(
budget['Total Net Income'] = budget['Gross Profit'] + budget['Other Income'],
actual['Total Net Income'] = actual['Gross Profit'] + actual['Other Income'],
budget['Total Costs'] = budget['Wage Costs'] + budget['Rent Costs'] + budget['Other Costs'],
actual['Total Costs'] = actual['Wage Costs'] + actual['Rent Costs'] + actual['Other Costs'],
budget['Net Contribution'] = budget['Total Net Income'] - budget['Total Costs'],
actual['Net Contribution'] = actual['Total Net Income'] - actual['Total Costs'],
budget['Gross Profit % of Net Sales'] = Round(budget['Gross Profit']/budget['Net Sales'], 2),
actual['Gross Profit % of Net Sales'] = Round(actual['Gross Profit']/actual['Net Sales'],2),
budget['Wages % of Net Sales'] = Round(budget['Wage Costs']/budget['Net Sales'], 2),
actual['Wages % of Net Sales'] = Round(actual['Wage Costs']/actual['Net Sales'],2),
budget['Total Costs % of Net Sales'] = Round(budget['Total Costs']/budget['Net Sales'], 2),
actual['Total Costs % of Net Sales'] = Round(actual['Total Costs']/actual['Net Sales'],2),
budget['Net Contrib. % of Net Sales'] = Round(budget['Net Contribution']/budget['Net Sales'], 2),
actual['Net Contrib. % of Net Sales'] = Round(actual['Net Contribution']/actual['Net Sales'],2),
variance[ANY] = presentnnv(actual[cv(rep_line_item)],actual[cv(rep_line_item)],0) - presentnnv(budget[cv(rep_line_item)],budget[ cv(rep_line_item)],0)
);
select product, country, year, week, inventory, sale, receipts
from sales_fact
where country in ('Australia') and product ='Xtend Memory'
model return updated rows
partition by (product, country)
dimension by (year, week)
measures ( 0 inventory , sale, receipts)
rules automatic order(
inventory [2002, week ] = nvl(inventory [cv(year), cv(week)-1 ] ,0) - sale[cv(year), cv(week) ] + + receipts [cv(year), cv(week) ],
sale [2002, for week from 1 to 53 increment 1] = 0,
receipts [ 2002,for week from 1 to 53 increment 1] =0
)
order by product, country,year, week;
Aggregations in Model:
select product, country, year, week, inventory, avg_inventory, max_sale
from sales_fact
where country in ('Australia') and product ='Xtend Memory'
model return updated rows
partition by (product,country)
dimension by (year, week)
measures ( 0 inventory ,0 avg_inventory , 0 max_sale, sale, receipts)
rules automatic order(
inventory [year, week ] = nvl(inventory [cv(year), cv(week)-1 ] ,0) - sale[cv(year), cv(week) ] + + receipts [cv(year), cv(week) ],
avg_inventory [ year,ANY ] = avg (inventory) [ cv(year), week ],
max_Sale [ year, ANY ] = max( sale) [ cv(year), week ]
)
order by product, country,year, week;
Please note the difference between week and cv(week) in the cell reference. In avg(inventory)[cv(year), week], "week" refers to all weeks of cv(year).
Using Iteration to pivot:
select year, week,sale, sale_list
from sales_fact
where country in ('Australia') and product ='Xtend Memory'
model return updated rows
partition by (product, country)
dimension by (year, week)
measures ( cast(' ' as varchar2(50) ) sale_list, sale)
rules iterate (5) (
sale_list [ year, week ] order by year, week =
sale [cv(year), CV(week)-ITERATION_NUMBER +2 ] ||
case when iteration_number=0 then '' else ', ' end ||
sale_list [cv(year) ,cv(week)]
)
order by year, week;
Iteration_number starts from 0 to 4. Rule for sale_list is an ordered rule. It accesses cells in the order prescribed by ORDER BY and applies the right side computation.
Presentv and nulls:
select year, week,sale, sale_list
from sales_fact
where country in ('Australia') and product ='Xtend Memory'
model return updated rows
partition by (product, country)
dimension by (year, week)
measures ( cast(' ' as varchar2(120) ) sale_list, sale, 0 tmp)
rules iterate (5) (
sale_list [ year, week ] order by year, week =
presentv( sale [cv(year), CV(week)-iteration_number + 2 ],
sale [cv(year), CV(week)-iteration_number + 2 ] || case when iteration_number=0 then '' else ', ' end || sale_list [cv(year) ,cv(week)],
sale_list [cv(year) ,cv(week)])
)
order by year, week;
This query removes redendant commas.
Reference models - lookup table:
select year, week,sale, prod_list_price
from sales_fact
where country in ('Australia') and product ='Xtend Memory'
model return updated rows
REFERENCE ref_prod on (select prod_name, max(prod_list_price) prod_list_price from products group by prod_name)
dimension by (prod_name)
measures (prod_list_price)
MAIN main_section
partition by (product,country)
dimension by (year, week)
measures( sale, receipts, 0 prod_list_price )
rules (
prod_list_price[year,week] order by year, week = ref_prod.prod_list_price [ cv(product) ]
)
order by year, week;
Oracle supports multiple reference models.
This example is based on the post: http://www.orafaq.com/node/69
DEPARTMENT VARCHAR2 (30), commit;
CREATE TABLE FINANCIAL_STATEMENT (
REP_LINE_ID NUMBER,
REP_LINE_ITEM VARCHAR2 (30),
DEPARTMENT VARCHAR2 (30),
ACTUAL NUMBER (10,2),
BUDGET NUMBER (10,2),
VARIANCE NUMBER (10,2) );
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE )
VALUES ( 1, 'Net Sales', 'Retail', 5000, 5500, NULL);
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE )
VALUES ( 2, 'Gross Profit', 'Retail', 4000, 4750, NULL);
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE )
VALUES ( 3, 'Other Income', 'Retail', 900, 0, NULL);
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE )
VALUES ( 4, 'Total Net Income', 'Retail', NULL, NULL, NULL);
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE )
VALUES ( 5, 'Wage Costs', 'Retail', 3500, 3400, NULL);
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE )
VALUES ( 6, 'Rent Costs', 'Retail', 750, 750, NULL);
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE )
VALUES ( 7, 'Other Costs', 'Retail', 200, 0, NULL);
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE )
VALUES ( 8, 'Total Costs', 'Retail', NULL, NULL, NULL);
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE )
VALUES ( 9, 'Net Contribution', 'Retail', NULL, NULL, NULL);
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE )
VALUES ( 10, 'Gross Profit % of Net Sales', 'Retail', NULL, NULL, NULL);
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE )
VALUES ( 11, 'Wages % of Net Sales', 'Retail', NULL, NULL, NULL);
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE )
VALUES ( 12, 'Total Costs % of Net Sales', 'Retail', NULL, NULL, NULL);
INSERT INTO FINANCIAL_STATEMENT ( REP_LINE_ID, REP_LINE_ITEM, DEPARTMENT, ACTUAL, BUDGET, VARIANCE )
VALUES ( 13, 'Net Contrib. % of Net Sales', 'Retail', NULL, NULL, NULL);
commit;
select rep_line_item,department, budget, actual actual, variance
from financial_statement
where department='Retail'
model
return all rows
partition by (department)
dimension by(rep_line_item)
measures (actual actual, budget budget, variance variance)
rules upsert(
budget['Total Net Income'] = budget['Gross Profit'] + budget['Other Income'],
actual['Total Net Income'] = actual['Gross Profit'] + actual['Other Income'],
budget['Total Costs'] = budget['Wage Costs'] + budget['Rent Costs'] + budget['Other Costs'],
actual['Total Costs'] = actual['Wage Costs'] + actual['Rent Costs'] + actual['Other Costs'],
budget['Net Contribution'] = budget['Total Net Income'] - budget['Total Costs'],
actual['Net Contribution'] = actual['Total Net Income'] - actual['Total Costs'],
budget['Gross Profit % of Net Sales'] = Round(budget['Gross Profit']/budget['Net Sales'], 2),
actual['Gross Profit % of Net Sales'] = Round(actual['Gross Profit']/actual['Net Sales'],2),
budget['Wages % of Net Sales'] = Round(budget['Wage Costs']/budget['Net Sales'], 2),
actual['Wages % of Net Sales'] = Round(actual['Wage Costs']/actual['Net Sales'],2),
budget['Total Costs % of Net Sales'] = Round(budget['Total Costs']/budget['Net Sales'], 2),
actual['Total Costs % of Net Sales'] = Round(actual['Total Costs']/actual['Net Sales'],2),
budget['Net Contrib. % of Net Sales'] = Round(budget['Net Contribution']/budget['Net Sales'], 2),
actual['Net Contrib. % of Net Sales'] = Round(actual['Net Contribution']/actual['Net Sales'],2),
variance[ANY] = presentnnv(actual[cv(rep_line_item)],actual[cv(rep_line_item)],0) - presentnnv(budget[cv(rep_line_item)],budget[ cv(rep_line_item)],0)
);
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/638844/viewspace-1061262/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/638844/viewspace-1061262/