| <!-- 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: Summary</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__summary.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">Summary<div class="ingroups"><a class="el" href="group__grp__stats.html">Statistics</a> » <a class="el" href="group__grp__desc__stats.html">Descriptive Statistics</a></div></div> </div> |
| </div><!--header--> |
| <div class="contents"> |
| <div class="toc"><b>Contents</b> </p><ul> |
| <li> |
| <a href="#usage">Summary Function Syntax</a> </li> |
| <li> |
| <a href="#examples">Examples</a> </li> |
| <li> |
| <a href="#notes">Notes</a> </li> |
| <li> |
| <a href="#related">Related Topics</a> </li> |
| </ul> |
| </div><p>The MADlib <b><a class="el" href="summary_8sql__in.html#a14ff66aaa0248c5e2741dfaf2e360881">summary()</a></b> function produces summary statistics for any data table. The function invokes various methods from the MADlib library to provide the data overview.</p> |
| <p><a class="anchor" id="usage"></a></p><dl class="section user"><dt>Summary Function Syntax</dt><dd>The <b><a class="el" href="summary_8sql__in.html#a14ff66aaa0248c5e2741dfaf2e360881">summary()</a></b> function has the following syntax:</dd></dl> |
| <pre class="syntax"> |
| summary ( source_table, |
| output_table, |
| target_cols, |
| grouping_cols, |
| get_distinct, |
| get_quartiles, |
| ntile_array, |
| how_many_mfv, |
| get_estimates |
| ) |
| </pre><p> The <b><a class="el" href="summary_8sql__in.html#a14ff66aaa0248c5e2741dfaf2e360881">summary()</a></b> function returns a composite type containing three fields: </p><table class="output"> |
| <tr> |
| <th>output_table </th><td>TEXT. The name of the output table. </td></tr> |
| <tr> |
| <th>row_count </th><td>INTEGER. The number of rows in the output table. </td></tr> |
| <tr> |
| <th>duration </th><td>FLOAT8. The time taken (in seconds) to compute the summary. </td></tr> |
| </table> |
| <p><b>Arguments</b> </p><dl class="arglist"> |
| <dt>source_table </dt> |
| <dd>TEXT. The name of the table containing the input data. </dd> |
| <dt>output_table </dt> |
| <dd><p class="startdd">TEXT. The name of the table to contain the output summary data.</p> |
| <p class="enddd">Summary statistics are saved in a table with the name specifed in the <em>output_table</em> argument. The table contains the following columns: </p><table class="output"> |
| <tr> |
| <th>group_by </th><td>Group-by column name. NULL if none provided. </td></tr> |
| <tr> |
| <th>group_by_value </th><td>Value of the Group-by column. NULL if there is no grouping. </td></tr> |
| <tr> |
| <th>target_column </th><td>Targeted column values for which summary is requested. </td></tr> |
| <tr> |
| <th>column_number </th><td>Physical column number for the target column, as described in <em>pg_attribute</em> catalog. </td></tr> |
| <tr> |
| <th>data_type </th><td>Data type of the target column. Standard GPDB type descriptors are displayed. </td></tr> |
| <tr> |
| <th>row_count </th><td>Number of rows for the target column. </td></tr> |
| <tr> |
| <th>distinct_values </th><td>Number of distinct values in the target column. When the <a class="el" href="summary_8sql__in.html#a14ff66aaa0248c5e2741dfaf2e360881">summary()</a> function is called with the <em>get_estimates</em> argument set to TRUE, this is an estimated statistic based on the Flajolet-Martin distinct count estimator. </td></tr> |
| <tr> |
| <th>missing_values </th><td>Number of missing values in the target column. </td></tr> |
| <tr> |
| <th>blank_values </th><td>Number of blank values. Blanks are defined by this regular expression:<pre class="fragment">'^\w*$'</pre> </td></tr> |
| <tr> |
| <th>fraction_missing </th><td>Percentage of total rows that are missing, as a decimal value, e.g. 0.3. </td></tr> |
| <tr> |
| <th>fraction_blank </th><td>Percentage of total rows that are blank, as a decimal value, e.g. 0.3. </td></tr> |
| <tr> |
| <th>mean </th><td>Mean value of target column if target is numeric, otherwise NULL. </td></tr> |
| <tr> |
| <th>variance </th><td>Variance of target column if target is numeric, otherwise NULL. </td></tr> |
| <tr> |
| <th>min </th><td>Minimum value of target column. For strings this is the length of the shortest string. </td></tr> |
| <tr> |
| <th>max </th><td>Maximum value of target column. For strings this is the length of the longest string. </td></tr> |
| <tr> |
| <th>first_quartile </th><td>First quartile (25th percentile), only for numeric columns. <b>Currently unavailable for PostgreSQL 9.3 or lower</b>. </td></tr> |
| <tr> |
| <th>median </th><td>Median value of target column, if target is numeric, otherwise NULL. <b>Currently unavailable for PostgreSQL 9.3 or lower</b>. </td></tr> |
| <tr> |
| <th>third_quartile </th><td>Third quartile (25th percentile), only for numeric columns. <b>Currently unavailable for PostgreSQL 9.3 or lower</b>. </td></tr> |
| <tr> |
| <th>quantile_array </th><td>Percentile values corresponding to <em>ntile_array</em>. <b>Currently unavailable for PostgreSQL 9.3 or lower</b>. </td></tr> |
| <tr> |
| <th>most_frequent_values </th><td>An array containing the most frequently occurring values. The <em>how_many_mfv</em> argument determines the length of the array, 10 by default. If the <a class="el" href="summary_8sql__in.html#a14ff66aaa0248c5e2741dfaf2e360881">summary()</a> function is called with the <em>get_estimates</em> argument set to TRUE (default), the frequent values computation is performed using a parallel aggregation method that is faster, but in some cases can fail to detect the exact most frequent values. </td></tr> |
| <tr> |
| <th>mfv_frequencies </th><td>Array containing the frequency count for each of the most frequent values. </td></tr> |
| </table> |
| </dd> |
| <dt>target_columns (optional) </dt> |
| <dd>TEXT, default NULL. A comma-separated list of columns to summarize. If NULL, summaries are produced for all columns. </dd> |
| <dt>grouping_cols (optional) </dt> |
| <dd>TEXT, default: null. A comma-separated list of columns on which to group results. If NULL, summaries are produced on the complete table. <dl class="section note"><dt>Note</dt><dd>Please note that summary statistics are calculated for each grouping column independently. That is, grouping columns are not combined together as in the regular PostgreSQL style GROUP BY directive. (This was done to reduce long run time and huge output table size which would otherwise result in the case of large input tables with a lot of grouping_cols and target_cols specified.) </dd></dl> |
| </dd> |
| <dt>get_distinct (optional) </dt> |
| <dd>BOOLEAN, default TRUE. If true, distinct values are counted. </dd> |
| <dt>get_quartiles (optional) </dt> |
| <dd>BOOLEAN, default TRUE. If TRUE, quartiles are computed. </dd> |
| <dt>ntile_array (optional) </dt> |
| <dd>FLOAT8[], default NULL. An array of quantile values to compute. If NULL, quantile values are not computed. <dl class="section note"><dt>Note</dt><dd>Quartile and quantile functions are not available for PostgreSQL 9.3 or lower. If you are using PostgreSQL 9.3 or lower, the output table will not contain these values, even if you set 'get_quartiles' = TRUE or provide an array of quantile values for the parameter 'ntile_array'. </dd></dl> |
| </dd> |
| <dt>how_many_mfv (optional) </dt> |
| <dd>INTEGER, default: 10. The number of most-frequent-values to compute. </dd> |
| <dt>get_estimates (optional) </dt> |
| <dd>BOOLEAN, default TRUE. If TRUE, estimated values are produced. If FALSE, exact values are calculated. </dd> |
| </dl> |
| <p><a class="anchor" id="examples"></a></p><dl class="section user"><dt>Examples</dt><dd></dd></dl> |
| <ol type="1"> |
| <li>View online help for the <a class="el" href="summary_8sql__in.html#a14ff66aaa0248c5e2741dfaf2e360881">summary()</a> function. <pre class="example"> |
| SELECT * FROM madlib.summary(); |
| </pre></li> |
| <li>Create an input data set. <pre class="example"> |
| CREATE TABLE houses (id INT, tax INT, bedroom INT, bath FLOAT, price INT, |
| size INT, lot INT); |
| COPY houses FROM STDIN WITH DELIMITER '|'; |
| 1 | 590 | 2 | 1 | 50000 | 770 | 22100 |
| 2 | 1050 | 3 | 2 | 85000 | 1410 | 12000 |
| 3 | 20 | 3 | 1 | 22500 | 1060 | 3500 |
| 4 | 870 | 2 | 2 | 90000 | 1300 | 17500 |
| 5 | 1320 | 3 | 2 | 133000 | 1500 | 30000 |
| 6 | 1350 | 2 | 1 | 90500 | 820 | 25700 |
| 7 | 2790 | 3 | 2.5 | 260000 | 2130 | 25000 |
| 8 | 680 | 2 | 1 | 142500 | 1170 | 22000 |
| 9 | 1840 | 3 | 2 | 160000 | 1500 | 19000 |
| 10 | 3680 | 4 | 2 | 240000 | 2790 | 20000 |
| 11 | 1660 | 3 | 1 | 87000 | 1030 | 17500 |
| 12 | 1620 | 3 | 2 | 118600 | 1250 | 20000 |
| 13 | 3100 | 3 | 2 | 140000 | 1760 | 38000 |
| 14 | 2070 | 2 | 3 | 148000 | 1550 | 14000 |
| 15 | 650 | 3 | 1.5 | 65000 | 1450 | 12000 |
| \. |
| </pre></li> |
| <li>Run the <b><a class="el" href="summary_8sql__in.html#a14ff66aaa0248c5e2741dfaf2e360881">summary()</a></b> function. <pre class="example"> |
| SELECT * FROM madlib.summary( 'houses', |
| 'houses_summary', |
| 'tax,bedroom,lot,bath,price,size,lot', |
| 'bedroom', |
| TRUE, |
| TRUE, |
| NULL, |
| 5, |
| FALSE |
| ); |
| </pre> Result: <pre class="result"> |
| output_table | row_count | duration |
| ----------------+-----------+---------------- |
| houses_summary | 21 | 0.207587003708 |
| (1 row) |
| </pre></li> |
| <li>View the summary data. <pre class="example"> |
| -- Turn on expanded display for readability. |
| \x on |
| SELECT * FROM houses_summary; |
| </pre> Result: <pre class="result"> |
|  -[ RECORD 1 ]--------+----------------------------------- |
| group_by | bedroom |
| group_by_value | 3 |
| target_column | tax |
| column_number | 2 |
| data_type | int4 |
| row_count | 9 |
| distinct_values | 9 |
| missing_values | 0 |
| blank_values | |
| fraction_missing | 0 |
| fraction_blank | |
| mean | 1561.11111111111 |
| variance | 936736.111111111 |
| min | 20 |
| max | 3100 |
| most_frequent_values | {20,1320,2790,1840,1660} |
| mfv_frequencies | {1,1,1,1,1} |
|  -[ RECORD 2 ]--------+----------------------------------- |
| group_by | bedroom |
| group_by_value | 3 |
| target_column | bath |
| column_number | 4 |
| ... |
| </pre></li> |
| </ol> |
| <p><a class="anchor" id="notes"></a></p><dl class="section user"><dt>Notes</dt><dd><ul> |
| <li>Table names can be optionally schema qualified (current_schemas() would be searched if a schema name is not provided) and table and column names should follow case-sensitivity and quoting rules per the database. (For instance, 'mytable' and 'MyTable' both resolve to the same entity, i.e. 'mytable'. If mixed-case or multi-byte characters are desired for entity names then the string should be double-quoted; in this case the input would be '"MyTable"').</li> |
| <li>Estimated values are only implemented for the distinct values computation.</li> |
| <li>The <em>get_estimates</em> parameter controls computation for two statistics:<ul> |
| <li>If <em>get_estimates</em> is TRUE then the distinct value computation is estimated. Further, the most frequent values computation is computed using a "quick and dirty" method that does parallel aggregation in Greenplum Database at the expense of missing some of the most frequent values.</li> |
| <li>If <em>get_estimates</em> is FALSE then the distinct values are computed in a slow but exact method. The most frequent values are computed using a faithful implementation that preserves the approximation guarantees of the Cormode/Muthukrishnan method (more information in <a class="el" href="group__grp__mfvsketch.html">MFV (Most Frequent Values)</a>).</li> |
| </ul> |
| </li> |
| <li>Summary statistics are calculated for each grouping column independently. That is, grouping columns are not combined together as in the regular PostgreSQL style GROUP BY directive. (This was done to reduce long run time and huge output table size which would otherwise result in the case of large input tables with a lot of grouping_cols and target_cols specified.)</li> |
| <li>Quartile and quantile functions are not available for PostgreSQL 9.3 or lower. If you are using PostgreSQL 9.3 or lower, the output table will not contain these values, even if you set 'get_quartiles' = TRUE or provide an array of quantile values for the parameter 'ntile_array'.</li> |
| </ul> |
| </dd></dl> |
| <p><a class="anchor" id="related"></a></p><dl class="section user"><dt>Related Topics</dt><dd>File <a class="el" href="summary_8sql__in.html" title="Summary function for descriptive statistics. ">summary.sql_in</a> documenting the <b><a class="el" href="summary_8sql__in.html#a14ff66aaa0248c5e2741dfaf2e360881">summary()</a></b> function</dd></dl> |
| <p><a class="el" href="group__grp__mfvsketch.html">MFV (Most Frequent Values)</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> |
