<!-- 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.13"/>
<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: Array Operations</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);
</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>
<script type="text/x-mathjax-config">
  MathJax.Hub.Config({
    extensions: ["tex2jax.js", "TeX/AMSmath.js", "TeX/AMSsymbols.js"],
    jax: ["input/TeX","output/HTML-CSS"],
});
</script><script type="text/javascript" src="http://cdn.mathjax.org/mathjax/latest/MathJax.js"></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.apache.org');
  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.apache.org"><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.17.0</span>
   </div>
   <div id="projectbrief">User Documentation for Apache 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.13 -->
<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__array.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">Array Operations<div class="ingroups"><a class="el" href="group__grp__datatrans.html">Data Types and Transformations</a> &raquo; <a class="el" href="group__grp__arraysmatrix.html">Arrays and Matrices</a></div></div>  </div>
</div><!--header-->
<div class="contents">
<div class="toc"><b>Contents</b> <ul>
<li>
<a href="#notes">Implementation Notes</a> </li>
<li>
<a href="#list">List of Array Operations</a> </li>
<li>
<a href="#examples">Examples</a> </li>
<li>
<a href="#related">Related Topics</a> </li>
</ul>
</div><p>This module provides a set of basic array operations implemented in C and SQL. It is a support module for several machine learning algorithms that require fast array operations.</p>
<p><a class="anchor" id="notes"></a></p><dl class="section user"><dt>Implementation Notes</dt><dd></dd></dl>
<p>All functions (except <a class="el" href="array__ops_8sql__in.html#acb57ea4521dcb717f9e3148e0acccc74" title="This function normalizes an array as sum of squares to be 1. ">normalize()</a> and <a class="el" href="array__ops_8sql__in.html#acc295a568878940ffc3e2c9a75990efb" title="This function takes an array as the input and keep only elements that satisfy the operator on specifi...">array_filter()</a>) described in this module work with 2-D arrays.</p>
<p>These functions support several numeric types:</p><ul>
<li>SMALLINT</li>
<li>INTEGER</li>
<li>BIGINT</li>
<li>REAL</li>
<li>DOUBLE PRECISION (FLOAT8)</li>
<li>NUMERIC (internally casted into FLOAT8, loss of precisions can happen)</li>
</ul>
<p>Additionally, <a class="el" href="array__ops_8sql__in.html#af057b589f2a2cb1095caa99feaeb3d70" title="This function takes a 2-D array as the input and unnests it by one level. It returns a set of 1-D arr...">array_unnest_2d_to_1d()</a> supports other data types such as TEXT or VARCHAR.</p>
<p>Several of the function require NO NULL VALUES, while others omit NULLs and return results. See details in description of individual functions.</p>
<p><a class="anchor" id="list"></a></p><dl class="section user"><dt>Array Operations</dt><dd><table class="output">
<tr>
<th><a class="el" href="array__ops_8sql__in.html#a91c8d3715142927b3967f05a4fbf1575" title="Adds two arrays. It requires that all the values are NON-NULL. Return type is the same as the input t...">array_add()</a></th><td><p class="starttd">Adds two arrays. It requires that all the values are NON-NULL. Return type is the same as the input type.</p>
<p class="endtd"></p>
</td></tr>
<tr>
<th><a class="el" href="array__ops_8sql__in.html#a26e8508a2bae10a6574cec697a270eea" title="Aggregate, element-wise sum of arrays. It requires that all the values are NON-NULL. Return type is the same as the input type. ">sum()</a></th><td><p class="starttd">Aggregate, sums vector element-wisely. It requires that all the values are NON-NULL. Return type is the same as the input type.</p>
<p class="endtd"></p>
</td></tr>
<tr>
<th><a class="el" href="array__ops_8sql__in.html#a2875a161a01c7dcdea9a4997b074eefc" title="Subtracts two arrays. It requires that all the values are NON-NULL. Return type is the same as the in...">array_sub()</a></th><td><p class="starttd">Subtracts two arrays. It requires that all the values are NON-NULL. Return type is the same as the input type. </p>
<p class="endtd"></p>
</td></tr>
<tr>
<th><a class="el" href="array__ops_8sql__in.html#a652d70c480d484c4a1a92ded384b0dd7" title="Element-wise product of two arrays. It requires that all the values are NON-NULL. Return type is the ...">array_mult()</a></th><td><p class="starttd">Element-wise product of two arrays. It requires that all the values are NON-NULL. Return type is the same as the input type. </p>
<p class="endtd"></p>
</td></tr>
<tr>
<th><a class="el" href="array__ops_8sql__in.html#a6cc05e7052495f8b64692faf40219576" title="Element-wise division of two arrays. It requires that all the values are NON-NULL. Return type is the same as the input type. ">array_div()</a></th><td><p class="starttd">Element-wise division of two arrays. It requires that all the values are NON-NULL. Return type is the same as the input type. </p>
<p class="endtd"></p>
</td></tr>
<tr>
<th><a class="el" href="array__ops_8sql__in.html#acde10964ed23b7c8da515fb84cb8d5e0" title="Dot-product of two arrays. It requires that all the values are NON-NULL. Return type is the same as t...">array_dot()</a></th><td><p class="starttd">Dot-product of two arrays. It requires that all the values are NON-NULL. Return type is the same as the input type. </p>
<p class="endtd"></p>
</td></tr>
<tr>
<th><a class="el" href="array__ops_8sql__in.html#aedf6cb13eb4803bcc12dc4d95ea8ff4e" title="Checks whether one array contains the other. This function returns TRUE if each non-zero element in t...">array_contains()</a></th><td><p class="starttd">Checks whether one array contains the other. This function returns TRUE if each non-zero element in the right array equals to the element with the same index in the left array. </p>
<p class="endtd"></p>
</td></tr>
<tr>
<th><a class="el" href="array__ops_8sql__in.html#ae891429cc50705c530f3e5ca15541849" title="This function finds the maximum value in the array. NULLs are ignored. Return type is the same as the...">array_max()</a></th><td><p class="starttd">This function finds the maximum value in the array. NULLs are ignored. Return type is the same as the input type. </p>
<p class="endtd"></p>
</td></tr>
<tr>
<th><a class="el" href="array__ops_8sql__in.html#aa415256a9064aecc600dfb5e377fb7b1" title="This function finds the maximum value and corresponding index in the array. NULLs are ignored...">array_max_index()</a></th><td><p class="starttd">This function finds the maximum value and corresponding index in the array. NULLs are ignored. Return type is array in format [max, index], and its element type is the same as the input type. </p>
<p class="endtd"></p>
</td></tr>
<tr>
<th><a class="el" href="array__ops_8sql__in.html#a6659bf9d9363eb179fab34f81f8ac59b" title="This function finds the minimum value in the array. NULLs are ignored. Return type is the same as the...">array_min()</a></th><td><p class="starttd">This function finds the minimum value in the array. NULLs are ignored. Return type is the same as the input type. </p>
<p class="endtd"></p>
</td></tr>
<tr>
<th><a class="el" href="array__ops_8sql__in.html#a813a4d9ffc1c18b1b3e18f6ecdb2051f" title="This function finds the minimum value and corresponding index in the array. NULLs are ignored...">array_min_index()</a></th><td><p class="starttd">This function finds the minimum value and corresponding index in the array. NULLs are ignored. Return type is array in format [min, index], and its element type is the same as the input type. </p>
<p class="endtd"></p>
</td></tr>
<tr>
<th><a class="el" href="array__ops_8sql__in.html#a4c98f20e6a737358806f63318daea5ec" title="This function finds the sum of the values in the array. NULLs are ignored. Return type is the same as...">array_sum()</a></th><td><p class="starttd">This function finds the sum of the values in the array. NULLs are ignored. Return type is the same as the input type. </p>
<p class="endtd"></p>
</td></tr>
<tr>
<th><a class="el" href="array__ops_8sql__in.html#a418de59800833aa95f9b7cbd6b12901c" title="This function finds the sum of the values in the array. NULLs are ignored. Return type is always FLOA...">array_sum_big()</a></th><td><p class="starttd">This function finds the sum of the values in the array. NULLs are ignored. Return type is always FLOAT8 regardless of input. This function is meant to replace <a class="el" href="array__ops_8sql__in.html#a4c98f20e6a737358806f63318daea5ec" title="This function finds the sum of the values in the array. NULLs are ignored. Return type is the same as...">array_sum()</a> in cases when a sum may overflow the element type. </p>
<p class="endtd"></p>
</td></tr>
<tr>
<th><a class="el" href="array__ops_8sql__in.html#a13c0b0c53e8b0dc4e08c21bb8152ee7d" title="This function finds the sum of abs of the values in the array. NULLs are ignored. Return type is the ...">array_abs_sum()</a></th><td><p class="starttd">This function finds the sum of abs of the values in the array. NULLs are ignored. Return type is the same as the input type. </p>
<p class="endtd"></p>
</td></tr>
<tr>
<th><a class="el" href="array__ops_8sql__in.html#ac14e74c10b58f5518cd0e3e56067e5ba" title="This function takes an array as the input and finds absolute value of each element in the array...">array_abs()</a></th><td><p class="starttd">This function takes an array as the input and finds abs of each element in the array, returning the resulting array. It requires that all the values are NON-NULL. </p>
<p class="endtd"></p>
</td></tr>
<tr>
<th><a class="el" href="array__ops_8sql__in.html#a407598f9eb70637798b02fd731bfca2c" title="This function finds the mean of the values in the array. NULLs are ignored. ">array_mean()</a></th><td><p class="starttd">This function finds the mean of the values in the array. NULLs are ignored. </p>
<p class="endtd"></p>
</td></tr>
<tr>
<th><a class="el" href="array__ops_8sql__in.html#a3b6c2d173a611e6d6b184d825c2b336d" title="This function finds the standard deviation of the values in the array. NULLs are ignored. ">array_stddev()</a></th><td><p class="starttd">This function finds the standard deviation of the values in the array. NULLs are ignored. </p>
<p class="endtd"></p>
</td></tr>
<tr>
<th><a class="el" href="array__ops_8sql__in.html#ab066e65a41db78b00b4532996b2a6efc" title="This function creates an array of set size (the argument value) of FLOAT8, initializing the values to...">array_of_float()</a></th><td><p class="starttd">This function creates an array of set size (the argument value) of FLOAT8, initializing the values to 0.0. </p>
<p class="endtd"></p>
</td></tr>
<tr>
<th><a class="el" href="array__ops_8sql__in.html#ab7d8550e66d2e0bd54b8f0997d93880c" title="This function creates an array of set size (the argument value) of BIGINT, initializing the values to...">array_of_bigint()</a></th><td><p class="starttd">This function creates an array of set size (the argument value) of BIGINT, initializing the values to 0. </p>
<p class="endtd"></p>
</td></tr>
<tr>
<th><a class="el" href="array__ops_8sql__in.html#a065a5323f3b742be47e39ad8b4c90fc2" title="This functions set every values in the array to some desired value (provided as the argument)...">array_fill()</a></th><td><p class="starttd">This functions set every value in the array to some desired value (provided as the argument). </p>
<p class="endtd"></p>
</td></tr>
<tr>
<th><a class="el" href="array__ops_8sql__in.html#acc295a568878940ffc3e2c9a75990efb" title="This function takes an array as the input and keep only elements that satisfy the operator on specifi...">array_filter()</a></th><td><p class="starttd">This function takes an array as the input and keep only elements that satisfy the operator on specified scalar. It requires that the array is 1-D and all the values are NON-NULL. Return type is the same as the input type. By default, this function removes all zeros. </p>
<p class="endtd"></p>
</td></tr>
<tr>
<th><a class="el" href="array__ops_8sql__in.html#ae6881cc5c86941b6ffca35d7f3cd5c12" title="This function takes an array as the input and executes element-wise multiplication by the scalar prov...">array_scalar_mult()</a></th><td><p class="starttd">This function takes an array as the input and executes element-wise multiplication by the scalar provided as the second argument, returning the resulting array. It requires that all the values are NON-NULL. Return type is the same as the input type. </p>
<p class="endtd"></p>
</td></tr>
<tr>
<th><a class="el" href="array__ops_8sql__in.html#a0b6ffe59b12c3dee076c3059f9ab363f" title="This function takes an array as the input and executes element-wise addition by the scalar provided a...">array_scalar_add()</a></th><td><p class="starttd">This function takes an array as the input and executes element-wise addition of the scalar provided as the second argument, returning the resulting array. It requires that all the values are NON-NULL. Return type is the same as the input type. </p>
<p class="endtd"></p>
</td></tr>
<tr>
<th><a class="el" href="array__ops_8sql__in.html#a83451ed0c3ca5a9c62751dba47e45df7" title="This function takes an array as the input and finds square root of each element in the array...">array_sqrt()</a></th><td><p class="starttd">This function takes an array as the input and finds square root of each element in the array, returning the resulting array. It requires that all the values are NON-NULL. </p>
<p class="endtd"></p>
</td></tr>
<tr>
<th><a class="el" href="array__ops_8sql__in.html#a761e7ca753a5e1acf26896b37ed8b0bd" title="This function takes an array and a float8 as the input and finds power of each element in the array...">array_pow()</a></th><td><p class="starttd">This function takes an array and a float8 as the input and finds power of each element in the array, returning the resulting array. It requires that all the values are NON-NULL. </p>
<p class="endtd"></p>
</td></tr>
<tr>
<th><a class="el" href="array__ops_8sql__in.html#aff60f4091bed6374683f047c8a70ef9a" title="This function takes an array as the input and finds square of each element in the array...">array_square()</a></th><td><p class="starttd">This function takes an array as the input and finds square of each element in the array, returning the resulting array. It requires that all the values are NON-NULL. </p>
<p class="endtd"></p>
</td></tr>
<tr>
<th><a class="el" href="array__ops_8sql__in.html#acb57ea4521dcb717f9e3148e0acccc74" title="This function normalizes an array as sum of squares to be 1. ">normalize()</a></th><td><p class="starttd">This function normalizes an array as sum of squares to be 1. It requires that the array is 1-D and all the values are NON-NULL. </p>
<p class="endtd"></p>
</td></tr>
<tr>
<th><a class="el" href="array__ops_8sql__in.html#af057b589f2a2cb1095caa99feaeb3d70" title="This function takes a 2-D array as the input and unnests it by one level. It returns a set of 1-D arr...">array_unnest_2d_to_1d()</a></th><td><p class="starttd">This function takes a 2-D array as the input and unnests it by one level. It returns a set of 1-D arrays that correspond to rows of the input array as well as an ID column with values corresponding to row positions occupied by those 1-D arrays within the 2-D array. </p>
<p class="endtd"></p>
</td></tr>
</table>
</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 database table with two integer array columns and add some data. <pre class="example">
CREATE TABLE array_tbl ( id integer,
                         array1 integer[],
                         array2 integer[]
                       );
