438/functions/aggregate.html


<!DOCTYPE html>

<html lang="en">
  <head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" /><meta name="generator" content="Docutils 0.18.1: http://docutils.sourceforge.net/" />

  <meta name="viewport" content="width=device-width,initial-scale=1">
  <meta http-equiv="x-ua-compatible" content="ie=edge">
  <meta name="lang:clipboard.copy" content="Copy to clipboard">
  <meta name="lang:clipboard.copied" content="Copied to clipboard">
  <meta name="lang:search.language" content="en">
  <meta name="lang:search.pipeline.stopwords" content="True">
  <meta name="lang:search.pipeline.trimmer" content="True">
  <meta name="lang:search.result.none" content="No matching documents">
  <meta name="lang:search.result.one" content="1 matching document">
  <meta name="lang:search.result.other" content="# matching documents">
  <meta name="lang:search.tokenizer" content="[\s\-]+">

  
    <link href="https://fonts.gstatic.com/" rel="preconnect" crossorigin>
    <link href="https://fonts.googleapis.com/css?family=Roboto+Mono:400,500,700|Roboto:300,400,400i,700&display=fallback" rel="stylesheet">

    <link href="https://fonts.googleapis.com/css2?family=Alata&family=Open+Sans:ital,wght%400,300;0,400;0,600;1,400&display=swap" rel="stylesheet">
    <style>
      body,
      input {
        font-family: "Roboto", "Helvetica Neue", Helvetica, Arial, sans-serif
      }

      code,
      kbd,
      pre {
        font-family: "Roboto Mono", "Courier New", Courier, monospace
      }
    </style>
  

  <link rel="stylesheet" href="../_static/stylesheets/application.css"/>
  <link rel="stylesheet" href="../_static/stylesheets/application-palette.css"/>
  <link rel="stylesheet" href="../_static/stylesheets/application-fixes.css"/>
  
  <link rel="stylesheet" href="../_static/fonts/material-icons.css"/>
  
  <meta name="theme-color" content="2196f3">
  <script src="../_static/javascripts/modernizr.js"></script>
  
  
    <title>Aggregate functions &#8212; Trino 438 Documentation</title>
    <link rel="stylesheet" type="text/css" href="../_static/pygments.css" />
    <link rel="stylesheet" type="text/css" href="../_static/material.css" />
    <link rel="stylesheet" type="text/css" href="../_static/copybutton.css" />
    <link rel="stylesheet" type="text/css" href="../_static/trino.css" />
    <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/_sphinx_javascript_frameworks_compat.js"></script>
    <script src="../_static/doctools.js"></script>
    <script src="../_static/clipboard.min.js"></script>
    <script src="../_static/copybutton.js"></script>
    <link rel="index" title="Index" href="../genindex.html" />
    <link rel="search" title="Search" href="../search.html" />
    <link rel="next" title="Array functions and operators" href="array.html" />
    <link rel="prev" title="List of functions by topic" href="list-by-topic.html" />
  
   
    <script async src="https://www.googletagmanager.com/gtag/js?id=G-RJ94STKPJ5"></script>
    <script>
      window.dataLayer = window.dataLayer || [];
      function gtag(){dataLayer.push(arguments);}
      gtag('js', new Date());
      gtag('config', 'G-RJ94STKPJ5');
      gtag('config', 'UA-133457846-1');
      gtag('config', 'AW-1036784065');
      window._linkedin_data_partner_ids = ['2842796'];
    </script>
    <script async src="https://snap.licdn.com/li.lms-analytics/insight.min.js"></script>
  </head>
  <body dir=ltr
        data-md-color-primary= data-md-color-accent=>
  
  <svg class="md-svg">
    <defs data-children-count="0">
      
      <svg xmlns="http://www.w3.org/2000/svg" width="416" height="448" viewBox="0 0 416 448" id="__github"><path fill="currentColor" d="M160 304q0 10-3.125 20.5t-10.75 19T128 352t-18.125-8.5-10.75-19T96 304t3.125-20.5 10.75-19T128 256t18.125 8.5 10.75 19T160 304zm160 0q0 10-3.125 20.5t-10.75 19T288 352t-18.125-8.5-10.75-19T256 304t3.125-20.5 10.75-19T288 256t18.125 8.5 10.75 19T320 304zm40 0q0-30-17.25-51T296 232q-10.25 0-48.75 5.25Q229.5 240 208 240t-39.25-2.75Q130.75 232 120 232q-29.5 0-46.75 21T56 304q0 22 8 38.375t20.25 25.75 30.5 15 35 7.375 37.25 1.75h42q20.5 0 37.25-1.75t35-7.375 30.5-15 20.25-25.75T360 304zm56-44q0 51.75-15.25 82.75-9.5 19.25-26.375 33.25t-35.25 21.5-42.5 11.875-42.875 5.5T212 416q-19.5 0-35.5-.75t-36.875-3.125-38.125-7.5-34.25-12.875T37 371.5t-21.5-28.75Q0 312 0 260q0-59.25 34-99-6.75-20.5-6.75-42.5 0-29 12.75-54.5 27 0 47.5 9.875t47.25 30.875Q171.5 96 212 96q37 0 70 8 26.25-20.5 46.75-30.25T376 64q12.75 25.5 12.75 54.5 0 21.75-6.75 42 34 40 34 99.5z"/></svg>
      
    </defs>
  </svg>
  
  <input class="md-toggle" data-md-toggle="drawer" type="checkbox" id="__drawer">
  <input class="md-toggle" data-md-toggle="search" type="checkbox" id="__search">
  <label class="md-overlay" data-md-component="overlay" for="__drawer"></label>
  <a href="#functions/aggregate" tabindex="1" class="md-skip"> Skip to content </a>
  <header class="md-header" data-md-component="header">
  <nav class="md-header-nav md-grid">
    <div class="md-flex navheader">
      <div class="md-flex__cell md-flex__cell--shrink">
        <a href="/" title="Trino"
           class="md-header-nav__button md-logo">
          
              <img src="../_static/trino.svg" height="26"
                   alt="Trino 438 Documentation logo">
          
        </a>
      </div>
      <div class="md-flex__cell md-flex__cell--shrink">
        <label class="md-icon md-icon--menu md-header-nav__button" for="__drawer"></label>
      </div>
      <div class="md-flex__cell md-flex__cell--stretch">
        <div class="md-flex__ellipsis md-header-nav__title" data-md-component="title">
          <span class="md-header-nav__topic">Trino 438 Documentation</span>
          <span class="md-header-nav__topic"> Aggregate functions </span>
        </div>
      </div>
      <div class="md-flex__cell md-flex__cell--shrink">
        <label class="md-icon md-icon--search md-header-nav__button" for="__search"></label>
        
<div class="md-search" data-md-component="search" role="dialog">
  <label class="md-search__overlay" for="__search"></label>
  <div class="md-search__inner" role="search">
    <form class="md-search__form" action="../search.html" method="GET" name="search">
      <input type="text" class="md-search__input" name="q" placeholder="Search"
             autocapitalize="off" autocomplete="off" spellcheck="false"
             data-md-component="query" data-md-state="active">
      <label class="md-icon md-search__icon" for="__search"></label>
      <button type="reset" class="md-icon md-search__icon" data-md-component="reset" tabindex="-1">
        &#xE5CD;
      </button>
    </form>
    <div class="md-search__output">
      <div class="md-search__scrollwrap" data-md-scrollfix>
        <div class="md-search-result" data-md-component="result">
          <div class="md-search-result__meta">
            Type to start searching
          </div>
          <ol class="md-search-result__list"></ol>
        </div>
      </div>
    </div>
  </div>
</div>

      </div>
      
        <div class="md-flex__cell md-flex__cell--shrink">
          <div class="md-header-nav__source">
            <a href="https://github.com/trinodb/trino" title="Go to repository" class="md-source" data-md-source="github">

    <div class="md-source__icon">
      <svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" viewBox="0 0 24 24" width="28" height="28">
        <use xlink:href="#__github" width="24" height="24"></use>
      </svg>
    </div>
  
  <div class="md-source__repository">
    Trino
  </div>
</a>
          </div>
        </div>
      
      
  <script src="../_static/javascripts/version_dropdown.js"></script>
  <script>
    var json_loc = "../../versions.json",
        target_loc = "../../",
        text = "Versions";
    $( document ).ready( add_version_dropdown(json_loc, target_loc, text));
  </script>
  

    </div>
  </nav>
</header>

  
  <div class="md-container">
    
    
    <!-- empty -->
    <main class="md-main">
      <div class="md-main__inner md-grid" data-md-component="container">
        
          <div class="md-sidebar md-sidebar--primary" data-md-component="navigation">
            <div class="md-sidebar__scrollwrap">
              <div class="md-sidebar__inner">
                <nav class="md-nav md-nav--primary" data-md-level="0">
  <label class="md-nav__title md-nav__title--site" for="__drawer">
    <a href="/" title="Trino" class="md-nav__button md-logo">
      
        <img src="../_static/trino.svg" alt=" logo" width="48" height="48">
      
    </a>
    <a href="../index.html"
       title="Trino 438 Documentation">Trino 438 Documentation</a>
  </label>
    <div class="md-nav__source">
      <a href="https://github.com/trinodb/trino" title="Go to repository" class="md-source" data-md-source="github">

    <div class="md-source__icon">
      <svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" viewBox="0 0 24 24" width="28" height="28">
        <use xlink:href="#__github" width="24" height="24"></use>
      </svg>
    </div>
  
  <div class="md-source__repository">
    Trino
  </div>
