http://git-wip-us.apache.org/repos/asf/incubator-madlib-site/blob/bed9253d/docs/latest/group__grp__pivot.html ---------------------------------------------------------------------- diff --git a/docs/latest/group__grp__pivot.html b/docs/latest/group__grp__pivot.html new file mode 100644 index 0000000..832672e --- /dev/null +++ b/docs/latest/group__grp__pivot.html @@ -0,0 +1,731 @@ +<!-- HTML header for doxygen 1.8.4--> +<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd";> +<html xmlns="http://www.w3.org/1999/xhtml";> +<head> +<meta http-equiv="Content-Type" content="text/xhtml;charset=UTF-8"/> +<meta http-equiv="X-UA-Compatible" content="IE=9"/> +<meta name="generator" content="Doxygen 1.8.10"/> +<meta name="keywords" content="madlib,postgres,greenplum,machine learning,data mining,deep learning,ensemble methods,data science,market basket analysis,affinity analysis,pca,lda,regression,elastic net,huber white,proportional hazards,k-means,latent dirichlet allocation,bayes,support vector machines,svm"/> +<title>MADlib: Pivot</title> +<link href="tabs.css" rel="stylesheet" type="text/css"/> +<script type="text/javascript" src="jquery.js"></script> +<script type="text/javascript" src="dynsections.js"></script> +<link href="navtree.css" rel="stylesheet" type="text/css"/> +<script type="text/javascript" src="resize.js"></script> +<script type="text/javascript" src="navtreedata.js"></script> +<script type="text/javascript" src="navtree.js"></script> +<script type="text/javascript"> + $(document).ready(initResizable); + $(window).load(resizeHeight); +</script> +<link href="search/search.css" rel="stylesheet" type="text/css"/> +<script type="text/javascript" src="search/searchdata.js"></script> +<script type="text/javascript" src="search/search.js"></script> +<script type="text/javascript"> + $(document).ready(function() { init_search(); }); +</script> +<!-- hack in the navigation tree --> +<script type="text/javascript" src="eigen_navtree_hacks.js"></script> +<link href="doxygen.css" rel="stylesheet" type="text/css" /> +<link href="madlib_extra.css" rel="stylesheet" type="text/css"/> +<!-- google analytics --> +<script> + (function(i,s,o,g,r,a,m){i['GoogleAnalyticsObject']=r;i[r]=i[r]||function(){ + (i[r].q=i[r].q||[]).push(arguments)},i[r].l=1*new Date();a=s.createElement(o), + m=s.getElementsByTagName(o)[0];a.async=1;a.src=g;m.parentNode.insertBefore(a,m) + })(window,document,'script','//www.google-analytics.com/analytics.js','ga'); + ga('create', 'UA-45382226-1', 'madlib.net'); + ga('send', 'pageview'); +</script> +</head> +<body> +<div id="top"><!-- do not remove this div, it is closed by doxygen! --> +<div id="titlearea"> +<table cellspacing="0" cellpadding="0"> + <tbody> + <tr style="height: 56px;"> + <td id="projectlogo"><a href="http://madlib.net";><img alt="Logo" src="madlib.png" height="50" style="padding-left:0.5em;" border="0"/ ></a></td> + <td style="padding-left: 0.5em;"> + <div id="projectname"> + <span id="projectnumber">1.9.1</span> + </div> + <div id="projectbrief">User Documentation for MADlib</div> + </td> + <td> <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> +</td> + </tr> + </tbody> +</table> +</div> +<!-- end header part --> +<!-- Generated by Doxygen 1.8.10 --> +<script type="text/javascript"> +var searchBox = new SearchBox("searchBox", "search",false,'Search'); +</script> +</div><!-- top --> +<div id="side-nav" class="ui-resizable side-nav-resizable"> + <div id="nav-tree"> + <div id="nav-tree-contents"> + <div id="nav-sync" class="sync"></div> + </div> + </div> + <div id="splitbar" style="-moz-user-select:none;" + class="ui-resizable-handle"> + </div> +</div> +<script type="text/javascript"> +$(document).ready(function(){initNavTree('group__grp__pivot.html','');}); +</script> +<div id="doc-content"> +<!-- window showing the filter options --> +<div id="MSearchSelectWindow" + onmouseover="return searchBox.OnSearchSelectShow()" + onmouseout="return searchBox.OnSearchSelectHide()" + onkeydown="return searchBox.OnSearchSelectKey(event)"> +</div> + +<!-- iframe showing the search results (closed by default) --> +<div id="MSearchResultsWindow"> +<iframe src="javascript:void(0)" frameborder="0" + name="MSearchResults" id="MSearchResults"> +</iframe> +</div> + +<div class="header"> + <div class="headertitle"> +<div class="title">Pivot<div class="ingroups"><a class="el" href="group__grp__datatrans.html">Data Types and Transformations</a></div></div> </div> +</div><!--header--> +<div class="contents"> +<div class="toc"><b>Contents</b> </p><ul> +<li> +<a href="#categorical">Pivoting</a> </li> +<li> +<a href="#notes">Notes</a> </li> +<li> +<a href="#examples">Examples</a> </li> +<li> +<a href="#literature">Literature</a> </li> +</ul> +</div><p><a class="anchor" id="categorical"></a>The goal of the MADlib pivot function is to provide a data summarization tool that can do basic OLAP type operations on data stored in one table and output the summarized data to a second table.</p> +<pre class="syntax"> +pivot( + source_table, + output_table, + index, + pivot_cols, + pivot_values, + aggregate_func, + fill_value, + keep_null, + output_col_dictionary + ) +</pre><p> <b>Arguments</b> </p><dl class="arglist"> +<dt>source_table </dt> +<dd>VARCHAR. Name of the source table (or view) containing data to pivot. </dd> +<dt>output_table </dt> +<dd><p class="startdd">VARCHAR. Name of output table that contains the pivoted data. The output table contains all the columns present in the <em>'index'</em> column list, plus additional columns for each distinct value in <em>'pivot_cols'</em>.</p> +<dl class="section note"><dt>Note</dt><dd>The names of the columns in the output table are auto-generated. Please see the examples section below to see how this works in practice. The convention used is to concatenate the following strings and separate each by an underscore '_' :<ul> +<li>name of the value column <em>'pivot_values'</em></li> +<li>aggregate function</li> +<li>name of the pivot column <em>'pivot_cols'</em></li> +<li>values in the pivot column</li> +</ul> +</dd></dl> +</dd> +<dt>index </dt> +<dd>VARCHAR. Comma-separated columns that will form the index of the output pivot table. By index we mean the values to group by; these are the rows in the output pivot table. </dd> +<dt>pivot_cols </dt> +<dd>VARCHAR. Comma-separated columns that will form the columns of the output pivot table. </dd> +<dt>pivot_values </dt> +<dd>VARCHAR. Comma-separated columns that contain the values to be summarized in the output pivot table. </dd> +<dt>aggregate_func (optional) </dt> +<dd><p class="startdd">VARCHAR. default: 'AVG'. A comma-separated list of aggregates to be applied to values. These can be PostgreSQL built-in aggregates [1] or UDAs. It is possible to assign a set of aggregates per value column. Please refer to the examples 12-14 below for syntax details.</p> +<dl class="section note"><dt>Note</dt><dd>Only aggregates with strict transition functions are permitted here. A strict transition function means rows with null values are ignored; the function is not called and the previous state value is retained. If you need some other behavior for null inputs, this should be done prior to calling the pivot function. Aggregates with strict transition functions are described in [2,3].</dd></dl> +</dd> +<dt>fill_value (optional) </dt> +<dd>VARCHAR. default: NULL. If specified, determines how to fill NULL values resulting from pivot operation. This is a global parameter (not applied per aggregate) and is applied post-aggregation to the output table. </dd> +<dt>keep_null (optional) </dt> +<dd>BOOLEAN. default: FALSE. If TRUE, then pivot columns are created corresponding to NULL categories. If FALSE, then no pivot columns will be created for NULL categories. </dd> +<dt>output_col_dictionary (optional) </dt> +<dd><p class="startdd">BOOLEAN. default: FALSE. This parameter is used to handle auto-generated column names that exceed the PostgreSQL limit of 63 bytes (could be a common occurrence). If TRUE, column names will be set as numerical IDs and will create a dictionary table called output_table appended with _dictionary. If FALSE, will auto-generate column names in the usual way unless the limit of 63 bytes will be exceeded. In this case, a dictionary output file will be created and a message given to the user. </p> +<p class="enddd"></p> +</dd> +</dl> +<p><a class="anchor" id="notes"></a></p><dl class="section note"><dt>Note</dt><dd><ul> +<li>NULLs in the index column are treated like any other value.</li> +<li>NULLs in the pivot column are ignored unless keep_null is TRUE.</li> +<li>Only strict transition functions are allowed so NULLs are ignored.</li> +<li>It is not allowed to set the fill_value parameter without setting the aggregate_func parameter due to possible ambiguity. Set aggregate_func to NULL for the default behavior and use fill_value as desired.</li> +<li>It is not allowed to set the output_col_dictionary parameter without setting the keep_null parameter due to possible ambiguity. Set keep_null to NULL for the default behavior and use output_col_dictionary as desired.</li> +<li>Expressions (instead of column names) are not supported. Create a view with the desired expressions and pass it as the input table (see example 3 below).</li> +<li>It is allowed to pass a partial mapping for the aggregate_func parameter. The missing value columns will be aggregated using the default function (average).</li> +</ul> +</dd></dl> +<p><a class="anchor" id="examples"></a></p><dl class="section user"><dt>Examples</dt><dd></dd></dl> +<ol type="1"> +<li>Create a simple dataset to demonstrate a basic pivot: <pre class="example"> +DROP TABLE IF EXISTS pivset CASCADE; -- View below may depend on table so use CASCADE +CREATE TABLE pivset( + id INTEGER, + piv INTEGER, + val FLOAT8 + ); +INSERT INTO pivset VALUES + (0, 10, 1), + (0, 10, 2), + (0, 20, 3), + (1, 20, 4), + (1, 30, 5), + (1, 30, 6), + (1, 10, 7), + (NULL, 10, 8), + (1, NULL, 9), + (1, 10, NULL); +</pre></li> +<li>Pivot the table: <pre class="example"> +DROP TABLE IF EXISTS pivout; +SELECT madlib.pivot('pivset', 'pivout', 'id', 'piv', 'val'); +SELECT * FROM pivout ORDER BY id; +</pre> <pre class="result"> + id | val_avg_piv_10 | val_avg_piv_20 | val_avg_piv_30 +----+----------------+----------------+---------------- + 0 | 1.5 | 3 | + 1 | 7 | 4 | 5.5 + | 8 | | +</pre> Here NULL is showing as an empty cell in the output.</li> +<li>Now let's add some more columns to our data set and create a view: <pre class="example"> +DROP VIEW IF EXISTS pivset_ext; +CREATE VIEW pivset_ext AS + SELECT *, + COALESCE(id + (val / 3)::integer, 0) AS id2, + COALESCE(100*(val / 3)::integer, 0) AS piv2, + COALESCE(val + 10, 0) AS val2 + FROM pivset; +SELECT id,id2,piv,piv2,val,val2 FROM pivset_ext +ORDER BY id,id2,piv,piv2,val,val2; +</pre> <pre class="result"> + id | id2 | piv | piv2 | val | val2 +----+-----+-----+------+-----+------ + 0 | 0 | 10 | 0 | 1 | 11 + 0 | 1 | 10 | 100 | 2 | 12 + 0 | 1 | 20 | 100 | 3 | 13 + 1 | 0 | 10 | 0 | | 0 + 1 | 2 | 20 | 100 | 4 | 14 + 1 | 3 | 10 | 200 | 7 | 17 + 1 | 3 | 30 | 200 | 5 | 15 + 1 | 3 | 30 | 200 | 6 | 16 + 1 | 4 | | 300 | 9 | 19 + | 0 | 10 | 300 | 8 | 18 +(10 rows) +</pre></li> +<li>Let's use a different aggregate function on the view we just created: <pre class="example"> +DROP TABLE IF EXISTS pivout; +SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val', 'sum'); +SELECT * FROM pivout ORDER BY id; +</pre> <pre class="result"> + id | val_sum_piv_10 | val_sum_piv_20 | val_sum_piv_30 +----+----------------+----------------+---------------- + 0 | 3 | 3 | + 1 | 7 | 4 | 11 + | 8 | | +</pre></li> +<li>Now create a custom aggregate. Note that the aggregate must have a strict transition function: <pre class="example"> +DROP FUNCTION IF EXISTS array_add1 (ANYARRAY, ANYELEMENT) CASCADE; +CREATE FUNCTION array_add1(ANYARRAY, ANYELEMENT) RETURNS ANYARRAY AS $$ + SELECT $1 || $2 +$$ LANGUAGE sql STRICT; +DROP AGGREGATE IF EXISTS array_accum1 (anyelement); +CREATE AGGREGATE array_accum1 (anyelement) ( + sfunc = array_add1, + stype = anyarray, + initcond = '{}' +); +DROP TABLE IF EXISTS pivout; +SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val', 'array_accum1'); +SELECT * FROM pivout ORDER BY id; +</pre> <pre class="result"> + id | val_array_accum1_piv_10 | val_array_accum1_piv_20 | val_array_accum1_piv_30 +----+-------------------------+-------------------------+------------------------- + 0 | {1,2} | {3} | {} + 1 | {7} | {4} | {5,6} + | {8} | {} | {} +</pre></li> +<li>Keep null values in the pivot column: <pre class="example"> +DROP TABLE IF EXISTS pivout; +SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val', 'sum', True); +SELECT * FROM pivout ORDER BY id; +</pre> <pre class="result"> + id | val_sum_piv_10 | val_sum_piv_20 | val_sum_piv_30 | val_sum_piv_null +----+----------------+----------------+----------------+------------------ + 0 | 3 | 3 | | + 1 | 7 | 4 | 11 | 9 + | 8 | | | +</pre></li> +<li>Fill null results with a value of interest: <pre class="example"> +DROP TABLE IF EXISTS pivout; +SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val', 'sum', '111'); +SELECT * FROM pivout ORDER BY id; +</pre> <pre class="result"> + id | val_sum_piv_10 | val_sum_piv_20 | val_sum_piv_30 +----+----------------+----------------+---------------- + 0 | 3 | 3 | 111 + 1 | 7 | 4 | 11 + | 8 | 111 | 111 +</pre></li> +<li>Use multiple index columns: <pre class="example"> +DROP TABLE IF EXISTS pivout; +SELECT madlib.pivot('pivset_ext', 'pivout', 'id,id2', 'piv', 'val'); +SELECT * FROM pivout ORDER BY id,id2; +</pre> <pre class="result"> + id | id2 | val_avg_piv_10 | val_avg_piv_20 | val_avg_piv_30 +----+-----+----------------+----------------+---------------- + 0 | 0 | 1 | | + 0 | 1 | 2 | 3 | + 1 | 0 | | | + 1 | 2 | | 4 | + 1 | 3 | 7 | | 5.5 + 1 | 4 | | | + | 0 | 8 | | +</pre></li> +<li>Turn on the extended view for readability: <pre class="example"> +\x on +</pre></li> +<li>Use multiple pivot columns: <pre class="example"> +DROP TABLE IF EXISTS pivout; +SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv, piv2', 'val'); +SELECT * FROM pivout ORDER BY id; +</pre> <pre class="result"> +-[ RECORD 1 ]-----------+---- +id | 0 +val_avg_piv_10_piv2_0 | 1 +val_avg_piv_10_piv2_100 | 2 +val_avg_piv_10_piv2_200 | +val_avg_piv_10_piv2_300 | +val_avg_piv_20_piv2_0 | +val_avg_piv_20_piv2_100 | 3 +val_avg_piv_20_piv2_200 | +val_avg_piv_20_piv2_300 | +val_avg_piv_30_piv2_0 | +val_avg_piv_30_piv2_100 | +val_avg_piv_30_piv2_200 | +val_avg_piv_30_piv2_300 | +-[ RECORD 2 ]-----------+---- +id | 1 +val_avg_piv_10_piv2_0 | +val_avg_piv_10_piv2_100 | +val_avg_piv_10_piv2_200 | 7 +val_avg_piv_10_piv2_300 | +val_avg_piv_20_piv2_0 | +val_avg_piv_20_piv2_100 | 4 +val_avg_piv_20_piv2_200 | +val_avg_piv_20_piv2_300 | +val_avg_piv_30_piv2_0 | +val_avg_piv_30_piv2_100 | +val_avg_piv_30_piv2_200 | 5.5 +val_avg_piv_30_piv2_300 | +... +</pre></li> +<li>Use multiple value columns: <pre class="example"> +DROP TABLE IF EXISTS pivout; +SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val, val2'); +SELECT * FROM pivout ORDER BY id; +</pre> <pre class="result"> +-[ RECORD 1 ]---+----- +id | 0 +val_avg_piv_10 | 1.5 +val_avg_piv_20 | 3 +val_avg_piv_30 | +val2_avg_piv_10 | 11.5 +val2_avg_piv_20 | 13 +val2_avg_piv_30 | +-[ RECORD 2 ]---+----- +id | 1 +val_avg_piv_10 | 7 +val_avg_piv_20 | 4 +val_avg_piv_30 | 5.5 +val2_avg_piv_10 | 8.5 +val2_avg_piv_20 | 14 +val2_avg_piv_30 | 15.5 +... +</pre></li> +<li>Use multiple aggregate functions on the same value column (cross product): <pre class="example"> +DROP TABLE IF EXISTS pivout; +SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val', 'avg, sum'); +SELECT * FROM pivout ORDER BY id; +</pre> <pre class="result"> +-[ RECORD 1 ]--+---- +id | 0 +val_avg_piv_10 | 1.5 +val_avg_piv_20 | 3 +val_avg_piv_30 | +val_sum_piv_10 | 3 +val_sum_piv_20 | 3 +val_sum_piv_30 | +-[ RECORD 2 ]--+---- +id | 1 +val_avg_piv_10 | 7 +val_avg_piv_20 | 4 +val_avg_piv_30 | 5.5 +val_sum_piv_10 | 7 +val_sum_piv_20 | 4 +val_sum_piv_30 | 11 +... +</pre></li> +<li>Use different aggregate functions for different value columns: <pre class="example"> +DROP TABLE IF EXISTS pivout; +SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val, val2', + 'val=avg, val2=sum'); +SELECT * FROM pivout ORDER BY id; +</pre> <pre class="result"> +-[ RECORD 1 ]---+---- +id | 0 +val_avg_piv_10 | 1.5 +val_avg_piv_20 | 3 +val_avg_piv_30 | +val2_sum_piv_10 | 23 +val2_sum_piv_20 | 13 +val2_sum_piv_30 | +-[ RECORD 2 ]---+---- +id | 1 +val_avg_piv_10 | 7 +val_avg_piv_20 | 4 +val_avg_piv_30 | 5.5 +val2_sum_piv_10 | 17 +val2_sum_piv_20 | 14 +val2_sum_piv_30 | 31 +... +</pre></li> +<li>Use multiple aggregate functions for different value columns: <pre class="example"> +DROP TABLE IF EXISTS pivout; +SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val, val2', + 'val=avg, val2=[avg,sum]'); +SELECT * FROM pivout ORDER BY id; +</pre> <pre class="result"> +-[ RECORD 1 ]---+----- +id | 0 +val_avg_piv_10 | 1.5 +val_avg_piv_20 | 3 +val_avg_piv_30 | +val2_avg_piv_10 | 11.5 +val2_avg_piv_20 | 13 +val2_avg_piv_30 | +val2_sum_piv_10 | 23 +val2_sum_piv_20 | 13 +val2_sum_piv_30 | +-[ RECORD 2 ]---+----- +id | 1 +val_avg_piv_10 | 7 +val_avg_piv_20 | 4 +val_avg_piv_30 | 5.5 +val2_avg_piv_10 | 8.5 +val2_avg_piv_20 | 14 +val2_avg_piv_30 | 15.5 +val2_sum_piv_10 | 17 +val2_sum_piv_20 | 14 +val2_sum_piv_30 | 31 +... +</pre></li> +<li>Combine all of the options: <pre class="example"> +DROP TABLE IF EXISTS pivout; +SELECT madlib.pivot('pivset_ext', 'pivout', 'id, id2', 'piv, piv2', 'val, val2', + 'val=avg, val2=[avg,sum]', '111', True); +SELECT * FROM pivout ORDER BY id,id2; +</pre> <pre class="result"> +-[ RECORD 1 ]--------------+----- +id | 0 +id2 | 0 +val_avg_piv_null_piv2_0 | 111 +val_avg_piv_null_piv2_100 | 111 +val_avg_piv_null_piv2_200 | 111 +val_avg_piv_null_piv2_300 | 111 +val_avg_piv_10_piv2_0 | 1 +val_avg_piv_10_piv2_100 | 111 +val_avg_piv_10_piv2_200 | 111 +val_avg_piv_10_piv2_300 | 111 +val_avg_piv_20_piv2_0 | 111 +val_avg_piv_20_piv2_100 | 111 +val_avg_piv_20_piv2_200 | 111 +val_avg_piv_20_piv2_300 | 111 +val_avg_piv_30_piv2_0 | 111 +val_avg_piv_30_piv2_100 | 111 +val_avg_piv_30_piv2_200 | 111 +val_avg_piv_30_piv2_300 | 111 +val2_avg_piv_null_piv2_0 | 111 +val2_avg_piv_null_piv2_100 | 111 +val2_avg_piv_null_piv2_200 | 111 +val2_avg_piv_null_piv2_300 | 111 +val2_avg_piv_10_piv2_0 | 11 +val2_avg_piv_10_piv2_100 | 111 +val2_avg_piv_10_piv2_200 | 111 +val2_avg_piv_10_piv2_300 | 111 +val2_avg_piv_20_piv2_0 | 111 +val2_avg_piv_20_piv2_100 | 111 +val2_avg_piv_20_piv2_200 | 111 +val2_avg_piv_20_piv2_300 | 111 +val2_avg_piv_30_piv2_0 | 111 +val2_avg_piv_30_piv2_100 | 111 +val2_avg_piv_30_piv2_200 | 111 +val2_avg_piv_30_piv2_300 | 111 +val2_sum_piv_null_piv2_0 | 111 +val2_sum_piv_null_piv2_100 | 111 +val2_sum_piv_null_piv2_200 | 111 +val2_sum_piv_null_piv2_300 | 111 +val2_sum_piv_10_piv2_0 | 11 +val2_sum_piv_10_piv2_100 | 111 +val2_sum_piv_10_piv2_200 | 111 +val2_sum_piv_10_piv2_300 | 111 +val2_sum_piv_20_piv2_0 | 111 +val2_sum_piv_20_piv2_100 | 111 +val2_sum_piv_20_piv2_200 | 111 +val2_sum_piv_20_piv2_300 | 111 +val2_sum_piv_30_piv2_0 | 111 +val2_sum_piv_30_piv2_100 | 111 +val2_sum_piv_30_piv2_200 | 111 +val2_sum_piv_30_piv2_300 | 111 +-[ RECORD 2 ]--------------+----- +id | 0 +id2 | 1 +val_avg_piv_null_piv2_0 | 111 +val_avg_piv_null_piv2_100 | 111 +val_avg_piv_null_piv2_200 | 111 +val_avg_piv_null_piv2_300 | 111 +val_avg_piv_10_piv2_0 | 111 +val_avg_piv_10_piv2_100 | 2 +val_avg_piv_10_piv2_200 | 111 +val_avg_piv_10_piv2_300 | 111 +val_avg_piv_20_piv2_0 | 111 +val_avg_piv_20_piv2_100 | 3 +val_avg_piv_20_piv2_200 | 111 +val_avg_piv_20_piv2_300 | 111 +val_avg_piv_30_piv2_0 | 111 +val_avg_piv_30_piv2_100 | 111 +val_avg_piv_30_piv2_200 | 111 +val_avg_piv_30_piv2_300 | 111 +val2_avg_piv_null_piv2_0 | 111 +val2_avg_piv_null_piv2_100 | 111 +val2_avg_piv_null_piv2_200 | 111 +val2_avg_piv_null_piv2_300 | 111 +val2_avg_piv_10_piv2_0 | 111 +val2_avg_piv_10_piv2_100 | 12 +val2_avg_piv_10_piv2_200 | 111 +val2_avg_piv_10_piv2_300 | 111 +val2_avg_piv_20_piv2_0 | 111 +val2_avg_piv_20_piv2_100 | 13 +val2_avg_piv_20_piv2_200 | 111 +val2_avg_piv_20_piv2_300 | 111 +val2_avg_piv_30_piv2_0 | 111 +val2_avg_piv_30_piv2_100 | 111 +val2_avg_piv_30_piv2_200 | 111 +val2_avg_piv_30_piv2_300 | 111 +val2_sum_piv_null_piv2_0 | 111 +val2_sum_piv_null_piv2_100 | 111 +val2_sum_piv_null_piv2_200 | 111 +val2_sum_piv_null_piv2_300 | 111 +val2_sum_piv_10_piv2_0 | 111 +val2_sum_piv_10_piv2_100 | 12 +val2_sum_piv_10_piv2_200 | 111 +val2_sum_piv_10_piv2_300 | 111 +val2_sum_piv_20_piv2_0 | 111 +val2_sum_piv_20_piv2_100 | 13 +val2_sum_piv_20_piv2_200 | 111 +val2_sum_piv_20_piv2_300 | 111 +val2_sum_piv_30_piv2_0 | 111 +val2_sum_piv_30_piv2_100 | 111 +val2_sum_piv_30_piv2_200 | 111 +val2_sum_piv_30_piv2_300 | 111 +... +</pre></li> +<li>Create a dictionary for output column names: <pre class="example"> +DROP TABLE IF EXISTS pivout, pivout_dictionary; +SELECT madlib.pivot('pivset_ext', 'pivout', 'id, id2', 'piv, piv2', 'val, val2', + 'val=avg, val2=[avg,sum]', '111', True, True); +SELECT * FROM pivout_dictionary; +</pre> <pre class="result"> + __pivot_cid__ | pval | agg | piv | piv2 | col_name +---------------+------+-----+-----+------+------------------------------ + __p_1__ | val | avg | | 100 | "val_avg_piv_null_piv2_100" + __p_5__ | val | avg | 10 | 100 | "val_avg_piv_10_piv2_100" + __p_9__ | val | avg | 20 | 100 | "val_avg_piv_20_piv2_100" + __p_12__ | val | avg | 30 | 0 | "val_avg_piv_30_piv2_0" + __p_16__ | val2 | avg | | 0 | "val2_avg_piv_null_piv2_0" + __p_23__ | val2 | avg | 10 | 300 | "val2_avg_piv_10_piv2_300" + __p_27__ | val2 | avg | 20 | 300 | "val2_avg_piv_20_piv2_300" + __p_30__ | val2 | avg | 30 | 200 | "val2_avg_piv_30_piv2_200" + __p_34__ | val2 | sum | | 200 | "val2_sum_piv_null_piv2_200" + __p_38__ | val2 | sum | 10 | 200 | "val2_sum_piv_10_piv2_200" + __p_41__ | val2 | sum | 20 | 100 | "val2_sum_piv_20_piv2_100" + __p_45__ | val2 | sum | 30 | 100 | "val2_sum_piv_30_piv2_100" + __p_2__ | val | avg | | 200 | "val_avg_piv_null_piv2_200" + __p_6__ | val | avg | 10 | 200 | "val_avg_piv_10_piv2_200" + __p_11__ | val | avg | 20 | 300 | "val_avg_piv_20_piv2_300" + __p_15__ | val | avg | 30 | 300 | "val_avg_piv_30_piv2_300" + __p_19__ | val2 | avg | | 300 | "val2_avg_piv_null_piv2_300" + __p_20__ | val2 | avg | 10 | 0 | "val2_avg_piv_10_piv2_0" + __p_24__ | val2 | avg | 20 | 0 | "val2_avg_piv_20_piv2_0" + __p_28__ | val2 | avg | 30 | 0 | "val2_avg_piv_30_piv2_0" + __p_33__ | val2 | sum | | 100 | "val2_sum_piv_null_piv2_100" + __p_37__ | val2 | sum | 10 | 100 | "val2_sum_piv_10_piv2_100" + __p_42__ | val2 | sum | 20 | 200 | "val2_sum_piv_20_piv2_200" + __p_46__ | val2 | sum | 30 | 200 | "val2_sum_piv_30_piv2_200" + __p_3__ | val | avg | | 300 | "val_avg_piv_null_piv2_300" + __p_7__ | val | avg | 10 | 300 | "val_avg_piv_10_piv2_300" + __p_10__ | val | avg | 20 | 200 | "val_avg_piv_20_piv2_200" + __p_14__ | val | avg | 30 | 200 | "val_avg_piv_30_piv2_200" + __p_18__ | val2 | avg | | 200 | "val2_avg_piv_null_piv2_200" + __p_21__ | val2 | avg | 10 | 100 | "val2_avg_piv_10_piv2_100" + __p_25__ | val2 | avg | 20 | 100 | "val2_avg_piv_20_piv2_100" + __p_29__ | val2 | avg | 30 | 100 | "val2_avg_piv_30_piv2_100" + __p_32__ | val2 | sum | | 0 | "val2_sum_piv_null_piv2_0" + __p_36__ | val2 | sum | 10 | 0 | "val2_sum_piv_10_piv2_0" + __p_43__ | val2 | sum | 20 | 300 | "val2_sum_piv_20_piv2_300" + __p_47__ | val2 | sum | 30 | 300 | "val2_sum_piv_30_piv2_300" + __p_0__ | val | avg | | 0 | "val_avg_piv_null_piv2_0" + __p_4__ | val | avg | 10 | 0 | "val_avg_piv_10_piv2_0" + __p_8__ | val | avg | 20 | 0 | "val_avg_piv_20_piv2_0" + __p_13__ | val | avg | 30 | 100 | "val_avg_piv_30_piv2_100" + __p_17__ | val2 | avg | | 100 | "val2_avg_piv_null_piv2_100" + __p_22__ | val2 | avg | 10 | 200 | "val2_avg_piv_10_piv2_200" + __p_26__ | val2 | avg | 20 | 200 | "val2_avg_piv_20_piv2_200" + __p_31__ | val2 | avg | 30 | 300 | "val2_avg_piv_30_piv2_300" + __p_35__ | val2 | sum | | 300 | "val2_sum_piv_null_piv2_300" + __p_39__ | val2 | sum | 10 | 300 | "val2_sum_piv_10_piv2_300" + __p_40__ | val2 | sum | 20 | 0 | "val2_sum_piv_20_piv2_0" + __p_44__ | val2 | sum | 30 | 0 | "val2_sum_piv_30_piv2_0" +(48 rows) +</pre> <pre class="example"> +SELECT * FROM pivout ORDER BY id,id2; +</pre> <pre class="result"> +-[ RECORD 1 ]-- +id | 0 +id2 | 0 +__p_0__ | 111 +__p_1__ | 111 +__p_2__ | 111 +__p_3__ | 111 +__p_4__ | 1 +__p_5__ | 111 +__p_6__ | 111 +__p_7__ | 111 +__p_8__ | 111 +__p_9__ | 111 +__p_10__ | 111 +__p_11__ | 111 +__p_12__ | 111 +__p_13__ | 111 +__p_14__ | 111 +__p_15__ | 111 +__p_16__ | 111 +__p_17__ | 111 +__p_18__ | 111 +__p_19__ | 111 +__p_20__ | 11 +__p_21__ | 111 +__p_22__ | 111 +__p_23__ | 111 +__p_24__ | 111 +__p_25__ | 111 +__p_26__ | 111 +__p_27__ | 111 +__p_28__ | 111 +__p_29__ | 111 +__p_30__ | 111 +__p_31__ | 111 +__p_32__ | 111 +__p_33__ | 111 +__p_34__ | 111 +__p_35__ | 111 +__p_36__ | 11 +__p_37__ | 111 +__p_38__ | 111 +__p_39__ | 111 +__p_40__ | 111 +__p_41__ | 111 +__p_42__ | 111 +__p_43__ | 111 +__p_44__ | 111 +__p_45__ | 111 +__p_46__ | 111 +__p_47__ | 111 +-[ RECORD 2 ]-- +id | 0 +id2 | 1 +__p_0__ | 111 +__p_1__ | 111 +__p_2__ | 111 +__p_3__ | 111 +__p_4__ | 111 +__p_5__ | 2 +__p_6__ | 111 +__p_7__ | 111 +__p_8__ | 111 +__p_9__ | 3 +__p_10__ | 111 +__p_11__ | 111 +__p_12__ | 111 +__p_13__ | 111 +__p_14__ | 111 +__p_15__ | 111 +__p_16__ | 111 +__p_17__ | 111 +__p_18__ | 111 +__p_19__ | 111 +__p_20__ | 111 +__p_21__ | 12 +__p_22__ | 111 +__p_23__ | 111 +__p_24__ | 111 +__p_25__ | 13 +__p_26__ | 111 +__p_27__ | 111 +__p_28__ | 111 +__p_29__ | 111 +__p_30__ | 111 +__p_31__ | 111 +__p_32__ | 111 +__p_33__ | 111 +__p_34__ | 111 +__p_35__ | 111 +__p_36__ | 111 +__p_37__ | 12 +__p_38__ | 111 +__p_39__ | 111 +__p_40__ | 111 +__p_41__ | 13 +__p_42__ | 111 +__p_43__ | 111 +__p_44__ | 111 +__p_45__ | 111 +__p_46__ | 111 +__p_47__ | 111 +... +</pre></li> +</ol> +<p><a class="anchor" id="literature"></a></p><dl class="section user"><dt>Literature</dt><dd></dd></dl> +<p><a class="anchor" id="svm-lit-1"></a>[1] <a href="https://www.postgresql.org/docs/8.2/static/functions-aggregate.html";>https://www.postgresql.org/docs/8.2/static/functions-aggregate.html</a></p> +<p>[2] <a href="https://www.postgresql.org/docs/8.2/static/sql-createaggregate.html";>https://www.postgresql.org/docs/8.2/static/sql-createaggregate.html</a></p> +<p>[3] <a href="https://www.postgresql.org/docs/8.2/static/xaggr.html";>https://www.postgresql.org/docs/8.2/static/xaggr.html</a> </p> +</div><!-- contents --> +</div><!-- doc-content --> +<!-- start footer part --> +<div id="nav-path" class="navpath"><!-- id is needed for treeview function! --> + <ul> + <li class="footer">Generated on Tue Sep 20 2016 11:27:01 for MADlib by + <a href="http://www.doxygen.org/index.html";> + <img class="footer" src="doxygen.png" alt="doxygen"/></a> 1.8.10 </li> + </ul> +</div> +</body> +</html>
http://git-wip-us.apache.org/repos/asf/incubator-madlib-site/blob/bed9253d/docs/latest/group__grp__pmml.html ---------------------------------------------------------------------- diff --git a/docs/latest/group__grp__pmml.html b/docs/latest/group__grp__pmml.html index 3d063f5..94c8200 100644 --- a/docs/latest/group__grp__pmml.html +++ b/docs/latest/group__grp__pmml.html @@ -47,7 +47,7 @@ <td id="projectlogo"><a href="http://madlib.net";><img alt="Logo" src="madlib.png" height="50" style="padding-left:0.5em;" border="0"/ ></a></td> <td style="padding-left: 0.5em;"> <div id="projectname"> - <span id="projectnumber">1.9</span> + <span id="projectnumber">1.9.1</span> </div> <div id="projectbrief">User Documentation for MADlib</div> </td> @@ -184,7 +184,7 @@ SELECT madlib.pmml('patients_logregr'); </pre> Result: <pre class="result"> <?xml version="1.0" standalone="yes"?> <PMML version="4.1" xmlns="http://www.dmg.org/pmml-v4-1.html"> - <Header copyright="Copyright (c) 2014 gpadmin"> + <Header copyright="redacted for this example"> <Extension extender="MADlib" name="user" value="gpadmin"> <Application name="MADlib" version="1.7"> <Timestamp> @@ -235,7 +235,7 @@ SELECT madlib.pmml('patients_logregr_grouping', </pre> Result: <pre class="result"> <?xml version="1.0" standalone="yes"?> <PMML version="4.1" xmlns="http://www.dmg.org/pmml-v4-1.html"> - <Header copyright="Copyright (c) 2014 gpadmin"> + <Header copyright="redacted for this example"> <Extension extender="MADlib" name="user" value="gpadmin"> <Application name="MADlib" version="1.7"> <Timestamp> @@ -318,7 +318,7 @@ SELECT madlib.pmml('patients_logregr_grouping', <!-- start footer part --> <div id="nav-path" class="navpath"><!-- id is needed for treeview function! --> <ul> - <li class="footer">Generated on Thu Apr 7 2016 14:24:11 for MADlib by + <li class="footer">Generated on Tue Sep 20 2016 11:27:01 for MADlib by <a href="http://www.doxygen.org/index.html";> <img class="footer" src="doxygen.png" alt="doxygen"/></a> 1.8.10 </li> </ul> http://git-wip-us.apache.org/repos/asf/incubator-madlib-site/blob/bed9253d/docs/latest/group__grp__pred.html ---------------------------------------------------------------------- diff --git a/docs/latest/group__grp__pred.html b/docs/latest/group__grp__pred.html new file mode 100644 index 0000000..a36b564 --- /dev/null +++ b/docs/latest/group__grp__pred.html @@ -0,0 +1,360 @@ +<!-- HTML header for doxygen 1.8.4--> +<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd";> +<html xmlns="http://www.w3.org/1999/xhtml";> +<head> +<meta http-equiv="Content-Type" content="text/xhtml;charset=UTF-8"/> +<meta http-equiv="X-UA-Compatible" content="IE=9"/> +<meta name="generator" content="Doxygen 1.8.10"/> +<meta name="keywords" content="madlib,postgres,greenplum,machine learning,data mining,deep learning,ensemble methods,data science,market basket analysis,affinity analysis,pca,lda,regression,elastic net,huber white,proportional hazards,k-means,latent dirichlet allocation,bayes,support vector machines,svm"/> +<title>MADlib: Prediction Metrics</title> +<link href="tabs.css" rel="stylesheet" type="text/css"/> +<script type="text/javascript" src="jquery.js"></script> +<script type="text/javascript" src="dynsections.js"></script> +<link href="navtree.css" rel="stylesheet" type="text/css"/> +<script type="text/javascript" src="resize.js"></script> +<script type="text/javascript" src="navtreedata.js"></script> +<script type="text/javascript" src="navtree.js"></script> +<script type="text/javascript"> + $(document).ready(initResizable); + $(window).load(resizeHeight); +</script> +<link href="search/search.css" rel="stylesheet" type="text/css"/> +<script type="text/javascript" src="search/searchdata.js"></script> +<script type="text/javascript" src="search/search.js"></script> +<script type="text/javascript"> + $(document).ready(function() { init_search(); }); +</script> +<!-- hack in the navigation tree --> +<script type="text/javascript" src="eigen_navtree_hacks.js"></script> +<link href="doxygen.css" rel="stylesheet" type="text/css" /> +<link href="madlib_extra.css" rel="stylesheet" type="text/css"/> +<!-- google analytics --> +<script> + (function(i,s,o,g,r,a,m){i['GoogleAnalyticsObject']=r;i[r]=i[r]||function(){ + (i[r].q=i[r].q||[]).push(arguments)},i[r].l=1*new Date();a=s.createElement(o), + m=s.getElementsByTagName(o)[0];a.async=1;a.src=g;m.parentNode.insertBefore(a,m) + })(window,document,'script','//www.google-analytics.com/analytics.js','ga'); + ga('create', 'UA-45382226-1', 'madlib.net'); + ga('send', 'pageview'); +</script> +</head> +<body> +<div id="top"><!-- do not remove this div, it is closed by doxygen! --> +<div id="titlearea"> +<table cellspacing="0" cellpadding="0"> + <tbody> + <tr style="height: 56px;"> + <td id="projectlogo"><a href="http://madlib.net";><img alt="Logo" src="madlib.png" height="50" style="padding-left:0.5em;" border="0"/ ></a></td> + <td style="padding-left: 0.5em;"> + <div id="projectname"> + <span id="projectnumber">1.9.1</span> + </div> + <div id="projectbrief">User Documentation for MADlib</div> + </td> + <td> <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> +</td> + </tr> + </tbody> +</table> +</div> +<!-- end header part --> +<!-- Generated by Doxygen 1.8.10 --> +<script type="text/javascript"> +var searchBox = new SearchBox("searchBox", "search",false,'Search'); +</script> +</div><!-- top --> +<div id="side-nav" class="ui-resizable side-nav-resizable"> + <div id="nav-tree"> + <div id="nav-tree-contents"> + <div id="nav-sync" class="sync"></div> + </div> + </div> + <div id="splitbar" style="-moz-user-select:none;" + class="ui-resizable-handle"> + </div> +</div> +<script type="text/javascript"> +$(document).ready(function(){initNavTree('group__grp__pred.html','');}); +</script> +<div id="doc-content"> +<!-- window showing the filter options --> +<div id="MSearchSelectWindow" + onmouseover="return searchBox.OnSearchSelectShow()" + onmouseout="return searchBox.OnSearchSelectHide()" + onkeydown="return searchBox.OnSearchSelectKey(event)"> +</div> + +<!-- iframe showing the search results (closed by default) --> +<div id="MSearchResultsWindow"> +<iframe src="javascript:void(0)" frameborder="0" + name="MSearchResults" id="MSearchResults"> +</iframe> +</div> + +<div class="header"> + <div class="headertitle"> +<div class="title">Prediction Metrics<div class="ingroups"><a class="el" href="group__grp__mdl.html">Model Evaluation</a></div></div> </div> +</div><!--header--> +<div class="contents"> +<div class="toc"><b>Contents</b> </p><ul> +<li> +<a href="#list">List of Prediction Metric Functions</a> </li> +<li> +<a href="#specs">Function Specific Details</a> </li> +<li> +<a href="#examples">Examples</a> </li> +<li> +<a href="#literature">Literature</a> </li> +<li> +<a href="#related">Related Topics</a> </li> +</ul> +</div><p>This module provides a set of metrics to evaluate the quality of predictions of a model. A typical function will take a set of "prediction" and "observation" values and use them to calculate the desired metric, unless noted otherwise. Grouping is supported for all functions (except confusion matrix).</p> +<p><a class="anchor" id="list"></a></p><dl class="section user"><dt>Prediction Metrics Functions</dt><dd><table class="output"> +<tr> +<th>mean_abs_error(table_in, table_out, prediction_col, observed_col, grouping_cols)</th><td>Mean absolute error </td></tr> +<tr> +<th>mean_abs_perc_error(table_in, table_out, prediction_col, observed_col, grouping_cols)</th><td>Mean absolute percentage error </td></tr> +<tr> +<th>mean_perc_error(table_in, table_out, prediction_col, observed_col, grouping_cols)</th><td>Mean percentage error </td></tr> +<tr> +<th>mean_squared_error(table_in, table_out, prediction_col, observed_col, grouping_cols)</th><td>Mean squared error </td></tr> +<tr> +<th>r2_score(table_in, table_out, prediction_col, observed_col, grouping_cols)</th><td>R-squared </td></tr> +<tr> +<th>adjusted_r2_score(table_in, table_out, prediction_col, observed_col, num_predictors, training_size, grouping_cols)</th><td>Adjusted R-squared </td></tr> +<tr> +<th>binary_classifier(table_in, table_out, prediction_col, observed_col, grouping_cols)</th><td>Collection of prediction metrics related to binary classification </td></tr> +<tr> +<th>area_under_roc(table_in, table_out, prediction_col, observed_col, grouping_cols)</th><td>Area under the ROC curve (in binary classification) </td></tr> +<tr> +<th>confusion_matrix(table_in, table_out, prediction_col, observed_col, grouping_cols)</th><td>Confusion matrix for a multi-class classifier </td></tr> +</table> +</dd></dl> +<p><b>Arguments</b> </p><dl class="arglist"> +<dt>table_in </dt> +<dd>TEXT. Name of the input table. </dd> +<dt>table_out </dt> +<dd>TEXT. Name of the output table. For consistency, a table is created for all metric outputs even when grouping is not used, which may mean there is only a single value in the output table in some cases. </dd> +<dt>prediction_col </dt> +<dd>TEXT. Name of the column of predicted values from input table. </dd> +<dt>observed_col </dt> +<dd>TEXT. Name of the column of observed values from input table. </dd> +<dt>num_predictors (for adjusted R-squared score only) </dt> +<dd>INTEGER. The number of parameters in the predicting model, not counting the constant term. </dd> +<dt>training_size (for adjusted R-squared score only) </dt> +<dd>INTEGER. The number of rows used for training, excluding any NULL rows. </dd> +<dt>grouping_cols (optional) </dt> +<dd>TEXT, default: NULL. Name of the column of grouping values from input table. </dd> +</dl> +<p><a class="anchor" id="specs"></a></p><dl class="section user"><dt>Function Specific Details</dt><dd></dd></dl> +<p><b>R-squared Score</b></p> +<p>This function returns the coefficient of determination (R2) between the predicted and observed values. An R2 of 1 indicates that the regression line perfectly fits the data, while an R2 of 0 indicates that the line does not fit the data at all. Negative values of R2 may occur when fitting non-linear functions to data. Please refer to reference <a href="#r2">[1]</a> for more details.</p> +<p><b>Adjusted R-squared Score</b></p> +<p>This function returns the adjusted R2 score in addition to the R-squared score described above. Adjusted R2 score is used to counter the problem of the R2 automatically increasing when extra explanatory variables are added to the model. It takes two additional parameters describing the degrees of freedom of the model (num_predictors) and the size of the training set over which it was developed (training_size):</p> +<ul> +<li>num_predictors: Indicates the number of parameters the model has other than the constant term. For example, if it is set to '3' the model may take the following form as an example: 7 + 5x + 39y + 0.91z.</li> +<li>training_size: Indicates the number of rows in the training set (excluding any NULL rows).</li> +</ul> +<p>Neither of these arguments can be deduced from the predicted values and the test data alone which is why they are explicit inputs. Please refer to reference <a href="#r2">[1]</a> for more details.</p> +<p><a class="anchor" id="bc"></a><b>Binary Classification</b></p> +<p>This function returns an output table with a number of metrics commonly used in binary classification.</p> +<p>The definitions of the various metrics are as follows:</p> +<ul> +<li><img class="formulaInl" alt="$\textit{tp}$" src="form_562.png"/> is the count of correctly-classified positives.</li> +<li><img class="formulaInl" alt="$\textit{tn}$" src="form_563.png"/> is the count of correctly-classified negatives.</li> +<li><img class="formulaInl" alt="$\textit{fp}$" src="form_564.png"/> is the count of misclassified negatives.</li> +<li><img class="formulaInl" alt="$\textit{fn}$" src="form_565.png"/> is the count of misclassified positives.</li> +<li><img class="formulaInl" alt="$\textit{tpr}=\textit{tp}/(\textit{tp}+\textit{fn})$" src="form_566.png"/>.</li> +<li><img class="formulaInl" alt="$\textit{tnr}=\textit{tn}/(\textit{fp}+\textit{tn})$" src="form_567.png"/>.</li> +<li><img class="formulaInl" alt="$\textit{ppv}=\textit{tp}/(\textit{tp}+\textit{fp})$" src="form_568.png"/>.</li> +<li><img class="formulaInl" alt="$\textit{npv}=\textit{tn}/(\textit{tn}+\textit{fn})$" src="form_569.png"/>.</li> +<li><img class="formulaInl" alt="$\textit{fpr}=\textit{fp}/(\textit{fp}+\textit{tn})$" src="form_570.png"/>.</li> +<li><img class="formulaInl" alt="$\textit{fdr}=1-\textit{ppv}$" src="form_571.png"/>.</li> +<li><img class="formulaInl" alt="$\textit{fnr}=\textit{fn}/(\textit{fn}+\textit{tp})$" src="form_572.png"/>.</li> +<li><img class="formulaInl" alt="$\textit{acc}=(\textit{tp}+\textit{tn})/(\textit{tp}+\textit{tn}+\textit{fp} +\textit{fn})$" src="form_575.png"/>.</li> +<li><img class="formulaInl" alt="$\textit{f1}=2*\textit{tp}/(2*\textit{tp}+\textit{fp}+\textit{fn})$" src="form_574.png"/>.</li> +</ul> +<p><b>Area Under ROC Curve</b></p> +<p>This function returns the area under the Receiver Operating Characteristic curve for binary classification (the AUC). The ROC curve is the curve relating the classifier's TPR and FPR metrics. (See <a href="#bc">Binary Classification</a> above for a definition of these metrics). Please refer to reference <a href="#aoc">[2]</a> for more details. Note that the binary classification function can be used to obtain the data (TPR and FPR values) required for drawing the ROC curve.</p> +<dl class="section note"><dt>Note</dt><dd>For 'binary_classifier' and 'area_under_roc' functions:<ul> +<li>The 'observed_col' column is assumed to be a numeric column with two values: 0 and 1, or a Boolean column. For the purposes of the metric calculation, 0 is considered to be negative and 1 to be positive.</li> +<li>The 'pred_col' column is expected to contain numeric values corresponding to likelihood/probability. A larger value corresponds to greater certainty that the observed value will be '1', and a lower value corresponds to a greater certainty that it will be '0'.</li> +</ul> +</dd></dl> +<p><b>Confusion Matrix</b></p> +<p>This function returns the confusion matrix of a multi-class classification. Each column of the matrix represents the instances in a predicted class while each row represents the instances in an actual class. This allows more detailed analysis than mere proportion of correct guesses (accuracy). Please refer to the reference <a href="#cm">[3]</a> for more details. Please note that grouping is not supported for the confusion matrix.</p> +<p><a class="anchor" id="examples"></a></p><dl class="section user"><dt>Examples</dt><dd></dd></dl> +<ol type="1"> +<li>Create the sample data: <pre class="example"> +DROP TABLE IF EXISTS test_set; +CREATE TABLE test_set( + pred FLOAT8, + obs FLOAT8 + ); +INSERT INTO test_set VALUES + (37.5,53.1), (12.3,34.2), (74.2,65.4), (91.1,82.1); +</pre></li> +<li>Run the Mean Absolute Error function: <pre class="example"> +DROP TABLE IF EXISTS table_out; +SELECT madlib.mean_abs_error( 'test_set', 'table_out', 'pred', 'obs'); +SELECT * FROM table_out; +</pre> Result <pre class="result"> + mean_abs_error + ---------------- + 13.825 +</pre></li> +<li>Run the Mean Absolute Percentage Error function: <pre class="example"> +DROP TABLE IF EXISTS table_out; +SELECT madlib.mean_abs_perc_error( 'test_set', 'table_out', 'pred', 'obs'); +SELECT * FROM table_out; +</pre> Result <pre class="result"> + mean_abs_perc_error + --------------------- + 0.294578793636013 +</pre></li> +<li>Run the Mean Percentage Error function: <pre class="example"> +DROP TABLE IF EXISTS table_out; +SELECT madlib.mean_perc_error( 'test_set', 'table_out', 'pred', 'obs'); +SELECT * FROM table_out; +</pre> Result <pre class="result"> + mean_perc_error + ------------------- + -0.17248930032771 +</pre></li> +<li>Run the Mean Squared Error function: <pre class="example"> +DROP TABLE IF EXISTS table_out; +SELECT madlib.mean_squared_error( 'test_set', 'table_out', 'pred', 'obs'); +SELECT * FROM table_out; +</pre> Result <pre class="result"> + mean_squared_error + -------------------- + 220.3525 +</pre></li> +<li>Run the R2 Score function: <pre class="example"> +DROP TABLE IF EXISTS table_out; +SELECT madlib.r2_score( 'test_set', 'table_out', 'pred', 'obs'); +SELECT * FROM table_out; +</pre> Result <pre class="result"> + r2_score + ------------------------ + 0.27992908844337695865 +</pre></li> +<li>Run the Adjusted R2 Score function: <pre class="example"> +DROP TABLE IF EXISTS table_out; +SELECT madlib.adjusted_r2_score( 'test_set', 'table_out', 'pred', 'obs', 3, 100); +SELECT * FROM table_out; +</pre> Result <pre class="result"> + r2_score | adjusted_r2_score + --------------------+------------------ + 0.279929088443375 | 0.257426872457231 +</pre></li> +<li>Create the sample data for binary classifier metrics: <pre class="example"> +DROP TABLE IF EXISTS test_set; +CREATE TABLE test_set AS + SELECT ((a*8)::integer)/8.0 pred, + ((a*0.5+random()*0.5)>0.5) obs + FROM (select random() as a from generate_series(1,100)) x; +</pre></li> +<li>Run the Binary Classifier metrics function: <pre class="example"> +DROP TABLE IF EXISTS table_out; +SELECT madlib.binary_classifier( 'test_set', 'table_out', 'pred', 'obs'); +</pre></li> +<li>View the True Positive Rate and the False Positive Rate: <pre class="example"> +SELECT threshold, tpr, fpr FROM table_out ORDER BY threshold; +</pre> Result (your results for this and other functions below will look different due to the presence of the random function in sample data generator): <pre class="result"> + threshold | tpr | fpr +------------------------+------------------------+------------------------ + 0.00000000000000000000 | 1.00000000000000000000 | 1.00000000000000000000 + 0.12500000000000000000 | 1.00000000000000000000 | 0.94915254237288135593 + 0.25000000000000000000 | 0.92682926829268292683 | 0.64406779661016949153 + 0.37500000000000000000 | 0.80487804878048780488 | 0.47457627118644067797 + 0.50000000000000000000 | 0.70731707317073170732 | 0.35593220338983050847 + 0.62500000000000000000 | 0.63414634146341463415 | 0.25423728813559322034 + 0.75000000000000000000 | 0.48780487804878048780 | 0.06779661016949152542 + 0.87500000000000000000 | 0.29268292682926829268 | 0.03389830508474576271 + 1.00000000000000000000 | 0.12195121951219512195 | 0.00000000000000000000 +</pre></li> +<li>View all metrics at a given threshold value: <pre class="example"> +-- Set extended display on for easier reading of output +\x on +SELECT * FROM table_out WHERE threshold=0.5; +</pre> Result <pre class="result"> +-[ RECORD 1 ]--------------------- +threshold | 0.50000000000000000000 +tp | 29 +fp | 21 +fn | 12 +tn | 38 +tpr | 0.70731707317073170732 +tnr | 0.64406779661016949153 +ppv | 0.58000000000000000000 +npv | 0.76000000000000000000 +fpr | 0.35593220338983050847 +fdr | 0.42000000000000000000 +fnr | 0.29268292682926829268 +acc | 0.67000000000000000000 +f1 | 0.63736263736263736264 +</pre></li> +<li>Run the Area Under ROC curve function: <pre class="example"> +DROP TABLE IF EXISTS table_out; +SELECT madlib.area_under_roc( 'test_set', 'table_out', 'pred', 'obs'); +SELECT * FROM table_out; +</pre> Result <pre class="result"> + area_under_roc + --------------------------------------------- +0.77428689541132699462698842496899545266640 +</pre></li> +<li>Create the sample data for confusion matrix. <pre class="example"> +DROP TABLE IF EXISTS test_set; +CREATE TABLE test_set AS + SELECT (x+y)%5+1 AS pred, + (x*y)%5 AS obs + FROM generate_series(1,5) x, + generate_series(1,5) y; +</pre></li> +<li>Run the confusion matrix function: <pre class="example"> +DROP TABLE IF EXISTS table_out; +SELECT madlib.confusion_matrix( 'test_set', 'table_out', 'pred', 'obs'); +SELECT * FROM table_out ORDER BY class; +</pre> Result <pre class="result"> + class | confusion_arr +-------+--------------- + 0 | {0,1,2,2,2,2} + 1 | {0,2,0,1,1,0} + 2 | {0,0,0,2,2,0} + 3 | {0,0,2,0,0,2} + 4 | {0,2,1,0,0,1} + 5 | {0,0,0,0,0,0} +</pre></li> +</ol> +<p><a class="anchor" id="literature"></a></p><dl class="section user"><dt>Literature</dt><dd></dd></dl> +<p><a class="anchor" id="r2"></a> [1] <a href="https://en.wikipedia.org/wiki/Coefficient_of_determination";>https://en.wikipedia.org/wiki/Coefficient_of_determination</a></p> +<p><a class="anchor" id="aoc"></a> [2] <a href="https://en.wikipedia.org/wiki/Receiver_operating_characteristic";>https://en.wikipedia.org/wiki/Receiver_operating_characteristic</a></p> +<p><a class="anchor" id="cm"></a> [3] <a href="https://en.wikipedia.org/wiki/Confusion_matrix";>https://en.wikipedia.org/wiki/Confusion_matrix</a></p> +<p><a class="anchor" id="related"></a></p><dl class="section user"><dt>Related Topics</dt><dd></dd></dl> +<p>File <a class="el" href="pred__metrics_8sql__in.html" title="A collection of summary statistics to gauge model accuracy based on predicted values vs...">pred_metrics.sql_in</a> for list of functions and usage. </p> +</div><!-- contents --> +</div><!-- doc-content --> +<!-- start footer part --> +<div id="nav-path" class="navpath"><!-- id is needed for treeview function! --> + <ul> + <li class="footer">Generated on Tue Sep 20 2016 11:27:01 for MADlib by + <a href="http://www.doxygen.org/index.html";> + <img class="footer" src="doxygen.png" alt="doxygen"/></a> 1.8.10 </li> + </ul> +</div> +</body> +</html> http://git-wip-us.apache.org/repos/asf/incubator-madlib-site/blob/bed9253d/docs/latest/group__grp__prob.html ---------------------------------------------------------------------- diff --git a/docs/latest/group__grp__prob.html b/docs/latest/group__grp__prob.html index 0a396b5..ae45d02 100644 --- a/docs/latest/group__grp__prob.html +++ b/docs/latest/group__grp__prob.html @@ -47,7 +47,7 @@ <td id="projectlogo"><a href="http://madlib.net";><img alt="Logo" src="madlib.png" height="50" style="padding-left:0.5em;" border="0"/ ></a></td> <td style="padding-left: 0.5em;"> <div id="projectname"> - <span id="projectnumber">1.9</span> + <span id="projectnumber">1.9.1</span> </div> <div id="projectbrief">User Documentation for MADlib</div> </td> @@ -122,9 +122,9 @@ $(document).ready(function(){initNavTree('group__grp__prob.html','');}); </div><p>The Probability Functions module provides cumulative distribution, density/mass, and quantile functions for a wide range of probability distributions.</p> <p>Unless otherwise documented, all of these functions are wrappers around functionality provided by the boost C++ library [1, â<a href="http://www.boost.org/doc/libs/1_49_0/libs/math/doc/sf_and_dist/html/math_toolkit/dist.html";>Statistical Distributions and Functions</a>â].</p> <p>For convenience, all cumulative distribution and density/mass functions (CDFs and PDF/PMFs in short) are defined over the range of all floating-point numbers including infinity. Inputs that are <code>NULL</code> or <code>NaN</code> (not a number) will always produce a <code>NULL</code> or <code>NaN</code> result, respectively. Inputs that are plus or minus infinity will return the respective limits.</p> -<p>A quantile function for a probability distrution with CDF <img class="formulaInl" alt="$ F $" src="form_112.png"/> takes a probability argument <img class="formulaInl" alt="$ p \in [0,1] $" src="form_240.png"/> and returns the value <img class="formulaInl" alt="$ x $" src="form_178.png"/> so that <img class="formulaInl" alt="$ F(x) = p $" src="form_241.png"/>, provided such an <img class="formulaInl" alt="$ x $" src="form_178.png"/> exists and it is unique. If it does not, the result will be <img class="formulaInl" alt="$ \sup \{ x \in D \mid F(x) \leq p \} $" src="form_242.png"/> (interpreted as 0 if the supremum is over an empty set) if <img class="formulaInl" alt="$ p < 0.5 $" src="form_243.png"/>, and <img class="formulaInl" alt="$ \inf \{ x \in D \mid F(x) \geq p \} $" src="form_244.png"/> if <img class="formulaInl" alt="$ p \geq 0.5 $" src="form_245.png"/>. Here <img class="formulaInl" alt="$ D $" src="form_246.png"/> denotes the domain of the distribution, which is the set of reals <img class="formulaInl" alt="$ \mathbb R $" src="form_247.png"/> for continuous and the set of nonnegative integers <img class="formulaInl" alt="$ \mathbb N_0 $" src="form_248.png"/> for discrete distributions.</p> -<p>Intuitively, the formulas in the previous paragraph deal with the following special cases. The 0-quantile will always be the âleft endâ of the support, and the 1-quantile will be the âright endâ of the support of the distribution. For discrete distributions, most values of <img class="formulaInl" alt="$ p \in [0,1] $" src="form_240.png"/> do not admit an <img class="formulaInl" alt="$ x $" src="form_178.png"/> with <img class="formulaInl" alt="$ F(x) = p $" src="form_241.png"/>. Instead, there is an <img class="formulaInl" alt="$ x \in \mathbb N_0 $" src="form_249.png"/> so that <img class="formulaInl" alt="$ F(x) < p < F(x + 1) $" src="form_250.png"/>. The above formulas mean that the value returned as <img class="formulaInl" alt="$ p $" src="form_110.png"/>-quantile is <img class="formulaInl" alt="$ x $" src="form_178.png"/> if <img class="formulaInl" alt="$ p < 0.5 $" src="form_243.png"/>, and it is <img class="formulaInl" alt="$ x + 1 $" src="form_251.png"/> if <img c lass="formulaInl" alt="$ p \geq 0.5 $" src="form_245.png"/>. (As a special case, in order to ensure that quantiles are always within the support, the <img class="formulaInl" alt="$ p $" src="form_110.png"/>-quantile will be 0 if <img class="formulaInl" alt="$ p < F(0) $" src="form_252.png"/>).</p> -<p>The rationale for choosing this behavior is that <img class="formulaInl" alt="$p$" src="form_253.png"/>-quantiles for <img class="formulaInl" alt="$ p < 0.5 $" src="form_243.png"/> are typically requested when interested in the value <img class="formulaInl" alt="$ x $" src="form_178.png"/> such that with confidence level <b>at least</b> <img class="formulaInl" alt="$ 1 - p $" src="form_254.png"/> a random variable will be <img class="formulaInl" alt="$ > x $" src="form_255.png"/> (or equivalently, with probability <b>at most</b> <img class="formulaInl" alt="$ p $" src="form_110.png"/>, it will be <img class="formulaInl" alt="$ \leq x $" src="form_256.png"/>). Likewise, <img class="formulaInl" alt="$p$" src="form_253.png"/>-quantiles for <img class="formulaInl" alt="$ p \geq 0.5 $" src="form_245.png"/> are typically requested when interested in the value <img class="formulaInl" alt="$ x $" src="form_178.png"/> such that with confidence level <b>at least</b> <img class="formulaInl" alt="$ p $" src="form_110.png"/> a random variable will be <img class="formulaInl" alt="$ \leq x $" src="form_256.png"/>. See also [1, â<a href="http://www.boost.org/doc/libs/1_46_1/libs/math/doc/sf_and_dist/html/math_toolkit/policy/pol_tutorial/understand_dis_quant.html";>Understanding Quantiles of Discrete Distributions</a>â].</p> +<p>A quantile function for a probability distrution with CDF <img class="formulaInl" alt="$ F $" src="form_112.png"/> takes a probability argument <img class="formulaInl" alt="$ p \in [0,1] $" src="form_241.png"/> and returns the value <img class="formulaInl" alt="$ x $" src="form_178.png"/> so that <img class="formulaInl" alt="$ F(x) = p $" src="form_242.png"/>, provided such an <img class="formulaInl" alt="$ x $" src="form_178.png"/> exists and it is unique. If it does not, the result will be <img class="formulaInl" alt="$ \sup \{ x \in D \mid F(x) \leq p \} $" src="form_243.png"/> (interpreted as 0 if the supremum is over an empty set) if <img class="formulaInl" alt="$ p < 0.5 $" src="form_244.png"/>, and <img class="formulaInl" alt="$ \inf \{ x \in D \mid F(x) \geq p \} $" src="form_245.png"/> if <img class="formulaInl" alt="$ p \geq 0.5 $" src="form_246.png"/>. Here <img class="formulaInl" alt="$ D $" src="form_247.png"/> denotes the domain of the distribution, which is the set of reals <img class="formulaInl" alt="$ \mathbb R $" src="form_248.png"/> for continuous and the set of nonnegative integers <img class="formulaInl" alt="$ \mathbb N_0 $" src="form_249.png"/> for discrete distributions.</p> +<p>Intuitively, the formulas in the previous paragraph deal with the following special cases. The 0-quantile will always be the âleft endâ of the support, and the 1-quantile will be the âright endâ of the support of the distribution. For discrete distributions, most values of <img class="formulaInl" alt="$ p \in [0,1] $" src="form_241.png"/> do not admit an <img class="formulaInl" alt="$ x $" src="form_178.png"/> with <img class="formulaInl" alt="$ F(x) = p $" src="form_242.png"/>. Instead, there is an <img class="formulaInl" alt="$ x \in \mathbb N_0 $" src="form_250.png"/> so that <img class="formulaInl" alt="$ F(x) < p < F(x + 1) $" src="form_251.png"/>. The above formulas mean that the value returned as <img class="formulaInl" alt="$ p $" src="form_110.png"/>-quantile is <img class="formulaInl" alt="$ x $" src="form_178.png"/> if <img class="formulaInl" alt="$ p < 0.5 $" src="form_244.png"/>, and it is <img class="formulaInl" alt="$ x + 1 $" src="form_252.png"/> if <img c lass="formulaInl" alt="$ p \geq 0.5 $" src="form_246.png"/>. (As a special case, in order to ensure that quantiles are always within the support, the <img class="formulaInl" alt="$ p $" src="form_110.png"/>-quantile will be 0 if <img class="formulaInl" alt="$ p < F(0) $" src="form_253.png"/>).</p> +<p>The rationale for choosing this behavior is that <img class="formulaInl" alt="$p$" src="form_254.png"/>-quantiles for <img class="formulaInl" alt="$ p < 0.5 $" src="form_244.png"/> are typically requested when interested in the value <img class="formulaInl" alt="$ x $" src="form_178.png"/> such that with confidence level <b>at least</b> <img class="formulaInl" alt="$ 1 - p $" src="form_255.png"/> a random variable will be <img class="formulaInl" alt="$ > x $" src="form_256.png"/> (or equivalently, with probability <b>at most</b> <img class="formulaInl" alt="$ p $" src="form_110.png"/>, it will be <img class="formulaInl" alt="$ \leq x $" src="form_257.png"/>). Likewise, <img class="formulaInl" alt="$p$" src="form_254.png"/>-quantiles for <img class="formulaInl" alt="$ p \geq 0.5 $" src="form_246.png"/> are typically requested when interested in the value <img class="formulaInl" alt="$ x $" src="form_178.png"/> such that with confidence level <b>at least</b> <img class="formulaInl" alt="$ p $" src="form_110.png"/> a random variable will be <img class="formulaInl" alt="$ \leq x $" src="form_257.png"/>. See also [1, â<a href="http://www.boost.org/doc/libs/1_46_1/libs/math/doc/sf_and_dist/html/math_toolkit/policy/pol_tutorial/understand_dis_quant.html";>Understanding Quantiles of Discrete Distributions</a>â].</p> <p><a class="anchor" id="syntax"></a></p><dl class="section user"><dt>Function Syntax</dt><dd></dd></dl> <p>Cumulative distribution functions:</p> <pre class="syntax"><em>distribution</em>_cdf(<em>random variate</em>[, <em>parameter1</em> [, <em>parameter2</em> [, <em>parameter3</em>] ] ])</pre><p>Probability density/mass functions: </p><pre class="syntax"><em>distribution</em>_{pdf|pmf}(<em>random variate</em>[, <em>parameter1</em> [, <em>parameter2</em> [, <em>parameter3</em>] ] ])</pre><p>Quantile functions: </p><pre class="syntax"><em>distribution</em>_quantile(<em>probability</em>[, <em>parameter1</em> [, <em>parameter2</em> [, <em>parameter3</em>] ] ])</pre><p>For concrete function signatures, see <a class="el" href="prob_8sql__in.html">prob.sql_in</a>.</p> @@ -150,7 +150,7 @@ SELECT madlib.normal_quantile(0.5, 0, 1); <!-- start footer part --> <div id="nav-path" class="navpath"><!-- id is needed for treeview function! --> <ul> - <li class="footer">Generated on Thu Apr 7 2016 14:24:10 for MADlib by + <li class="footer">Generated on Tue Sep 20 2016 11:27:01 for MADlib by <a href="http://www.doxygen.org/index.html";> <img class="footer" src="doxygen.png" alt="doxygen"/></a> 1.8.10 </li> </ul> http://git-wip-us.apache.org/repos/asf/incubator-madlib-site/blob/bed9253d/docs/latest/group__grp__profile.html ---------------------------------------------------------------------- diff --git a/docs/latest/group__grp__profile.html b/docs/latest/group__grp__profile.html index ce8d6b4..7c91180 100644 --- a/docs/latest/group__grp__profile.html +++ b/docs/latest/group__grp__profile.html @@ -47,7 +47,7 @@ <td id="projectlogo"><a href="http://madlib.net";><img alt="Logo" src="madlib.png" height="50" style="padding-left:0.5em;" border="0"/ ></a></td> <td style="padding-left: 0.5em;"> <div id="projectname"> - <span id="projectnumber">1.9</span> + <span id="projectnumber">1.9dev</span> </div> <div id="projectbrief">User Documentation for MADlib</div> </td> @@ -198,7 +198,7 @@ SELECT * FROM profile_full( 'pg_catalog.pg_tables', <!-- start footer part --> <div id="nav-path" class="navpath"><!-- id is needed for treeview function! --> <ul> - <li class="footer">Generated on Thu Apr 7 2016 14:24:11 for MADlib by + <li class="footer">Generated on Tue Jun 7 2016 09:40:56 for MADlib by <a href="http://www.doxygen.org/index.html";> <img class="footer" src="doxygen.png" alt="doxygen"/></a> 1.8.10 </li> </ul> http://git-wip-us.apache.org/repos/asf/incubator-madlib-site/blob/bed9253d/docs/latest/group__grp__quantile.html ---------------------------------------------------------------------- diff --git a/docs/latest/group__grp__quantile.html b/docs/latest/group__grp__quantile.html index b03557f..2729396 100644 --- a/docs/latest/group__grp__quantile.html +++ b/docs/latest/group__grp__quantile.html @@ -47,7 +47,7 @@ <td id="projectlogo"><a href="http://madlib.net";><img alt="Logo" src="madlib.png" height="50" style="padding-left:0.5em;" border="0"/ ></a></td> <td style="padding-left: 0.5em;"> <div id="projectname"> - <span id="projectnumber">1.9</span> + <span id="projectnumber">1.9dev</span> </div> <div id="projectbrief">User Documentation for MADlib</div> </td> @@ -159,7 +159,7 @@ SELECT quantile( 'tab1', <!-- start footer part --> <div id="nav-path" class="navpath"><!-- id is needed for treeview function! --> <ul> - <li class="footer">Generated on Thu Apr 7 2016 14:24:11 for MADlib by + <li class="footer">Generated on Tue Jun 7 2016 09:40:56 for MADlib by <a href="http://www.doxygen.org/index.html";> <img class="footer" src="doxygen.png" alt="doxygen"/></a> 1.8.10 </li> </ul> http://git-wip-us.apache.org/repos/asf/incubator-madlib-site/blob/bed9253d/docs/latest/group__grp__random__forest.html ---------------------------------------------------------------------- diff --git a/docs/latest/group__grp__random__forest.html b/docs/latest/group__grp__random__forest.html index 6269ba0..076dcbc 100644 --- a/docs/latest/group__grp__random__forest.html +++ b/docs/latest/group__grp__random__forest.html @@ -47,7 +47,7 @@ <td id="projectlogo"><a href="http://madlib.net";><img alt="Logo" src="madlib.png" height="50" style="padding-left:0.5em;" border="0"/ ></a></td> <td style="padding-left: 0.5em;"> <div id="projectname"> - <span id="projectnumber">1.9</span> + <span id="projectnumber">1.9.1</span> </div> <div id="projectbrief">User Documentation for MADlib</div> </td> @@ -360,7 +360,9 @@ forest_train(training_table_name, <p class="enddd"></p> </dd> <dt>sample_ratio (optional) </dt> -<dd>double precision, in the range of (0, 1], default: 1. If sample_ratio is less than 1, a bootstrap sample size smaller than the data table is expected to be used for training each tree in the forest. A ratio that is close to 0 may result in trees with only the root node. This allows users to experiment with the function in a speedy fashion. </dd> +<dd><p class="startdd">double precision, in the range of (0, 1], default: 1. If sample_ratio is less than 1, a bootstrap sample size smaller than the data table is expected to be used for training each tree in the forest. A ratio that is close to 0 may result in trees with only the root node. This allows users to experiment with the function in a speedy fashion.</p> +<dl class="section note"><dt>Note</dt><dd>The main parameters that affect memory usage are: depth of tree, number of features, and number of values per feature. If you are hitting VMEM limits, consider reducing one or more of these parameters.</dd></dl> +</dd> </dl> <p><a class="anchor" id="predict"></a></p><dl class="section user"><dt>Prediction Function</dt><dd>The prediction function is provided to estimate the conditional mean given a new predictor. It has the following syntax: <pre class="syntax"> forest_predict(random_forest_model, @@ -701,7 +703,7 @@ ORDER BY am, id; <!-- start footer part --> <div id="nav-path" class="navpath"><!-- id is needed for treeview function! --> <ul> - <li class="footer">Generated on Thu Apr 7 2016 14:24:10 for MADlib by + <li class="footer">Generated on Tue Sep 20 2016 11:27:01 for MADlib by <a href="http://www.doxygen.org/index.html";> <img class="footer" src="doxygen.png" alt="doxygen"/></a> 1.8.10 </li> </ul> http://git-wip-us.apache.org/repos/asf/incubator-madlib-site/blob/bed9253d/docs/latest/group__grp__regml.html ---------------------------------------------------------------------- diff --git a/docs/latest/group__grp__regml.html b/docs/latest/group__grp__regml.html index 5d00bde..3f83ff5 100644 --- a/docs/latest/group__grp__regml.html +++ b/docs/latest/group__grp__regml.html @@ -47,7 +47,7 @@ <td id="projectlogo"><a href="http://madlib.net";><img alt="Logo" src="madlib.png" height="50" style="padding-left:0.5em;" border="0"/ ></a></td> <td style="padding-left: 0.5em;"> <div id="projectname"> - <span id="projectnumber">1.9</span> + <span id="projectnumber">1.9.1</span> </div> <div id="projectbrief">User Documentation for MADlib</div> </td> @@ -152,7 +152,7 @@ Modules</h2></td></tr> <!-- start footer part --> <div id="nav-path" class="navpath"><!-- id is needed for treeview function! --> <ul> - <li class="footer">Generated on Thu Apr 7 2016 14:24:10 for MADlib by + <li class="footer">Generated on Tue Sep 20 2016 11:27:01 for MADlib by <a href="http://www.doxygen.org/index.html";> <img class="footer" src="doxygen.png" alt="doxygen"/></a> 1.8.10 </li> </ul> http://git-wip-us.apache.org/repos/asf/incubator-madlib-site/blob/bed9253d/docs/latest/group__grp__rf.html ---------------------------------------------------------------------- diff --git a/docs/latest/group__grp__rf.html b/docs/latest/group__grp__rf.html index 5a5991d..6263003 100644 --- a/docs/latest/group__grp__rf.html +++ b/docs/latest/group__grp__rf.html @@ -47,7 +47,7 @@ <td id="projectlogo"><a href="http://madlib.net";><img alt="Logo" src="madlib.png" height="50" style="padding-left:0.5em;" border="0"/ ></a></td> <td style="padding-left: 0.5em;"> <div id="projectname"> - <span id="projectnumber">1.9</span> + <span id="projectnumber">1.9dev</span> </div> <div id="projectbrief">User Documentation for MADlib</div> </td> @@ -114,7 +114,7 @@ $(document).ready(function(){initNavTree('group__grp__rf.html','');}); <!-- start footer part --> <div id="nav-path" class="navpath"><!-- id is needed for treeview function! --> <ul> - <li class="footer">Generated on Thu Apr 7 2016 14:24:11 for MADlib by + <li class="footer">Generated on Tue Jun 7 2016 09:40:56 for MADlib by <a href="http://www.doxygen.org/index.html";> <img class="footer" src="doxygen.png" alt="doxygen"/></a> 1.8.10 </li> </ul> http://git-wip-us.apache.org/repos/asf/incubator-madlib-site/blob/bed9253d/docs/latest/group__grp__robust.html ---------------------------------------------------------------------- diff --git a/docs/latest/group__grp__robust.html b/docs/latest/group__grp__robust.html index abe5c06..eb45a08 100644 --- a/docs/latest/group__grp__robust.html +++ b/docs/latest/group__grp__robust.html @@ -47,7 +47,7 @@ <td id="projectlogo"><a href="http://madlib.net";><img alt="Logo" src="madlib.png" height="50" style="padding-left:0.5em;" border="0"/ ></a></td> <td style="padding-left: 0.5em;"> <div id="projectname"> - <span id="projectnumber">1.9</span> + <span id="projectnumber">1.9.1</span> </div> <div id="projectbrief">User Documentation for MADlib</div> </td> @@ -403,18 +403,18 @@ hessian | {{2.78043065745405,-2.25848560642669},{-2.25848560642669,8.50472 <p><a class="anchor" id="background"></a></p><dl class="section user"><dt>Technical Background</dt><dd></dd></dl> <p>When doing regression analysis, we are sometimes interested in the variance of the computed coefficients <img class="formulaInl" alt="$ \boldsymbol c $" src="form_78.png"/>. While the built-in regression functions provide variance estimates, we may prefer a <em>robust</em> variance estimate.</p> <p>The robust variance calculation can be expressed in a sandwich formation, which is the form </p><p class="formulaDsp"> -<img class="formulaDsp" alt="\[ S( \boldsymbol c) = B( \boldsymbol c) M( \boldsymbol c) B( \boldsymbol c) \]" src="form_372.png"/> +<img class="formulaDsp" alt="\[ S( \boldsymbol c) = B( \boldsymbol c) M( \boldsymbol c) B( \boldsymbol c) \]" src="form_373.png"/> </p> -<p> where <img class="formulaInl" alt="$ B( \boldsymbol c)$" src="form_373.png"/> and <img class="formulaInl" alt="$ M( \boldsymbol c)$" src="form_374.png"/> are matrices. The <img class="formulaInl" alt="$ B( \boldsymbol c) $" src="form_375.png"/> matrix, also known as the bread, is relatively straight forward, and can be computed as </p><p class="formulaDsp"> -<img class="formulaDsp" alt="\[ B( \boldsymbol c) = n\left(\sum_i^n -H(y_i, x_i, \boldsymbol c) \right)^{-1} \]" src="form_376.png"/> +<p> where <img class="formulaInl" alt="$ B( \boldsymbol c)$" src="form_374.png"/> and <img class="formulaInl" alt="$ M( \boldsymbol c)$" src="form_375.png"/> are matrices. The <img class="formulaInl" alt="$ B( \boldsymbol c) $" src="form_376.png"/> matrix, also known as the bread, is relatively straight forward, and can be computed as </p><p class="formulaDsp"> +<img class="formulaDsp" alt="\[ B( \boldsymbol c) = n\left(\sum_i^n -H(y_i, x_i, \boldsymbol c) \right)^{-1} \]" src="form_377.png"/> </p> <p> where <img class="formulaInl" alt="$ H $" src="form_107.png"/> is the hessian matrix.</p> -<p>The <img class="formulaInl" alt="$ M( \boldsymbol c)$" src="form_374.png"/> matrix has several variations, each with different robustness properties. The form implemented here is the Huber-White sandwich operator, which takes the form </p><p class="formulaDsp"> -<img class="formulaDsp" alt="\[ M_{H} =\frac{1}{n} \sum_i^n \psi(y_i,x_i, \boldsymbol c)^T \psi(y_i,x_i, \boldsymbol c). \]" src="form_377.png"/> +<p>The <img class="formulaInl" alt="$ M( \boldsymbol c)$" src="form_375.png"/> matrix has several variations, each with different robustness properties. The form implemented here is the Huber-White sandwich operator, which takes the form </p><p class="formulaDsp"> +<img class="formulaDsp" alt="\[ M_{H} =\frac{1}{n} \sum_i^n \psi(y_i,x_i, \boldsymbol c)^T \psi(y_i,x_i, \boldsymbol c). \]" src="form_378.png"/> </p> <p>The above method for calculating robust variance (Huber-White estimates) is implemented for linear regression, logistic regression, and multinomial logistic regression. It is useful in calculating variances in a dataset with potentially noisy outliers. The Huber-White implemented here is identical to the "HC0" sandwich operator in the R module "sandwich".</p> -<p>When multinomial logistic regression is computed before the multinomial robust regression, it uses a default reference category of zero and the regression coefficients are included in the output table. The regression coefficients in the output are in the same order as the multinomial logistic regression function, which is described below. For a problem with <img class="formulaInl" alt="$ K $" src="form_117.png"/> dependent variables <img class="formulaInl" alt="$ (1, ..., K) $" src="form_118.png"/> and <img class="formulaInl" alt="$ J $" src="form_119.png"/> categories <img class="formulaInl" alt="$ (0, ..., J-1) $" src="form_120.png"/>, let <img class="formulaInl" alt="$ {m_{k,j}} $" src="form_121.png"/> denote the coefficient for dependent variable <img class="formulaInl" alt="$ k $" src="form_97.png"/> and category <img class="formulaInl" alt="$ j $" src="form_122.png"/> . The output is <img class="formulaInl" alt="$ {m_{k_1, j_0}, m_{k_1, j_1} \ldots m_{k_1, j_{J-1}}, m_{k_2, j_0}, m_{k_2, j_1} \ldots m_{k_K, j_{J-1}}} $" src="form_322.png"/>. The order is NOT CONSISTENT with the multinomial regression marginal effect calculation with function <em>marginal_mlogregr</em>. This is deliberate because the interfaces of all multinomial regressions (robust, clustered, ...) will be moved to match that used in marginal.</p> -<p>The robust variance of Cox proportional hazards is more complex because coeeficients are trained by maximizing a partial log-likelihood. Therefore, one cannot directly use the formula for <img class="formulaInl" alt="$ M( \boldsymbol c) $" src="form_378.png"/> as in Huber-White robust estimator. Extra terms are needed. See [4] for details.</p> +<p>When multinomial logistic regression is computed before the multinomial robust regression, it uses a default reference category of zero and the regression coefficients are included in the output table. The regression coefficients in the output are in the same order as the multinomial logistic regression function, which is described below. For a problem with <img class="formulaInl" alt="$ K $" src="form_117.png"/> dependent variables <img class="formulaInl" alt="$ (1, ..., K) $" src="form_118.png"/> and <img class="formulaInl" alt="$ J $" src="form_119.png"/> categories <img class="formulaInl" alt="$ (0, ..., J-1) $" src="form_120.png"/>, let <img class="formulaInl" alt="$ {m_{k,j}} $" src="form_121.png"/> denote the coefficient for dependent variable <img class="formulaInl" alt="$ k $" src="form_97.png"/> and category <img class="formulaInl" alt="$ j $" src="form_122.png"/> . The output is <img class="formulaInl" alt="$ {m_{k_1, j_0}, m_{k_1, j_1} \ldots m_{k_1, j_{J-1}}, m_{k_2, j_0}, m_{k_2, j_1} \ldots m_{k_K, j_{J-1}}} $" src="form_323.png"/>. The order is NOT CONSISTENT with the multinomial regression marginal effect calculation with function <em>marginal_mlogregr</em>. This is deliberate because the interfaces of all multinomial regressions (robust, clustered, ...) will be moved to match that used in marginal.</p> +<p>The robust variance of Cox proportional hazards is more complex because coeeficients are trained by maximizing a partial log-likelihood. Therefore, one cannot directly use the formula for <img class="formulaInl" alt="$ M( \boldsymbol c) $" src="form_379.png"/> as in Huber-White robust estimator. Extra terms are needed. See [4] for details.</p> <p><a class="anchor" id="literature"></a></p><dl class="section user"><dt>Literature</dt><dd></dd></dl> <p>[1] vce(cluster) function in STATA: <a href="http://www.stata.com/help.cgi?vce_option";>http://www.stata.com/help.cgi?vce_option</a></p> <p>[2] clustered estimators in R: <a href="http://people.su.se/~ma/clustering.pdf";>http://people.su.se/~ma/clustering.pdf</a></p> @@ -426,7 +426,7 @@ hessian | {{2.78043065745405,-2.25848560642669},{-2.25848560642669,8.50472 <!-- start footer part --> <div id="nav-path" class="navpath"><!-- id is needed for treeview function! --> <ul> - <li class="footer">Generated on Thu Apr 7 2016 14:24:10 for MADlib by + <li class="footer">Generated on Tue Sep 20 2016 11:27:01 for MADlib by <a href="http://www.doxygen.org/index.html";> <img class="footer" src="doxygen.png" alt="doxygen"/></a> 1.8.10 </li> </ul> http://git-wip-us.apache.org/repos/asf/incubator-madlib-site/blob/bed9253d/docs/latest/group__grp__sample.html ---------------------------------------------------------------------- diff --git a/docs/latest/group__grp__sample.html b/docs/latest/group__grp__sample.html index a05a6cf..3c3a649 100644 --- a/docs/latest/group__grp__sample.html +++ b/docs/latest/group__grp__sample.html @@ -47,7 +47,7 @@ <td id="projectlogo"><a href="http://madlib.net";><img alt="Logo" src="madlib.png" height="50" style="padding-left:0.5em;" border="0"/ ></a></td> <td style="padding-left: 0.5em;"> <div id="projectname"> - <span id="projectnumber">1.9</span> + <span id="projectnumber">1.9.1</span> </div> <div id="projectbrief">User Documentation for MADlib</div> </td> @@ -136,7 +136,7 @@ weighted_sample( value, <!-- start footer part --> <div id="nav-path" class="navpath"><!-- id is needed for treeview function! --> <ul> - <li class="footer">Generated on Thu Apr 7 2016 14:24:11 for MADlib by + <li class="footer">Generated on Tue Sep 20 2016 11:27:01 for MADlib by <a href="http://www.doxygen.org/index.html";> <img class="footer" src="doxygen.png" alt="doxygen"/></a> 1.8.10 </li> </ul>
