Обсуждение: aggregate function ?
hi again,
Some days ago I asked for help, nobody replied, and after trying to
do it in some way, I think aggregate function is the solution to my
problem, but I found difficult to understand how it works ...
What I want to do is accumulate the value for each row and add it to
the next:
a 100 100
b 50 150
c 25 175
My first approach was using a function/stored procedure, create a
cursor, loop through it, and insert the values in a temporary table,
that's why I was asking for those 'create table' before.
using a front-en application this is very easy, but I want to do it
directly in the server, and also it's a great exercice to learn more
about postgresql.
aggregate function
What I understand is:
I have to define the return-type of the aggregate function => float
The type of thing it aggregates => float (will be the value for each
row)
An initial value, probably 0
And a state-transition function to accumulate values (takes the
running total and the next value)
The optionally finalisation function I think I don't need it ...
How I can pack all this info in a aggregate function ?
In the manuals there is this example:
CREATE AGGREGATE avg (
sfunc = float8_accum,
basetype = float8,
stype = float8[],
finalfunc = float8_avg,
initcond = ’{0,0}’
);
but if I use real data with this aggregate, it doesn't work: (note
that I change avg to test, also in the create aggregate)
GlobalGest=# select test(saldo_deure,saldo_haver,saldo) from comptes
limit 5;
ERROR: function test(numeric, numeric, numeric) does not exist at
character 8
HINT: No function matches the given name and argument types. You may
need to add explicit type casts.
thanks in advance,
regards,
raimon fernandez
--- Raimon Fernandez <coder@montx.com> wrote:
> hi again,
>
>
> Some days ago I asked for help, nobody replied, and after trying to
> do it in some way, I think aggregate function is the solution to my
> problem, but I found difficult to understand how it works ...
>
> What I want to do is accumulate the value for each row and add it to
> the next:
>
> a 100 100
> b 50 150
> c 25 175
>
One solution is to use a correlated sub-query:
SELECT A.letter_field, A.letter_value,
( SELECT sum( letter_value )
FROM Your_table AS B
WHERE B.letter_field <= A.letter_field ) AS value_sum
FROM Your_table AS A
ORDER BY A.letter_field;
Another solution is to use a join:
SELECT A.letter_field, A.letter_value,
sum( B.letter_value ) AS value_sum
FROM Your_table AS A
INNER JOIN Your_table AS B
ON A.letter_field <= B.letter_field
GROUP BY A.letter_field, A.letter_value,
ORDER BY A.letter_field;
There are a couple of good SQL books that are really good at teaching methods on how to construct
such queries if you are enterested.
Regards,
Richard Broersma Jr.
Begin forwarded message: > From: Raimon Fernandez <coder@montx.com> > Date: 16 maig 2007 01:04:10 GMT+02:00 > To: Richard Broersma Jr <rabroersma@yahoo.com> > Subject: Re: [NOVICE] aggregate function ? > > I see I wasn't clear enough ... > > All the records are from the same table, the letters were just row > data. > > So I should know the value of the row that is before the one I'm > going to fetch ... > > > thanks! > > raimon > > > On 15/05/2007, at 23:59, Richard Broersma Jr wrote: > >> >> --- Raimon Fernandez <coder@montx.com> wrote: >> >>> hi again, >>> >>> >>> Some days ago I asked for help, nobody replied, and after trying to >>> do it in some way, I think aggregate function is the solution to my >>> problem, but I found difficult to understand how it works ... >>> >>> What I want to do is accumulate the value for each row and add it to >>> the next: >>> >>> a 100 100 >>> b 50 150 >>> c 25 175 >>> >> One solution is to use a correlated sub-query: >> >> SELECT A.letter_field, A.letter_value, >> ( SELECT sum( letter_value ) >> FROM Your_table AS B >> WHERE B.letter_field <= A.letter_field ) AS value_sum >> FROM Your_table AS A >> ORDER BY A.letter_field; >> >> Another solution is to use a join: >> >> SELECT A.letter_field, A.letter_value, >> sum( B.letter_value ) AS value_sum >> FROM Your_table AS A >> INNER JOIN Your_table AS B >> ON A.letter_field <= B.letter_field >> GROUP BY A.letter_field, A.letter_value, >> ORDER BY A.letter_field; >> >> There are a couple of good SQL books that are really good at >> teaching methods on how to construct >> such queries if you are enterested. >> >> Regards, >> Richard Broersma Jr. >> >> >
> All the records are from the same table, ... True. But notice that the examples that I provided also referenced only one table. If you look closely, you will see that I am querying the table twice. Once ( with the alias name "A" ) to find your list of rows and once ( with the alias name "B" ) to find the running total. > ... the letters were just row > data. Yes, I expected that you were simplifying you data in the example that you used. However, since you were able to abstract you row data as single letters, conversely you should be able to extend the example I provided to fit your table's design. > So I should know the value of the row that is before the one I'm > going to fetch ... I do not understand you question here. Regards, Richard Broesma Jr.
Hi Richard,
Ok, now I understand ...
Any recomended good book for SQL ?
On 16/05/2007, at 01:57, Richard Broersma Jr wrote:
>> All the records are from the same table, ...
>
> True. But notice that the examples that I provided also referenced
> only one table. If you look
> closely, you will see that I am querying the table twice. Once
> ( with the alias name "A" ) to find
> your list of rows and once ( with the alias name "B" ) to find the
> running total.
ok,
>> ... the letters were just row
>> data.
>
> Yes, I expected that you were simplifying you data in the example
> that you used. However, since
> you were able to abstract you row data as single letters,
> conversely you should be able to extend
> the example I provided to fit your table's design.
>
>> So I should know the value of the row that is before the one I'm
>> going to fetch ...
ok
> I do not understand you question here.
Yes, that for getting the accumulate of line 2 (50) first I have to
know the accumulate of line 1 (75)
Maybe with this example is more clear ...
Table assenatments
column 1: oid
column 2: detail
column 3: value_d
column 4: value_h
column 5: (accumulate value_d)-(accumulate(value_h)
1 invoice 75 0 => 75
2 income 0 25 => 50
3 invoice 50 0 => 100
I changed the fields from mines, but as this table has more than
700.000 rows, I would like to put a LIMIT or WHERE clausule, but it
doesn't work ....
SELECT A.oid, A.detail, A.value_d, A.value_h
(sum( B.value_d )-sum(B.value_h)) AS value_sum
FROM assentaments AS A
INNER JOIN assentaments AS B
ON A.oid <= B.oid
GROUP BY A.oid, A.detail, A.value_d, A.value_h
ORDER BY A.oid
With this code it says: Error, Shcema 'a' doesn't exist ...
thanks for your help, really !
regards,
raimon fernandez
> Any recomended good book for SQL ? http://www.elsevier.com/wps/find/bookdescription.cws_home/706077/description#description http://www.elsevier.com/wps/find/bookdescription.cws_home/710075/description#description you can buy these books almost any where. However, I can only find the 2nd addition for the SQL puzzles book on this website. I recommend the 2nd addition of the first. > Yes, that for getting the accumulate of line 2 (50) first I have to > know the accumulate of line 1 (75) > > Maybe with this example is more clear ... > I changed the fields from mines, but as this table has more than > 700.000 rows, I would like to put a LIMIT or WHERE clausule, but it > doesn't work .... Well the number of rows will probably be a problem, since the running total ( B ) is going to have to scan most of the table for each row returned from your table ( A ). However, you can easily limit the rows returned by table ( A ): SELECT A.oid, A.detail, A.value_d, A.value_h sum( B.value_d - B.value_h) AS value_sum FROM Assentaments AS A INNER JOIN Assentaments AS B ON A.oid <= B.oid WHERE A.oid BETWEEN 1 AND 100 -- you will have to pick the appropriate values GROUP BY A.oid, A.detail, A.value_d, A.value_h ORDER BY A.oid; If ... sum( B.value_d - B.value_h) AS value_sum ... is not what you really want, we can force your original syntax, but we will have to reform you query a little. Also, getting it to work will probably hurt performance a bit more. > With this code it says: Error, Shcema 'a' doesn't exist ... I am not sure about this error. It doesn't make sense to me. Could you Copy/Paste the actual query with the associated error message? Regards, Richard Broersma Jr.
On 16/05/2007, at 03:43, Richard Broersma Jr wrote: >> Any recomended good book for SQL ? > > http://www.elsevier.com/wps/find/bookdescription.cws_home/706077/ > description#description > http://www.elsevier.com/wps/find/bookdescription.cws_home/710075/ > description#description > > you can buy these books almost any where. However, I can only find > the 2nd addition for the SQL > puzzles book on this website. I recommend the 2nd addition of the > first. thanks >> Yes, that for getting the accumulate of line 2 (50) first I have to >> know the accumulate of line 1 (75) >> >> Maybe with this example is more clear ... >> I changed the fields from mines, but as this table has more than >> 700.000 rows, I would like to put a LIMIT or WHERE clausule, but it >> doesn't work .... > > Well the number of rows will probably be a problem, since the > running total ( B ) is going to have > to scan most of the table for each row returned from your table > ( A ). However, you can easily > limit the rows returned by table ( A ): > > SELECT A.oid, A.detail, A.value_d, A.value_h > sum( B.value_d - B.value_h) AS value_sum > FROM Assentaments AS A > INNER JOIN Assentaments AS B > ON A.oid <= B.oid > WHERE A.oid BETWEEN 1 AND 100 -- you will have to pick the > appropriate values > GROUP BY A.oid, A.detail, A.value_d, A.value_h > ORDER BY A.oid; Yes, the rows selected from that table normally are between 100 and 4000 aprox. > If ... sum( B.value_d - B.value_h) AS value_sum ... is not what you > really want, we can force your original syntax, but we will have to > reform you query a little. Also, getting it to work will probably > hurt performance a bit more. yes, what I want really is the Sum(b.value_d) - sum(b.value_h) accumulate will be faster a function for this that this SELECT ... ? >> With this code it says: Error, Shcema 'a' doesn't exist ... > > I am not sure about this error. It doesn't make sense to me. > Could you Copy/Paste the actual > query with the associated error message? GlobalGest=# SELECT A.oid, A.concepte, A.deure, A.haver (sum( B.deure )-sum(B.haver)) AS value_sum FROM assentaments AS A INNER JOIN assentaments AS B ON A.oid <= B.oid WHERE A.numero=11189 GROUP BY A.oid, A.concepte, A.deure, A.haver ORDER BY A.oid; ERROR: schema "a" does not exist GlobalGest=# thanks! raimon
--- Raimon Fernandez <coder@montx.com> wrote:
>
> yes, what I want really is the Sum(b.value_d) - sum(b.value_h)
> accumulate
>
> will be faster a function for this that this SELECT ... ?
Using a custom function could be comparable to a pure SQL statement in performance. However,
sometimes function like this can hide details of the query from the PostgreSQL optimizer. If this
happens you will see poor query performance.
>
> GlobalGest=# SELECT A.oid, A.concepte, A.deure, A.haver
--Your are missing a comma here ^
> (sum( B.deure )-sum(B.haver)) AS value_sum
> FROM assentaments AS A
> INNER JOIN assentaments AS B
> ON A.oid <= B.oid
> WHERE A.numero=11189
> GROUP BY A.oid, A.concepte, A.deure, A.haver
> ORDER BY A.oid;
> ERROR: schema "a" does not exist
What version of PostgreSQL are you using?
Your version of the query will need to look like this:
SELECT A1.oid, A1.concepte, A1.deure, A1.haver,
(sum( A2.deure )-sum(A3.haver)) AS value_sum
FROM Assentaments AS A1
INNER JOIN Assentaments AS A2
ON A1.oid <= A2.oid
INNER JOIN Assentaments AS A3
ON A1.oid <= A3.oid
WHERE A1.numero=11189
GROUP BY A1.oid, A1.concepte, A1.deure, A1.haver
ORDER BY A1.oid;
Regards,
Richard Broersma Jr.
On 16/05/2007, at 14:03, Richard Broersma Jr wrote: > --- Raimon Fernandez <coder@montx.com> wrote: > >> >> yes, what I want really is the Sum(b.value_d) - sum(b.value_h) >> accumulate >> >> will be faster a function for this that this SELECT ... ? > > Using a custom function could be comparable to a pure SQL statement > in performance. However, > sometimes function like this can hide details of the query from the > PostgreSQL optimizer. If this > happens you will see poor query performance. ok, >> GlobalGest=# SELECT A.oid, A.concepte, A.deure, A.haver > --Your are missing a comma here ^ thanks ! >> (sum( B.deure )-sum(B.haver)) AS value_sum >> FROM assentaments AS A >> INNER JOIN assentaments AS B >> ON A.oid <= B.oid >> WHERE A.numero=11189 >> GROUP BY A.oid, A.concepte, A.deure, A.haver >> ORDER BY A.oid; >> ERROR: schema "a" does not exist > > What version of PostgreSQL are you using? 8.2 on OS X > > Your version of the query will need to look like this: > > SELECT A1.oid, A1.concepte, A1.deure, A1.haver, > (sum( A2.deure )-sum(A3.haver)) AS value_sum > FROM Assentaments AS A1 > INNER JOIN Assentaments AS A2 > ON A1.oid <= A2.oid > INNER JOIN Assentaments AS A3 > ON A1.oid <= A3.oid > WHERE A1.numero=11189 > GROUP BY A1.oid, A1.concepte, A1.deure, A1.haver > ORDER BY A1.oid; ok, GlobalGest=# SELECT count(*) FROM assentaments WHERE numero=11189; +-------+ | count | +-------+ | 10 | +-------+ 1 rows in set (0.00 sec) GlobalGest=# but If I send the code that you send me, it takes too much .... well, more than 20 minutes and still waiting ... regards, raimon
--- Raimon Fernandez <coder@montx.com> wrote:
> but If I send the code that you send me, it takes too much .... well,
> more than 20 minutes and still waiting ...
just for testing try this version again so see if it preforms much better, then see if the summing
results are correct according to what you need.
SELECT A1.oid, A1.concepte, A1.deure, A1.haver,
sum( COALESCE( A1.deure, 0 ) - COALESCE( A2.haver, 0 )) AS value_sum
FROM Assentaments AS A1
INNER JOIN Assentaments AS A2
ON A1.oid <= A2.oid
WHERE A1.numero=11189
GROUP BY A1.oid, A1.concepte, A1.deure, A1.haver
ORDER BY A1.oid;
On 16/05/2007, at 15:01, Richard Broersma Jr wrote:
>
> --- Raimon Fernandez <coder@montx.com> wrote:
>
>> but If I send the code that you send me, it takes too much .... well,
>> more than 20 minutes and still waiting ...
>
> just for testing try this version again so see if it preforms much
> better, then see if the summing
> results are correct according to what you need.
>
> SELECT A1.oid, A1.concepte, A1.deure, A1.haver,
> sum( COALESCE( A1.deure, 0 ) - COALESCE( A2.haver, 0 ))
> AS value_sum
> FROM Assentaments AS A1
> INNER JOIN Assentaments AS A2
> ON A1.oid <= A2.oid
> WHERE A1.numero=11189
> GROUP BY A1.oid, A1.concepte, A1.deure, A1.haver
> ORDER BY A1.oid;
ok, here are the results:
now I'm using PGadminIII ...
180108;"fra.reg.prop. L´HOSPITALET-4";381.19;0.00;-523787081.74
180109;"fra.reg.prop. L´HOSPITALET-4";121.54;0.00;-728296890.68
180110;"fra.reg.prop. L´HOSPITALET-4";146.82;0.00;-708385624.70
180111;"fra.reg.prop. L´HOSPITALET-4";0.00;1746.83;-824026048.58
181496;"fra.reg.prop. L´HOSPITALET-4";140.46;0.00;-712741517.72
181497;"fra.reg.prop. L´HOSPITALET-4";146.36;0.00;-708102800.88
181498;"fra.reg.prop. L´HOSPITALET-4";134.11;0.00;-717734460.74
181499;"fra.reg.prop. L´HOSPITALET-4";143.75;0.00;-710155193.05
181500;"fra.reg.prop. L´HOSPITALET-4";116.05;0.00;-731934295.60
181501;"fra.reg.prop. L´HOSPITALET-4";416.55;0.00;-495668390.15
-- Executing query:
SELECT A1.oid, A1.concepte, A1.deure, A1.haver,
sum( COALESCE( A1.deure, 0 ) - COALESCE( A2.haver, 0 ))
AS value_sum
FROM Assentaments AS A1
INNER JOIN Assentaments AS A2
ON A1.oid <= A2.oid
WHERE A1.numero=11189
GROUP BY A1.oid, A1.concepte, A1.deure, A1.haver
ORDER BY A1.oid;
Total query runtime: 58371 ms.
10 rows retrieved.
using postgreSQL from Navicat ...
GlobalGest=# SELECT A1.oid, A1.concepte, A1.deure, A1.haver,
sum( COALESCE( A1.deure, 0 ) - COALESCE( A2.haver, 0 ))
AS value_sum
FROM Assentaments AS A1
INNER JOIN Assentaments AS A2
ON A1.oid <= A2.oid
WHERE A1.numero=11189
GROUP BY A1.oid, A1.concepte, A1.deure, A1.haver
ORDER BY A1.oid;
+--------+-------------------------------+--------+---------
+---------------+
| oid | concepte | deure | haver |
value_sum |
+--------+-------------------------------+--------+---------
+---------------+
| 180108 | fra.reg.prop. L´HOSPITALET-4 | 381.19 | 0.00 |
-523787081.74 |
| 180109 | fra.reg.prop. L´HOSPITALET-4 | 121.54 | 0.00 |
-728296890.68 |
| 180110 | fra.reg.prop. L´HOSPITALET-4 | 146.82 | 0.00 |
-708385624.70 |
| 180111 | fra.reg.prop. L´HOSPITALET-4 | 0.00 | 1746.83 |
-824026048.58 |
| 181496 | fra.reg.prop. L´HOSPITALET-4 | 140.46 | 0.00 |
-712741517.72 |
| 181497 | fra.reg.prop. L´HOSPITALET-4 | 146.36 | 0.00 |
-708102800.88 |
| 181498 | fra.reg.prop. L´HOSPITALET-4 | 134.11 | 0.00 |
-717734460.74 |
| 181499 | fra.reg.prop. L´HOSPITALET-4 | 143.75 | 0.00 |
-710155193.05 |
| 181500 | fra.reg.prop. L´HOSPITALET-4 | 116.05 | 0.00 |
-731934295.60 |
| 181501 | fra.reg.prop. L´HOSPITALET-4 | 416.55 | 0.00 |
-495668390.15 |
+--------+-------------------------------+--------+---------
+---------------+
10 rows in set (49.94 sec)
GlobalGest=#
The value_sum is wrong, in the first row should be: 381.19 and go on ...
but maybe is a silly question, but I'm a really novice, i thought
this should be really-really faster ... near 50 seconds is too much ...
regards and really thanks for your help.
--- Raimon Fernandez <coder@montx.com> wrote:
> but maybe is a silly question, but I'm a really novice, i thought
> this should be really-really faster ... near 50 seconds is too much ...
>
Do worry, we are gett closer to a working query. Each round trip is providing more details that
will help us to craft a better query:
SELECT A1.oid, A1.concepte, A1.deure, A1.haver,
sum( COALESCE( A1.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 --this should only preform a sum on a specify numero
WHERE A1.numero=11189
GROUP BY A1.oid, A1.concepte, A1.deure, A1.haver
ORDER BY A1.oid;
hi again ...
GlobalGest=# SELECT A1.oid, A1.concepte, A1.deure, A1.haver,
sum( COALESCE( A1.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 --this should only preform a sum
on a specify numero
WHERE A1.numero=11189
GROUP BY A1.oid, A1.concepte, A1.deure, A1.haver
ORDER BY A1.oid;
GlobalGest-# ;
+--------+-------------------------------+--------+---------
+-----------+
| oid | concepte | deure | haver |
value_sum |
+--------+-------------------------------+--------+---------
+-----------+
| 180108 | fra.reg.prop. L´HOSPITALET-4 | 381.19 | 0.00 | 2065.07 |
| 180109 | fra.reg.prop. L´HOSPITALET-4 | 121.54 | 0.00 | -652.97 |
| 180110 | fra.reg.prop. L´HOSPITALET-4 | 146.82 | 0.00 | -572.27 |
| 180111 | fra.reg.prop. L´HOSPITALET-4 | 0.00 | 1746.83 | -1746.83 |
| 181496 | fra.reg.prop. L´HOSPITALET-4 | 140.46 | 0.00 | 842.76 |
| 181497 | fra.reg.prop. L´HOSPITALET-4 | 146.36 | 0.00 | 731.80 |
| 181498 | fra.reg.prop. L´HOSPITALET-4 | 134.11 | 0.00 | 536.44 |
| 181499 | fra.reg.prop. L´HOSPITALET-4 | 143.75 | 0.00 | 431.25 |
| 181500 | fra.reg.prop. L´HOSPITALET-4 | 116.05 | 0.00 | 232.10 |
| 181501 | fra.reg.prop. L´HOSPITALET-4 | 416.55 | 0.00 | 416.55 |
+--------+-------------------------------+--------+---------
+-----------+
10 rows in set (0.11 sec)
GlobalGest=#
now it's much faster !!!!!!!!
I'm guessing what's doing as the value_sum is the same as the deure
in some columns ...
thanks again!
raimon
On 16/05/2007, at 16:06, Richard Broersma Jr wrote:
>
> --- Raimon Fernandez <coder@montx.com> wrote:
>> but maybe is a silly question, but I'm a really novice, i thought
>> this should be really-really faster ... near 50 seconds is too
>> much ...
>>
>
> Do worry, we are gett closer to a working query. Each round trip
> is providing more details that
> will help us to craft a better query:
>
> SELECT A1.oid, A1.concepte, A1.deure, A1.haver,
> sum( COALESCE( A1.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 --this should only preform a sum
> on a specify numero
> WHERE A1.numero=11189
> GROUP BY A1.oid, A1.concepte, A1.deure, A1.haver
> ORDER BY A1.oid;
>
>
>
> ---------------------------(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
>
One last change...
--- Raimon Fernandez <coder@montx.com> wrote:
> hi again ...
>
> GlobalGest=# SELECT A1.oid, A1.concepte, A1.deure, A1.haver,
> sum( COALESCE( A1.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 --this should only preform a sum
This is only a comment and not needed ^
SELECT A1.oid, A1.concepte, A1.deure, A1.haver,
sum( COALESCE( A1.deure, 0 ) -
COALESCE( A2.haver, 0 )) AS value_sum
FROM Assentaments AS A1
INNER JOIN Assentaments AS A2
ON A1.oid >= A2.oid -- change "<=" to ">="
AND A1.numero = A2.numero
WHERE A1.numero=11189
GROUP BY A1.oid, A1.concepte, A1.deure, A1.haver
ORDER BY A1.oid;
Regards,
Richard Broersma Jr.
oops I just found a mistake in the query...
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.numero=11189
GROUP BY A1.oid, A1.concepte, A1.deure, A1.haver
ORDER BY A1.oid;
hi Richard,
On 16/05/2007, at 17:10, Richard Broersma Jr wrote:
> 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.numero=11189
> GROUP BY A1.oid, A1.concepte, A1.deure, A1.haver
> ORDER BY A1.oid;
GlobalGest=# 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.numero=11189
GROUP BY A1.oid, A1.concepte, A1.deure, A1.haver
ORDER BY A1.oid;
+--------+-------------------------------+--------+---------
+-----------+
| oid | concepte | deure | haver |
value_sum |
+--------+-------------------------------+--------+---------
+-----------+
| 180108 | fra.reg.prop. L´HOSPITALET-4 | 381.19 | 0.00 | 0.00 |
| 180109 | fra.reg.prop. L´HOSPITALET-4 | 121.54 | 0.00 | -381.19 |
| 180110 | fra.reg.prop. L´HOSPITALET-4 | 146.82 | 0.00 | -502.73 |
| 180111 | fra.reg.prop. L´HOSPITALET-4 | 0.00 | 1746.83 | -649.55 |
| 181496 | fra.reg.prop. L´HOSPITALET-4 | 140.46 | 0.00 | 1097.28 |
| 181497 | fra.reg.prop. L´HOSPITALET-4 | 146.36 | 0.00 | 956.82 |
| 181498 | fra.reg.prop. L´HOSPITALET-4 | 134.11 | 0.00 | 810.46 |
| 181499 | fra.reg.prop. L´HOSPITALET-4 | 143.75 | 0.00 | 676.35 |
| 181500 | fra.reg.prop. L´HOSPITALET-4 | 116.05 | 0.00 | 532.60 |
| 181501 | fra.reg.prop. L´HOSPITALET-4 | 416.55 | 0.00 | 416.55 |
+--------+-------------------------------+--------+---------
+-----------+
10 rows in set (0.01 sec)
GlobalGest=#
We (you) are very near !!!!!
The first value_sum must be the same as the same row (deure-haver) =>
381.19
The operation is always deure-haver, I don't know why here appears in
minus (-) ...
The last row, is the same the deure as the value_sum, but it's real
data in it's real operation, both sums in that case are equal, so
it's ok.
GlobalGest=# SELECT sum(deure),sum(haver) FROM assentaments WHERE
numero=11189;
+---------+---------+
| sum | sum(2) |
+---------+---------+
| 1746.83 | 1746.83 |
+---------+---------+
1 rows in set (0.01 sec)
GlobalGest=#
thanks for your time !
rai
If all else fails, look for the answer on google: http://www.sqlteam.com/item.asp?ItemID=3856 aparently "ON A1.oid <= A2.oid" should have been: "ON A1.oid >= A2.oid". 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.numero=11189 GROUP BY A1.oid, A1.concepte, A1.deure, A1.haver ORDER BY A1.oid;
Well, that's perfectly ... Now I'm going to try to understand how this work ... In the web page that you refer, it also shows how to do it with a temporal table/cursor, and I see it's faster there. You have more options if you need to do some extra operations, but I see how powerful SQL can be ... Would be possible with this code to start the sum_value with a previous value from another SELECT ? For example, SELECT SUM(deure)-SUM(haver) FROM assentaments WHERE data<2007-01-01; thanks again four your help, I really appreciated, really ! regards, raimon On 16/05/2007, at 17:45, Richard Broersma Jr wrote: > If all else fails, look for the answer on google: > http://www.sqlteam.com/item.asp?ItemID=3856 > > aparently "ON A1.oid <= A2.oid" should have been: > "ON A1.oid >= A2.oid". > > > 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.numero=11189 > GROUP BY A1.oid, A1.concepte, A1.deure, A1.haver > ORDER BY A1.oid; > > > >
--- Raimon Fernandez <coder@montx.com> wrote:
> Well, that's perfectly ...
>
> Now I'm going to try to understand how this work ...
>
> In the web page that you refer, it also shows how to do it with a
> temporal table/cursor, and I see it's faster there.
>
> You have more options if you need to do some extra operations, but I
> see how powerful SQL can be ...
Really, from a SQL pure-ist point of view, all operations on the database can be and SHOULD BE
preformed with SQL statements as the initial development effort. The only reason to change a
procedural solution (such as using a cursor) would be if you can achieve better performance by
using a procedure. However, I would not take the performance results from that web site
seriously. The are most likely not using PostgreSQL in there test. And PostgreSQL may favor one
of the query methods over the procedure. You will have to test for your self which solution is
the best for your environment.
>
> Would be possible with this code to start the sum_value with a
> previous value from another SELECT ?
>
> For example, SELECT SUM(deure)-SUM(haver) FROM assentaments WHERE
> data<2007-01-01;
Sure, you can achieve what you are looking for. However, you have to know exactly what you want
to achieve AND you have to know the predicates to use to achieve these results. As to can tell,
for the previous SQL link, there can be various solutions to a problem. So have performance
advantages over others. Some of the values that you could tinker with that would can the results
of the query would be:
...
AND A1.numero = A2.numero
WHERE A1.numero=11189
...
Regards,
Richard Broersma Jr.
On 16/05/2007, at 18:39, Richard Broersma Jr wrote: > > --- Raimon Fernandez <coder@montx.com> wrote: > >> Well, that's perfectly ... >> >> Now I'm going to try to understand how this work ... >> >> In the web page that you refer, it also shows how to do it with a >> temporal table/cursor, and I see it's faster there. >> >> You have more options if you need to do some extra operations, but I >> see how powerful SQL can be ... > > Really, from a SQL pure-ist point of view, all operations on the > database can be and SHOULD BE > preformed with SQL statements as the initial development effort. > The only reason to change a > procedural solution (such as using a cursor) would be if you can > achieve better performance by > using a procedure. However, I would not take the performance > results from that web site > seriously. The are most likely not using PostgreSQL in there > test. And PostgreSQL may favor one > of the query methods over the procedure. You will have to test for > your self which solution is > the best for your environment. I'll try to do it as an exercise, and because I'm a programmer, I can see the solution easier from my point of view, but that doesn't mean that my solution will be better ... >> Would be possible with this code to start the sum_value with a >> previous value from another SELECT ? >> >> For example, SELECT SUM(deure)-SUM(haver) FROM assentaments WHERE >> data<2007-01-01; > > Sure, you can achieve what you are looking for. However, you have > to know exactly what you want > to achieve AND you have to know the predicates to use to achieve > these results. As to can tell, > for the previous SQL link, there can be various solutions to a > problem. So have performance > advantages over others. I'm going to use this way: the user enter two dates, and I perform the query with the date field instead of the numero field. so I'would have to query first from the very first row of data till the first data entered by the user, and store that value as the initial sum_value, and later apply the code you sent me, that works great ... > Some of the values that you could tinker with that would can the > results > of the query would be: > > ... > AND A1.numero = A2.numero > WHERE A1.numero=11189 > ... I don't understand this ... as a conclusion, I'll need a really good book and plenty of time to play and work with SQL ... regards and many thanks ! raimon
Hello again,
Is this the correct way to add a number to each sum_value ?
SELECT A1.oid, A1.concepte, A1.deure, A1.haver,
(SELECT deure FROM assentaments WHERE oid=180108)+ 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.numero=11189
GROUP BY A1.oid, A1.concepte, A1.deure, A1.haver
ORDER BY A1.oid;
I think it's not the correct way, as I believe it SELECTS the value
for each row, so it's getting longer ...
I'm trying also with a SELECT INTO target select_expressions
FROM ...; but not found the right way yet ...
thanks again,
raimon
On 16/05/2007, at 18:54, Raimon Fernandez wrote:
>
> On 16/05/2007, at 18:39, Richard Broersma Jr wrote:
>
>>
>> --- Raimon Fernandez <coder@montx.com> wrote:
>>
>>> Well, that's perfectly ...
>>>
>>> Now I'm going to try to understand how this work ...
>>>
>>> In the web page that you refer, it also shows how to do it with a
>>> temporal table/cursor, and I see it's faster there.
>>>
>>> You have more options if you need to do some extra operations, but I
>>> see how powerful SQL can be ...
>>
>> Really, from a SQL pure-ist point of view, all operations on the
>> database can be and SHOULD BE
>> preformed with SQL statements as the initial development effort.
>> The only reason to change a
>> procedural solution (such as using a cursor) would be if you can
>> achieve better performance by
>> using a procedure. However, I would not take the performance
>> results from that web site
>> seriously. The are most likely not using PostgreSQL in there
>> test. And PostgreSQL may favor one
>> of the query methods over the procedure. You will have to test
>> for your self which solution is
>> the best for your environment.
>
> I'll try to do it as an exercise, and because I'm a programmer, I
> can see the solution easier from my point of view, but that doesn't
> mean that my solution will be better ...
>
>
>>> Would be possible with this code to start the sum_value with a
>>> previous value from another SELECT ?
>>>
>>> For example, SELECT SUM(deure)-SUM(haver) FROM assentaments WHERE
>>> data<2007-01-01;
>>
>> Sure, you can achieve what you are looking for. However, you have
>> to know exactly what you want
>> to achieve AND you have to know the predicates to use to achieve
>> these results. As to can tell,
>> for the previous SQL link, there can be various solutions to a
>> problem. So have performance
>> advantages over others.
>
> I'm going to use this way:
>
> the user enter two dates, and I perform the query with the date
> field instead of the numero field.
>
> so I'would have to query first from the very first row of data till
> the first data entered by the user, and store that value as the
> initial sum_value, and later apply the code you sent me, that works
> great ...
>
>
>
>> Some of the values that you could tinker with that would can the
>> results
>> of the query would be:
>>
>> ...
>> AND A1.numero = A2.numero
>> WHERE A1.numero=11189
>> ...
>
> I don't understand this ...
>
>
> as a conclusion, I'll need a really good book and plenty of time to
> play and work with SQL ...
>
> regards and many thanks !
>
> raimon
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
--- Raimon Fernandez <coder@montx.com> wrote:
> Is this the correct way to add a number to each sum_value ?
> (SELECT deure FROM assentaments WHERE oid=180108)+ sum
^^^^^^
One question why did you choose oid=180108 to identify the row to use for adding to your sum. Is
there some sort of hidden relationship here that you can use craft a better select query?
Basically, the question to ask is, what is the relationship between the rolling Sum and the
initial "shifting" value.
Regards,
Richard Broersma Jr.
Hi, I choose because it's an easy example, normally I would change for a Date selection and another field. The user selects a compte and a date interval, for example: compte: 572 0000 01 date init: 15/01/2007 date end: 30/01/2007 The initial sum, would be the compte field plus a date init of the year, in that case, 01/01/2007 to the date init, 15/01/2007. Here we'll get the SUM of the two values (deure-haver), and this would be the initial value of the sum_value to add, so the code would be: SELECT SUM(deure)-SUM(haver) FROM assentaments WHERE assentaments.compte='572000001' and assentaments.data >='2007-01-15' and assentaments.data <='2007-01-30'; Regards, rai On 18/05/2007, at 06:49, Richard Broersma Jr wrote: > > --- Raimon Fernandez <coder@montx.com> wrote: >> Is this the correct way to add a number to each sum_value ? > >> (SELECT deure FROM assentaments WHERE oid=180108)+ sum > ^^^^^^ > One question why did you choose oid=180108 to identify the row to > use for adding to your sum. Is > there some sort of hidden relationship here that you can use craft > a better select query? > Basically, the question to ask is, what is the relationship between > the rolling Sum and the > initial "shifting" value. > > Regards, > Richard Broersma Jr. > > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings >
--- Raimon Fernandez <coder@montx.com> wrote:
>
> compte: 572 0000 01
> date init: 15/01/2007
> date end: 30/01/2007
>
> The initial sum, would be the compte field plus a date init of the
> year, in that case, 01/01/2007 to the date init, 15/01/2007. Here
> we'll get the SUM of the two values (deure-haver), and this would be
> the initial value of the sum_value to add, so the code would be:
>
> SELECT SUM(deure)-SUM(haver) FROM assentaments WHERE
> assentaments.compte='572000001' and assentaments.data >='2007-01-15'
> and assentaments.data <='2007-01-30';
So if I understand you correctly:
SELECT oid, concepte, deure, haver,
delta_sum + ( SELECT SUM( COALESCE( A3.deure ) - COALESCE ( A3.haver ) )
FROM Assentaments A3
WHERE A3.compte = '572000001'
AND A1.data BETWEEN '2007-01-15' AND '2007-01-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.numero = A2.numero
WHERE A1.numero=11189
GROUP BY A1.oid, A1.concepte, A1.deure, A1.haver
ORDER BY A1.oid ) AS Summed_Assentaments( oid, concepte, deure, haver, delta_sum );
Regards,
Richard Broersma Jr.