| Team Fly | |
The model clause has many variations and allows for very powerful calculations. Let's point out some of the characteristics and/or features you should be aware of. Supported functionalities include the following:
Looping (for example, FOR loops)
Recursive calculations
Regression calculations
Nested cell references
Dimension wildcards and ranges
The model clause does not update any base table, although in theory you could create a table or materialized view from the results of the query using the model clause.
Restrictions include the following:
The rules clause cannot include any analytic SQL or windowing functions.
A maximum of 20,000 rules may be specified. This may seem like plenty, but a FOR loop is expanded into many single-cell rules at execution time.
Once all the database structures have been put in place and data has been loaded, the users will want to analyze it. Knowing what functions are available is important, and so is their use as well, at least to some extent. So, in this project we will walk through a more complex analytical example that includes using the lag function and creating a SQL model.
1. Create a view of the SALES table using the following listing. (The SALES table should have been created during your Oracle installation process.) This view will calculate the percentage change (called percent_chng) of quantity_sold from one year to the next using the lag function, summarized by prod_category, channel_desc, and calendar_year.
create or replace view sales_trends
as
select p.prod_category, c.channel_desc, t.calendar_year year,
sum(s.quantity_sold) quantity_sold,
| Team Fly | |