Message-ID: <3EDB5F00.8080407@openratings.com>
Date: Mon, 02 Jun 2003 10:28:16 -0400
From: Dmitry Tkach <dmitry@openratings.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.2.1) Gecko/20021130
MIME-Version: 1.0
To: "Erik G. Burrows" <eburrows@erikburrows.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: SQL problem: bank account
References: <1054531815.13212.27.camel@griffin.jedi-group.com>
In-Reply-To: <1054531815.13212.27.camel@griffin.jedi-group.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

I am afraid, this looks even uglier then your second solution, but 
should work, and be quicker...

-- You need this to avoid having to rescan the whole table for each 
customerid every time and resort the results
create index customer_txstamp_idx on bank_account (customer_id, ts);

select ba.* from bank_account ba where
transaction_id = (select transaction_id from bank_account where 
customer_id = ba.customer_id order by customer_id desc, ts desc limit 1);

Now, note that, if you have lots of different customers in that table, 
it will still take a while to fetch them all (although, it should still 
be a lot quicker then half an hour) - in that case, you may consider 
either getting them one-by-one (by adding ... and customer_id=? to the 
above query) or using cursors...

I hope, it helps...

Dima


Erik G. Burrows wrote:

>It seems to me this is a simple problem, but the solution eludes me.
>
>I have a table:
>
>bank_account (
>  transaction_id int not null serial,
>  customer_id int not null references customer(id),
>  ts timestamp not null default now(),
>  amount float not null,
>  balance float not null,
>  primary key(transaction_id)
>)
>
>I need to get the most recent transaction for each customer. I need only
>the transaction ID, but the entire row would be best.
>
>
>
>I have two solutions, both of which are too slow for use in my
>interactive web-based interface:
>
>Solution1: Outer left self join:
>SELECT
>  ba1.*
>FROM
>  bank_account ba1
>  LEFT OUTER JOIN bank_account ba2
>    ON ba1.customer_id = ba2.customer_id AND ba1.ts < ba2.ts
>WHERE
>  ba2.ts IS NULL;
>
>This query works great on tables of less than a few thousand rows. For
>my 300k row table, it takes several hours.
>
>
>Solution2: max-concat trick
>SELECT
>  split_part(max(
>      extract(EPOCH from ts)::VARCHAR || '    ' ||
>      transaction_id::VARCHAR), '    ', 2)::INT
>FROM
>  bank_account
>GROUP BY
>  customer_id
>
>This is an ugly and obviously inefficient solution, but it does the job
>in about 1/2 hour. Still too long though.
>
>
>
>I've been working on this problem for days, and consulting friends. No
>elegant, fast solution is presenting itself. As I said, I feel I'm not
>seeing the obvious solution in front of my face.
>
>In the mean-time I can use this query to do the job on a per-customer
>basis:
>
>select
>  *
>from
>  bank_account
>where
>  id = <the customer's ID>
>  and ts = (select max(ts) from bank_account ba2 where ba2.customer_id =
>bank_account.customer_id);
>
>
>However, doing this for all 40,000 customers is not workable as a manual
>process. My last resort is to do it this way to pre-generate the report,
>but I'd far rather do it real-time.
>
>Help! My brain hurts!
>
>  
>