| |
| |
| <!DOCTYPE html> |
| <html class="writer-html5" lang="en" > |
| <head> |
| <meta charset="utf-8" /> |
| |
| <meta name="viewport" content="width=device-width, initial-scale=1.0" /> |
| |
| <title>Aggregation Functions — Apache AGE master documentation</title> |
| |
| |
| |
| <link rel="stylesheet" href="../_static/css/theme.css" type="text/css" /> |
| <link rel="stylesheet" href="../_static/pygments.css" type="text/css" /> |
| <link rel="stylesheet" href="../_static/pygments.css" type="text/css" /> |
| <link rel="stylesheet" href="../_static/css/theme.css" type="text/css" /> |
| <link rel="stylesheet" href="../_static/css/custom.css" type="text/css" /> |
| |
| |
| |
| <link rel="shortcut icon" href="../_static/favicon.ico"/> |
| |
| |
| |
| |
| |
| |
| |
| |
| <!--[if lt IE 9]> |
| <script src="../_static/js/html5shiv.min.js"></script> |
| <![endif]--> |
| |
| |
| <script type="text/javascript" id="documentation_options" data-url_root="../" src="../_static/documentation_options.js"></script> |
| <script data-url_root="../" id="documentation_options" src="../_static/documentation_options.js"></script> |
| <script src="../_static/jquery.js"></script> |
| <script src="../_static/underscore.js"></script> |
| <script src="../_static/doctools.js"></script> |
| |
| <script type="text/javascript" src="../_static/js/theme.js"></script> |
| |
| |
| <link rel="index" title="Index" href="../genindex.html" /> |
| <link rel="search" title="Search" href="../search.html" /> |
| <link rel="next" title="User defined functions" href="user_functions.html" /> |
| <link rel="prev" title="String Functions" href="string_functions.html" /> |
| </head> |
| |
| <body class="wy-body-for-nav"> |
| |
| |
| <div class="wy-grid-for-nav"> |
| |
| <nav data-toggle="wy-nav-shift" class="wy-nav-side"> |
| <div class="wy-side-scroll"> |
| <div class="wy-side-nav-search" > |
| |
| |
| |
| <a href="../index.html" class="icon icon-home"> Apache AGE |
| |
| |
| |
| |
| <img src="../_static/logo.png" class="logo" alt="Logo"/> |
| |
| </a> |
| |
| |
| |
| |
| |
| |
| |
| <div role="search"> |
| <form id="rtd-search-form" class="wy-form" action="../search.html" method="get"> |
| <input type="text" name="q" placeholder="Search docs" /> |
| <input type="hidden" name="check_keywords" value="yes" /> |
| <input type="hidden" name="area" value="default" /> |
| </form> |
| </div> |
| |
| |
| </div> |
| |
| |
| <div class="wy-menu wy-menu-vertical" data-spy="affix" role="navigation" aria-label="main navigation"> |
| |
| |
| |
| |
| |
| |
| <p class="caption" role="heading"><span class="caption-text">Introduction</span></p> |
| <ul> |
| <li class="toctree-l1"><a class="reference internal" href="../intro/overview.html">Overview</a></li> |
| <li class="toctree-l1"><a class="reference internal" href="../intro/setup.html">Setup</a></li> |
| <li class="toctree-l1"><a class="reference internal" href="../intro/graphs.html">Graphs</a></li> |
| <li class="toctree-l1"><a class="reference internal" href="../intro/cypher.html">The AGE Cypher Query Format</a></li> |
| <li class="toctree-l1"><a class="reference internal" href="../intro/types.html">Data Types - An Introduction to agtype</a></li> |
| <li class="toctree-l1"><a class="reference internal" href="../intro/comparability.html">Comparability, Equality, Orderability and Equivalence</a></li> |
| <li class="toctree-l1"><a class="reference internal" href="../intro/operators.html">Operators</a></li> |
| <li class="toctree-l1"><a class="reference internal" href="../intro/aggregation.html">Aggregation</a></li> |
| </ul> |
| <p class="caption" role="heading"><span class="caption-text">Clauses</span></p> |
| <ul> |
| <li class="toctree-l1"><a class="reference internal" href="../clauses/match.html">MATCH</a></li> |
| <li class="toctree-l1"><a class="reference internal" href="../clauses/with.html">WITH</a></li> |
| <li class="toctree-l1"><a class="reference internal" href="../clauses/return.html">RETURN</a></li> |
| <li class="toctree-l1"><a class="reference internal" href="../clauses/order_by.html">ORDER BY</a></li> |
| <li class="toctree-l1"><a class="reference internal" href="../clauses/skip.html">SKIP</a></li> |
| <li class="toctree-l1"><a class="reference internal" href="../clauses/limit.html">LIMIT</a></li> |
| <li class="toctree-l1"><a class="reference internal" href="../clauses/create.html">CREATE</a></li> |
| <li class="toctree-l1"><a class="reference internal" href="../clauses/set.html">SET</a></li> |
| <li class="toctree-l1"><a class="reference internal" href="../clauses/remove.html">REMOVE</a></li> |
| <li class="toctree-l1"><a class="reference internal" href="../clauses/delete.html">DELETE</a></li> |
| </ul> |
| <p class="caption" role="heading"><span class="caption-text">Functions</span></p> |
| <ul class="current"> |
| <li class="toctree-l1"><a class="reference internal" href="predicate_functions.html">Predicate Functions</a></li> |
| <li class="toctree-l1"><a class="reference internal" href="scalar_functions.html">Scalar Functions</a></li> |
| <li class="toctree-l1"><a class="reference internal" href="list_functions.html">List Functions</a></li> |
| <li class="toctree-l1"><a class="reference internal" href="numeric_functions.html">Numeric Functions</a></li> |
| <li class="toctree-l1"><a class="reference internal" href="logarithmic_functions.html">Logarithmic Functions</a></li> |
| <li class="toctree-l1"><a class="reference internal" href="trigonometric_functions.html">Trigonometric Functions</a></li> |
| <li class="toctree-l1"><a class="reference internal" href="string_functions.html">String Functions</a></li> |
| <li class="toctree-l1 current"><a class="current reference internal" href="#">Aggregation Functions</a><ul> |
| <li class="toctree-l2"><a class="reference internal" href="#data-setup">Data Setup</a></li> |
| <li class="toctree-l2"><a class="reference internal" href="#min">min</a><ul> |
| <li class="toctree-l3"><a class="reference internal" href="#using-min-with-lists">Using min() with Lists</a></li> |
| </ul> |
| </li> |
| <li class="toctree-l2"><a class="reference internal" href="#max">max</a></li> |
| <li class="toctree-l2"><a class="reference internal" href="#stdev">stDev</a></li> |
| <li class="toctree-l2"><a class="reference internal" href="#stdevp">stDevP</a></li> |
| <li class="toctree-l2"><a class="reference internal" href="#percentilecont">percentileCont</a></li> |
| <li class="toctree-l2"><a class="reference internal" href="#percentiledisc">percentileDisc</a></li> |
| <li class="toctree-l2"><a class="reference internal" href="#count">count</a><ul> |
| <li class="toctree-l3"><a class="reference internal" href="#using-count-expression-to-return-the-number-of-values">Using count(expression) to return the number of values</a></li> |
| <li class="toctree-l3"><a class="reference internal" href="#counting-non-null-values">Counting non-null values</a></li> |
| <li class="toctree-l3"><a class="reference internal" href="#counting-with-and-without-duplicates">Counting with and without duplicates</a></li> |
| </ul> |
| </li> |
| <li class="toctree-l2"><a class="reference internal" href="#avg">avg</a></li> |
| <li class="toctree-l2"><a class="reference internal" href="#sum">sum</a></li> |
| </ul> |
| </li> |
| <li class="toctree-l1"><a class="reference internal" href="user_functions.html">User defined functions</a></li> |
| </ul> |
| <p class="caption" role="heading"><span class="caption-text">AGE Beyond Cypher</span></p> |
| <ul> |
| <li class="toctree-l1"><a class="reference internal" href="../advanced/advanced_overview.html">Overview</a></li> |
| <li class="toctree-l1"><a class="reference internal" href="../advanced/advanced.html">Using Cypher in a CTE Expression</a></li> |
| <li class="toctree-l1"><a class="reference internal" href="../advanced/advanced.html#using-cypher-in-a-join-expression">Using Cypher in a Join expression</a></li> |
| <li class="toctree-l1"><a class="reference internal" href="../advanced/advanced.html#cypher-in-sql-expressions">Cypher in SQL expressions</a></li> |
| <li class="toctree-l1"><a class="reference internal" href="../advanced/prepared_statements.html">Prepared Statements</a></li> |
| <li class="toctree-l1"><a class="reference internal" href="../advanced/plpgsql.html">PL/pgSQL Functions</a></li> |
| <li class="toctree-l1"><a class="reference internal" href="../advanced/sql_in_cypher.html">SQL In Cypher</a></li> |
| </ul> |
| |
| |
| |
| </div> |
| |
| </div> |
| </nav> |
| |
| <section data-toggle="wy-nav-shift" class="wy-nav-content-wrap"> |
| |
| |
| <nav class="wy-nav-top" aria-label="top navigation"> |
| |
| <i data-toggle="wy-nav-top" class="fa fa-bars"></i> |
| <a href="../index.html">Apache AGE</a> |
| |
| </nav> |
| |
| |
| <div class="wy-nav-content"> |
| |
| <div class="rst-content"> |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| <div role="navigation" aria-label="breadcrumbs navigation"> |
| |
| <ul class="wy-breadcrumbs"> |
| |
| <li><a href="../index.html" class="icon icon-home"></a> »</li> |
| |
| <li>Aggregation Functions</li> |
| |
| |
| <li class="wy-breadcrumbs-aside"> |
| |
| |
| |
| <a href="https://github.com/apache/incubator-age-website/blob/master/docs/functions/aggregate_functions.md" class="fa fa-github"> Edit on GitHub</a> |
| |
| |
| |
| </li> |
| |
| </ul> |
| |
| |
| <hr/> |
| </div> |
| <div role="main" class="document" itemscope="itemscope" itemtype="http://schema.org/Article"> |
| <div itemprop="articleBody"> |
| |
| <div class="tex2jax_ignore mathjax_ignore section" id="aggregation-functions"> |
| <h1>Aggregation Functions<a class="headerlink" href="#aggregation-functions" title="Permalink to this headline">¶</a></h1> |
| <p>Functions that activate <span class="xref myst">auto aggregation</span>.</p> |
| <div class="section" id="data-setup"> |
| <h2>Data Setup<a class="headerlink" href="#data-setup" title="Permalink to this headline">¶</a></h2> |
| <div class="highlight-default notranslate"><div class="highlight"><pre><span></span>LOAD 'age'; |
| SET search_path TO ag_catalog; |
| |
| SELECT create_graph('graph_name'); |
| |
| SELECT * FROM cypher('graph_name', $$ |
| CREATE (a:Person {name: 'A', age: 13}), |
| (b:Person {name: 'B', age: 33, eyes: "blue"}), |
| (c:Person {name: 'C', age: 44, eyes: "blue"}), |
| (d1:Person {name: 'D', eyes: "brown"}), |
| (d2:Person {name: 'D'}), |
| (a)-[:KNOWS]->(b), |
| (a)-[:KNOWS]->(c), |
| (a)-[:KNOWS]->(d1), |
| (b)-[:KNOWS]->(d2), |
| (c)-[:KNOWS]->(d2) |
| $$) as (a agtype); |
| </pre></div> |
| </div> |
| </div> |
| <div class="section" id="min"> |
| <h2>min<a class="headerlink" href="#min" title="Permalink to this headline">¶</a></h2> |
| <p>min() returns the minimum value in a set of values.</p> |
| <p>Syntax: <code class="docutils literal notranslate"><span class="pre">min(expression)</span></code></p> |
| <p>Returns:</p> |
| <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">A</span> <span class="nb">property</span> <span class="nb">type</span><span class="p">,</span> <span class="ow">or</span> <span class="n">a</span> <span class="nb">list</span><span class="p">,</span> <span class="n">depending</span> <span class="n">on</span> <span class="n">the</span> <span class="n">values</span> <span class="n">returned</span> <span class="n">by</span> <span class="n">expression</span><span class="o">.</span> |
| </pre></div> |
| </div> |
| <p>Arguments:</p> |
| <table> |
| <tr> |
| <td>Name |
| </td> |
| <td>Description |
| </td> |
| </tr> |
| <tr> |
| <td>expression |
| </td> |
| <td>An expression returning a set containing any combination of property types and lists thereof. |
| </td> |
| </tr> |
| </table> |
| <p>Considerations:</p> |
| <ul class="simple"> |
| <li><p>Any null values are excluded from the calculation.</p></li> |
| <li><p>In a mixed set, any string value is always considered to be lower than any numeric value, and anylist is always considered to be lower than any string.</p></li> |
| <li><p>Lists are compared in dictionary order, i.e. list elements are compared pairwise in ascending order from the start of the list to the end.</p></li> |
| <li><p>min(null) returns null.</p></li> |
| </ul> |
| <p>Query</p> |
| <div class="highlight-default notranslate"><div class="highlight"><pre><span></span>SELECT * |
| FROM cypher('graph_name', $$ |
| MATCH (v:Person) |
| RETURN min(v.age) |
| $$) as (min_age agtype); |
| </pre></div> |
| </div> |
| <p>Result:</p> |
| <table> |
| <tr> |
| <td>min_age |
| </td> |
| </tr> |
| <tr> |
| <td>2123e1af756543542064ae0d07792be90176b311be |
| </td> |
| </tr> |
| <tr> |
| <td>1 row(s) returned |
| </td> |
| </tr> |
| </table> |
| <div class="section" id="using-min-with-lists"> |
| <h3>Using min() with Lists<a class="headerlink" href="#using-min-with-lists" title="Permalink to this headline">¶</a></h3> |
| <p>Data Setup:</p> |
| <p>To clarify the following example, assume the next three commands are run first:</p> |
| <div class="highlight-default notranslate"><div class="highlight"><pre><span></span>SELECT * FROM cypher('graph_name', $$ |
| CREATE (:min_test {val:'d'}) |
| $$) as (result agtype); |
| |
| SELECT * FROM cypher('graph_name', $$ |
| CREATE (:min_test {val:['a', 'b', 23]}) |
| $$) as (result agtype); |
| |
| SELECT * FROM cypher('graph_name', $$ |
| CREATE (:min_test {val:['a', 'b', 23]}) |
| $$) as (result agtype); |
| </pre></div> |
| </div> |
| <p>Query</p> |
| <div class="highlight-default notranslate"><div class="highlight"><pre><span></span>SELECT * |
| FROM cypher('graph_name', $$ |
| MATCH (v:min_test) |
| RETURN min(v.val) |
| $$) as (min_val agtype); |
| </pre></div> |
| </div> |
| <p>The lowest of all the values in the set—in this case, the list [‘a’, ‘c’, 23]—is returned, as (i) the two lists are considered to be lower values than the string “d”, and (ii) the string “a” is considered tobe a lower value than the numerical value 1.</p> |
| <p>Result:</p> |
| <table> |
| <tr> |
| <td>min_age |
| </td> |
| </tr> |
| <tr> |
| <td>["a", "b", 23] |
| </td> |
| </tr> |
| <tr> |
| <td>1 row(s) returned |
| </td> |
| </tr> |
| </table> |
| </div> |
| </div> |
| <div class="section" id="max"> |
| <h2>max<a class="headerlink" href="#max" title="Permalink to this headline">¶</a></h2> |
| <p>max() returns the maximum value in a set of values.</p> |
| <p>Syntax: <code class="docutils literal notranslate"><span class="pre">max(expression)</span></code></p> |
| <p>Returns:</p> |
| <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">A</span> <span class="nb">property</span> <span class="nb">type</span><span class="p">,</span> <span class="ow">or</span> <span class="n">a</span> <span class="nb">list</span><span class="p">,</span> <span class="n">depending</span> <span class="n">on</span> <span class="n">the</span> <span class="n">values</span> <span class="n">returned</span> <span class="n">by</span> <span class="n">expression</span><span class="o">.</span> |
| </pre></div> |
| </div> |
| <p>Arguments:</p> |
| <table> |
| <tr> |
| <td>Name |
| </td> |
| <td>Description |
| </td> |
| </tr> |
| <tr> |
| <td>expression |
| </td> |
| <td>An expression returning a set containing any combination of property types and lists thereof. |
| </td> |
| </tr> |
| </table> |
| <p>Considerations:</p> |
| <ul class="simple"> |
| <li><p>Any null values are excluded from the calculation.</p></li> |
| <li><p>In a mixed set, any numeric value is always considered to be higher than any string value, and anystring value is always considered to be higher than any list.</p></li> |
| <li><p>Lists are compared in dictionary order, i.e. list elements are compared pairwise in ascending order from the start of the list to the end.</p></li> |
| <li><p>max(null) returns null.</p></li> |
| </ul> |
| <p>Query:</p> |
| <div class="highlight-default notranslate"><div class="highlight"><pre><span></span>SELECT * |
| FROM cypher('graph_name', $$ |
| MATCH (n:Person) |
| RETURN max(n.age) |
| $$) as (max_age agtype); |
| </pre></div> |
| </div> |
| <p>The highest of all the values in the property age is returned.</p> |
| <p>Result:</p> |
| <table> |
| <tr> |
| <td>min_age |
| </td> |
| </tr> |
| <tr> |
| <td>44 |
| </td> |
| </tr> |
| <tr> |
| <td>1 row(s) returned |
| </td> |
| </tr> |
| </table> |
| </div> |
| <div class="section" id="stdev"> |
| <h2>stDev<a class="headerlink" href="#stdev" title="Permalink to this headline">¶</a></h2> |
| <p>stDev() returns the standard deviation for the given value over a group. It uses a standard two-pass method, with N - 1 as the denominator, and should be used when taking a sample of the population for an unbiased estimate. When the standard variation of the entire population is being calculated, stdDevP should be used.</p> |
| <p>Syntax: <code class="docutils literal notranslate"><span class="pre">stDev(expression)</span></code></p> |
| <p>Returns:</p> |
| <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">An</span> <span class="n">agtype</span> <span class="n">Float</span><span class="o">.</span> |
| </pre></div> |
| </div> |
| <p>Arguments:</p> |
| <table> |
| <tr> |
| <td>Name |
| </td> |
| <td>Description |
| </td> |
| </tr> |
| <tr> |
| <td>expression |
| </td> |
| <td>An agtype number expression |
| </td> |
| </tr> |
| </table> |
| <p>Considerations:</p> |
| <ul class="simple"> |
| <li><p>Any null values are excluded from the calculation.</p></li> |
| <li><p>stDev(null) returns 0.</p></li> |
| </ul> |
| <p>Query</p> |
| <div class="highlight-default notranslate"><div class="highlight"><pre><span></span>SELECT * |
| FROM cypher('graph_name', $$ |
| MATCH (n:Person) |
| RETURN stDev(n.age) |
| $$) as (stdev_age agtype); |
| </pre></div> |
| </div> |
| <p>The standard deviation of the values in the property age is returned.</p> |
| <p>Result:</p> |
| <table> |
| <tr> |
| <td>stdev_age |
| </td> |
| </tr> |
| <tr> |
| <td>15.716233645501712 |
| </td> |
| </tr> |
| <tr> |
| <td>1 row(s) returned |
| </td> |
| </tr> |
| </table> |
| </div> |
| <div class="section" id="stdevp"> |
| <h2>stDevP<a class="headerlink" href="#stdevp" title="Permalink to this headline">¶</a></h2> |
| <p>stDevP() returns the standard deviation for the given value over a group. It uses a standard two-pass method, with N as the denominator, and should be used when calculating the standard deviation for an entire population. When the standard variation of only a sample of the population is being calculated, stDev should be used.</p> |
| <p>Syntax: <code class="docutils literal notranslate"><span class="pre">stDevP(expression)</span></code></p> |
| <p>Returns:</p> |
| <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">An</span> <span class="n">agtype</span> <span class="n">Float</span><span class="o">.</span> |
| </pre></div> |
| </div> |
| <p>Arguments:</p> |
| <table> |
| <tr> |
| <td>Name |
| </td> |
| <td>Description |
| </td> |
| </tr> |
| <tr> |
| <td>expression |
| </td> |
| <td>An agtype number expression |
| </td> |
| </tr> |
| </table> |
| <p>Considerations:</p> |
| <ul class="simple"> |
| <li><p>Any null values are excluded from the calculation.</p></li> |
| <li><p>stDevP(null) returns 0.</p></li> |
| </ul> |
| <p>Query</p> |
| <div class="highlight-default notranslate"><div class="highlight"><pre><span></span>SELECT * |
| FROM cypher('graph_name', $$ |
| MATCH (n:Person) |
| RETURN stDevP(n.age) |
| $$ as (stdevp_age agtype); |
| </pre></div> |
| </div> |
| <p>The population standard deviation of the values in the property age is returned.</p> |
| <p>Result:</p> |
| <table> |
| <tr> |
| <td>stdevp_age |
| </td> |
| </tr> |
| <tr> |
| <td>12.832251036613439 |
| </td> |
| </tr> |
| <tr> |
| <td>1 row(s) returned |
| </td> |
| </tr> |
| </table> |
| </div> |
| <div class="section" id="percentilecont"> |
| <h2>percentileCont<a class="headerlink" href="#percentilecont" title="Permalink to this headline">¶</a></h2> |
| <p>percentileCont() returns the percentile of the given value over a group, with a percentile from 0.0 to 1.0. It uses a linear interpolation method, calculating a weighted average between two values if the desired percentile lies between them. For nearest values using a rounding method, see percentileDisc.</p> |
| <p>Syntax: <code class="docutils literal notranslate"><span class="pre">percentileCont(expression,</span> <span class="pre">percentile)</span></code></p> |
| <p>Returns:</p> |
| <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">An</span> <span class="n">agtype</span> <span class="n">Float</span><span class="o">.</span> |
| </pre></div> |
| </div> |
| <p>Arguments:</p> |
| <table> |
| <tr> |
| <td>Name |
| </td> |
| <td>Description |
| </td> |
| </tr> |
| <tr> |
| <td>expression |
| </td> |
| <td>An agtype number expression |
| </td> |
| </tr> |
| <tr> |
| <td>percentile |
| </td> |
| <td>An agtype number value between 0.0 and 1.0 |
| </td> |
| </tr> |
| </table> |
| <p>Considerations:</p> |
| <ul class="simple"> |
| <li><p>Any null values are excluded from the calculation.</p></li> |
| <li><p>percentileCont(null, percentile) returns null.</p></li> |
| </ul> |
| <p>Query</p> |
| <div class="highlight-default notranslate"><div class="highlight"><pre><span></span>SELECT * |
| FROM cypher('graph_name', $$ |
| MATCH (n:Person) |
| RETURN percentileCont(n.age, 0.4) |
| $$ as (percentile_cont_age agtype); |
| </pre></div> |
| </div> |
| <p>The 40th percentile of the values in the property age is returned, calculated with a weighted average. In this case, 0.4 is the median, or 40th percentile.</p> |
| <p>Result:</p> |
| <table> |
| <tr> |
| <td>percentile_cont_age |
| </td> |
| </tr> |
| <tr> |
| <td>29.0 |
| </td> |
| </tr> |
| <tr> |
| <td>1 row(s) returned |
| </td> |
| </tr> |
| </table> |
| </div> |
| <div class="section" id="percentiledisc"> |
| <h2>percentileDisc<a class="headerlink" href="#percentiledisc" title="Permalink to this headline">¶</a></h2> |
| <p>percentileDisc() returns the percentile of the given value over a group, with a percentile from 0.0to 1.0. It uses a rounding method and calculates the nearest value to the percentile. For interpolated values, see percentileCont.</p> |
| <p>Syntax: <code class="docutils literal notranslate"><span class="pre">percentileDisc(expression,</span> <span class="pre">percentile)</span></code></p> |
| <p>Returns:</p> |
| <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">An</span> <span class="n">agtype</span> <span class="n">Float</span><span class="o">.</span> |
| </pre></div> |
| </div> |
| <p>Arguments:</p> |
| <table> |
| <tr> |
| <td>Name |
| </td> |
| <td>Description |
| </td> |
| </tr> |
| <tr> |
| <td>expression |
| </td> |
| <td>An agtype number expression |
| </td> |
| </tr> |
| <tr> |
| <td>percentile |
| </td> |
| <td>An agtype number value between 0.0 and 1.0 |
| </td> |
| </tr> |
| </table> |
| <p>Considerations:</p> |
| <ul class="simple"> |
| <li><p>Any null values are excluded from the calculation.</p></li> |
| <li><p>percentileDisc(null, percentile) returns null.</p></li> |
| </ul> |
| <p>Query</p> |
| <div class="highlight-default notranslate"><div class="highlight"><pre><span></span>SELECT * |
| FROM cypher('graph_name', $$ |
| MATCH (n:Person) |
| RETURN percentileDisc(n.age, 0.5) |
| $$ as (percentile_disc_age agtype); |
| </pre></div> |
| </div> |
| <p>The 50th percentile of the values in the property age is returned.</p> |
| <p>Result:</p> |
| <table> |
| <tr> |
| <td>percentile_cont_age |
| </td> |
| </tr> |
| <tr> |
| <td>33 |
| </td> |
| </tr> |
| <tr> |
| <td>1 row(s) returned |
| </td> |
| </tr> |
| </table> |
| </div> |
| <div class="section" id="count"> |
| <h2>count<a class="headerlink" href="#count" title="Permalink to this headline">¶</a></h2> |
| <p>count() returns the number of values or records, and appears in two variants:</p> |
| <ul class="simple"> |
| <li><p>count(*) returns the number of matching records</p></li> |
| <li><p>count(expr) returns the number of non-null values returned by an expression.</p></li> |
| </ul> |
| <p>Syntax: <code class="docutils literal notranslate"><span class="pre">count(expression)</span></code></p> |
| <p>Returns:</p> |
| <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">An</span> <span class="n">agtype</span> <span class="n">Integer</span><span class="o">.</span> |
| </pre></div> |
| </div> |
| <p>Arguments:</p> |
| <table> |
| <tr> |
| <td>Name |
| </td> |
| <td>Description |
| </td> |
| </tr> |
| <tr> |
| <td>expression |
| </td> |
| <td>An expression |
| </td> |
| </tr> |
| </table> |
| <p>Considerations:</p> |
| <ul class="simple"> |
| <li><p>count(*) includes records returning null.</p></li> |
| <li><p>count(expr) ignores null values.</p></li> |
| <li><p>count(null) returns 0.</p></li> |
| <li><p>Using count(*) to return the number of nodes</p></li> |
| <li><p>count(*) can be used to return the number of nodes; for example, the number of nodes connected to some node n.</p></li> |
| </ul> |
| <p>Query</p> |
| <div class="highlight-default notranslate"><div class="highlight"><pre><span></span>SELECT * |
| FROM cypher('graph_name', $$ |
| MATCH (n {name: 'A'})-[]->(x) |
| RETURN n.age, count(*) |
| $$ as (age agtype, number_of_people agtype); |
| </pre></div> |
| </div> |
| <p>The labels and age property of the start node n and the number of nodes related to n are returned.</p> |
| <p>Result:</p> |
| <table> |
| <tr> |
| <td>age |
| </td> |
| <td>number_of_people |
| </td> |
| </tr> |
| <tr> |
| <td>13 |
| </td> |
| <td>3 |
| </td> |
| </tr> |
| <tr> |
| <td colspan="2" >1 row(s) returned |
| </td> |
| </tr> |
| </table> |
| <p>Using count(<em>) to group and count relationship typescount(</em>) can be used to group relationship types and return the number.</p> |
| <p>Query</p> |
| <div class="highlight-default notranslate"><div class="highlight"><pre><span></span>SELECT * |
| FROM cypher('graph_name', $$ |
| MATCH (n {name: 'A'})-[r]->() |
| RETURN type(r), count(*) |
| $$ as (label agtype, count agtype); |
| </pre></div> |
| </div> |
| <p>The relationship types and their group count are returned.</p> |
| <p>Result:</p> |
| <table> |
| <tr> |
| <td>label |
| </td> |
| <td>count |
| </td> |
| </tr> |
| <tr> |
| <td>“KNOWS” |
| </td> |
| <td>3 |
| </td> |
| </tr> |
| <tr> |
| <td colspan="2" >1 row(s) returned |
| </td> |
| </tr> |
| </table> |
| <div class="section" id="using-count-expression-to-return-the-number-of-values"> |
| <h3>Using count(expression) to return the number of values<a class="headerlink" href="#using-count-expression-to-return-the-number-of-values" title="Permalink to this headline">¶</a></h3> |
| <p>Instead of simply returning the number of records with count(*), it may be more useful to return the actual number of values returned by an expression.</p> |
| <p>Query</p> |
| <div class="highlight-default notranslate"><div class="highlight"><pre><span></span>SELECT * |
| FROM cypher('graph_name', $$ |
| MATCH (n {name: 'A'})-[]->(x) |
| RETURN count(x) |
| $$) as (count agtype); |
| </pre></div> |
| </div> |
| <p>The number of nodes connected to the start node is returned.</p> |
| <p>Result:</p> |
| <table> |
| <tr> |
| <td>count |
| </td> |
| </tr> |
| <tr> |
| <td>3 |
| </td> |
| </tr> |
| <tr> |
| <td>1 row(s) returned |
| </td> |
| </tr> |
| </table> |
| </div> |
| <div class="section" id="counting-non-null-values"> |
| <h3>Counting non-null values<a class="headerlink" href="#counting-non-null-values" title="Permalink to this headline">¶</a></h3> |
| <p>count(expression) can be used to return the number of non-null values returned by the expression.</p> |
| <p>Query</p> |
| <div class="highlight-default notranslate"><div class="highlight"><pre><span></span>SELECT * |
| FROM cypher('graph_name', $$ |
| MATCH (n:Person) |
| RETURN count(n.age) |
| $$) as (count agtype); |
| </pre></div> |
| </div> |
| <p>The number of :Person nodes having an age property is returned.</p> |
| <p>Result:</p> |
| <table> |
| <tr> |
| <td>count |
| </td> |
| </tr> |
| <tr> |
| <td>3 |
| </td> |
| </tr> |
| <tr> |
| <td>1 row(s) returned |
| </td> |
| </tr> |
| </table> |
| </div> |
| <div class="section" id="counting-with-and-without-duplicates"> |
| <h3>Counting with and without duplicates<a class="headerlink" href="#counting-with-and-without-duplicates" title="Permalink to this headline">¶</a></h3> |
| <p>In this example we are trying to find all our friends of friends, and count them:</p> |
| <ul class="simple"> |
| <li><p>The first aggregate function, count(DISTINCT friend_of_friend), will only count a friend_of_friend once, as DISTINCT removes the duplicates.</p></li> |
| <li><p>The second aggregate function, count(friend_of_friend), will consider the same friend_of_friend multiple times.</p></li> |
| </ul> |
| <p>Query</p> |
| <div class="highlight-default notranslate"><div class="highlight"><pre><span></span>SELECT * |
| FROM cypher('graph_name', $$ |
| MATCH (me:Person)-[]->(friend:Person)-[]->(friend_of_friend:Person) |
| WHERE me.name = 'A' |
| RETURN count(DISTINCT friend_of_friend), count(friend_of_friend) |
| $$) as (friend_of_friends_distinct agtype, friend_of_friends agtype); |
| </pre></div> |
| </div> |
| <p>Both B and C know D and thus D will get counted twice when not using DISTINCT</p> |
| <p>Result:</p> |
| <table> |
| <tr> |
| <td>friend_of_friends_distinct |
| </td> |
| <td>friend_of_friends |
| </td> |
| </tr> |
| <tr> |
| <td>1 |
| </td> |
| <td>2 |
| </td> |
| </tr> |
| <tr> |
| <td>1 row |
| </td> |
| </tr> |
| </table> |
| </div> |
| </div> |
| <div class="section" id="avg"> |
| <h2>avg<a class="headerlink" href="#avg" title="Permalink to this headline">¶</a></h2> |
| <p>avg() returns the average of a set of numeric values.</p> |
| <p>Syntax: <code class="docutils literal notranslate"><span class="pre">avg(expression)</span></code></p> |
| <p>Returns:</p> |
| <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">An</span> <span class="n">agtype</span> <span class="n">Integer</span> |
| </pre></div> |
| </div> |
| <p>Arguments:</p> |
| <table> |
| <tr> |
| <td>Name |
| </td> |
| <td>Description |
| </td> |
| </tr> |
| <tr> |
| <td>expression |
| </td> |
| <td>An expression returning a set of numeric values. |
| </td> |
| </tr> |
| </table> |
| <p>Considerations:</p> |
| <ul class="simple"> |
| <li><p>Any null values are excluded from the calculation.</p></li> |
| <li><p>avg(null) returns null.</p></li> |
| </ul> |
| <p>Query</p> |
| <div class="highlight-default notranslate"><div class="highlight"><pre><span></span>SELECT * |
| FROM cypher('graph_name', $$ |
| MATCH (n:Person) |
| RETURN avg(n.age) |
| $$) as (avg_age agtype); |
| </pre></div> |
| </div> |
| <p>The average of all the values in the property age is returned.</p> |
| <p>Result:</p> |
| <table> |
| <tr> |
| <td>avg_age |
| </td> |
| </tr> |
| <tr> |
| <td>30.0 |
| </td> |
| </tr> |
| <tr> |
| <td>1 row(s) returned |
| </td> |
| </tr> |
| </table> |
| </div> |
| <div class="section" id="sum"> |
| <h2>sum<a class="headerlink" href="#sum" title="Permalink to this headline">¶</a></h2> |
| <p>sum() returns the sum of a set of numeric values.</p> |
| <p>Syntax: <code class="docutils literal notranslate"><span class="pre">sum(expression)</span></code></p> |
| <p>Returns:</p> |
| <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">An</span> <span class="n">agtype</span> <span class="n">Float</span> |
| </pre></div> |
| </div> |
| <p>Arguments:</p> |
| <table> |
| <tr> |
| <td>Name |
| </td> |
| <td>Description |
| </td> |
| </tr> |
| <tr> |
| <td>expression |
| </td> |
| <td>An expression returning a set of numeric values. |
| </td> |
| </tr> |
| </table> |
| <p>Considerations:</p> |
| <ul class="simple"> |
| <li><p>Any null values are excluded from the calculation.</p></li> |
| <li><p>sum(null) returns 0.</p></li> |
| </ul> |
| <p>Query</p> |
| <div class="highlight-default notranslate"><div class="highlight"><pre><span></span>SELECT * |
| FROM cypher('graph_name', $$ |
| MATCH (n:Person) |
| RETURN sum(n.age) |
| $$ as (total_age agtype); |
| </pre></div> |
| </div> |
| <p>The sum of all the values in the property age is returned.</p> |
| <p>Result:</p> |
| <table> |
| <tr> |
| <td>total_age |
| </td> |
| </tr> |
| <tr> |
| <td>90 |
| </td> |
| </tr> |
| <tr> |
| <td>1 row(s) returned |
| </td> |
| </tr> |
| </table> |
| </div> |
| </div> |
| |
| |
| </div> |
| |
| </div> |
| <footer> |
| <div class="rst-footer-buttons" role="navigation" aria-label="footer navigation"> |
| <a href="user_functions.html" class="btn btn-neutral float-right" title="User defined functions" accesskey="n" rel="next">Next <span class="fa fa-arrow-circle-right" aria-hidden="true"></span></a> |
| <a href="string_functions.html" class="btn btn-neutral float-left" title="String Functions" accesskey="p" rel="prev"><span class="fa fa-arrow-circle-left" aria-hidden="true"></span> Previous</a> |
| </div> |
| |
| <hr/> |
| |
| <div role="contentinfo"> |
| <p> |
| © Copyright 2021, Apache AGE. |
| |
| </p> |
| </div> |
| |
| |
| |
| Built with <a href="https://www.sphinx-doc.org/">Sphinx</a> using a |
| |
| <a href="https://github.com/readthedocs/sphinx_rtd_theme">theme</a> |
| |
| provided by <a href="https://readthedocs.org">Read the Docs</a>. |
| |
| </footer> |
| </div> |
| </div> |
| |
| </section> |
| |
| </div> |
| |
| <div class="rst-versions" data-toggle="rst-versions" role="note" aria-label="versions"> |
| <span class="rst-current-version" data-toggle="rst-current-version"> |
| <span class="fa fa-book"> Other Versions</span> |
| v: v0.6.0 |
| <span class="fa fa-caret-down"></span> |
| </span> |
| <div class="rst-other-versions"> |
| <dl> |
| <dt>Versions</dt> |
| <dd><a href="../../master/index.html">master</a></dd> |
| <dd><a href="aggregate_functions.html">v0.6.0</a></dd> |
| </dl> |
| </div> |
| </div> |
| |
| <script type="text/javascript"> |
| jQuery(function () { |
| SphinxRtdTheme.Navigation.enable(true); |
| }); |
| </script> |
| |
| |
| |
| |
| |
| |
| </body> |
| </html> |
