I'm trying to summarize some data which is stored as rows/columns and name/value pairs using standard postgresql capabilities (not special functions).
I think a case statement should work but am not sure my logic is sound.
The rows are as follows:
Timestamp, Element, Type, Value
The 'Type' can be one of several strings but I am really interested only in 'CallsInProgress' and 'BandwidthAvailable'. The 'Element' is the thing that has these properties and there can be several. The timestamp is down to a second.
The output I'm looking for is the peak CallsInProgress and min BandwidthAvailable (they should correspond to one another) every hour grouped by unique Element.
The approach I took was to first try and sort out the data using the case statement and then the max/min (probably doesn't matter) to reduce the extra rows and collapse the set; that corresponds to the inner sql query. If I don't do that, I'll have something like
Now, I still will have multiple rows with the same 'Hour' and different values which is why I need the outer 'group by' and 'max(temp.peakcallsinprogress)'. I'm assuming by just focusing on the max peakcallsinprogress, I can return the accompanying bandwidthavailable which will be a min.
Does this sound reasonable? Thanks in advance.
select temp.elementname, max(temp.peakcallsinprogress) as peakcallsinprogress, temp.minbwavailable, temp.hour from
(select sepia.elementname as elementname,
max(case when seca.type = 'CallsInProgress' then seca.value end) as peakcallsinprogress,
min(case when seca.type = 'BandwidthAvailable' then seca.value end) as minbwavailable,
date_trunc('hour', seca.sync_timestamp) as hour
from default_system_element_counter_audit seca
left join default_system_element_performance_info_audit sepia on sepia.audit_id = seca.systemperformanceproperty_id
where seca.sync_timestamp >= (NOW() - interval '24 day')
and sepia.elementtype = 'ELEMENT_LOCATION'
group by sepia.elementname, seca.sync_timestamp ) as temp
group by temp.elementname, temp.hour, temp.minbwavailable
order by temp.hour, temp.elementname
I should clarify that the problem I am seeing is the last or outer 'max(temp.peakcallsinprogress)' is not having the desired effect. I'm still seeing multiple rows returned for the same elementname, hour.