How get AVG by Date

Hi!

Vertica rules

https://my.vertica.com/docs/7.0.x/HTML/index.htm#Authoring/SQLReferenceManual/Functions/Analytic/AVG...

AVG [Analytic]

Computes an average of an expression in a group within a window.

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;

Example by minutes (did mistake in dates - all same hour:) ). Also I simplified a MINUTEs extraction. You should use in DATE_TRUNC('HOUR', ...)

dbadmin=> create table t (tm timestamp, val int);
CREATE TABLE
dbadmin=> copy t from stdin direct;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 2000-01-01 00:00:00|2
>> 2000-01-01 00:00:10|3
>> 2000-01-01 00:00:20|5
>> 2000-01-01 00:01:00|1
>> 2000-01-01 00:01:10|2
>> 2000-01-01 00:01:40|9
>> dbadmin=> create table t (tm timestamp, val int);
CREATE TABLE
dbadmin=> copy t from stdin direct;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 2000-01-01 00:00:00|2
>> 2000-01-01 00:00:10|3
>> 2000-01-01 00:00:20|5
>> 2000-01-01 00:01:00|1
>> 2000-01-01 00:01:10|2
>> 2000-01-01 00:01:40|9
>> \.
#46;

dbadmin=> select distinct minute(tm), avg(val) over (partition by minute(tm)) from t;
 minute |     ?column?     
--------+------------------
      0 | 3.33333333333333
      1 |                4
(2 rows)

dbadmin=> select distinct date_trunc('MINUTE', tm), avg(val) over (partition by date_trunc('MINUTE', tm)) from t;
     date_trunc      |     ?column?     
---------------------+------------------
 2000-01-01 00:00:00 | 3.33333333333333
 2000-01-01 00:01:00 |                4
(2 rows)

Thanks again.
One more question.
How i can give the column a name? like "

     date_trunc      |     test     
---------------------+------------------
 2000-01-01 00:00:00 | 3.33333333333333
 2000-01-01 00:01:00 |                4
(2 rows)

No comments O_o, just read this: http://www.sql-tutorial.net/SQL-Aliases.asp

daniel=> select * from test limit 1;
 id | c 
----+---
  1 | x
(1 row)

daniel=> select id as COLUMN_ALIAS_1, c as COLUMN_ALIAS_2 from test limit 1;
 COLUMN_ALIAS_1 | COLUMN_ALIAS_2 
----------------+----------------
              1 | x
(1 row)

daniel=> select avg(id) over () as COLUMN_ALIAS_1, c as COLUMN_ALIAS_2 from test limit 1;
  COLUMN_ALIAS_1  | COLUMN_ALIAS_2 
------------------+----------------
 1.48387096774194 | x
(1 row)

I tried it already thats why I asked because it didn't work

"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

AVG(col) over (partition_clause order_clause window_frame)

so put alias after over ()

and one more - Vertica has 2 types of AVG function:

AVG [Aggregate]

Computes the average (arithmetic mean) of an expression over a group of rows. It returns a DOUBLE PRECISION value for a floating-point expression. Otherwise, the return value is the same as the expression data type.

and

AVG [Analytic]

Computes an average of an expression in a group within a window.

Behavior Type

Immutable

Syntax

expression ) OVER (  ... [ window_partition_clause ]  ... [ window_order_clause ] ... [ window_frame_clause ] ) AVG ( 

when you writes :

AVG(...) AS SOME_ALIAS

you says that is an AVG AGGREGATE function, while you need analytical.

Welcome to Vertica analytical world

oh I thought this go over all columns but if not it makes sense to but it after over

yeap, you need somehow to identify AVG analytical and aggregative, so it is a syntax
(it by ANSI standard: http://users.atw.hu/sqlnut/sqlnut2-chp-4-sect-3.html)