INSERT INTO array_tbl VALUES
                       ( 1, '{1,2,3,4,5,6,7,8,9}', '{9,8,7,6,5,4,3,2,1}' ),
                       ( 2, '{1,1,0,1,1,2,3,99,8}','{0,0,0,-5,4,1,1,7,6}' );
</pre></li>
<li>Find the minimum, maximum, mean, and standard deviation of the <code>array1</code> column. <pre class="example">
SELECT id, madlib.array_min(array1), madlib.array_max(array1),
           madlib.array_min_index(array1), madlib.array_max_index(array1),
           madlib.array_mean(array1), madlib.array_stddev(array1)
FROM array_tbl;
</pre> Result: <pre class="result">
id | array_min | array_max | array_min_index | array_max_index |    array_mean    |   array_stddev
----+-----------+-----------+---------------+---------------+------------------+------------------
  1 |         1 |         9 | {1,1}         | {9,9}         |                5 | 2.73861278752583
  2 |         0 |        99 | {0,3}         | {99,8}        | 12.8888888888889 | 32.3784050118457(2 rows)
</pre></li>
<li>Perform array addition and subtraction. <pre class="example">
SELECT id, madlib.array_add(array1,array2),
          madlib.array_sub(array1,array2)
FROM array_tbl;
</pre> Result: <pre class="result">
 id |          array_add           |        array_sub
