Window Functions (part2) - cumulative aggregates

Posted on in firebird

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

And we run this query:

select
    id, salary, sum(salary) over (order by salary) cum_salary
  from employee
  order by salary;

The result set produced will be:

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

So cum_salary returns the partial/accumulated aggregation (of the SUM function). You may found strange the 37.00 repeated for the ids 1 and 5, but that is how it should work. The ORDER BY keys are grouped together and the aggregation is computed once (but summing the two 10.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.

← Back to home