what is the Analytic function that I am searching for and how can I use it?

kfruchtman

Hi Verticas!
I have finally reached out to the point that I actually need the analytic functions.
I am looking for but can't find the exact function I need.
Attached is a screen shot of my table but here is another example:
I need to add a column on the right that will do the calculation difference between amount of
"Sales" from one row to the amount of "Sales" of the following row:
E.g -
Lets say my query looks like this:

Catagory month+year Sales

Books 01/2020 100$
Books 02/2020 150$
Books 03/2020 220$
Books 04/2020 280$

I need to add another column on the right that will sum up the difference in Sales between one row to the other, so for the example above on the left to the "Sales" column I need a "Difference in sales" Column :

Difference

0$
50$
70$
60$

I added a screenshot of my real table if it was not clear...
I know there is a simple analytic function to use with OVER and Window .
Can someone tell me what it is exactly and how to use it?

Many thanks in advance!
Keren

Jim_Knicely

Here is a super simple example:

verticademos=> SELECT * FROM t;
 Catagory | month+year | Sales
----------+------------+-------
 Books    | 01/2020    |     0
 Books    | 02/2020    |   150
 Books    | 03/2020    |   220
 Books    | 04/2020    |   280
(4 rows)

verticademos=> SELECT *, Sales - NVL(LAG(Sales, 1) OVER (PARTITION BY Catagory ORDER BY Catagory, "month+year"), 0) "Difference in sales" FROM t ORDER BY Catagory, "month+year";
 Catagory | month+year | Sales | Difference in sales
----------+------------+-------+---------------------
 Books    | 01/2020    |     0 |                   0
 Books    | 02/2020    |   150 |                 150
 Books    | 03/2020    |   220 |                  70
 Books    | 04/2020    |   280 |                  60
(4 rows)

Might have to handle the ordering of the "month+year" a little better by maybe converting it to a date...

See doc page: LAG

kfruchtman

Worked like magic:-)) Thank you so so much