Mastering Oracle SQL, 2nd Edition

Chapter 14. Advanced Analytic SQL

For years, SQL has been criticized for its inability to handle routine decision support queries. With a host of new analytic functions introduced in Oracle8i, Oracle9i, Database, and Oracle Database 10g, Oracle has taken giant strides toward 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 10 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.

Although all of the previous queries can be easily expressed in English, they have historically been difficult to ...