</a>
    </div>
  
  
  <ul class="md-nav__list">
    <li class="md-nav__item">
    
    
      <a href="../overview.html" class="md-nav__link">Overview</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../installation.html" class="md-nav__link">Installation</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../client.html" class="md-nav__link">Clients</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../security.html" class="md-nav__link">Security</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../admin.html" class="md-nav__link">Administration</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../optimizer.html" class="md-nav__link">Query optimizer</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../connector.html" class="md-nav__link">Connectors</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../functions.html" class="md-nav__link">Functions and operators</a>
      <ul class="md-nav__list"> 
    <li class="md-nav__item">
    
    
      <a href="list.html" class="md-nav__link">List of functions and operators</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="list-by-topic.html" class="md-nav__link">List of functions by topic</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
    <input class="md-toggle md-nav__toggle" data-md-toggle="toc" type="checkbox" id="__toc">
    <label class="md-nav__link md-nav__link--active" for="__toc"> Aggregate </label>
    
      <a href="#" class="md-nav__link md-nav__link--active">Aggregate</a>
      
        
<nav class="md-nav md-nav--secondary">
    <label class="md-nav__title" for="__toc">Contents</label>
    <ul class="md-nav__list" data-md-scrollfix="">
        <li class="md-nav__item"><a href="#ordering-during-aggregation" class="md-nav__link">Ordering during aggregation</a>
        </li>
        <li class="md-nav__item"><a href="#filtering-during-aggregation" class="md-nav__link">Filtering during aggregation</a>
        </li>
        <li class="md-nav__item"><a href="#general-aggregate-functions" class="md-nav__link">General aggregate functions</a>
        </li>
        <li class="md-nav__item"><a href="#bitwise-aggregate-functions" class="md-nav__link">Bitwise aggregate functions</a>
        </li>
        <li class="md-nav__item"><a href="#map-aggregate-functions" class="md-nav__link">Map aggregate functions</a>
        </li>
        <li class="md-nav__item"><a href="#approximate-aggregate-functions" class="md-nav__link">Approximate aggregate functions</a>
        </li>
        <li class="md-nav__item"><a href="#statistical-aggregate-functions" class="md-nav__link">Statistical aggregate functions</a>
        </li>
        <li class="md-nav__item"><a href="#lambda-aggregate-functions" class="md-nav__link">Lambda aggregate functions</a>
        </li>
    </ul>
</nav>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="array.html" class="md-nav__link">Array</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="binary.html" class="md-nav__link">Binary</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="bitwise.html" class="md-nav__link">Bitwise</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="color.html" class="md-nav__link">Color</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="comparison.html" class="md-nav__link">Comparison</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="conditional.html" class="md-nav__link">Conditional</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="conversion.html" class="md-nav__link">Conversion</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="datetime.html" class="md-nav__link">Date and time</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="decimal.html" class="md-nav__link">Decimal</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="geospatial.html" class="md-nav__link">Geospatial</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="hyperloglog.html" class="md-nav__link">HyperLogLog</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="ipaddress.html" class="md-nav__link">IP Address</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="json.html" class="md-nav__link">JSON</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="lambda.html" class="md-nav__link">Lambda</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="logical.html" class="md-nav__link">Logical</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="ml.html" class="md-nav__link">Machine learning</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="map.html" class="md-nav__link">Map</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="math.html" class="md-nav__link">Math</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="qdigest.html" class="md-nav__link">Quantile digest</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="regexp.html" class="md-nav__link">Regular expression</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="session.html" class="md-nav__link">Session</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="setdigest.html" class="md-nav__link">Set Digest</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="string.html" class="md-nav__link">String</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="system.html" class="md-nav__link">System</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="table.html" class="md-nav__link">Table</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="teradata.html" class="md-nav__link">Teradata</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="tdigest.html" class="md-nav__link">T-Digest</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="url.html" class="md-nav__link">URL</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="uuid.html" class="md-nav__link">UUID</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="window.html" class="md-nav__link">Window</a>
      
    
    </li></ul>
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../language.html" class="md-nav__link">SQL language</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../sql.html" class="md-nav__link">SQL statement syntax</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../routines.html" class="md-nav__link">SQL routines</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../develop.html" class="md-nav__link">Developer guide</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../glossary.html" class="md-nav__link">Glossary</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../appendix.html" class="md-nav__link">Appendix</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../release.html" class="md-nav__link">Release notes</a>
      
    
    </li>
  </ul>
  

</nav>
              </div>
            </div>
          </div>
          <div class="md-sidebar md-sidebar--secondary" data-md-component="toc">
            <div class="md-sidebar__scrollwrap">
              <div class="md-sidebar__inner">
                
<nav class="md-nav md-nav--secondary">
    <label class="md-nav__title" for="__toc">Contents</label>
    <ul class="md-nav__list" data-md-scrollfix="">
        <li class="md-nav__item"><a href="#ordering-during-aggregation" class="md-nav__link">Ordering during aggregation</a>
        </li>
        <li class="md-nav__item"><a href="#filtering-during-aggregation" class="md-nav__link">Filtering during aggregation</a>
        </li>
        <li class="md-nav__item"><a href="#general-aggregate-functions" class="md-nav__link">General aggregate functions</a>
        </li>
        <li class="md-nav__item"><a href="#bitwise-aggregate-functions" class="md-nav__link">Bitwise aggregate functions</a>
        </li>
        <li class="md-nav__item"><a href="#map-aggregate-functions" class="md-nav__link">Map aggregate functions</a>
        </li>
        <li class="md-nav__item"><a href="#approximate-aggregate-functions" class="md-nav__link">Approximate aggregate functions</a>
        </li>
        <li class="md-nav__item"><a href="#statistical-aggregate-functions" class="md-nav__link">Statistical aggregate functions</a>
        </li>
        <li class="md-nav__item"><a href="#lambda-aggregate-functions" class="md-nav__link">Lambda aggregate functions</a>
        </li>
    </ul>
</nav>
              </div>
            </div>
          </div>
        
        <div class="md-content">
          <article class="md-content__inner md-typeset" role="main">
            
  <section id="aggregate-functions">
