In today’s issue I would like to address a very useful topic in an oracle environment called analytic functions. These functions are extremely useful to build complex calculations in an easy manner. In older oracle versions you had to create compley sub-queries and joins what can be now done by analytic functions.
To give you a brief introduction in this topic we will build YearToDate sums with these functions, that means for our monthly turnovers we want to calculate the YearToMonth turnover (turnover from January until each month) for each month with the help of analytic functions. For this our base table TBL_SALES looks like this:
| MONTH | QTY | TURNOVER | |
| 1 | 201212 | 5 | 500 |
| 2 | 201301 | 1 | 100 |
| 3 | 201302 | 2 | 150 |
| 4 | 201303 | 3 | 250 |
| 5 | 201304 | 4 | 450 |
Fig.1: TBL_SALES
The general syntax fort he analytic functions is like this:
Function(….) Over( Partition by ….. Order by ….. Rows between …. And …..)
There are a lot of functions that can be used with this, but today we will focus on the SUM function, which is also known as aggregate function. The disadvantage of these aggregates is, that they can only work on the grouping of the whole query. But sometimes you need different groupings for different calculations. Maybe we want to list every month with its quantity and then the complete quantity. Without analytic functions one could create a sub query that sums all quantities, maybe like this:
SELECT MONTH,
QTY,
(SELECT SUM(QTY) FROM TBL_SALES) AS TOTAL_QTY
FROM TBL_SALES
With analytical functions the same would look like this:
SELECT MONTH,
QTY,
SUM(QTY) OVER() AS TOTAL_QTY
FROM TBL_SALES
The result in both cases is this:
MONTH QTY TOTAL_QTY
———————————————–
201212 5 15
201304 4 15
201303 3 15
201301 1 15
201302 2 15
As this example is pretty simple the advantage isn’t that big. But the more complex the aggregates are, the higher is the advantage using analytic functions.
Back to the syntax of analytic functions – In our case above we didn’t use the PARTITION BY neither the ORDER BY clause. The reason is because we wanted to calculate a SUM over everything in the table. We can use the PARTITION BY clause to define on what level the function is working, i.e. on what level the total quantities are calculated. If we want to calculate them for each year then we would define a partition on the year like this:
SELECT MONTH,
QTY,
SUM(QTY) OVER(substr(MONTH,0,4) AS TOTAL_QTY_YEAR
FROM TBL_SALES
The result would be like this:
MONTH QTY TOTAL_QTY_YEAR
———————————————–
201212 5 5
201304 4 10
201303 3 10
201301 1 10
201302 2 10
We now want to calculate the YearToMonth-Sum for each Month. For this we need to tell oracle that in Jan 2013 he has to summarize just the January qty, in Feb 2013 he has to sum the Jan and Feb quantity and so on. We can give this information with the ROWS BETWEEN …. AND …. Statement. With this we can define a dynamic window. But when we use it we need to order our datasets too. This is done with the ORDER BY clause within the Function. The complete Statement for the YearToMonth SUM would look like this:
SELECT MONTH,
QTY,
SUM(QTY) OVER(PARTITION BY substr(MONTH,0,4)
ORDER BY MONTH ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS YTM
FROM TBL_SALES
The result is this:
MONTH QTY TOTAL_QTY_YEAR
———————————————–
201212 5 5
201301 1 1
201302 2 3
201303 3 6
201304 4 10
In this case UNBOUNDED PRECEDING means that all rows before the current row but within the partition are taken.
This was an example to show the basic principle how these functions work. You can easily do the same on partitions over several columns. And of course you can combine them all. For example you could combine the YTM Calculation and the Total_Qty_Year in one statement.
This article was the first part of the series on the analytic functions. In the next parts I will describe more functions and give you further examples on how to use them correctly.
If you are interested in this or other advanced Cognos/SQL topics you can also attend training on my corporate homepage. At the moment my open courses are only available in German. Inhouse training and online courses are also available in English language. So check out my open trainings (German) or my online courses (English)!
