How get AVG by Date

Buchegger_Sebas

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.

Daniel_Leybovic

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;

Daniel_Leybovic

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)

Buchegger_Sebas

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)

Daniel_Leybovic

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)

Buchegger_Sebas

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."

Daniel_Leybovic

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 ()

Daniel_Leybovic

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

Buchegger_Sebas

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

Daniel_Leybovic

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)