Обсуждение: agregates

Поиск
Список
Период
Сортировка

agregates

От
"David Blood"
Дата:
 
I would like to be a ble to get a bunch of payments from a payment table.  I want to return not the last payment the payment before that for all customers.
 
I have tried

select max(paymentid) as paymentid
   from tblpayment
   where paymentdebit > 0
   and paymentid not in (select max(paymentid) as paymentid
   from tblpayment
   where paymentdebit > 0
   group by customerid)
 
 
the cost on this thru the roof
 

NOTICE:  QUERY PLAN:
 
Aggregate  (cost=1520829785.44..1520829785.44 rows=1 width=4)
  ->  Seq Scan on tblpayment  (cost=0.00..1520829707.94 rows=31002 width=4)
        SubPlan
          ->  Materialize  (cost=10336.74..10336.74 rows=6200 width=8)
                ->  Aggregate  (cost=10026.72..10336.74 rows=6200 width=8)
                      ->  Group  (cost=10026.72..10181.73 rows=62005 width=8)
                            ->  Sort  (cost=10026.72..10026.72 rows=62005 width=8)
                                  ->  Seq Scan on tblpayment  (cost=0.00..5091.10 rows=62005 width=8)
 
the same query rewritten using exist has a better time but still much to long
 

NOTICE:  QUERY PLAN:
 
Aggregate  (cost=5769119.39..5769274.41 rows=3100 width=8)
  ->  Group  (cost=5769119.39..5769196.90 rows=31002 width=8)
        ->  Sort  (cost=5769119.39..5769119.39 rows=31002 width=8)
              ->  Seq Scan on tblpayment thismonth  (cost=0.00..5766806.60 rows=31002 width=8)
                    SubPlan
                      ->  Limit  (cost=39.16..39.16 rows=1 width=12)
                            ->  Sort  (cost=39.16..39.16 rows=5 width=12)
                                  ->  Index Scan using tblpayment_idx on tblpayment  (cost=0.00..39.11 rows=5 width=12)
 
EXPLAIN
 
is there a better way to get the max - 1?
I could loop through and run the queriy for each customer but the cost on ten thousand quesries is rather high also?
 
David Blood
Boise, ID
 
David Blood
Boise, ID
 

Re: agregates

От
Andrew Sullivan
Дата:
On Wed, Jan 22, 2003 at 10:07:06AM -0700, David Blood wrote:
>
> is there a better way to get the max - 1?

You could try ORDER BY paymentid DESC LIMIT 1 OFFSET 1.  That'll give
you the second from the top of the list, according to the order you
used.

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

Re: agregates

От
"David Blood"
Дата:
I was able to do this with following query
 
 
select max(paymentid) as paymentid
    from
    (
      select paymentid ,customerid
      from tblpayment
     except
      (select max(paymentid) as paymentid, customerid
      from tblpayment
      )
    ) as this
    group by customerid
 
 
This will get me the max for each customer yet I need it for each month so I could run this query for each month which is much better that on for each customer I think that I can get it better though.
 
 
David Blood
Boise, ID
-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of David Blood
Sent: Wednesday, January 22, 2003 10:07 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] agregates

 
I would like to be a ble to get a bunch of payments from a payment table.  I want to return not the last payment the payment before that for all customers.
 
I have tried

select max(paymentid) as paymentid
   from tblpayment
   where paymentdebit > 0
   and paymentid not in (select max(paymentid) as paymentid
   from tblpayment
   where paymentdebit > 0
   group by customerid)
 
 
the cost on this thru the roof
 

NOTICE:  QUERY PLAN:
 
Aggregate  (cost=1520829785.44..1520829785.44 rows=1 width=4)
  ->  Seq Scan on tblpayment  (cost=0.00..1520829707.94 rows=31002 width=4)
        SubPlan
          ->  Materialize  (cost=10336.74..10336.74 rows=6200 width=8)
                ->  Aggregate  (cost=10026.72..10336.74 rows=6200 width=8)
                      ->  Group  (cost=10026.72..10181.73 rows=62005 width=8)
                            ->  Sort  (cost=10026.72..10026.72 rows=62005 width=8)
                                  ->  Seq Scan on tblpayment  (cost=0.00..5091.10 rows=62005 width=8)
 
the same query rewritten using exist has a better time but still much to long
 

NOTICE:  QUERY PLAN:
 
Aggregate  (cost=5769119.39..5769274.41 rows=3100 width=8)
  ->  Group  (cost=5769119.39..5769196.90 rows=31002 width=8)
        ->  Sort  (cost=5769119.39..5769119.39 rows=31002 width=8)
              ->  Seq Scan on tblpayment thismonth  (cost=0.00..5766806.60 rows=31002 width=8)
                    SubPlan
                      ->  Limit  (cost=39.16..39.16 rows=1 width=12)
                            ->  Sort  (cost=39.16..39.16 rows=5 width=12)
                                  ->  Index Scan using tblpayment_idx on tblpayment  (cost=0.00..39.11 rows=5 width=12)
 
EXPLAIN
 
is there a better way to get the max - 1?
I could loop through and run the queriy for each customer but the cost on ten thousand quesries is rather high also?
 
David Blood
Boise, ID
 
David Blood
Boise, ID
 

Re: agregates

От
Eric B.Ridge
Дата:
On Wednesday, January 22, 2003, at 02:28  PM, Andrew Sullivan wrote:

> On Wed, Jan 22, 2003 at 10:07:06AM -0700, David Blood wrote:
>>
>> is there a better way to get the max - 1?
>
> You could try ORDER BY paymentid DESC LIMIT 1 OFFSET 1.  That'll give

wouldn't you want LIMIT 2 OFFSET 1 ?

eric

Re: agregates

От
Andrew Sullivan
Дата:
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

Re: agregates

От
"codeWarrior"
Дата:
SELECT  paymentid, customerid FROM tblpayment where paymentdebit > 0 LIMIT 1
OFFSET 1



