Skip to main content
Question

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

  • June 11, 2021
  • 2 replies
  • 6 views

kfruchtman
Forum|alt.badge.img+2

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

2 replies

Jim_Knicely
Forum|alt.badge.img+2
  • Participating Frequently
  • June 11, 2021

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
Forum|alt.badge.img+2
  • Author
  • Participating Frequently
  • June 12, 2021

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