Mastering Oracle SQL

Chapter 13. Advanced Analytic SQL

For years, SQL has been criticized for its inability to handle routine decision support queries. With the host of new analytic functions introduced in Oracle8i and Oracle9i, Oracle has taken giant strides towards eliminating this deficiency. In doing so, Oracle has further blurred the distinction between its multipurpose relational database server and other, special-purpose data warehouse and statistical analysis servers.

Analytic SQL Overview

The types of queries issued by Decision Support Systems (DSS) differ from those issued against OLTP systems. Consider the following business queries:

• Find the top ten salespeople in each sales district last year.

• Find all customers whose total orders last year exceeded 20% of the aggregate sales for their geographic region.

• Identify the region that suffered the worst quarter-to-quarter sales decline last year.

• Find the best and worst selling menu items by state for each quarter last year.

Queries such as these are staples of DSS, and are used by managers, analysts, marketing executives, etc. to spot trends, identify outliers, uncover business opportunities, and predict future business performance. DSS systems typically sit atop data warehouses, in which large quantities of scrubbed, aggregated data provide fertile grounds for researching and formulating business decisions.

While all of the previous queries can be easily expressed in English, they have historically been difficult to formulate using SQL for the ...