title: Data Mining Operation using SQL Query (Fuzzy Apriori Algorithm) - Coding it using SQL
body: <p>So I have this Table:</p>
  <pre><code>Trans_ID    Name    Fuzzy_Value    Total_Item  
  100          I1  0.33333333        3  
  100          I2  0.33333333        3  
  100          I5  0.33333333        3  
  200          I2  0.5               2  
  200          I5  0.5               2  
  300          I2  0.5               2  
  300          I3  0.5               2  
  400          I1  0.33333333        3  
  400          I2  0.33333333        3  
  400          I4  0.33333333        3  
  500          I1  0.5               2  
  500          I3  0.5               2  
  600          I2  0.5               2  
  600          I3  0.5               2  
  700          I1  0.5               2  
  700          I3  0.5               2  
  800          I1  0.25              4  
  800          I2  0.25              4  
  800          I3  0.25              4  
  800          I5  0.25              4  
  900          I1  0.33333333        3  
  900          I2  0.33333333        3  
  900          I3  0.33333333        3  
  1000         I1  0.2               5  
  1000         I2  0.2               5  
  1000         I4  0.2               5  
  1000         I6  0.2               5  
  1000         I8  0.2               5  
  </code></pre>
  <p>And two blank Tables:</p>
  <pre><code>Table  ITEMSET
    
  &quot;ITEM_SET&quot; &quot;Support&quot; 
  
  
  
  Table Confidence
    
  &quot;ANTECEDENT&quot; &quot;CONSEQUENT&quot; 
  </code></pre>
  <hr />
  <p>I need to find FUZZY value for each item that occurs in each transaction:</p>
  <pre><code>I1 = Sum of (Fuzzy_Value from item I1 in trans 100 until 1000 which is trans: 100,400,500,700,800,900,1000)/Total Trans  
  -&gt; (.33333333+0.33333333+0.5+0.5+0.25+0.33333333+0.2)/10 = 0.244999999
  
    
  I2 = Sum of (Fuzzy_Value from item I2 in trans 100 - 1000 which is trans:100,200,300,400,600,800,900,1000)/Total Trans  
  -&gt; (0.33333333+0.5+0.5+0.33333333+0.5+0.25+0.33333333)/10 = 0.274999999
  
  
  I3 -&gt; 0.258333333  
  I4 -&gt; 0.103333333  
  I5 -&gt; 0.058333333    
  I6 -&gt; 0.02    
  I8 -&gt; 0.02    
  </code></pre>
  <p>E.g., I use minimum Support 10% -&gt; 0.1<br />
  I need to remove I5,I6,I8 since it's value &lt; 0.1  =&gt; <strong>prune step</strong></p>
  <p>then store:</p>
  <pre><code>I1=0.244999999, I2=0.274999999, I3=0.258333333,I4=0.103333333  on new table 'ITEMSET' 
  </code></pre>
  <hr />
  <p><strong>2 COMBINATIONS</strong></p>
  <p><strong>NOTE: This is the basic 1st step after this most likely need to use repeat or recursive, since the process will keep going on until no other item combination is possible</strong><br />
  then from what's left I need to find K+1 itemset (which is 2 combination itemset) =&gt; <strong>join step</strong></p>
  <pre><code>{I1,I2} =Sum of (Fuzzy_Value from item I1 + I2 in trans 100 - 1000 which is trans:100,400,800,900,1000)/Total Trans 
  -&gt;(0.666666667+0.666666667+0.5+0.666666667+0.4)/9 = 0.29
  
  *do the same for the rest*
  {I1,I3} =(1+1+0.5+0.666666667)/9 = 0.316666667
  {I1,I4} =(0.666666667+0.4)/9 = 0.106666667
  {I2,I3} =(1+1+0.5+0.666666667)/9 = 0.316666667
  {I2,I4} =(1+0.666666667+0.4)/9 =0.206666667
  {I3,I4} =0  
  </code></pre>
  <p>Then Do another <strong>Prune Step</strong> removing less than 0.1 value which is {I3,I4}</p>
  <pre><code>Store {I1,I2} = 0.29, {I1,I3} = 0.316666667, {I1,I4} =0.106666667, {I2,I3} = 0.316666667, {I2,I4} = 0.206666667  AT &quot;ITEMSET&quot; TABLE 
  </code></pre>
  <hr />
  <p><strong>3 COMBINATION</strong></p>
  <p>After that Do another <strong>JOIN STEP</strong> combining itemset that pass pruning</p>
  <pre><code>{I1,I2,I3} = Sum of (Fuzzy_Value from item I1 + I2 +I3 in trans 100 - 1000 which is trans:800,900)/Total Trans  
  -&gt; 0.75+1 = 0.175  
  **Same for the rest**  
  {I1,I2,I4} = 1+0.6 = 0.16  
  {I2,I3,I4} = 0  
  </code></pre>
  <p>Do another <strong>Prune Step</strong> removing less than 0.1 value which is {I1,I3,I4}</p>
  <pre><code>Store {I1,I2,I3} = 0.176 AND {I1,I2,I4} = 0,16 AT &quot;ITEMSET&quot; TABLE  
  </code></pre>
  <hr />
  <p><strong>4 COMBINATION</strong></p>
  <p>Combine itemset that pass pruning  K+4 (4 combination)</p>
  <pre><code>{I1,I2,I3,I4} = 0
  </code></pre>
  <p>**since no transaction containing this item</p>
  <p>after process stop since there's no possible combination left</p>
  <hr />
  <p>At this point, ITEMSET database have:</p>
  <pre><code>ITEM_SET           Support  
  {I1}               0.244999999
  {I2}               0.274999999     
  {I3}               0.258333333    
  {I4}               0.103333333  
  {I1,I2}            0.29    
  {I1,I3}            0.316666667  
  {I1,I4}            0.106666667  
  {I2,I3}            0.316666667  
  {I2,I4}            0.206666667  
  {I1,I2,I3}         0.176  
  {I1,I2,I4}         0,16  
  </code></pre>
  <hr />
  <p>How do I code that in sql?
  Thank you very much!</p>
  <p><em>Note</em>: You can add another table as needed.</p>