""David Blood"" <david@matraex.com> wrote in message
news:016b01c2c238$b40ca580$1f00a8c0@redwood...
> This is a multi-part message in MIME format.
>
> ------=_NextPart_000_016C_01C2C1FE.07ADCD80
> Content-Type: text/plain;
> charset="US-ASCII"
> Content-Transfer-Encoding: 7bit
>
>
> I would like to be a ble to get a bunch of payments from a payment
> table.  I want to return not the last payment the payment before that
> for all customers.
>
> I have tried
>
> select max(paymentid) as paymentid
>    from tblpayment
>    where paymentdebit > 0
>    and paymentid not in (select max(paymentid) as paymentid
>    from tblpayment
>    where paymentdebit > 0
>    group by customerid)
>
>
> the cost on this thru the roof
>
>
> NOTICE:  QUERY PLAN:
>
> Aggregate  (cost=1520829785.44..1520829785.44 rows=1 width=4)
>   ->  Seq Scan on tblpayment  (cost=0.00..1520829707.94 rows=31002
> width=4)
>         SubPlan
>           ->  Materialize  (cost=10336.74..10336.74 rows=6200 width=8)
>                 ->  Aggregate  (cost=10026.72..10336.74 rows=6200
> width=8)
>                       ->  Group  (cost=10026.72..10181.73 rows=62005
> width=8)
>                             ->  Sort  (cost=10026.72..10026.72
> rows=62005 width=8)
>                                   ->  Seq Scan on tblpayment
> (cost=0.00..5091.10 rows=62005 width=8)
>
>
> the same query rewritten using exist has a better time but still much to
> long
>
>
> NOTICE:  QUERY PLAN:
>
> Aggregate  (cost=5769119.39..5769274.41 rows=3100 width=8)
>   ->  Group  (cost=5769119.39..5769196.90 rows=31002 width=8)
>         ->  Sort  (cost=5769119.39..5769119.39 rows=31002 width=8)
>               ->  Seq Scan on tblpayment thismonth
> (cost=0.00..5766806.60 rows=31002 width=8)
>                     SubPlan
>                       ->  Limit  (cost=39.16..39.16 rows=1 width=12)
>                             ->  Sort  (cost=39.16..39.16 rows=5
> width=12)
>                                   ->  Index Scan using tblpayment_idx on
> tblpayment  (cost=0.00..39.11 rows=5 width=12)
>
> EXPLAIN
>
>
> is there a better way to get the max - 1?
> I could loop through and run the queriy for each customer but the cost
> on ten thousand quesries is rather high also?
>
> David Blood
> Boise, ID
>
> David Blood
> Boise, ID
>
>
> ------=_NextPart_000_016C_01C2C1FE.07ADCD80
> Content-Type: text/html;
> charset="US-ASCII"
> Content-Transfer-Encoding: quoted-printable
>
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> <HTML><HEAD>
> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html;
charset=3Dus-ascii">
> <TITLE>Message</TITLE>
>
> <META content=3D"MSHTML 6.00.2722.900" name=3DGENERATOR></HEAD>
> <BODY>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV>
> <DIV><SPAN class=3D096202915-22012003><FONT face=3DArial size=3D2>I would
l=
> ike to be a=20
> ble to get a bunch of payments from a payment table.  I want to
return=
>  not=20
> the last payment the payment before that for all
customers.</FONT></SPAN></=
> DIV>
> <DIV><SPAN class=3D096202915-22012003><FONT face=3DArial=20
> size=3D2></FONT></SPAN> </DIV>
> <DIV><SPAN class=3D096202915-22012003>I have tried</SPAN></DIV><SPAN=20
> class=3D096202915-22012003>
> <DIV><BR><FONT face=3DArial size=3D2>select max(paymentid) as=20
> paymentid<BR>   from tblpayment
<BR>   where=
> =20
> paymentdebit > 0<BR>   and paymentid not in (select=20
> max(paymentid) as paymentid<BR>   from tblpayment=20
> <BR>   where paymentdebit > 0<BR>   group
=
> by=20
> customerid)</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV></SPAN><SPAN class=3D096202915-22012003><FONT face=3DArial
size=3D2>th=
> e cost on=20
> this thru the roof</FONT></SPAN></DIV>
> <DIV><SPAN class=3D096202915-22012003><FONT face=3DArial=20
> size=3D2></FONT></SPAN> </DIV><SPAN class=3D096202915-22012003>
> <DIV><BR><FONT face=3DArial size=3D2>NOTICE:  QUERY
PLAN:</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2>Aggregate 
(cost=3D1520829785.44..152=
> 0829785.44=20
> rows=3D1 width=3D4)<BR>  ->  Seq Scan on tblpayment =20
> (cost=3D0.00..1520829707.94 rows=3D31002=20
> width=3D4)<BR>       =20
>
SubPlan<BR>          ->&nbs=
> p;=20
> Materialize  (cost=3D10336.74..10336.74 rows=3D6200=20
>
width=3D8)<BR>          &=
> nbsp;    =20
> ->  Aggregate  (cost=3D10026.72..10336.74 rows=3D6200=20
>
width=3D8)<BR>          &=
> nbsp;          =20
> ->  Group  (cost=3D10026.72..10181.73 rows=3D62005=20
>
width=3D8)<BR>          &=
>
nbsp;           &nbs=
> p;    =20
> ->  Sort  (cost=3D10026.72..10026.72 rows=3D62005=20
>
width=3D8)<BR>          &=
>
nbsp;           &nbs=
> p;          =20
> ->  Seq Scan on tblpayment  (cost=3D0.00..5091.10
rows=3D62005=
> =20
> width=3D8)<BR></FONT></DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><SPAN class=3D096202915-22012003><FONT face=3DArial size=3D2>the same
=
> query=20
> rewritten using exist has a better time but still much to=20
> long</FONT></SPAN></DIV>
> <DIV><SPAN class=3D096202915-22012003><FONT face=3DArial=20
> size=3D2></FONT></SPAN> </DIV><SPAN class=3D096202915-22012003>
> <DIV><BR><FONT face=3DArial size=3D2>NOTICE:  QUERY
PLAN:</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2>Aggregate 
(cost=3D5769119.39..576927=
> 4.41=20
> rows=3D3100 width=3D8)<BR>  ->  Group =20
> (cost=3D5769119.39..5769196.90 rows=3D31002=20
> width=3D8)<BR>        -> 
Sort&n=
> bsp;=20
> (cost=3D5769119.39..5769119.39 rows=3D31002=20
>
width=3D8)<BR>          &=
> nbsp;  =20
> ->  Seq Scan on tblpayment thismonth 
(cost=3D0.00..5766806.60=
> =20
> rows=3D31002=20
>
width=3D8)<BR>          &=
> nbsp;        =20
>
SubPlan<BR>          &nbs=
> p;          =20
> ->  Limit  (cost=3D39.16..39.16 rows=3D1=20
>
width=3D12)<BR>          =
>
            &nb=
