代码之家 › 专栏 › 技术社区 › WolfmanDragon

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

subquery greatest-n-per-group sql-order-by postgresql sql

WolfmanDragon · 技术社区 · 15 年前

在PostgreSQL: 我有一个表有三列:

CustomerNum, OrderNum, OrderDate .

每个日期范围内每个客户可能(或不可能)有许多订单。我需要的是每个客户在提供的日期范围内的最后一个ordernum。我一直在做的是获取客户的结果集,并分别查询每个结果集,但这花费了太多时间。

是否有任何方法可以使用Sub-Select来选择客户,然后获取每个客户的最后一个orderNum?

6 回复 | 直到 11 年前

user80168 15 年前

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

这就是全部。

Emanuele Aina 13 年前

在Postgres上,您也可以使用非标准 DISTINCT ON 条款:

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

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

manji 15 年前

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

six8 15 年前

不确定客户表的结构或关系,但这应该有效:

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

Ants Aasma 15 年前

如果按最后一个订单号表示最大的订单号,则可以使用select作为customer num的谓词,对结果分组并选择最大值:

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

如果最后一个订单号不一定是最大的订单号,那么您需要为每个客户找到最大的订单日期,并将其与其余订单一起查找相应的订单号:

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

wildplasser 13 年前

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

结果:

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)