Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2010
    Posts
    40

    Unanswered: help converting rows into pivot

    Hi,
    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.

    Hour, Element, PeakCallsInProgress, MinBandwidthAvailable

    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

    elementname, CallsInProgress, BandwidthAvailable, Hour
    --------------------------------------------------------
    EL1, 52, null , 2011-5-31 12:00
    EL1, null , 12 , 2011-5-31 12:00

    The max/min converts it into (again I think I could use either max or min to collapse them actually)

    elementname, CallsInProgress, BandwidthAvailable, Hour
    --------------------------------------------------------
    EL1, 52, 12, 2011-5-31 12:00
    EL1, 51, 13, 2011-5-31 12:00

    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

  2. #2
    Join Date
    Feb 2010
    Posts
    40
    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.

    elementname, CallsInProgress, BandwidthAvailable, Hour
    --------------------------------------------------------
    EL1, 52, 12, 2011-5-31 12:00
    EL1, 51, 13, 2011-5-31 12:00

    when I would expect just
    elementname, CallsInProgress, BandwidthAvailable, Hour
    --------------------------------------------------------
    EL1, 52, 12, 2011-5-31 12:00

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •