From: Torsten Grust <torsten.grust@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: reduce many loosely related rows down to one
Date: Tue, 28 May 2013 17:07:35 +0200
Message-ID: <5ED3E628-D3B4-44BE-992A-568B7FD42996@gmail.com> (raw)
In-Reply-To: <51A0660A.2010806@dhs-club.com>
References: <51A0660A.2010806@dhs-club.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
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 row containing "iac"
> -- in this example "rspid" and "nspid" are always the same for a given
> ID, however they could possibly be absent for a given 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
--
| Torsten "Teggy" Grust
| Torsten.Grust@gmail.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: torsten.grust@gmail.com
Subject: Re: reduce many loosely related rows down to one
In-Reply-To: <5ED3E628-D3B4-44BE-992A-568B7FD42996@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox