April 21, 2010 at 8:44 am
Hi,
I need some help with a query...
I have an orders table
DROP TABLE #orders
CREATE TABLE #orders(
order_id int,
product_id int,
orderDate datetime,
amount float
)
INSERT INTO #orders VALUES (1,1,'1/Jan/2009',10)
INSERT INTO #orders VALUES (2,1,'11/Jan/2008',10.9)
INSERT INTO #orders VALUES (3,1,'19/Jan/2008',12)
INSERT INTO #orders VALUES (4,2,'21/Jan/2009',19)
INSERT INTO #orders VALUES (5,2,'25/Jan/2007',1)
INSERT INTO #orders VALUES (6,2,'16/Jan/2005',17)
INSERT INTO #orders VALUES (7,2,'10/Jan/2006',13)
INSERT INTO #orders VALUES (8,2,'1/Jan/2004',10)
INSERT INTO #orders VALUES (9,2,'1/Jan/2003',31)
INSERT INTO #orders VALUES (10,3,'1/Jan/2009',3)
INSERT INTO #orders VALUES (11,3,'12/Jan/2005',7)
INSERT INTO #orders VALUES (12,3,'5/Jan/2004',19)
INSERT INTO #orders VALUES (13,3,'6/Jan/2007',10)
INSERT INTO #orders VALUES (14,3,'9/Jan/2005',1)
INSERT INTO #orders VALUES (15,3,'21/Jan/2001',17)
I want to to know: For every entry in #orders, what is the average value of the last 3 orders for that product.
e.g for order 15, I would like the average value of order 11, 13, 14 = 6. As these were the 3 most recent orders for that product
At the moment I have this:
SELECT o.product_id as o1
,t1.product_id as t1
,o.order_id as o2
,t1.order_id as t2
FROM #orders o
INNER JOIN (SELECT TOP 3* FROM #orders o2 ORDER BY DESC )t1 ON t1.product_id = o.product_id AND t1.order_id <> o.order_id
ORDER BY o.product_id,o.orderDate
But the TOP is performed on the whole table giving me incorrect results!
Thanks for any help. Please ask for more info if this does not make sense
April 21, 2010 at 9:09 am
Try this:
SELECT A.*, AV = (
SELECT AVG(amount)
FROM #Orders AS B
WHERE B.product_id = A.product_id
AND B.order_id IN (
SELECT TOP 3 order_id
FROM #Orders AS C
WHERE C.product_id = A.product_id
AND C.order_id < A.order_id
ORDER BY orderDate DESC
)
)
FROM #Orders AS A
BTW, for order 15, the orders to check are 10, 13 and 11, for those seem to be the most recent 3 (prior to order 15, obviously).
Hope this helps
Gianluca
-- Gianluca Sartori
April 21, 2010 at 9:20 am
Another option:
CREATE TABLE #orders(
order_id int,
product_id int,
orderDate datetime,
amount float
)
INSERT INTO #orders VALUES (1,1,'1/Jan/2009',10)
INSERT INTO #orders VALUES (2,1,'11/Jan/2008',10.9)
INSERT INTO #orders VALUES (3,1,'19/Jan/2008',12)
INSERT INTO #orders VALUES (4,2,'21/Jan/2009',19)
INSERT INTO #orders VALUES (5,2,'25/Jan/2007',1)
INSERT INTO #orders VALUES (6,2,'16/Jan/2005',17)
INSERT INTO #orders VALUES (7,2,'10/Jan/2006',13)
INSERT INTO #orders VALUES (8,2,'1/Jan/2004',10)
INSERT INTO #orders VALUES (9,2,'1/Jan/2003',31)
INSERT INTO #orders VALUES (10,3,'1/Jan/2009',3)
INSERT INTO #orders VALUES (11,3,'12/Jan/2005',7)
INSERT INTO #orders VALUES (12,3,'5/Jan/2004',19)
INSERT INTO #orders VALUES (13,3,'6/Jan/2007',10)
INSERT INTO #orders VALUES (14,3,'9/Jan/2005',1)
INSERT INTO #orders VALUES (15,3,'21/Jan/2001',17);
with OrdersByProductDate as (
select
row_number() over (partition by product_id order by orderDate desc) as rownum,
order_id,
product_id,
orderDate,
amount
from
#orders
)
select
product_id,
avg(amount) AvgAmount
from
OrdersByProductDate
where
rownum <= 3
group by
product_id
;
April 21, 2010 at 9:26 am
Thank you Gianluca,
Very clever!
I just tweaked it a tiny bit, as given an order, I want the 3 orders that preceded it (didn't word this too well in my OP)
So, I now have:
SELECT A.*, AV = (
SELECT AVG(amount)
FROM #Orders AS B
WHERE B.product_id = A.product_id
AND B.order_id IN (
SELECT TOP 3 order_id
FROM #Orders AS C
WHERE C.product_id = A.product_id
AND C.order_id <> A.order_id
AND C.orderDate <= A.orderDate
ORDER BY orderDate DESC
)
)
FROM #Orders AS A
Thank you again!
April 21, 2010 at 9:32 am
nevermind......misread your question....
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
April 21, 2010 at 9:43 am
You're welcome.
Glad I could help.
-- Gianluca Sartori
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply