How to Group By -Runnig Total for Year to Date in VSQL

Moderator: NorbertKrupa

Vertica4VK
Newbie
Newbie
Posts: 6
Joined: Fri Aug 22, 2014 2:10 am

How to Group By -Runnig Total for Year to Date in VSQL

Post by Vertica4VK » Sat Aug 23, 2014 2:34 am

Hello

I need help with Calaculating Year to date running total using VSQL

I have data like this

rpt_Dt( 01/01/2014)
demand = 5
supply = 10
Jan-Avg= 10/5 = 2
YTD-avg-upto-Jan = 2

rpt_Dt( 02/01/2014)
demand = 2
supply = 10
Feb-Avg= 10/2 = 5
YTD-avg-From Jan-to-Feb = (Total of Jan+Feb-Supply)= 20 / ( Total of Jan+Feb Demand)= 7) = 2.857


I can calcuate Avg for each month as above for Jan it's 2 - Feb 5 ..etc. but i need 4th Columns called YTDavg -year to date AVG -which is kind of running total

How can i calcuate this YTD-running avg column in Vertica ?

Any help?? Please

Thanks

VK

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: How to Group By -Runnig Total for Year to Date in VSQL

Post by NorbertKrupa » Sat Aug 23, 2014 2:49 am

I think it might be difficult to come up with a query if we don't have a sample data set (seems you provided a rolled up view).
Checkout vertica.tips for more Vertica resources.

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: How to Group By -Runnig Total for Year to Date in VSQL

Post by JimKnicely » Sat Aug 23, 2014 6:42 pm

Maybe using analytic functions will help?

Example:

Code: Select all

dbadmin=> SELECT *,
dbadmin->        supply/demand month_avg,
dbadmin->        AVG(supply/demand) OVER (PARTITION BY EXTRACT(YEAR FROM rpt_dt ) ORDER BY rpt_dt) ytd_avg1,
dbadmin->        SUM(supply) OVER (PARTITION BY EXTRACT(YEAR FROM rpt_dt ) ORDER BY rpt_dt) /
dbadmin->          SUM(demand) OVER (PARTITION BY EXTRACT(YEAR FROM rpt_dt) ORDER BY rpt_dt) ytd_avg2
dbadmin->   FROM avg_test
dbadmin->  ORDER
dbadmin->     BY rpt_dt;
   rpt_dt   | demand | supply |      month_avg       | ytd_avg1 |       ytd_avg2
------------+--------+--------+----------------------+----------+----------------------
 2014-01-01 |      5 |     10 | 2.000000000000000000 |        2 | 2.000000000000000000
 2014-02-01 |      2 |     10 | 5.000000000000000000 |      3.5 | 2.857142857142857143
(2 rows)
Note that I did the average two ways.
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Vertica4VK
Newbie
Newbie
Posts: 6
Joined: Fri Aug 22, 2014 2:10 am

Re: How to Group By -Runnig Total for Year to Date in VSQL

Post by Vertica4VK » Mon Aug 25, 2014 7:20 pm

Hello Knicely87

Perfect - the last one ( ytd_avg2) is what i was looking for - Thanks.

One more( Please ) - with same data i am looking for Rolling 3/6/9/12 Months avg.

so - for report month of 2014-01-01 - my avg will be ( prior 3 months - Jan14+-Dec+Nov13) Rolling 3 Month
for report month of 2014-02-01 - my avg will be ( prior 3 months- Feb+Jan14+ Dec2013)...

for each reporting month i am looking for

MonthAvg -
RunningYTD- which you just showed
Rolling3Mnth-
Rolling6Mnth
....

Thanks Again

VK

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: How to Group By -Runnig Total for Year to Date in VSQL

Post by id10t » Mon Aug 25, 2014 7:25 pm

Hi!

[DELETED]
Last edited by id10t on Wed May 06, 2015 4:46 pm, edited 1 time in total.

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: How to Group By -Runnig Total for Year to Date in VSQL

Post by JimKnicely » Mon Aug 25, 2014 8:23 pm

In case you aren't Vertica 7.1 ... maybe this query will help?

Code: Select all

dbadmin=> SELECT * FROM avg_test ORDER BY rpt_dt;
   rpt_dt   | demand | supply
------------+--------+--------
 2014-01-01 |      5 |     10
 2014-02-01 |      2 |     10
 2014-03-01 |      5 |     10
 2014-04-01 |      2 |     10
 2014-05-01 |      5 |     10
 2014-06-01 |      2 |     10
 2014-07-01 |      5 |     10
 2014-08-01 |      2 |     10
(8 rows)

Code: Select all

dbadmin=> SELECT rpt_dt,
dbadmin->        demand,
dbadmin->        supply,
dbadmin->        supply/demand month_avg,
dbadmin->        SUM(supply) OVER (PARTITION BY EXTRACT(YEAR FROM rpt_dt ) ORDER BY rpt_dt) /
dbadmin->          SUM(demand) OVER (PARTITION BY EXTRACT(YEAR FROM rpt_dt) ORDER BY rpt_dt) ytd_avg,
dbadmin->        SUM(supply) OVER (ORDER BY rpt_dt ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) /
dbadmin->          SUM(demand) OVER (ORDER BY rpt_dt ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) rolling3mnth,
dbadmin->        SUM(supply) OVER (ORDER BY rpt_dt ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) /
dbadmin->          SUM(demand) OVER (ORDER BY rpt_dt ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) rolling6mnth
dbadmin->   FROM avg_test
dbadmin->  ORDER
dbadmin->     BY rpt_dt;
   rpt_dt   | demand | supply |      month_avg       |       ytd_avg        |     rolling3mnth     |     rolling6mnth
------------+--------+--------+----------------------+----------------------+----------------------+----------------------
 2014-01-01 |      5 |     10 | 2.000000000000000000 | 2.000000000000000000 | 2.000000000000000000 | 2.000000000000000000
 2014-02-01 |      2 |     10 | 5.000000000000000000 | 2.857142857142857143 | 2.857142857142857143 | 2.857142857142857143
 2014-03-01 |      5 |     10 | 2.000000000000000000 | 2.500000000000000000 | 2.500000000000000000 | 2.500000000000000000
 2014-04-01 |      2 |     10 | 5.000000000000000000 | 2.857142857142857143 | 3.333333333333333333 | 2.857142857142857143
 2014-05-01 |      5 |     10 | 2.000000000000000000 | 2.631578947368421053 | 2.500000000000000000 | 2.631578947368421053
 2014-06-01 |      2 |     10 | 5.000000000000000000 | 2.857142857142857143 | 3.333333333333333333 | 2.857142857142857143
 2014-07-01 |      5 |     10 | 2.000000000000000000 | 2.692307692307692308 | 2.500000000000000000 | 2.857142857142857143
 2014-08-01 |      2 |     10 | 5.000000000000000000 | 2.857142857142857143 | 3.333333333333333333 | 2.857142857142857143
(8 rows)
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Vertica4VK
Newbie
Newbie
Posts: 6
Joined: Fri Aug 22, 2014 2:10 am

Re: How to Group By -Runnig Total for Year to Date in VSQL

Post by Vertica4VK » Mon Aug 25, 2014 10:44 pm

Perfect - Thanks James...

I noticed i get divide by zero error for some of the calculation - how do i take care of that in this ?

Thanks

VK

Post Reply

Return to “New to Vertica SQL”