Re: agregates - Mailing list pgsql-general

Here si the explain plan on this.  Not very fast.

Seq Scan on tblpayment p1  (cost=0.00..5422453.14 rows=1 width=8)
  SubPlan
    ->  Limit  (cost=36.69..36.69 rows=1 width=4)
          ->  Sort  (cost=36.69..36.69 rows=2 width=4)
                ->  Index Scan using tblpayment_idx on tblpayment p2
(cost=0.00..36.68 rows=2 width=4)

EXPLAIN

Good news though I figured out how to get it done quickly.

select
 lastmonth.paymentid as lmpayment ,max(beforemonth.paymentid) as
paymentid,  date_trunc('month',lastmonth.paymentdate) as
lmpaymentdate,lastmonth.loginid
            from tblpayment as lastmonth, tblpayment as
beforemonth
            where
                lastmonth.customerid =
beforemonth.customerid
                and lastmonth.paymentid >
beforemonth.paymentid
                and lastmonth.paymentdebit > 0
                    and beforemonth.paymentdebit > 0
            group by
                   lastmonth.paymentid,
date_trunc('month',lastmonth.paymentdate), lastmonth.customerid
            ) as tp1           join tblpayment using
(paymentid)


I wanted the payment before the last on a per month basis so I added
that in.  If you take out the references to date then if would give the
payment before the last payment for each customer
Here is the explain for this query

Aggregate  (cost=17355.43..17560.13 rows=2047 width=24)
  ->  Group  (cost=17355.43..17508.95 rows=20470 width=24)
        ->  Sort  (cost=17355.43..17355.43 rows=20470 width=24)
              ->  Merge Join  (cost=14836.39..15889.66 rows=20470
width=24)
                    ->  Sort  (cost=7418.19..7418.19 rows=26424
width=16)
                          ->  Seq Scan on tblpayment lastmonth
(cost=0.00..5477.39 rows=26424 width=16)
                    ->  Sort  (cost=7418.19..7418.19 rows=26424 width=8)
                          ->  Seq Scan on tblpayment beforemonth
(cost=0.00..5477.39 rows=26424 width=8)

EXPLAIN


A whole lot better.

Thanks for the suggestions though.



David Blood
Boise, ID


-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Jean-Luc
Lachance
Sent: Thursday, January 23, 2003 1:56 PM
To: Andrew Sullivan; David Blood
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] agregates



Sure it does!

select customersid, paymentid
from tblpayment p1
where paymentdebit > 0 and paymentid = (
  select paymentid
  from tblpayment p2
  where p2.paymentdebit > 0 and p2.customerid = p1.customerid
  order by paymentid desc limit 1 offset 1);

JLL


Andrew Sullivan wrote:
>
> On Wed, Jan 22, 2003 at 05:15:44PM -0500, Eric B. Ridge wrote:
> >
> > wouldn't you want LIMIT 2 OFFSET 1 ?
>
> No, he only wants one record.  But actually, he wanted one record per
> customer, so my suggestion didn't help.
>
> A
> --
> ----
> Andrew Sullivan                         204-4141 Yonge Street
> Liberty RMS                           Toronto, Ontario Canada
> <andrew@libertyrms.info>                              M2P 2A8
>                                          +1 416 646 3304 x110
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
majordomo@postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org