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%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

edited 26 Sep '10, 10:59

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050

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

permanent link

answered 01 Sep '10, 16:33

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

edited 01 Sep '10, 16:41

Glad you found that whitepaper useful, Volker.

(01 Sep '10, 18:25) Glenn Paulley
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • 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:

×19
×6

question asked: 01 Sep '10, 16:12

question was seen: 2,609 times

last updated: 26 Sep '10, 10:59