<h1 id="functions-aggregate--page-root">Aggregate functions<a class="headerlink" href="#functions-aggregate--page-root" title="Permalink to this heading">#</a></h1>
<p>Aggregate functions operate on a set of values to compute a single result.</p>
<p>Except for <a class="reference internal" href="#count" title="count"><code class="xref py py-func docutils literal notranslate"><span class="pre">count()</span></code></a>, <a class="reference internal" href="#count_if" title="count_if"><code class="xref py py-func docutils literal notranslate"><span class="pre">count_if()</span></code></a>, <a class="reference internal" href="#max_by" title="max_by"><code class="xref py py-func docutils literal notranslate"><span class="pre">max_by()</span></code></a>, <a class="reference internal" href="#min_by" title="min_by"><code class="xref py py-func docutils literal notranslate"><span class="pre">min_by()</span></code></a> and
<a class="reference internal" href="#approx_distinct" title="approx_distinct"><code class="xref py py-func docutils literal notranslate"><span class="pre">approx_distinct()</span></code></a>, all of these aggregate functions ignore null values
and return null for no input rows or when all values are null. For example,
<a class="reference internal" href="#sum" title="sum"><code class="xref py py-func docutils literal notranslate"><span class="pre">sum()</span></code></a> returns null rather than zero and <a class="reference internal" href="#avg" title="avg"><code class="xref py py-func docutils literal notranslate"><span class="pre">avg()</span></code></a> does not include null
values in the count. The <code class="docutils literal notranslate"><span class="pre">coalesce</span></code> function can be used to convert null into
zero.</p>
<section id="ordering-during-aggregation">
<span id="aggregate-function-ordering-during-aggregation"></span><h2 id="ordering-during-aggregation">Ordering during aggregation<a class="headerlink" href="#ordering-during-aggregation" title="Permalink to this heading">#</a></h2>
<p>Some aggregate functions such as <a class="reference internal" href="#array_agg" title="array_agg"><code class="xref py py-func docutils literal notranslate"><span class="pre">array_agg()</span></code></a> produce different results
depending on the order of input values. This ordering can be specified by writing
an <a class="reference internal" href="../sql/select.html#order-by-clause"><span class="std std-ref">ORDER BY clause</span></a> within the aggregate function:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="n">array_agg</span><span class="p">(</span><span class="n">x</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">y</span> <span class="k">DESC</span><span class="p">)</span>
<span class="n">array_agg</span><span class="p">(</span><span class="n">x</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">x</span><span class="p">,</span> <span class="n">y</span><span class="p">,</span> <span class="n">z</span><span class="p">)</span>
</pre></div>
</div>
</section>
<section id="filtering-during-aggregation">
<span id="aggregate-function-filtering-during-aggregation"></span><h2 id="filtering-during-aggregation">Filtering during aggregation<a class="headerlink" href="#filtering-during-aggregation" title="Permalink to this heading">#</a></h2>
<p>The <code class="docutils literal notranslate"><span class="pre">FILTER</span></code> keyword can be used to remove rows from aggregation processing
with a condition expressed using a <code class="docutils literal notranslate"><span class="pre">WHERE</span></code> clause. This is evaluated for each
row before it is used in the aggregation and is supported for all aggregate
functions.</p>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span>aggregate_function(...) FILTER (WHERE &lt;condition&gt;)
</pre></div>
</div>
<p>A common and very useful example is to use <code class="docutils literal notranslate"><span class="pre">FILTER</span></code> to remove nulls from
consideration when using <code class="docutils literal notranslate"><span class="pre">array_agg</span></code>:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">array_agg</span><span class="p">(</span><span class="n">name</span><span class="p">)</span> <span class="n">FILTER</span> <span class="p">(</span><span class="k">WHERE</span> <span class="n">name</span> <span class="k">IS</span> <span class="k">NOT</span> <span class="k">NULL</span><span class="p">)</span>
<span class="k">FROM</span> <span class="n">region</span><span class="p">;</span>
</pre></div>
</div>
<p>As another example, imagine you want to add a condition on the count for Iris
flowers, modifying the following query:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">species</span><span class="p">,</span>
       <span class="k">count</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="k">AS</span> <span class="k">count</span>
<span class="k">FROM</span> <span class="n">iris</span>
<span class="k">GROUP</span> <span class="k">BY</span> <span class="n">species</span><span class="p">;</span>
</pre></div>
</div>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span>species    | count
-----------+-------
setosa     |   50
virginica  |   50
versicolor |   50
</pre></div>
</div>
<p>If you just use a normal <code class="docutils literal notranslate"><span class="pre">WHERE</span></code> statement you lose information:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">species</span><span class="p">,</span>
    <span class="k">count</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="k">AS</span> <span class="k">count</span>
<span class="k">FROM</span> <span class="n">iris</span>
<span class="k">WHERE</span> <span class="n">petal_length_cm</span> <span class="o">&gt;</span> <span class="mi">4</span>
<span class="k">GROUP</span> <span class="k">BY</span> <span class="n">species</span><span class="p">;</span>
</pre></div>
</div>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span>species    | count
-----------+-------
virginica  |   50
versicolor |   34
</pre></div>
</div>
<p>Using a filter you retain all information:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">species</span><span class="p">,</span>
       <span class="k">count</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="n">FILTER</span> <span class="p">(</span><span class="k">where</span> <span class="n">petal_length_cm</span> <span class="o">&gt;</span> <span class="mi">4</span><span class="p">)</span> <span class="k">AS</span> <span class="k">count</span>
<span class="k">FROM</span> <span class="n">iris</span>
<span class="k">GROUP</span> <span class="k">BY</span> <span class="n">species</span><span class="p">;</span>
</pre></div>
</div>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span>species    | count
-----------+-------
virginica  |   50
setosa     |    0
versicolor |   34
</pre></div>
</div>
</section>
<section id="general-aggregate-functions">
<h2 id="general-aggregate-functions">General aggregate functions<a class="headerlink" href="#general-aggregate-functions" title="Permalink to this heading">#</a></h2>
<dl class="py function">
<dt class="sig sig-object py" id="any_value">
<span class="sig-name descname"><span class="pre">any_value</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">x</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">[same</span> <span class="pre">as</span> <span class="pre">input]</span></span></span><a class="headerlink" href="#any_value" title="Permalink to this definition">#</a></dt>
<dd><p>Returns an arbitrary non-null value <code class="docutils literal notranslate"><span class="pre">x</span></code>, if one exists. <code class="docutils literal notranslate"><span class="pre">x</span></code> can be any
valid expression. This allows you to return values from columns that are not
directly part of the aggregation, inluding expressions using these columns,
in a query.</p>
<p>For example, the following query returns the customer name from the <code class="docutils literal notranslate"><span class="pre">name</span></code>
column, and returns the sum of all total prices as customer spend. The
aggregation however uses the rows grouped by the customer identifier
<code class="docutils literal notranslate"><span class="pre">custkey</span></code> a required, since only that column is guaranteed to be unique:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="k">sum</span><span class="p">(</span><span class="n">o</span><span class="p">.</span><span class="n">totalprice</span><span class="p">)</span> <span class="k">as</span> <span class="n">spend</span><span class="p">,</span>
    <span class="n">any_value</span><span class="p">(</span><span class="k">c</span><span class="p">.</span><span class="n">name</span><span class="p">)</span>
<span class="k">FROM</span> <span class="n">tpch</span><span class="p">.</span><span class="n">tiny</span><span class="p">.</span><span class="n">orders</span> <span class="n">o</span>
<span class="k">JOIN</span> <span class="n">tpch</span><span class="p">.</span><span class="n">tiny</span><span class="p">.</span><span class="n">customer</span> <span class="k">c</span>
<span class="k">ON</span> <span class="n">o</span><span class="p">.</span><span class="n">custkey</span>  <span class="o">=</span> <span class="k">c</span><span class="p">.</span><span class="n">custkey</span>
<span class="k">GROUP</span> <span class="k">BY</span> <span class="k">c</span><span class="p">.</span><span class="n">custkey</span><span class="p">;</span>
<span class="k">ORDER</span> <span class="k">BY</span> <span class="n">spend</span><span class="p">;</span>
</pre></div>
</div>
</dd></dl>
<dl class="py function">
<dt class="sig sig-object py" id="arbitrary">
<span class="sig-name descname"><span class="pre">arbitrary</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">x</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">[same</span> <span class="pre">as</span> <span class="pre">input]</span></span></span><a class="headerlink" href="#arbitrary" title="Permalink to this definition">#</a></dt>
<dd><p>Returns an arbitrary non-null value of <code class="docutils literal notranslate"><span class="pre">x</span></code>, if one exists. Identical to
<a class="reference internal" href="#any_value" title="any_value"><code class="xref py py-func docutils literal notranslate"><span class="pre">any_value()</span></code></a>.</p>
</dd></dl>
<dl class="py function">
<dt class="sig sig-object py" id="array_agg">
<span class="sig-name descname"><span class="pre">array_agg</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">x</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">array&lt;[same</span> <span class="pre">as</span> <span class="pre">input]&gt;</span></span></span><a class="headerlink" href="#array_agg" title="Permalink to this definition">#</a></dt>
<dd><p>Returns an array created from the input <code class="docutils literal notranslate"><span class="pre">x</span></code> elements.</p>
</dd></dl>
<dl class="py function">
<dt class="sig sig-object py" id="avg">
<span class="sig-name descname"><span class="pre">avg</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">x</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">double</span></span></span><a class="headerlink" href="#avg" title="Permalink to this definition">#</a></dt>
<dd><p>Returns the average (arithmetic mean) of all input values.</p>
</dd></dl>
<dl class="py function">
<dt class="sig sig-object py">
<span class="sig-name descname"><span class="pre">avg</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">time</span> <span class="pre">interval</span> <span class="pre">type</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">time</span> <span class="pre">interval</span> <span class="pre">type</span></span></span></dt>
<dd><p>Returns the average interval length of all input values.</p>
</dd></dl>
<dl class="py function">
<dt class="sig sig-object py" id="bool_and">
<span class="sig-name descname"><span class="pre">bool_and</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">boolean</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">boolean</span></span></span><a class="headerlink" href="#bool_and" title="Permalink to this definition">#</a></dt>
<dd><p>Returns <code class="docutils literal notranslate"><span class="pre">TRUE</span></code> if every input value is <code class="docutils literal notranslate"><span class="pre">TRUE</span></code>, otherwise <code class="docutils literal notranslate"><span class="pre">FALSE</span></code>.</p>
</dd></dl>
<dl class="py function">
<dt class="sig sig-object py" id="bool_or">
<span class="sig-name descname"><span class="pre">bool_or</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">boolean</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">boolean</span></span></span><a class="headerlink" href="#bool_or" title="Permalink to this definition">#</a></dt>
<dd><p>Returns <code class="docutils literal notranslate"><span class="pre">TRUE</span></code> if any input value is <code class="docutils literal notranslate"><span class="pre">TRUE</span></code>, otherwise <code class="docutils literal notranslate"><span class="pre">FALSE</span></code>.</p>
</dd></dl>
<dl class="py function">
<dt class="sig sig-object py" id="checksum">
<span class="sig-name descname"><span class="pre">checksum</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">x</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">varbinary</span></span></span><a class="headerlink" href="#checksum" title="Permalink to this definition">#</a></dt>
<dd><p>Returns an order-insensitive checksum of the given values.</p>
</dd></dl>
<dl class="py function">
<dt class="sig sig-object py" id="count">
<span class="sig-name descname"><span class="pre">count</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">*</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">bigint</span></span></span><a class="headerlink" href="#count" title="Permalink to this definition">#</a></dt>
<dd><p>Returns the number of input rows.</p>
</dd></dl>
<dl class="py function">
<dt class="sig sig-object py">
<span class="sig-name descname"><span class="pre">count</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">x</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">bigint</span></span></span></dt>
<dd><p>Returns the number of non-null input values.</p>
</dd></dl>
<dl class="py function">
<dt class="sig sig-object py" id="count_if">
<span class="sig-name descname"><span class="pre">count_if</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">x</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">bigint</span></span></span><a class="headerlink" href="#count_if" title="Permalink to this definition">#</a></dt>
<dd><p>Returns the number of <code class="docutils literal notranslate"><span class="pre">TRUE</span></code> input values.
This function is equivalent to <code class="docutils literal notranslate"><span class="pre">count(CASE</span> <span class="pre">WHEN</span> <span class="pre">x</span> <span class="pre">THEN</span> <span class="pre">1</span> <span class="pre">END)</span></code>.</p>
</dd></dl>
<dl class="py function">
<dt class="sig sig-object py" id="every">
<span class="sig-name descname"><span class="pre">every</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">boolean</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">boolean</span></span></span><a class="headerlink" href="#every" title="Permalink to this definition">#</a></dt>
<dd><p>This is an alias for <a class="reference internal" href="#bool_and" title="bool_and"><code class="xref py py-func docutils literal notranslate"><span class="pre">bool_and()</span></code></a>.</p>
</dd></dl>
<dl class="py function">
<dt class="sig sig-object py" id="geometric_mean">
<span class="sig-name descname"><span class="pre">geometric_mean</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">x</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">double</span></span></span><a class="headerlink" href="#geometric_mean" title="Permalink to this definition">#</a></dt>
<dd><p>Returns the geometric mean of all input values.</p>
</dd></dl>
<dl class="py function">
<dt class="sig sig-object py" id="listagg">
<span class="sig-name descname"><span class="pre">listagg</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">x</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">separator</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">varchar</span></span></span><a class="headerlink" href="#listagg" title="Permalink to this definition">#</a></dt>
<dd><p>Returns the concatenated input values, separated by the <code class="docutils literal notranslate"><span class="pre">separator</span></code> string.</p>
<p>Synopsis:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="n">LISTAGG</span><span class="p">(</span> <span class="n">expression</span> <span class="p">[,</span> <span class="n">separator</span><span class="p">]</span> <span class="p">[</span><span class="k">ON</span> <span class="n">OVERFLOW</span> <span class="n">overflow_behaviour</span><span class="p">])</span>
    <span class="n">WITHIN</span> <span class="k">GROUP</span> <span class="p">(</span><span class="k">ORDER</span> <span class="k">BY</span> <span class="n">sort_item</span><span class="p">,</span> <span class="p">[...])</span> <span class="p">[</span><span class="n">FILTER</span> <span class="p">(</span><span class="k">WHERE</span> <span class="n">condition</span><span class="p">)]</span>
</pre></div>
</div>
<p>If <code class="docutils literal notranslate"><span class="pre">separator</span></code> is not specified, the empty string will be used as <code class="docutils literal notranslate"><span class="pre">separator</span></code>.</p>
<p>In its simplest form the function looks like:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">listagg</span><span class="p">(</span><span class="n">value</span><span class="p">,</span> <span class="s1">','</span><span class="p">)</span> <span class="n">WITHIN</span> <span class="k">GROUP</span> <span class="p">(</span><span class="k">ORDER</span> <span class="k">BY</span> <span class="n">value</span><span class="p">)</span> <span class="n">csv_value</span>
<span class="k">FROM</span> <span class="p">(</span><span class="k">VALUES</span> <span class="s1">'a'</span><span class="p">,</span> <span class="s1">'c'</span><span class="p">,</span> <span class="s1">'b'</span><span class="p">)</span> <span class="n">t</span><span class="p">(</span><span class="n">value</span><span class="p">);</span>
</pre></div>
</div>
<p>and results in:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="n">csv_value</span>
<span class="c1">-----------</span>
<span class="s1">'a,b,c'</span>
</pre></div>
</div>
<p>The overflow behaviour is by default to throw an error in case that the length of the output
of the function exceeds <code class="docutils literal notranslate"><span class="pre">1048576</span></code> bytes:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">listagg</span><span class="p">(</span><span class="n">value</span><span class="p">,</span> <span class="s1">','</span> <span class="k">ON</span> <span class="n">OVERFLOW</span> <span class="n">ERROR</span><span class="p">)</span> <span class="n">WITHIN</span> <span class="k">GROUP</span> <span class="p">(</span><span class="k">ORDER</span> <span class="k">BY</span> <span class="n">value</span><span class="p">)</span> <span class="n">csv_value</span>
<span class="k">FROM</span> <span class="p">(</span><span class="k">VALUES</span> <span class="s1">'a'</span><span class="p">,</span> <span class="s1">'b'</span><span class="p">,</span> <span class="s1">'c'</span><span class="p">)</span> <span class="n">t</span><span class="p">(</span><span class="n">value</span><span class="p">);</span>
</pre></div>
</div>
<p>There exists also the possibility to truncate the output <code class="docutils literal notranslate"><span class="pre">WITH</span> <span class="pre">COUNT</span></code> or <code class="docutils literal notranslate"><span class="pre">WITHOUT</span> <span class="pre">COUNT</span></code>
of omitted non-null values in case that the length of the output of the
function exceeds <code class="docutils literal notranslate"><span class="pre">1048576</span></code> bytes:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">listagg</span><span class="p">(</span><span class="n">value</span><span class="p">,</span> <span class="s1">','</span> <span class="k">ON</span> <span class="n">OVERFLOW</span> <span class="k">TRUNCATE</span> <span class="s1">'.....'</span> <span class="k">WITH</span> <span class="k">COUNT</span><span class="p">)</span> <span class="n">WITHIN</span> <span class="k">GROUP</span> <span class="p">(</span><span class="k">ORDER</span> <span class="k">BY</span> <span class="n">value</span><span class="p">)</span>
<span class="k">FROM</span> <span class="p">(</span><span class="k">VALUES</span> <span class="s1">'a'</span><span class="p">,</span> <span class="s1">'b'</span><span class="p">,</span> <span class="s1">'c'</span><span class="p">)</span> <span class="n">t</span><span class="p">(</span><span class="n">value</span><span class="p">);</span>
</pre></div>
</div>
<p>If not specified, the truncation filler string is by default <code class="docutils literal notranslate"><span class="pre">'...'</span></code>.</p>
<p>This aggregation function can be also used in a scenario involving grouping:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">id</span><span class="p">,</span> <span class="n">listagg</span><span class="p">(</span><span class="n">value</span><span class="p">,</span> <span class="s1">','</span><span class="p">)</span> <span class="n">WITHIN</span> <span class="k">GROUP</span> <span class="p">(</span><span class="k">ORDER</span> <span class="k">BY</span> <span class="n">o</span><span class="p">)</span> <span class="n">csv_value</span>
<span class="k">FROM</span> <span class="p">(</span><span class="k">VALUES</span>
    <span class="p">(</span><span class="mi">100</span><span class="p">,</span> <span class="mi">1</span><span class="p">,</span> <span class="s1">'a'</span><span class="p">),</span>
    <span class="p">(</span><span class="mi">200</span><span class="p">,</span> <span class="mi">3</span><span class="p">,</span> <span class="s1">'c'</span><span class="p">),</span>
    <span class="p">(</span><span class="mi">200</span><span class="p">,</span> <span class="mi">2</span><span class="p">,</span> <span class="s1">'b'</span><span class="p">)</span>
<span class="p">)</span> <span class="n">t</span><span class="p">(</span><span class="n">id</span><span class="p">,</span> <span class="n">o</span><span class="p">,</span> <span class="n">value</span><span class="p">)</span>
<span class="k">GROUP</span> <span class="k">BY</span> <span class="n">id</span>
<span class="k">ORDER</span> <span class="k">BY</span> <span class="n">id</span><span class="p">;</span>
</pre></div>
</div>
<p>results in:</p>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span> id  | csv_value
-----+-----------
 100 | a
 200 | b,c
</pre></div>
</div>
<p>This aggregation function supports
<a class="reference internal" href="#aggregate-function-filtering-during-aggregation"><span class="std std-ref">filtering during aggregation</span></a>
for scenarios where the aggregation for the data not matching the filter
condition still needs to show up in the output:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> 
    <span class="n">country</span><span class="p">,</span>
    <span class="n">listagg</span><span class="p">(</span><span class="n">city</span><span class="p">,</span> <span class="s1">','</span><span class="p">)</span>
        <span class="n">WITHIN</span> <span class="k">GROUP</span> <span class="p">(</span><span class="k">ORDER</span> <span class="k">BY</span> <span class="n">population</span> <span class="k">DESC</span><span class="p">)</span>
        <span class="n">FILTER</span> <span class="p">(</span><span class="k">WHERE</span> <span class="n">population</span> <span class="o">&gt;=</span> <span class="mi">10</span><span class="n">_000_000</span><span class="p">)</span> <span class="n">megacities</span>
<span class="k">FROM</span> <span class="p">(</span><span class="k">VALUES</span> 
    <span class="p">(</span><span class="s1">'India'</span><span class="p">,</span> <span class="s1">'Bangalore'</span><span class="p">,</span> <span class="mi">13</span><span class="n">_700_000</span><span class="p">),</span>
    <span class="p">(</span><span class="s1">'India'</span><span class="p">,</span> <span class="s1">'Chennai'</span><span class="p">,</span> <span class="mi">12</span><span class="n">_200_000</span><span class="p">),</span>
    <span class="p">(</span><span class="s1">'India'</span><span class="p">,</span> <span class="s1">'Ranchi'</span><span class="p">,</span> <span class="mi">1</span><span class="n">_547_000</span><span class="p">),</span>
    <span class="p">(</span><span class="s1">'Austria'</span><span class="p">,</span> <span class="s1">'Vienna'</span><span class="p">,</span> <span class="mi">1</span><span class="n">_897_000</span><span class="p">),</span>
    <span class="p">(</span><span class="s1">'Poland'</span><span class="p">,</span> <span class="s1">'Warsaw'</span><span class="p">,</span> <span class="mi">1</span><span class="n">_765_000</span><span class="p">)</span>
<span class="p">)</span> <span class="n">t</span><span class="p">(</span><span class="n">country</span><span class="p">,</span> <span class="n">city</span><span class="p">,</span> <span class="n">population</span><span class="p">)</span>
<span class="k">GROUP</span> <span class="k">BY</span> <span class="n">country</span>
<span class="k">ORDER</span> <span class="k">BY</span> <span class="n">country</span><span class="p">;</span>
</pre></div>
</div>
<p>results in:</p>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span> country |    megacities     
---------+-------------------
 Austria | NULL              
 India   | Bangalore,Chennai 
 Poland  | NULL
</pre></div>
</div>
<p>The current implementation of <code class="docutils literal notranslate"><span class="pre">listagg</span></code> function does not support window frames.</p>
</dd></dl>
<dl class="py function">
<dt class="sig sig-object py" id="max">
<span class="sig-name descname"><span class="pre">max</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">x</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">[same</span> <span class="pre">as</span> <span class="pre">input]</span></span></span><a class="headerlink" href="#max" title="Permalink to this definition">#</a></dt>
<dd><p>Returns the maximum value of all input values.</p>
</dd></dl>
<dl class="py function">
<dt class="sig sig-object py">
<span class="sig-name descname"><span class="pre">max</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">x</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">n</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">array&lt;[same</span> <span class="pre">as</span> <span class="pre">x]&gt;</span></span></span></dt>
<dd><p>Returns <code class="docutils literal notranslate"><span class="pre">n</span></code> largest values of all input values of <code class="docutils literal notranslate"><span class="pre">x</span></code>.</p>
</dd></dl>
<dl class="py function">
<dt class="sig sig-object py" id="max_by">
<span class="sig-name descname"><span class="pre">max_by</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">x</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">y</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">[same</span> <span class="pre">as</span> <span class="pre">x]</span></span></span><a class="headerlink" href="#max_by" title="Permalink to this definition">#</a></dt>
<dd><p>Returns the value of <code class="docutils literal notranslate"><span class="pre">x</span></code> associated with the maximum value of <code class="docutils literal notranslate"><span class="pre">y</span></code> over all input values.</p>
</dd></dl>
<dl class="py function">
<dt class="sig sig-object py">
<span class="sig-name descname"><span class="pre">max_by</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">x</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">y</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">n</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">array&lt;[same</span> <span class="pre">as</span> <span class="pre">x]&gt;</span></span></span></dt>
<dd><p>Returns <code class="docutils literal notranslate"><span class="pre">n</span></code> values of <code class="docutils literal notranslate"><span class="pre">x</span></code> associated with the <code class="docutils literal notranslate"><span class="pre">n</span></code> largest of all input values of <code class="docutils literal notranslate"><span class="pre">y</span></code>
in descending order of <code class="docutils literal notranslate"><span class="pre">y</span></code>.</p>
</dd></dl>
<dl class="py function">
<dt class="sig sig-object py" id="min">
<span class="sig-name descname"><span class="pre">min</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">x</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">[same</span> <span class="pre">as</span> <span class="pre">input]</span></span></span><a class="headerlink" href="#min" title="Permalink to this definition">#</a></dt>
<dd><p>Returns the minimum value of all input values.</p>
</dd></dl>
<dl class="py function">
<dt class="sig sig-object py">
<span class="sig-name descname"><span class="pre">min</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">x</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">n</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">array&lt;[same</span> <span class="pre">as</span> <span class="pre">x]&gt;</span></span></span></dt>
<dd><p>Returns <code class="docutils literal notranslate"><span class="pre">n</span></code> smallest values of all input values of <code class="docutils literal notranslate"><span class="pre">x</span></code>.</p>
</dd></dl>
<dl class="py function">
<dt class="sig sig-object py" id="min_by">
<span class="sig-name descname"><span class="pre">min_by</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">x</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">y</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">[same</span> <span class="pre">as</span> <span class="pre">x]</span></span></span><a class="headerlink" href="#min_by" title="Permalink to this definition">#</a></dt>
<dd><p>Returns the value of <code class="docutils literal notranslate"><span class="pre">x</span></code> associated with the minimum value of <code class="docutils literal notranslate"><span class="pre">y</span></code> over all input values.</p>
</dd></dl>
<dl class="py function">
<dt class="sig sig-object py">
<span class="sig-name descname"><span class="pre">min_by</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">x</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">y</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">n</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">array&lt;[same</span> <span class="pre">as</span> <span class="pre">x]&gt;</span></span></span></dt>
<dd><p>Returns <code class="docutils literal notranslate"><span class="pre">n</span></code> values of <code class="docutils literal notranslate"><span class="pre">x</span></code> associated with the <code class="docutils literal notranslate"><span class="pre">n</span></code> smallest of all input values of <code class="docutils literal notranslate"><span class="pre">y</span></code>
in ascending order of <code class="docutils literal notranslate"><span class="pre">y</span></code>.</p>
</dd></dl>
<dl class="py function">
<dt class="sig sig-object py" id="sum">
<span class="sig-name descname"><span class="pre">sum</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">x</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">[same</span> <span class="pre">as</span> <span class="pre">input]</span></span></span><a class="headerlink" href="#sum" title="Permalink to this definition">#</a></dt>
<dd><p>Returns the sum of all input values.</p>
</dd></dl>
</section>
<section id="bitwise-aggregate-functions">
<h2 id="bitwise-aggregate-functions">Bitwise aggregate functions<a class="headerlink" href="#bitwise-aggregate-functions" title="Permalink to this heading">#</a></h2>
<dl class="py function">
<dt class="sig sig-object py" id="bitwise_and_agg">
<span class="sig-name descname"><span class="pre">bitwise_and_agg</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">x</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">bigint</span></span></span><a class="headerlink" href="#bitwise_and_agg" title="Permalink to this definition">#</a></dt>
<dd><p>Returns the bitwise AND of all input values in 2’s complement representation.</p>
</dd></dl>
<dl class="py function">
<dt class="sig sig-object py" id="bitwise_or_agg">
<span class="sig-name descname"><span class="pre">bitwise_or_agg</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">x</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">bigint</span></span></span><a class="headerlink" href="#bitwise_or_agg" title="Permalink to this definition">#</a></dt>
<dd><p>Returns the bitwise OR of all input values in 2’s complement representation.</p>
</dd></dl>
</section>
<section id="map-aggregate-functions">
<h2 id="map-aggregate-functions">Map aggregate functions<a class="headerlink" href="#map-aggregate-functions" title="Permalink to this heading">#</a></h2>
<dl class="py function">
<dt class="sig sig-object py" id="histogram">
<span class="sig-name descname"><span class="pre">histogram</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">x</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">map&lt;K,bigint&gt;</span></span></span><a class="headerlink" href="#histogram" title="Permalink to this definition">#</a></dt>
<dd><p>Returns a map containing the count of the number of times each input value occurs.</p>
</dd></dl>
<dl class="py function">
<dt class="sig sig-object py" id="map_agg">
<span class="sig-name descname"><span class="pre">map_agg</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">key</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">value</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">map&lt;K,V&gt;</span></span></span><a class="headerlink" href="#map_agg" title="Permalink to this definition">#</a></dt>
<dd><p>Returns a map created from the input <code class="docutils literal notranslate"><span class="pre">key</span></code> / <code class="docutils literal notranslate"><span class="pre">value</span></code> pairs.</p>
</dd></dl>
<dl class="py function">
<dt class="sig sig-object py" id="map_union">
<span class="sig-name descname"><span class="pre">map_union</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">x(K</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">V)</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">map&lt;K,V&gt;</span></span></span><a class="headerlink" href="#map_union" title="Permalink to this definition">#</a></dt>
<dd><p>Returns the union of all the input maps. If a key is found in multiple
input maps, that key’s value in the resulting map comes from an arbitrary input map.</p>
<p>For example, take the following histogram function that creates multiple maps from the Iris dataset:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">histogram</span><span class="p">(</span><span class="n">floor</span><span class="p">(</span><span class="n">petal_length_cm</span><span class="p">))</span> <span class="n">petal_data</span>
<span class="k">FROM</span> <span class="n">memory</span><span class="p">.</span><span class="k">default</span><span class="p">.</span><span class="n">iris</span>
<span class="k">GROUP</span> <span class="k">BY</span> <span class="n">species</span><span class="p">;</span>

        <span class="n">petal_data</span>
<span class="c1">-- {4.0=6, 5.0=33, 6.0=11}</span>
<span class="c1">-- {4.0=37, 5.0=2, 3.0=11}</span>
<span class="c1">-- {1.0=50}</span>
</pre></div>
</div>
<p>You can combine these maps using <code class="docutils literal notranslate"><span class="pre">map_union</span></code>:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">map_union</span><span class="p">(</span><span class="n">petal_data</span><span class="p">)</span> <span class="n">petal_data_union</span>
<span class="k">FROM</span> <span class="p">(</span>
       <span class="k">SELECT</span> <span class="n">histogram</span><span class="p">(</span><span class="n">floor</span><span class="p">(</span><span class="n">petal_length_cm</span><span class="p">))</span> <span class="n">petal_data</span>
       <span class="k">FROM</span> <span class="n">memory</span><span class="p">.</span><span class="k">default</span><span class="p">.</span><span class="n">iris</span>
       <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">species</span>
       <span class="p">);</span>

             <span class="n">petal_data_union</span>
<span class="c1">--{4.0=6, 5.0=2, 6.0=11, 1.0=50, 3.0=11}</span>
</pre></div>
</div>
</dd></dl>
<dl class="py function">
<dt class="sig sig-object py" id="multimap_agg">
<span class="sig-name descname"><span class="pre">multimap_agg</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">key</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">value</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">map&lt;K,array(V)&gt;</span></span></span><a class="headerlink" href="#multimap_agg" title="Permalink to this definition">#</a></dt>
<dd><p>Returns a multimap created from the input <code class="docutils literal notranslate"><span class="pre">key</span></code> / <code class="docutils literal notranslate"><span class="pre">value</span></code> pairs.
Each key can be associated with multiple values.</p>
</dd></dl>
</section>
<section id="approximate-aggregate-functions">
<h2 id="approximate-aggregate-functions">Approximate aggregate functions<a class="headerlink" href="#approximate-aggregate-functions" title="Permalink to this heading">#</a></h2>
<dl class="py function">
<dt class="sig sig-object py" id="approx_distinct">
<span class="sig-name descname"><span class="pre">approx_distinct</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">x</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">bigint</span></span></span><a class="headerlink" href="#approx_distinct" title="Permalink to this definition">#</a></dt>
<dd><p>Returns the approximate number of distinct input values.
This function provides an approximation of <code class="docutils literal notranslate"><span class="pre">count(DISTINCT</span> <span class="pre">x)</span></code>.
Zero is returned if all input values are null.</p>
<p>This function should produce a standard error of 2.3%, which is the
standard deviation of the (approximately normal) error distribution over
all possible sets. It does not guarantee an upper bound on the error for
any specific input set.</p>
</dd></dl>
<dl class="py function">
<dt class="sig sig-object py">
<span class="sig-name descname"><span class="pre">approx_distinct</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">x</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">e</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">bigint</span></span></span></dt>
<dd><p>Returns the approximate number of distinct input values.
This function provides an approximation of <code class="docutils literal notranslate"><span class="pre">count(DISTINCT</span> <span class="pre">x)</span></code>.
Zero is returned if all input values are null.</p>
<p>This function should produce a standard error of no more than <code class="docutils literal notranslate"><span class="pre">e</span></code>, which
is the standard deviation of the (approximately normal) error distribution
over all possible sets. It does not guarantee an upper bound on the error
for any specific input set. The current implementation of this function
requires that <code class="docutils literal notranslate"><span class="pre">e</span></code> be in the range of <code class="docutils literal notranslate"><span class="pre">[0.0040625,</span> <span class="pre">0.26000]</span></code>.</p>
</dd></dl>
<dl class="py function">
<dt class="sig sig-object py" id="approx_most_frequent">
<span class="sig-name descname"><span class="pre">approx_most_frequent</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">buckets</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">value</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">capacity</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">map&lt;[same</span> <span class="pre">as</span> <span class="pre">value],</span> <span class="pre">bigint&gt;</span></span></span><a class="headerlink" href="#approx_most_frequent" title="Permalink to this definition">#</a></dt>
<dd><p>Computes the top frequent values up to <code class="docutils literal notranslate"><span class="pre">buckets</span></code> elements approximately.
Approximate estimation of the function enables us to pick up the frequent
values with less memory. Larger <code class="docutils literal notranslate"><span class="pre">capacity</span></code> improves the accuracy of
underlying algorithm with sacrificing the memory capacity. The returned
value is a map containing the top elements with corresponding estimated
frequency.</p>
<p>The error of the function depends on the permutation of the values and its
cardinality. We can set the capacity same as the cardinality of the
underlying data to achieve the least error.</p>
<p><code class="docutils literal notranslate"><span class="pre">buckets</span></code> and <code class="docutils literal notranslate"><span class="pre">capacity</span></code> must be <code class="docutils literal notranslate"><span class="pre">bigint</span></code>. <code class="docutils literal notranslate"><span class="pre">value</span></code> can be numeric
or string type.</p>
<p>The function uses the stream summary data structure proposed in the paper
<a class="reference external" href="https://www.cse.ust.hk/~raywong/comp5331/References/EfficientComputationOfFrequentAndTop-kElementsInDataStreams.pdf">Efficient Computation of Frequent and Top-k Elements in Data Streams</a>
by A. Metwalley, D. Agrawl and A. Abbadi.</p>
</dd></dl>
<dl class="py function">
<dt class="sig sig-object py" id="approx_percentile">
<span class="sig-name descname"><span class="pre">approx_percentile</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">x</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">percentage</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">[same</span> <span class="pre">as</span> <span class="pre">x]</span></span></span><a class="headerlink" href="#approx_percentile" title="Permalink to this definition">#</a></dt>
<dd><p>Returns the approximate percentile for all input values of <code class="docutils literal notranslate"><span class="pre">x</span></code> at the
given <code class="docutils literal notranslate"><span class="pre">percentage</span></code>. The value of <code class="docutils literal notranslate"><span class="pre">percentage</span></code> must be between zero and
one and must be constant for all input rows.</p>
</dd></dl>
<dl class="py function">
<dt class="sig sig-object py">
<span class="sig-name descname"><span class="pre">approx_percentile</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">x</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">percentages</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">array&lt;[same</span> <span class="pre">as</span> <span class="pre">x]&gt;</span></span></span></dt>
<dd><p>Returns the approximate percentile for all input values of <code class="docutils literal notranslate"><span class="pre">x</span></code> at each of
the specified percentages. Each element of the <code class="docutils literal notranslate"><span class="pre">percentages</span></code> array must be
between zero and one, and the array must be constant for all input rows.</p>
</dd></dl>
<dl class="py function">
<dt class="sig sig-object py">
<span class="sig-name descname"><span class="pre">approx_percentile</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">x</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">w</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">percentage</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">[same</span> <span class="pre">as</span> <span class="pre">x]</span></span></span></dt>
<dd><p>Returns the approximate weighed percentile for all input values of <code class="docutils literal notranslate"><span class="pre">x</span></code>
using the per-item weight <code class="docutils literal notranslate"><span class="pre">w</span></code> at the percentage <code class="docutils literal notranslate"><span class="pre">percentage</span></code>. Weights must be
greater or equal to 1. Integer-value weights can be thought of as a replication
count for the value <code class="docutils literal notranslate"><span class="pre">x</span></code> in the percentile set. The value of <code class="docutils literal notranslate"><span class="pre">percentage</span></code> must be
between zero and one and must be constant for all input rows.</p>
</dd></dl>
<dl class="py function">
<dt class="sig sig-object py">
<span class="sig-name descname"><span class="pre">approx_percentile</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">x</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">w</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">percentages</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">array&lt;[same</span> <span class="pre">as</span> <span class="pre">x]&gt;</span></span></span></dt>
<dd><p>Returns the approximate weighed percentile for all input values of <code class="docutils literal notranslate"><span class="pre">x</span></code>
using the per-item weight <code class="docutils literal notranslate"><span class="pre">w</span></code> at each of the given percentages specified
in the array. Weights must be greater or equal to 1. Integer-value weights can
be thought of as a replication count for the value <code class="docutils literal notranslate"><span class="pre">x</span></code> in the percentile
set. Each element of the <code class="docutils literal notranslate"><span class="pre">percentages</span></code> array must be between zero and one, and the array
must be constant for all input rows.</p>
</dd></dl>
<dl class="py function">
<dt class="sig sig-object py">
<span class="sig-name descname"><span class="pre">approx_set</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">x</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">HyperLogLog</span></span></span></dt>
<dd><p>See <a class="reference internal" href="hyperloglog.html"><span class="doc">HyperLogLog functions</span></a>.</p>
</dd></dl>
<dl class="py function">
<dt class="sig sig-object py">
<span class="sig-name descname"><span class="pre">merge</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">x</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">HyperLogLog</span></span></span></dt>
<dd><p>See <a class="reference internal" href="hyperloglog.html"><span class="doc">HyperLogLog functions</span></a>.</p>
</dd></dl>
<dl class="py function">
<dt class="sig sig-object py">
<span class="sig-name descname"><span class="pre">merge</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">qdigest(T))</span> <span class="pre">-&gt;</span> <span class="pre">qdigest(T</span></span></em><span class="sig-paren">)</span></dt>
<dd><p>See <a class="reference internal" href="qdigest.html"><span class="doc">Quantile digest functions</span></a>.</p>
</dd></dl>
<dl class="py function">
<dt class="sig sig-object py">
<span class="sig-name descname"><span class="pre">merge</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">tdigest</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">tdigest</span></span></span></dt>
<dd><p>See <a class="reference internal" href="tdigest.html"><span class="doc">T-Digest functions</span></a>.</p>
</dd></dl>
<dl class="py function">
<dt class="sig sig-object py">
<span class="sig-name descname"><span class="pre">numeric_histogram</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">buckets</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">value</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">map&lt;double,</span> <span class="pre">double&gt;</span></span></span></dt>
<dd><p>Computes an approximate histogram with up to <code class="docutils literal notranslate"><span class="pre">buckets</span></code> number of buckets
for all <code class="docutils literal notranslate"><span class="pre">value</span></code>s. This function is equivalent to the variant of
<a class="reference internal" href="#numeric_histogram" title="numeric_histogram"><code class="xref py py-func docutils literal notranslate"><span class="pre">numeric_histogram()</span></code></a> that takes a <code class="docutils literal notranslate"><span class="pre">weight</span></code>, with a per-item weight of <code class="docutils literal notranslate"><span class="pre">1</span></code>.</p>
</dd></dl>
<dl class="py function">
<dt class="sig sig-object py" id="numeric_histogram">
<span class="sig-name descname"><span class="pre">numeric_histogram</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">buckets</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">value</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">weight</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">map&lt;double,</span> <span class="pre">double&gt;</span></span></span><a class="headerlink" href="#numeric_histogram" title="Permalink to this definition">#</a></dt>
<dd><p>Computes an approximate histogram with up to <code class="docutils literal notranslate"><span class="pre">buckets</span></code> number of buckets
for all <code class="docutils literal notranslate"><span class="pre">value</span></code>s with a per-item weight of <code class="docutils literal notranslate"><span class="pre">weight</span></code>. The algorithm
is based loosely on:</p>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span>Yael Ben-Haim and Elad Tom-Tov, "A streaming parallel decision tree algorithm",
J. Machine Learning Research 11 (2010), pp. 849--872.
</pre></div>
</div>
<p><code class="docutils literal notranslate"><span class="pre">buckets</span></code> must be a <code class="docutils literal notranslate"><span class="pre">bigint</span></code>. <code class="docutils literal notranslate"><span class="pre">value</span></code> and <code class="docutils literal notranslate"><span class="pre">weight</span></code> must be numeric.</p>
</dd></dl>
<dl class="py function">
<dt class="sig sig-object py">
<span class="sig-name descname"><span class="pre">qdigest_agg</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="pre">x)</span> <span class="pre">-&gt;</span> <span class="pre">qdigest([same</span> <span class="pre">as</span> <span class="pre">x]</span></em><span class="sig-paren">)</span></dt>
<dd><p>See <a class="reference internal" href="qdigest.html"><span class="doc">Quantile digest functions</span></a>.</p>
</dd></dl>
<dl class="py function">
<dt class="sig sig-object py">
<span class="sig-name descname"><span class="pre">qdigest_agg</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="pre">x,</span> <span class="pre">w)</span> <span class="pre">-&gt;</span> <span class="pre">qdigest([same</span> <span class="pre">as</span> <span class="pre">x]</span></em><span class="sig-paren">)</span></dt>
<dd><p>See <a class="reference internal" href="qdigest.html"><span class="doc">Quantile digest functions</span></a>.</p>
</dd></dl>
<dl class="py function">
<dt class="sig sig-object py">
<span class="sig-name descname"><span class="pre">qdigest_agg</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="pre">x,</span> <span class="pre">w,</span> <span class="pre">accuracy)</span> <span class="pre">-&gt;</span> <span class="pre">qdigest([same</span> <span class="pre">as</span> <span class="pre">x]</span></em><span class="sig-paren">)</span></dt>
<dd><p>See <a class="reference internal" href="qdigest.html"><span class="doc">Quantile digest functions</span></a>.</p>
</dd></dl>
<dl class="py function">
<dt class="sig sig-object py">
<span class="sig-name descname"><span class="pre">tdigest_agg</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">x</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">tdigest</span></span></span></dt>
<dd><p>See <a class="reference internal" href="tdigest.html"><span class="doc">T-Digest functions</span></a>.</p>
</dd></dl>
<dl class="py function">
<dt class="sig sig-object py">
<span class="sig-name descname"><span class="pre">tdigest_agg</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">x</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">w</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">tdigest</span></span></span></dt>
<dd><p>See <a class="reference internal" href="tdigest.html"><span class="doc">T-Digest functions</span></a>.</p>
</dd></dl>
</section>
<section id="statistical-aggregate-functions">
<h2 id="statistical-aggregate-functions">Statistical aggregate functions<a class="headerlink" href="#statistical-aggregate-functions" title="Permalink to this heading">#</a></h2>
<dl class="py function">
<dt class="sig sig-object py" id="corr">
<span class="sig-name descname"><span class="pre">corr</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">y</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">x</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">double</span></span></span><a class="headerlink" href="#corr" title="Permalink to this definition">#</a></dt>
<dd><p>Returns correlation coefficient of input values.</p>
</dd></dl>
<dl class="py function">
<dt class="sig sig-object py" id="covar_pop">
<span class="sig-name descname"><span class="pre">covar_pop</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">y</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">x</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">double</span></span></span><a class="headerlink" href="#covar_pop" title="Permalink to this definition">#</a></dt>
<dd><p>Returns the population covariance of input values.</p>
</dd></dl>
<dl class="py function">
<dt class="sig sig-object py" id="covar_samp">
<span class="sig-name descname"><span class="pre">covar_samp</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">y</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">x</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">double</span></span></span><a class="headerlink" href="#covar_samp" title="Permalink to this definition">#</a></dt>
<dd><p>Returns the sample covariance of input values.</p>
</dd></dl>
<dl class="py function">
<dt class="sig sig-object py" id="kurtosis">
<span class="sig-name descname"><span class="pre">kurtosis</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">x</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">double</span></span></span><a class="headerlink" href="#kurtosis" title="Permalink to this definition">#</a></dt>
<dd><p>Returns the excess kurtosis of all input values. Unbiased estimate using
the following expression:</p>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span>kurtosis(x) = n(n+1)/((n-1)(n-2)(n-3))sum[(x_i-mean)^4]/stddev(x)^4-3(n-1)^2/((n-2)(n-3))
</pre></div>
</div>
</dd></dl>
<dl class="py function">
<dt class="sig sig-object py" id="regr_intercept">
<span class="sig-name descname"><span class="pre">regr_intercept</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">y</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">x</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">double</span></span></span><a class="headerlink" href="#regr_intercept" title="Permalink to this definition">#</a></dt>
<dd><p>Returns linear regression intercept of input values. <code class="docutils literal notranslate"><span class="pre">y</span></code> is the dependent
value. <code class="docutils literal notranslate"><span class="pre">x</span></code> is the independent value.</p>
</dd></dl>
<dl class="py function">
<dt class="sig sig-object py" id="regr_slope">
<span class="sig-name descname"><span class="pre">regr_slope</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">y</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">x</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">double</span></span></span><a class="headerlink" href="#regr_slope" title="Permalink to this definition">#</a></dt>
<dd><p>Returns linear regression slope of input values. <code class="docutils literal notranslate"><span class="pre">y</span></code> is the dependent
value. <code class="docutils literal notranslate"><span class="pre">x</span></code> is the independent value.</p>
</dd></dl>
<dl class="py function">
<dt class="sig sig-object py" id="skewness">
<span class="sig-name descname"><span class="pre">skewness</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">x</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">double</span></span></span><a class="headerlink" href="#skewness" title="Permalink to this definition">#</a></dt>
<dd><p>Returns the Fisher’s moment coefficient of <a class="reference external" href="https://wikipedia.org/wiki/Skewness">skewness</a> of all input values.</p>
</dd></dl>
<dl class="py function">
<dt class="sig sig-object py" id="stddev">
<span class="sig-name descname"><span class="pre">stddev</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">x</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">double</span></span></span><a class="headerlink" href="#stddev" title="Permalink to this definition">#</a></dt>
<dd><p>This is an alias for <a class="reference internal" href="#stddev_samp" title="stddev_samp"><code class="xref py py-func docutils literal notranslate"><span class="pre">stddev_samp()</span></code></a>.</p>
</dd></dl>
<dl class="py function">
<dt class="sig sig-object py" id="stddev_pop">
<span class="sig-name descname"><span class="pre">stddev_pop</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">x</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">double</span></span></span><a class="headerlink" href="#stddev_pop" title="Permalink to this definition">#</a></dt>
<dd><p>Returns the population standard deviation of all input values.</p>
</dd></dl>
<dl class="py function">
<dt class="sig sig-object py" id="stddev_samp">
<span class="sig-name descname"><span class="pre">stddev_samp</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">x</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">double</span></span></span><a class="headerlink" href="#stddev_samp" title="Permalink to this definition">#</a></dt>
<dd><p>Returns the sample standard deviation of all input values.</p>
</dd></dl>
<dl class="py function">
<dt class="sig sig-object py" id="variance">
<span class="sig-name descname"><span class="pre">variance</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">x</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">double</span></span></span><a class="headerlink" href="#variance" title="Permalink to this definition">#</a></dt>
<dd><p>This is an alias for <a class="reference internal" href="#var_samp" title="var_samp"><code class="xref py py-func docutils literal notranslate"><span class="pre">var_samp()</span></code></a>.</p>
</dd></dl>
<dl class="py function">
<dt class="sig sig-object py" id="var_pop">
<span class="sig-name descname"><span class="pre">var_pop</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">x</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">double</span></span></span><a class="headerlink" href="#var_pop" title="Permalink to this definition">#</a></dt>
<dd><p>Returns the population variance of all input values.</p>
</dd></dl>
<dl class="py function">
<dt class="sig sig-object py" id="var_samp">
<span class="sig-name descname"><span class="pre">var_samp</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">x</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">double</span></span></span><a class="headerlink" href="#var_samp" title="Permalink to this definition">#</a></dt>
<dd><p>Returns the sample variance of all input values.</p>
</dd></dl>
</section>
<section id="lambda-aggregate-functions">
<h2 id="lambda-aggregate-functions">Lambda aggregate functions<a class="headerlink" href="#lambda-aggregate-functions" title="Permalink to this heading">#</a></h2>
<dl class="py function">
<dt class="sig sig-object py" id="reduce_agg">
<span class="sig-name descname"><span class="pre">reduce_agg</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">inputValue</span> <span class="pre">T</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">initialState</span> <span class="pre">S</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">inputFunction(S</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">T</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">S)</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">combineFunction(S</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">S</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">S)</span></span></em><span class="sig-paren">)</span> <span class="sig-return"><span class="sig-return-icon">→</span> <span class="sig-return-typehint"><span class="pre">S</span></span></span><a class="headerlink" href="#reduce_agg" title="Permalink to this definition">#</a></dt>
<dd><p>Reduces all input values into a single value. <code class="docutils literal notranslate"><span class="pre">inputFunction</span></code> will be invoked
for each non-null input value. In addition to taking the input value, <code class="docutils literal notranslate"><span class="pre">inputFunction</span></code>
takes the current state, initially <code class="docutils literal notranslate"><span class="pre">initialState</span></code>, and returns the new state.
<code class="docutils literal notranslate"><span class="pre">combineFunction</span></code> will be invoked to combine two states into a new state.
The final state is returned:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">id</span><span class="p">,</span> <span class="n">reduce_agg</span><span class="p">(</span><span class="n">value</span><span class="p">,</span> <span class="mi">0</span><span class="p">,</span> <span class="p">(</span><span class="n">a</span><span class="p">,</span> <span class="n">b</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="n">a</span> <span class="o">+</span> <span class="n">b</span><span class="p">,</span> <span class="p">(</span><span class="n">a</span><span class="p">,</span> <span class="n">b</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="n">a</span> <span class="o">+</span> <span class="n">b</span><span class="p">)</span>
<span class="k">FROM</span> <span class="p">(</span>
    <span class="k">VALUES</span>
        <span class="p">(</span><span class="mi">1</span><span class="p">,</span> <span class="mi">3</span><span class="p">),</span>
        <span class="p">(</span><span class="mi">1</span><span class="p">,</span> <span class="mi">4</span><span class="p">),</span>
        <span class="p">(</span><span class="mi">1</span><span class="p">,</span> <span class="mi">5</span><span class="p">),</span>
        <span class="p">(</span><span class="mi">2</span><span class="p">,</span> <span class="mi">6</span><span class="p">),</span>
        <span class="p">(</span><span class="mi">2</span><span class="p">,</span> <span class="mi">7</span><span class="p">)</span>
<span class="p">)</span> <span class="k">AS</span> <span class="n">t</span><span class="p">(</span><span class="n">id</span><span class="p">,</span> <span class="n">value</span><span class="p">)</span>
<span class="k">GROUP</span> <span class="k">BY</span> <span class="n">id</span><span class="p">;</span>
<span class="c1">-- (1, 12)</span>
<span class="c1">-- (2, 13)</span>

<span class="k">SELECT</span> <span class="n">id</span><span class="p">,</span> <span class="n">reduce_agg</span><span class="p">(</span><span class="n">value</span><span class="p">,</span> <span class="mi">1</span><span class="p">,</span> <span class="p">(</span><span class="n">a</span><span class="p">,</span> <span class="n">b</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="n">a</span> <span class="o">*</span> <span class="n">b</span><span class="p">,</span> <span class="p">(</span><span class="n">a</span><span class="p">,</span> <span class="n">b</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="n">a</span> <span class="o">*</span> <span class="n">b</span><span class="p">)</span>
<span class="k">FROM</span> <span class="p">(</span>
    <span class="k">VALUES</span>
        <span class="p">(</span><span class="mi">1</span><span class="p">,</span> <span class="mi">3</span><span class="p">),</span>
        <span class="p">(</span><span class="mi">1</span><span class="p">,</span> <span class="mi">4</span><span class="p">),</span>
        <span class="p">(</span><span class="mi">1</span><span class="p">,</span> <span class="mi">5</span><span class="p">),</span>
        <span class="p">(</span><span class="mi">2</span><span class="p">,</span> <span class="mi">6</span><span class="p">),</span>
        <span class="p">(</span><span class="mi">2</span><span class="p">,</span> <span class="mi">7</span><span class="p">)</span>
<span class="p">)</span> <span class="k">AS</span> <span class="n">t</span><span class="p">(</span><span class="n">id</span><span class="p">,</span> <span class="n">value</span><span class="p">)</span>
<span class="k">GROUP</span> <span class="k">BY</span> <span class="n">id</span><span class="p">;</span>
<span class="c1">-- (1, 60)</span>
<span class="c1">-- (2, 42)</span>
</pre></div>
</div>
<p>The state type must be a boolean, integer, floating-point, char, varchar or date/time/interval.</p>
</dd></dl>
</section>
</section>


          </article>
        </div>
      </div>
    </main>
  </div>
  <footer class="md-footer">
    <div class="md-footer-nav">
      <nav class="md-footer-nav__inner md-grid">
          
            <a href="list-by-topic.html" title="List of functions by topic"
               class="md-flex md-footer-nav__link md-footer-nav__link--prev"
               rel="prev">
              <div class="md-flex__cell md-flex__cell--shrink">
                <i class="md-icon md-icon--arrow-back md-footer-nav__button"></i>
              </div>
              <div class="md-flex__cell md-flex__cell--stretch md-footer-nav__title">
                <span class="md-flex__ellipsis">
                  <span
                      class="md-footer-nav__direction"> Previous </span> List of functions by topic </span>
              </div>
            </a>
          
          
            <a href="array.html" title="Array functions and operators"
               class="md-flex md-footer-nav__link md-footer-nav__link--next"
               rel="next">
            <div class="md-flex__cell md-flex__cell--stretch md-footer-nav__title"><span
                class="md-flex__ellipsis"> <span
                class="md-footer-nav__direction"> Next </span> Array functions and operators </span>
            </div>
            <div class="md-flex__cell md-flex__cell--shrink"><i
                class="md-icon md-icon--arrow-forward md-footer-nav__button"></i>
            </div>
          
        </a>
        
      </nav>
    </div>
    <div class="md-footer-meta md-typeset">
      <div class="md-footer-meta__inner md-grid">
        <div class="md-footer-copyright">
        </div>
      </div>
    </div>
  </footer>
  <script src="../_static/javascripts/application.js"></script>
  <script>app.initialize({version: "1.0.4", url: {base: ".."}})</script>
  </body>
</html>