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.