&#160;---+------------------------------+-------------------------
  2 | {1,1,0,-4,5,3,4,106,14}      | {1,1,0,6,-3,1,2,92,2}
  1 | {10,10,10,10,10,10,10,10,10} | {-8,-6,-4,-2,0,2,4,6,8}
(2 rows)
</pre></li>
<li>Perform element-wise array multiplication and division. The row with <code>id=2</code> is excluded because the divisor array contains zero, which would cause a divide-by-zero error. <pre class="example">
SELECT id, madlib.array_mult(array1,array2),
           madlib.array_div(array1,array2)
FROM array_tbl
WHERE 0 != ALL(array2);</pre> Result: <pre class="result">
 id |         array_mult         |      array_div
&#160;---+----------------------------+---------------------
  1 | {9,16,21,24,25,24,21,16,9} | {0,0,0,0,1,1,2,4,9}
(1 row)
</pre></li>
<li>Calculate the dot product of the arrays. <pre class="example">
SELECT id, madlib.array_dot(array1, array2)
FROM array_tbl;
</pre> Result: <pre class="result">
 id | array_dot
&#160;---+----------
  2 |       745
  1 |       165
(2 rows)
</pre></li>
<li>Multiply an array by a scalar 3. <pre class="example">
SELECT id, madlib.array_scalar_mult(array1,3)
FROM array_tbl;
</pre> Result: <pre class="result">
 id |     array_scalar_mult
