PostgreSQL getting avg, min, max of latency value for each day

user_13019253 0
Viewed 198

I have a PostgreSQL table that shows period (date time) with an 15 second increment starting i.g. from 

2020-03-27 00:00:00

to

2020-03-27 23:59:45

Each day is identical

2020-03-28 00:00:00

to

2020-03-28 23:59:45

Each of those 15 second increments show a numeric value: Latency_us.

enter image description here

I would like to get the avg, min, max of each day in a separate column. How can I do that?

Here is my select query

create view s3fstj.pc_latency as
select TO_TIMESTAMP(start_time, 'YYYY/MM/DD HH24:MI:SS') as period, epoch, host_name, db_alias, database, db_host, db_host_ip, ip_port, latency_us, pc_domain 
from s3fstj.pc_dblatmonstat_latency

I'm also converting period string to timestamp.

postgresql sql
1 Answers

You would use window functions:

select TO_TIMESTAMP(start_time, 'YYYY/MM/DD HH24:MI:SS') as period,
       epoch, host_name, db_alias, database, db_host, db_host_ip, ip_port,
       latency_us, pc_domain,
       avg(latency_us) over (partition by start_time::date) as avg_day_latency_us,
       min(latency_us) over (partition by start_time::date) as min_day_latency_us,
       max(latency_us) over (partition by start_time::date) as max_day_latency_us
from s3fstj.pc_dblatmonstat_latency
user_11440350
Related