Les10_ModelingData

Modeling Data Using SQLObjectivesAfter completing this lesson, you should be able to: •Perform interrow calculations with the SQL MODEL clause •Use symbolic and positional references to identify cells •Use UPDATE, UPSERT, and UPSERT ALL options •Describe FOR loop enhancements•Write SQL statements with the MODEL clause by using incremental loops•Include the analytic functions within the MODEL clauseLesson Agenda•Using the MODEL clause and cell references•Using the CV function, the FOR construct, and distinguishing missing cells from NULL s•Using the UPDATE, UPSERT, and UPSERT ALL options •Reference models and cyclic rules in modelsBenefits of IntegratingInterrow Calculations in SQL•Offer analytical capabilities•Use the MODEL clause to integrate interrow functionality into the Oracle server•Enhance SQL by directly providing spreadsheet-like computationsWhy Use SQL Modeling?•The MODEL clause brings a new level of power and flexibility to SQL calculations.•Use the MODEL clause to create a multidimensional array from a query results set and then apply formulas (called “rules”) to this array to calculate new values.•The rules can range from basic arithmetic to simultaneous equations using recursion.•For some applications, the MODEL clause can replacePC-based spreadsheets.•Models in SQL leverage Oracle Database's strengths in scalability, manageability, collaboration, and security.Partitions, Measures, and DimensionsLearning the ConceptsLearning the Concepts: Example1. Query results are used asinput to the MODEL clause 2. Multi-dimensionalarray is defined 3. Rule 1is applied4. Rule 2is applied 6. MODEL clause resultsare converted back to rows 5. Rule 3is appliedReviewing the Sample DataMODEL Syntax: ExampleImportant InstructionsCell References•Must qualify all dimensions in the partition•Consist of two types:–Positional:A dimension is implied by its position in the DIMENSION BY clause.–Symbolic:A dimension is qualified using a Boolean condition.Positional and Symbolic Cell References•Positional:–Dimension implied by its position in the DIMENSION BYclause–Allows inserts and updates•Symbolic:–Allows updates onlyUsing Cell Reference: ExampleRange References•Are references to a set of cells on the right of a rule and apply an aggregate operation to that set•Loop over existing rowsThe Complete Query: ExampleLesson Agenda•Using the MODEL clause and cell references•Using the CV function, the FOR construct, and distinguishing missing cells from NULL s•Using the UPDATE, UPSERT, and UPSERT ALL options •Reference models and cyclic rules in modelsThe CV()Function•Returns the current value (CV) of a dimension from the left of an assignment•Performs relative indexing with respect to the leftThe CV()Function: ExampleUsing the FOR Construct with IN List OperatorUsing the FOR Construct with Incremental ValuesUsing the FOR Construct with a Subquery •You can use a subquery to loop over a dimension with the FOR construct.•Follow these guidelines:–The subquery cannot be correlated.–The subquery cannot be a query defined in the WITH clause.•FOR loops may be unfolded at query plan creation or query execution.Using Analytic Functions in the SQL MODEL ClauseDistinguishing Missing Cells from NULL s•The two types of undetermined values are:–NULL in an existing cell–Nondetermined value from a missing cell•The MODEL clause provides:– A default treatment for nulls and missing cells that is consistent with the ANSI SQL standard–Options to treat nulls and missing cells in other useful ways according to business logic•The positional references[NULL, NULL]qualify a cell whose two dimension values are NULL.•The missing values are treated as:–NULL s in direct cell references–Nonexisting values in aggregatesDistinguishing Missing Cells from NULL s: Using Conditions, Functions, and ClausesUsing the IS PRESENT Condition: ExampleLesson Agenda•Using the MODEL clause and cell references•The CV function, the FOR construct, and distinguishing missing cells from NULL s•Using the UPDATE, UPSERT, and UPSERT ALL options •Reference models and cyclic rules in modelsUsing the UPDATE, UPSERT, andUPSERT ALL Options•Three choices for RULES behavior:–UPDATE: Updates existing cells–UPSERT: Updates existing cells and inserts new ones (default)–UPSERT ALL: Allows a broader set of ruled notation •Example of UPDATE:Using the Per-Rule Basis Option. . .Using the UPSERT and UPSERT ALL Options •Positional upsert:•Symbolic upsert:–Use the UPSERT option to update a cell that exists or insert a new cell if it does not exist:Order of Evaluation of RulesOrder of Evaluation of RulesNested Cell References•Only one level of nesting is supported.•Nested references can appear on the right of a rule for sequential and automatic order models.•In automatic order models, nested references can appear on the left side of rules with some restrictions.•In sequential order models, nested references can appear on the left side of rules.•Nested cell references must be single cell references; aggregates are not supported.Lesson Agenda•Using the MODEL clause and cell references•The CV function, the FOR construct, and distinguishing missing cells from NULL s•Using the UPDATE, UPSERT, and UPSERT ALL options •Reference models and cyclic rules in modelsReference Models•You can create one or more read-only multidimensional arrays, called “reference models,”and reference them in the MODEL clause to act as look-up tables for the main model. •A reference model:–Is defined over a query block and has DIMENSION BY and MEASURES clauses to indicate its dimensions and measures,respectively–Is similar to VARRAY()and other lookup functions in Excel–Is useful for looking up factors such as currency conversion and tax rates–Cannot be updated (read-only)–Have no rulesReference Models•Created by the REFERENCE subclause of the MODEL clause •Can be used only on the right side of formulasReference Models: ExampleCyclic Rules in Models•Cyclic dependency spanning two rules:•Selfcyclic rule:Cyclic References and Iterations•The ITERATE subclause is used to handle computations that do not have a convergence point (# of iterations to perform).•Iterative evaluation stops after finishing the specified number of iterations or when the termination condition evaluates to TRUE, whichever comes first.•You can access the current iteration number by using the ITERATION_NUMBER system variable:–ITERATION_NUMBER starts at value 0 and is incremented after each iteration.–By using PREVIOUS and ITERATION_NUMBER, you canconstruct complex termination conditions.Cycles and Simultaneous EquationsSummaryIn this lesson, you should have learned how to:•Perform interrow calculations with the SQL MODEL clause •Use symbolic and positional references to identify cells •Write SQL statements with the MODEL clause usingincremental loops•Use UPDATE, UPSERT, and UPSERT ALL options •Describe the FOR loop enhancements•Include the analytic functions within the MODEL clausePractice 4: OverviewThis practice covers the following topics:•Using the MODEL clause to perform interrow calculations:–Identifying positional cell references–Using the CV()function–Handling NULL values。