From the course: Oracle Database 19c: Advanced SQL
Unlock the full course today
Join today to access over 22,500 courses taught by industry experts or purchase this course individually.
Analytic functions, grouping in a window - Oracle Database Tutorial
From the course: Oracle Database 19c: Advanced SQL
Analytic functions, grouping in a window
- Multi row functions, as their name suggests, operate over more than one row in a table and often in subsets of rows in a table. Analytic functions take multi row functions to the next level by not only partitioning and sorting rows but by aggregating them within a moving window. What is an analytic function? It's an extension of some of the aggregate functions you may have already seen. An analytic function is a super set of aggregate functions that returns multiple rows for each row in a defined window. The window is defined in the analytic query itself. Analytic functions include aggregate functions such as average, mean, max and standard deviation. But since they use a moving window they can include functionality that returns more than one row for each row in the moving window. The query partition clause identifies how the result set is divided into groups. Each group will have the analytic function performed. The order by clause identifies which columns to sort by before…