| <!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"/> |
| <title>MADlib: c45.sql_in Source File</title> |
| |
| <link href="tabs.css" rel="stylesheet" type="text/css"/> |
| <link href="doxygen.css" rel="stylesheet" type="text/css" /> |
| <link href="navtree.css" rel="stylesheet" type="text/css"/> |
| <script type="text/javascript" src="jquery.js"></script> |
| <script type="text/javascript" src="resize.js"></script> |
| <script type="text/javascript" src="navtree.js"></script> |
| <script type="text/javascript"> |
| $(document).ready(initResizable); |
| </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 src="../mathjax/MathJax.js"> |
| MathJax.Hub.Config({ |
| extensions: ["tex2jax.js", "TeX/AMSmath.js", "TeX/AMSsymbols.js"], |
| jax: ["input/TeX","output/HTML-CSS"], |
| }); |
| </script> |
| </head> |
| <body> |
| <div id="top"><!-- do not remove this div! --> |
| |
| |
| <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">0.6</span> <span style="font-size:10pt; font-style:italic"><a href="../latest/./c45_8sql__in_source.html"> A newer version is available</a></span> |
| </div> |
| <div id="projectbrief">User Documentation</div> |
| </td> |
| |
| |
| |
| </tr> |
| </tbody> |
| </table> |
| </div> |
| |
| <!-- Generated by Doxygen 1.7.5.1 --> |
| <script type="text/javascript"> |
| var searchBox = new SearchBox("searchBox", "search",false,'Search'); |
| </script> |
| <script type="text/javascript" src="dynsections.js"></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 class="current"><a href="files.html"><span>Files</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 id="navrow2" class="tabs2"> |
| <ul class="tablist"> |
| <li><a href="files.html"><span>File List</span></a></li> |
| <li><a href="globals.html"><span>File Members</span></a></li> |
| </ul> |
| </div> |
| </div> |
| <div id="side-nav" class="ui-resizable side-nav-resizable"> |
| <div id="nav-tree"> |
| <div id="nav-tree-contents"> |
| </div> |
| </div> |
| <div id="splitbar" style="-moz-user-select:none;" |
| class="ui-resizable-handle"> |
| </div> |
| </div> |
| <script type="text/javascript"> |
| initNavTree('c45_8sql__in.html',''); |
| </script> |
| <div id="doc-content"> |
| <div class="header"> |
| <div class="headertitle"> |
| <div class="title">c45.sql_in</div> </div> |
| </div> |
| <div class="contents"> |
| <a href="c45_8sql__in.html">Go to the documentation of this file.</a><div class="fragment"><pre class="fragment"><a name="l00001"></a>00001 <span class="comment">/* ----------------------------------------------------------------------- */</span><span class="comment">/** </span> |
| <a name="l00002"></a>00002 <span class="comment"> *</span> |
| <a name="l00003"></a>00003 <span class="comment"> * @file c45.sql_in</span> |
| <a name="l00004"></a>00004 <span class="comment"> *</span> |
| <a name="l00005"></a>00005 <span class="comment"> * @brief C4.5 APIs and main controller written in PL/PGSQL</span> |
| <a name="l00006"></a>00006 <span class="comment"> * @date April 5, 2012</span> |
| <a name="l00007"></a>00007 <span class="comment"> *</span> |
| <a name="l00008"></a>00008 <span class="comment"> * @sa For a brief introduction to decision trees, see the</span> |
| <a name="l00009"></a>00009 <span class="comment"> * module description \ref grp_dectree.</span> |
| <a name="l00010"></a>00010 <span class="comment"> *</span> |
| <a name="l00011"></a>00011 <span class="comment"> */</span><span class="comment">/* ----------------------------------------------------------------------- */</span> |
| <a name="l00012"></a>00012 |
| <a name="l00013"></a>00013 m4_include(`SQLCommon.m4<span class="stringliteral">')</span> |
| <a name="l00014"></a>00014 <span class="stringliteral"></span> |
| <a name="l00015"></a>00015 <span class="stringliteral">/* Own macro definitions */</span> |
| <a name="l00016"></a>00016 <span class="stringliteral">m4_ifelse(</span> |
| <a name="l00017"></a>00017 <span class="stringliteral"> m4_eval(</span> |
| <a name="l00018"></a>00018 <span class="stringliteral"> m4_ifdef(`__GREENPLUM__'</span>, 1, 0) && |
| <a name="l00019"></a>00019 __DBMS_VERSION_MAJOR__ * 100 + __DBMS_VERSION_MINOR__ < 401 |
| <a name="l00020"></a>00020 ), 1, |
| <a name="l00021"></a>00021 `m4_define(`__GREENPLUM_PRE_4_1__<span class="charliteral">')'</span> |
| <a name="l00022"></a>00022 ) |
| <a name="l00023"></a>00023 m4_ifelse( |
| <a name="l00024"></a>00024 m4_eval( |
| <a name="l00025"></a>00025 m4_ifdef(`__POSTGRESQL__<span class="stringliteral">', 1, 0) &&</span> |
| <a name="l00026"></a>00026 <span class="stringliteral"> __DBMS_VERSION_MAJOR__ < 9</span> |
| <a name="l00027"></a>00027 <span class="stringliteral"> ), 1,</span> |
| <a name="l00028"></a>00028 <span class="stringliteral"> `m4_define(`__POSTGRESQL_PRE_9_0__'</span>)<span class="stringliteral">'</span> |
| <a name="l00029"></a>00029 <span class="stringliteral">)</span> |
| <a name="l00030"></a>00030 <span class="stringliteral"></span><span class="comment"></span> |
| <a name="l00031"></a>00031 <span class="comment">/**</span> |
| <a name="l00032"></a>00032 <span class="comment">@addtogroup grp_dectree</span> |
| <a name="l00033"></a>00033 <span class="comment"></span> |
| <a name="l00034"></a>00034 <span class="comment">@about</span> |
| <a name="l00035"></a>00035 <span class="comment"></span> |
| <a name="l00036"></a>00036 <span class="comment">This module provides an implementation of the C4.5 implementation to </span> |
| <a name="l00037"></a>00037 <span class="comment">grow decision trees.</span> |
| <a name="l00038"></a>00038 <span class="comment"></span> |
| <a name="l00039"></a>00039 <span class="comment">The implementation supports:</span> |
| <a name="l00040"></a>00040 <span class="comment">- Building decision tree</span> |
| <a name="l00041"></a>00041 <span class="comment">- Multiple split critera, including:</span> |
| <a name="l00042"></a>00042 <span class="comment"> . Information Gain</span> |
| <a name="l00043"></a>00043 <span class="comment"> . Gini Coefficient</span> |
| <a name="l00044"></a>00044 <span class="comment"> . Gain Ratio</span> |
| <a name="l00045"></a>00045 <span class="comment">- Decision tree Pruning</span> |
| <a name="l00046"></a>00046 <span class="comment">- Decision tree classification/scoring</span> |
| <a name="l00047"></a>00047 <span class="comment">- Decision tree display</span> |
| <a name="l00048"></a>00048 <span class="comment">- Rule generation</span> |
| <a name="l00049"></a>00049 <span class="comment">- Continuous and discrete features</span> |
| <a name="l00050"></a>00050 <span class="comment">- Missing value handling</span> |
| <a name="l00051"></a>00051 <span class="comment"></span> |
| <a name="l00052"></a>00052 <span class="comment">@input</span> |
| <a name="l00053"></a>00053 <span class="comment"></span> |
| <a name="l00054"></a>00054 <span class="comment">The <b>training data</b> is expected to be of </span> |
| <a name="l00055"></a>00055 <span class="comment">the following form:</span> |
| <a name="l00056"></a>00056 <span class="comment"><pre>{TABLE|VIEW} <em>trainingSource</em> (</span> |
| <a name="l00057"></a>00057 <span class="comment"> ...</span> |
| <a name="l00058"></a>00058 <span class="comment"> <em>id</em> INT|BIGINT,</span> |
| <a name="l00059"></a>00059 <span class="comment"> <em>feature1</em> SUPPORTED_DATA_TYPE,</span> |
| <a name="l00060"></a>00060 <span class="comment"> <em>feature2</em> SUPPORTED_DATA_TYPE,</span> |
| <a name="l00061"></a>00061 <span class="comment"> <em>feature3</em> SUPPORTED_DATA_TYPE,</span> |
| <a name="l00062"></a>00062 <span class="comment"> ....................</span> |
| <a name="l00063"></a>00063 <span class="comment"> <em>featureN</em> SUPPORTED_DATA_TYPE,</span> |
| <a name="l00064"></a>00064 <span class="comment"> <em>class</em> SUPPORTED_DATA_TYPE,</span> |
| <a name="l00065"></a>00065 <span class="comment"> ...</span> |
| <a name="l00066"></a>00066 <span class="comment">)</pre></span> |
| <a name="l00067"></a>00067 <span class="comment"></span> |
| <a name="l00068"></a>00068 <span class="comment">The detailed list of SUPPORTED_DATA_TYPE is: </span> |
| <a name="l00069"></a>00069 <span class="comment">SMALLINT, INT, BIGINT, FLOAT8, REAL, </span> |
| <a name="l00070"></a>00070 <span class="comment">DECIMAL, INET, CIDR, MACADDR, BOOLEAN,</span> |
| <a name="l00071"></a>00071 <span class="comment">CHAR, VARCHAR, TEXT, "char", </span> |
| <a name="l00072"></a>00072 <span class="comment">DATE, TIME, TIMETZ, TIMESTAMP, TIMESTAMPTZ, and INTERVAL.</span> |
| <a name="l00073"></a>00073 <span class="comment"></span> |
| <a name="l00074"></a>00074 <span class="comment">The <b>data to classify</b> is expected to be </span> |
| <a name="l00075"></a>00075 <span class="comment">of the same form as <b>training data</b>, except</span> |
| <a name="l00076"></a>00076 <span class="comment">that it does not need a class column.</span> |
| <a name="l00077"></a>00077 <span class="comment"></span> |
| <a name="l00078"></a>00078 <span class="comment">@usage</span> |
| <a name="l00079"></a>00079 <span class="comment"></span> |
| <a name="l00080"></a>00080 <span class="comment">- Run the training algorithm on the source data:</span> |
| <a name="l00081"></a>00081 <span class="comment"> <pre>SELECT * FROM \ref c45_train(</span> |
| <a name="l00082"></a>00082 <span class="comment"> '<em>split_criterion</em>',</span> |
| <a name="l00083"></a>00083 <span class="comment"> '<em>training_table_name</em>', </span> |
| <a name="l00084"></a>00084 <span class="comment"> '<em>result_tree_table_name</em>', </span> |
| <a name="l00085"></a>00085 <span class="comment"> '<em>validation_table_name</em>',</span> |
| <a name="l00086"></a>00086 <span class="comment"> '<em>continuous_feature_names</em>',</span> |
| <a name="l00087"></a>00087 <span class="comment"> '<em>feature_col_names</em>',</span> |
| <a name="l00088"></a>00088 <span class="comment"> '<em>id_col_name</em>', </span> |
| <a name="l00089"></a>00089 <span class="comment"> '<em>class_col_name</em>',</span> |
| <a name="l00090"></a>00090 <span class="comment"> '<em>confidence_level</em>',</span> |
| <a name="l00091"></a>00091 <span class="comment"> '<em>how2handle_missing_value</em>'</span> |
| <a name="l00092"></a>00092 <span class="comment"> '<em>max_tree_depth</em>',</span> |
| <a name="l00093"></a>00093 <span class="comment"> '<em>node_prune_threshold</em>',</span> |
| <a name="l00094"></a>00094 <span class="comment"> '<em>node_split_threshold</em>'</span> |
| <a name="l00095"></a>00095 <span class="comment"> '<em>verbosity</em>');</span> |
| <a name="l00096"></a>00096 <span class="comment"> </pre></span> |
| <a name="l00097"></a>00097 <span class="comment"> This will create the decision tree output table storing an abstract object</span> |
| <a name="l00098"></a>00098 <span class="comment"> (representing the model) used for further classification. Column names:</span> |
| <a name="l00099"></a>00099 <span class="comment"> <pre> </span> |
| <a name="l00100"></a>00100 <span class="comment"> id | tree_location | feature | probability | ebp_coeff | maxclass | scv | live | sample_size | parent_id | lmc_nid | lmc_fval | is_continuous | split_value | tid | dp_ids </span> |
| <a name="l00101"></a>00101 <span class="comment">----+---------------+---------+-------------------+------------------+----------+-------------------+------+-----------+-----------+---------+----------+-----------------+-------------+-----+--------</span> |
| <a name="l00102"></a>00102 <span class="comment"> ...</pre> </span> |
| <a name="l00103"></a>00103 <span class="comment"> </span> |
| <a name="l00104"></a>00104 <span class="comment">- Run the classification function using the learned model: </span> |
| <a name="l00105"></a>00105 <span class="comment"> <pre>SELECT * FROM \ref c45_classify(</span> |
| <a name="l00106"></a>00106 <span class="comment"> '<em>tree_table_name</em>', </span> |
| <a name="l00107"></a>00107 <span class="comment"> '<em>classification_table_name</em>', </span> |
| <a name="l00108"></a>00108 <span class="comment"> '<em>result_table_name</em>');</pre></span> |
| <a name="l00109"></a>00109 <span class="comment"> This will create the result_table with the </span> |
| <a name="l00110"></a>00110 <span class="comment"> classification results. </span> |
| <a name="l00111"></a>00111 <span class="comment"> <pre> </pre> </span> |
| <a name="l00112"></a>00112 <span class="comment"></span> |
| <a name="l00113"></a>00113 <span class="comment">- Run the scorinf function to score the learned model against a validation data set:</span> |
| <a name="l00114"></a>00114 <span class="comment"> <pre>SELECT * FROM \ref c45_score(</span> |
| <a name="l00115"></a>00115 <span class="comment"> '<em>tree_table_name</em>',</span> |
| <a name="l00116"></a>00116 <span class="comment"> '<em>validation_table_name</em>',</span> |
| <a name="l00117"></a>00117 <span class="comment"> '<em>verbosity</em>');</pre></span> |
| <a name="l00118"></a>00118 <span class="comment"> This will give a ratio of correctly classified items in the validation set.</span> |
| <a name="l00119"></a>00119 <span class="comment"> <pre> </pre></span> |
| <a name="l00120"></a>00120 <span class="comment"></span> |
| <a name="l00121"></a>00121 <span class="comment">- Run the display tree function using the learned model: </span> |
| <a name="l00122"></a>00122 <span class="comment"> <pre>SELECT * FROM \ref c45_display(</span> |
| <a name="l00123"></a>00123 <span class="comment"> '<em>tree_table_name</em>');</pre></span> |
| <a name="l00124"></a>00124 <span class="comment"> This will display the trained tree in human readable format. </span> |
| <a name="l00125"></a>00125 <span class="comment"> <pre> </pre> </span> |
| <a name="l00126"></a>00126 <span class="comment"></span> |
| <a name="l00127"></a>00127 <span class="comment">- Run the clean tree function as below: </span> |
| <a name="l00128"></a>00128 <span class="comment"> <pre>SELECT * FROM \ref c45_clean(</span> |
| <a name="l00129"></a>00129 <span class="comment"> '<em>tree_table_name</em>');</pre></span> |
| <a name="l00130"></a>00130 <span class="comment"> This will clean up the learned model and all metadata.</span> |
| <a name="l00131"></a>00131 <span class="comment"> <pre> </pre> </span> |
| <a name="l00132"></a>00132 <span class="comment"></span> |
| <a name="l00133"></a>00133 <span class="comment">@examp</span> |
| <a name="l00134"></a>00134 <span class="comment"></span> |
| <a name="l00135"></a>00135 <span class="comment">-# Prepare an input table/view, e.g.:</span> |
| <a name="l00136"></a>00136 <span class="comment">\verbatim</span> |
| <a name="l00137"></a>00137 <span class="comment">sql> select * from golf_data order by id;</span> |
| <a name="l00138"></a>00138 <span class="comment"> id | outlook | temperature | humidity | windy | class </span> |
| <a name="l00139"></a>00139 <span class="comment">----+----------+-------------+----------+--------+--------------</span> |
| <a name="l00140"></a>00140 <span class="comment"> 1 | sunny | 85 | 85 | false | Do not Play</span> |
| <a name="l00141"></a>00141 <span class="comment"> 2 | sunny | 80 | 90 | true | Do not Play</span> |
| <a name="l00142"></a>00142 <span class="comment"> 3 | overcast | 83 | 78 | false | Play</span> |
| <a name="l00143"></a>00143 <span class="comment"> 4 | rain | 70 | 96 | false | Play</span> |
| <a name="l00144"></a>00144 <span class="comment"> 5 | rain | 68 | 80 | false | Play</span> |
| <a name="l00145"></a>00145 <span class="comment"> 6 | rain | 65 | 70 | true | Do not Play</span> |
| <a name="l00146"></a>00146 <span class="comment"> 7 | overcast | 64 | 65 | true | Play</span> |
| <a name="l00147"></a>00147 <span class="comment"> 8 | sunny | 72 | 95 | false | Do not Play</span> |
| <a name="l00148"></a>00148 <span class="comment"> 9 | sunny | 69 | 70 | false | Play</span> |
| <a name="l00149"></a>00149 <span class="comment"> 10 | rain | 75 | 80 | false | Play</span> |
| <a name="l00150"></a>00150 <span class="comment"> 11 | sunny | 75 | 70 | true | Play</span> |
| <a name="l00151"></a>00151 <span class="comment"> 12 | overcast | 72 | 90 | true | Play</span> |
| <a name="l00152"></a>00152 <span class="comment"> 13 | overcast | 81 | 75 | false | Play</span> |
| <a name="l00153"></a>00153 <span class="comment"> 14 | rain | 71 | 80 | true | Do not Play</span> |
| <a name="l00154"></a>00154 <span class="comment">(14 rows)</span> |
| <a name="l00155"></a>00155 <span class="comment"></span> |
| <a name="l00156"></a>00156 <span class="comment">\endverbatim</span> |
| <a name="l00157"></a>00157 <span class="comment">-# Train the decision tree model, e.g.:</span> |
| <a name="l00158"></a>00158 <span class="comment">\verbatim</span> |
| <a name="l00159"></a>00159 <span class="comment">sql> SELECT * FROM MADLIB_SCHEMA.c45_clean('trained_tree_infogain');</span> |
| <a name="l00160"></a>00160 <span class="comment">sql> SELECT * FROM MADLIB_SCHEMA.c45_train(</span> |
| <a name="l00161"></a>00161 <span class="comment"> 'infogain', -- split criterion_name</span> |
| <a name="l00162"></a>00162 <span class="comment"> 'golf_data', -- input table name</span> |
| <a name="l00163"></a>00163 <span class="comment"> 'trained_tree_infogain', -- result tree name</span> |
| <a name="l00164"></a>00164 <span class="comment"> null, -- validation table name</span> |
| <a name="l00165"></a>00165 <span class="comment"> 'temperature,humidity', -- continuous feature names</span> |
| <a name="l00166"></a>00166 <span class="comment"> 'outlook,temperature,humidity,windy', -- feature column names</span> |
| <a name="l00167"></a>00167 <span class="comment"> 'id', -- id column name</span> |
| <a name="l00168"></a>00168 <span class="comment"> 'class', -- class column name</span> |
| <a name="l00169"></a>00169 <span class="comment"> 100, -- confidence level</span> |
| <a name="l00170"></a>00170 <span class="comment"> 'explicit', -- missing value preparation</span> |
| <a name="l00171"></a>00171 <span class="comment"> 5, -- max tree depth</span> |
| <a name="l00172"></a>00172 <span class="comment"> 0.001, -- min percent mode</span> |
| <a name="l00173"></a>00173 <span class="comment"> 0.001, -- min percent split</span> |
| <a name="l00174"></a>00174 <span class="comment"> 0); -- verbosity</span> |
| <a name="l00175"></a>00175 <span class="comment"> training_set_size | tree_nodes | tree_depth | training_time | split_criterion </span> |
| <a name="l00176"></a>00176 <span class="comment">-------------------+------------+------------+-----------------+-----------------</span> |
| <a name="l00177"></a>00177 <span class="comment"> 14 | 8 | 3 | 00:00:00.871805 | infogain</span> |
| <a name="l00178"></a>00178 <span class="comment">(1 row)</span> |
| <a name="l00179"></a>00179 <span class="comment">\endverbatim</span> |
| <a name="l00180"></a>00180 <span class="comment">-# Check few rows from the tree model table:</span> |
| <a name="l00181"></a>00181 <span class="comment">\verbatim</span> |
| <a name="l00182"></a>00182 <span class="comment">sql> select * from trained_tree_infogain order by id;</span> |
| <a name="l00183"></a>00183 <span class="comment"> id | tree_location | feature | probability | ebp_coeff | maxclass | scv | live |sample_size | parent_id | lmc_nid | lmc_fval | is_continuous | split_value </span> |
| <a name="l00184"></a>00184 <span class="comment">----+---------------+---------+-------------------+-----------+----------+-------------------+------+----------+-----------+---------+----------+-----------------+-------------</span> |
| <a name="l00185"></a>00185 <span class="comment"> 1 | {0} | 3 | 0.642857142857143 | 1 | 2 | 0.171033941880327 | 0 | 14 | 0 | 2 | 1 | f | </span> |
| <a name="l00186"></a>00186 <span class="comment"> 2 | {0,1} | 4 | 1 | 1 | 2 | 0 | 0 | 4 | 1 | | | f | </span> |
| <a name="l00187"></a>00187 <span class="comment"> 3 | {0,2} | 4 | 0.6 | 1 | 2 | 0.673011667009257 | 0 | 5 | 1 | 5 | 1 | f | </span> |
| <a name="l00188"></a>00188 <span class="comment"> 4 | {0,3} | 2 | 0.6 | 1 | 1 | 0.673011667009257 | 0 | 5 | 1 | 7 | 1 | t | 70</span> |
| <a name="l00189"></a>00189 <span class="comment"> 5 | {0,2,1} | 4 | 1 | 1 | 2 | 0 | 0 | 3 | 3 | | | f | </span> |
| <a name="l00190"></a>00190 <span class="comment"> 6 | {0,2,2} | 4 | 1 | 1 | 1 | 0 | 0 | 2 | 3 | | | f | </span> |
| <a name="l00191"></a>00191 <span class="comment"> 7 | {0,3,1} | 4 | 1 | 1 | 2 | 0 | 0 | 2 | 4 | | | f | </span> |
| <a name="l00192"></a>00192 <span class="comment"> 8 | {0,3,2} | 4 | 1 | 1 | 1 | 0 | 0 | 3 | 4 | | | f | </span> |
| <a name="l00193"></a>00193 <span class="comment">(8 rows)</span> |
| <a name="l00194"></a>00194 <span class="comment"></span> |
| <a name="l00195"></a>00195 <span class="comment">\endverbatim</span> |
| <a name="l00196"></a>00196 <span class="comment">-# To display the tree with human readable format:</span> |
| <a name="l00197"></a>00197 <span class="comment">\verbatim</span> |
| <a name="l00198"></a>00198 <span class="comment">sql> select MADLIB_SCHEMA.c45_display('trained_tree_infogain');</span> |
| <a name="l00199"></a>00199 <span class="comment"> c45_display </span> |
| <a name="l00200"></a>00200 <span class="comment">---------------------------------------------------------------------------------------</span> |
| <a name="l00201"></a>00201 <span class="comment">Tree 1</span> |
| <a name="l00202"></a>00202 <span class="comment"> Root Node : class( Play) num_elements(14) predict_prob(0.642857142857143) </span> |
| <a name="l00203"></a>00203 <span class="comment"> outlook: = overcast : class( Play) num_elements(4) predict_prob(1) </span> |
| <a name="l00204"></a>00204 <span class="comment"> outlook: = rain : class( Play) num_elements(5) predict_prob(0.6) </span> |
| <a name="l00205"></a>00205 <span class="comment"> windy: = false : class( Play) num_elements(3) predict_prob(1) </span> |
| <a name="l00206"></a>00206 <span class="comment"> windy: = true : class( Do not Play) num_elements(2) predict_prob(1) </span> |
| <a name="l00207"></a>00207 <span class="comment"> outlook: = sunny : class( Do not Play) num_elements(5) predict_prob(0.6) </span> |
| <a name="l00208"></a>00208 <span class="comment"> humidity: <= 70 : class( Play) num_elements(2) predict_prob(1) </span> |
| <a name="l00209"></a>00209 <span class="comment"> humidity: > 70 : class( Do not Play) num_elements(3) predict_prob(1) </span> |
| <a name="l00210"></a>00210 <span class="comment">(1 row)</span> |
| <a name="l00211"></a>00211 <span class="comment"></span> |
| <a name="l00212"></a>00212 <span class="comment">\endverbatim</span> |
| <a name="l00213"></a>00213 <span class="comment">-# To classify data with the learned model:</span> |
| <a name="l00214"></a>00214 <span class="comment">\verbatim</span> |
| <a name="l00215"></a>00215 <span class="comment">sql> select * from MADLIB_SCHEMA.c45_classify</span> |
| <a name="l00216"></a>00216 <span class="comment"> 'trained_tree_infogain', -- name of the trained model</span> |
| <a name="l00217"></a>00217 <span class="comment"> 'golf_data', -- name of the table containing data to classify</span> |
| <a name="l00218"></a>00218 <span class="comment"> 'classification_result'); -- name of the output table</span> |
| <a name="l00219"></a>00219 <span class="comment"> input_set_size | classification_time </span> |
| <a name="l00220"></a>00220 <span class="comment">----------------+-----------------</span> |
| <a name="l00221"></a>00221 <span class="comment"> 14 | 00:00:00.247713</span> |
| <a name="l00222"></a>00222 <span class="comment">(1 row)</span> |
| <a name="l00223"></a>00223 <span class="comment">\endverbatim</span> |
| <a name="l00224"></a>00224 <span class="comment">-# Check classification results: </span> |
| <a name="l00225"></a>00225 <span class="comment">\verbatim</span> |
| <a name="l00226"></a>00226 <span class="comment">sql> select t.id,t.outlook,t.temperature,t.humidity,t.windy,c.class from</span> |
| <a name="l00227"></a>00227 <span class="comment"> MADLIB_SCHEMA.classification_result c,golf_data t where t.id=c.id order by id;</span> |
| <a name="l00228"></a>00228 <span class="comment"> id | outlook | temperature | humidity | windy | class </span> |
| <a name="l00229"></a>00229 <span class="comment">----+----------+-------------+----------+--------+--------------</span> |
| <a name="l00230"></a>00230 <span class="comment"> 1 | sunny | 85 | 85 | false | Do not Play</span> |
| <a name="l00231"></a>00231 <span class="comment"> 2 | sunny | 80 | 90 | true | Do not Play</span> |
| <a name="l00232"></a>00232 <span class="comment"> 3 | overcast | 83 | 78 | false | Play</span> |
| <a name="l00233"></a>00233 <span class="comment"> 4 | rain | 70 | 96 | false | Play</span> |
| <a name="l00234"></a>00234 <span class="comment"> 5 | rain | 68 | 80 | false | Play</span> |
| <a name="l00235"></a>00235 <span class="comment"> 6 | rain | 65 | 70 | true | Do not Play</span> |
| <a name="l00236"></a>00236 <span class="comment"> 7 | overcast | 64 | 65 | true | Play</span> |
| <a name="l00237"></a>00237 <span class="comment"> 8 | sunny | 72 | 95 | false | Do not Play</span> |
| <a name="l00238"></a>00238 <span class="comment"> 9 | sunny | 69 | 70 | false | Play</span> |
| <a name="l00239"></a>00239 <span class="comment"> 10 | rain | 75 | 80 | false | Play</span> |
| <a name="l00240"></a>00240 <span class="comment"> 11 | sunny | 75 | 70 | true | Play</span> |
| <a name="l00241"></a>00241 <span class="comment"> 12 | overcast | 72 | 90 | true | Play</span> |
| <a name="l00242"></a>00242 <span class="comment"> 13 | overcast | 81 | 75 | false | Play</span> |
| <a name="l00243"></a>00243 <span class="comment"> 14 | rain | 71 | 80 | true | Do not Play</span> |
| <a name="l00244"></a>00244 <span class="comment">(14 rows)</span> |
| <a name="l00245"></a>00245 <span class="comment">\endverbatim</span> |
| <a name="l00246"></a>00246 <span class="comment">-# Score the data against a validation set:</span> |
| <a name="l00247"></a>00247 <span class="comment">\verbatim</span> |
| <a name="l00248"></a>00248 <span class="comment">sql> select * from MADLIB_SCHEMA.c45_score(</span> |
| <a name="l00249"></a>00249 <span class="comment"> 'trained_tree_infogain',</span> |
| <a name="l00250"></a>00250 <span class="comment"> 'golf_data_validation',</span> |
| <a name="l00251"></a>00251 <span class="comment"> 0);</span> |
| <a name="l00252"></a>00252 <span class="comment"> c45_score </span> |
| <a name="l00253"></a>00253 <span class="comment">-----------</span> |
| <a name="l00254"></a>00254 <span class="comment"> 1</span> |
| <a name="l00255"></a>00255 <span class="comment">(1 row)</span> |
| <a name="l00256"></a>00256 <span class="comment">\endverbatim</span> |
| <a name="l00257"></a>00257 <span class="comment">-# clean up the tree and metadata: </span> |
| <a name="l00258"></a>00258 <span class="comment">\verbatim</span> |
| <a name="l00259"></a>00259 <span class="comment">testdb=# select MADLIB_SCHEMA.c45_clean('trained_tree_infogain');</span> |
| <a name="l00260"></a>00260 <span class="comment"> c45_clean </span> |
| <a name="l00261"></a>00261 <span class="comment">-----------</span> |
| <a name="l00262"></a>00262 <span class="comment"> </span> |
| <a name="l00263"></a>00263 <span class="comment">(1 row)</span> |
| <a name="l00264"></a>00264 <span class="comment">\endverbatim</span> |
| <a name="l00265"></a>00265 <span class="comment"></span> |
| <a name="l00266"></a>00266 <span class="comment">@literature</span> |
| <a name="l00267"></a>00267 <span class="comment"></span> |
| <a name="l00268"></a>00268 <span class="comment">[1] http://en.wikipedia.org/wiki/C4.5_algorithm</span> |
| <a name="l00269"></a>00269 <span class="comment"></span> |
| <a name="l00270"></a>00270 <span class="comment">@sa File c45.sql_in documenting the SQL functions.</span> |
| <a name="l00271"></a>00271 <span class="comment">*/</span> |
| <a name="l00272"></a>00272 |
| <a name="l00273"></a>00273 /* |
| <a name="l00274"></a>00274 * This structure is used to store the result for the function of c45_train. |
| <a name="l00275"></a>00275 * |
| <a name="l00276"></a>00276 * training_set_size The number of rows in the training set. |
| <a name="l00277"></a>00277 * tree_nodes The number of total tree nodes. |
| <a name="l00278"></a>00278 * tree_depth The depth of the trained tree. |
| <a name="l00279"></a>00279 * training_time The time consumed during training the tree. |
| <a name="l00280"></a>00280 * split_criterion The split criterion used to train the tree. |
| <a name="l00281"></a>00281 * |
| <a name="l00282"></a>00282 */ |
| <a name="l00283"></a>00283 DROP TYPE IF EXISTS MADLIB_SCHEMA.c45_train_result CASCADE; |
| <a name="l00284"></a>00284 CREATE TYPE MADLIB_SCHEMA.c45_train_result AS |
| <a name="l00285"></a>00285 ( |
| <a name="l00286"></a>00286 training_set_size BIGINT, |
| <a name="l00287"></a>00287 tree_nodes BIGINT, |
| <a name="l00288"></a>00288 tree_depth INT, |
| <a name="l00289"></a>00289 training_time INTERVAL, |
| <a name="l00290"></a>00290 split_criterion TEXT |
| <a name="l00291"></a>00291 ); |
| <a name="l00292"></a>00292 |
| <a name="l00293"></a>00293 |
| <a name="l00294"></a>00294 /* |
| <a name="l00295"></a>00295 * This structure is used to store the result for the function of c45_classify. |
| <a name="l00296"></a>00296 * |
| <a name="l00297"></a>00297 * input_set_size The number of rows in the classification set. |
| <a name="l00298"></a>00298 * classification_time The time consumed during classifying the tree. |
| <a name="l00299"></a>00299 * |
| <a name="l00300"></a>00300 */ |
| <a name="l00301"></a>00301 DROP TYPE IF EXISTS MADLIB_SCHEMA.c45_classify_result CASCADE; |
| <a name="l00302"></a>00302 CREATE TYPE MADLIB_SCHEMA.c45_classify_result AS |
| <a name="l00303"></a>00303 ( |
| <a name="l00304"></a>00304 input_set_size BIGINT, |
| <a name="l00305"></a>00305 classification_time INTERVAL |
| <a name="l00306"></a>00306 ); |
| <a name="l00307"></a>00307 <span class="comment"></span> |
| <a name="l00308"></a>00308 <span class="comment">/**</span> |
| <a name="l00309"></a>00309 <span class="comment"> * @brief This is the long form API of training tree with all specified parameters.</span> |
| <a name="l00310"></a>00310 <span class="comment"> *</span> |
| <a name="l00311"></a>00311 <span class="comment"> * @param split_criterion The name of the split criterion that should be used </span> |
| <a name="l00312"></a>00312 <span class="comment"> * for tree construction. The valid values are</span> |
| <a name="l00313"></a>00313 <span class="comment"> * ‘infogain’, ‘gainratio’, and ‘gini’. It can't be NULL.</span> |
| <a name="l00314"></a>00314 <span class="comment"> * Information gain(infogain) and gini index(gini) are biased </span> |
| <a name="l00315"></a>00315 <span class="comment"> * toward multivalued attributes. Gain ratio(gainratio) adjusts </span> |
| <a name="l00316"></a>00316 <span class="comment"> * for this bias. However, it tends to prefer unbalanced splits </span> |
| <a name="l00317"></a>00317 <span class="comment"> * in which one partition is much smaller than the others.</span> |
| <a name="l00318"></a>00318 <span class="comment"> * @param training_table_name The name of the table/view with the source data.</span> |
| <a name="l00319"></a>00319 <span class="comment"> * @param result_tree_table_name The name of the table where the resulting DT </span> |
| <a name="l00320"></a>00320 <span class="comment"> * will be kept.</span> |
| <a name="l00321"></a>00321 <span class="comment"> * @param validation_table_name The name of the table/view that contains the validation </span> |
| <a name="l00322"></a>00322 <span class="comment"> * set used for tree pruning. The default is NULL, in which </span> |
| <a name="l00323"></a>00323 <span class="comment"> * case we will not do tree pruning. </span> |
| <a name="l00324"></a>00324 <span class="comment"> * @param continuous_feature_names A comma-separated list of the names of features whose values </span> |
| <a name="l00325"></a>00325 <span class="comment"> * are continuous. The default is null, which means there are </span> |
| <a name="l00326"></a>00326 <span class="comment"> * no continuous features in the training table.</span> |
| <a name="l00327"></a>00327 <span class="comment"> * @param feature_col_names A comma-separated list of the names of table columns, each of</span> |
| <a name="l00328"></a>00328 <span class="comment"> * which defines a feature. The default value is null, which means </span> |
| <a name="l00329"></a>00329 <span class="comment"> * all the columns in the training table, except columns named </span> |
| <a name="l00330"></a>00330 <span class="comment"> * ‘id’ and ‘class’, will be used as features.</span> |
| <a name="l00331"></a>00331 <span class="comment"> * @param id_col_name The name of the column containing an ID for each record.</span> |
| <a name="l00332"></a>00332 <span class="comment"> * @param class_col_name The name of the column containing the labeled class. </span> |
| <a name="l00333"></a>00333 <span class="comment"> * @param confidence_level A statistical confidence interval of the </span> |
| <a name="l00334"></a>00334 <span class="comment"> * resubstitution error.</span> |
| <a name="l00335"></a>00335 <span class="comment"> * @param how2handle_missing_value The way to handle missing value. The valid value </span> |
| <a name="l00336"></a>00336 <span class="comment"> * is 'explicit' or 'ignore'.</span> |
| <a name="l00337"></a>00337 <span class="comment"> * @param max_tree_depth Specifies the maximum number of levels in the result DT </span> |
| <a name="l00338"></a>00338 <span class="comment"> * to avoid overgrown DTs. </span> |
| <a name="l00339"></a>00339 <span class="comment"> * @param node_prune_threshold The minimum percentage of the number of records required in a</span> |
| <a name="l00340"></a>00340 <span class="comment"> * child node. It can't be NULL. The range of it is in [0.0, 1.0].</span> |
| <a name="l00341"></a>00341 <span class="comment"> * This threshold only applies to the non-root nodes. Therefore,</span> |
| <a name="l00342"></a>00342 <span class="comment"> * if its value is 1, then the trained tree only has one node (the root node);</span> |
| <a name="l00343"></a>00343 <span class="comment"> * if its value is 0, then no nodes will be pruned by this parameter.</span> |
| <a name="l00344"></a>00344 <span class="comment"> * @param node_split_threshold The minimum percentage of the number of records required in a</span> |
| <a name="l00345"></a>00345 <span class="comment"> * node in order for a further split to be possible.</span> |
| <a name="l00346"></a>00346 <span class="comment"> * It can't be NULL. The range of it is in [0.0, 1.0].</span> |
| <a name="l00347"></a>00347 <span class="comment"> * If it's value is 1, then the trained tree only has two levels, since</span> |
| <a name="l00348"></a>00348 <span class="comment"> * only the root node can grow; if its value is 0, then trees can grow</span> |
| <a name="l00349"></a>00349 <span class="comment"> * extensively.</span> |
| <a name="l00350"></a>00350 <span class="comment"> * @param verbosity > 0 means this function runs in verbose mode.</span> |
| <a name="l00351"></a>00351 <span class="comment"> *</span> |
| <a name="l00352"></a>00352 <span class="comment"> * @return An c45_train_result object.</span> |
| <a name="l00353"></a>00353 <span class="comment"> *</span> |
| <a name="l00354"></a>00354 <span class="comment"> */</span> |
| <a name="l00355"></a>00355 CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.c45_train |
| <a name="l00356"></a>00356 ( |
| <a name="l00357"></a>00357 split_criterion TEXT, |
| <a name="l00358"></a>00358 training_table_name TEXT, |
| <a name="l00359"></a>00359 result_tree_table_name TEXT, |
| <a name="l00360"></a>00360 validation_table_name TEXT, |
| <a name="l00361"></a>00361 continuous_feature_names TEXT, |
| <a name="l00362"></a>00362 feature_col_names TEXT, |
| <a name="l00363"></a>00363 id_col_name TEXT, |
| <a name="l00364"></a>00364 class_col_name TEXT, |
| <a name="l00365"></a><a class="code" href="c45_8sql__in.html#a4fbee855d22101d15d195d573189eb98">00365</a> confidence_level FLOAT, |
| <a name="l00366"></a>00366 how2handle_missing_value TEXT, |
| <a name="l00367"></a>00367 max_tree_depth INT, |
| <a name="l00368"></a>00368 node_prune_threshold FLOAT, |
| <a name="l00369"></a>00369 node_split_threshold FLOAT, |
| <a name="l00370"></a>00370 verbosity INT |
| <a name="l00371"></a>00371 ) |
| <a name="l00372"></a>00372 RETURNS MADLIB_SCHEMA.c45_train_result AS $$ |
| <a name="l00373"></a>00373 DECLARE |
| <a name="l00374"></a>00374 begin_func_exec TIMESTAMP; |
| <a name="l00375"></a>00375 tree_table_name TEXT; |
| <a name="l00376"></a>00376 ret MADLIB_SCHEMA.c45_train_result; |
| <a name="l00377"></a>00377 train_rs RECORD; |
| <a name="l00378"></a>00378 BEGIN |
| <a name="l00379"></a>00379 begin_func_exec = clock_timestamp(); |
| <a name="l00380"></a>00380 |
| <a name="l00381"></a>00381 IF (verbosity < 1) THEN |
| <a name="l00382"></a>00382 -- get rid of the messages whose severity level is lower than 'WARNING<span class="stringliteral">'</span> |
| <a name="l00383"></a>00383 <span class="stringliteral"> SET client_min_messages = WARNING;</span> |
| <a name="l00384"></a>00384 <span class="stringliteral"> END IF;</span> |
| <a name="l00385"></a>00385 <span class="stringliteral"></span> |
| <a name="l00386"></a>00386 <span class="stringliteral"> PERFORM MADLIB_SCHEMA.__assert</span> |
| <a name="l00387"></a>00387 <span class="stringliteral"> (</span> |
| <a name="l00388"></a>00388 <span class="stringliteral"> (confidence_level IS NOT NULL) AND</span> |
| <a name="l00389"></a>00389 <span class="stringliteral"> float8ge(confidence_level, 0.001) AND</span> |
| <a name="l00390"></a>00390 <span class="stringliteral"> float8le(confidence_level, 100),</span> |
| <a name="l00391"></a>00391 <span class="stringliteral"> '</span>confidence level value must be in range from 0.001 to 100<span class="stringliteral">'</span> |
| <a name="l00392"></a>00392 <span class="stringliteral"> );</span> |
| <a name="l00393"></a>00393 <span class="stringliteral"></span> |
| <a name="l00394"></a>00394 <span class="stringliteral"> PERFORM MADLIB_SCHEMA.__assert</span> |
| <a name="l00395"></a>00395 <span class="stringliteral"> (</span> |
| <a name="l00396"></a>00396 <span class="stringliteral"> validation_table_name IS NULL OR</span> |
| <a name="l00397"></a>00397 <span class="stringliteral"> MADLIB_SCHEMA.__table_exists</span> |
| <a name="l00398"></a>00398 <span class="stringliteral"> (</span> |
| <a name="l00399"></a>00399 <span class="stringliteral"> validation_table_name</span> |
| <a name="l00400"></a>00400 <span class="stringliteral"> ),</span> |
| <a name="l00401"></a>00401 <span class="stringliteral"> '</span>the specified validation table<span class="stringliteral">' ||</span> |
| <a name="l00402"></a>00402 <span class="stringliteral"> '</span><<span class="stringliteral">' ||</span> |
| <a name="l00403"></a>00403 <span class="stringliteral"> validation_table_name ||</span> |
| <a name="l00404"></a>00404 <span class="stringliteral"> '</span>> does not exist<span class="stringliteral">'</span> |
| <a name="l00405"></a>00405 <span class="stringliteral"> );</span> |
| <a name="l00406"></a>00406 <span class="stringliteral"></span> |
| <a name="l00407"></a>00407 <span class="stringliteral"> tree_table_name = btrim(lower(result_tree_table_name), '</span> <span class="stringliteral">');</span> |
| <a name="l00408"></a>00408 <span class="stringliteral"> PERFORM MADLIB_SCHEMA.__check_dt_common_params</span> |
| <a name="l00409"></a>00409 <span class="stringliteral"> (</span> |
| <a name="l00410"></a>00410 <span class="stringliteral"> split_criterion,</span> |
| <a name="l00411"></a>00411 <span class="stringliteral"> training_table_name, </span> |
| <a name="l00412"></a>00412 <span class="stringliteral"> tree_table_name,</span> |
| <a name="l00413"></a>00413 <span class="stringliteral"> continuous_feature_names, </span> |
| <a name="l00414"></a>00414 <span class="stringliteral"> feature_col_names, </span> |
| <a name="l00415"></a>00415 <span class="stringliteral"> id_col_name, </span> |
| <a name="l00416"></a>00416 <span class="stringliteral"> class_col_name, </span> |
| <a name="l00417"></a>00417 <span class="stringliteral"> how2handle_missing_value,</span> |
| <a name="l00418"></a>00418 <span class="stringliteral"> max_tree_depth,</span> |
| <a name="l00419"></a>00419 <span class="stringliteral"> node_prune_threshold,</span> |
| <a name="l00420"></a>00420 <span class="stringliteral"> node_split_threshold, </span> |
| <a name="l00421"></a>00421 <span class="stringliteral"> verbosity,</span> |
| <a name="l00422"></a>00422 <span class="stringliteral"> '</span>tree<span class="stringliteral">'</span> |
| <a name="l00423"></a>00423 <span class="stringliteral"> );</span> |
| <a name="l00424"></a>00424 <span class="stringliteral"> </span> |
| <a name="l00425"></a>00425 <span class="stringliteral"> train_rs = MADLIB_SCHEMA.__encode_and_train</span> |
| <a name="l00426"></a>00426 <span class="stringliteral"> (</span> |
| <a name="l00427"></a>00427 <span class="stringliteral"> '</span>C4.5<span class="stringliteral">',</span> |
| <a name="l00428"></a>00428 <span class="stringliteral"> split_criterion,</span> |
| <a name="l00429"></a>00429 <span class="stringliteral"> 1,</span> |
| <a name="l00430"></a>00430 <span class="stringliteral"> NULL,</span> |
| <a name="l00431"></a>00431 <span class="stringliteral"> training_table_name,</span> |
| <a name="l00432"></a>00432 <span class="stringliteral"> validation_table_name,</span> |
| <a name="l00433"></a>00433 <span class="stringliteral"> tree_table_name,</span> |
| <a name="l00434"></a>00434 <span class="stringliteral"> continuous_feature_names, </span> |
| <a name="l00435"></a>00435 <span class="stringliteral"> feature_col_names, </span> |
| <a name="l00436"></a>00436 <span class="stringliteral"> id_col_name, </span> |
| <a name="l00437"></a>00437 <span class="stringliteral"> class_col_name, </span> |
| <a name="l00438"></a>00438 <span class="stringliteral"> confidence_level,</span> |
| <a name="l00439"></a>00439 <span class="stringliteral"> how2handle_missing_value,</span> |
| <a name="l00440"></a>00440 <span class="stringliteral"> max_tree_depth,</span> |
| <a name="l00441"></a>00441 <span class="stringliteral"> 1.0,</span> |
| <a name="l00442"></a>00442 <span class="stringliteral"> '</span>f<span class="stringliteral">',</span> |
| <a name="l00443"></a>00443 <span class="stringliteral"> node_prune_threshold,</span> |
| <a name="l00444"></a>00444 <span class="stringliteral"> node_split_threshold, </span> |
| <a name="l00445"></a>00445 <span class="stringliteral"> '</span><tree_schema_name>_<tree_table_name><span class="stringliteral">',</span> |
| <a name="l00446"></a>00446 <span class="stringliteral"> verbosity</span> |
| <a name="l00447"></a>00447 <span class="stringliteral"> );</span> |
| <a name="l00448"></a>00448 <span class="stringliteral"></span> |
| <a name="l00449"></a>00449 <span class="stringliteral"> IF ( verbosity > 0 ) THEN</span> |
| <a name="l00450"></a>00450 <span class="stringliteral"> RAISE INFO '</span>Training Total Time: %<span class="stringliteral">', </span> |
| <a name="l00451"></a>00451 <span class="stringliteral"> clock_timestamp() - begin_func_exec;</span> |
| <a name="l00452"></a>00452 <span class="stringliteral"> RAISE INFO '</span>training result:%<span class="stringliteral">', train_rs;</span> |
| <a name="l00453"></a>00453 <span class="stringliteral"> END IF;</span> |
| <a name="l00454"></a>00454 <span class="stringliteral"> </span> |
| <a name="l00455"></a>00455 <span class="stringliteral"> ret.training_set_size = train_rs.num_of_samples; </span> |
| <a name="l00456"></a>00456 <span class="stringliteral"> ret.tree_nodes = train_rs.num_tree_nodes; </span> |
| <a name="l00457"></a>00457 <span class="stringliteral"> ret.tree_depth = train_rs.max_tree_depth;</span> |
| <a name="l00458"></a>00458 <span class="stringliteral"> ret.training_time = clock_timestamp() - begin_func_exec;</span> |
| <a name="l00459"></a>00459 <span class="stringliteral"> ret.split_criterion = split_criterion;</span> |
| <a name="l00460"></a>00460 <span class="stringliteral"> </span> |
| <a name="l00461"></a>00461 <span class="stringliteral"> RETURN ret;</span> |
| <a name="l00462"></a>00462 <span class="stringliteral">END</span> |
| <a name="l00463"></a>00463 <span class="stringliteral">$$ LANGUAGE PLPGSQL;</span> |
| <a name="l00464"></a>00464 <span class="stringliteral"></span> |
| <a name="l00465"></a>00465 <span class="stringliteral"></span><span class="comment"></span> |
| <a name="l00466"></a>00466 <span class="comment">/**</span> |
| <a name="l00467"></a>00467 <span class="comment"> * @brief C45 train algorithm in short form.</span> |
| <a name="l00468"></a>00468 <span class="comment"> *</span> |
| <a name="l00469"></a>00469 <span class="comment"> * @param split_criterion The name of the split criterion that should be used </span> |
| <a name="l00470"></a>00470 <span class="comment"> * for tree construction. Possible values are</span> |
| <a name="l00471"></a>00471 <span class="comment"> * ‘gain’, ‘gainratio’, and ‘gini’.</span> |
| <a name="l00472"></a>00472 <span class="comment"> * @param training_table_name The name of the table/view with the source data.</span> |
| <a name="l00473"></a>00473 <span class="comment"> * @param result_tree_table_name The name of the table where the resulting DT </span> |
| <a name="l00474"></a>00474 <span class="comment"> * will be kept.</span> |
| <a name="l00475"></a>00475 <span class="comment"> * @param validation_table_name The name of the table/view that contains the validation </span> |
| <a name="l00476"></a>00476 <span class="comment"> * set used for tree pruning. The default is NULL, in which </span> |
| <a name="l00477"></a>00477 <span class="comment"> * case we will not do tree pruning. </span> |
| <a name="l00478"></a>00478 <span class="comment"> * @param continuous_feature_names A comma-separated list of the names of features whose values </span> |
| <a name="l00479"></a>00479 <span class="comment"> * are continuous. The default is null, which means there are </span> |
| <a name="l00480"></a>00480 <span class="comment"> * no continuous features in the training table.</span> |
| <a name="l00481"></a>00481 <span class="comment"> * @param feature_col_names A comma-separated list of the names of table columns, each of</span> |
| <a name="l00482"></a>00482 <span class="comment"> * which defines a feature. The default value is null, which means </span> |
| <a name="l00483"></a>00483 <span class="comment"> * all the columns in the training table, except columns named </span> |
| <a name="l00484"></a>00484 <span class="comment"> * ‘id’ and ‘class’, will be used as features.</span> |
| <a name="l00485"></a>00485 <span class="comment"> * @param id_col_name The name of the column containing an ID for each record.</span> |
| <a name="l00486"></a>00486 <span class="comment"> * @param class_col_name The name of the column containing the labeled class. </span> |
| <a name="l00487"></a>00487 <span class="comment"> * @param confidence_level A statistical confidence interval of the </span> |
| <a name="l00488"></a>00488 <span class="comment"> * resubstitution error.</span> |
| <a name="l00489"></a>00489 <span class="comment"> * @param how2handle_missing_value The way to handle missing value. The valid value </span> |
| <a name="l00490"></a>00490 <span class="comment"> * is 'explicit' or 'ignore'.</span> |
| <a name="l00491"></a>00491 <span class="comment"> *</span> |
| <a name="l00492"></a>00492 <span class="comment"> * @return An c45_train_result object.</span> |
| <a name="l00493"></a>00493 <span class="comment"> *</span> |
| <a name="l00494"></a>00494 <span class="comment"> * @note </span> |
| <a name="l00495"></a>00495 <span class="comment"> * This calls the long form of C45 with the following default parameters:</span> |
| <a name="l00496"></a>00496 <span class="comment"> * - max_tree_deapth := 10</span> |
| <a name="l00497"></a>00497 <span class="comment"> * - node_prune_threshold := 0.001</span> |
| <a name="l00498"></a>00498 <span class="comment"> * - node_split_threshold := 0.01</span> |
| <a name="l00499"></a>00499 <span class="comment"> * - verbosity := 0</span> |
| <a name="l00500"></a>00500 <span class="comment"> *</span> |
| <a name="l00501"></a>00501 <span class="comment"> */</span> |
| <a name="l00502"></a>00502 CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.c45_train |
| <a name="l00503"></a>00503 ( |
| <a name="l00504"></a>00504 split_criterion TEXT, |
| <a name="l00505"></a>00505 training_table_name TEXT, |
| <a name="l00506"></a>00506 result_tree_table_name TEXT, |
| <a name="l00507"></a>00507 validation_table_name TEXT, |
| <a name="l00508"></a>00508 continuous_feature_names TEXT, |
| <a name="l00509"></a>00509 feature_col_names TEXT, |
| <a name="l00510"></a>00510 id_col_name TEXT, |
| <a name="l00511"></a>00511 class_col_name TEXT, |
| <a name="l00512"></a><a class="code" href="c45_8sql__in.html#a6c039416b94686b915e2a4c1133a5d44">00512</a> confidence_level FLOAT, |
| <a name="l00513"></a>00513 how2handle_missing_value TEXT |
| <a name="l00514"></a>00514 ) |
| <a name="l00515"></a>00515 RETURNS MADLIB_SCHEMA.c45_train_result AS $$ |
| <a name="l00516"></a>00516 DECLARE |
| <a name="l00517"></a>00517 ret MADLIB_SCHEMA.c45_train_result; |
| <a name="l00518"></a>00518 BEGIN |
| <a name="l00519"></a>00519 ret = MADLIB_SCHEMA.c45_train |
| <a name="l00520"></a>00520 ( |
| <a name="l00521"></a>00521 split_criterion, |
| <a name="l00522"></a>00522 training_table_name, |
| <a name="l00523"></a>00523 result_tree_table_name, |
| <a name="l00524"></a>00524 validation_table_name , |
| <a name="l00525"></a>00525 continuous_feature_names , |
| <a name="l00526"></a>00526 feature_col_names , |
| <a name="l00527"></a>00527 id_col_name , |
| <a name="l00528"></a>00528 class_col_name , |
| <a name="l00529"></a>00529 confidence_level, |
| <a name="l00530"></a>00530 how2handle_missing_value, |
| <a name="l00531"></a>00531 10, |
| <a name="l00532"></a>00532 0.001, |
| <a name="l00533"></a>00533 0.01, |
| <a name="l00534"></a>00534 0 |
| <a name="l00535"></a>00535 ); |
| <a name="l00536"></a>00536 |
| <a name="l00537"></a>00537 RETURN ret; |
| <a name="l00538"></a>00538 END |
| <a name="l00539"></a>00539 $$ LANGUAGE PLPGSQL; |
| <a name="l00540"></a>00540 |
| <a name="l00541"></a>00541 <span class="comment"></span> |
| <a name="l00542"></a>00542 <span class="comment">/**</span> |
| <a name="l00543"></a>00543 <span class="comment"> * @brief C45 train algorithm in short form.</span> |
| <a name="l00544"></a>00544 <span class="comment"> *</span> |
| <a name="l00545"></a>00545 <span class="comment"> * @param split_criterion The name of the split criterion that should be used </span> |
| <a name="l00546"></a>00546 <span class="comment"> * for tree construction. Possible values are</span> |
| <a name="l00547"></a>00547 <span class="comment"> * ‘gain’, ‘gainratio’, and ‘gini’.</span> |
| <a name="l00548"></a>00548 <span class="comment"> * @param training_table_name The name of the table/view with the source data.</span> |
| <a name="l00549"></a>00549 <span class="comment"> * @param result_tree_table_name The name of the table where the resulting DT </span> |
| <a name="l00550"></a>00550 <span class="comment"> * will be kept.</span> |
| <a name="l00551"></a>00551 <span class="comment"> *</span> |
| <a name="l00552"></a>00552 <span class="comment"> * @return An c45_train_result object.</span> |
| <a name="l00553"></a>00553 <span class="comment"> *</span> |
| <a name="l00554"></a>00554 <span class="comment"> * @note </span> |
| <a name="l00555"></a>00555 <span class="comment"> * This calls the above short form of C45 with the following default parameters:</span> |
| <a name="l00556"></a>00556 <span class="comment"> * - validation_table_name := NULL</span> |
| <a name="l00557"></a>00557 <span class="comment"> * - continuous_feature_names := NULL</span> |
| <a name="l00558"></a>00558 <span class="comment"> * - id_column_name := 'id'</span> |
| <a name="l00559"></a>00559 <span class="comment"> * - class_column_name := 'class'</span> |
| <a name="l00560"></a>00560 <span class="comment"> * - confidence_level := 25</span> |
| <a name="l00561"></a>00561 <span class="comment"> * - how2handle_missing_value := 'explicit'</span> |
| <a name="l00562"></a>00562 <span class="comment"> * - max_tree_deapth := 10</span> |
| <a name="l00563"></a>00563 <span class="comment"> * - node_prune_threshold := 0.001</span> |
| <a name="l00564"></a>00564 <span class="comment"> * - node_split_threshold := 0.01</span> |
| <a name="l00565"></a>00565 <span class="comment"> * - verbosity := 0</span> |
| <a name="l00566"></a>00566 <span class="comment"> *</span> |
| <a name="l00567"></a>00567 <span class="comment"> */</span> |
| <a name="l00568"></a>00568 CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.c45_train |
| <a name="l00569"></a>00569 ( |
| <a name="l00570"></a>00570 split_criterion TEXT, |
| <a name="l00571"></a>00571 training_table_name TEXT, |
| <a name="l00572"></a>00572 result_tree_table_name TEXT |
| <a name="l00573"></a>00573 ) |
| <a name="l00574"></a>00574 RETURNS MADLIB_SCHEMA.c45_train_result AS $$ |
| <a name="l00575"></a>00575 DECLARE |
| <a name="l00576"></a>00576 ret MADLIB_SCHEMA.c45_train_result; |
| <a name="l00577"></a>00577 BEGIN |
| <a name="l00578"></a><a class="code" href="c45_8sql__in.html#a18b30ff1a063e7cd16274bf7ab2a71dc">00578</a> ret = MADLIB_SCHEMA.c45_train |
| <a name="l00579"></a>00579 ( |
| <a name="l00580"></a>00580 split_criterion, |
| <a name="l00581"></a>00581 training_table_name, |
| <a name="l00582"></a>00582 result_tree_table_name, |
| <a name="l00583"></a>00583 null, |
| <a name="l00584"></a>00584 null, |
| <a name="l00585"></a>00585 null, |
| <a name="l00586"></a>00586 '<span class="keywordtype">id</span><span class="stringliteral">',</span> |
| <a name="l00587"></a>00587 <span class="stringliteral"> '</span><span class="keyword">class</span><span class="stringliteral">',</span> |
| <a name="l00588"></a>00588 <span class="stringliteral"> 25,</span> |
| <a name="l00589"></a>00589 <span class="stringliteral"> '</span><span class="keyword">explicit</span><span class="stringliteral">'</span> |
| <a name="l00590"></a>00590 <span class="stringliteral"> );</span> |
| <a name="l00591"></a>00591 <span class="stringliteral"> </span> |
| <a name="l00592"></a>00592 <span class="stringliteral"> RETURN ret;</span> |
| <a name="l00593"></a>00593 <span class="stringliteral">END</span> |
| <a name="l00594"></a>00594 <span class="stringliteral">$$ LANGUAGE PLPGSQL;</span> |
| <a name="l00595"></a>00595 <span class="stringliteral"></span> |
| <a name="l00596"></a>00596 <span class="stringliteral"></span><span class="comment"></span> |
| <a name="l00597"></a>00597 <span class="comment">/**</span> |
| <a name="l00598"></a>00598 <span class="comment"> * @brief Display the trained decision tree model with rules.</span> |
| <a name="l00599"></a>00599 <span class="comment"> *</span> |
| <a name="l00600"></a>00600 <span class="comment"> * @param tree_table_name The name of the table containing the tree's information.</span> |
| <a name="l00601"></a>00601 <span class="comment"> * @param verbosity If >= 1 will run in verbose mode.</span> |
| <a name="l00602"></a>00602 <span class="comment"> *</span> |
| <a name="l00603"></a>00603 <span class="comment"> * @return The rule representation text for a decision tree.</span> |
| <a name="l00604"></a>00604 <span class="comment"> *</span> |
| <a name="l00605"></a>00605 <span class="comment"> */</span> |
| <a name="l00606"></a>00606 CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.c45_genrule |
| <a name="l00607"></a>00607 ( |
| <a name="l00608"></a>00608 tree_table_name TEXT, |
| <a name="l00609"></a>00609 verbosity INT |
| <a name="l00610"></a>00610 ) |
| <a name="l00611"></a>00611 RETURNS SETOF TEXT AS $$ |
| <a name="l00612"></a>00612 DECLARE |
| <a name="l00613"></a>00613 metatable_name TEXT; |
| <a name="l00614"></a>00614 classtable_name TEXT; |
| <a name="l00615"></a>00615 class_column_name TEXT; |
| <a name="l00616"></a><a class="code" href="c45_8sql__in.html#ac71787c47795b3b0b133cdbd37438242">00616</a> rec RECORD; |
| <a name="l00617"></a>00617 fvalue_stmt TEXT; |
| <a name="l00618"></a>00618 feature_rule TEXT; |
| <a name="l00619"></a>00619 curstmt TEXT; |
| <a name="l00620"></a>00620 union_stmt TEXT := NULL; |
| <a name="l00621"></a>00621 exec_begin TIMESTAMP; |
| <a name="l00622"></a>00622 exec_leaves_rule INTERVAL; |
| <a name="l00623"></a>00623 exec_internode_rule INTERVAL; |
| <a name="l00624"></a>00624 exec_union INTERVAL; |
| <a name="l00625"></a>00625 BEGIN |
| <a name="l00626"></a>00626 |
| <a name="l00627"></a>00627 IF (verbosity < 1) THEN |
| <a name="l00628"></a>00628 -- get rid of the messages whose severity level is lower than 'WARNING<span class="stringliteral">'</span> |
| <a name="l00629"></a>00629 <span class="stringliteral"> SET client_min_messages = WARNING;</span> |
| <a name="l00630"></a>00630 <span class="stringliteral"> END IF;</span> |
| <a name="l00631"></a>00631 <span class="stringliteral"> </span> |
| <a name="l00632"></a>00632 <span class="stringliteral"> PERFORM MADLIB_SCHEMA.__assert</span> |
| <a name="l00633"></a>00633 <span class="stringliteral"> (</span> |
| <a name="l00634"></a>00634 <span class="stringliteral"> (tree_table_name IS NOT NULL) AND</span> |
| <a name="l00635"></a>00635 <span class="stringliteral"> (</span> |
| <a name="l00636"></a>00636 <span class="stringliteral"> MADLIB_SCHEMA.__table_exists</span> |
| <a name="l00637"></a>00637 <span class="stringliteral"> (</span> |
| <a name="l00638"></a>00638 <span class="stringliteral"> tree_table_name</span> |
| <a name="l00639"></a>00639 <span class="stringliteral"> )</span> |
| <a name="l00640"></a>00640 <span class="stringliteral"> ),</span> |
| <a name="l00641"></a>00641 <span class="stringliteral"> '</span>the specified tree table<span class="stringliteral">' || </span> |
| <a name="l00642"></a>00642 <span class="stringliteral"> coalesce('</span><<span class="stringliteral">' || </span> |
| <a name="l00643"></a>00643 <span class="stringliteral"> tree_table_name || </span> |
| <a name="l00644"></a>00644 <span class="stringliteral"> '</span>> does not exists<span class="stringliteral">', '</span> is NULL<span class="stringliteral">')</span> |
| <a name="l00645"></a>00645 <span class="stringliteral"> ); </span> |
| <a name="l00646"></a>00646 <span class="stringliteral"> </span> |
| <a name="l00647"></a>00647 <span class="stringliteral"> PERFORM MADLIB_SCHEMA.__assert</span> |
| <a name="l00648"></a>00648 <span class="stringliteral"> (</span> |
| <a name="l00649"></a>00649 <span class="stringliteral"> verbosity IS NOT NULL, </span> |
| <a name="l00650"></a>00650 <span class="stringliteral"> '</span>verbosity must be non-null<span class="stringliteral">'</span> |
| <a name="l00651"></a>00651 <span class="stringliteral"> ); </span> |
| <a name="l00652"></a>00652 <span class="stringliteral"> </span> |
| <a name="l00653"></a>00653 <span class="stringliteral"> IF (verbosity > 0 ) THEN </span> |
| <a name="l00654"></a>00654 <span class="stringliteral"> exec_begin = clock_timestamp();</span> |
| <a name="l00655"></a>00655 <span class="stringliteral"> exec_leaves_rule = exec_begin - exec_begin;</span> |
| <a name="l00656"></a>00656 <span class="stringliteral"> exec_union = exec_leaves_rule;</span> |
| <a name="l00657"></a>00657 <span class="stringliteral"> exec_internode_rule = exec_leaves_rule;</span> |
| <a name="l00658"></a>00658 <span class="stringliteral"> END IF;</span> |
| <a name="l00659"></a>00659 <span class="stringliteral"> </span> |
| <a name="l00660"></a>00660 <span class="stringliteral"> -- get metatable and classtable name given the tree table name</span> |
| <a name="l00661"></a>00661 <span class="stringliteral"> metatable_name = MADLIB_SCHEMA.__get_metatable_name(tree_table_name);</span> |
| <a name="l00662"></a>00662 <span class="stringliteral"> classtable_name = MADLIB_SCHEMA.__get_classtable_name(metatable_name);</span> |
| <a name="l00663"></a>00663 <span class="stringliteral"> class_column_name = MADLIB_SCHEMA.__get_class_column_name(metatable_name);</span> |
| <a name="l00664"></a>00664 <span class="stringliteral"> </span> |
| <a name="l00665"></a>00665 <span class="stringliteral"> curstmt = MADLIB_SCHEMA.__format</span> |
| <a name="l00666"></a>00666 <span class="stringliteral"> (</span> |
| <a name="l00667"></a>00667 <span class="stringliteral"> '</span>SELECT <span class="keywordtype">id</span>, maxclass, probability, |
| <a name="l00668"></a>00668 sample_size, lmc_nid, lmc_fval |
| <a name="l00669"></a>00669 FROM % |
| <a name="l00670"></a>00670 WHERE <span class="keywordtype">id</span> = 1<span class="stringliteral">',</span> |
| <a name="l00671"></a>00671 <span class="stringliteral"> ARRAY[</span> |
| <a name="l00672"></a>00672 <span class="stringliteral"> tree_table_name</span> |
| <a name="l00673"></a>00673 <span class="stringliteral"> ]</span> |
| <a name="l00674"></a>00674 <span class="stringliteral"> );</span> |
| <a name="l00675"></a>00675 <span class="stringliteral"> </span> |
| <a name="l00676"></a>00676 <span class="stringliteral"> EXECUTE curstmt INTO rec;</span> |
| <a name="l00677"></a>00677 <span class="stringliteral"> </span> |
| <a name="l00678"></a>00678 <span class="stringliteral"> -- in sample the root node is leaf</span> |
| <a name="l00679"></a>00679 <span class="stringliteral"> IF (rec.lmc_nid IS NULL) THEN</span> |
| <a name="l00680"></a>00680 <span class="stringliteral"> RETURN NEXT '</span>All instances will be classified to <span class="keyword">class</span> <span class="stringliteral">' || </span> |
| <a name="l00681"></a>00681 <span class="stringliteral"> MADLIB_SCHEMA.__get_class_value</span> |
| <a name="l00682"></a>00682 <span class="stringliteral"> (rec.maxclass, metatable_name) ||</span> |
| <a name="l00683"></a>00683 <span class="stringliteral"> '</span> [<span class="stringliteral">' || </span> |
| <a name="l00684"></a>00684 <span class="stringliteral"> (rec.probability * rec.sample_size)::BIGINT || </span> |
| <a name="l00685"></a>00685 <span class="stringliteral"> '</span>/<span class="stringliteral">' || </span> |
| <a name="l00686"></a>00686 <span class="stringliteral"> rec.sample_size || </span> |
| <a name="l00687"></a>00687 <span class="stringliteral"> '</span>]<span class="stringliteral">'; </span> |
| <a name="l00688"></a>00688 <span class="stringliteral"> RETURN; </span> |
| <a name="l00689"></a>00689 <span class="stringliteral"> END IF;</span> |
| <a name="l00690"></a>00690 <span class="stringliteral"> </span> |
| <a name="l00691"></a>00691 <span class="stringliteral"> -- get the meta info for features in the tree table (as best split)</span> |
| <a name="l00692"></a>00692 <span class="stringliteral"> curstmt = MADLIB_SCHEMA.__format</span> |
| <a name="l00693"></a>00693 <span class="stringliteral"> (</span> |
| <a name="l00694"></a>00694 <span class="stringliteral"> '</span>SELECT |
| <a name="l00695"></a>00695 <span class="keywordtype">id</span>, |
| <a name="l00696"></a>00696 column_name, |
| <a name="l00697"></a>00697 MADLIB_SCHEMA.__regclass_to_text |
| <a name="l00698"></a>00698 (table_oid) as table_name, |
| <a name="l00699"></a>00699 is_cont |
| <a name="l00700"></a>00700 FROM |
| <a name="l00701"></a>00701 % n1 |
| <a name="l00702"></a>00702 WHERE <span class="keywordtype">id</span> IN |
| <a name="l00703"></a>00703 (SELECT DISTINCT feature |
| <a name="l00704"></a>00704 FROM % |
| <a name="l00705"></a>00705 WHERE lmc_nid IS NOT NULL |
| <a name="l00706"></a>00706 )<span class="stringliteral">',</span> |
| <a name="l00707"></a>00707 <span class="stringliteral"> ARRAY[</span> |
| <a name="l00708"></a>00708 <span class="stringliteral"> metatable_name,</span> |
| <a name="l00709"></a>00709 <span class="stringliteral"> tree_table_name</span> |
| <a name="l00710"></a>00710 <span class="stringliteral"> ]</span> |
| <a name="l00711"></a>00711 <span class="stringliteral"> );</span> |
| <a name="l00712"></a>00712 <span class="stringliteral"> </span> |
| <a name="l00713"></a>00713 <span class="stringliteral"> -- put all the features'</span> value together <span class="keyword">using</span> <span class="stringliteral">'union all'</span> |
| <a name="l00714"></a>00714 FOR rec IN EXECUTE curstmt LOOP |
| <a name="l00715"></a>00715 -- continuous feature will produce two rows |
| <a name="l00716"></a>00716 IF (rec.is_cont) THEN |
| <a name="l00717"></a>00717 SELECT MADLIB_SCHEMA.__format |
| <a name="l00718"></a>00718 ( |
| <a name="l00719"></a>00719 'SELECT % as fid, 1 as key, |
| <a name="l00720"></a>00720 ''% <= ''::TEXT as fname, null::text as fval |
| <a name="l00721"></a>00721 UNION ALL |
| <a name="l00722"></a>00722 SELECT % as fid, 2 as key, ''% > ''::TEXT as fname, |
| <a name="l00723"></a>00723 null::text as fval', |
| <a name="l00724"></a>00724 ARRAY[ |
| <a name="l00725"></a>00725 rec.<span class="keywordtype">id</span>::TEXT, |
| <a name="l00726"></a>00726 rec.column_name, |
| <a name="l00727"></a>00727 rec.<span class="keywordtype">id</span>::TEXT, |
| <a name="l00728"></a>00728 rec.column_name |
| <a name="l00729"></a>00729 ] |
| <a name="l00730"></a>00730 ) INTO fvalue_stmt; |
| <a name="l00731"></a>00731 |
| <a name="l00732"></a>00732 -- discrete feature will produce the number of rows |
| <a name="l00733"></a>00733 -- which is the same with distinct values |
| <a name="l00734"></a>00734 ELSE |
| <a name="l00735"></a>00735 SELECT MADLIB_SCHEMA.__format |
| <a name="l00736"></a>00736 ( |
| <a name="l00737"></a>00737 'SELECT % as fid, key, ''% = ''::TEXT as fname, |
| <a name="l00738"></a>00738 MADLIB_SCHEMA.__to_char(%) as fval |
| <a name="l00739"></a>00739 FROM % |
| <a name="l00740"></a>00740 WHERE key IS NOT NULL', |
| <a name="l00741"></a>00741 ARRAY[ |
| <a name="l00742"></a>00742 rec.<span class="keywordtype">id</span>::TEXT, |
| <a name="l00743"></a>00743 rec.column_name, |
| <a name="l00744"></a>00744 rec.column_name, |
| <a name="l00745"></a>00745 rec.table_name |
| <a name="l00746"></a>00746 ] |
| <a name="l00747"></a>00747 ) |
| <a name="l00748"></a>00748 INTO fvalue_stmt; |
| <a name="l00749"></a>00749 END IF; |
| <a name="l00750"></a>00750 |
| <a name="l00751"></a>00751 IF (union_stmt IS NULL) THEN |
| <a name="l00752"></a>00752 union_stmt = fvalue_stmt; |
| <a name="l00753"></a>00753 ELSE |
| <a name="l00754"></a>00754 union_stmt = union_stmt || ' UNION ALL ' || fvalue_stmt; |
| <a name="l00755"></a>00755 END IF; |
| <a name="l00756"></a>00756 END LOOP; |
| <a name="l00757"></a>00757 |
| <a name="l00758"></a>00758 IF (verbosity > 0 ) THEN |
| <a name="l00759"></a>00759 exec_union = clock_timestamp() - exec_begin; |
| <a name="l00760"></a>00760 RAISE INFO 'compose feature values statement time:%', exec_union; |
| <a name="l00761"></a>00761 RAISE INFO 'feature info stmt: %', curstmt; |
| <a name="l00762"></a>00762 RAISE INFO 'feature value stmt: %', union_stmt; |
| <a name="l00763"></a>00763 END IF; |
| <a name="l00764"></a>00764 |
| <a name="l00765"></a>00765 -- put the rules for leaves into a temp table |
| <a name="l00766"></a>00766 DROP TABLE IF EXISTS c45_gen_rules_leaves; |
| <a name="l00767"></a>00767 SELECT MADLIB_SCHEMA.__format |
| <a name="l00768"></a>00768 ( |
| <a name="l00769"></a>00769 'CREATE TEMP TABLE c45_gen_rules_leaves as |
| <a name="l00770"></a>00770 SELECT |
| <a name="l00771"></a>00771 <span class="keywordtype">id</span>, |
| <a name="l00772"></a>00772 '' then class '' || |
| <a name="l00773"></a>00773 class::TEXT || |
| <a name="l00774"></a>00774 '' ['' || |
| <a name="l00775"></a>00775 (probability * sample_size)::BIGINT || |
| <a name="l00776"></a>00776 ''/'' || |
| <a name="l00777"></a>00777 sample_size || |
| <a name="l00778"></a>00778 '']'' |
| <a name="l00779"></a>00779 as str, |
| <a name="l00780"></a>00780 array_to_string(tree_location, '''') as location, |
| <a name="l00781"></a>00781 1 as rlid |
| <a name="l00782"></a>00782 FROM |
| <a name="l00783"></a>00783 (SELECT <span class="keywordtype">id</span>, maxclass, tree_location, probability, sample_size |
| <a name="l00784"></a>00784 FROM % |
| <a name="l00785"></a>00785 WHERE lmc_nid IS NULL |
| <a name="l00786"></a>00786 ) n1 |
| <a name="l00787"></a>00787 LEFT JOIN |
| <a name="l00788"></a>00788 (SELECT % as class, key |
| <a name="l00789"></a>00789 FROM % |
| <a name="l00790"></a>00790 WHERE key IS NOT NULL |
| <a name="l00791"></a>00791 ) n2 |
| <a name="l00792"></a>00792 ON n1.maxclass = n2.key |
| <a name="l00793"></a>00793 m4_ifdef(`__GREENPLUM__', `DISTRIBUTED BY (location)')', |
| <a name="l00794"></a>00794 ARRAY[ |
| <a name="l00795"></a>00795 tree_table_name, |
| <a name="l00796"></a>00796 class_column_name, |
| <a name="l00797"></a>00797 classtable_name |
| <a name="l00798"></a>00798 ] |
| <a name="l00799"></a>00799 ) |
| <a name="l00800"></a>00800 INTO curstmt; |
| <a name="l00801"></a>00801 |
| <a name="l00802"></a>00802 EXECUTE curstmt; |
| <a name="l00803"></a>00803 |
| <a name="l00804"></a>00804 IF (verbosity > 0 ) THEN |
| <a name="l00805"></a>00805 exec_leaves_rule = clock_timestamp() - exec_begin; |
| <a name="l00806"></a>00806 RAISE INFO 'create table for leaves'' rules time:%', |
| <a name="l00807"></a>00807 exec_leaves_rule - exec_union; |
| <a name="l00808"></a>00808 RAISE INFO 'create tablefor leaves stmt: %', curstmt; |
| <a name="l00809"></a>00809 END IF; |
| <a name="l00810"></a>00810 |
| <a name="l00811"></a>00811 DROP TABLE IF EXISTS c45_gen_rules_internode; |
| <a name="l00812"></a>00812 -- put rules of the internal nodes into a table |
| <a name="l00813"></a>00813 SELECT MADLIB_SCHEMA.__format |
| <a name="l00814"></a>00814 ( |
| <a name="l00815"></a>00815 'CREATE TEMP TABLE c45_gen_rules_internode AS |
| <a name="l00816"></a>00816 SELECT |
| <a name="l00817"></a>00817 lmc_nid + (key - lmc_fval) AS <span class="keywordtype">id</span>, |
| <a name="l00818"></a>00818 CASE WHEN (<span class="keywordtype">id</span> = 1) THEN |
| <a name="l00819"></a>00819 '' if '' || |
| <a name="l00820"></a>00820 fname || |
| <a name="l00821"></a>00821 COALESCE(split_value::TEXT, |
| <a name="l00822"></a>00822 MADLIB_SCHEMA.__to_char(fval), ''NULL'') |
| <a name="l00823"></a>00823 ELSE |
| <a name="l00824"></a>00824 '' '' || |
| <a name="l00825"></a>00825 fname || |
| <a name="l00826"></a>00826 COALESCE(split_value::TEXT, |
| <a name="l00827"></a>00827 MADLIB_SCHEMA.__to_char(fval), ''NULL'') |
| <a name="l00828"></a>00828 END AS str, |
| <a name="l00829"></a>00829 array_to_string(tree_location, '''') || key AS location, |
| <a name="l00830"></a>00830 0 AS rlid |
| <a name="l00831"></a>00831 FROM |
| <a name="l00832"></a>00832 (SELECT <span class="keywordtype">id</span>, feature, tree_location, |
| <a name="l00833"></a>00833 lmc_nid, lmc_fval, split_value |
| <a name="l00834"></a>00834 FROM % |
| <a name="l00835"></a>00835 WHERE lmc_nid IS NOT NULL |
| <a name="l00836"></a>00836 ) n1 |
| <a name="l00837"></a>00837 LEFT JOIN |
| <a name="l00838"></a>00838 (%) n2 |
| <a name="l00839"></a>00839 ON n1.feature = n2.fid |
| <a name="l00840"></a>00840 WHERE |
| <a name="l00841"></a>00841 (lmc_nid + key - lmc_fval) IN (SELECT <span class="keywordtype">id</span> from %) |
| <a name="l00842"></a>00842 m4_ifdef(`__GREENPLUM__', `DISTRIBUTED BY (location)')', |
| <a name="l00843"></a>00843 ARRAY[ |
| <a name="l00844"></a>00844 tree_table_name, |
| <a name="l00845"></a>00845 union_stmt, |
| <a name="l00846"></a>00846 tree_table_name |
| <a name="l00847"></a>00847 ] |
| <a name="l00848"></a>00848 ) INTO curstmt; |
| <a name="l00849"></a>00849 EXECUTE curstmt; |
| <a name="l00850"></a>00850 |
| <a name="l00851"></a>00851 IF (verbosity > 0 ) THEN |
| <a name="l00852"></a>00852 exec_internode_rule = clock_timestamp() - exec_begin; |
| <a name="l00853"></a>00853 RAISE INFO 'create table for internal nodes'' rules time:%', |
| <a name="l00854"></a>00854 exec_internode_rule - exec_leaves_rule; |
| <a name="l00855"></a>00855 RAISE INFO 'create tablefor internal nodes stmt: %', curstmt; |
| <a name="l00856"></a>00856 END IF; |
| <a name="l00857"></a>00857 |
| <a name="l00858"></a>00858 FOR rec IN EXECUTE ' |
| <a name="l00859"></a>00859 SELECT t1.<span class="keywordtype">id</span>, t1.rlid, t2.location, t1.str |
| <a name="l00860"></a>00860 FROM |
| <a name="l00861"></a>00861 c45_gen_rules_internode t1 |
| <a name="l00862"></a>00862 LEFT JOIN |
| <a name="l00863"></a>00863 c45_gen_rules_leaves t2 |
| <a name="l00864"></a>00864 ON position(t1.location in t2.location) = 1 |
| <a name="l00865"></a>00865 UNION ALL |
| <a name="l00866"></a>00866 SELECT <span class="keywordtype">id</span>, rlid, location, str |
| <a name="l00867"></a>00867 FROM c45_gen_rules_leaves n |
| <a name="l00868"></a>00868 ORDER BY location, rlid, <span class="keywordtype">id</span>' |
| <a name="l00869"></a>00869 LOOP |
| <a name="l00870"></a>00870 RETURN NEXT rec.str; |
| <a name="l00871"></a>00871 END LOOP; |
| <a name="l00872"></a>00872 |
| <a name="l00873"></a>00873 IF (verbosity > 0 ) THEN |
| <a name="l00874"></a>00874 RAISE INFO 'Total rules generation time:%', |
| <a name="l00875"></a>00875 clock_timestamp() - exec_begin; |
| <a name="l00876"></a>00876 END IF; |
| <a name="l00877"></a>00877 |
| <a name="l00878"></a>00878 RETURN; |
| <a name="l00879"></a>00879 END $$ LANGUAGE PLPGSQL; |
| <a name="l00880"></a>00880 |
| <a name="l00881"></a>00881 <span class="comment"></span> |
| <a name="l00882"></a>00882 <span class="comment">/**</span> |
| <a name="l00883"></a>00883 <span class="comment"> * @brief Display the trained decision tree model with rules.</span> |
| <a name="l00884"></a>00884 <span class="comment"> *</span> |
| <a name="l00885"></a>00885 <span class="comment"> * @param tree_table_name The name of the table containing the tree's information.</span> |
| <a name="l00886"></a>00886 <span class="comment"> *</span> |
| <a name="l00887"></a>00887 <span class="comment"> * @return The rule representation text for a decision tree.</span> |
| <a name="l00888"></a>00888 <span class="comment"> *</span> |
| <a name="l00889"></a>00889 <span class="comment"> */</span> |
| <a name="l00890"></a>00890 CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.<a class="code" href="c45_8sql__in.html#ac71787c47795b3b0b133cdbd37438242" title="Display the trained decision tree model with rules.">c45_genrule</a> |
| <a name="l00891"></a>00891 ( |
| <a name="l00892"></a>00892 tree_table_name TEXT |
| <a name="l00893"></a>00893 ) |
| <a name="l00894"></a>00894 RETURNS SETOF TEXT AS $$ |
| <a name="l00895"></a>00895 DECLARE |
| <a name="l00896"></a>00896 str TEXT; |
| <a name="l00897"></a>00897 BEGIN |
| <a name="l00898"></a>00898 -- run in non-verbose mode |
| <a name="l00899"></a>00899 FOR str IN EXECUTE |
| <a name="l00900"></a><a class="code" href="c45_8sql__in.html#acdba07d3897356a75666aa6d5999f490">00900</a> 'SELECT * |
| <a name="l00901"></a>00901 FROM MADLIB_SCHEMA.<a class="code" href="c45_8sql__in.html#ac71787c47795b3b0b133cdbd37438242" title="Display the trained decision tree model with rules.">c45_genrule</a> |
| <a name="l00902"></a>00902 (' || coalesce('''' || tree_table_name || '''', 'NULL') || ', 0)' |
| <a name="l00903"></a>00903 LOOP |
| <a name="l00904"></a>00904 RETURN NEXT str; |
| <a name="l00905"></a>00905 END LOOP; |
| <a name="l00906"></a>00906 |
| <a name="l00907"></a>00907 RETURN; |
| <a name="l00908"></a>00908 END |
| <a name="l00909"></a>00909 $$ LANGUAGE PLPGSQL; |
| <a name="l00910"></a>00910 |
| <a name="l00911"></a>00911 <span class="comment"></span> |
| <a name="l00912"></a>00912 <span class="comment">/**</span> |
| <a name="l00913"></a>00913 <span class="comment"> * @brief Display the trained decision tree model with human readable format.</span> |
| <a name="l00914"></a>00914 <span class="comment"> *</span> |
| <a name="l00915"></a>00915 <span class="comment"> * @param tree_table The name of the table containing the tree's information.</span> |
| <a name="l00916"></a>00916 <span class="comment"> * @param max_depth The max depth to be displayed. If null, this function </span> |
| <a name="l00917"></a>00917 <span class="comment"> * will show all levels.</span> |
| <a name="l00918"></a>00918 <span class="comment"> * </span> |
| <a name="l00919"></a>00919 <span class="comment"> * @return The text representing the tree with human readable format.</span> |
| <a name="l00920"></a>00920 <span class="comment"> *</span> |
| <a name="l00921"></a>00921 <span class="comment"> */</span> |
| <a name="l00922"></a>00922 CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.<a class="code" href="c45_8sql__in.html#a32d2bcbc016c990991d77b6f6148306d" title="Display the trained decision tree model with human readable format.">c45_display</a> |
| <a name="l00923"></a>00923 ( |
| <a name="l00924"></a>00924 tree_table TEXT, |
| <a name="l00925"></a>00925 max_depth INT |
| <a name="l00926"></a>00926 ) |
| <a name="l00927"></a>00927 RETURNS SETOF TEXT AS $$ |
| <a name="l00928"></a>00928 DECLARE |
| <a name="l00929"></a>00929 tids INT[] := ARRAY[1]; |
| <a name="l00930"></a>00930 str TEXT; |
| <a name="l00931"></a>00931 BEGIN |
| <a name="l00932"></a><a class="code" href="c45_8sql__in.html#a32d2bcbc016c990991d77b6f6148306d">00932</a> -- get rid of the messages whose severity level is lower than 'WARNING' |
| <a name="l00933"></a>00933 SET client_min_messages = WARNING; |
| <a name="l00934"></a>00934 |
| <a name="l00935"></a>00935 PERFORM MADLIB_SCHEMA.__assert |
| <a name="l00936"></a>00936 ( |
| <a name="l00937"></a>00937 (tree_table IS NOT NULL) AND |
| <a name="l00938"></a>00938 ( |
| <a name="l00939"></a>00939 MADLIB_SCHEMA.__table_exists |
| <a name="l00940"></a>00940 ( |
| <a name="l00941"></a>00941 tree_table |
| <a name="l00942"></a>00942 ) |
| <a name="l00943"></a>00943 ), |
| <a name="l00944"></a>00944 'the specified tree table' || |
| <a name="l00945"></a>00945 coalesce('<' || |
| <a name="l00946"></a>00946 tree_table || |
| <a name="l00947"></a>00947 '> does not exists', ' is NULL') |
| <a name="l00948"></a>00948 ); |
| <a name="l00949"></a>00949 |
| <a name="l00950"></a>00950 FOR str IN SELECT * FROM |
| <a name="l00951"></a>00951 m4_changequote(`>>>', `<<<') |
| <a name="l00952"></a>00952 m4_ifdef(>>>__HAS_ORDERED_AGGREGATES__<<<, >>> |
| <a name="l00953"></a>00953 MADLIB_SCHEMA.__treemodel_display_with_ordered_aggr |
| <a name="l00954"></a>00954 (tree_table,tids,max_depth) LOOP |
| <a name="l00955"></a>00955 <<<, >>> |
| <a name="l00956"></a>00956 MADLIB_SCHEMA.__treemodel_display_no_ordered_aggr |
| <a name="l00957"></a>00957 (tree_table,tids,max_depth) LOOP |
| <a name="l00958"></a>00958 <<<) |
| <a name="l00959"></a>00959 m4_changequote(>>>`<<<, >>>'<<<) |
| <a name="l00960"></a>00960 RETURN NEXT str; |
| <a name="l00961"></a>00961 END LOOP; |
| <a name="l00962"></a>00962 RETURN; |
| <a name="l00963"></a>00963 END $$ LANGUAGE PLPGSQL; |
| <a name="l00964"></a>00964 |
| <a name="l00965"></a>00965 <span class="comment"></span> |
| <a name="l00966"></a>00966 <span class="comment">/**</span> |
| <a name="l00967"></a>00967 <span class="comment"> * @brief Display the whole trained decision tree model with human readable format.</span> |
| <a name="l00968"></a>00968 <span class="comment"> *</span> |
| <a name="l00969"></a>00969 <span class="comment"> * @param tree_table: The name of the table containing the tree's information.</span> |
| <a name="l00970"></a>00970 <span class="comment"> * </span> |
| <a name="l00971"></a>00971 <span class="comment"> * @return The text representing the tree with human readable format.</span> |
| <a name="l00972"></a>00972 <span class="comment"> *</span> |
| <a name="l00973"></a>00973 <span class="comment"> */</span> |
| <a name="l00974"></a>00974 CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.<a class="code" href="c45_8sql__in.html#a32d2bcbc016c990991d77b6f6148306d" title="Display the trained decision tree model with human readable format.">c45_display</a> |
| <a name="l00975"></a>00975 ( |
| <a name="l00976"></a>00976 tree_table TEXT |
| <a name="l00977"></a>00977 ) |
| <a name="l00978"></a>00978 RETURNS SETOF TEXT AS $$ |
| <a name="l00979"></a>00979 DECLARE |
| <a name="l00980"></a>00980 str TEXT; |
| <a name="l00981"></a><a class="code" href="c45_8sql__in.html#ad7f190eb8e5d53f4772fac699787c0fe">00981</a> BEGIN |
| <a name="l00982"></a>00982 FOR str IN SELECT * FROM MADLIB_SCHEMA.<a class="code" href="c45_8sql__in.html#a32d2bcbc016c990991d77b6f6148306d" title="Display the trained decision tree model with human readable format.">c45_display</a>(tree_table,NULL) LOOP |
| <a name="l00983"></a>00983 RETURN NEXT str; |
| <a name="l00984"></a>00984 END LOOP; |
| <a name="l00985"></a>00985 RETURN; |
| <a name="l00986"></a>00986 END $$ LANGUAGE PLPGSQL; |
| <a name="l00987"></a>00987 |
| <a name="l00988"></a>00988 <span class="comment"></span> |
| <a name="l00989"></a>00989 <span class="comment">/**</span> |
| <a name="l00990"></a>00990 <span class="comment"> * @brief Classify dataset using trained decision tree model.</span> |
| <a name="l00991"></a>00991 <span class="comment"> * The classification result will be stored in the table which is defined </span> |
| <a name="l00992"></a>00992 <span class="comment"> * as: </span> |
| <a name="l00993"></a>00993 <span class="comment"> .</span> |
| <a name="l00994"></a>00994 <span class="comment"> * CREATE TABLE classification_result</span> |
| <a name="l00995"></a>00995 <span class="comment"> * (</span> |
| <a name="l00996"></a>00996 <span class="comment"> * id INT|BIGINT,</span> |
| <a name="l00997"></a>00997 <span class="comment"> * class SUPPORTED_DATA_TYPE,</span> |
| <a name="l00998"></a>00998 <span class="comment"> * prob FLOAT</span> |
| <a name="l00999"></a>00999 <span class="comment"> * ); </span> |
| <a name="l01000"></a>01000 <span class="comment"> *</span> |
| <a name="l01001"></a>01001 <span class="comment"> * @param tree_table_name The name of trained tree.</span> |
| <a name="l01002"></a>01002 <span class="comment"> * @param classification_table_name The name of the table/view with the source data.</span> |
| <a name="l01003"></a>01003 <span class="comment"> * @param result_table_name The name of result table.</span> |
| <a name="l01004"></a>01004 <span class="comment"> * @param verbosity > 0 means this function runs in verbose mode.</span> |
| <a name="l01005"></a>01005 <span class="comment"> *</span> |
| <a name="l01006"></a>01006 <span class="comment"> * @return A c45_classify_result object.</span> |
| <a name="l01007"></a>01007 <span class="comment"> *</span> |
| <a name="l01008"></a>01008 <span class="comment"> */</span> |
| <a name="l01009"></a>01009 CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.<a class="code" href="c45_8sql__in.html#afe136e52f498f2ff9e2b91e38e29d670" title="Classify dataset using trained decision tree model. The classification result will be stored in the t...">c45_classify</a> |
| <a name="l01010"></a>01010 ( |
| <a name="l01011"></a>01011 tree_table_name TEXT, |
| <a name="l01012"></a>01012 classification_table_name TEXT, |
| <a name="l01013"></a>01013 result_table_name TEXT, |
| <a name="l01014"></a>01014 verbosity INT |
| <a name="l01015"></a>01015 ) |
| <a name="l01016"></a><a class="code" href="c45_8sql__in.html#afe136e52f498f2ff9e2b91e38e29d670">01016</a> RETURNS MADLIB_SCHEMA.c45_classify_result AS $$ |
| <a name="l01017"></a>01017 DECLARE |
| <a name="l01018"></a>01018 encoded_table_name TEXT := ''; |
| <a name="l01019"></a>01019 begin_time TIMESTAMP; |
| <a name="l01020"></a>01020 ret MADLIB_SCHEMA.c45_classify_result; |
| <a name="l01021"></a>01021 temp_result_table TEXT := ''; |
| <a name="l01022"></a>01022 metatable_name TEXT; |
| <a name="l01023"></a>01023 result_rec RECORD; |
| <a name="l01024"></a>01024 curstmt TEXT; |
| <a name="l01025"></a>01025 table_names TEXT[]; |
| <a name="l01026"></a>01026 BEGIN |
| <a name="l01027"></a>01027 IF (verbosity < 1) THEN |
| <a name="l01028"></a>01028 -- get rid of the messages whose severity level is lower than 'WARNING' |
| <a name="l01029"></a>01029 SET client_min_messages = WARNING; |
| <a name="l01030"></a>01030 END IF; |
| <a name="l01031"></a>01031 |
| <a name="l01032"></a>01032 begin_time = clock_timestamp(); |
| <a name="l01033"></a>01033 |
| <a name="l01034"></a>01034 PERFORM MADLIB_SCHEMA.__assert |
| <a name="l01035"></a>01035 ( |
| <a name="l01036"></a>01036 (result_table_name IS NOT NULL) AND |
| <a name="l01037"></a>01037 ( |
| <a name="l01038"></a>01038 NOT MADLIB_SCHEMA.__table_exists |
| <a name="l01039"></a>01039 ( |
| <a name="l01040"></a>01040 result_table_name |
| <a name="l01041"></a>01041 ) |
| <a name="l01042"></a>01042 ), |
| <a name="l01043"></a>01043 'the specified result table' || coalesce('<' || result_table_name || '> exists', ' is NULL') |
| <a name="l01044"></a>01044 ); |
| <a name="l01045"></a>01045 |
| <a name="l01046"></a>01046 table_names = MADLIB_SCHEMA.__treemodel_classify_internal |
| <a name="l01047"></a>01047 ( |
| <a name="l01048"></a>01048 classification_table_name, |
| <a name="l01049"></a>01049 tree_table_name, |
| <a name="l01050"></a>01050 verbosity |
| <a name="l01051"></a>01051 ); |
| <a name="l01052"></a>01052 |
| <a name="l01053"></a>01053 encoded_table_name = table_names[1]; |
| <a name="l01054"></a>01054 temp_result_table = table_names[2]; |
| <a name="l01055"></a>01055 |
| <a name="l01056"></a>01056 EXECUTE 'DELETE FROM '||temp_result_table||' WHERE tid <> 1;'; |
| <a name="l01057"></a>01057 metatable_name = MADLIB_SCHEMA.__get_metatable_name( tree_table_name ); |
| <a name="l01058"></a>01058 |
| <a name="l01059"></a>01059 curstmt = MADLIB_SCHEMA.__format |
| <a name="l01060"></a>01060 ( |
| <a name="l01061"></a>01061 'SELECT |
| <a name="l01062"></a>01062 column_name, |
| <a name="l01063"></a>01063 MADLIB_SCHEMA.__regclass_to_text |
| <a name="l01064"></a>01064 (table_oid) as table_name |
| <a name="l01065"></a>01065 FROM % |
| <a name="l01066"></a>01066 WHERE column_type=''c'' LIMIT 1', |
| <a name="l01067"></a>01067 ARRAY[ |
| <a name="l01068"></a>01068 metatable_name |
| <a name="l01069"></a>01069 ] |
| <a name="l01070"></a>01070 ); |
| <a name="l01071"></a>01071 |
| <a name="l01072"></a>01072 EXECUTE curstmt INTO result_rec; |
| <a name="l01073"></a>01073 |
| <a name="l01074"></a>01074 -- translate the encoded class information back |
| <a name="l01075"></a>01075 curstmt = MADLIB_SCHEMA.__format |
| <a name="l01076"></a>01076 ( |
| <a name="l01077"></a>01077 'CREATE TABLE % AS SELECT n.<span class="keywordtype">id</span>, m.fval as class, n.prob |
| <a name="l01078"></a>01078 From % n, % m |
| <a name="l01079"></a>01079 WHERE n.class = m.code |
| <a name="l01080"></a>01080 m4_ifdef(`__GREENPLUM__', `DISTRIBUTED BY (<span class="keywordtype">id</span>)');', |
| <a name="l01081"></a>01081 ARRAY[ |
| <a name="l01082"></a>01082 result_table_name, |
| <a name="l01083"></a>01083 temp_result_table, |
| <a name="l01084"></a>01084 result_rec.table_name |
| <a name="l01085"></a>01085 ] |
| <a name="l01086"></a>01086 ); |
| <a name="l01087"></a>01087 EXECUTE curstmt; |
| <a name="l01088"></a>01088 |
| <a name="l01089"></a>01089 EXECUTE 'DROP TABLE IF EXISTS ' || encoded_table_name || ';'; |
| <a name="l01090"></a>01090 EXECUTE 'DROP TABLE IF EXISTS ' || temp_result_table || ';'; |
| <a name="l01091"></a>01091 EXECUTE 'SELECT COUNT(*) FROM ' ||classification_table_name||';' |
| <a name="l01092"></a>01092 INTO ret.input_set_size; |
| <a name="l01093"></a>01093 |
| <a name="l01094"></a>01094 ret.classification_time = clock_timestamp() - begin_time; |
| <a name="l01095"></a>01095 |
| <a name="l01096"></a>01096 RETURN ret; |
| <a name="l01097"></a>01097 END |
| <a name="l01098"></a>01098 $$ LANGUAGE PLPGSQL; |
| <a name="l01099"></a>01099 |
| <a name="l01100"></a>01100 <span class="comment"></span> |
| <a name="l01101"></a>01101 <span class="comment">/**</span> |
| <a name="l01102"></a>01102 <span class="comment"> * @brief Classify dataset using trained decision tree model. It runs in quiet </span> |
| <a name="l01103"></a>01103 <span class="comment"> * mode. The classification result will be stored in the table which is </span> |
| <a name="l01104"></a>01104 <span class="comment"> * defined as: </span> |
| <a name="l01105"></a>01105 <span class="comment"> *</span> |
| <a name="l01106"></a>01106 <span class="comment"> * CREATE TABLE classification_result</span> |
| <a name="l01107"></a>01107 <span class="comment"> * (</span> |
| <a name="l01108"></a>01108 <span class="comment"> * id INT|BIGINT,</span> |
| <a name="l01109"></a>01109 <span class="comment"> * class SUPPORTED_DATA_TYPE,</span> |
| <a name="l01110"></a>01110 <span class="comment"> * prob FLOAT</span> |
| <a name="l01111"></a>01111 <span class="comment"> * ); </span> |
| <a name="l01112"></a>01112 <span class="comment"> *</span> |
| <a name="l01113"></a>01113 <span class="comment"> * @param tree_table_name The name of trained tree.</span> |
| <a name="l01114"></a>01114 <span class="comment"> * @param classification_table_name The name of the table/view with the source data.</span> |
| <a name="l01115"></a>01115 <span class="comment"> * @param result_table_name The name of result table.</span> |
| <a name="l01116"></a>01116 <span class="comment"> *</span> |
| <a name="l01117"></a>01117 <span class="comment"> * @return A c45_classify_result object.</span> |
| <a name="l01118"></a>01118 <span class="comment"> *</span> |
| <a name="l01119"></a>01119 <span class="comment"> */</span> |
| <a name="l01120"></a>01120 CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.<a class="code" href="c45_8sql__in.html#afe136e52f498f2ff9e2b91e38e29d670" title="Classify dataset using trained decision tree model. The classification result will be stored in the t...">c45_classify</a> |
| <a name="l01121"></a>01121 ( |
| <a name="l01122"></a>01122 tree_table_name TEXT, |
| <a name="l01123"></a>01123 classification_table_name TEXT, |
| <a name="l01124"></a>01124 result_table_name TEXT |
| <a name="l01125"></a>01125 ) |
| <a name="l01126"></a>01126 RETURNS MADLIB_SCHEMA.c45_classify_result AS $$ |
| <a name="l01127"></a><a class="code" href="c45_8sql__in.html#af5eb174eeecd11233409657221586cf1">01127</a> DECLARE |
| <a name="l01128"></a>01128 ret MADLIB_SCHEMA.c45_classify_result; |
| <a name="l01129"></a>01129 BEGIN |
| <a name="l01130"></a>01130 -- get rid of the messages whose severity level is lower than 'WARNING' |
| <a name="l01131"></a>01131 SET client_min_messages = WARNING; |
| <a name="l01132"></a>01132 |
| <a name="l01133"></a>01133 ret = MADLIB_SCHEMA.<a class="code" href="c45_8sql__in.html#afe136e52f498f2ff9e2b91e38e29d670" title="Classify dataset using trained decision tree model. The classification result will be stored in the t...">c45_classify</a> |
| <a name="l01134"></a>01134 ( |
| <a name="l01135"></a>01135 tree_table_name, |
| <a name="l01136"></a>01136 classification_table_name, |
| <a name="l01137"></a>01137 result_table_name, |
| <a name="l01138"></a>01138 0 |
| <a name="l01139"></a>01139 ); |
| <a name="l01140"></a>01140 |
| <a name="l01141"></a>01141 RETURN ret; |
| <a name="l01142"></a>01142 END $$ LANGUAGE PLPGSQL; |
| <a name="l01143"></a>01143 |
| <a name="l01144"></a>01144 <span class="comment"></span> |
| <a name="l01145"></a>01145 <span class="comment">/**</span> |
| <a name="l01146"></a>01146 <span class="comment"> * @brief Check the accuracy of the decision tree model.</span> |
| <a name="l01147"></a>01147 <span class="comment"> * </span> |
| <a name="l01148"></a>01148 <span class="comment"> * @param tree_table_name The name of the trained tree.</span> |
| <a name="l01149"></a>01149 <span class="comment"> * @param scoring_table_name The name of the table/view with the source data.</span> |
| <a name="l01150"></a>01150 <span class="comment"> * @param verbosity > 0 means this function runs in verbose mode.</span> |
| <a name="l01151"></a>01151 <span class="comment"> *</span> |
| <a name="l01152"></a>01152 <span class="comment"> * @return The estimated accuracy information.</span> |
| <a name="l01153"></a>01153 <span class="comment"> *</span> |
| <a name="l01154"></a>01154 <span class="comment"> */</span> |
| <a name="l01155"></a>01155 CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.<a class="code" href="c45_8sql__in.html#a1b634db47e9006d114da0987e80b9601" title="Check the accuracy of the decision tree model.">c45_score</a> |
| <a name="l01156"></a>01156 ( |
| <a name="l01157"></a>01157 tree_table_name TEXT, |
| <a name="l01158"></a>01158 scoring_table_name TEXT, |
| <a name="l01159"></a>01159 verbosity INT |
| <a name="l01160"></a>01160 ) |
| <a name="l01161"></a>01161 RETURNS FLOAT8 AS $$ |
| <a name="l01162"></a><a class="code" href="c45_8sql__in.html#a1b634db47e9006d114da0987e80b9601">01162</a> DECLARE |
| <a name="l01163"></a>01163 accuracy FLOAT8; |
| <a name="l01164"></a>01164 BEGIN |
| <a name="l01165"></a>01165 accuracy = MADLIB_SCHEMA.__treemodel_score |
| <a name="l01166"></a>01166 ( |
| <a name="l01167"></a>01167 tree_table_name, |
| <a name="l01168"></a>01168 scoring_table_name, |
| <a name="l01169"></a>01169 verbosity |
| <a name="l01170"></a>01170 ); |
| <a name="l01171"></a>01171 RETURN accuracy; |
| <a name="l01172"></a>01172 END; |
| <a name="l01173"></a>01173 $$ LANGUAGE PLPGSQL; |
| <a name="l01174"></a>01174 |
| <a name="l01175"></a>01175 <span class="comment"></span> |
| <a name="l01176"></a>01176 <span class="comment">/**</span> |
| <a name="l01177"></a>01177 <span class="comment"> * @brief Check the accuracy of the decision tree model.</span> |
| <a name="l01178"></a>01178 <span class="comment"> * </span> |
| <a name="l01179"></a>01179 <span class="comment"> * @param tree_table_name The name of the trained tree.</span> |
| <a name="l01180"></a>01180 <span class="comment"> * @param scoring_table_name The name of the table/view with the source data.</span> |
| <a name="l01181"></a>01181 <span class="comment"> *</span> |
| <a name="l01182"></a>01182 <span class="comment"> * @return The estimated accuracy information.</span> |
| <a name="l01183"></a>01183 <span class="comment"> *</span> |
| <a name="l01184"></a>01184 <span class="comment"> */</span> |
| <a name="l01185"></a>01185 CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.<a class="code" href="c45_8sql__in.html#a1b634db47e9006d114da0987e80b9601" title="Check the accuracy of the decision tree model.">c45_score</a> |
| <a name="l01186"></a>01186 ( |
| <a name="l01187"></a>01187 tree_table_name TEXT, |
| <a name="l01188"></a>01188 scoring_table_name TEXT |
| <a name="l01189"></a>01189 ) |
| <a name="l01190"></a>01190 RETURNS FLOAT8 AS $$ |
| <a name="l01191"></a>01191 DECLARE |
| <a name="l01192"></a><a class="code" href="c45_8sql__in.html#af0739749507c1097003dcf529d29fee2">01192</a> accuracy FLOAT8; |
| <a name="l01193"></a>01193 BEGIN |
| <a name="l01194"></a>01194 accuracy = MADLIB_SCHEMA.__treemodel_score |
| <a name="l01195"></a>01195 ( |
| <a name="l01196"></a>01196 tree_table_name, |
| <a name="l01197"></a>01197 scoring_table_name, |
| <a name="l01198"></a>01198 0 |
| <a name="l01199"></a>01199 ); |
| <a name="l01200"></a>01200 RETURN accuracy; |
| <a name="l01201"></a>01201 END; |
| <a name="l01202"></a>01202 $$ LANGUAGE PLPGSQL; |
| <a name="l01203"></a>01203 |
| <a name="l01204"></a>01204 <span class="comment"></span> |
| <a name="l01205"></a>01205 <span class="comment">/**</span> |
| <a name="l01206"></a>01206 <span class="comment"> * @brief Cleanup the trained tree table and any relevant tables.</span> |
| <a name="l01207"></a>01207 <span class="comment"> *</span> |
| <a name="l01208"></a>01208 <span class="comment"> * @param result_tree_table_name The name of the table containing</span> |
| <a name="l01209"></a>01209 <span class="comment"> * the tree's information.</span> |
| <a name="l01210"></a>01210 <span class="comment"> *</span> |
| <a name="l01211"></a>01211 <span class="comment"> * @return The status of that cleanup operation.</span> |
| <a name="l01212"></a>01212 <span class="comment"> *</span> |
| <a name="l01213"></a>01213 <span class="comment"> */</span> |
| <a name="l01214"></a>01214 CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.<a class="code" href="c45_8sql__in.html#ac25e17ecbc70149aa559018e718fc793" title="Cleanup the trained tree table and any relevant tables.">c45_clean</a> |
| <a name="l01215"></a>01215 ( |
| <a name="l01216"></a>01216 result_tree_table_name TEXT |
| <a name="l01217"></a>01217 ) |
| <a name="l01218"></a>01218 RETURNS BOOLEAN AS $$ |
| <a name="l01219"></a>01219 DECLARE |
| <a name="l01220"></a>01220 result BOOLEAN; |
| <a name="l01221"></a><a class="code" href="c45_8sql__in.html#ac25e17ecbc70149aa559018e718fc793">01221</a> BEGIN |
| <a name="l01222"></a>01222 result=MADLIB_SCHEMA.__treemodel_clean(result_tree_table_name); |
| <a name="l01223"></a>01223 RETURN result; |
| <a name="l01224"></a>01224 END |
| <a name="l01225"></a>01225 $$ LANGUAGE PLPGSQL; |
| </pre></div></div> |
| </div> |
| <div id="nav-path" class="navpath"> |
| <ul> |
| <li class="navelem"><a class="el" href="c45_8sql__in.html">c45.sql_in</a> </li> |
| <!-- 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></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> |
| |
| |
| <li class="footer">Generated on Tue Apr 2 2013 14:57:03 for MADlib by |
| <a href="http://www.doxygen.org/index.html"> |
| <img class="footer" src="doxygen.png" alt="doxygen"/></a> 1.7.5.1 </li> |
| </ul> |
| </div> |
| |
| |
| </body> |
| </html> |
