| <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> |
| <html xmlns="http://www.w3.org/1999/xhtml"> |
| <head> |
| <meta http-equiv="Content-Type" content="text/xhtml;charset=UTF-8"/> |
| <meta http-equiv="X-UA-Compatible" content="IE=9"/> |
| <meta name="generator" content="Doxygen 1.8.4"/> |
| <title>MADlib: assoc_rules.sql_in Source File</title> |
| <link href="tabs.css" rel="stylesheet" type="text/css"/> |
| <script type="text/javascript" src="jquery.js"></script> |
| <script type="text/javascript" src="dynsections.js"></script> |
| <link href="navtree.css" rel="stylesheet" type="text/css"/> |
| <script type="text/javascript" src="resize.js"></script> |
| <script type="text/javascript" src="navtree.js"></script> |
| <script type="text/javascript"> |
| $(document).ready(initResizable); |
| $(window).load(resizeHeight); |
| </script> |
| <link href="search/search.css" rel="stylesheet" type="text/css"/> |
| <script type="text/javascript" src="search/search.js"></script> |
| <script type="text/javascript"> |
| $(document).ready(function() { searchBox.OnSelectItem(0); }); |
| </script> |
| <script type="text/x-mathjax-config"> |
| MathJax.Hub.Config({ |
| extensions: ["tex2jax.js", "TeX/AMSmath.js", "TeX/AMSsymbols.js"], |
| jax: ["input/TeX","output/HTML-CSS"], |
| }); |
| </script><script src="../mathjax/MathJax.js"></script> |
| <link href="doxygen.css" rel="stylesheet" type="text/css" /> |
| </head> |
| <body> |
| <div id="top"><!-- do not remove this div, it is closed by doxygen! --> |
| <div id="titlearea"> |
| <table cellspacing="0" cellpadding="0"> |
| <tbody> |
| <tr style="height: 56px;"> |
| <td style="padding-left: 0.5em;"> |
| <div id="projectname">MADlib |
|  <span id="projectnumber">1.0</span> <span style="font-size:10pt; font-style:italic"><a href="../latest/./assoc__rules_8sql__in_source.html"> A newer version is available</a></span> |
| </div> |
| <div id="projectbrief">User Documentation</div> |
| </td> |
| </tr> |
| </tbody> |
| </table> |
| </div> |
| <!-- end header part --> |
| <!-- Generated by Doxygen 1.8.4 --> |
| <script type="text/javascript"> |
| var searchBox = new SearchBox("searchBox", "search",false,'Search'); |
| </script> |
| <div id="navrow1" class="tabs"> |
| <ul class="tablist"> |
| <li><a href="index.html"><span>Main Page</span></a></li> |
| <li><a href="modules.html"><span>Modules</span></a></li> |
| <li> |
| <div id="MSearchBox" class="MSearchBoxInactive"> |
| <span class="left"> |
| <img id="MSearchSelect" src="search/mag_sel.png" |
| onmouseover="return searchBox.OnSearchSelectShow()" |
| onmouseout="return searchBox.OnSearchSelectHide()" |
| alt=""/> |
| <input type="text" id="MSearchField" value="Search" accesskey="S" |
| onfocus="searchBox.OnSearchFieldFocus(true)" |
| onblur="searchBox.OnSearchFieldFocus(false)" |
| onkeyup="searchBox.OnSearchFieldChange(event)"/> |
| </span><span class="right"> |
| <a id="MSearchClose" href="javascript:searchBox.CloseResultsWindow()"><img id="MSearchCloseImg" border="0" src="search/close.png" alt=""/></a> |
| </span> |
| </div> |
| </li> |
| </ul> |
| </div> |
| </div><!-- top --> |
| <div id="side-nav" class="ui-resizable side-nav-resizable"> |
| <div id="nav-tree"> |
| <div id="nav-tree-contents"> |
| <div id="nav-sync" class="sync"></div> |
| </div> |
| </div> |
| <div id="splitbar" style="-moz-user-select:none;" |
| class="ui-resizable-handle"> |
| </div> |
| </div> |
| <script type="text/javascript"> |
| $(document).ready(function(){initNavTree('assoc__rules_8sql__in_source.html','');}); |
| </script> |
| <div id="doc-content"> |
| <!-- window showing the filter options --> |
| <div id="MSearchSelectWindow" |
| onmouseover="return searchBox.OnSearchSelectShow()" |
| onmouseout="return searchBox.OnSearchSelectHide()" |
| onkeydown="return searchBox.OnSearchSelectKey(event)"> |
| <a class="SelectItem" href="javascript:void(0)" onclick="searchBox.OnSelectItem(0)"><span class="SelectionMark"> </span>All</a><a class="SelectItem" href="javascript:void(0)" onclick="searchBox.OnSelectItem(1)"><span class="SelectionMark"> </span>Files</a><a class="SelectItem" href="javascript:void(0)" onclick="searchBox.OnSelectItem(2)"><span class="SelectionMark"> </span>Functions</a><a class="SelectItem" href="javascript:void(0)" onclick="searchBox.OnSelectItem(3)"><span class="SelectionMark"> </span>Groups</a></div> |
| |
| <!-- iframe showing the search results (closed by default) --> |
| <div id="MSearchResultsWindow"> |
| <iframe src="javascript:void(0)" frameborder="0" |
| name="MSearchResults" id="MSearchResults"> |
| </iframe> |
| </div> |
| |
| <div class="header"> |
| <div class="headertitle"> |
| <div class="title">assoc_rules.sql_in</div> </div> |
| </div><!--header--> |
| <div class="contents"> |
| <a href="assoc__rules_8sql__in.html">Go to the documentation of this file.</a><div class="fragment"><div class="line"><a name="l00001"></a><span class="lineno"> 1</span> <span class="comment">/* ----------------------------------------------------------------------- */</span><span class="comment">/**</span></div> |
| <div class="line"><a name="l00002"></a><span class="lineno"> 2</span> <span class="comment"> *</span></div> |
| <div class="line"><a name="l00003"></a><span class="lineno"> 3</span> <span class="comment"> * @file assoc_rules.sql_in</span></div> |
| <div class="line"><a name="l00004"></a><span class="lineno"> 4</span> <span class="comment"> *</span></div> |
| <div class="line"><a name="l00005"></a><span class="lineno"> 5</span> <span class="comment"> * @brief The \ref assoc_rules function computes association rules for a given set of data. The data is assumed to have two dimensions; items (between which we are trying to discover associations), and a transaction id. This tranaction id groups the items by event and could also be a user id, date, etc. depending on the context of the data. This function assumes the data is stored in two columns with one transaction id and one item per row.</span></div> |
| <div class="line"><a name="l00006"></a><span class="lineno"> 6</span> <span class="comment"></span></div> |
| <div class="line"><a name="l00007"></a><span class="lineno"> 7</span> <span class="comment"> * @date June 2011</span></div> |
| <div class="line"><a name="l00008"></a><span class="lineno"> 8</span> <span class="comment"> * @modified August 2012</span></div> |
| <div class="line"><a name="l00009"></a><span class="lineno"> 9</span> <span class="comment"> *</span></div> |
| <div class="line"><a name="l00010"></a><span class="lineno"> 10</span> <span class="comment"> * @sa For a brief introduction to the association rules implementation, see the module</span></div> |
| <div class="line"><a name="l00011"></a><span class="lineno"> 11</span> <span class="comment"> * description \ref grp_assoc_rules.</span></div> |
| <div class="line"><a name="l00012"></a><span class="lineno"> 12</span> <span class="comment"> *</span></div> |
| <div class="line"><a name="l00013"></a><span class="lineno"> 13</span> <span class="comment"> */</span><span class="comment">/* ----------------------------------------------------------------------- */</span></div> |
| <div class="line"><a name="l00014"></a><span class="lineno"> 14</span> </div> |
| <div class="line"><a name="l00015"></a><span class="lineno"> 15</span> m4_include(`SQLCommon.m4<span class="stringliteral">')</span></div> |
| <div class="line"><a name="l00016"></a><span class="lineno"> 16</span> <span class="stringliteral"></span></div> |
| <div class="line"><a name="l00017"></a><span class="lineno"> 17</span> <span class="stringliteral"></span><span class="comment"></span></div> |
| <div class="line"><a name="l00018"></a><span class="lineno"> 18</span> <span class="comment">/**</span></div> |
| <div class="line"><a name="l00019"></a><span class="lineno"> 19</span> <span class="comment">@addtogroup grp_assoc_rules</span></div> |
| <div class="line"><a name="l00020"></a><span class="lineno"> 20</span> <span class="comment"></span></div> |
| <div class="line"><a name="l00021"></a><span class="lineno"> 21</span> <span class="comment">@about</span></div> |
| <div class="line"><a name="l00022"></a><span class="lineno"> 22</span> <span class="comment">This module implements the association rules data mining technique on a transactional data set. Given the names of a table and the columns, minimum support and confidence values, this function generates all single and multidimensional association rules that meet the minimum thresholds.</span></div> |
| <div class="line"><a name="l00023"></a><span class="lineno"> 23</span> <span class="comment"></span></div> |
| <div class="line"><a name="l00024"></a><span class="lineno"> 24</span> <span class="comment">Association rule mining is a widely used technique for discovering relationships between variables in a large data set (e.g items in a store that are commonly purchased together). The classic market basket analysis example using association rules is the "beer and diapers" rule. According to data mining urban legend, a study of customers' purchase behavior in a supermarket found that men often purchased beer and diapers together. After making this discovery, the managers strategically placed beer and diapers closer together on the shelves and saw a dramatic increase in sales. In addition to market basket analysis, association rules are also used in bioinformatics, web analytics, and several other fields.</span></div> |
| <div class="line"><a name="l00025"></a><span class="lineno"> 25</span> <span class="comment"></span></div> |
| <div class="line"><a name="l00026"></a><span class="lineno"> 26</span> <span class="comment">This type of data mining algorithm uses transactional data. Every transaction event has a unique identification, and each transaction consists of a set of items (or itemset). Purchases are considered binary (either it was purchased or not), and this implementation does not take into consideration the quantity of each item. For the MADlib association rules function, it is assumed that the data is stored in two columns with one item and transaction id per row. Transactions with multiple items will span multiple rows with one row per item.</span></div> |
| <div class="line"><a name="l00027"></a><span class="lineno"> 27</span> <span class="comment"></span></div> |
| <div class="line"><a name="l00028"></a><span class="lineno"> 28</span> <span class="comment"><pre></span></div> |
| <div class="line"><a name="l00029"></a><span class="lineno"> 29</span> <span class="comment"> tran_id | product</span></div> |
| <div class="line"><a name="l00030"></a><span class="lineno"> 30</span> <span class="comment"> ---------+---------</span></div> |
| <div class="line"><a name="l00031"></a><span class="lineno"> 31</span> <span class="comment"> 1 | 1</span></div> |
| <div class="line"><a name="l00032"></a><span class="lineno"> 32</span> <span class="comment"> 1 | 2</span></div> |
| <div class="line"><a name="l00033"></a><span class="lineno"> 33</span> <span class="comment"> 1 | 3</span></div> |
| <div class="line"><a name="l00034"></a><span class="lineno"> 34</span> <span class="comment"> 1 | 4</span></div> |
| <div class="line"><a name="l00035"></a><span class="lineno"> 35</span> <span class="comment"> 2 | 3</span></div> |
| <div class="line"><a name="l00036"></a><span class="lineno"> 36</span> <span class="comment"> 2 | 4</span></div> |
| <div class="line"><a name="l00037"></a><span class="lineno"> 37</span> <span class="comment"> 2 | 5</span></div> |
| <div class="line"><a name="l00038"></a><span class="lineno"> 38</span> <span class="comment"> 3 | 1</span></div> |
| <div class="line"><a name="l00039"></a><span class="lineno"> 39</span> <span class="comment"> 3 | 4</span></div> |
| <div class="line"><a name="l00040"></a><span class="lineno"> 40</span> <span class="comment"> 3 | 6</span></div> |
| <div class="line"><a name="l00041"></a><span class="lineno"> 41</span> <span class="comment"> ...</span></div> |
| <div class="line"><a name="l00042"></a><span class="lineno"> 42</span> <span class="comment"></pre></span></div> |
| <div class="line"><a name="l00043"></a><span class="lineno"> 43</span> <span class="comment"></span></div> |
| <div class="line"><a name="l00044"></a><span class="lineno"> 44</span> <span class="comment">\b Rules</span></div> |
| <div class="line"><a name="l00045"></a><span class="lineno"> 45</span> <span class="comment"></span></div> |
| <div class="line"><a name="l00046"></a><span class="lineno"> 46</span> <span class="comment">Association rules take the form "If X, then Y", where X and Y are non-empty itemsets. X and Y are called the antecedent and consequent, or the left-hand-side and right-hand-side, of the rule respectively. Using our previous example, the association rule may state "If {diapers}, then {beer}" with .2 support and .85 confidence.</span></div> |
| <div class="line"><a name="l00047"></a><span class="lineno"> 47</span> <span class="comment"></span></div> |
| <div class="line"><a name="l00048"></a><span class="lineno"> 48</span> <span class="comment">Given any association rule "If X, then Y", the association rules function will also calculate the following metrics:</span></div> |
| <div class="line"><a name="l00049"></a><span class="lineno"> 49</span> <span class="comment">- Support: The ratio of transactions that contain X to all transactions, T</span></div> |
| <div class="line"><a name="l00050"></a><span class="lineno"> 50</span> <span class="comment">\f[</span></div> |
| <div class="line"><a name="l00051"></a><span class="lineno"> 51</span> <span class="comment">S (X) = \frac{Total X}{Total transactions}</span></div> |
| <div class="line"><a name="l00052"></a><span class="lineno"> 52</span> <span class="comment">\f]</span></div> |
| <div class="line"><a name="l00053"></a><span class="lineno"> 53</span> <span class="comment"></span></div> |
| <div class="line"><a name="l00054"></a><span class="lineno"> 54</span> <span class="comment">- Confidence: The ratio of transactions that contain \f$ X,Y \f$ to transactions that contain \f$ X \f$. One could view this metric as the conditional probability of \f$ Y \f$ , given \f$ X \f$ . \f$ P(Y|X) \f$</span></div> |
| <div class="line"><a name="l00055"></a><span class="lineno"> 55</span> <span class="comment"></span></div> |
| <div class="line"><a name="l00056"></a><span class="lineno"> 56</span> <span class="comment">\f[</span></div> |
| <div class="line"><a name="l00057"></a><span class="lineno"> 57</span> <span class="comment">C (X \Rightarrow Y) = \frac{s(X \cap Y )}{s(X)}</span></div> |
| <div class="line"><a name="l00058"></a><span class="lineno"> 58</span> <span class="comment">\f]</span></div> |
| <div class="line"><a name="l00059"></a><span class="lineno"> 59</span> <span class="comment"></span></div> |
| <div class="line"><a name="l00060"></a><span class="lineno"> 60</span> <span class="comment">- Lift: The ratio of observed support of \f$ X,Y \f$ to the expected support of \f$ X,Y \f$ , assuming \f$ X \f$ and \f$ Y \f$ are independent.</span></div> |
| <div class="line"><a name="l00061"></a><span class="lineno"> 61</span> <span class="comment">\f[</span></div> |
| <div class="line"><a name="l00062"></a><span class="lineno"> 62</span> <span class="comment">L (X \Rightarrow Y) = \frac{s(X \cap Y )}{s(X) \cdot s(Y)}</span></div> |
| <div class="line"><a name="l00063"></a><span class="lineno"> 63</span> <span class="comment">\f]</span></div> |
| <div class="line"><a name="l00064"></a><span class="lineno"> 64</span> <span class="comment"></span></div> |
| <div class="line"><a name="l00065"></a><span class="lineno"> 65</span> <span class="comment">- Conviction: The ratio of expected support of \f$ X \f$ occurring without\f$ Y \f$ assuming \f$ X \f$ and \f$ \neg Y \f$ are independent, to the observed support of \f$ X \f$ occuring without \f$ Y \f$. If conviction is greater than 1, then this metric shows that incorrect predictions ( \f$ X \Rightarrow Y \f$ ) occur less often than if these two actions were independent. This metric can be viewed as the ratio that the association rule would be incorrect if the actions were independent (i.e. a conviction of 1.5 indicates that if the variables were independent, this rule would be incorrect 50% more often.)</span></div> |
| <div class="line"><a name="l00066"></a><span class="lineno"> 66</span> <span class="comment"></span></div> |
| <div class="line"><a name="l00067"></a><span class="lineno"> 67</span> <span class="comment">\f[</span></div> |
| <div class="line"><a name="l00068"></a><span class="lineno"> 68</span> <span class="comment">Conv (X \Rightarrow Y) = \frac{1 - S(Y)}{1 - C(X \Rightarrow Y)}</span></div> |
| <div class="line"><a name="l00069"></a><span class="lineno"> 69</span> <span class="comment">\f]</span></div> |
| <div class="line"><a name="l00070"></a><span class="lineno"> 70</span> <span class="comment"></span></div> |
| <div class="line"><a name="l00071"></a><span class="lineno"> 71</span> <span class="comment"></span></div> |
| <div class="line"><a name="l00072"></a><span class="lineno"> 72</span> <span class="comment">\b Apriori \b algorithm</span></div> |
| <div class="line"><a name="l00073"></a><span class="lineno"> 73</span> <span class="comment"></span></div> |
| <div class="line"><a name="l00074"></a><span class="lineno"> 74</span> <span class="comment">Although there are many algorithms that generate association rules, the classic algorithm used is called Apriori (which we implemented in this module). It is a breadth-first search, as opposed to depth-first searches like eclat. Frequent itemsets of order \f$ n \f$ are generated from sets of order \f$ n - 1 \f$. Using the downward closure property, all sets must have frequent subsets. There are two steps in this algorithm; generating frequent itemsets, and using these itemsets to construct the association rules. A simplified version of the algorithm is as follows, and assumes a minimum level of support and confidence is provided:</span></div> |
| <div class="line"><a name="l00075"></a><span class="lineno"> 75</span> <span class="comment"></span></div> |
| <div class="line"><a name="l00076"></a><span class="lineno"> 76</span> <span class="comment">\e Initial \e step</span></div> |
| <div class="line"><a name="l00077"></a><span class="lineno"> 77</span> <span class="comment">-# Generate all itemsets of order 1</span></div> |
| <div class="line"><a name="l00078"></a><span class="lineno"> 78</span> <span class="comment">-# Eliminate itemsets that have support is less than minimum support</span></div> |
| <div class="line"><a name="l00079"></a><span class="lineno"> 79</span> <span class="comment"></span></div> |
| <div class="line"><a name="l00080"></a><span class="lineno"> 80</span> <span class="comment">\e Main \e algorithm</span></div> |
| <div class="line"><a name="l00081"></a><span class="lineno"> 81</span> <span class="comment">-# For \f$ n \ge 2 \f$, generate itemsets of order \f$ n \f$ by combining the itemsets of order \f$ n - 1 \f$. This is done by doing the union of two itemsets that have identical items except one.</span></div> |
| <div class="line"><a name="l00082"></a><span class="lineno"> 82</span> <span class="comment">-# Eliminate itemsets that have (n-1) order subsets with insufficient support</span></div> |
| <div class="line"><a name="l00083"></a><span class="lineno"> 83</span> <span class="comment">-# Eliminate itemsets with insufficient support</span></div> |
| <div class="line"><a name="l00084"></a><span class="lineno"> 84</span> <span class="comment">-# Repeat until itemsets cannot be generated</span></div> |
| <div class="line"><a name="l00085"></a><span class="lineno"> 85</span> <span class="comment"></span></div> |
| <div class="line"><a name="l00086"></a><span class="lineno"> 86</span> <span class="comment">\e Association \e rule \e generation</span></div> |
| <div class="line"><a name="l00087"></a><span class="lineno"> 87</span> <span class="comment"></span></div> |
| <div class="line"><a name="l00088"></a><span class="lineno"> 88</span> <span class="comment">Given a frequent itemset \f$ A \f$ generated from the Apriori algorithm, and all subsets \f$ B \f$ , we generate rules such that \f$ B \Rightarrow (A - B) \f$ meets minimum confidence requirements.</span></div> |
| <div class="line"><a name="l00089"></a><span class="lineno"> 89</span> <span class="comment"></span></div> |
| <div class="line"><a name="l00090"></a><span class="lineno"> 90</span> <span class="comment">@input</span></div> |
| <div class="line"><a name="l00091"></a><span class="lineno"> 91</span> <span class="comment"></span></div> |
| <div class="line"><a name="l00092"></a><span class="lineno"> 92</span> <span class="comment">The input data is expected to be of the following form:</span></div> |
| <div class="line"><a name="l00093"></a><span class="lineno"> 93</span> <span class="comment"><pre>{TABLE|VIEW} <em>input_table</em> (</span></div> |
| <div class="line"><a name="l00094"></a><span class="lineno"> 94</span> <span class="comment"> <em>trans_id</em> INTEGER,</span></div> |
| <div class="line"><a name="l00095"></a><span class="lineno"> 95</span> <span class="comment"> <em>product</em> TEXT</span></div> |
| <div class="line"><a name="l00096"></a><span class="lineno"> 96</span> <span class="comment">)</pre></span></div> |
| <div class="line"><a name="l00097"></a><span class="lineno"> 97</span> <span class="comment"></span></div> |
| <div class="line"><a name="l00098"></a><span class="lineno"> 98</span> <span class="comment">The algorithm will map the product names to consective integer ids starting at 1. If they are already structured this way, then the ids will not change.</span></div> |
| <div class="line"><a name="l00099"></a><span class="lineno"> 99</span> <span class="comment"></span></div> |
| <div class="line"><a name="l00100"></a><span class="lineno"> 100</span> <span class="comment">@usage</span></div> |
| <div class="line"><a name="l00101"></a><span class="lineno"> 101</span> <span class="comment">- Association rules can be called by:</span></div> |
| <div class="line"><a name="l00102"></a><span class="lineno"> 102</span> <span class="comment"> <pre>SELECT \ref assoc_rules(</span></div> |
| <div class="line"><a name="l00103"></a><span class="lineno"> 103</span> <span class="comment"> <em>support</em>, <em>confidence</em>,'<em>tid_col</em>','<em>item_col</em>',</span></div> |
| <div class="line"><a name="l00104"></a><span class="lineno"> 104</span> <span class="comment"> '<em>input_table</em>','<em>output_schema</em>', <em> verbose </em></span></div> |
| <div class="line"><a name="l00105"></a><span class="lineno"> 105</span> <span class="comment"> );</pre></span></div> |
| <div class="line"><a name="l00106"></a><span class="lineno"> 106</span> <span class="comment"> This will generate all association rules that meet a minimum support of <em>support</em> and confidence of <em>confidence</em>.</span></div> |
| <div class="line"><a name="l00107"></a><span class="lineno"> 107</span> <span class="comment"></span></div> |
| <div class="line"><a name="l00108"></a><span class="lineno"> 108</span> <span class="comment">- The results containing the rules, support, confidence, lift, and conviction are stored in the table assoc_rules in the schema specified by <em>output_schema</em>.</span></div> |
| <div class="line"><a name="l00109"></a><span class="lineno"> 109</span> <span class="comment"><pre></span></div> |
| <div class="line"><a name="l00110"></a><span class="lineno"> 110</span> <span class="comment"> Table "output_schema.assoc_rules"</span></div> |
| <div class="line"><a name="l00111"></a><span class="lineno"> 111</span> <span class="comment"> Column | Type | Modifiers </span></div> |
| <div class="line"><a name="l00112"></a><span class="lineno"> 112</span> <span class="comment"> ------------+------------------+-----------</span></div> |
| <div class="line"><a name="l00113"></a><span class="lineno"> 113</span> <span class="comment"> ruleid | integer | </span></div> |
| <div class="line"><a name="l00114"></a><span class="lineno"> 114</span> <span class="comment"> pre | text[] | </span></div> |
| <div class="line"><a name="l00115"></a><span class="lineno"> 115</span> <span class="comment"> post | text[] | </span></div> |
| <div class="line"><a name="l00116"></a><span class="lineno"> 116</span> <span class="comment"> support | double precision | </span></div> |
| <div class="line"><a name="l00117"></a><span class="lineno"> 117</span> <span class="comment"> confidence | double precision | </span></div> |
| <div class="line"><a name="l00118"></a><span class="lineno"> 118</span> <span class="comment"> lift | double precision | </span></div> |
| <div class="line"><a name="l00119"></a><span class="lineno"> 119</span> <span class="comment"> conviction | double precision | </span></div> |
| <div class="line"><a name="l00120"></a><span class="lineno"> 120</span> <span class="comment"> Distributed by: (ruleid)</span></div> |
| <div class="line"><a name="l00121"></a><span class="lineno"> 121</span> <span class="comment"></span></div> |
| <div class="line"><a name="l00122"></a><span class="lineno"> 122</span> <span class="comment"></pre></span></div> |
| <div class="line"><a name="l00123"></a><span class="lineno"> 123</span> <span class="comment"> The \c pre and \c post are the itemsets of left and right hand sides of the association rule respectively. The \c support, \c confidence, \c lift, and \c conviction columns are calculated as mentioned in the about section.</span></div> |
| <div class="line"><a name="l00124"></a><span class="lineno"> 124</span> <span class="comment"></span></div> |
| <div class="line"><a name="l00125"></a><span class="lineno"> 125</span> <span class="comment">@implementation</span></div> |
| <div class="line"><a name="l00126"></a><span class="lineno"> 126</span> <span class="comment"></span></div> |
| <div class="line"><a name="l00127"></a><span class="lineno"> 127</span> <span class="comment">The association rules function will always create a table named assoc_rules. Please make a copy of this table before running the function again if you would like to keep multiple association rule tables.</span></div> |
| <div class="line"><a name="l00128"></a><span class="lineno"> 128</span> <span class="comment"></span></div> |
| <div class="line"><a name="l00129"></a><span class="lineno"> 129</span> <span class="comment">@examp</span></div> |
| <div class="line"><a name="l00130"></a><span class="lineno"> 130</span> <span class="comment"></span></div> |
| <div class="line"><a name="l00131"></a><span class="lineno"> 131</span> <span class="comment">Let us take a look at some sample transactional data and generate association rules:</span></div> |
| <div class="line"><a name="l00132"></a><span class="lineno"> 132</span> <span class="comment"></span></div> |
| <div class="line"><a name="l00133"></a><span class="lineno"> 133</span> <span class="comment">\code</span></div> |
| <div class="line"><a name="l00134"></a><span class="lineno"> 134</span> <span class="comment">DROP TABLE IF EXISTS test_data;</span></div> |
| <div class="line"><a name="l00135"></a><span class="lineno"> 135</span> <span class="comment">CREATE TABLE test_data (</span></div> |
| <div class="line"><a name="l00136"></a><span class="lineno"> 136</span> <span class="comment"> trans_id INT</span></div> |
| <div class="line"><a name="l00137"></a><span class="lineno"> 137</span> <span class="comment"> , product text</span></div> |
| <div class="line"><a name="l00138"></a><span class="lineno"> 138</span> <span class="comment">);</span></div> |
| <div class="line"><a name="l00139"></a><span class="lineno"> 139</span> <span class="comment"></span></div> |
| <div class="line"><a name="l00140"></a><span class="lineno"> 140</span> <span class="comment">INSERT INTO test_data VALUES (1, 'beer');</span></div> |
| <div class="line"><a name="l00141"></a><span class="lineno"> 141</span> <span class="comment">INSERT INTO test_data VALUES (1, 'diapers');</span></div> |
| <div class="line"><a name="l00142"></a><span class="lineno"> 142</span> <span class="comment">INSERT INTO test_data VALUES (1, 'chips');</span></div> |
| <div class="line"><a name="l00143"></a><span class="lineno"> 143</span> <span class="comment">INSERT INTO test_data VALUES (2, 'beer');</span></div> |
| <div class="line"><a name="l00144"></a><span class="lineno"> 144</span> <span class="comment">INSERT INTO test_data VALUES (2, 'diapers');</span></div> |
| <div class="line"><a name="l00145"></a><span class="lineno"> 145</span> <span class="comment">INSERT INTO test_data VALUES (3, 'beer');</span></div> |
| <div class="line"><a name="l00146"></a><span class="lineno"> 146</span> <span class="comment">INSERT INTO test_data VALUES (3, 'diapers');</span></div> |
| <div class="line"><a name="l00147"></a><span class="lineno"> 147</span> <span class="comment">INSERT INTO test_data VALUES (4, 'beer');</span></div> |
| <div class="line"><a name="l00148"></a><span class="lineno"> 148</span> <span class="comment">INSERT INTO test_data VALUES (4, 'chips');</span></div> |
| <div class="line"><a name="l00149"></a><span class="lineno"> 149</span> <span class="comment">INSERT INTO test_data VALUES (5, 'beer');</span></div> |
| <div class="line"><a name="l00150"></a><span class="lineno"> 150</span> <span class="comment">INSERT INTO test_data VALUES (6, 'beer');</span></div> |
| <div class="line"><a name="l00151"></a><span class="lineno"> 151</span> <span class="comment">INSERT INTO test_data VALUES (6, 'diapers');</span></div> |
| <div class="line"><a name="l00152"></a><span class="lineno"> 152</span> <span class="comment">INSERT INTO test_data VALUES (6, 'chips');</span></div> |
| <div class="line"><a name="l00153"></a><span class="lineno"> 153</span> <span class="comment">INSERT INTO test_data VALUES (7, 'beer');</span></div> |
| <div class="line"><a name="l00154"></a><span class="lineno"> 154</span> <span class="comment">INSERT INTO test_data VALUES (7, 'diapers');</span></div> |
| <div class="line"><a name="l00155"></a><span class="lineno"> 155</span> <span class="comment"></span></div> |
| <div class="line"><a name="l00156"></a><span class="lineno"> 156</span> <span class="comment">\endcode</span></div> |
| <div class="line"><a name="l00157"></a><span class="lineno"> 157</span> <span class="comment"></span></div> |
| <div class="line"><a name="l00158"></a><span class="lineno"> 158</span> <span class="comment">Let \f$ min(support) = .25 \f$ and \f$ min(confidence) = .5 \f$, and the output schema be 'myschema'. For this example, we will set verbose to 'true' so that we have some insight into the progress of the function. We can now generate association rules as follows:</span></div> |
| <div class="line"><a name="l00159"></a><span class="lineno"> 159</span> <span class="comment"></span></div> |
| <div class="line"><a name="l00160"></a><span class="lineno"> 160</span> <span class="comment">\code</span></div> |
| <div class="line"><a name="l00161"></a><span class="lineno"> 161</span> <span class="comment"></span></div> |
| <div class="line"><a name="l00162"></a><span class="lineno"> 162</span> <span class="comment">SELECT * FROM MADLIB_SCHEMA.assoc_rules (.25, .5, 'trans_id', 'product', 'test_data','myschema', false);</span></div> |
| <div class="line"><a name="l00163"></a><span class="lineno"> 163</span> <span class="comment"></span></div> |
| <div class="line"><a name="l00164"></a><span class="lineno"> 164</span> <span class="comment">\endcode</span></div> |
| <div class="line"><a name="l00165"></a><span class="lineno"> 165</span> <span class="comment"></span></div> |
| <div class="line"><a name="l00166"></a><span class="lineno"> 166</span> <span class="comment">This should generate this output:</span></div> |
| <div class="line"><a name="l00167"></a><span class="lineno"> 167</span> <span class="comment"></span></div> |
| <div class="line"><a name="l00168"></a><span class="lineno"> 168</span> <span class="comment">\code</span></div> |
| <div class="line"><a name="l00169"></a><span class="lineno"> 169</span> <span class="comment"></span></div> |
| <div class="line"><a name="l00170"></a><span class="lineno"> 170</span> <span class="comment"> output_schema | output_table | total_rules | total_time </span></div> |
| <div class="line"><a name="l00171"></a><span class="lineno"> 171</span> <span class="comment">---------------+--------------+-------------+-----------------</span></div> |
| <div class="line"><a name="l00172"></a><span class="lineno"> 172</span> <span class="comment"> myschema | assoc_rules | 7 | 00:00:03.162094</span></div> |
| <div class="line"><a name="l00173"></a><span class="lineno"> 173</span> <span class="comment">(1 row)</span></div> |
| <div class="line"><a name="l00174"></a><span class="lineno"> 174</span> <span class="comment"></span></div> |
| <div class="line"><a name="l00175"></a><span class="lineno"> 175</span> <span class="comment"></span></div> |
| <div class="line"><a name="l00176"></a><span class="lineno"> 176</span> <span class="comment">\endcode</span></div> |
| <div class="line"><a name="l00177"></a><span class="lineno"> 177</span> <span class="comment"></span></div> |
| <div class="line"><a name="l00178"></a><span class="lineno"> 178</span> <span class="comment">The association rules are stored in the myschema.assoc_rules:</span></div> |
| <div class="line"><a name="l00179"></a><span class="lineno"> 179</span> <span class="comment"></span></div> |
| <div class="line"><a name="l00180"></a><span class="lineno"> 180</span> <span class="comment">\code</span></div> |
| <div class="line"><a name="l00181"></a><span class="lineno"> 181</span> <span class="comment">select * from myschema.assoc_rules order by support desc;</span></div> |
| <div class="line"><a name="l00182"></a><span class="lineno"> 182</span> <span class="comment"> ruleid | pre | post | support | confidence | lift | conviction </span></div> |
| <div class="line"><a name="l00183"></a><span class="lineno"> 183</span> <span class="comment">--------+-----------------+----------------+-------------------+-------------------+-------------------+-------------------</span></div> |
| <div class="line"><a name="l00184"></a><span class="lineno"> 184</span> <span class="comment"> 4 | {diapers} | {beer} | 0.714285714285714 | 1 | 1 | 0</span></div> |
| <div class="line"><a name="l00185"></a><span class="lineno"> 185</span> <span class="comment"> 2 | {beer} | {diapers} | 0.714285714285714 | 0.714285714285714 | 1 | 1</span></div> |
| <div class="line"><a name="l00186"></a><span class="lineno"> 186</span> <span class="comment"> 1 | {chips} | {beer} | 0.428571428571429 | 1 | 1 | 0</span></div> |
| <div class="line"><a name="l00187"></a><span class="lineno"> 187</span> <span class="comment"> 5 | {chips} | {beer,diapers} | 0.285714285714286 | 0.666666666666667 | 0.933333333333333 | 0.857142857142857</span></div> |
| <div class="line"><a name="l00188"></a><span class="lineno"> 188</span> <span class="comment"> 6 | {chips,beer} | {diapers} | 0.285714285714286 | 0.666666666666667 | 0.933333333333333 | 0.857142857142857</span></div> |
| <div class="line"><a name="l00189"></a><span class="lineno"> 189</span> <span class="comment"> 7 | {chips,diapers} | {beer} | 0.285714285714286 | 1 | 1 | 0</span></div> |
| <div class="line"><a name="l00190"></a><span class="lineno"> 190</span> <span class="comment"> 3 | {chips} | {diapers} | 0.285714285714286 | 0.666666666666667 | 0.933333333333333 | 0.857142857142857</span></div> |
| <div class="line"><a name="l00191"></a><span class="lineno"> 191</span> <span class="comment">(7 rows)</span></div> |
| <div class="line"><a name="l00192"></a><span class="lineno"> 192</span> <span class="comment"></span></div> |
| <div class="line"><a name="l00193"></a><span class="lineno"> 193</span> <span class="comment">\endcode</span></div> |
| <div class="line"><a name="l00194"></a><span class="lineno"> 194</span> <span class="comment"></span></div> |
| <div class="line"><a name="l00195"></a><span class="lineno"> 195</span> <span class="comment">@sa File assoc_rules.sql_in documenting the SQL function.</span></div> |
| <div class="line"><a name="l00196"></a><span class="lineno"> 196</span> <span class="comment"></span></div> |
| <div class="line"><a name="l00197"></a><span class="lineno"> 197</span> <span class="comment">*/</span></div> |
| <div class="line"><a name="l00198"></a><span class="lineno"> 198</span> </div> |
| <div class="line"><a name="l00199"></a><span class="lineno"> 199</span> /*</div> |
| <div class="line"><a name="l00200"></a><span class="lineno"> 200</span>  * @brief The result data type for the association rule API</div> |
| <div class="line"><a name="l00201"></a><span class="lineno"> 201</span>  *</div> |
| <div class="line"><a name="l00202"></a><span class="lineno"> 202</span>  * output_schema the name of the output schema.</div> |
| <div class="line"><a name="l00203"></a><span class="lineno"> 203</span>  * output_table the name of the output table.</div> |
| <div class="line"><a name="l00204"></a><span class="lineno"> 204</span>  * total_rules the number of total rules.</div> |
| <div class="line"><a name="l00205"></a><span class="lineno"> 205</span>  * total_time the running time.</div> |
| <div class="line"><a name="l00206"></a><span class="lineno"> 206</span>  */</div> |
| <div class="line"><a name="l00207"></a><span class="lineno"> 207</span> CREATE TYPE MADLIB_SCHEMA.assoc_rules_results AS</div> |
| <div class="line"><a name="l00208"></a><span class="lineno"> 208</span>  (</div> |
| <div class="line"><a name="l00209"></a><span class="lineno"> 209</span>  output_schema TEXT,</div> |
| <div class="line"><a name="l00210"></a><span class="lineno"> 210</span>  output_table TEXT,</div> |
| <div class="line"><a name="l00211"></a><span class="lineno"> 211</span>  total_rules INT,</div> |
| <div class="line"><a name="l00212"></a><span class="lineno"> 212</span>  total_time INTERVAL</div> |
| <div class="line"><a name="l00213"></a><span class="lineno"> 213</span> );</div> |
| <div class="line"><a name="l00214"></a><span class="lineno"> 214</span> </div> |
| <div class="line"><a name="l00215"></a><span class="lineno"> 215</span> </div> |
| <div class="line"><a name="l00216"></a><span class="lineno"> 216</span> /*</div> |
| <div class="line"><a name="l00217"></a><span class="lineno"> 217</span>  * @brief Given the text form of a closed frequent pattern (cfp), this function</div> |
| <div class="line"><a name="l00218"></a><span class="lineno"> 218</span>  * generates the association rules for that pattern. We use text format</div> |
| <div class="line"><a name="l00219"></a><span class="lineno"> 219</span>  * because text values are hash joinable. The output is a set of text</div> |
| <div class="line"><a name="l00220"></a><span class="lineno"> 220</span>  * array. For example, assuming the input pattern is '1,2,3<span class="stringliteral">'.</span></div> |
| <div class="line"><a name="l00221"></a><span class="lineno"> 221</span> <span class="stringliteral"> * The result rules:</span></div> |
| <div class="line"><a name="l00222"></a><span class="lineno"> 222</span> <span class="stringliteral"> * array['</span>1<span class="stringliteral">', '</span>2,3<span class="stringliteral">']</span></div> |
| <div class="line"><a name="l00223"></a><span class="lineno"> 223</span> <span class="stringliteral"> * array['</span>2<span class="stringliteral">', '</span>1,3<span class="stringliteral">']</span></div> |
| <div class="line"><a name="l00224"></a><span class="lineno"> 224</span> <span class="stringliteral"> * array['</span>3<span class="stringliteral">', '</span>1,2<span class="stringliteral">']</span></div> |
| <div class="line"><a name="l00225"></a><span class="lineno"> 225</span> <span class="stringliteral"> * array['</span>1,2<span class="stringliteral">', '</span>3<span class="stringliteral">']</span></div> |
| <div class="line"><a name="l00226"></a><span class="lineno"> 226</span> <span class="stringliteral"> * array['</span>1,3<span class="stringliteral">', '</span>2<span class="stringliteral">']</span></div> |
| <div class="line"><a name="l00227"></a><span class="lineno"> 227</span> <span class="stringliteral"> * array['</span>2,3<span class="stringliteral">', '</span>1<span class="stringliteral">']</span></div> |
| <div class="line"><a name="l00228"></a><span class="lineno"> 228</span> <span class="stringliteral"> * Note that two meaningless rules will be excluded:</span></div> |
| <div class="line"><a name="l00229"></a><span class="lineno"> 229</span> <span class="stringliteral"> * array['</span>1,2,3<span class="stringliteral">', NULL]</span></div> |
| <div class="line"><a name="l00230"></a><span class="lineno"> 230</span> <span class="stringliteral"> * array[NULL, '</span>1,2,3<span class="stringliteral">']</span></div> |
| <div class="line"><a name="l00231"></a><span class="lineno"> 231</span> <span class="stringliteral"> *</span></div> |
| <div class="line"><a name="l00232"></a><span class="lineno"> 232</span> <span class="stringliteral"> * @param arg 1 The text form of a closed frequent pattern.</span></div> |
| <div class="line"><a name="l00233"></a><span class="lineno"> 233</span> <span class="stringliteral"> * @param arg 2 The number of items in the pattern.</span></div> |
| <div class="line"><a name="l00234"></a><span class="lineno"> 234</span> <span class="stringliteral"> *</span></div> |
| <div class="line"><a name="l00235"></a><span class="lineno"> 235</span> <span class="stringliteral"> * @return A set of text array. Each array has two elements, corresponding to</span></div> |
| <div class="line"><a name="l00236"></a><span class="lineno"> 236</span> <span class="stringliteral"> * the left and right parts of an association rule.</span></div> |
| <div class="line"><a name="l00237"></a><span class="lineno"> 237</span> <span class="stringliteral"> *</span></div> |
| <div class="line"><a name="l00238"></a><span class="lineno"> 238</span> <span class="stringliteral"> */</span></div> |
| <div class="line"><a name="l00239"></a><span class="lineno"> 239</span> <span class="stringliteral">CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.gen_rules_from_cfp</span></div> |
| <div class="line"><a name="l00240"></a><span class="lineno"> 240</span> <span class="stringliteral"> (</span></div> |
| <div class="line"><a name="l00241"></a><span class="lineno"> 241</span> <span class="stringliteral"> TEXT,</span></div> |
| <div class="line"><a name="l00242"></a><span class="lineno"> 242</span> <span class="stringliteral"> INT</span></div> |
| <div class="line"><a name="l00243"></a><span class="lineno"> 243</span> <span class="stringliteral"> )</span></div> |
| <div class="line"><a name="l00244"></a><span class="lineno"> 244</span> <span class="stringliteral">RETURNS SETOF TEXT[] AS '</span>MODULE_PATHNAME<span class="stringliteral">'</span></div> |
| <div class="line"><a name="l00245"></a><span class="lineno"> 245</span> <span class="stringliteral">LANGUAGE C STRICT IMMUTABLE;</span></div> |
| <div class="line"><a name="l00246"></a><span class="lineno"> 246</span> <span class="stringliteral"></span></div> |
| <div class="line"><a name="l00247"></a><span class="lineno"> 247</span> <span class="stringliteral"></span><span class="comment"></span></div> |
| <div class="line"><a name="l00248"></a><span class="lineno"> 248</span> <span class="comment">/**</span></div> |
| <div class="line"><a name="l00249"></a><span class="lineno"> 249</span> <span class="comment"> *</span></div> |
| <div class="line"><a name="l00250"></a><span class="lineno"> 250</span> <span class="comment"> * @param support minimum level of support needed for each itemset to</span></div> |
| <div class="line"><a name="l00251"></a><span class="lineno"> 251</span> <span class="comment"> * be included in result</span></div> |
| <div class="line"><a name="l00252"></a><span class="lineno"> 252</span> <span class="comment"> * @param confidence minimum level of confidence needed for each rule to</span></div> |
| <div class="line"><a name="l00253"></a><span class="lineno"> 253</span> <span class="comment"> * be included in result</span></div> |
| <div class="line"><a name="l00254"></a><span class="lineno"> 254</span> <span class="comment"> * @param tid_col name of the column storing the transaction ids</span></div> |
| <div class="line"><a name="l00255"></a><span class="lineno"> 255</span> <span class="comment"> * @param item_col name of the column storing the products</span></div> |
| <div class="line"><a name="l00256"></a><span class="lineno"> 256</span> <span class="comment"> * @param input_table name of the table where the data is stored</span></div> |
| <div class="line"><a name="l00257"></a><span class="lineno"> 257</span> <span class="comment"> * @param output_schema name of the schema where the final results will be stored</span></div> |
| <div class="line"><a name="l00258"></a><span class="lineno"> 258</span> <span class="comment"> * @param verbose determining if output contains comments</span></div> |
| <div class="line"><a name="l00259"></a><span class="lineno"> 259</span> <span class="comment"> *</span></div> |
| <div class="line"><a name="l00260"></a><span class="lineno"> 260</span> <span class="comment"> * @returns The schema and table name containing association rules,</span></div> |
| <div class="line"><a name="l00261"></a><span class="lineno"> 261</span> <span class="comment"> * and total number of rules found.</span></div> |
| <div class="line"><a name="l00262"></a><span class="lineno"> 262</span> <span class="comment"> *</span></div> |
| <div class="line"><a name="l00263"></a><span class="lineno"> 263</span> <span class="comment"> * This function computes the association rules between products in a data set.</span></div> |
| <div class="line"><a name="l00264"></a><span class="lineno"> 264</span> <span class="comment"> * It reads the name of the table, the column names of the product and ids, and</span></div> |
| <div class="line"><a name="l00265"></a><span class="lineno"> 265</span> <span class="comment"> * computes ssociation rules using the Apriori algorithm, and subject to the</span></div> |
| <div class="line"><a name="l00266"></a><span class="lineno"> 266</span> <span class="comment"> * support and confidence constraints as input by the user. This version of</span></div> |
| <div class="line"><a name="l00267"></a><span class="lineno"> 267</span> <span class="comment"> * association rules has verbose functionality. When verbose is true, output of</span></div> |
| <div class="line"><a name="l00268"></a><span class="lineno"> 268</span> <span class="comment"> * function includes iteration steps and comments on Apriori algorithm steps.</span></div> |
| <div class="line"><a name="l00269"></a><span class="lineno"> 269</span> <span class="comment"> *</span></div> |
| <div class="line"><a name="l00270"></a><span class="lineno"> 270</span> <span class="comment"> */</span></div> |
| <div class="line"><a name="l00271"></a><span class="lineno"> 271</span> CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.assoc_rules</div> |
| <div class="line"><a name="l00272"></a><span class="lineno"> 272</span>  (</div> |
| <div class="line"><a name="l00273"></a><span class="lineno"> 273</span>  support FLOAT8,</div> |
| <div class="line"><a name="l00274"></a><span class="lineno"> 274</span>  confidence FLOAT8,</div> |
| <div class="line"><a name="l00275"></a><span class="lineno"> 275</span>  tid_col TEXT,</div> |
| <div class="line"><a name="l00276"></a><span class="lineno"> 276</span>  item_col TEXT,</div> |
| <div class="line"><a name="l00277"></a><span class="lineno"> 277</span>  input_table TEXT,</div> |
| <div class="line"><a name="l00278"></a><span class="lineno"> 278</span>  output_schema TEXT,</div> |
| <div class="line"><a name="l00279"></a><span class="lineno"> 279</span>  verbose BOOLEAN</div> |
| <div class="line"><a name="l00280"></a><span class="lineno"> 280</span>  )</div> |
| <div class="line"><a name="l00281"></a><span class="lineno"> 281</span> RETURNS MADLIB_SCHEMA.assoc_rules_results</div> |
| <div class="line"><a name="l00282"></a><span class="lineno"> 282</span> AS $$</div> |
| <div class="line"><a name="l00283"></a><span class="lineno"> 283</span> </div> |
| <div class="line"><a name="l00284"></a><span class="lineno"> 284</span>  PythonFunctionBodyOnly(`assoc_rules', `<a class="code" href="assoc__rules_8sql__in.html#af9456adb6dad01e452415b9a0a5371dc">assoc_rules</a><span class="stringliteral">')</span></div> |
| <div class="line"><a name="l00285"></a><span class="lineno"> 285</span> <span class="stringliteral"></span></div> |
| <div class="line"><a name="l00286"></a><span class="lineno"> 286</span> <span class="stringliteral"> plpy.execute("SET client_min_messages = error;")</span></div> |
| <div class="line"><a name="l00287"></a><span class="lineno"> 287</span> <span class="stringliteral"></span></div> |
| <div class="line"><a name="l00288"></a><span class="lineno"> 288</span> <span class="stringliteral"> # schema_madlib comes from PythonFunctionBodyOnly</span></div> |
| <div class="line"><a name="l00289"></a><span class="lineno"> 289</span> <span class="stringliteral"> return assoc_rules.assoc_rules(</span></div> |
| <div class="line"><a name="l00290"></a><span class="lineno"> 290</span> <span class="stringliteral"> schema_madlib,</span></div> |
| <div class="line"><a name="l00291"></a><span class="lineno"> 291</span> <span class="stringliteral"> support,</span></div> |
| <div class="line"><a name="l00292"></a><span class="lineno"> 292</span> <span class="stringliteral"> confidence,</span></div> |
| <div class="line"><a name="l00293"></a><span class="lineno"><a class="code" href="assoc__rules_8sql__in.html#af9456adb6dad01e452415b9a0a5371dc"> 293</a></span> <span class="stringliteral"> tid_col,</span></div> |
| <div class="line"><a name="l00294"></a><span class="lineno"> 294</span> <span class="stringliteral"> item_col,</span></div> |
| <div class="line"><a name="l00295"></a><span class="lineno"> 295</span> <span class="stringliteral"> input_table,</span></div> |
| <div class="line"><a name="l00296"></a><span class="lineno"> 296</span> <span class="stringliteral"> output_schema,</span></div> |
| <div class="line"><a name="l00297"></a><span class="lineno"> 297</span> <span class="stringliteral"> verbose</span></div> |
| <div class="line"><a name="l00298"></a><span class="lineno"> 298</span> <span class="stringliteral"> );</span></div> |
| <div class="line"><a name="l00299"></a><span class="lineno"> 299</span> <span class="stringliteral"></span></div> |
| <div class="line"><a name="l00300"></a><span class="lineno"> 300</span> <span class="stringliteral">$$ LANGUAGE plpythonu;</span></div> |
| <div class="line"><a name="l00301"></a><span class="lineno"> 301</span> <span class="stringliteral"></span></div> |
| <div class="line"><a name="l00302"></a><span class="lineno"> 302</span> <span class="stringliteral"></span><span class="comment"></span></div> |
| <div class="line"><a name="l00303"></a><span class="lineno"> 303</span> <span class="comment">/**</span></div> |
| <div class="line"><a name="l00304"></a><span class="lineno"> 304</span> <span class="comment"> *</span></div> |
| <div class="line"><a name="l00305"></a><span class="lineno"> 305</span> <span class="comment"> * @brief The short form of the above function with vobose removed.</span></div> |
| <div class="line"><a name="l00306"></a><span class="lineno"> 306</span> <span class="comment"> *</span></div> |
| <div class="line"><a name="l00307"></a><span class="lineno"> 307</span> <span class="comment"> */</span></div> |
| <div class="line"><a name="l00308"></a><span class="lineno"> 308</span> CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.assoc_rules</div> |
| <div class="line"><a name="l00309"></a><span class="lineno"> 309</span>  (</div> |
| <div class="line"><a name="l00310"></a><span class="lineno"> 310</span>  support FLOAT8,</div> |
| <div class="line"><a name="l00311"></a><span class="lineno"> 311</span>  confidence FLOAT8,</div> |
| <div class="line"><a name="l00312"></a><span class="lineno"> 312</span>  tid_col TEXT,</div> |
| <div class="line"><a name="l00313"></a><span class="lineno"> 313</span>  item_col TEXT,</div> |
| <div class="line"><a name="l00314"></a><span class="lineno"> 314</span>  input_table TEXT,</div> |
| <div class="line"><a name="l00315"></a><span class="lineno"> 315</span>  output_schema TEXT</div> |
| <div class="line"><a name="l00316"></a><span class="lineno"> 316</span>  )</div> |
| <div class="line"><a name="l00317"></a><span class="lineno"> 317</span> RETURNS MADLIB_SCHEMA.assoc_rules_results</div> |
| <div class="line"><a name="l00318"></a><span class="lineno"> 318</span> AS $$</div> |
| <div class="line"><a name="l00319"></a><span class="lineno"> 319</span> </div> |
| <div class="line"><a name="l00320"></a><span class="lineno"> 320</span>  PythonFunctionBodyOnly(`assoc_rules', `<a class="code" href="assoc__rules_8sql__in.html#af9456adb6dad01e452415b9a0a5371dc">assoc_rules</a><span class="stringliteral">')</span></div> |
| <div class="line"><a name="l00321"></a><span class="lineno"> 321</span> <span class="stringliteral"></span></div> |
| <div class="line"><a name="l00322"></a><span class="lineno"> 322</span> <span class="stringliteral"> plpy.execute("SET client_min_messages = error;")</span></div> |
| <div class="line"><a name="l00323"></a><span class="lineno"> 323</span> <span class="stringliteral"></span></div> |
| <div class="line"><a name="l00324"></a><span class="lineno"> 324</span> <span class="stringliteral"> # schema_madlib comes from PythonFunctionBodyOnly</span></div> |
| <div class="line"><a name="l00325"></a><span class="lineno"> 325</span> <span class="stringliteral"> return assoc_rules.assoc_rules(</span></div> |
| <div class="line"><a name="l00326"></a><span class="lineno"> 326</span> <span class="stringliteral"> schema_madlib,</span></div> |
| <div class="line"><a name="l00327"></a><span class="lineno"> 327</span> <span class="stringliteral"> support,</span></div> |
| <div class="line"><a name="l00328"></a><span class="lineno"> 328</span> <span class="stringliteral"> confidence,</span></div> |
| <div class="line"><a name="l00329"></a><span class="lineno"> 329</span> <span class="stringliteral"> tid_col,</span></div> |
| <div class="line"><a name="l00330"></a><span class="lineno"><a class="code" href="assoc__rules_8sql__in.html#a68a256d98b82ac15bac7df92e806f6f8"> 330</a></span> <span class="stringliteral"> item_col,</span></div> |
| <div class="line"><a name="l00331"></a><span class="lineno"> 331</span> <span class="stringliteral"> input_table,</span></div> |
| <div class="line"><a name="l00332"></a><span class="lineno"> 332</span> <span class="stringliteral"> output_schema,</span></div> |
| <div class="line"><a name="l00333"></a><span class="lineno"> 333</span> <span class="stringliteral"> False</span></div> |
| <div class="line"><a name="l00334"></a><span class="lineno"> 334</span> <span class="stringliteral"> );</span></div> |
| <div class="line"><a name="l00335"></a><span class="lineno"> 335</span> <span class="stringliteral"></span></div> |
| <div class="line"><a name="l00336"></a><span class="lineno"> 336</span> <span class="stringliteral">$$ LANGUAGE plpythonu;</span></div> |
| </div><!-- fragment --></div><!-- contents --> |
| </div><!-- doc-content --> |
| <!-- start footer part --> |
| <div id="nav-path" class="navpath"><!-- id is needed for treeview function! --> |
| <ul> |
| <li class="navelem"><a class="el" href="dir_68267d1309a1af8e8297ef4c3efbcdba.html">src</a></li><li class="navelem"><a class="el" href="dir_efbcf68973d247bbf15f9eecae7f24e3.html">ports</a></li><li class="navelem"><a class="el" href="dir_a4a48839224ef8488facbffa8a397967.html">postgres</a></li><li class="navelem"><a class="el" href="dir_dc596537ad427a4d866006d1a3e1fe29.html">modules</a></li><li class="navelem"><a class="el" href="dir_df86748cb94fb6c2fa09e991cce090c0.html">assoc_rules</a></li><li class="navelem"><a class="el" href="assoc__rules_8sql__in.html">assoc_rules.sql_in</a></li> |
| <li class="footer">Generated on Tue Sep 10 2013 15:48:04 for MADlib by |
| <a href="http://www.doxygen.org/index.html"> |
| <img class="footer" src="doxygen.png" alt="doxygen"/></a> 1.8.4 </li> |
| </ul> |
| </div> |
| </body> |
| </html> |
