Learning SQL, 3rd Edition

Chapter 16. Analytic Functions

Data volumes have been growing at a staggering pace, and organizations are having difficulty storing all of it, not to mention trying to make sense of it. While data analysis has traditionally occurred outside of the database server, using specialized tools or languages such as Excel, R, and Python, the SQL language includes a robust set of functions useful for analytical processing. If you need to generate rankings to identify the top 10 salespeople in your company or if you are generating a financial report for your customer and need to calculate three-month rolling averages, you can use SQL’s built-in analytic functions to perform these types of calculations.

Analytic Function Concepts

After the database server has completed all of the steps necessary to evaluate a query, including joining, filtering, grouping, and sorting, the result set is complete and ready to be returned to the caller. Imagine if you could pause the query execution at this point and take a walk through the result set while it is still held in memory; what types of analysis might you want to do? If your result set contains sales data, perhaps you might want to generate rankings for salespeople or regions, or calculate percentage differences between one time period and another. If you are generating results for a financial report, perhaps you would like to calculate subtotals for each report section, and a grand total for the final section. Using analytic functions, you can ...