Thread: Re: [NOVICE] aggregate function ?
hello again,
well, now it's faster, maybe it was the internet ...
Now it took only 6 seconds for 3217 rows through internet, great time, but the value of the sume is wrong ...
86067;"registro prop. barcelona";0.00;3.01;-9000.00
86069;"reg.mercantil barcelona (maribel)";0.00;9.00;-9000.00
86071;"reg.mercantil barcelona (maribel)";0.00;3.00;-9000.00
86073;"reg.mercantil barcelona (maribel)";0.00;7.50;-9000.00
86075;"reg.mercantil barcelona (maribel)";0.00;29.45;-9000.00
86089;"mensajeria 19/11-21/11/02 Maribel";0.00;44.43;-9000.00
86092;"mensajeria 24/12-03/12/02 Maribel";0.00;153.11;-9000.00
...
SELECT SUM( COALESCE( A3.deure ) - COALESCE ( A3.haver ) )
FROM Assentaments A3
WHERE A3.clau_compte = '0257000000002'
AND A3.data BETWEEN '2006-01-01' AND '2006-06-30'
This select returns this: -9000.00, so the number is ok.
thanks again, really !
rai
----- Original Message -----
From: Richard Broersma Jr
[mailto:rabroersma@yahoo.com]
To: coder@montx.com,
pgsql-novice@postgresql.org
Sent: Fri, 18 May 2007 21:06:02 +0200
Subject:
Re: [NOVICE] aggregate function ?
> I found the typo in the query: by the way, it seems my version of postgresql
> is more verbose with
> it error message than yours.
> ...
> ERROR: missing FROM-clause entry in subquery for table "a1"
> LINE 5: AND A1.data BETWEEN '2006-01-01' AND...
> ...
>
> anyway here is the correction:
>
>
> SELECT oid, concepte, deure, haver,
> delta_sum + ( SELECT SUM( COALESCE( A3.deure ) - COALESCE ( A3.haver
> ) )
> FROM Assentaments A3
> WHERE A3.clau_compte = '0257000000002'
> AND A3.data BETWEEN '2006-01-01' AND '2006-06-30' )
> AS starting_Sum
> --This was A1 but should be A3^
> FROM( SELECT A1.oid, A1.concepte, A1.deure, A1.haver,
> sum( COALESCE( A2.deure, 0 ) -
> COALESCE( A2.haver, 0 )) AS value_sum
> FROM Assentaments AS A1
> INNER JOIN Assentaments AS A2
> ON A1.oid >= A2.oid
> AND A1.numero = A2.numero
> WHERE A1.clau_compte = '0257000000002'
> GROUP BY A1.oid, A1.concepte, A1.deure, A1.haver
> ORDER BY A1.oid ) AS Summed_Assentaments( oid, concepte, deure, haver,
> delta_sum );
>
> >
> > and here comes the error:
> >
> > ERROR: missing FROM-clause entry in subquery for table "a1"
> > estat SQL: 42P01
> > Caràcter: 254
> >
> >
> > I can send you in a private e-mail some rows in .txt if you want to try
> ...
> >
> > And no, there's no relation between numero and compte in my table, I used
> numero because for me
> > it was easier to use only one field that use one with to options, data and
> between, but the code
> > should look like it's now.
> >
> > Sorry for the inconvenience ...
> >
> >
> >
> > thanks again !
> >
> >
> >
> >
> >
> >
> > ----- Original Message -----
> > From: Richard Broersma Jr
> > [mailto:rabroersma@yahoo.com]
> > To: coder@montx.com,
> > pgsql-novice@postgresql.org
> > Sent: Fri, 18 May 2007 19:47:11 +0200
> > Subject:
> > Re: [NOVICE] aggregate function ?
> >
> >
> > >
> > > --- coder@montx.com wrote:
> > > >
> > > > ERROR: missing FROM-clause entry in subquery for table "a1"
> > > > estat SQL: 42P01
> > > > Caràcter: 254
> > > >
> > > >
> > > > This line ' WHERE A1.numero=11189 ' must change as this:
> > > >
> > > > 'WHERE A1.compte='572000001' as the values must be from the same
> 'compte'
> > >
> > > Yes, it sounds like there is a relationship in your datamodel that I am
> not
> > > able to precieve
> > > across emails. If there is a relationship between compte and numero,
> you
> > > should be able to
> > > connect this relationship up in the SQL select query.
> > >
> > > It is hard to know what the error is being produced by. can you send the
> > > actual query that you are
> > > posting and it resulting error message.
> > > Regards,
> > > Richard
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 1: if posting/reading through Usenet, please send an appropriate
> > > subscribe-nomail command to majordomo@postgresql.org so that your
> > > message can get through to the mailing list cleanly
> > >
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
Hello again,
Well, now it's working ...
SELECT oid, concepte, deure, haver,
delta_sum + ( SELECT SUM( COALESCE( A3.deure ) - COALESCE
( A3.haver ) )
FROM Assentaments A3
WHERE A3.clau_compte = '0257000000002'
AND A3.data BETWEEN '2006-01-01' AND
'2006-06-30' ) AS starting_Sum
FROM( SELECT A1.oid, A1.concepte, A1.deure, A1.haver,
sum( COALESCE( A2.deure, 0 ) -
COALESCE( A2.haver, 0 )) AS value_sum
FROM Assentaments AS A1
INNER JOIN Assentaments AS A2
ON A1.oid >= A2.oid
AND A1. clau_compte = A2. clau_compte -- this was
A1.numero=A2.numero but should be Ax.clau_compte
WHERE A1.clau_compte = '0257000000002'
GROUP BY A1.oid, A1.concepte, A1.deure, A1.haver
ORDER BY A1.oid ) AS Summed_Assentaments( oid, concepte,
deure, haver, delta_sum );
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 ?
Here is the explain:
---------------------------------------------------------
Subquery Scan summed_assentaments (cost=39706774.23..39784540.01
rows=3941 width=96)
InitPlan
-> Aggregate (cost=259.83..259.84 rows=1 width=28)
-> Bitmap Heap Scan on assentaments a3
(cost=181.43..259.78 rows=20 width=28)
Recheck Cond: ((data >= '2006-01-01'::date) AND
(data <= '2006-06-30'::date) AND (clau_compte =
'0257000000002'::bpchar))
-> BitmapAnd (cost=181.43..181.43 rows=20 width=0)
-> Bitmap Index Scan on data
(cost=0.00..75.48 rows=3941 width=0)
Index Cond: ((data >=
'2006-01-01'::date) AND (data <= '2006-06-30'::date))
-> Bitmap Index Scan on clau_compte
(cost=0.00..105.70 rows=3941 width=0)
Index Cond: (clau_compte =
'0257000000002'::bpchar)
-> GroupAggregate (cost=39706514.39..39784230.90 rows=3941
width=92)
-> Sort (cost=39706514.39..39719457.29 rows=5177160 width=92)
Sort Key: a1.oid, a1.concepte, a1.deure, a1.haver
-> Nested Loop (cost=105.70..38067432.51
rows=5177160 width=92)
Join Filter: (a1.oid >= a2.oid)
-> Index Scan using clau_compte on assentaments
a1 (cost=0.00..14345.11 rows=3941 width=120)
Index Cond: (clau_compte =
'0257000000002'::bpchar)
-> Bitmap Heap Scan on assentaments a2
(cost=105.70..9606.43 rows=3941 width=88)
Recheck Cond: ('0257000000002'::bpchar =
clau_compte)
-> Bitmap Index Scan on clau_compte
(cost=0.00..105.70 rows=3941 width=0)
Index Cond: ('0257000000002'::bpchar
= clau_compte)
thanks in advance!
regards,
raimon
--- 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.
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. >