&#160;---+--------------------------
  1 | {3,6,9,12,15,18,21,24,27}
  2 | {3,3,0,3,3,6,9,297,24}
(2 rows)
</pre></li>
<li>Construct a nine-element array with each element set to the value 1.3. <pre class="example">
SELECT madlib.array_fill(madlib.array_of_float(9), 1.3::float);
</pre> Result: <pre class="result">
              array_fill
&#160;--------------------------------------
 {1.3,1.3,1.3,1.3,1.3,1.3,1.3,1.3,1.3}
(1 row)
</pre></li>
<li>Unnest a column of 2-D arrays into sets of 1-D arrays. <pre class="example">
SELECT id, (madlib.array_unnest_2d_to_1d(val)).*
FROM (
  SELECT 1::INT AS id, ARRAY[[1.3,2.0,3.2],[10.3,20.0,32.2]]::FLOAT8[][] AS val
  UNION ALL
  SELECT 2, ARRAY[[pi(),pi()/2],[2*pi(),pi()],[pi()/4,4*pi()]]::FLOAT8[][]
) t
ORDER BY 1,2;
</pre> Result: <pre class="result">
 id | unnest_row_id |            unnest_result
----+---------------+--------------------------------------
  1 |             1 | {1.3,2,3.2}
  1 |             2 | {10.3,20,32.2}
  2 |             1 | {3.14159265358979,1.5707963267949}
  2 |             2 | {6.28318530717959,3.14159265358979}
  2 |             3 | {0.785398163397448,12.5663706143592}
(5 rows)
</pre> If the function is called without the .* notation then it will return a composite record type with two attributes: the row ID and corresponding unnested array result.</li>
</ol>
<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="array__ops_8sql__in.html" title="implementation of array operations in SQL ">array_ops.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 Mon Apr 6 2020 21:46:58 for MADlib by
    <a href="http://www.doxygen.org/index.html">
    <img class="footer" src="doxygen.png" alt="doxygen"/></a> 1.8.13 </li>
  </ul>
</div>
</body>
</html>