Обсуждение: Debet-Credit-Balance Calculation

I think you forget FOREIGN KEY:
transactions.trx_type_id -> trx_type.id

MAMH> Dear All,

MAMH> I have problem to calculation 
MAMH> balance from debet and credit.

MAMH> my transaction table:
...
MAMH> CREATE TABLE "public"."transactions" (
MAMH>   
MAMH> "id" SERIAL, 
MAMH>   "trx_timestamptz" TIMESTAMP(0) WITH TIME ZONE DEFAULT 
MAMH> ('now'::text)::timestamp(6) with time zone NOT NULL, 
MAMH>   "account" 
MAMH> CHAR(16) NOT NULL, 
MAMH>   "trx_type_id" INTEGER NOT NULL, 
MAMH>   
MAMH> "amount" NUMERIC(15,2) DEFAULT 0 NOT NULL, 
MAMH>   "uid" INTEGER NOT NULL,
MAMH>   CONSTRAINT "transactions_pkey" PRIMARY KEY("id")
MAMH> ) WITH 
MAMH> OIDS;...

MAMH> and transaction type :
...
MAMH> CREATE TABLE "public"."trx_type" (
MAMH>   "id" SERIAL,
MAMH>   "trx_name" VARCHAR(32), 
MAMH>   "t_type" CHAR(2), 
MAMH>   
MAMH> CONSTRAINT "trx_type_pkey" PRIMARY KEY("id"), 
MAMH>   CONSTRAINT 
MAMH> "trx_typei_check0" CHECK ((t_type = 'CR'::bpchar) OR (t_type = 
MAMH> 'DB'::bpchar))
MAMH> ) WITH OIDS;  

MAMH> ---------------------------------------------------------------------

MAMH> so, i'm using this query:

...
MAMH> ... query eat all my cpu process and take a long time ...

-- 
regards,Mihail                          mailto:m.nasedkin.perm@mail.ru

Oops! middink@indo.net.id ("Muhyiddin A.M Hayat") was seen spray-painting on a wall:
> 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)

What you're asking for is fairly much inherently exceedingly
expensive, and that's not really a PostgreSQL issue, it would be much
the same with any database.


The cost of the balance calculation for the first row may be 1.
For row 2, it's 1+1 = 2.
For row 3, it needs the balance from #2, so cost = 2+1 = 3.

Those add up, so the cost leaps thus:Individual costs Row    Aggregate                 1          1         1 + 2 = 3
      4     1 + 2 + 3 = 6         101 + 2 + 3 + 4 = 10         20and so forth...
 

The "naive" algorithm for this essentially results in the cost of the
query increasingly with O(n^3) where n is the number of elements in
the table.

You can get closer to O(n) by cacheing balances, but that will _not_
fall in an obvious way from an SQL query.

There is an easy way to do this; write a plpgsql set returning
function which adds the balance to the last column of the table.  That
query will always have a cost in both time and memory proportional to
the size of the table, and the memory cost may bite you as table size
grows...
-- 
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','gmail.com').
http://linuxdatabases.info/info/x.html
"It's like  a house   of cards  that   Godzilla  has  been  blundering
through."  -- Moon, describing how system messages work on ITS

> There is an easy way to do this; write a plpgsql set returning
> function which adds the balance to the last column of the table.  That
> query will always have a cost in both time and memory proportional to
> the size of the table, and the memory cost may bite you as table size
> grows...
> -- 

Can you give me some example function which adds the balance to the last 
column of the table.

Thanks. 

> There is an easy way to do this; write a plpgsql set returning
> function which adds the balance to the last column of the table.  That
> query will always have a cost in both time and memory proportional to
> the size of the table, and the memory cost may bite you as table size
> grows...
> -- 

Can you give me some example function which adds the balance to the last 
column of the table. or other query which same result and more faster

Thanks. 

middink@indo.net.id ("Muhyiddin A.M Hayat") writes:
>> There is an easy way to do this; write a plpgsql set returning
>> function which adds the balance to the last column of the table.  That
>> query will always have a cost in both time and memory proportional to
>> the size of the table, and the memory cost may bite you as table size
>> grows...
>> -- 
>
> Can you give me some example function which adds the balance to the
> last column of the table.
>
> Thanks.

No time, but here is a link to a useful article on SRFs.

<http://techdocs.postgresql.org/guides/SetReturningFunctions>

Once you know how to do an SRF, you should be able to build the one
you need.
-- 
(format nil "~S@~S" "cbbrowne" "acm.org")
http://www.ntlug.org/~cbbrowne/sap.html
Rules of the Evil Overlord #78.  "I will not tell my Legions of Terror
"And he must  be taken alive!" The command will be:  ``And try to take
him alive if it is reasonably practical.''"
<http://www.eviloverlord.com/>

-----Original Message-----
From: Muhyiddin A.M Hayat [mailto:middink@indo.net.id]
Sent: Tuesday, April 19, 2005 10:18 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] Debet-Credit-Balance Calculation

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")
) 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.id <= trans.id)
    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.

 

Thanks