Ordered Subsets with Rolling Sums

Abstract

One of the most useful features of analytic functions is the flexibility of the window clause, enabling aggregation of particular subsets of the data within a specific order. A classic subset that can be used for many purposes is the set of data from the beginning until the current row – if, for example, the sum aggregate function is used on that subset, you get an accumulated sum or rolling sum or running total (many names for the same thing). The use cases are plenty; many financial reports need running totals. But a different and extremely practical use case involves a slight variation of the running total, using the sum of all the previous rows to keep selecting rows until a just sufficiently large subset to cover the necessary sum has been reached. This can, for example, be used to build a FIFO warehouse picking list in a single SQL statement.