В PostgreSQL: у меня есть таблица с 3 столбцами:
CustomerNum, OrderNum, OrderDate.
Для каждого клиента может быть (а может и не быть) много заказов за один диапазон дат. Мне нужен последний номер заказа для каждого клиента, который находится в указанном диапазоне дат. Я получаю ResultSet клиентов и опрашиваю каждого отдельно, но это занимает слишком много времени.
Есть ли способ использовать подвыборку для выбора клиентов, а затем получить последний номер заказа для каждого клиента?
select customernum, max(ordernum)
from table
where orderdate between '...' and '...'
group by customernum
это все.
OrderNum является последовательным значением, может не соблюдаться или просто порядок создания может не соответствовать порядку дат (например, OrderDate может отмечать, когда порядок был завершен, а не когда он был создан, и вы заботитесь о время доработки).
- person Emanuele Aina; 21.06.2017
В 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
SELECT DISTINCT ON (field(s)) * FROM...
- person Le Droid; 06.04.2017
Не уверен в структуре или отношениях вашей таблицы 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
Если под номером последнего заказа вы имеете в виду самый большой номер заказа, вы можете просто использовать свой выбор в качестве предиката для номера клиента, сгруппировать результаты и выбрать максимум:
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);
-- 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)
