The forum will be down for maintenance at some point between Friday, September 25, 2020 at 5pm PDT and Sunday, September 27, 2020 at 11:59 PDT. Downtime is unknown but will be minimized.

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

 3 2 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...) asked 01 Sep '10, 16:12 Volker Barth 36.7k●343●505●761 accept rate: 34% Breck Carter 30.8k●496●680●992 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? (01 Sep '10, 16:55) Volker Barth 1 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. (01 Sep '10, 18:25) Glenn Paulley @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. (01 Sep '10, 20:16) Volker Barth

 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... answered 01 Sep '10, 16:33 Volker Barth 36.7k●343●505●761 accept rate: 34% Glad you found that whitepaper useful, Volker. (01 Sep '10, 18:25) Glenn Paulley
 toggle preview community wiki:

By Email:

Markdown Basics

• *italic* or _italic_
• **bold** or __bold__
• image?![alt text](/path/img.jpg "title")
• numbered list: 1. Foo 2. Bar
• to add a line break simply add two spaces to where you would like the new line to be.
• basic HTML tags are also supported

Question tags:

question asked: 01 Sep '10, 16:12

question was seen: 1,862 times

last updated: 26 Sep '10, 10:59