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? |
Just to add a rant: While testing my simple sample, as usually I got the FK clause wrong: "foreign key ProductID references Products(ID)" - and the missing parantheses around the first ProductID turned that into a role name, and the table got an unwanted additional ID column. I just noticed that while the insert statements failed. – Ain't that something to improve in the FK clause syntax?
Adding the column on-the-fly with CREATE or ALTER is a long-standing SQL feature of SQL Anywhere that, frankly, I wish had not been implemented. Anil and I have discussed this before; it is exceedingly unlikely that any customer is aware that the software can do this - I'm not sure it's even documented.
@Glenn: I remember Breck and others ranting over this "I wish it would not have been implemented"-feature:) But it is truly documented in the docs, cf. http://dcx.sybase.com/index.html#1200en/dbusage/managingforeignkeys-sql.html.