Re: reduce many loosely related rows down to one - Mailing list pgsql-sql

On 5/28/2013 11:04 AM, Torsten Grust wrote:
> On 25 May 2013, at 9:19, Bill MacArthur wrote (with possible deletions):
>> [...]
>> select * from test;
>>
>> id | rspid | nspid | cid | iac | newp | oldp |   ppv   |  tppv
>> ----+-------+-------+-----+-----+------+------+---------+---------
>> 1 |     2 |     3 |   4 | t   |      |      |         |
>> 1 |     2 |     3 |     |     |  100 |      |         |
>> 1 |     2 |     3 |     |     |      |  200 |         |
>> 1 |     2 |     3 |     |     |      |      |         | 4100.00
>> 1 |     2 |     3 |     |     |      |      |         | 3100.00
>> 1 |     2 |     3 |     |     |      |      | -100.00 |
>> 1 |     2 |     3 |     |     |      |      |  250.00 |
>> 2 |     7 |     8 |   4 |     |      |      |         |
>> (8 rows)
>>
>> -- I want this result (where ppv and tppv are summed and the other distinct values are boiled down into one row)
>> -- I want to avoid writing explicit UNIONs that will break if, say the "cid" was entered as a discreet row from the
rowcontaining "iac"
 
>> -- in this example "rspid" and "nspid" are always the same for a given ID, however they could possibly be absent for
agiven row as well
 
>>
>> id | rspid | nspid | cid | iac | newp | oldp |   ppv   |  tppv
>> ----+-------+-------+-----+-----+------+------+---------+---------
>> 1 |    2  |     3 |  4  | t   | 100  | 200  |  150.00  | 7200.00
>> 2 |    7  |     8 |  4  |     |      |      |    0.00  |    0.00
>
> One possible option could be
>
> SELECT id,
>         (array_agg(rspid))[1] AS rspid,        -- (1)
>         (array_agg(nspid))[1] AS nspid,
>         (array_agg(cid))[1]   AS cid,
>         bool_or(iac)          AS iac,          -- (2)
>         max(newp)             AS newp,         -- (3)
>         min(oldp)             AS oldp,         -- (4)
>         coalesce(sum(ppv), 0) AS ppv,
>         coalesce(sum(tppv),0) AS tppv
> FROM test
> GROUP BY id;
>
>
> This query computes the desired output for your example input.
>
> There's a caveat here: your description of the problem has been
> somewhat vague and it remains unclear how the query should
> respond if the functional dependency id -> rspid
> does not hold.  In this case, the array_agg(rspid)[1] in the line
> marked (1) will pick one among many different(!) rspid values.
> I don't know your scenario well enough to judge whether this would be
> an acceptable behavior.  Other possible behaviors have been
> implemented in the lines (2), (3), (4) where different aggregation
> functions are used to reduce sets to a single value (e.g., pick the
> largest/smallest of many values ...).
>
> Cheers,
>    --Torsten
>
Slick! Interesting usage scenarios for those aggregate functions array_agg and bool_or, one new to me and the other
rarelyused, and even for min and max which I never thought of using in this sense.
 

I tried not be be overbearing with descriptive details hoping that somebody would look at the simplistic case and offer
whatmight be considered an obscure way of implementing some of Postgres's handy features for an unusual problem. With a
littletweaking for the exact nature of the environment, I am good to go.
 

Thank you, Torsten!
Bill MacArthur