docs/v0.6/group__grp__linreg.html - madlib-site - Git at Google

 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 <html xmlns="http://www.w3.org/1999/xhtml">
 <head>
 <meta http-equiv="Content-Type" content="text/xhtml;charset=UTF-8"/>
 <title>MADlib: Linear Regression</title>

 <link href="tabs.css" rel="stylesheet" type="text/css"/>
 <link href="doxygen.css" rel="stylesheet" type="text/css" />
 <link href="navtree.css" rel="stylesheet" type="text/css"/>
 <script type="text/javascript" src="jquery.js"></script>
 <script type="text/javascript" src="resize.js"></script>
 <script type="text/javascript" src="navtree.js"></script>
 <script type="text/javascript">
   $(document).ready(initResizable);
 </script>
 <link href="search/search.css" rel="stylesheet" type="text/css"/>
 <script type="text/javascript" src="search/search.js"></script>
 <script type="text/javascript">
   $(document).ready(function() { searchBox.OnSelectItem(0); });
 </script>
 <script src="../mathjax/MathJax.js">
   MathJax.Hub.Config({
     extensions: ["tex2jax.js", "TeX/AMSmath.js", "TeX/AMSsymbols.js"],
     jax: ["input/TeX","output/HTML-CSS"],
 });
 </script>
 </head>
 <body>
 <div id="top"><!-- do not remove this div! -->


 <div id="titlearea">
 <table cellspacing="0" cellpadding="0">
  <tbody>
  <tr style="height: 56px;">


   <td style="padding-left: 0.5em;">
    <div id="projectname">MADlib
    &#160;<span id="projectnumber">0.6</span> <span style="font-size:10pt; font-style:italic"><a href="../latest/./group__grp__linreg.html"> A newer version is available</a></span>
    </div>
    <div id="projectbrief">User Documentation</div>
   </td>


  </tr>
  </tbody>
 </table>
 </div>

 <!-- Generated by Doxygen 1.7.5.1 -->
 <script type="text/javascript">
 var searchBox = new SearchBox("searchBox", "search",false,'Search');
 </script>
 <script type="text/javascript" src="dynsections.js"></script>
   <div id="navrow1" class="tabs">
     <ul class="tablist">
       <li><a href="index.html"><span>Main&#160;Page</span></a></li>
       <li><a href="modules.html"><span>Modules</span></a></li>
       <li><a href="files.html"><span>Files</span></a></li>
       <li>
         <div id="MSearchBox" class="MSearchBoxInactive">
         <span class="left">
           <img id="MSearchSelect" src="search/mag_sel.png"
                onmouseover="return searchBox.OnSearchSelectShow()"
                onmouseout="return searchBox.OnSearchSelectHide()"
                alt=""/>
           <input type="text" id="MSearchField" value="Search" accesskey="S"
                onfocus="searchBox.OnSearchFieldFocus(true)"
                onblur="searchBox.OnSearchFieldFocus(false)"
                onkeyup="searchBox.OnSearchFieldChange(event)"/>
           </span><span class="right">
             <a id="MSearchClose" href="javascript:searchBox.CloseResultsWindow()"><img id="MSearchCloseImg" border="0" src="search/close.png" alt=""/></a>
           </span>
         </div>
       </li>
     </ul>
   </div>
 </div>
 <div id="side-nav" class="ui-resizable side-nav-resizable">
   <div id="nav-tree">
     <div id="nav-tree-contents">
     </div>
   </div>
   <div id="splitbar" style="-moz-user-select:none;"
        class="ui-resizable-handle">
   </div>
 </div>
 <script type="text/javascript">
   initNavTree('group__grp__linreg.html','');
 </script>
 <div id="doc-content">
 <div class="header">
   <div class="headertitle">
 <div class="title">Linear Regression</div>  </div>
 <div class="ingroups"><a class="el" href="group__grp__suplearn.html">Supervised Learning</a></div></div>
 <div class="contents">
 <div id="dynsection-0" onclick="return toggleVisibility(this)" class="dynheader closed" style="cursor:pointer;">
   <img id="dynsection-0-trigger" src="closed.png" alt="+"/> Collaboration diagram for Linear Regression:</div>
 <div id="dynsection-0-summary" class="dynsummary" style="display:block;">
 </div>
 <div id="dynsection-0-content" class="dyncontent" style="display:none;">
 <center><table><tr><td><div class="center"><iframe scrolling="no" frameborder="0" src="group__grp__linreg.svg" width="347" height="40"><p><b>This browser is not able to show SVG: try Firefox, Chrome, Safari, or Opera instead.</b></p></iframe>
 </div>
 </td></tr></table></center>
 </div>
 <dl class="user"><dt><b>About:</b></dt><dd></dd></dl>
 <p>Ordinary least-squares (OLS) linear regression refers to a stochastic model in which the conditional mean of the dependent variable (usually denoted \( Y \)) is an affine function of the vector of independent variables (usually denoted \( \boldsymbol x \)). That is, </p>
 <p class="formulaDsp">
 \[ E[Y \mid \boldsymbol x] = \boldsymbol c^T \boldsymbol x \]
 </p>
 <p> for some unknown vector of coefficients \( \boldsymbol c \). The assumption is that the residuals are i.i.d. distributed Gaussians. That is, the (conditional) probability density of \( Y \) is given by </p>
 <p class="formulaDsp">
 \[ f(y \mid \boldsymbol x) = \frac{1}{\sqrt{2 \pi \sigma^2}} \cdot \exp\left(-\frac{1}{2 \sigma^2} \cdot (y - \boldsymbol x^T \boldsymbol c)^2 \right) \,. \]
 </p>
 <p> OLS linear regression finds the vector of coefficients \( \boldsymbol c \) that maximizes the likelihood of the observations.</p>
 <p>Let</p>
 <ul>
 <li>\( \boldsymbol y \in \mathbf R^n \) denote the vector of observed dependent variables, with \( n \) rows, containing the observed values of the dependent variable,</li>
 <li>\( X \in \mathbf R^{n \times k} \) denote the design matrix with \( k \) columns and \( n \) rows, containing all observed vectors of independent variables. \( \boldsymbol x_i \) as rows,</li>
 <li>\( X^T \) denote the transpose of \( X \),</li>
 <li>\( X^+ \) denote the pseudo-inverse of \( X \).</li>
 </ul>
 <p>Maximizing the likelihood is equivalent to maximizing the log-likelihood \( \sum_{i=1}^n \log f(y_i \mid \boldsymbol x_i) \), which simplifies to minimizing the <b>residual sum of squares</b> \( RSS \) (also called sum of squared residuals or sum of squared errors of prediction), </p>
 <p class="formulaDsp">
 \[ RSS = \sum_{i=1}^n ( y_i - \boldsymbol c^T \boldsymbol x_i )^2 = (\boldsymbol y - X \boldsymbol c)^T (\boldsymbol y - X \boldsymbol c) \,. \]
 </p>
 <p> The first-order conditions yield that the \( RSS \) is minimized at </p>
 <p class="formulaDsp">
 \[ \boldsymbol c = (X^T X)^+ X^T \boldsymbol y \,. \]
 </p>
 <p>Computing the <b>total sum of squares</b> \( TSS \), the <b>explained sum of squares</b> \( ESS \) (also called the regression sum of squares), and the <b>coefficient of determination</b> \( R^2 \) is done according to the following formulas: </p>
 <p class="formulaDsp">
 \begin{align*} ESS &amp; = \boldsymbol y^T X \boldsymbol c - \frac{ \| y \|_1^2 }{n} \\ TSS &amp; = \sum_{i=1}^n y_i^2 - \frac{ \| y \|_1^2 }{n} \\ R^2 &amp; = \frac{ESS}{TSS} \end{align*}
 </p>
 <p> Note: The last equality follows from the definition \( R^2 = 1 - \frac{RSS}{TSS} \) and the fact that for linear regression \( TSS = RSS + ESS \). A proof of the latter can be found, e.g., at: <a href="http://en.wikipedia.org/wiki/Sum_of_squares">http://en.wikipedia.org/wiki/Sum_of_squares</a></p>
 <p>We estimate the variance \( Var[Y - \boldsymbol c^T \boldsymbol x \mid \boldsymbol x] \) as </p>
 <p class="formulaDsp">
 \[ \sigma^2 = \frac{RSS}{n - k} \]
 </p>
 <p> and compute the t-statistic for coefficient \( i \) as </p>
 <p class="formulaDsp">
 \[ t_i = \frac{c_i}{\sqrt{\sigma^2 \cdot \left( (X^T X)^{-1} \right)_{ii} }} \,. \]
 </p>
 <p>The \( p \)-value for coefficient \( i \) gives the probability of seeing a value at least as extreme as the one observed, provided that the null hypothesis ( \( c_i = 0 \)) is true. Letting \( F_\nu \) denote the cumulative density function of student-t with \( \nu \) degrees of freedom, the \( p \)-value for coefficient \( i \) is therefore </p>
 <p class="formulaDsp">
 \[ p_i = \Pr(|T| \geq |t_i|) = 2 \cdot (1 - F_{n - k}( |t_i| )) \]
 </p>
 <p> where \( T \) is a student-t distributed random variable with mean 0.</p>
 <p>The condition number [2] \( \kappa(X) = \|X\|_2\cdot\|X^{-1}\|_2\) is computed as the product of two spectral norms [3]. The spectral norm of a matrix \(X\) is the largest singular value of \(X\) i.e. the square root of the largest eigenvalue of the positive-semidefinite matrix \(X^{*}X\):</p>
 <p class="formulaDsp">
 \[ \|X\|_2 = \sqrt{\lambda_{\max}\left(X^{*}X\right)}\ , \]
 </p>
 <p> where \(X^{*}\) is the conjugate transpose of \(X\). The condition number of a linear regression problem is a worst-case measure of how sensitive the result is to small perturbations of the input. A large condition number (say, more than 1000) indicates the presence of significant multicollinearity.</p>
 <dl class="user"><dt><b>Input:</b></dt><dd></dd></dl>
 <p>The training data is expected to be of the following form: </p>
 <pre>{TABLE|VIEW} <em>sourceName</em> (
     ...
     <em>dependentVariable</em> FLOAT8,
     <em>independentVariables</em> FLOAT8[],
     ...
 )</pre><dl class="user"><dt><b>Usage:</b></dt><dd></dd></dl>
 <p><b>(1) The Simple Interface</b></p>
 <ul>
 <li>Get vector of coefficients \( \boldsymbol c \) and all diagnostic statistics: <pre>SELECT (madlib.<a class="el" href="linear_8sql__in.html#a71d8295a18e93619b3331cefabe6e79b">linregr</a>(<em>dependentVariable</em>,
     <em>independentVariables</em>)).*
 FROM <em>sourceName</em>;</pre> Output: <pre>
 coef | r2 | std_err | t_stats | p_values | condition_no
 -----+----+---------+---------+----------+-------------
                           ...
 </pre></li>
 </ul>
 <ul>
 <li>Get vector of coefficients \( \boldsymbol c \):<br/>
  <pre>SELECT (madlib.<a class="el" href="linear_8sql__in.html#a71d8295a18e93619b3331cefabe6e79b">linregr</a>(<em>dependentVariable</em>,
   <em>independentVariables</em>)).coef
 FROM <em>sourceName</em>;</pre></li>
 </ul>
 <ul>
 <li>Get a subset of the output columns, e.g., only the array of coefficients \( \boldsymbol c \), the coefficient of determination \( R^2 \), and the array of p-values \( \boldsymbol p \): <pre>SELECT (lr).coef, (lr).r2, (lr).p_values
 FROM (
     SELECT madlib.<a class="el" href="linear_8sql__in.html#a71d8295a18e93619b3331cefabe6e79b">linregr</a>(<em>dependentVariable</em>,
       <em>independentVariables</em>) AS lr
     FROM <em>sourceName</em>
 ) AS subq;</pre></li>
 </ul>
 <p><b>(2) The Full Interface</b></p>
 <p>The full interface support the analysis of heteroskedasticity of the linear fit.</p>
 <pre>
 SELECT madlib.linregr_train (
     <em>'source_table'</em>,        -- name of input table, VARCHAR
     <em>'out_table'</em>,           -- name of output table, VARCHAR
     <em>'dependent_varname'</em>,   -- dependent variable, VARCHAR
     <em>'independent_varname'</em>, -- independent variable, VARCHAR
     [<em>group_cols</em>,           -- names of columns to group by, VARCHAR[].
                                     -- Default value: Null
     [<em>heteroskedasticity_option</em>]] -- whether to analyze
                                           --    heteroskedasticity,
                                           -- BOOLEAN. Default value: False
 );
 </pre><p>Here the <em>'independent_varname'</em> can be the name of a column, which contains array of numeric values. It can also have a format of string 'array[1, x1, x2, x3]', where <em>x1</em>, <em>x2</em> and <em>x3</em> are all column names.</p>
 <p>Output is stored in the <em>out_table</em>: </p>
 <pre>
 [ group_col_1 | group_col_2 | ... |] coef | r2 | std_err | t_stats | p_values | condition_no [|
 -----------+-------------+-----+------+----+---------+---------+----------+--------------+---</pre><pre>bp_stats | bp_p_value ]
 -------------+---------
 </pre><p>Where the first part </p>
 <pre>[ group_col_1 | group_col_2 | ... |]</pre><p> presents only when <em>group_cols</em> is not Null. The last part </p>
 <pre>[ bp_stats | ... |
 corrected_p_values ]</pre><p> presents only when <em>heteroskedasticity_option</em> is <em>True</em>.</p>
 <p>When <em>group_cols</em> is given, the data is grouped by the given columns and a linear model is fit to each group of data. The output will have additional columns for all combinations of the values of all the <em>group_cols</em>. For each combination of <em>group_cols</em> values, linear regression result is shown.</p>
 <p>When <em>heteroskedasticity_option</em> is <em>True</em>, the output will have additional columns. The function computes the Breusch–Pagan test [4] statistics and the corresponding \(p\)-value.</p>
 <dl class="user"><dt><b>Examples:</b></dt><dd></dd></dl>
 <p>The following example is taken from <a href="http://www.stat.columbia.edu/~martin/W2110/SAS_7.pdf.">http://www.stat.columbia.edu/~martin/W2110/SAS_7.pdf.</a></p>
 <ol type="1">
 <li>Create the sample data set: <div class="fragment"><pre class="fragment">
 sql&gt; CREATE TABLE houses (id INT, tax INT, bedroom INT, bath FLOAT, price INT,
             size INT, lot INT);
 sql&gt; 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></div></li>
 <li>You can call the <a class="el" href="linear_8sql__in.html#a71d8295a18e93619b3331cefabe6e79b" title="Compute linear regression coefficients and diagnostic statistics.">linregr()</a> function for an individual metric: <div class="fragment"><pre class="fragment">
 sql&gt; SELECT (linregr(price, array[1, bedroom, bath, size])).coef FROM houses;
                                   coef
 ------------------------------------------------------------------------
  {27923.4334170641,-35524.7753390234,2269.34393735323,130.793920208133}
 (1 row)

 sql&gt; SELECT (linregr(price, array[1, bedroom, bath, size])).r2 FROM houses;
         r2
 -------------------
  0.745374010140315
 (1 row)

 sql&gt; SELECT (linregr(price, array[1, bedroom, bath, size])).std_err FROM houses;
                                std_err
 ----------------------------------------------------------------------
  {56306.4821787474,25036.6537279169,22208.6687270562,36.208642285651}
 (1 row)

 sql&gt; SELECT (linregr(price, array[1, bedroom, bath, size])).t_stats FROM houses;
                                 t_stats
 ------------------------------------------------------------------------
  {0.495918628487924,-1.41891067892239,0.10218279921428,3.6122293450358}
 (1 row)

 sql&gt; SELECT (linregr(price, array[1, bedroom, bath, size])).p_values FROM houses;
                                   p_values
 -----------------------------------------------------------------------------
  {0.629711069315512,0.183633155781461,0.920450514073051,0.00408159079312354}
 (1 row)
 </pre></div></li>
 <li>Alternatively you can call the linreg() function for the full record: <div class="fragment"><pre class="fragment">
 sql&gt; \x on
 Expanded display is on.
 sql&gt; SELECT (r).* FROM (SELECT linregr(price, array[1, bedroom, bath, size])
             AS r FROM houses) q;
 -[ RECORD 1 ]+-----------------------------------------------------------------
 coef         | {27923.4334170641,-35524.7753390234,2269.34393735323,130.793920208133}
 r2           | 0.745374010140315
 std_err      | {56306.4821787474,25036.6537279169,22208.6687270562,36.208642285651}
 t_stats      | {0.495918628487924,-1.41891067892239,0.10218279921428,3.6122293450358}
 p_values     | {0.629711069315512,0.183633155781461,0.920450514073051,0.00408159079312354}
 condition_no | 9783.018

 </pre></div></li>
 </ol>
 <ol type="1">
 <li>You can call linregr_train() function for more functionality <div class="fragment"><pre class="fragment">
 sql&gt; SELECT madlib.linregr_train('houses', 'result', 'price',
                                  'array[1, tax, bath, size]',
                                  '{bedroom}'::varchar[], True);

 sql&gt; SELECT * from result;
 -[ RECORD 1]---------+-------------------------------------------------------
 bedroom             | 2
 coef                | {-84242.0345, 55.4430, -78966.9754, 225.6119}
 r2                  | 0.9688
 std_err             | {35019.00, 19.57, 23036.81, 49.04}
 t_stats             | {-2.406, 2.833, -3.428, 4.600}
 p_values            | {0.251, 0.216, 0.181, 0.136}
 condition_no        | 10086.1
 bp_stats            | 2.5451
 bp_p_value          | 0.4672

 -[ RECORD 2]---------+------------------------------------------------------
 bedroom             | 3
 coef                | {-88155.8292502747,27.1966436293179,41404.0293389239,62.6375210724027}
 r2                  | 0.841699901312963
 std_err             | {57867.9999699512,17.82723091538,43643.1321521931,70.8506824870639}
 t_stats             | {-1.52339512850022,1.52556747362568,0.948695185179172,0.884077878626493}
 p_values            | {0.18816143289241,0.187636685729725,0.38634003235866,0.417132778730133}
 condition_no        | 11722.62
 bp_stats            | 6.7538
 bp_p_value          | 0.08017

 -[ RECORD 3]---------+-------------------------------------------------------
 bedroom             | 4
 coef                | {0.0112536020318378,41.4132554771633,0.0225072040636757,31.3975496688276}
 r2                  | 1
 std_err             | {0,0,0,0}
 t_stats             | {Infinity,Infinity,Infinity,Infinity}
 p_values            | Null
 condition_no        | Null
 bp_stats            | Null
 bp_p_value          | Null

 </pre></div></li>
 </ol>
 <dl class="user"><dt><b>Literature:</b></dt><dd></dd></dl>
 <p>[1] Cosma Shalizi: Statistics 36-350: Data Mining, Lecture Notes, 21 October 2009, <a href="http://www.stat.cmu.edu/~cshalizi/350/lectures/17/lecture-17.pdf">http://www.stat.cmu.edu/~cshalizi/350/lectures/17/lecture-17.pdf</a></p>
 <p>[2] Wikipedia: Condition Number, <a href="http://en.wikipedia.org/wiki/Condition_number.">http://en.wikipedia.org/wiki/Condition_number.</a></p>
 <p>[3] Wikipedia: Spectral Norm, <a href="http://en.wikipedia.org/wiki/Spectral_norm#Spectral_norm">http://en.wikipedia.org/wiki/Spectral_norm#Spectral_norm</a></p>
 <p>[4] Wikipedia: Breusch–Pagan test, <a href="http://en.wikipedia.org/wiki/Breusch%E2%80%93Pagan_test">http://en.wikipedia.org/wiki/Breusch%E2%80%93Pagan_test</a></p>
 <p>[5] Wikipedia: Heteroscedasticity-consistent standard errors, <a href="http://en.wikipedia.org/wiki/Heteroscedasticity-consistent_standard_errors">http://en.wikipedia.org/wiki/Heteroscedasticity-consistent_standard_errors</a></p>
 <dl class="see"><dt><b>See also:</b></dt><dd>File <a class="el" href="linear_8sql__in.html" title="SQL functions for linear regression.">linear.sql_in</a> documenting the SQL functions. </dd></dl>
 </div>
 </div>
   <div id="nav-path" class="navpath">
     <ul>
 <!-- window showing the filter options -->
 <div id="MSearchSelectWindow"
      onmouseover="return searchBox.OnSearchSelectShow()"
      onmouseout="return searchBox.OnSearchSelectHide()"
      onkeydown="return searchBox.OnSearchSelectKey(event)">
 <a class="SelectItem" href="javascript:void(0)" onclick="searchBox.OnSelectItem(0)"><span class="SelectionMark">&#160;</span>All</a><a class="SelectItem" href="javascript:void(0)" onclick="searchBox.OnSelectItem(1)"><span class="SelectionMark">&#160;</span>Files</a><a class="SelectItem" href="javascript:void(0)" onclick="searchBox.OnSelectItem(2)"><span class="SelectionMark">&#160;</span>Functions</a></div>

 <!-- iframe showing the search results (closed by default) -->
 <div id="MSearchResultsWindow">
 <iframe src="javascript:void(0)" frameborder="0"
         name="MSearchResults" id="MSearchResults">
 </iframe>
 </div>


     <li class="footer">Generated on Tue Apr 2 2013 14:57:03 for MADlib by
     <a href="http://www.doxygen.org/index.html">
     <img class="footer" src="doxygen.png" alt="doxygen"/></a> 1.7.5.1 </li>
    </ul>
  </div>


 </body>
 </html>
	<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
	<html xmlns="http://www.w3.org/1999/xhtml">
	<head>
	<meta http-equiv="Content-Type" content="text/xhtml;charset=UTF-8"/>
	<title>MADlib: Linear Regression</title>

	<link href="tabs.css" rel="stylesheet" type="text/css"/>
	<link href="doxygen.css" rel="stylesheet" type="text/css" />
	<link href="navtree.css" rel="stylesheet" type="text/css"/>
	<script type="text/javascript" src="jquery.js"></script>
	<script type="text/javascript" src="resize.js"></script>
	<script type="text/javascript" src="navtree.js"></script>
	<script type="text/javascript">
	$(document).ready(initResizable);
	</script>
	<link href="search/search.css" rel="stylesheet" type="text/css"/>
	<script type="text/javascript" src="search/search.js"></script>
	<script type="text/javascript">
	$(document).ready(function() { searchBox.OnSelectItem(0); });
	</script>
	<script src="../mathjax/MathJax.js">
	MathJax.Hub.Config({
	extensions: ["tex2jax.js", "TeX/AMSmath.js", "TeX/AMSsymbols.js"],
	jax: ["input/TeX","output/HTML-CSS"],
	});
	</script>
	</head>
	<body>
	<div id="top"><!-- do not remove this div! -->


	<div id="titlearea">
	<table cellspacing="0" cellpadding="0">
	<tbody>
	<tr style="height: 56px;">


	<td style="padding-left: 0.5em;">
	<div id="projectname">MADlib
	<span id="projectnumber">0.6</span> <span style="font-size:10pt; font-style:italic"><a href="../latest/./group__grp__linreg.html"> A newer version is available</a></span>
	</div>
	<div id="projectbrief">User Documentation</div>
	</td>



	</tr>
	</tbody>
	</table>
	</div>

	<!-- Generated by Doxygen 1.7.5.1 -->
	<script type="text/javascript">
	var searchBox = new SearchBox("searchBox", "search",false,'Search');
	</script>
	<script type="text/javascript" src="dynsections.js"></script>
	<div id="navrow1" class="tabs">
	<ul class="tablist">
	<li><a href="index.html"><span>Main Page</span></a></li>
	<li><a href="modules.html"><span>Modules</span></a></li>
	<li><a href="files.html"><span>Files</span></a></li>
	<li>
	<div id="MSearchBox" class="MSearchBoxInactive">
	<span class="left">
	<img id="MSearchSelect" src="search/mag_sel.png"
	onmouseover="return searchBox.OnSearchSelectShow()"
	onmouseout="return searchBox.OnSearchSelectHide()"
	alt=""/>
	<input type="text" id="MSearchField" value="Search" accesskey="S"
	onfocus="searchBox.OnSearchFieldFocus(true)"
	onblur="searchBox.OnSearchFieldFocus(false)"
	onkeyup="searchBox.OnSearchFieldChange(event)"/>
	</span><span class="right">
	<a id="MSearchClose" href="javascript:searchBox.CloseResultsWindow()"><img id="MSearchCloseImg" border="0" src="search/close.png" alt=""/></a>
	</span>
	</div>
	</li>
	</ul>
	</div>
	</div>
	<div id="side-nav" class="ui-resizable side-nav-resizable">
	<div id="nav-tree">
	<div id="nav-tree-contents">
	</div>
	</div>
	<div id="splitbar" style="-moz-user-select:none;"
	class="ui-resizable-handle">
	</div>
	</div>
	<script type="text/javascript">
	initNavTree('group__grp__linreg.html','');
	</script>
	<div id="doc-content">
	<div class="header">
	<div class="headertitle">
	<div class="title">Linear Regression</div> </div>
	<div class="ingroups"><a class="el" href="group__grp__suplearn.html">Supervised Learning</a></div></div>
	<div class="contents">
	<div id="dynsection-0" onclick="return toggleVisibility(this)" class="dynheader closed" style="cursor:pointer;">
	<img id="dynsection-0-trigger" src="closed.png" alt="+"/> Collaboration diagram for Linear Regression:</div>
	<div id="dynsection-0-summary" class="dynsummary" style="display:block;">
	</div>
	<div id="dynsection-0-content" class="dyncontent" style="display:none;">
	<center><table><tr><td><div class="center"><iframe scrolling="no" frameborder="0" src="group__grp__linreg.svg" width="347" height="40"><p><b>This browser is not able to show SVG: try Firefox, Chrome, Safari, or Opera instead.</b></p></iframe>
	</div>
	</td></tr></table></center>
	</div>
	<dl class="user"><dt><b>About:</b></dt><dd></dd></dl>
	<p>Ordinary least-squares (OLS) linear regression refers to a stochastic model in which the conditional mean of the dependent variable (usually denoted \( Y \)) is an affine function of the vector of independent variables (usually denoted \( \boldsymbol x \)). That is, </p>
	<p class="formulaDsp">
	\[ E[Y \mid \boldsymbol x] = \boldsymbol c^T \boldsymbol x \]
	</p>
	<p> for some unknown vector of coefficients \( \boldsymbol c \). The assumption is that the residuals are i.i.d. distributed Gaussians. That is, the (conditional) probability density of \( Y \) is given by </p>
	<p class="formulaDsp">
	\[ f(y \mid \boldsymbol x) = \frac{1}{\sqrt{2 \pi \sigma^2}} \cdot \exp\left(-\frac{1}{2 \sigma^2} \cdot (y - \boldsymbol x^T \boldsymbol c)^2 \right) \,. \]
	</p>
	<p> OLS linear regression finds the vector of coefficients \( \boldsymbol c \) that maximizes the likelihood of the observations.</p>
	<p>Let</p>
	<ul>
	<li>\( \boldsymbol y \in \mathbf R^n \) denote the vector of observed dependent variables, with \( n \) rows, containing the observed values of the dependent variable,</li>
	<li>\( X \in \mathbf R^{n \times k} \) denote the design matrix with \( k \) columns and \( n \) rows, containing all observed vectors of independent variables. \( \boldsymbol x_i \) as rows,</li>
	<li>\( X^T \) denote the transpose of \( X \),</li>
	<li>\( X^+ \) denote the pseudo-inverse of \( X \).</li>
	</ul>
	<p>Maximizing the likelihood is equivalent to maximizing the log-likelihood \( \sum_{i=1}^n \log f(y_i \mid \boldsymbol x_i) \), which simplifies to minimizing the <b>residual sum of squares</b> \( RSS \) (also called sum of squared residuals or sum of squared errors of prediction), </p>
	<p class="formulaDsp">
	\[ RSS = \sum_{i=1}^n ( y_i - \boldsymbol c^T \boldsymbol x_i )^2 = (\boldsymbol y - X \boldsymbol c)^T (\boldsymbol y - X \boldsymbol c) \,. \]
	</p>
	<p> The first-order conditions yield that the \( RSS \) is minimized at </p>
	<p class="formulaDsp">
	\[ \boldsymbol c = (X^T X)^+ X^T \boldsymbol y \,. \]
	</p>
	<p>Computing the <b>total sum of squares</b> \( TSS \), the <b>explained sum of squares</b> \( ESS \) (also called the regression sum of squares), and the <b>coefficient of determination</b> \( R^2 \) is done according to the following formulas: </p>
	<p class="formulaDsp">
	\begin{align} ESS & = \boldsymbol y^T X \boldsymbol c - \frac{ \\| y \\|_1^2 }{n} \\ TSS & = \sum_{i=1}^n y_i^2 - \frac{ \\| y \\|_1^2 }{n} \\ R^2 & = \frac{ESS}{TSS} \end{align}
	</p>
	<p> Note: The last equality follows from the definition \( R^2 = 1 - \frac{RSS}{TSS} \) and the fact that for linear regression \( TSS = RSS + ESS \). A proof of the latter can be found, e.g., at: <a href="http://en.wikipedia.org/wiki/Sum_of_squares">http://en.wikipedia.org/wiki/Sum_of_squares</a></p>
	<p>We estimate the variance \( Var[Y - \boldsymbol c^T \boldsymbol x \mid \boldsymbol x] \) as </p>
	<p class="formulaDsp">
	\[ \sigma^2 = \frac{RSS}{n - k} \]
	</p>
	<p> and compute the t-statistic for coefficient \( i \) as </p>
	<p class="formulaDsp">
	\[ t_i = \frac{c_i}{\sqrt{\sigma^2 \cdot \left( (X^T X)^{-1} \right)_{ii} }} \,. \]
	</p>
	<p>The \( p \)-value for coefficient \( i \) gives the probability of seeing a value at least as extreme as the one observed, provided that the null hypothesis ( \( c_i = 0 \)) is true. Letting \( F_\nu \) denote the cumulative density function of student-t with \( \nu \) degrees of freedom, the \( p \)-value for coefficient \( i \) is therefore </p>
	<p class="formulaDsp">
	\[ p_i = \Pr(\|T\| \geq \|t_i\|) = 2 \cdot (1 - F_{n - k}( \|t_i\| )) \]
	</p>
	<p> where \( T \) is a student-t distributed random variable with mean 0.</p>
	<p>The condition number [2] \( \kappa(X) = \\|X\\|_2\cdot\\|X^{-1}\\|_2\) is computed as the product of two spectral norms [3]. The spectral norm of a matrix \(X\) is the largest singular value of \(X\) i.e. the square root of the largest eigenvalue of the positive-semidefinite matrix \(X^{*}X\):</p>
	<p class="formulaDsp">
	\[ \\|X\\|_2 = \sqrt{\lambda_{\max}\left(X^{*}X\right)}\ , \]
	</p>
	<p> where \(X^{*}\) is the conjugate transpose of \(X\). The condition number of a linear regression problem is a worst-case measure of how sensitive the result is to small perturbations of the input. A large condition number (say, more than 1000) indicates the presence of significant multicollinearity.</p>
	<dl class="user"><dt><b>Input:</b></dt><dd></dd></dl>
	<p>The training data is expected to be of the following form: </p>
	<pre>{TABLE\|VIEW} <em>sourceName</em> (
	...
	<em>dependentVariable</em> FLOAT8,
	<em>independentVariables</em> FLOAT8[],
	...
	)</pre><dl class="user"><dt><b>Usage:</b></dt><dd></dd></dl>
	<p><b>(1) The Simple Interface</b></p>
	<ul>
	<li>Get vector of coefficients \( \boldsymbol c \) and all diagnostic statistics: <pre>SELECT (madlib.<a class="el" href="linear_8sql__in.html#a71d8295a18e93619b3331cefabe6e79b">linregr</a>(<em>dependentVariable</em>,
	<em>independentVariables</em>)).*
	FROM <em>sourceName</em>;</pre> Output: <pre>
	coef \| r2 \| std_err \| t_stats \| p_values \| condition_no
	-----+----+---------+---------+----------+-------------
	...
	</pre></li>
	</ul>
	<ul>
	<li>Get vector of coefficients \( \boldsymbol c \):<br/>
	<pre>SELECT (madlib.<a class="el" href="linear_8sql__in.html#a71d8295a18e93619b3331cefabe6e79b">linregr</a>(<em>dependentVariable</em>,
	<em>independentVariables</em>)).coef
	FROM <em>sourceName</em>;</pre></li>
	</ul>
	<ul>
	<li>Get a subset of the output columns, e.g., only the array of coefficients \( \boldsymbol c \), the coefficient of determination \( R^2 \), and the array of p-values \( \boldsymbol p \): <pre>SELECT (lr).coef, (lr).r2, (lr).p_values
	FROM (
	SELECT madlib.<a class="el" href="linear_8sql__in.html#a71d8295a18e93619b3331cefabe6e79b">linregr</a>(<em>dependentVariable</em>,
	<em>independentVariables</em>) AS lr
	FROM <em>sourceName</em>
	) AS subq;</pre></li>
	</ul>
	<p><b>(2) The Full Interface</b></p>
	<p>The full interface support the analysis of heteroskedasticity of the linear fit.</p>
	<pre>
	SELECT madlib.linregr_train (
	<em>'source_table'</em>, -- name of input table, VARCHAR
	<em>'out_table'</em>, -- name of output table, VARCHAR
	<em>'dependent_varname'</em>, -- dependent variable, VARCHAR
	<em>'independent_varname'</em>, -- independent variable, VARCHAR
	[<em>group_cols</em>, -- names of columns to group by, VARCHAR[].
	-- Default value: Null
	[<em>heteroskedasticity_option</em>]] -- whether to analyze
	-- heteroskedasticity,
	-- BOOLEAN. Default value: False
	);
	</pre><p>Here the <em>'independent_varname'</em> can be the name of a column, which contains array of numeric values. It can also have a format of string 'array[1, x1, x2, x3]', where <em>x1</em>, <em>x2</em> and <em>x3</em> are all column names.</p>
	<p>Output is stored in the <em>out_table</em>: </p>
	<pre>
	[ group_col_1 \| group_col_2 \| ... \|] coef \| r2 \| std_err \| t_stats \| p_values \| condition_no [\|
	-----------+-------------+-----+------+----+---------+---------+----------+--------------+---</pre><pre>bp_stats \| bp_p_value ]
	-------------+---------
	</pre><p>Where the first part </p>
	<pre>[ group_col_1 \| group_col_2 \| ... \|]</pre><p> presents only when <em>group_cols</em> is not Null. The last part </p>
	<pre>[ bp_stats \| ... \|
	corrected_p_values ]</pre><p> presents only when <em>heteroskedasticity_option</em> is <em>True</em>.</p>
	<p>When <em>group_cols</em> is given, the data is grouped by the given columns and a linear model is fit to each group of data. The output will have additional columns for all combinations of the values of all the <em>group_cols</em>. For each combination of <em>group_cols</em> values, linear regression result is shown.</p>
	<p>When <em>heteroskedasticity_option</em> is <em>True</em>, the output will have additional columns. The function computes the Breusch–Pagan test [4] statistics and the corresponding \(p\)-value.</p>
	<dl class="user"><dt><b>Examples:</b></dt><dd></dd></dl>
	<p>The following example is taken from <a href="http://www.stat.columbia.edu/~martin/W2110/SAS_7.pdf.">http://www.stat.columbia.edu/~martin/W2110/SAS_7.pdf.</a></p>
	<ol type="1">
	<li>Create the sample data set: <div class="fragment"><pre class="fragment">
	sql> CREATE TABLE houses (id INT, tax INT, bedroom INT, bath FLOAT, price INT,
	size INT, lot INT);
	sql> 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></div></li>
	<li>You can call the <a class="el" href="linear_8sql__in.html#a71d8295a18e93619b3331cefabe6e79b" title="Compute linear regression coefficients and diagnostic statistics.">linregr()</a> function for an individual metric: <div class="fragment"><pre class="fragment">
	sql> SELECT (linregr(price, array[1, bedroom, bath, size])).coef FROM houses;
	coef
	------------------------------------------------------------------------
	{27923.4334170641,-35524.7753390234,2269.34393735323,130.793920208133}
	(1 row)

	sql> SELECT (linregr(price, array[1, bedroom, bath, size])).r2 FROM houses;
	r2
	-------------------
	0.745374010140315
	(1 row)

	sql> SELECT (linregr(price, array[1, bedroom, bath, size])).std_err FROM houses;
	std_err
	----------------------------------------------------------------------
	{56306.4821787474,25036.6537279169,22208.6687270562,36.208642285651}
	(1 row)

	sql> SELECT (linregr(price, array[1, bedroom, bath, size])).t_stats FROM houses;
	t_stats
	------------------------------------------------------------------------
	{0.495918628487924,-1.41891067892239,0.10218279921428,3.6122293450358}
	(1 row)

	sql> SELECT (linregr(price, array[1, bedroom, bath, size])).p_values FROM houses;
	p_values
	-----------------------------------------------------------------------------
	{0.629711069315512,0.183633155781461,0.920450514073051,0.00408159079312354}
	(1 row)
	</pre></div></li>
	<li>Alternatively you can call the linreg() function for the full record: <div class="fragment"><pre class="fragment">
	sql> \x on
	Expanded display is on.
	sql> SELECT (r).* FROM (SELECT linregr(price, array[1, bedroom, bath, size])
	AS r FROM houses) q;
	-[ RECORD 1 ]+-----------------------------------------------------------------
	coef \| {27923.4334170641,-35524.7753390234,2269.34393735323,130.793920208133}
	r2 \| 0.745374010140315
	std_err \| {56306.4821787474,25036.6537279169,22208.6687270562,36.208642285651}
	t_stats \| {0.495918628487924,-1.41891067892239,0.10218279921428,3.6122293450358}
	p_values \| {0.629711069315512,0.183633155781461,0.920450514073051,0.00408159079312354}
	condition_no \| 9783.018

	</pre></div></li>
	</ol>
	<ol type="1">
	<li>You can call linregr_train() function for more functionality <div class="fragment"><pre class="fragment">
	sql> SELECT madlib.linregr_train('houses', 'result', 'price',
	'array[1, tax, bath, size]',
	'{bedroom}'::varchar[], True);

	sql> SELECT * from result;
	-[ RECORD 1]---------+-------------------------------------------------------
	bedroom \| 2
	coef \| {-84242.0345, 55.4430, -78966.9754, 225.6119}
	r2 \| 0.9688
	std_err \| {35019.00, 19.57, 23036.81, 49.04}
	t_stats \| {-2.406, 2.833, -3.428, 4.600}
	p_values \| {0.251, 0.216, 0.181, 0.136}
	condition_no \| 10086.1
	bp_stats \| 2.5451
	bp_p_value \| 0.4672

	-[ RECORD 2]---------+------------------------------------------------------
	bedroom \| 3
	coef \| {-88155.8292502747,27.1966436293179,41404.0293389239,62.6375210724027}
	r2 \| 0.841699901312963
	std_err \| {57867.9999699512,17.82723091538,43643.1321521931,70.8506824870639}
	t_stats \| {-1.52339512850022,1.52556747362568,0.948695185179172,0.884077878626493}
	p_values \| {0.18816143289241,0.187636685729725,0.38634003235866,0.417132778730133}
	condition_no \| 11722.62
	bp_stats \| 6.7538
	bp_p_value \| 0.08017

	-[ RECORD 3]---------+-------------------------------------------------------
	bedroom \| 4
	coef \| {0.0112536020318378,41.4132554771633,0.0225072040636757,31.3975496688276}
	r2 \| 1
	std_err \| {0,0,0,0}
	t_stats \| {Infinity,Infinity,Infinity,Infinity}
	p_values \| Null
	condition_no \| Null
	bp_stats \| Null
	bp_p_value \| Null

	</pre></div></li>
	</ol>
	<dl class="user"><dt><b>Literature:</b></dt><dd></dd></dl>
	<p>[1] Cosma Shalizi: Statistics 36-350: Data Mining, Lecture Notes, 21 October 2009, <a href="http://www.stat.cmu.edu/~cshalizi/350/lectures/17/lecture-17.pdf">http://www.stat.cmu.edu/~cshalizi/350/lectures/17/lecture-17.pdf</a></p>
	<p>[2] Wikipedia: Condition Number, <a href="http://en.wikipedia.org/wiki/Condition_number.">http://en.wikipedia.org/wiki/Condition_number.</a></p>
	<p>[3] Wikipedia: Spectral Norm, <a href="http://en.wikipedia.org/wiki/Spectral_norm#Spectral_norm">http://en.wikipedia.org/wiki/Spectral_norm#Spectral_norm</a></p>
	<p>[4] Wikipedia: Breusch–Pagan test, <a href="http://en.wikipedia.org/wiki/Breusch%E2%80%93Pagan_test">http://en.wikipedia.org/wiki/Breusch%E2%80%93Pagan_test</a></p>
	<p>[5] Wikipedia: Heteroscedasticity-consistent standard errors, <a href="http://en.wikipedia.org/wiki/Heteroscedasticity-consistent_standard_errors">http://en.wikipedia.org/wiki/Heteroscedasticity-consistent_standard_errors</a></p>
	<dl class="see"><dt><b>See also:</b></dt><dd>File <a class="el" href="linear_8sql__in.html" title="SQL functions for linear regression.">linear.sql_in</a> documenting the SQL functions. </dd></dl>
	</div>
	</div>
	<div id="nav-path" class="navpath">
	<ul>
	<!-- window showing the filter options -->
	<div id="MSearchSelectWindow"
	onmouseover="return searchBox.OnSearchSelectShow()"
	onmouseout="return searchBox.OnSearchSelectHide()"
	onkeydown="return searchBox.OnSearchSelectKey(event)">
	<a class="SelectItem" href="javascript:void(0)" onclick="searchBox.OnSelectItem(0)"><span class="SelectionMark"> </span>All</a><a class="SelectItem" href="javascript:void(0)" onclick="searchBox.OnSelectItem(1)"><span class="SelectionMark"> </span>Files</a><a class="SelectItem" href="javascript:void(0)" onclick="searchBox.OnSelectItem(2)"><span class="SelectionMark"> </span>Functions</a></div>

	<!-- iframe showing the search results (closed by default) -->
	<div id="MSearchResultsWindow">
	<iframe src="javascript:void(0)" frameborder="0"
	name="MSearchResults" id="MSearchResults">
	</iframe>
	</div>


	<li class="footer">Generated on Tue Apr 2 2013 14:57:03 for MADlib by
	<a href="http://www.doxygen.org/index.html">
	<img class="footer" src="doxygen.png" alt="doxygen"/></a> 1.7.5.1 </li>
	</ul>
	</div>


	</body>
	</html>