Fwd: grouping/clustering query

I forgot the list.

---------- Forwarded message ----------
From: Osvaldo Kussama <osvaldo.kussama@gmail.com>
Date: Mon, 27 Oct 2008 12:28:57 -0200
Subject: Re: [SQL] grouping/clustering query
To: David Garamond <davidgaramond@gmail.com>

2008/10/24, David Garamond <davidgaramond@gmail.com>:
> Tony, Joe, Steve,
>
> Thanks for the follow-ups. Yes, the problem is related to double-entry
> accounting, where one needs to balance total debit and credit
> (payments and invoices) in each journal/transaction.
>
> Due to time constraint, I ended up doing this in the client-side
> programming language, since I am nowhere near fluent in PLs. The
> algorithm should be simple (at least the "brute force" version), it's
> basically checking if each element of the pair (txid, invoiceid) is
> already mentioned in some journal and if it is, add the pair to the
> journal, otherwise create a new journal with that pair as the first
> entry. I believe this can easily be implemented in a PL. But still I
> wonder if there is some SQL incantation that can do the same without
> any PL.
>


Interesting problem.

I think there are no SQL-only solution.

Using arrays and PL/pgSQL function:
bdteste=# SELECT * FROM bar;aid | bid
-----+-----A   |   1A   |   3B   |   1B   |   2C   |   5D   |   6D   |   7E   |   8F   |   8
(9 registros)

bdteste=# CREATE OR REPLACE FUNCTION combina() RETURNS setof record AS $$
bdteste$# DECLARE
bdteste$#    res   record;
bdteste$#    res1  record;
bdteste$# BEGIN
bdteste$#    CREATE TEMP TABLE foobar(
bdteste$#       fbaid   text[],
bdteste$#       fbbid   int[])
bdteste$#    ON COMMIT DROP;
bdteste$#
bdteste$#    FOR res IN SELECT agr1, bid FROM (SELECT bid,
array_accum(aid) AS agr1 FROM bar
bdteste$#                  GROUP BY bid) b1 ORDER BY array_upper(agr1,
1) DESC, agr1 LOOP
bdteste$#       SELECT * INTO res1 FROM foobar WHERE fbaid @> res.agr1;
bdteste$#       IF NOT FOUND THEN
bdteste$#          INSERT INTO foobar VALUES (res.agr1, array[res.bid]);
bdteste$#       ELSE
bdteste$#          UPDATE foobar SET fbbid = array_append(fbbid,
res.bid) WHERE fbaid @> res.agr1;
bdteste$#       END IF;
bdteste$#    END LOOP;
bdteste$#
bdteste$#    RETURN QUERY SELECT * FROM foobar;
bdteste$# END;
bdteste$# $$ LANGUAGE PLPGSQL;
CREATE FUNCTION
bdteste=#
bdteste=# SELECT * FROM combina() AS(a text[], b int[]);  a   |    b
-------+---------{E,F} | {8}{A,B} | {1,3,2}{C}   | {5}{D}   | {7,6}
(4 registros)

Osvaldo

PS. - Aggregate array_accum defined at:
http://www.postgresql.org/docs/current/interactive/xaggr.html
- If you need sorted arrays use Andreas Kretschmer's function array_sort:
http://archives.postgresql.org/pgsql-general/2007-02/msg01534.php