Analytic Functions 101

Plain SQL gives you either detailed single rows or aggregated data. When you need both, or need in one row to get data of other rows, you tend to do self-joins or slow procedural processing. Instead you should learn to use analytic functions. Here you learn basics of this indispensable part of SQL.

SQL gives you rows of detail, aggregate functions gives you summarized data. To solve your requirements you often need both at the same time. Or you need in one row to access data from or compare with one or more other rows. That can be messy with plain SQL often requiring accessing the same data multiple times.

Analytic functions can help you to make your SQL do many such things efficiently, that you otherwise might have been tempted to do in slow procedural loops. Properly applied analytic functions can speed up the tasks (for which they are appropriate) manyfold.

In this session I’ll cover the syntax (including 21c enhancements) as well as little quirks to remember, all demonstrated with short and clear examples.

This presentation will focus on how analytic functions work. Analytics will be explained from the basics requiring no particular audience knowledge besides basic SQL.

• Explanation of syntax.
• When and how to use PARTITION BY.
• When and how to use ORDER BY.
• When and how to use ROWS BETWEEN.
• When and how to use RANGE BETWEEN.
• 21c enhancements - WINDOW, GROUPS, EXCLUDE
• How analytic functions differ from aggregate functions.
• When are analytic functions appropriate to use rather than aggregates.
• Using analytic functions on aggregate results.
• “Visualize” your data and requirements to help thinking analytically.
• When analytics can outperform aggregates.
• Analytic functions not always the best choice.

Note: This presentation and presentation “Analytic Functions in Real Life” are well suited for a 2-hour double session on analytic functions.