0

I currently have a table containing raw temperature data that looks like this: enter image description here

I have written a query to find the min, max and avg of this data grouped by the date of the timestamp:

drop table solardb.weatherinfodaily;

select date_trunc('day',ts) as ts,
min("Air Temperature (Actual) (Degrees C)")*9/5 + 32 as "Daily Min Temp (F)",
avg("Air Temperature (Actual) (Degrees C)")*9/5 + 32 as "Daily Avg Temp (F)",
max("Air Temperature (Actual) (Degrees C)")*9/5 + 32 as "Daily Max Temp (F)"
into solardb.weatherinfodaily
from solardb.weatherdata
group by date_trunc('day',ts) 
order by date_trunc('day',ts);

select * from solardb.weatherinfodaily;

I would like to return the timestamp that is associated with the aggregate function and I have not figured out a way to do this yet.

i.e. the query would return:

ts, Daily Min Temp, timestamp from raw data table when the min temp happened, daily avg temp, daily max temp, timestamp from raw data when max temp happened.

How can I write such a query?

  • You mean you would like to return the timestamp at the time you created the records for the min(), avg() and max() values in the separate table? – hot2use Jul 25 '18 at 13:39
1

You need to self-join the results of your current query with the base table (twice) on the values of temperature and date. Your query might look something like this:

with agg (day, min_c, avg_c, max_c ) as (
    select date_trunc('day',ts) as ts,
    min("Air Temperature (Actual) (Degrees C)") as "Daily Min Temp (F)",
    avg("Air Temperature (Actual) (Degrees C)"),
    max("Air Temperature (Actual) (Degrees C)")
    from solardb.weatherdata
    group by date_trunc('day',ts) 
    order by date_trunc('day',ts)
)
select 
  agg.day as ts,
  agg.min_c*9/5 + 32  as "Daily Min Temp (F)",
  data_min.ts as "Time of minimum",
  agg.avg_c*9/5 + 32 as "Daily Avg Temp (F)",
  agg.max_c*9/5 + 32 as "Daily Max Temp (F)",
  data_max.ts as "Time of maximum"
from agg 
join solardb.weatherdata data_min 
  on agg.min_c = data_min."Air Temperature (Actual) (Degrees C)"
  and agg.day = date_trunc('day',data_min.ts)
join solardb.weatherdata data_max 
  on agg.max_c = data_max."Air Temperature (Actual) (Degrees C)"
  and agg.day = date_trunc('day',data_max.ts)

The above is not tested. I used the CTE to make logic more obvious, but one could use a simple subselect too.

1

You can use aggregates with windows functions to get the result. For example (I omit Celsius to Fahrenheit conversion for the sake of simplicity and readability ) :

with qr1 as
 (
    select a.* from 
    (
        select 
        ts,
        date_trunc('day',ts) as dt,
        temperature,
        min(temperature) over (partition by date_trunc('day',ts) )as min_temp ,
        avg(temperature) over (partition by date_trunc('day',ts) )as av_temp ,
        max(temperature) over (partition by date_trunc('day',ts) )as max_temp ,
        row_number()over (partition by date_trunc('day',ts) order by ts asc) as min_temp_rn,
        row_number()over (partition by date_trunc('day',ts) order by ts desc) as max_temp_rn

        from solardb.weatherdata
    )a 
    where a.min_temp_rn =1 or a.max_temp_rn = 1 
)
select 
dt as ts,
min_temp,av_temp,max_temp,
max(case when temperature=min_temp  then ts end) as min_temp_timestamp,
max(case when temperature=max_temp  then ts end) as max_temp_timestamp

from qr1

group by dt
order by dt

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.