Thread: Balance Calculation

Balance Calculation

From
"Muhyiddin A.M Hayat"
Date:
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
 
 
 
 


 

Re: [despammed] Balance Calculation

From
Andreas Kretschmer
Date:
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    === 


Re: [despammed] Balance Calculation

From
Andreas Kretschmer
Date:
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    === 


Re: [despammed] Balance Calculation

From
LAMBEAU Bernard
Date:
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
>