开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