Custom aggregate and window functions in PostgreSQL and Oracle

 
In this article, we'll see how to create custom aggregate and windowed (in Oracle terminology - analytical) functions in two systems. Despite the differences in the syntax and, in general, the approach to extensibility, the mechanism of these functions is very similar. But there are differences too.
 
 
It must be admitted that its own aggregate and window functions are quite rare. Window functions generally for some reason are traditionally classified as "advanced" SQL and are considered difficult to understand and master. Here we should deal with those functions that already exist in the DBMS!
 
 
Why, then, do you really go into this question? I can name several reasons:
 
 
 
Although window functions are objectively more complex than conventional aggregate functions, there is nothing beyond them; This is absolutely necessary tool for SQL-developer. And creating your own window function, even quite simple, allows you to better understand how the standard works.
 
 
Window and aggregate functions are an excellent way to combine procedural processing with declarative logic. In some situations, you can perform complex actions, remaining within the framework of the problem solving paradigm with a single SQL query.
 
 
Yes, and just an interesting topic, and even more interesting to compare the two systems.
 
 
 
The example on which we will train is the average calculation, the analog of the standard avg function for numeric (number in Oracle). We will write this function and see how it works in the aggregate and window modes and whether it can be computed by several parallel processes. In conclusion, let's look at an example from real life.
 
Custom aggregate functions
 
CREATE AGGREGATE
 
Standard aggregate and windows functions
 
 
Oracle:
 
 
 
Custom aggregate functions
 
Interface of aggregate functions
 
Standard aggregate and analytical functions
 
 

Example of rounding of kopecks


 
And the promised example of life. I came up with this function when I had to write reports for the accounting department working under RAS (Russian accounting rules).
 
 
The simplest task in which there is a need for rounding is the distribution of total expenses (say, 100 rubles) per department (say 3 pieces) according to some principle (say, evenly):
 
 
WITH depts (name) AS (
 
VALUES ('A'), ('B'), ('C')
 
), report (dept, amount) AS (
 
SELECT name, ??? /count (*) OVER () FROM depts
 
)
 
SELECT dept, round (amount, 2) FROM report;

 
dept | round
 
------ + -------
 
A | ???r3r32194.  
B | ???r3r32194.  
C | ???r3r32194.  
(3 rows)
 

 
This query shows the problem: the amount should be rounded, but the kopeck is lost. And RAS does not forgive this.
 
 
The task can be solved in different ways, but for my taste the most elegant way is the window function, which works in an increasing mode and takes the whole fight with copecks on itself:
 
 
WITH depts (name) AS (
 
VALUES ('A'), ('B'), ('C')
 
), report (dept, amount) AS (
 
SELECT name, ??? /count (*) OVER () FROM depts
 
)
 
SELECT dept, round2 (amount) OVER (ORDER BY dept) FROM report;

 
dept | round2
 
------ + --------
 
A | ???r3r32194.  
B | ???r3r32194.  
C | ???r3r32194.  
(3 rows)
 

 
The status of this function includes the rounding error (r_error) and the current rounded value (amount). The function of processing the next value increases the rounding error, and if it already exceeds half a penny, adds a penny to the rounded sum:
 
 
state.r_error: = state.r_error + val - round (val, 2);
 
state.amount: = round (val, 2) + round (state.r_error, 2);
 
state.r_error: = state.r_error - round (state.r_error, 2);
 

 
A function that returns a result simply returns the already prepared state.amount.
 
 
I will not cite the full code of the function: using the examples already given, it is not difficult to write it.
 
 
If you've seen interesting examples of using your own aggregate or window functions - share them in the comments.
+ 0 -

Add comment