Intro: I have to admit to belong to the huge group of developers who do not use WINDOW functions on a daily basis – cf. Glenn's blog's article on OLAP...
Today, I have to build a quite complex calculation:
Say, you have a table of the history of some entities, and you have to find out which entities have had particular steps in their history. Less abstract: Take the SQL Anywhere sample database, and you would have to find out the products whose quantity has decreased a particular number on a single day.
Imagine there would be an additional table ProductsHistory which contains the quantity of each product at the end of any day (something like a daily inventory), something like
As an example, the table might contain the following quantities for product 400 (the Cotton Baseball Cap) for the last 10 days:
How would I find out those products (and the according day) whose quantity has decreased by 20 pieces or more on any day? - That should return the 3rd and 7th row for this product.
Using min/max and comparing them won't do as each entry has to be compared with its ancestor. That seems fitting for a WINDOW expression. However, according to the docs, WINDOW expressions can only be used with aggregate functions or statistical functions, and here a simple substraction would do: quantity from day x - quantity from the day before.
So what can be done?
(The answer is about to come...)
Luckily, after a long while I came across the OLAP whitepaper by Glenn at others, and found a topic on calculating deltas with the help of window functions.
Then I noticed the same topic is contained in the help (though not easy to find, IMHO), and here it goes:
Use two window expressions with SUM to calculate the quantity, one for the current row, one for the previous row. Then use the window-expressions themselves as operators for the simple function (here: substraction).
In the above case:
This returns the following result set:
But how do I filter only those rows with a particular QuantityDecrease?