- Elevator Pitch -

When you learn the basics of analytic functions, most often you will see nice short examples. You may then have problems translating those basics into useful code for your real work. In this session you will see more complex examples of using analytic functions, all from applications in real life.

- Description -

Have you learned the syntax of analytic functions? Seen many presenters demonstrate with nice short examples how smart analytic functions are? And then went home and thought: “Well, they may be smart, but how can I use them in my real work?”

I’ve used analytic functions in thousands of SQL statements in more than 15 years developing our application. I can’t imagine coding SQL without analytic functions, as they allow me to put much functionality in SQL that otherwise might have needed procedural looping. Boss is happy as well, as the application perform much faster.

In this session I’ll showcase several of the real life use cases of analytic functions I’ve made over the years, hopefully serving as inspiration to your own work.

- Notes -

This presentation presumes some audience knowledge about analytic functions. It will focus on showing real-life usage examples to demonstrate the power of analytics giving attendees some idea of the breadth of problems that can be efficiently solved in single SQL statements.

• Top selling items: RANK, DENSE_RANK, ROW_NUMBER and RATIO_TO_REPORT.
• Picking by FIFO: Rolling sums using ROWS BETWEEN clause.
• Picking route: DENSE_RANK for numbering warehouse aisles.
• Picking efficiency: LAG on event log to determine time spent by employee waiting and working.
• Forecast sales: REGR_SLOPE to forecast next years sale.
• Forecast zero stock: Rolling sums on hourly budget data for fireworks sale.

Every one of these cases come from the retail application developed over the 16 years I worked at my previous employer.

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

Presented at:
- ODTUG Kscope 2014
- ilOUG 2019
- DOAG 2019
- OUGN 2022
- HrOUG 2024