People who bought one of these also bought ...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

People who bought one of these also bought ...

Author

Topic

Kristen
Test

22859 Posts

Posted - 2005-11-27 : 13:04:36

What's the best way to do this analysis from an OrderItems table?

Is it just as simple ranking the following query by T_Count?


SELECT	[T_ProductCode1] = O1.ProductCode, 
	[T_ProductCode2] = O3.ProductCode, 
	[T_Count] = COUNT(*)
FROM	dbo.OrderItems O1
	-- Find another order that has this product
	JOIN dbo.OrderItems O2
		 ON O2.ProductCode = O1.ProductCode
		AND O2.OrderID <> O1.OrderID
	-- Find other products on the second order
	JOIN dbo.OrderItems O3
		 ON O3.OrderID = O2.OrderID
		AND O3.OrderItem <> O2.OrderItem
GROUP BY O1.ProductCode, O3.ProductCode

Kristen

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2005-11-28 : 06:13:19

Er, what's T_Count supposed to be a count of?
Or is that the point? Are you asking if T_Count -- being (effectively) the multiple of how many orders the pair of items occur in × the number of orders - 1 that T_ProductCode1 occurs in -- if that is reasonable metric for doing analysis of the table? Buggered if I know

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2005-11-28 : 10:44:05

I don't see anything in your query that considers the person who made the order. Is the idea that you want to show all possible combinations of items person by person where a person bought multiple items?

Assuming that Fred bought Item1 and Item2 in one order. And then Fred bought Item3 in a second order. Would you want to see:

Fred Item1 Item2
Fred Item1 Item3
Fred Item2 Item1
Fred Item2 Item3
Fred Item3 Item1
Fred Item3 Item2

As four separate lines since they are different combinations or how would you want to see them? (The comments in your query seem to indicate that you only want to see the other items for different orders and not within the same order.)
Are you accepting any input paramters for the person to see items for, or is the query for all?

Kristen
Test

22859 Posts

Posted - 2005-11-28 : 14:12:35

Sorry Guys, insufficient spec!

I want to be able to put on a web site "People who bought this also bought these ..."

I was assuming that the best way to do this would be to make a table of the number of ORDER LINES for combinations of products - so may a Cartesian join of the Order Items to itself (excluding joining the order item back to itself) and count the number of order lines for each Product-Product combination (That's T_Count Arnold)

However, I think I have excluded joining the order back to itself but have not also excluded joining the Product back to itself - so O3 should be


JOIN dbo.OrderItems O3
	 ON O3.OrderID = O2.OrderID
	AND O3.ProductCode <> O1.ProductCode

Kristen

jhermiz

3564 Posts

Posted - 2005-11-28 : 14:37:29

SELECT o.ProductID FROM TheOrders o
INNER JOIN TheOrders t
ON t.ProductID = o.ProductID
AND t.OrderID <> o.OrderID
GROUP BY o.ProductID ORDER BY
o.ProductID

Should do it why are you joining multiple times ?

Also assuming ProductID are product names.

Sorry if I misread something.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2005-11-28 : 16:23:31

Was the OrderItem column a typo, then?

Can we do this in Northwind, changing OrderItems to "Order Details", keeping OrderID, and changing ProductCode to ProductID?
That would give you (using your revised code):


SELECT	[T_ProductID1] = O1.ProductID, 
	[T_ProductID2] = O3.ProductID, 
	[T_Count] = COUNT(*)
FROM	dbo."Order Details" O1
	-- Find another order that has this product
	JOIN dbo."Order Details" O2
		 ON O2.ProductID = O1.ProductID
		AND O2.OrderID <> O1.OrderID
	-- Find other products on the second order
	JOIN dbo."Order Details" O3
		 ON O3.OrderID = O2.OrderID
		AND O3.ProductID <> O1.ProductID
GROUP BY O1.ProductID, O3.ProductID
ORDER BY T_Count DESC

Which gives a result starting:


31   16   350
21   61   304

There are 51 orders containing ProductID 31, and 7 orders with both 31 and 16, so (51-1)*7 = 350,
There are 39 orders containing ProductID 21, and 8 orders containing both 21 and 61, so (39-1)*8 = 304.
Etc.

I kind of assumed you'd want something more like


SELECT	[T_ProductID1] = O1.ProductID, 
	[T_ProductID2] = O3.ProductID, 
	[T_Count] = COUNT(*)
FROM	dbo."Order Details" O1
	-- Find other products on the same order
	JOIN dbo."Order Details" O3
		 ON O3.OrderID = O1.OrderID
		AND O3.ProductID > O1.ProductID
GROUP BY O1.ProductID, O3.ProductID
ORDER BY T_Count DESC

With either > or <> depending on whether you wanted each pairs to appear in both ways round.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2005-11-30 : 08:17:02

Zzzzzzzzzzzzz

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources