Practical Oracle SQL : Mastering the Full Power of Oracle Database

Write powerful queries using as much of the feature-rich Oracle SQL language as possible, progressing beyond the simple queries of basic SQL as standardized in SQL-92. Both standard SQL and Oracle's own extensions to the language have progressed far over the decades in terms of how much you can work with your data in a single, albeit sometimes complex, SQL statement. If you already know the basics of SQL, this book provides many examples of how to write even more advanced SQL to huge benefit in your applications, such as: Pivoting rows to columns and columns to rows Recursion in SQL with MODEL and WITH clauses Answering Top-N questions Forecasting with linear regressions Row pattern matching to group or distribute rows Using MATCH_RECOGNIZE as a row processing engine The process of starting from simpler statements in SQL, and gradually working those statements stepwise into more complex statements that deliver powerful results, is covered in each example. By trying out the recipes and examples for yourself, you will put together the building blocks into powerful SQL statements that will make your application run circles around your competitors. What You Will Learn Take full advantage of advanced and modern features in Oracle SQL Recognize when modern SQL constructs can help create better applications Improve SQL query building skills through stepwise refinement Apply set-based thinking to process more data in fewer queries Make cross-row calculations with analytic functions Search for patterns across multiple rows using row pattern matching Break complex calculations into smaller steps with subquery factoring Who This Book Is For Oracle Database developers who already know some SQL, but rarely use features of the language beyond the SQL-92 standard. And it is for developers who would like to apply the more modern features of Oracle SQL, but don't know where to start. The book also is for those who want to write increasingly complex queries in a stepwise and understandable manner. Experienced developers will use the book to develop more efficient queries using the advanced features of the Oracle SQL language. Table of Contents About the Author Acknowledgments Introduction Part I: Core SQL Chapter 1: Correlating Inline Views Brewery products and sales Scalar subqueries and multiple columns Correlating inline view Outer joining correlated inline view Lessons learned Chapter 2: Pitfalls of Set Operations Sets of beer Set operators Set concatenation The three set operators Multiset operators Multiset union Multiset intersect Multiset except Minus vs. multiset except Lessons learned Chapter 3: Divide and Conquer with Subquery Factoring Products and sales data Best-selling years of the less strong beers Modularization using the with clause Multiple uses of the same subquery Listing column names Lessons learned Chapter 4: Tree Calculations with Recursion Bottles in boxes on pallets Multiplying hierarchical quantities Recursive subquery factoring Dynamic SQL in PL/SQL function Lessons learned Chapter 5: Functions Defined Within SQL Table with beer alcohol data Blood alcohol concentration Function with PRAGMA UDF Function in the with clause Encapsulated in a view Lessons learned Chapter 6: Iterative Calculations with Multidimensional Data Conway’s Game of Life Live neighbor count with the model clause Iterating generations Lessons learned Chapter 7: Unpivoting Columns to Rows Data received in columns Unpivoting to rows Do-it-yourself unpivoting More than one dimension and/or measure Using dimension tables Dynamic mapping to dimension tables Lessons learned Chapter 8: Pivoting Rows to Columns Tables for pivoting Pivoting single measure and dimension Do-it-yourself manual pivoting Multiple measures Multiple dimensions as well Lessons learned Chapter 9: Splitting Delimited Text Customer favorites and reviews Delimited single values Pipelined table function Built-in APEX table function Straight SQL with row generators Treating the string as a JSON array Delimited multiple values Custom ODCI table function Combining apex_string.split and substr Row generators and regexp_substr Transformation to JSON Lessons learned Chapter 10: Creating Delimited Text Delimited lists of products String aggregation Aggregate function listagg Aggregate function collect Custom aggregate function stragg Aggregate function xmlagg When it doesn’t fit in a VARCHAR2 Get just the first part of the result Try to make it fit with reduced data Use a CLOB instead of a VARCHAR2 Lessons learned Part II: Analytic Functions Chapter 11: Analytic Partitions, Ordering, and Windows Sums of quantities Analytic syntax Partitions Ordering and windows Flexibility of the window clause Windows on value ranges The danger of the default window Lessons learned Chapter 12: Answering Top-N Questions Top-N of sales data Which kind of Top-3 do you mean? The sales data for the beer Traditional rownum method Analytic functions for ranking Fetch only the first rows Handling of ties What the row limiting clause cannot do Top-N in multiple partitions The lateral trick for the row limiting clause Lessons learned Chapter 13: Ordered Subsets with Rolling Sums Data for goods picking Building the picking SQL Solving picking an order by FIFO Easy switch of picking principle Solving optimal picking route Solving batch picking Finalizing the complete picking SQL Lessons learned Chapter 14: Analyzing Activity Logs with Lead Picking activity log Analyzing departures and arrivals Analyzing picking activity Complete picking cycle analysis Teaser: row pattern matching Lessons learned Chapter 15: Forecasting with Linear Regression Sales forecasting Time series Calculating the basis for regression Linear regression Final forecast Lessons learned Chapter 16: Rolling Sums to Forecast Reaching Minimums Inventory, budget, and order The data Accumulating until zero Restocking when minimum reached Lessons learned Part III: Row Pattern Matching Chapter 17: Up-and-Down Patterns The stock ticker example Classifying downs and ups Downs + ups = V shapes Revisiting if SAME is needed V + V = W shapes Overlapping W shapes Lessons learned Chapter 18: Grouping Data Through Patterns Two sets of data to group Three grouping conditions Group consecutive data Analytic Tabibitosan vs. match_recognize Consecutive dates instead of integers Gap detection Group until gap too large Group until fixed limit Lessons learned Chapter 19: Merging Date Ranges Job hire periods Temporal validity Merging overlapping ranges Attempts comparing to the previous row Better comparing to the maximum end date Handling the null dates Lessons learned Chapter 20: Finding Abnormal Peaks Web page counter history The counter data Patterns in the raw counter data Looking at daily visits Patterns in daily visits data More complex patterns Lessons learned Chapter 21: Bin Fitting Inventory to be packed in boxes Bin fitting with unlimited number of bins of limited capacity Showing where box capacity is too small Bin fitting with limited number of bins of unlimited capacity Lessons learned Chapter 22: Counting Children in Trees Hierarchical tree of employees Counting subordinates of all levels Counting with row pattern matching The details of each match Fiddling with the output Lessons learned Index