PostgreSQL SELECT the last order per customer per

问题:

In PostgreSQL: I have a Table that has 3 columns:

CustomerNum, OrderNum, OrderDate.

There may(or may not) be many orders for each customer per date range. What I am needing is the last OrderNum for each Customer that lies in the date range that is supplied. What I have been doing is getting a ResultSet of the customers and querying each one separately, but this is taking too much time.

Is there any way of using a sub-select to select out the customers, then get the last OrderNum for each Customer?

回答1:

select customernum, max(ordernum)
from table
where orderdate between '...' and '...'
group by customernum

that's all.

回答2:

On postgres you can also use the non-standard DISTINCT ON clause:

SELECT DISTINCT ON (CustomerNum) CustomerNum, OrderNum, OrderDate
  FROM Orders
  WHERE OrderDate BETWEEN 'yesterday' AND 'today'
  ORDER BY CustomerNum, OrderDate DESC;

See http://www.postgresql.org/docs/current/static/sql-select.html#SQL-DISTINCT

回答3:

SELECT t1.CustomerNum, t1.OrderNum As LastOrderNum, t1.LastOrderDate
  FROM table1 As t1
 WHERE t1.OrderDate = (SELECT MAX(t2.OrderDate)
                         FROM table1 t2
                        WHERE t1.CustomerNum = t2.CustomerNum
                          AND t2.OrderDate BETWEEN date1 AND date2)
   AND t1.OrderDate BETWEEN date1 AND date2

回答4:

Not sure about your Customer table's structure or relationships, but this should work:

SELECT Customer.Num, (
    SELECT OrderNum FROM Orders WHERE CustomerNum = Customer.Num AND OrderDate BETWEEN :start AND :end ORDER BY OrderNum DESC LIMIT 1
) AS LastOrderNum
FROM Customer

回答5:

If by last order number you mean the largest order number then you can just use your select as the predicate for customer num, group the results and select the maximum:

SELECT CustomerNum, MAX(OrderNum) AS LastOrderNum
    FROM Orders
    WHERE 
        CustomerNum IN (SELECT CustomerNum FROM ...)
            AND
        OrderDate BETWEEN :first_date AND :last_date
    GROUP BY CustomerNum

If the last order number isn't necessarily the largest order number then you'll need to either find the largest order date for each customer and join it together with the rest of the orders to find the corresponding number(s):

SELECT O.CustomerNum, O.OrderNum AS LastOrderNum
    FROM
        (SELECT CustomerNum, MAX(OrderDate) AS OrderDate
             FROM Orders
             WHERE
                 OrderDate BETWEEN :first_date AND :last_date
                     AND
                 CustomerNum IN (SELECT CustomerNum FROM ...)
             GROUP BY CustomerNum
        ) AS CustLatest
            INNER JOIN
        Orders AS O USING (CustomerNum, OrderDate);

回答6:

-- generate some data
DROP TABLE tmp.orders;
CREATE TABLE tmp.orders
    ( id INTEGER NOT NULL
    , odate DATE NOT NULL
    , payload VARCHAR
    )
    ;
ALTER TABLE tmp.orders ADD PRIMARY KEY (id,odate);

INSERT INTO tmp.orders(id,odate,payload) VALUES
  (1, '2011-10-04' , 'one' )
, (1, '2011-10-24' , 'two' )
, (1, '2011-10-25' , 'three' )
, (1, '2011-10-26' , 'four' )
, (2, '2011-10-23' , 'five' )
, (2, '2011-10-24' , 'six' )
    ;

-- CTE to the rescue ...
WITH sel AS (
    SELECT * FROM tmp.orders
    WHERE odate BETWEEN '2011-10-23' AND '2011-10-24'
    )
SELECT * FROM sel s0
WHERE NOT EXISTS (
    SELECT * FROM sel sx
    WHERE sx.id = s0.id
    AND sx.odate > s0.odate
    )
    ;

result:

DROP TABLE
CREATE TABLE
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "orders_pkey" for table "orders"
ALTER TABLE
INSERT 0 6
 id |   odate    | payload 
----+------------+---------
  1 | 2011-10-24 | two
  2 | 2011-10-24 | six
(2 rows)