> sp;    =20
> ->  Sort  (cost=3D39.16..39.16 rows=3D5=20
>
width=3D12)<BR>          =
>
            &nb=
> sp;          =20
> ->  Index Scan using tblpayment_idx on tblpayment =20
> (cost=3D0.00..39.11 rows=3D5 width=3D12)</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2>EXPLAIN<BR></FONT></DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><SPAN class=3D096202915-22012003><FONT face=3DArial size=3D2>is there
=
> a better=20
> way to get the max - 1?</FONT></SPAN></DIV>
> <DIV><SPAN class=3D096202915-22012003><FONT face=3DArial size=3D2>I could
l=
> oop through=20
> and run the queriy for each customer but the cost on ten thousand quesries
=
> is=20
> rather high also?</FONT></SPAN></DIV></SPAN></SPAN>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV align=3Dleft><FONT face=3DArial size=3D2>David Blood</FONT></DIV>
> <DIV align=3Dleft><FONT face=3DArial size=3D2>Boise, ID</FONT></DIV>
> <DIV> </DIV></DIV>
> <DIV align=3Dleft><FONT face=3DArial size=3D2>David Blood</FONT></DIV>
> <DIV align=3Dleft><FONT face=3DArial size=3D2>Boise, ID</FONT></DIV>
> <DIV> </DIV></BODY></HTML>
>
> ------=_NextPart_000_016C_01C2C1FE.07ADCD80--
>
>

Re: agregates

От
"codeWarrior"
Дата:
SORRY -- I realized you need to specify an order to get the next to last
record .. -- Check the PG docs on LIMIT (Section 7.2 under "SELECT")...

SELECT  paymentid, customerid FROM tblpayment where paymentdebit > 0 ORDER
BY paymentid DESC LIMIT 1 OFFSET 1

"codeWarrior" <GPatnude@adelphia.net> wrote in message
news:b0mo75$11ne$1@news.hub.org...
> SELECT  paymentid, customerid FROM tblpayment where paymentdebit > 0 LIMIT
1
> OFFSET 1
>
>
>
> ""David Blood"" <david@matraex.com> wrote in message
> news:016b01c2c238$b40ca580$1f00a8c0@redwood...
> > This is a multi-part message in MIME format.
> >
> > ------=_NextPart_000_016C_01C2C1FE.07ADCD80
> > Content-Type: text/plain;
> > charset="US-ASCII"
> > Content-Transfer-Encoding: 7bit
> >
> >
> > I would like to be a ble to get a bunch of payments from a payment
> > table.  I want to return not the last payment the payment before that
> > for all customers.
> >
> > I have tried
> >
> > select max(paymentid) as paymentid
> >    from tblpayment
> >    where paymentdebit > 0
> >    and paymentid not in (select max(paymentid) as paymentid
> >    from tblpayment
> >    where paymentdebit > 0
> >    group by customerid)
> >
> >
> > the cost on this thru the roof
> >
> >
> > NOTICE:  QUERY PLAN:
> >
> > Aggregate  (cost=1520829785.44..1520829785.44 rows=1 width=4)
> >   ->  Seq Scan on tblpayment  (cost=0.00..1520829707.94 rows=31002
> > width=4)
> >         SubPlan
> >           ->  Materialize  (cost=10336.74..10336.74 rows=6200 width=8)
> >                 ->  Aggregate  (cost=10026.72..10336.74 rows=6200
> > width=8)
> >                       ->  Group  (cost=10026.72..10181.73 rows=62005
> > width=8)
> >                             ->  Sort  (cost=10026.72..10026.72
> > rows=62005 width=8)
> >                                   ->  Seq Scan on tblpayment
> > (cost=0.00..5091.10 rows=62005 width=8)
> >
> >
> > the same query rewritten using exist has a better time but still much to
> > long
> >
> >
> > NOTICE:  QUERY PLAN:
> >
> > Aggregate  (cost=5769119.39..5769274.41 rows=3100 width=8)
> >   ->  Group  (cost=5769119.39..5769196.90 rows=31002 width=8)
> >         ->  Sort  (cost=5769119.39..5769119.39 rows=31002 width=8)
> >               ->  Seq Scan on tblpayment thismonth
> > (cost=0.00..5766806.60 rows=31002 width=8)
> >                     SubPlan
> >                       ->  Limit  (cost=39.16..39.16 rows=1 width=12)
> >                             ->  Sort  (cost=39.16..39.16 rows=5
> > width=12)
> >                                   ->  Index Scan using tblpayment_idx on
> > tblpayment  (cost=0.00..39.11 rows=5 width=12)
> >
> > EXPLAIN
> >
> >
> > is there a better way to get the max - 1?
> > I could loop through and run the queriy for each customer but the cost
> > on ten thousand quesries is rather high also?
> >
> > David Blood
> > Boise, ID
> >
> > David Blood
> > Boise, ID
> >
> >
> > ------=_NextPart_000_016C_01C2C1FE.07ADCD80
> > Content-Type: text/html;
> > charset="US-ASCII"
> > Content-Transfer-Encoding: quoted-printable
> >
> > <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> > <HTML><HEAD>
> > <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html;
> charset=3Dus-ascii">
> > <TITLE>Message</TITLE>
> >
> > <META content=3D"MSHTML 6.00.2722.900" name=3DGENERATOR></HEAD>
> > <BODY>
> > <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> > <DIV>
> > <DIV><SPAN class=3D096202915-22012003><FONT face=3DArial size=3D2>I
would
> l=
> > ike to be a=20
> > ble to get a bunch of payments from a payment table.  I want to
> return=
> >  not=20
> > the last payment the payment before that for all
> customers.</FONT></SPAN></=
> > DIV>
> > <DIV><SPAN class=3D096202915-22012003><FONT face=3DArial=20
> > size=3D2></FONT></SPAN> </DIV>
> > <DIV><SPAN class=3D096202915-22012003>I have tried</SPAN></DIV><SPAN=20
> > class=3D096202915-22012003>
> > <DIV><BR><FONT face=3DArial size=3D2>select max(paymentid) as=20
> > paymentid<BR>   from tblpayment
> <BR>   where=
> > =20
> > paymentdebit > 0<BR>   and paymentid not in (select=20
> > max(paymentid) as paymentid<BR>   from tblpayment=20
> > <BR>   where paymentdebit >
0<BR>   group
> =
> > by=20
> > customerid)</FONT></DIV>
> > <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> > <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> > <DIV></SPAN><SPAN class=3D096202915-22012003><FONT face=3DArial
> size=3D2>th=
> > e cost on=20
> > this thru the roof</FONT></SPAN></DIV>
> > <DIV><SPAN class=3D096202915-22012003><FONT face=3DArial=20
> > size=3D2></FONT></SPAN> </DIV><SPAN class=3D096202915-22012003>
> > <DIV><BR><FONT face=3DArial size=3D2>NOTICE:  QUERY
> PLAN:</FONT></DIV>
> > <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> > <DIV><FONT face=3DArial size=3D2>Aggregate 
> (cost=3D1520829785.44..152=
> > 0829785.44=20
> > rows=3D1 width=3D4)<BR>  ->  Seq Scan on
tblpayment =20
> > (cost=3D0.00..1520829707.94 rows=3D31002=20
> > width=3D4)<BR>       =20
> >
>
SubPlan<BR>          ->&nbs=
> > p;=20
> > Materialize  (cost=3D10336.74..10336.74 rows=3D6200=20
> >
>
width=3D8)<BR>          &=
> > nbsp;    =20
> > ->  Aggregate  (cost=3D10026.72..10336.74 rows=3D6200=20
> >
>
width=3D8)<BR>          &=
> > nbsp;          =20
> > ->  Group  (cost=3D10026.72..10181.73 rows=3D62005=20
> >
>
width=3D8)<BR>          &=
> >
>
nbsp;           &nbs=
> > p;    =20
> > ->  Sort  (cost=3D10026.72..10026.72 rows=3D62005=20
> >
>
width=3D8)<BR>          &=
> >
>
nbsp;           &nbs=
> > p;          =20
> > ->  Seq Scan on tblpayment  (cost=3D0.00..5091.10
> rows=3D62005=
> > =20
> > width=3D8)<BR></FONT></DIV>
> > <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> > <DIV><SPAN class=3D096202915-22012003><FONT face=3DArial size=3D2>the
same
> =
> > query=20
> > rewritten using exist has a better time but still much to=20
> > long</FONT></SPAN></DIV>
> > <DIV><SPAN class=3D096202915-22012003><FONT face=3DArial=20
> > size=3D2></FONT></SPAN> </DIV><SPAN class=3D096202915-22012003>
> > <DIV><BR><FONT face=3DArial size=3D2>NOTICE:  QUERY
> PLAN:</FONT></DIV>
> > <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> > <DIV><FONT face=3DArial size=3D2>Aggregate 
> (cost=3D5769119.39..576927=
> > 4.41=20
> > rows=3D3100 width=3D8)<BR>  ->  Group =20
> > (cost=3D5769119.39..5769196.90 rows=3D31002=20
> > width=3D8)<BR>        -> 
> Sort&n=
> > bsp;=20
> > (cost=3D5769119.39..5769119.39 rows=3D31002=20
> >
>
width=3D8)<BR>          &=
> > nbsp;  =20
> > ->  Seq Scan on tblpayment thismonth 
> (cost=3D0.00..5766806.60=
> > =20
> > rows=3D31002=20
> >
>
width=3D8)<BR>          &=
> > nbsp;        =20
> >
>
SubPlan<BR>          &nbs=
> > p;          =20
> > ->  Limit  (cost=3D39.16..39.16 rows=3D1=20
> >
>
width=3D12)<BR>          =
> >
>
            &nb=
