Capping a Running Total
So far, we’ve seen how to calculate an “ordinary” running total with SQL using window functions. That was easy. Now, how about if we cap the running total such that it never goes below zero? Essentially, we want to calculate this:
| DATE | AMOUNT | TOTAL | |------------|--------|-------| | 2012-01-01 | 800 | 800 | | 2012-02-01 | 1900 | 2700 | | 2012-03-01 | 1750 | 4450 | | 2012-04-01 | -20000 | 0 | | 2012-05-01 | 900 | 900 | | 2012-06-01 | 3900 | 4800 | | 2012-07-01 | -2600 | 2200 | | 2012-08-01 | -2600 | 0 | | 2012-09-01 | 2100 | 2100 | | 2012-10-01 | -2400 | 0 | | 2012-11-01 | 1100 | 1100 | | 2012-12-01 | 1300 | 2400 |
So, when that big negative AMOUNT
-20000
was subtracted, instead of displaying the real TOTAL
of -15550
, we simply display 0
. In other words (or data sets):
| DATE | AMOUNT | TOTAL | |------------|--------|-------| | 2012-01-01 | 800 | 800 | GREATEST(0, 800) | 2012-02-01 | 1900 | 2700 | GREATEST(0, 2700) | 2012-03-01 | 1750 | 4450 | GREATEST(0, 4450) | 2012-04-01 | -20000 | 0 | GREATEST(0, -15550) | 2012-05-01 | 900 | 900 | GREATEST(0, 900) | 2012-06-01 | 3900 | 4800 | GREATEST(0, 4800) | 2012-07-01 | -2600 | 2200 | GREATEST(0, 2200) | 2012-08-01 | -2600 | 0 | GREATEST(0, -400) | 2012-09-01 | 2100 | 2100 | GREATEST(0, 2100) | 2012-10-01 | -2400 | 0 | GREATEST(0, -300) | 2012-11-01 | 1100 | 1100 | GREATEST(0, 1100) | 2012-12-01 | 1300 | 2400 | GREATEST(0, 2400)
How will we do it?
Exactly. With obscure, vendor-specific SQL. In this case, we’re using Oracle SQL
How does it work? Surprisingly easy!
Just add MODEL
after any table, and you’re opening up a can of awesome SQL worms!
1
2
3
4
|
SELECT ... FROM some_table
-- Put this after any table MODEL ... |
Once we put MODEL
there, we can implement spreadsheet logic directly in our SQL statements, just as with Microsoft Excel.
The following three clauses are the most useful and widely used (i.e. 1-2 per year by anyone on this planet):
1
2
3
4
5
6
7
8
9
|
MODEL -- The spreadsheet dimensions
DIMENSION BY ...
-- The spreadsheet cell type
MEASURES ...
-- The spreadsheet formulas
RULES ...
|
The meaning of each of these three additional clauses is best explained with slides again.
The DIMENSION BY
clause specifies the dimensions of your spreadsheet. Unlike in MS Excel, you can have any number of dimensions in Oracle:
The MEASURES
clause specifies the values that are available in each cell of your spreadsheet. Unlike in MS Excel, you can have a whole tuple in each cell in Oracle, not just a single value.
The RULES
clause specifies the formulas that apply to each cell in your spreadsheet. Unlike in MS Excel, these rules / formulas are centralised at a single place, instead of being put inside of each cell:
This design makes MODEL
a bit harder to use than MS Excel, but much more powerful, if you dare. The whole query will then be “trivially”:
1
2
3
4
5
6
7
8
9
10
11
12
|
SELECT *
FROM (
SELECT date , amount, 0 AS total
FROM amounts
) MODEL DIMENSION BY (row_number() OVER ( ORDER BY date ) AS rn)
MEASURES ( date , amount, total)
RULES (
total[ any ] = greatest(0,
coalesce (total[cv(rn) - 1], 0) + amount[cv(rn)])
)
|
This whole thing is so powerful, it ships with its own whitepaper by Oracle, so rather than explaining things further here in this article, please do read the excellent whitepaper:
http://www.oracle.com/technetwork/middleware/bi-foundation/10gr1-twp-bi-dw-sqlmodel-131067.pdf
- ۹۵/۰۳/۱۱