pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedBalance Calculation
4+ messages / 3 participants
[nested] [flat]
* Balance Calculation
@ 2005-05-22 04:15 Muhyiddin A.M Hayat <middink@indo.net.id>
0 siblings, 1 reply; 4+ messages in thread
From: Muhyiddin A.M Hayat @ 2005-05-22 04:15 UTC (permalink / raw)
To: pgsql-sql
Dear All,
I have problem to calculation balance from debet and credit.
my transaction table:
id | trx_timestamptz | account | trx_type_id | amount
----+------------------------+------------------+-------------+-------------
3 | 2005-04-14 17:16:49+08 | 01.2010100.00002 | 1 | 1000000.00
4 | 2005-04-14 17:17:12+08 | 01.2010100.00002 | 1 | 50000.00
5 | 2005-04-14 17:17:12+08 | 01.2010100.00003 | 1 | 600000.00
6 | 2005-04-14 17:17:47+08 | 01.2010100.00002 | 2 | 7000.00
7 | 2005-04-16 00:32:50+08 | 01.2010100.00003 | 1 | 20000.00
11 | 2005-04-16 02:45:06+08 | 01.2010100.00002 | 1 | 100000.00
12 | 2005-04-16 02:46:02+08 | 01.2010100.00002 | 1 | 20000.00
13 | 2005-04-16 02:46:59+08 | 01.2010100.00002 | 2 | 163000.00
14 | 2005-04-16 02:50:17+08 | 01.2010100.00005 | 1 | 100000.00
15 | 2005-04-16 02:53:42+08 | 01.2010301.00001 | 1 | 100000.00
16 | 2005-04-16 02:57:22+08 | 01.2010100.00001 | 1 | 2000000.00
17 | 2005-04-16 23:56:44+08 | 01.2010200.00000 | 1 | 10000000.00
18 | 2005-04-17 18:58:57+08 | 01.2010100.00003 | 1 | 100000.00
19 | 2005-04-17 19:13:05+08 | 01.2010100.00002 | 1 | 100000.00
20 | 2005-04-17 19:13:45+08 | 01.2010100.00002 | 1 | 200000.00
21 | 2005-04-17 19:15:36+08 | 01.2010100.00002 | 1 | 50000.00
22 | 2005-04-17 19:17:17+08 | 01.2010100.00005 | 2 | 10000.00
23 | 2005-04-17 19:18:06+08 | 01.2010100.00004 | 1 | 200000.00
24 | 2005-04-17 21:45:31+08 | 01.2010100.00002 | 1 | 9000.00
25 | 2005-04-17 22:16:08+08 | 01.2010100.00006 | 1 | 100000.00
-------------------------------------------------------------------------------------------------------------------------
CREATE TABLE "public"."transactions" (
"id" SERIAL,
"trx_timestamptz" TIMESTAMP(0) WITH TIME ZONE DEFAULT ('now'::text)::timestamp(6) with time zone NOT NULL,
"account" CHAR(16) NOT NULL,
"trx_type_id" INTEGER NOT NULL,
"amount" NUMERIC(15,2) DEFAULT 0 NOT NULL,
"uid" INTEGER NOT NULL,
CONSTRAINT "transactions_pkey" PRIMARY KEY("id"),
CONSTRAINT "transactions_trx_type_id_fkey" FOREIGN KEY ("trx_type_id")
REFERENCES "public"."trx_type"("id")
ON DELETE CASCADE
ON UPDATE CASCADE
NOT DEFERRABLE
) WITH OIDS;
-------------------------------------------------------------------------------------------
and transaction type :
id | trx_name | t_type
----+----------+--------
1 | Credit | CR
2 | Debet | DB
---------------------------------------------------------------------
CREATE TABLE "public"."trx_type" (
"id" SERIAL,
"trx_name" VARCHAR(32),
"t_type" CHAR(2),
CONSTRAINT "trx_type_pkey" PRIMARY KEY("id"),
CONSTRAINT "trx_typei_check0" CHECK ((t_type = 'CR'::bpchar) OR (t_type = 'DB'::bpchar))
) WITH OIDS;
---------------------------------------------------------------------
so, i'm using this query:
SELECT
trans.id,
trans.trx_timestamptz,
trans.account,
trans.debet,
trans.credit
FROM
(
SELECT
transactions.id,
transactions.trx_timestamptz,
transactions.account,
CASE
WHEN trx_type.t_type = 'DB' THEN
transactions.amount
ELSE
0
END AS debet,
CASE
WHEN trx_type.t_type = 'CR' THEN
transactions.amount
ELSE
0
END AS credit
FROM
transactions
INNER JOIN trx_type ON (transactions.trx_type_id = trx_type.id)
) AS trans
result from above query :
id | trx_timestamptz | account | debet | credit
----+------------------------+------------------+-----------+-------------
3 | 2005-04-14 17:16:49+08 | 01.2010100.00002 | 0 | 1000000.00
4 | 2005-04-14 17:17:12+08 | 01.2010100.00002 | 0 | 50000.00
5 | 2005-04-14 17:17:12+08 | 01.2010100.00003 | 0 | 600000.00
6 | 2005-04-14 17:17:47+08 | 01.2010100.00002 | 7000.00 | 0
7 | 2005-04-16 00:32:50+08 | 01.2010100.00003 | 0 | 20000.00
11 | 2005-04-16 02:45:06+08 | 01.2010100.00002 | 0 | 100000.00
12 | 2005-04-16 02:46:02+08 | 01.2010100.00002 | 0 | 20000.00
13 | 2005-04-16 02:46:59+08 | 01.2010100.00002 | 163000.00 | 0
14 | 2005-04-16 02:50:17+08 | 01.2010100.00005 | 0 | 100000.00
15 | 2005-04-16 02:53:42+08 | 01.2010301.00001 | 0 | 100000.00
16 | 2005-04-16 02:57:22+08 | 01.2010100.00001 | 0 | 2000000.00
17 | 2005-04-16 23:56:44+08 | 01.2010200.00000 | 0 | 10000000.00
18 | 2005-04-17 18:58:57+08 | 01.2010100.00003 | 0 | 100000.00
19 | 2005-04-17 19:13:05+08 | 01.2010100.00002 | 0 | 100000.00
20 | 2005-04-17 19:13:45+08 | 01.2010100.00002 | 0 | 200000.00
21 | 2005-04-17 19:15:36+08 | 01.2010100.00002 | 0 | 50000.00
22 | 2005-04-17 19:17:17+08 | 01.2010100.00005 | 10000.00 | 0
23 | 2005-04-17 19:18:06+08 | 01.2010100.00004 | 0 | 200000.00
24 | 2005-04-17 21:45:31+08 | 01.2010100.00002 | 0 | 9000.00
25 | 2005-04-17 22:16:08+08 | 01.2010100.00006 | 0 | 100000.00
my problem, i would like to place balance in last field. i try using this query :
SELECT
trans.id,
trans.trx_timestamptz,
trans.account,
trans.debet,
trans.credit,
(SELECT
SUM(coalesce(credit,0)-coalesce(debet,0))
FROM
(
SELECT
transactions.id,
transactions.trx_timestamptz,
transactions.account,
CASE
WHEN trx_type.t_type = 'DB' THEN
transactions.amount
ELSE
0
END AS debet,
CASE
WHEN trx_type.t_type = 'CR' THEN
transactions.amount
ELSE
0
END AS credit
FROM
transactions
INNER JOIN trx_type ON (transactions.trx_type_id = trx_type.id)
) sub
WHERE
(sub.trx_timestamptz <= trans.trx_timestamptz)
AND
(sub.account = trans.account)
)
AS balance
FROM
(
SELECT
transactions.id,
transactions.trx_timestamptz,
transactions.account,
CASE
WHEN trx_type.t_type = 'DB' THEN
transactions.amount
ELSE
0
END AS debet,
CASE
WHEN trx_type.t_type = 'CR' THEN
transactions.amount
ELSE
0
END AS credit
FROM
transactions
INNER JOIN trx_type ON (transactions.trx_type_id = trx_type.id)
) AS trans
-----
everything is ok, but when record > 1000000 that query eat all my cpu process and take a long time, i have wait for 3 mimutes but query doesn't finish. (pgsql-8.0-1 running on Dual Xeon 2.8 and 2GB of RAM)
can you help me how to fix them.
Can I using function to fix them (how?)
Thanks
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [despammed] Balance Calculation
@ 2005-05-22 08:17 Andreas Kretschmer <akretschmer@despammed.com>
parent: Muhyiddin A.M Hayat <middink@indo.net.id>
0 siblings, 1 reply; 4+ messages in thread
From: Andreas Kretschmer @ 2005-05-22 08:17 UTC (permalink / raw)
To: pgsql-sql
am 22.05.2005, um 12:15:56 +0800 mailte Muhyiddin A.M Hayat folgendes:
Nice SQL ;-)
>
> everything is ok, but when record > 1000000 that query eat all my cpu
> process and take a long time, i have wait for 3 mimutes but query
I think, because for every record in your result is a full table scan
required to calculate the balance.
>
> can you help me how to fix them.
> Can I using function to fix them (how?)
It is possible to create a extra table for the balance and a trigger for
insert/update that calculates the balance for the transactions-table?
I think, this should solve your perfomance-problem.
Regards, Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47212, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [despammed] Balance Calculation
@ 2005-05-22 08:42 Andreas Kretschmer <akretschmer@despammed.com>
parent: Andreas Kretschmer <akretschmer@despammed.com>
0 siblings, 1 reply; 4+ messages in thread
From: Andreas Kretschmer @ 2005-05-22 08:42 UTC (permalink / raw)
To: pgsql-sql; +Cc: Andreas Kretschmer <akretschmer@despammed.com>
am 22.05.2005, um 10:17:53 +0200 mailte Andreas Kretschmer folgendes:
> > can you help me how to fix them.
> > Can I using function to fix them (how?)
>
> It is possible to create a extra table for the balance and a trigger for
> insert/update that calculates the balance for the transactions-table?
>
>
> I think, this should solve your perfomance-problem.
Sorry, this is not the solution for your problem, because this provide
only the actual balance for a single account, but not the history.
Mmh...
A extra table with the oid and the balance for every row in
transactions. Then you can do a index scan, but you need also a trigger
that insert every transaction in this table and calculate the balance.
Regards
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47212, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [despammed] Balance Calculation
@ 2005-05-22 09:03 LAMBEAU Bernard <blambeau@gmail.com>
parent: Andreas Kretschmer <akretschmer@despammed.com>
0 siblings, 0 replies; 4+ messages in thread
From: LAMBEAU Bernard @ 2005-05-22 09:03 UTC (permalink / raw)
To: Andreas Kretschmer <akretschmer@despammed.com>; pgsql-sql
Do you tried to create an index on the account column ?
I think it may help.
Regards
-------------------------------------------------------------------------------------
Bernard LAMBEAU
Département d'Ingénierie Informatique
Université Catholique de Louvain-La-Neuve (Belgium)
On 5/22/05, Andreas Kretschmer <akretschmer@despammed.com> wrote:
> am 22.05.2005, um 10:17:53 +0200 mailte Andreas Kretschmer folgendes:
> > > can you help me how to fix them.
> > > Can I using function to fix them (how?)
> >
> > It is possible to create a extra table for the balance and a trigger for
> > insert/update that calculates the balance for the transactions-table?
> >
> >
> > I think, this should solve your perfomance-problem.
>
> Sorry, this is not the solution for your problem, because this provide
> only the actual balance for a single account, but not the history.
>
> Mmh...
>
> A extra table with the oid and the balance for every row in
> transactions. Then you can do a index scan, but you need also a trigger
> that insert every transaction in this table and calculate the balance.
>
>
>
>
> Regards
> --
> Andreas Kretschmer (Kontakt: siehe Header)
> Heynitz: 035242/47212, D1: 0160/7141639
> GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
> === Schollglas Unternehmensgruppe ===
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: 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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-05-22 09:03 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-05-22 04:15 Balance Calculation Muhyiddin A.M Hayat <middink@indo.net.id>
2005-05-22 08:17 ` Andreas Kretschmer <akretschmer@despammed.com>
2005-05-22 08:42 ` Andreas Kretschmer <akretschmer@despammed.com>
2005-05-22 09:03 ` LAMBEAU Bernard <blambeau@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox