Transcription
SQL for Analysis, Reporting and ModelingSQL - A Flexible and Comprehensive Framework for In-Database AnalyticsORACLE WHITE PAPER NOVEMBER 2016
ContentsData Analysis with SQL1SQL – A Flexible and Comprehensive Analytical Framework2In-database analytical SQL with Oracle Database 12c Release 23Processing concepts behind analytical SQL3Enhanced reporting6Spreadsheet-like data modeling and what-if analysis9Conclusion11Further Reading11DisclaimerThe following is intended to outline our general product direction. It is intended for information purposes only, andmay not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality,and should not be relied upon in making purchasing decisions. The development, release, and timing of anyfeatures or functionality described for Oracle’s products remains at the sole discretion of Oracle.SQL FOR ANALYSIS, REPORTING AND MODELING
Data Analysis with SQLToday information management systems along with operational applications need to support a widevariety of business requirements that typically involve some degree of analytical processing. Theserequirements can range from data enrichment and transformation during ETL workflows, creating timebased calculations like moving average and moving totals for sales reports, performing real-timepattern searches within logs files to building what-if data models during budgeting and planningexercises. Developers, business users and project teams can choose from a wide range of languagesto create solutions to meet these requirements.Over time many companies have found that the use so many different programming languages to drivetheir data systems creates five key problems:1.2.3.4.5.Decreases the ability to rapidly innovateCreates data silosResults in application-level performance bottlenecks that are hard to trace and rectifyDrives up costs by complicating the deployment and management processesIncreases the level of investment in trainingDevelopment teams need to quickly deliver new and innovative applications that provide significantcompetitive advantage and drive additional revenue streams. Anything that stifles innovation needs tobe urgently reviewed and resolved. The challenge facing many organizations is to find the rightplatform and language to securely and efficiently manage the data and analytical requirements while atthe same time supporting the broadest range of tools and applications to maximize the investment inexisting skills.IT and project managers need an agile platform to underpin their projects and applications so thatdevelopers can quickly and effectively respond to ever-changing business requirements withoutincurring the issues listed above.1 SQL FOR ANALYSIS, REPORTING AND MODELING
SQL – A Flexible and Comprehensive Analytical FrameworkThe process of analyzing data has seen many changes and significant technological advances over the last fortyyears. However, there has been one language, one capability that has endured and evolved: the Structured QueryLanguage or SQL. Many other languages and technologies have come and gone but SQL has been a constant. Infact, SQL has not only been a constant, but it has also improved significantly over time.SQL is now the default language for data analytics because it provides a mature and comprehensive framework fordata access and it supports a broad range of sophisticated analytical features. The key benefits for IT and businessteams provided by Oracle’s in-database analytical SQL features and functions are:Enhanced developer productivityUsing the latest built-in analytical SQL capabilities, developers can simplify their application code by replacingcomplex analytical processing – written using many different languages - with purpose-built analytical SQL that ismuch clearer and more concise. Tasks that in the past required the use of procedural languages or multiple SQLstatements can now be expressed using single, comprehensive SQL statements. This simplified SQL (analytic SQL)is quicker to formulate, maintain and deploy compared to older approaches, resulting in greater developerproductivity.Improved ManageabilityWhen computations are centralized close to the data then the inconsistency, lack of timeliness and poor security ofcalculations scattered across multiple specialized processing platforms completely disappears. The ability to accessa consolidated view of all your data is simplified when applications share a common relational environment ratherthan a mix of calculation engines with incompatible data structures and languages.Oracle’s in-database approach to analytics allows developers to efficiently layer their analysis using SQL because itcan support a very broad range of business requirements.Minimized Learning EffortThe amount of effort required to understand analytic SQL is minimized through the use of careful syntax design.Syntax typically leveraged existing SQL constructs, such as the aggregate functions SUM and AVG, and extendsthem using well-understood keywords such as OVER, PARTITION BY, ORDER BY, RANGE INTERVAL etc.Most developers and business users with a reasonable level of proficiency with SQL and can quickly adopt andintegrate sophisticated analytical features, such as pareto-distributions, pattern matching, cube and rollupaggregations into their applications and reports.The amount of time required for enhancements, maintenance and upgrades is minimized: more people will be ableto review and enhance the existing SQL code rather than having to rely on a few key people with specializedprogramming skills.ANSI SQL complianceMost of Oracle’s analytical SQL is part of the ANSI SQL standard; or in the process of becoming adopted in newerversions. This ensures broad support for these features and rapid adoption of newly introduced functionality acrossapplications and tools – both from Oracle’s partner network and other independent software vendors.Oracle is continuously working with its many partners to assist them in exploiting the expanding library of analyticfunctions. Already many independent software vendors have integrated support for the new Database 12c indatabase analytic functions into their products.2 SQL FOR ANALYSIS, REPORTING AND MODELING
5. Improved performanceOracle’s in-database analytical functions and features enable significantly better query performance. Not only does itremove the need for specialized data-processing silos but also the internal processing of these purpose-builtfunctions is fully optimized. Using SQL unlocks the full potential of the Oracle database - such as parallel execution– to provide enterprise level scalability unmatched by external specialized processing engines.SummaryThis section has outlined how Oracle’s in-database analytic SQL features provide IT, application development teamsand business users with a robust and agile analytical language that enhances both query performance andproductivity while providing investment protection by building on existing standards-based skills. For a more detailedanalysis of the benefits of SQL as an analysis language please refer to the following whitepaper: SQL – the naturallanguage for analysisThe rest of this paper will outline the key SQL-based features for analytical processing within Oracle Database 12cRelease 2 1.In-database analytical SQL with Oracle Database 12c Release 2This section outlines the high level processing concepts behind Oracle’s in-database analytic SQL.Processing concepts behind analytical SQLOracle’s in-database analytical SQL – first introduced in Oracle Database 8i Release 1- has introduced several newelements to SQL processing. These elements build on existing SQL features to provide developers and businessusers with a framework that is both flexible and powerful in terms of its ability to support sophisticated calculations.There are four essential concepts used in the processing of Oracle’s analytic SQL: Processing order Result set partitions Calculation windows Current RowThis four-step process is internally optimized and completely transparent but it does provide a high degree offlexibility in terms of being able to layer analytical features to create the desired result set without having to resort tolong and complicated SQL statements. The following sections will explore these four concepts in more detail.1. Processing orderQuery processing using analytic SQL takes place in three stages:Stage 1: All joins, WHERE, GROUP BY and HAVING clauses2 are performed. Where customers are usingExadata storage servers the initial join and filtering operations for the query will be managed by thestorage cells. This step can result in a significant reduction in the volume of data passed to the analyticfunction, which helps improve performance.Stage 2: The result set is made available to the analytic function, and all the calculations are applied.1 Oracle Database 12c Release 2 (12.2), the latest generation of the world’s most popular database, is now available in the Oracle Cloud2 Please note that this applies mostly to a single query block3 SQL FOR ANALYSIS, REPORTING AND MODELING
Stage 3: If the query has an ORDER BY clause then this is processed to allow for precise control of thefinal output.2. Partitions – organizing your data setsAnalytic SQL allows users to divide query result sets into ordered groups of rows called “partitions”3. Any aggregatedresults such as SUM's, AVG's etc. are available to the analytical functions. Partitions can be based upon anycolumn(s) or expression. A query result set may have just one partition holding all the rows, a few large partitions, ormany small partitions with each holding just a few rows.3. Calculation windowsWithin each partition, a sliding window of data can be defined. The window determines the range of rows used toperform the calculations for the "current row" (defined in the next section). Window sizes can be based on either aphysical number of rows or a logical interval such as time.The window has a starting row and an ending row. Depending on its definition, the window may move at one or bothends.For instance, a window defined for a cumulative sum function would have its starting row fixed at the first row of itswindow, and its ending row would slide from the starting point all the way to the last row of the window.SELECTQtrs, Months, Channels, Revenue, SUM(Revenue) OVER (PARTITION BY Qtrs) AS Qtr Sales, SUM(Revenue) OVER () AS Total SalesFROM sales tableIn contrast, a window defined for a moving average would have both its starting and end points slide so that theymaintained a constant physical or logical range. The example below demonstrates the use of physical window tocreate a 6-month moving average:SELECTcalendar month desc as "Month", amount sold as "Sales"AVG(amount sold) OVER(ORDER BY calendar month desc rows between 5 precedingand current row) as "6M Avg Sales"FROM . . .3 Note that the term "partitions" used within the context of analytic functions is unrelated to Oracle Partitioning.4 SQL FOR ANALYSIS, REPORTING AND MODELING
FIGURE 2 – USING WINDOW FUNCTION TO CREATE DIFFERENT LEVELS OF AGGREGATION BASED ON TIME-BASED QUARTERSAlternatively, if the data set contains a date column then it is possible to use logical windows by taking advantage ofOracle’s built-in time awareness. In this case we can use the “range interval '5' month preceding” syntax to createthe 6-month moving average:SELECTcalendar month desc as "Month", amount sold as "Sales", AVG(amount sold) OVER(ORDER BY calendar month desc range interval '5' monthpreceding) as "6M Avg Sales"FROM . . .The output from these queries is shown here:FIGURE 3 – AN EXAMPLE SHOWING THE CONCEPT OF ROLLING WINDOWS TO COMPUTE A SIX MONTH MOVING AVERAGE5 SQL FOR ANALYSIS, REPORTING AND MODELING
The concept of a window is very powerful and provides a lot of flexibility in terms of being able to interact with thedata. A window can be set as large as all the rows in a partition. At the other extreme it could be just a single row.Users may specify a window containing a constant number of rows, or a window containing all rows where a columnvalue is in a specified numeric range. Windows may also be defined to hold all rows where a date value falls within acertain time period, such as the prior month.4. Current RowEach calculation performed with an analytic function is based on a current row within a window. The current rowserves as the reference point determining the start and end of the window. In the example below the calculation of arunning total would be the result of the current row plus the values from the preceding two rows. At the end of thewindow the running total will be reset. The example shown below creates running totals within a result set showingthe total sales for each channel within a product category within year:SELECTcalendar year, prod category desc, channel desc, country name, sales, units, SUM(sales) OVER (PARTITION BY calendar year, prod category desc,channel desc order by country name) sales tot cat by channelFROM . . .Enhanced reportingThe following topics provide information about analytical SQL features and techniques that are linked to enhancedreporting. Although these topics are presented in terms of data warehousing, they are applicable to any activityneeding analysis and reporting: Windowing, lag-lead and reporting aggregate functions Pivoting operations Data densification for reporting and time series calculationsWindowing, lag-lead and reporting aggregate functionsWindowing functions are used to compute moving and cumulative versions of SUM, AVERAGE, COUNT, MAX, MIN,and many more functions. They provide access to more than one row of a table and remove the need to usemultiple self-joins. The functions can be used only in the SELECT and ORDER BY clauses of the query. Windowingaggregate functions include the term FIRST VALUE, which returns the first value in the window; and the termLAST VALUE, which returns the last value in the window. These two functions can only be used as analyticfunctions unlike FIRST and LAST which can both be used as aggregate functions and analytic functions.The FIRST VALUE and LAST VALUE functions provide control over the way nulls are processed. If the IGNORENULLS option is used with FIRST VALUE, it returns the first non-null value in the set, or NULL if all values are NULL.6 SQL FOR ANALYSIS, REPORTING AND MODELING
If IGNORE NULLS is used with LAST VALUE, it returns the last non-null value in the set, or NULL if all values areNULL. The example shown below demonstrates using the IGNORE NULLS option with FIRST VALUE:SELECT prod id, channel id, time id,CASE WHEN MIN(amount sold) 9.5THEN MIN(amount sold) ELSE NULL END amount sold,FIRST VALUE(CASE WHEN MIN(amount sold) 9.5THEN min(amount sold) ELSE NULL END)IGNORE NULLS OVER (PARTITION BY prod idORDER BY channel id DESC, time idROWS BETWEEN UNBOUNDED PRECEDINGAND UNBOUNDED FOLLOWING) fv nvFROM salesWHERE prod id 115 AND time id BETWEEN '18-DEC-01' AND '22-DEC-01'GROUP BY prod id, channel id, time idORDER BY prod id;Which generates the following output:FIGURE 4 – AN EXAMPLE SHOWING USE OF FIRST VALUE AND NULL PROCESSINGIn contrast, the FIRST/LAST aggregate functions allow a data set to be ranked and then continue to work with itstop-ranked or bottom-ranked rows. After finding the top or bottom ranked rows, an aggregate function can be appliedto any desired column. These analytical functions are valuable because they allow a developer to simplify their SQLby avoiding the need for a self-join or sub-query and this improves performance. The syntax for these functionsbegins with a regular aggregate function that produces a single return value per group which is then used todetermine the ranking based on the FIRST/LAST functions and includes a new clause starting with the word KEEP.The LAG and LEAD functions can be used to compare values within a data set when the relative positions of rowscan be determined in advance. The LAG function accesses a row based on a given offset working backwards fromthe current position. The LEAD function does the same but looks beyond the current position. Both work byspecifying the count of rows that separates the target row from the current row. These functions remove the need touse self-joins to fulfill this type of business requirement because they can access more than one row of a table at thesame time. To ensure the most efficient performance, the Oracle Database has built-in optimizations to support thistype of data processing.Once the processing for a query has completed it is then possible to incorporate additional reporting aggregates, ormeasures, such as compute running totals or moving averages. These additional calculations allow business users7 SQL FOR ANALYSIS, REPORTING AND MODELING
to build sophisticated queries such as creating a report that lists each product category and the region in which ithad maximum sales.This is another great example of how using SQL as the language to deliver analytics allows developers to layer theirSQL code with different functions while maintaining a level of simplicity that makes maintenance, support andenhancements much easier to manage compared to using bespoke and complex code (some of which might haveto exist outside the database).Data densification for reporting and time series calculationsData is normally stored in sparse form. That is, if no value exists for a given combination of dimension values, norow exists in the fact table. However, in some cases it may be necessary to view the data in a dense form, with rowsfor all combinations of dimension values displayed even when no data exists for specific data points. Time seriescalculations can be performed most easily when data is dense along the time dimension. This is because densedata will fill a consistent number of rows for each period, which in turn makes it easier to use the analytic windowingfunctions with physical offsets.Data densification is the process of converting sparse data into dense form. To overcome the problem of datasparsity, Oracle provides the partitioned outer join to fill the gaps in a time series or any other dimension. Such a joinextends the conventional outer join syntax by applying the outer join to each logical partition defined in a query.Oracle logically partitions the rows in the query based on the expression specified in the PARTITION BY clause. Theresult of a partitioned outer join is a UNION of the outer joins of each of the partitions in the logically partitioned tablewith the table on the other side of the join.The result is a much richer data set that is easier to use in terms of applying analytical functions such as movingaverages and moving totals.Data samplingSome business intelligence reports and advanced analytical processing often need to limit the number of rowsreturned by a query either by asking for a specific number of rows (for example top 5 and/or bottom 10) or apercentage of rows (for example 25% of all rows). This type of query is often called a Top-N query and it offers astraightforward way of creating reports that show "Who are the ten most profitable customers?". It is also useful foruser interfaces that provide the first few rows of a data set for browsing.This type of requirement highlights the key benefits of switching to Oracle’s SQL analytical functions. To date manydevelopers, report writers and software vendors have satisfied this type requirement by using the ROW NUMBERwindow function. The SQL code needed is not overly complicated but by using the ANSI SQL standard syntax, thenew “row limiting” clause that appears as the last part of a SELECT, the SQL statement is greatly simplified andeasier to understand as shown in the following example:SELECT empno, ename, deptnoFROM (SELECTempno, ename, deptno, sal, comm,row number() OVER (ORDER BYsal,comm) rnFROM emp)WHERE rn 5ORDER BY sal, comm;8 SQL FOR ANALYSIS, REPORTING AND MODELINGSELECT empno, ename, deptnoFROM empORDER BY sal, commFETCH FIRST 5 ROWS ONLY;
Spreadsheet-like data modeling and what-if analysisMany business users like the flexibility provided by spreadsheets. However, these tools have severe limitations interms of the amount of data they can manage. What business users really want is a solution that combines accessto the huge pools of data inside their data warehouse with the flexibility of analysis offered by traditionalspreadsheets. Oracle can now offer business users a SQL based solution: the MODEL clause. Oracle’s vision is thatfor many applications the MODEL clause will replace the need to use desktop spreadsheets.The MODEL clause allows business users to create a sophisticated multidimensional array from query results andthen apply business calculations (rules) to this array to derive new calculated values. These rules can range fromsimple arithmetic operations to more complex and sophisticated requirements such as recursive simultaneousequations. The MODEL clause leverages the Oracle Database's core strengths in terms of scalability, manageability,collaboration, and security. The core query engine can work with unlimited quantities of data. By defining andexecuting models within the database, users avoidtransferring large data sets to and from separatemodeling environments. Models can be sharedeasily across workgroups, ensuring thatcalculations are consistent for all applications.Access can also be controlled precisely withOracle's security features. With its rich functionality,the MODEL clause can enhance all types ofapplications.The Model clause defines a multidimensional arrayby mapping the columns of a query into threegroups: partitions, dimensions, and measurecolumns. These elements perform the followingtasks:1) Partitions define logical blocks within the resultset in a way similar to the partitions of the analyticalfunctions (see section on partitions in the sectionProcessing concepts behind analytical SQL). Eachpartition is viewed by the formulae as anindependent array.FIGURE 5 – USING THE SQL MODEL CLAUSE2) Dimensions identify each measure cell within a partition. These identifying characteristics include datapoints such as date, region and product name.3) Measures are equivalent to the measures of a fact table in a star schema. They typically containnumeric values such as sales units or cost. Each cell is accessed within its partition by specifying its fullcombination of dimensions.Figure 5 shows how these three concepts would be applied. The diagram is broken down into three parts:Stage 1: maps the columns to model entities. This divides the source table into three components:partition, dimension and measure columns.Stage 2: defines the required business rules that define the new data points. Finally, the last stage createsa unified output by applying the rules to the source table to create a unified data set containing the originaldata and the hypothetical data derived from the business rules.9 SQL FOR ANALYSIS, REPORTING AND MODELING
Stage 3: merge data with calculated rows - the unshaded area in Figure 5 shows the output block andcontains the data retrieved from the database, while the shaded output shows rows calculated using therules (note that the rules are applied within each partition).The SQL Model clause is able to support very sophisticated calculations. The SQL query below shows a typicalexample of how to use the SQL model clause:SELECT SUBSTR(country, 1, 20) country,SUBSTR(product, 1, 15) product, year, salesFROM sales viewWHERE country IN ('Italy', 'Japan')MODELPARTITION BY (country) DIMENSION BY (product, year)MEASURES (sales sales)RULES(sales['Bounce', 2002] sales['Bounce', 2001] sales['Bounce', 2000],sales['Y Box', 2002] sales['Y Box', 2001],sales['All Products', 2002] sales['Bounce', 2002] sales['Y Box', 2002])ORDER BY country, product, year;The above query partitions the source data, the table sales view, on country so that the model computation, asdefined by the three RULES, is performed on each country. The model calculates the sales of “Bounce” in “2002” asthe sum of its sales in “2000” and “2001” and sets the sales for “Y Box” in “2002” to the same value as they werein “2001”. In addition it introduces a new product category called “All Products” for year “2002” to be the sum ofsales of “Bounce” and “Y Box” for that year.The output from the above query is shown below and the highlighted row indicates new values:FIGURE 5 – AN EXAMPLE USING SQL MODEL CLAUSE TO INSERT NEW DIMENSION VALUES AND TOTALSFor more information please refer to the Oracle whitepaper “The SQL Model Clause of Oracle Database”4. There isan Oracle-by-Example tutorial available on the Oracle Learning Library5.The Model clause treats relational data as a multidimensional array in which every cell is accessible through aconcise, flexible notation. This means that complex SQL joins and unions are eliminated and processing is UTORIALS/OBE/DB/10G/R2/PROD/BIDW/SQLMODEL/SQLMODEL OTN.HTM10 SQL FOR ANALYSIS, REPORTING AND MODELING
- Oracle's parallel query processing powers are exploited by the Model clause and this allows applications to benefitfrom enterprise level scalability. The Model clause automatically handles logical dependencies among formulas andfurther simplifies the development and maintenance of sophisticated business calculations.ConclusionOracle’s analytical SQL features and functions provide business users and SQL developers with a simplified way tosupport the most important operational and business intelligence analysis, reporting and data modelingrequirements. By moving these types of processing requirements inside the database developers can benefit fromincreased productivity and business users can benefit from improved query performance across a broad rangebusiness calculations.The use of Oracle’s in-database analytical features and functions deliver the following benefits to IT teams andbusiness users: Increased developer productivity Minimizes learning effort Improves manageability Provides investment protection (adheres to industry standards based syntax) Delivers increased query speedThe flexibility and power of Oracle’s analytic capabilities, combined with their adherence to international SQLstandards, makes them an important tool for all SQL users: DBAs, application developers, data warehousedevelopers and business users. In addition, many business intelligence tool vendors have recognized theimportance of these features and functions by incorporating support for them directly in to their products.Overall, the SQL analytic functions and features in Oracle Database 12c make it the most effective platform fordelivering analytical results directly into operational, data warehousing and business intelligence projects.Further ReadingSee the following links for more information about the in-database analytic features that are part of Oracle Database:1.Database SQL Language Reference - Oracle and Standard SQL2.Oracle Analytical SQL Features and Functions - a compelling array of analytical features andfunctions accessible through SQL. Available via the Analytic SQL home page on OTN.3.SQL - the natural language for analysis – a review of the reasons why SQL is the best language for dataanalysis. Available via the Analytic SQL home page on OTN.4.Oracle Statistical Functions - eliminate movement and staging to external systems to perform statisticalanalysis. For more information see the SQL Statistical Functions home page on OTN.5.Oracle Database 12c Query Optimization - providing innovation in plan execution and stability.The following Oracle whitepapers, articles, presentations and data sheets are essential reading and available via theAnalytic SQL home page on OTN:11 SQL FOR ANALYSIS, REPORTING AND MODELING
a.SQL for Data Validation and Data Wranglingb.SQL for Analysis, Reporting and Modelingc.SQL for Advanced Data Aggregationd.SQL for Approximate Query Processinge.SQL for Pattern Matching2.Oracle Magazine SQL 101 Columns3.Oracle Database SQL Language Reference—T-test Statistical Functions4.Oracle Statistical Functions Overview5.SQL Analytics Data SheetYou will find links to the above papers, and more, on the “Oracle Analytical SQL” web page hosted on the OracleTechnology ml12 SQL FOR ANALYSIS, REPORTING AND MODELING
Oracle Corporation, World HeadquartersWorldwide Inquiries500 Oracle ParkwayPhone: 1.650.506.7000Redwood Shores, CA 94065, USAFax: 1.650.506.7200CONNECT W ITH Copyright 2015, Oracle and/or its affiliates. All rights reserved. This document is provided for information purposes only, and thecontents hereof are subject to change without notice. This document is not warranted to be error-free, nor subject to any otherwarranties or conditions, whether expressed orally or implied in law, including implied warranties and conditions of merchantability orfitness for a particular purpose. We specifically disclaim any liability with respect to this document, and no contractual obligations areformed either directly or indirectly by this document. This document may not be reproduced or transmitted in any form or by anymeans, electronic or mechanical, for any purpose, without our prior written permission.oracle.com/sqlOracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.github/oracle/analytical-sql-examplesIntel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license andare trademarks or registered trademarks of SPARC International, I
The rest of this paper will outline the key SQL-based features for analytical processing within Oracle Database 12c Release 2 1. In-database analytical SQL with Oracle Database 12c Release 2 This section outlines the high level processing concepts behind Oracle's in-database analytic SQL. Processing concepts behind analytical SQL
