PostgreSQL SELECT 每个客户每个日期范围的最后一个订单

PostgreSQL SELECT the last order per customer per date range

提问by WolfmanDragon

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

在 PostgreSQL 中：我有一个包含 3 列的表：

CustomerNum, OrderNum, OrderDate.

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.

在每个日期范围内，每个客户可能（也可能没有）有很多订单。我需要的是位于提供的日期范围内的每个客户的最后一个 OrderNum。我一直在做的是获取客户的 ResultSet 并分别查询每个客户，但这花费了太多时间。

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

有没有办法使用子选择来选择客户，然后获取每个客户的最后一个 OrderNum ？

回答by Emanuele Aina

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

在 postgres 上，您还可以使用非标准DISTINCT ON条款：

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

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

回答by Emanuele Aina

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

that's all.

就这样。

回答by manji

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

回答by six8

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

不确定您的 Customer 表的结构或关系，但这应该有效：

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

回答by Ants Aasma

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);

回答by wildplasser

-- 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)