Apriori algorithm -Find 2 of combination
I have an Order
table like this:
ORDER_ID PRODUCT_ID
1 1230
1 1231
1 1232
2 1231
2 2000
3 1230
3 3567
and a Product
table:
PRODUCT_ID NAME
1230 A
1231 B
1232 C
My first question, how to get combination of 2 Product Table, then how my new table structure should be?
for example;
{1230,1231}, {1230,1232}, {1231,1232}
but I don't want to this {1231,1230}
because it already added.
Second one, in Order
table, I keep sold pro开发者_如何转开发duct one session. How will be my new table?
example;
orderid products
1 {1230,1231,1232}
Finally, I want to find product other sold together support value,
exp: {1231,1230} count : 2
{1230,1232 count : 0 }
thanks in advance.
edit: i want to do like this: http://webdocs.cs.ualberta.ca/~zaiane/courses/cmput499/slides/Lect10/sld054.htm
If I have interpreted your requirement correctly?
;WITH T(P1, P2, ORDER_ID)
AS (SELECT p1.PRODUCT_ID,
p2.PRODUCT_ID,
O.ORDER_ID
FROM Product p1
JOIN Product p2
ON p1.PRODUCT_ID < p2.PRODUCT_ID
JOIN [ORDER] o
ON o.PRODUCT_ID IN ( p1.PRODUCT_ID, p2.PRODUCT_ID )
GROUP BY p1.PRODUCT_ID,
p2.PRODUCT_ID,
O.ORDER_ID
HAVING COUNT(*) = 2)
SELECT P1,
P2,
COUNT(*) AS Cnt
FROM T
GROUP BY P1,
P2
I don't really understand questions 2 or 3, so please clarify in your question.
The first one is tricky, but I think you're looking for something like this:
SELECT * FROM products p1, products p2 GROUP BY ((p1.PRODUCT_ID*p2.PRODUCT_ID)+p1.PRODUCT_ID+p2.PRODUCT_ID)
Because it would group by rows only where the two numbers are the same, without caring about order. There might be a more elegant way to create what's basically a unique id for that combination, but I can't think of any.
精彩评论