> > sp;    =20
> > ->  Sort  (cost=3D39.16..39.16 rows=3D5=20
> >
>
width=3D12)<BR>          =
> >
>
            &nb=
> > sp;          =20
> > ->  Index Scan using tblpayment_idx on tblpayment =20
> > (cost=3D0.00..39.11 rows=3D5 width=3D12)</FONT></DIV>
> > <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> > <DIV><FONT face=3DArial size=3D2>EXPLAIN<BR></FONT></DIV>
> > <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> > <DIV><SPAN class=3D096202915-22012003><FONT face=3DArial size=3D2>is
there
> =
> > a better=20
> > way to get the max - 1?</FONT></SPAN></DIV>
> > <DIV><SPAN class=3D096202915-22012003><FONT face=3DArial size=3D2>I
could
> l=
> > oop through=20
> > and run the queriy for each customer but the cost on ten thousand
quesries
> =
> > is=20
> > rather high also?</FONT></SPAN></DIV></SPAN></SPAN>
> > <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> > <DIV align=3Dleft><FONT face=3DArial size=3D2>David Blood</FONT></DIV>
> > <DIV align=3Dleft><FONT face=3DArial size=3D2>Boise, ID</FONT></DIV>
> > <DIV> </DIV></DIV>
> > <DIV align=3Dleft><FONT face=3DArial size=3D2>David Blood</FONT></DIV>
> > <DIV align=3Dleft><FONT face=3DArial size=3D2>Boise, ID</FONT></DIV>
> > <DIV> </DIV></BODY></HTML>
> >
> > ------=_NextPart_000_016C_01C2C1FE.07ADCD80--
> >
> >
>
>

Re: agregates

От
Jean-Luc Lachance
Дата:
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

Re: agregates

От
"David Blood"
Дата:
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