Blog : Apriori algorithm -Find 2 of combination

Apriori algorithm -Find 2 of combination

I have an Order table like this:

<code>  ORDER_ID PRODUCT_ID
  11230112311123221231220003123033567</code>

and a Product table:

<code>  PRODUCT_ID NAME
  1230  A
  1231  B
  1232  C</code>

My first question, how to get combination of 2 Product Table, then how my new table structure should be?

for example;

<code>{1230,1231},{1230,1232},{1231,1232}</code>

but I don't want to this {1231,1230} because it already added.

Second one, in Order table, I keep sold product one session. How will be my new table?

example;

<code>orderid  products
1{1230,1231,1232}</code>

Finally, I want to find product other sold together support value, exp: {1231,1230} count : 2 {1230,1232 count : 0 }

If I have interpreted your requirement correctly?

<code>;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 )GROUPBY p1.PRODUCT_ID,
  p2.PRODUCT_ID,
  O.ORDER_ID
  HAVING COUNT(*)=2)SELECT P1,
  P2,
  COUNT(*)AS Cnt
FROM  T
GROUPBY P1,
  P2  </code>

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:

<code>SELECT*FROM products p1, products p2 GROUPBY((p1.PRODUCT_ID*p2.PRODUCT_ID)+p1.PRODUCT_ID+p2.PRODUCT_ID)</code>

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.