Window Functions (part2) - cumulative aggregates
Posted In:
firebird
.
By Adriano
Continuing with the window functions support for Firebird 3, I have now added another OVER sub-clause: ORDER BY. The ORDER BY sub-clause can be used with or without partitions, and it makes aggregate functions return the partial aggregations as the records are being processed.
Considering our employee table has this data:
| id | salary |
| 1 | 10.00 |
| 2 | 12.00 |
| 3 | 8.00 |
| 4 | 9.00 |
| 5 | 10.00 |
select
id, salary, sum(salary) over (order by salary) cum_salary
from employee
order by salary;
| id | salary | cum_salary |
| 3 | 8.00 | 8.00 |
| 4 | 9.00 | 17.00 |
| 1 | 10.00 | 37.00 |
| 5 | 10.00 | 37.00 |
| 2 | 12.00 | 49.00 |
You can use multiple windows with different orders, and ORDER BY parts like DESC, NULLS LAST, etc. Here is a weird example:
select
id, salary,
sum(salary) over (order by salary) cum_salary,
sum(salary) over (order by salary desc) cum_salary_desc
from employee
order by salary;
The result set produced will be:
| id | salary | cum_salary | cum_salary_desc |
| 3 | 8.00 | 8.00 | 49.00 |
| 4 | 9.00 | 17.00 | 41.00 |
| 1 | 10.00 | 37.00 | 32.00 |
| 5 | 10.00 | 37.00 | 32.00 |
| 2 | 12.00 | 49.00 | 12.00 |
With a partition ORDER BY works the same way, but at each partition boundary the aggregation is reset.
All aggregation functions are usable with ORDER BY, except the LIST function. As LIST returns blob, it would be slow to return multiple blobs (one for each order group), trick to implement, and I don't see an usage scenario for it.
Posts
9 Responses to Window Functions (part2) - cumulative aggregates
"You may found strange the 37.00 repeated for the ids 1 and 5, but that is how it should work"
mmm...
That behaviour would lead to incorrect results even if it is "political" correct of the definition.
I guess thats the behaviour in Oracle and other RBDMS, isnt?
Should we (firebird users) have some parameter to avid this "political" correct result but "practical" incorrect result?
@ssamayoa it will give incorrect results if you try to compute an aggregate over the windowed result set. But I don't think this is an use case for it.
It work that way in Oracle and PgSQL, so Firebird shouldn't be different nor have any config option to do the thing in an unstandard way.
would be interesting to implement a clause for these results be "practical"
as
select id, salary, sum(salary) over (order by salary) cum_salary
from employee
order by salary;
as it is very strange. even in the Oracle database.
@Fernando,
Do you mean something like PostgreSQL WINDOW clause:
select id, salary, sum(salary) over win_salary cum_salary
from employee
window win_salary as (order by salary)
order by salary;
Or something else?
would be nice to list 8, 17, 37, 47, 59 and 8, 17, 37, 37, 49 (still do not understand the logic of this). a clause on / off would solve this.
8, 17, 37, 47 and 59 (?) now *that* would be really scary!
The thing here (INHO) is that the server (or ANSI SQL) does not make any decisions. Neither about who´s cumulative $37 is worth more or less. Again; if id 1 gave 27 and id 5 gave 37 we could have gotten different results for two identical consecutive queries.
select
id, salary,
sum(salary) over (order by salary) cum_salary,
sum(salary) over (order by salary desc) cum_salary_desc
from employee
where id = 5
order by salary;
Imagine getting 27 the first execution and 37 the second… nobody would trust the system after that.
You could probably get the result that you want by doing something simple like below (I don't have a server with OVER() capability, so I can’t test it:
sum(salary) over (order by salary + id/100) cum_salary
That would make employee number 1 come before number 5 so 1 would be given $27 and five would have gotten $37. Every time, "Salary + id/100" will always be unique until someone has exactly $0.04 less at which point we would have to cast to string and concatenate but I'm too tired now.
If you do:
select
id, salary, sum(salary) over (order by salary, ID) cum_salary
from employee
order by salary;
would it sum the value 10 separately as it have a different group?
@Paulo,
Yes, in this case you force the "strange" thing to not happen.
@adriano It's quite clear and correct now. Nothing strange in the pattern. The order clause also group records.
Its a great feature! Thank's a lot! Keep up the good work!
Something to say?