Re: aggregate function ? - Mailing list pgsql-novice

From Raimon Fernandez
Subject Re: aggregate function ?
Date
Msg-id 157693F0-A5A8-49BA-B2F7-A7861AF4FB13@montx.com
Whole thread Raw
In response to Re: aggregate function ?  (Richard Broersma Jr <rabroersma@yahoo.com>)
List pgsql-novice
ok, thanks for the clarification ...

I'll try to do it myself and see ... but also I would like to do it
in a function/stored procedure, as will be more clear for me ...

and in this way I'll compare the speed difference ...

my initial approach is:

create temporary table test as SELECT
oid,numero,data,concepte,deure,haver,(deure-haver) as saldo FROM
assentaments WHERE clau_compte='0257000000002';

create a cursor,

loop for each row and update the saldo field

and return the select * from test

still working ...


regards and thanks again !


raimon




On 21/05/2007, at 14:46, Richard Broersma Jr wrote:

>
> --- Raimon Fernandez <coder@montx.com> wrote:
>> This returns 3217 rows, and the value_sum is ok, but it takes too
>> long (89.45 sec)
>>
>> Can anyone confirm that it's doing for every row the
>> 'starting_sum' (first select), and if so, how to do it just once ?
>
> Yes, any sub-select in the Select expression list will be executed
> many times. To fix this, you
> will need to reform your query by pushing this sub-select down to
> the from clause.  Also you will
> need to add clau_compte to the SELECT expression list of both sub-
> Selects
>
>     SELECT <your expression list>, Summed_assentaments.delta_sum +
> Initialvalue.starting_sum
>       FROM ( <your first initial sub-select> ) AS
> Summed_Assentaments( oid, concepte,deure, haver,
> delta_sum, clau_compte )
> INNER JOIN ( <your initial value sub-select> ) AS Initialvalue
> ( starting_sum, clau_compte )
>         ON Summed_Assentaments.clau_compte = Initialvalue.clau_compte;
>
> I hope this can help to improve query time.
>
> Regards,
> Richard Broersma Jr.
>



pgsql-novice by date:

Previous
From: Richard Broersma Jr
Date:
Subject: Re: aggregate function ?
Next
From: Derrick Betts
Date:
Subject: Multiple row update with trigger