The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
How get AVG by Date
Hello,
I need to get the AVG from my data grouped by date.
Example:
DATA:
Timestamp ; float ; float ; float
06.02.2014 19:40:00;12611.63;9752.76;8699.21;
06.02.2014 19:40:00;12584.78;9732.45;8758.79;
06.02.2014 19:40:01;12626.25;9716.77;8756.91;
06.02.2014 19:40:01;12623.77;9773.17;8731.25;
06.02.2014 19:40:02;12567.35;9718.18;8578.38;
06.02.2014 19:40:02;12575.7;9751.16;8129.44;
Now i want to group the data by the date in seconds and calculate the AVG:
Result should be like that
06.02.2014 19:40:00;12597,5; ...
06.02.2014 19:40:01;12624,5; ...
06.02.2014 19:40:02;12571; ...
How i do this in Vertica
In MSSQL I made it like that Screenshot.
(There the sql statement calculate the AVG for data grouped by hours)
I need this for seconds, minutes, hours, days
Thanks for help.
0
Comments
Vertica rules
https://my.vertica.com/docs/7.0.x/HTML/index.htm#Authoring/SQLReferenceManual/Functions/Analytic/AVG... PS
For this question actually it isn't required for answer, but as a good practice post also a Vertica version, because Vertica do a big steps from version to version;
One more question.
How i can give the column a name? like "
"An error occurred when executing the SQL command:select distinct year(Datum), month(Datum), day(Datum),hour(Datum), minute(Datum), avg(Power1) as power_1 over (partition...
[Vertica][VJDBC](4856) ERROR: Syntax error at or near "over" [SQL State=42601, DB Errorcode=4856]
Execution time: 0s
1 statement(s) failed.
"
"select distinct year(Datum), month(Datum), day(Datum),hour(Datum), minute(Datum), (avg(Power1) as power1) over (partition by year(Datum), month(Datum), day(Datum),hour(Datum), minute(Datum)) from Daten_ms;"
"An error occurred when executing the SQL command:select distinct year(Datum), month(Datum), day(Datum),hour(Datum), minute(Datum), (avg(Power1) as power1) over (partitio...
[Vertica][VJDBC](4856) ERROR: Syntax error at or near "as" [SQL State=42601, DB Errorcode=4856]
Execution time: 0.01s
1 statement(s) failed."
1. Take a look on my last example.
2. By definition alias come after an expression/column, in this case expression is so put alias after over ()
Welcome to Vertica analytical world
(it by ANSI standard: http://users.atw.hu/sqlnut/sqlnut2-chp-4-sect-3.html)