Re: grouping/clustering query

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.

Regards,
dave

On Sat, Oct 25, 2008 at 3:48 AM, Tony Wasson <ajwasson(at)gmail(dot)com> wrote:
> On Fri, Oct 24, 2008 at 10:24 AM, Tony Wasson <ajwasson(at)gmail(dot)com> wrote:
>> On Fri, Oct 24, 2008 at 8:04 AM, Steve Midgley <science(at)misuse(dot)org> wrote:
>>> At 11:28 AM 10/23/2008, Joe wrote:
>>>>
>>>> Steve Midgley wrote:
>>>>>>
>>>>>> # (invoiceid, txid)
>>>>>> (A, 1)
>>>>>> (A, 3)
>>>>>> (B, 1)
>>>>>> (B, 2)
>>>>>> (C, 5)
>>>>>> (D, 6)
>>>>>> (D, 7)
>>>>>> (E, 8)
>>>>>> (F, 8)
>>>>>>
>>>>>> For journalling, I need to group/cluster this together. Is there a SQL
>>>>>> query that can generate this output:
>>>>>>
>>>>>> # (journal: invoiceids, txids)
>>>>>> [A,B] , [1,2,3]
>>>>>> [C], [5]
>>>>>> [D], [6,7]
>>>>>> [E,F], [8]
>>>>>
>>>>> Hi Dave,
>>>>>
>>>>> I'm not following the logic here. A has 1,3 and B has 1,2. So why does
>>>>> the first line print:
>>>>>
>>>>>> [A,B] , [1,2,3]
>>>>>
>>>>> What's the rule that tells the query to output this way? Is it that all
>>>>> of B's values are between A's values?
>>>>
>>>> From a purely accounting standpoint, since transaction 1 was applied to
>>>> both invoices A and B, you need to group the invoices so that you can
>>>> compare total invoiced against total paid.
>>>
>>> I tinkered around briefly but didn't come up with a good idea, but I bet
>>> someone on this list can. However, I did create a CREATE script for your
>>> table design which, in my experience, makes it more likely that a real
>>> expert will take on your problem..
>>>
>>> Hope this helps,
>>>
>>> Steve
>>>
>>> DROP TABLE IF EXISTS trans;
>>>
>>> CREATE TABLE trans
>>> (
>>> id serial NOT NULL,
>>> inv_id character varying,
>>> tx_id character varying,
>>> CONSTRAINT pk_id PRIMARY KEY (id)
>>> )
>>> WITH (OIDS=FALSE);
>>>
>>> insert into trans (inv_id, tx_id) values('A','1');
>>> insert into trans (inv_id, tx_id) values('A','3');
>>> insert into trans (inv_id, tx_id) values('B','1');
>>> insert into trans (inv_id, tx_id) values('B','2');
>>> insert into trans (inv_id, tx_id) values('C','5');
>>> insert into trans (inv_id, tx_id) values('D','6');
>>> insert into trans (inv_id, tx_id) values('D','7');
>>> insert into trans (inv_id, tx_id) values('E','8');
>>> insert into trans (inv_id, tx_id) values('F','8');
>
>
> Here's a stab at a custom aggregate attempting to explain what's going
> on inside.
>
> CREATE OR REPLACE FUNCTION varchar_array_accum_unique (x VARCHAR[], y
> VARCHAR) RETURNS VARCHAR[] AS $$
> DECLARE
> res VARCHAR[];
> BEGIN
> RAISE NOTICE 'input state is %',x;
> RAISE NOTICE 'input variable is %',y;
> IF x = '{}' THEN
> RAISE NOTICE 'x is empty, returning input variable %',y;
> res[1] := y;
> ELSE
> RAISE NOTICE 'input array is not empty, checking if input
> variable is a member %',y;
> res := x;
> IF y = ANY(res) THEN
> RAISE NOTICE 'y is already in array %, skipping',res;
> ELSE
> res := array_append(res, y);
> RAISE NOTICE 'appending input variable %',y;
> END IF;
> END IF;
> RETURN res;
> END
> $$ LANGUAGE plpgsql STRICT;
>
> DROP AGGREGATE array_accum_unique(VARCHAR);
> CREATE AGGREGATE array_accum_unique
> (
> basetype = VARCHAR
> , sfunc = varchar_array_accum_unique
> , stype = VARCHAR[]
> , initcond = '{}'
> );
>
> SELECT array_accum_unique(inv_id) AS invoiceids
> , array_accum_unique(tx_id) AS transactionids
> FROM (
> SELECT tx_id, inv_id
> FROM trans
> WHERE inv_id IN (
> SELECT inv_id
> FROM trans
> WHERE id IN (
> SELECT id FROM trans
> WHERE tx_id=1
> )
> )
> ORDER BY tx_id, inv_id
> ) AS ss
> ;
>
>
> Returns this result for transaction id 1.
>
> invoiceids | transactionids
> ------------+----------------
> {A,B} | {1,2,3}
> (1 row)
>
> Hope this helps!
> Tony
>