Re: agregates

Поиск
Список
Период
Сортировка
От David Blood
Тема Re: agregates
Дата
Msg-id 017c01c2c255$66d79b90$1f00a8c0@redwood
обсуждение исходный текст
Ответ на agregates  ("David Blood" <david@matraex.com>)
Список pgsql-general
Дерево обсуждения
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
 

В списке pgsql-general по дате отправления: