How can I use a non-aggregate function like substraction within a WINDOW function?

[VolkerBarth]

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

create table ProductsHistory (
   ProductID int not null,
   ProductDay date not null,
   Quantity double not null,
   primary key (ProductID, ProductDay),
   foreign key (ProductID) references Products(ID)
)

As an example, the table might contain the following quantities for product 400 (the Cotton Baseball Cap) for the last 10 days:

insert ProductsHistory values (400, dateadd(dd, -9, current date),  90);
insert ProductsHistory values (400, dateadd(dd, -8, current date), 130);
insert ProductsHistory values (400, dateadd(dd, -7, current date), 108);
insert ProductsHistory values (400, dateadd(dd, -6, current date), 107);
insert ProductsHistory values (400, dateadd(dd, -5, current date), 101);
insert ProductsHistory values (400, dateadd(dd, -4, current date),  88);
insert ProductsHistory values (400, dateadd(dd, -3, current date),  53);
insert ProductsHistory values (400, dateadd(dd, -2, current date), 121);
insert ProductsHistory values (400, dateadd(dd, -1, current date), 117);
insert ProductsHistory values (400, dateadd(dd, -0, current date), 112);

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...)

[VolkerBarth]

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?

[Former Member]

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.

[VolkerBarth]

@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.

[VolkerBarth · Accepted solution]

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:

select ProductID, ProductDay, Quantity,
   sum(Quantity) over (partition by ProductID order by ProductDay
      rows between current row and current row) as CurrentQuantity,
   sum(Quantity) over (partition by ProductID order by ProductDay
      rows between 1 preceding and 1 preceding) as PreviousQuantity,
   PreviousQuantity - CurrentQuantity as QuantityDecrease
from ProductsHistory
order by 1, 2

This returns the following result set:

P-ID    ProductDay  Quantity    CurQty  PrevQty QtyDecrease
400 '2010-08-23'    90.0    90.0        
400 '2010-08-24'    130.0   130.0   90.0    -40.0
400 '2010-08-25'    108.0   108.0   130.0   22.0
400 '2010-08-26'    107.0   107.0   108.0   1.0
400 '2010-08-27'    101.0   101.0   107.0   6.0
400 '2010-08-28'    88.0    88.0    101.0   13.0
400 '2010-08-29'    53.0    53.0    88.0    35.0
400 '2010-08-30'    121.0   121.0   53.0    -68.0
400 '2010-08-31'    117.0   117.0   121.0   4.0
400 '2010-09-01'    112.0   112.0   117.0   5.0

But how do I filter only those rows with a particular QuantityDecrease?

That's another question, for sure...

[Former Member · Accepted solution]

Glad you found that whitepaper useful, Volker.