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
How to Group By -Runnig Total for Year to Date in VSQL
Moderator: NorbertKrupa
-
- Newbie
- Posts: 6
- Joined: Fri Aug 22, 2014 2:10 am
-
- 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
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.
- JimKnicely
- 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
Maybe using analytic functions will help?
Example:
Note that I did the average two ways.
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)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
-
- Newbie
- Posts: 6
- Joined: Fri Aug 22, 2014 2:10 am
Re: How to Group By -Runnig Total for Year to Date in VSQL
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
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
Re: How to Group By -Runnig Total for Year to Date in VSQL
Hi!
[DELETED]
[DELETED]
Last edited by id10t on Wed May 06, 2015 4:46 pm, edited 1 time in total.
- JimKnicely
- 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
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
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
-
- Newbie
- Posts: 6
- Joined: Fri Aug 22, 2014 2:10 am
Re: How to Group By -Runnig Total for Year to Date in VSQL
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
I noticed i get divide by zero error for some of the calculation - how do i take care of that in this ?
Thanks
VK