| <!doctype html> |
| <html lang="en" dir="ltr" class="docs-wrapper plugin-docs plugin-id-default docs-version-current docs-doc-page docs-doc-id-querying/sql-window-functions" data-has-hydrated="false"> |
| <head> |
| <meta charset="UTF-8"> |
| <meta name="generator" content="Docusaurus v3.7.0"> |
| <title data-rh="true">Window functions | Apache® Druid</title><meta data-rh="true" name="viewport" content="width=device-width,initial-scale=1"><meta data-rh="true" name="twitter:card" content="summary_large_image"><meta data-rh="true" property="og:image" content="https://druid.apache.org/img/druid_nav.png"><meta data-rh="true" name="twitter:image" content="https://druid.apache.org/img/druid_nav.png"><meta data-rh="true" property="og:url" content="https://druid.apache.org/docs/latest/querying/sql-window-functions"><meta data-rh="true" property="og:locale" content="en"><meta data-rh="true" name="docusaurus_locale" content="en"><meta data-rh="true" name="docsearch:language" content="en"><meta data-rh="true" name="docusaurus_version" content="current"><meta data-rh="true" name="docusaurus_tag" content="docs-default-current"><meta data-rh="true" name="docsearch:version" content="current"><meta data-rh="true" name="docsearch:docusaurus_tag" content="docs-default-current"><meta data-rh="true" property="og:title" content="Window functions | Apache® Druid"><meta data-rh="true" name="description" content="Reference for window functions"><meta data-rh="true" property="og:description" content="Reference for window functions"><link data-rh="true" rel="icon" href="/img/favicon.png"><link data-rh="true" rel="canonical" href="https://druid.apache.org/docs/latest/querying/sql-window-functions"><link data-rh="true" rel="alternate" href="https://druid.apache.org/docs/latest/querying/sql-window-functions" hreflang="en"><link data-rh="true" rel="alternate" href="https://druid.apache.org/docs/latest/querying/sql-window-functions" hreflang="x-default"><link rel="stylesheet" href="/css/all.css"> |
| <script src="/js/clipboard.min.js"></script><link rel="stylesheet" href="/assets/css/styles.f3140859.css"> |
| <script src="/assets/js/runtime~main.55f4649c.js" defer="defer"></script> |
| <script src="/assets/js/main.3bc88d52.js" defer="defer"></script> |
| </head> |
| <body class="navigation-with-keyboard"> |
| <script>!function(){function t(t){document.documentElement.setAttribute("data-theme",t)}var e=function(){try{return new URLSearchParams(window.location.search).get("docusaurus-theme")}catch(t){}}()||function(){try{return window.localStorage.getItem("theme")}catch(t){}}();t(null!==e?e:"light")}(),function(){try{const n=new URLSearchParams(window.location.search).entries();for(var[t,e]of n)if(t.startsWith("docusaurus-data-")){var a=t.replace("docusaurus-data-","data-");document.documentElement.setAttribute(a,e)}}catch(t){}}()</script><div id="__docusaurus"><div role="region" aria-label="Skip to main content"><a class="skipToContent_fXgn" href="#__docusaurus_skipToContent_fallback">Skip to main content</a></div><nav aria-label="Main" class="navbar navbar--fixed-top navbar--dark"><div class="navbar__inner"><div class="navbar__items"><button aria-label="Toggle navigation bar" aria-expanded="false" class="navbar__toggle clean-btn" type="button"><svg width="30" height="30" viewBox="0 0 30 30" aria-hidden="true"><path stroke="currentColor" stroke-linecap="round" stroke-miterlimit="10" stroke-width="2" d="M4 7h22M4 15h22M4 23h22"></path></svg></button><a class="navbar__brand" href="/"><div class="navbar__logo"><img src="/img/druid_nav.png" alt="Apache® Druid" class="themedComponent_mlkZ themedComponent--light_NVdE"><img src="/img/druid_nav.png" alt="Apache® Druid" class="themedComponent_mlkZ themedComponent--dark_xIcU"></div></a></div><div class="navbar__items navbar__items--right"><a class="navbar__item navbar__link" href="/technology">Technology</a><a class="navbar__item navbar__link" href="/use-cases">Use Cases</a><a class="navbar__item navbar__link" href="/druid-powered">Powered By</a><a class="navbar__item navbar__link" href="/docs/latest/design/">Docs</a><a class="navbar__item navbar__link" href="/community/">Community</a><div class="navbar__item dropdown dropdown--hoverable dropdown--right"><a href="#" aria-haspopup="true" aria-expanded="false" role="button" class="navbar__link">Apache®</a><ul class="dropdown__menu"><li><a href="https://www.apache.org/" target="_blank" rel="noopener noreferrer" class="dropdown__link">Foundation<svg width="12" height="12" aria-hidden="true" viewBox="0 0 24 24" class="iconExternalLink_nPIU"><path fill="currentColor" d="M21 13v10h-21v-19h12v2h-10v15h17v-8h2zm3-12h-10.988l4.035 4-6.977 7.07 2.828 2.828 6.977-7.07 4.125 4.172v-11z"></path></svg></a></li><li><a href="https://apachecon.com/?ref=druid.apache.org" target="_blank" rel="noopener noreferrer" class="dropdown__link">Events<svg width="12" height="12" aria-hidden="true" viewBox="0 0 24 24" class="iconExternalLink_nPIU"><path fill="currentColor" d="M21 13v10h-21v-19h12v2h-10v15h17v-8h2zm3-12h-10.988l4.035 4-6.977 7.07 2.828 2.828 6.977-7.07 4.125 4.172v-11z"></path></svg></a></li><li><a href="https://www.apache.org/licenses/" target="_blank" rel="noopener noreferrer" class="dropdown__link">License<svg width="12" height="12" aria-hidden="true" viewBox="0 0 24 24" class="iconExternalLink_nPIU"><path fill="currentColor" d="M21 13v10h-21v-19h12v2h-10v15h17v-8h2zm3-12h-10.988l4.035 4-6.977 7.07 2.828 2.828 6.977-7.07 4.125 4.172v-11z"></path></svg></a></li><li><a href="https://www.apache.org/foundation/thanks.html" target="_blank" rel="noopener noreferrer" class="dropdown__link">Thanks<svg width="12" height="12" aria-hidden="true" viewBox="0 0 24 24" class="iconExternalLink_nPIU"><path fill="currentColor" d="M21 13v10h-21v-19h12v2h-10v15h17v-8h2zm3-12h-10.988l4.035 4-6.977 7.07 2.828 2.828 6.977-7.07 4.125 4.172v-11z"></path></svg></a></li><li><a href="https://www.apache.org/security/" target="_blank" rel="noopener noreferrer" class="dropdown__link">Security<svg width="12" height="12" aria-hidden="true" viewBox="0 0 24 24" class="iconExternalLink_nPIU"><path fill="currentColor" d="M21 13v10h-21v-19h12v2h-10v15h17v-8h2zm3-12h-10.988l4.035 4-6.977 7.07 2.828 2.828 6.977-7.07 4.125 4.172v-11z"></path></svg></a></li><li><a href="https://www.apache.org/foundation/sponsorship.html" target="_blank" rel="noopener noreferrer" class="dropdown__link">Sponsorship<svg width="12" height="12" aria-hidden="true" viewBox="0 0 24 24" class="iconExternalLink_nPIU"><path fill="currentColor" d="M21 13v10h-21v-19h12v2h-10v15h17v-8h2zm3-12h-10.988l4.035 4-6.977 7.07 2.828 2.828 6.977-7.07 4.125 4.172v-11z"></path></svg></a></li></ul></div><a class="navbar__item navbar__link" href="/downloads/">Download</a><div class="navbarSearchContainer_Bca1"><div class="navbar__search"><span aria-label="expand searchbar" role="button" class="search-icon" tabindex="0"></span><input id="search_input_react" type="search" placeholder="Loading..." aria-label="Search" class="navbar__search-input search-bar" disabled=""></div></div></div></div><div role="presentation" class="navbar-sidebar__backdrop"></div></nav><div id="__docusaurus_skipToContent_fallback" class="main-wrapper mainWrapper_z2l0"><div class="docsWrapper_hBAB"><button aria-label="Scroll back to top" class="clean-btn theme-back-to-top-button backToTopButton_sjWU" type="button"></button><div class="docRoot_UBD9"><aside class="theme-doc-sidebar-container docSidebarContainer_YfHR"><div class="sidebarViewport_aRkj"><div class="sidebar_njMd"><nav aria-label="Docs sidebar" class="menu thin-scrollbar menu_SIkG"><ul class="theme-doc-sidebar-menu menu__list"><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-1 menu__list-item"><a class="menu__link" href="/docs/latest/design/">Introduction to Apache Druid</a></li><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-1 menu__list-item menu__list-item--collapsed"><div class="menu__list-item-collapsible"><a class="menu__link menu__link--sublist" href="/docs/latest/tutorials/">Getting started</a><button aria-label="Expand sidebar category 'Getting started'" aria-expanded="false" type="button" class="clean-btn menu__caret"></button></div></li><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-1 menu__list-item menu__list-item--collapsed"><div class="menu__list-item-collapsible"><a class="menu__link menu__link--sublist menu__link--sublist-caret" role="button" aria-expanded="false" href="/docs/latest/design/architecture">Design</a></div></li><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-1 menu__list-item menu__list-item--collapsed"><div class="menu__list-item-collapsible"><a class="menu__link menu__link--sublist" href="/docs/latest/ingestion/">Ingestion</a><button aria-label="Expand sidebar category 'Ingestion'" aria-expanded="false" type="button" class="clean-btn menu__caret"></button></div></li><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-1 menu__list-item menu__list-item--collapsed"><div class="menu__list-item-collapsible"><a class="menu__link menu__link--sublist" href="/docs/latest/data-management/">Data management</a><button aria-label="Expand sidebar category 'Data management'" aria-expanded="false" type="button" class="clean-btn menu__caret"></button></div></li><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-1 menu__list-item"><div class="menu__list-item-collapsible"><a class="menu__link menu__link--sublist menu__link--sublist-caret menu__link--active" role="button" aria-expanded="true" href="/docs/latest/querying/sql">Querying</a></div><ul style="display:block;overflow:visible;height:auto" class="menu__list"><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-2 menu__list-item"><div class="menu__list-item-collapsible"><a class="menu__link menu__link--sublist menu__link--sublist-caret menu__link--active" role="button" aria-expanded="true" tabindex="0" href="/docs/latest/querying/sql">Druid SQL</a></div><ul style="display:block;overflow:visible;height:auto" class="menu__list"><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/latest/querying/sql">Overview and syntax</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/latest/querying/sql-functions">All functions</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/latest/querying/tips-good-queries">Tips for writing good queries</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/latest/querying/query-deep-storage">Query from deep storage</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/latest/querying/sql-data-types">SQL data types</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/latest/querying/sql-operators">Operators</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/latest/querying/sql-scalar">Scalar functions</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/latest/querying/sql-aggregations">Aggregation functions</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class="menu__link menu__link--active" aria-current="page" tabindex="0" href="/docs/latest/querying/sql-window-functions">Window functions</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/latest/querying/sql-array-functions">Array functions</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/latest/querying/sql-multivalue-string-functions">Multi-value string functions</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/latest/querying/sql-json-functions">JSON functions</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/latest/querying/sql-query-context">SQL query context</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/latest/querying/sql-metadata-tables">SQL metadata tables</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/latest/querying/sql-translation">SQL query translation</a></li></ul></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-2 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/latest/querying/">Native queries</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-2 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/latest/querying/query-processing">Query processing</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-2 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/latest/querying/query-execution">Query execution</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-2 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/latest/querying/dart">Dart engine</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-2 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/latest/querying/troubleshooting">Troubleshooting</a></li><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-2 menu__list-item menu__list-item--collapsed"><div class="menu__list-item-collapsible"><a class="menu__link menu__link--sublist menu__link--sublist-caret" role="button" aria-expanded="false" tabindex="0" href="/docs/latest/querying/datasource">Concepts</a></div></li><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-2 menu__list-item menu__list-item--collapsed"><div class="menu__list-item-collapsible"><a class="menu__link menu__link--sublist menu__link--sublist-caret" role="button" aria-expanded="false" tabindex="0" href="/docs/latest/querying/timeseriesquery">Native query types</a></div></li><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-2 menu__list-item menu__list-item--collapsed"><div class="menu__list-item-collapsible"><a class="menu__link menu__link--sublist menu__link--sublist-caret" role="button" aria-expanded="false" tabindex="0" href="/docs/latest/querying/filters">Native query components</a></div></li></ul></li><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-1 menu__list-item menu__list-item--collapsed"><div class="menu__list-item-collapsible"><a class="menu__link menu__link--sublist" href="/docs/latest/api-reference/">API reference</a><button aria-label="Expand sidebar category 'API reference'" aria-expanded="false" type="button" class="clean-btn menu__caret"></button></div></li><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-1 menu__list-item menu__list-item--collapsed"><div class="menu__list-item-collapsible"><a class="menu__link menu__link--sublist" href="/docs/latest/configuration/">Configuration</a><button aria-label="Expand sidebar category 'Configuration'" aria-expanded="false" type="button" class="clean-btn menu__caret"></button></div></li><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-1 menu__list-item menu__list-item--collapsed"><div class="menu__list-item-collapsible"><a class="menu__link menu__link--sublist menu__link--sublist-caret" role="button" aria-expanded="false" href="/docs/latest/api-reference/automatic-compaction-api">Operations</a></div></li><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-1 menu__list-item menu__list-item--collapsed"><div class="menu__list-item-collapsible"><a class="menu__link menu__link--sublist" href="/docs/latest/development/overview">Development</a><button aria-label="Expand sidebar category 'Development'" aria-expanded="false" type="button" class="clean-btn menu__caret"></button></div></li><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-1 menu__list-item menu__list-item--collapsed"><div class="menu__list-item-collapsible"><a class="menu__link menu__link--sublist menu__link--sublist-caret" role="button" aria-expanded="false" href="/docs/latest/release-info/release-notes">Release info</a></div></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-1 menu__list-item"><a class="menu__link" href="/docs/latest/misc/papers-and-talks">Papers</a></li></ul></nav></div></div></aside><main class="docMainContainer_TBSr"><div class="container padding-top--md padding-bottom--lg"><div class="row"><div class="col docItemCol_VOVn"><div class="docItemContainer_Djhp"><article><nav class="theme-doc-breadcrumbs breadcrumbsContainer_Z_bl" aria-label="Breadcrumbs"><ul class="breadcrumbs" itemscope="" itemtype="https://schema.org/BreadcrumbList"><li class="breadcrumbs__item"><a aria-label="Home page" class="breadcrumbs__link" href="/"><svg viewBox="0 0 24 24" class="breadcrumbHomeIcon_YNFT"><path d="M10 19v-5h4v5c0 .55.45 1 1 1h3c.55 0 1-.45 1-1v-7h1.7c.46 0 .68-.57.33-.87L12.67 3.6c-.38-.34-.96-.34-1.34 0l-8.36 7.53c-.34.3-.13.87.33.87H5v7c0 .55.45 1 1 1h3c.55 0 1-.45 1-1z" fill="currentColor"></path></svg></a></li><li class="breadcrumbs__item"><span class="breadcrumbs__link">Querying</span><meta itemprop="position" content="1"></li><li class="breadcrumbs__item"><span class="breadcrumbs__link">Druid SQL</span><meta itemprop="position" content="2"></li><li itemscope="" itemprop="itemListElement" itemtype="https://schema.org/ListItem" class="breadcrumbs__item breadcrumbs__item--active"><span class="breadcrumbs__link" itemprop="name">Window functions</span><meta itemprop="position" content="3"></li></ul></nav><div class="tocCollapsible_ETCw theme-doc-toc-mobile tocMobile_ITEo"><button type="button" class="clean-btn tocCollapsibleButton_TO0P">On this page</button></div><div class="theme-doc-markdown markdown"><header><h1>Window functions</h1></header><div class="theme-admonition theme-admonition-info admonition_xJq3 alert alert--info"><div class="admonitionHeading_Gvgb"><span class="admonitionIcon_Rf37"><svg viewBox="0 0 14 16"><path fill-rule="evenodd" d="M7 2.3c3.14 0 5.7 2.56 5.7 5.7s-2.56 5.7-5.7 5.7A5.71 5.71 0 0 1 1.3 8c0-3.14 2.56-5.7 5.7-5.7zM7 1C3.14 1 0 4.14 0 8s3.14 7 7 7 7-3.14 7-7-3.14-7-7-7zm1 3H6v5h2V4zm0 6H6v2h2v-2z"></path></svg></span>info</div><div class="admonitionContent_BuS1"><p>Apache Druid supports two query languages: <a href="/docs/latest/querying/sql">Druid SQL</a> and <a href="/docs/latest/querying/">native queries</a>. |
| This document describes the SQL language.</p></div></div> |
| <p>Window functions in Apache Druid produce values based upon the relationship of one row within a window of rows to the other rows within the same window. A window is a group of related rows within a result set. For example, rows with the same value for a specific dimension.</p> |
| <p>Window functions in Druid require a GROUP BY statement. Druid performs the row-level aggregations for the GROUP BY before performing the window function calculations.</p> |
| <p>The following example organizes results with the same <code>channel</code> value into windows. For each window, the query returns the rank of each row in ascending order based upon its <code>changed</code> value.</p> |
| <div class="language-sql codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#bfc7d5;--prism-background-color:#292d3e"><div class="codeBlockContent_biex"><pre tabindex="0" class="prism-code language-sql codeBlock_bY9V thin-scrollbar" style="color:#bfc7d5;background-color:#292d3e"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#bfc7d5"><span class="token keyword" style="font-style:italic">SELECT</span><span class="token plain"> FLOOR</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">__time </span><span class="token keyword" style="font-style:italic">TO</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">DAY</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">AS</span><span class="token plain"> event_time</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> channel</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> ABS</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">delta</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">AS</span><span class="token plain"> change</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> RANK</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">OVER</span><span class="token plain"> w </span><span class="token keyword" style="font-style:italic">AS</span><span class="token plain"> rank_value</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"></span><span class="token keyword" style="font-style:italic">FROM</span><span class="token plain"> wikipedia</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"></span><span class="token keyword" style="font-style:italic">WHERE</span><span class="token plain"> channel </span><span class="token operator" style="color:rgb(137, 221, 255)">in</span><span class="token plain"> </span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token string" style="color:rgb(195, 232, 141)">'#kk.wikipedia'</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> </span><span class="token string" style="color:rgb(195, 232, 141)">'#lt.wikipedia'</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"></span><span class="token operator" style="color:rgb(137, 221, 255)">AND</span><span class="token plain"> </span><span class="token string" style="color:rgb(195, 232, 141)">'2016-06-28'</span><span class="token plain"> </span><span class="token operator" style="color:rgb(137, 221, 255)">></span><span class="token plain"> FLOOR</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">__time </span><span class="token keyword" style="font-style:italic">TO</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">DAY</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token operator" style="color:rgb(137, 221, 255)">></span><span class="token plain"> </span><span class="token string" style="color:rgb(195, 232, 141)">'2016-06-26'</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"></span><span class="token keyword" style="font-style:italic">GROUP</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">BY</span><span class="token plain"> channel</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> ABS</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">delta</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> __time</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">WINDOW w </span><span class="token keyword" style="font-style:italic">AS</span><span class="token plain"> </span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token keyword" style="font-style:italic">PARTITION</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">BY</span><span class="token plain"> channel </span><span class="token keyword" style="font-style:italic">ORDER</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">BY</span><span class="token plain"> ABS</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">delta</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">ASC</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><br></span></code></pre><div class="buttonGroup__atx"><button type="button" aria-label="Copy code to clipboard" title="Copy" class="clean-btn"><span class="copyButtonIcons_eSgA" aria-hidden="true"><svg viewBox="0 0 24 24" class="copyButtonIcon_y97N"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewBox="0 0 24 24" class="copyButtonSuccessIcon_LjdS"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button></div></div></div> |
| <details class="details_lb9f alert alert--info details_b_Ee" data-collapsed="true"><summary> View results </summary><div><div class="collapsibleContent_i85q"><table><thead><tr><th><code>event_time</code></th><th><code>channel</code></th><th><code>change</code></th><th><code>rank_value</code></th></tr></thead><tbody><tr><td><code>2016-06-27T00:00:00.000Z</code></td><td><code>#kk.wikipedia</code></td><td>1</td><td>1</td></tr><tr><td><code>2016-06-27T00:00:00.000Z</code></td><td><code>#kk.wikipedia</code></td><td>1</td><td>1</td></tr><tr><td><code>2016-06-27T00:00:00.000Z</code></td><td><code>#kk.wikipedia</code></td><td>7</td><td>3</td></tr><tr><td><code>2016-06-27T00:00:00.000Z</code></td><td><code>#kk.wikipedia</code></td><td>56</td><td>4</td></tr><tr><td><code>2016-06-27T00:00:00.000Z</code></td><td><code>#kk.wikipedia</code></td><td>56</td><td>4</td></tr><tr><td><code>2016-06-27T00:00:00.000Z</code></td><td><code>#kk.wikipedia</code></td><td>63</td><td>6</td></tr><tr><td><code>2016-06-27T00:00:00.000Z</code></td><td><code>#kk.wikipedia</code></td><td>91</td><td>7</td></tr><tr><td><code>2016-06-27T00:00:00.000Z</code></td><td><code>#kk.wikipedia</code></td><td>2440</td><td>8</td></tr><tr><td><code>2016-06-27T00:00:00.000Z</code></td><td><code>#kk.wikipedia</code></td><td>2703</td><td>9</td></tr><tr><td><code>2016-06-27T00:00:00.000Z</code></td><td><code>#kk.wikipedia</code></td><td>6900</td><td>10</td></tr><tr><td><code>2016-06-27T00:00:00.000Z</code></td><td><code>#lt.wikipedia</code></td><td>1</td><td>1</td></tr><tr><td><code>2016-06-27T00:00:00.000Z</code></td><td><code>#lt.wikipedia</code></td><td>2</td><td>2</td></tr><tr><td><code>2016-06-27T00:00:00.000Z</code></td><td><code>#lt.wikipedia</code></td><td>13</td><td>3</td></tr><tr><td><code>2016-06-27T00:00:00.000Z</code></td><td><code>#lt.wikipedia</code></td><td>28</td><td>4</td></tr><tr><td><code>2016-06-27T00:00:00.000Z</code></td><td><code>#lt.wikipedia</code></td><td>53</td><td>5</td></tr><tr><td><code>2016-06-27T00:00:00.000Z</code></td><td><code>#lt.wikipedia</code></td><td>56</td><td>6</td></tr><tr><td><code>2016-06-27T00:00:00.000Z</code></td><td><code>#lt.wikipedia</code></td><td>59</td><td>7</td></tr><tr><td><code>2016-06-27T00:00:00.000Z</code></td><td><code>#lt.wikipedia</code></td><td>391</td><td>8</td></tr><tr><td><code>2016-06-27T00:00:00.000Z</code></td><td><code>#lt.wikipedia</code></td><td>894</td><td>9</td></tr><tr><td><code>2016-06-27T00:00:00.000Z</code></td><td><code>#lt.wikipedia</code></td><td>4358</td><td>10</td></tr></tbody></table></div></div></details> |
| <p>Window functions are similar to <a href="/docs/latest/querying/aggregations">aggregation functions</a>.</p> |
| <p>You can use the OVER clause to treat other Druid aggregation functions as window functions. For example, the sum of a value for rows within a window.</p> |
| <p>Window functions support aliasing.</p> |
| <h2 class="anchor anchorWithStickyNavbar_LWe7" id="window-function-syntax">Window function syntax<a href="#window-function-syntax" class="hash-link" aria-label="Direct link to Window function syntax" title="Direct link to Window function syntax"></a></h2> |
| <p>You can write a window function in Druid using either syntax below. |
| The second syntax shows a window alias to reference a window that you can reuse.</p> |
| <div class="language-sql codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#bfc7d5;--prism-background-color:#292d3e"><div class="codeBlockContent_biex"><pre tabindex="0" class="prism-code language-sql codeBlock_bY9V thin-scrollbar" style="color:#bfc7d5;background-color:#292d3e"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#bfc7d5"><span class="token plain">window_function</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">OVER</span><span class="token plain"> </span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> </span><span class="token punctuation" style="color:rgb(199, 146, 234)">[</span><span class="token keyword" style="font-style:italic">PARTITION</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">BY</span><span class="token plain"> partitioning expression</span><span class="token punctuation" style="color:rgb(199, 146, 234)">]</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> </span><span class="token punctuation" style="color:rgb(199, 146, 234)">[</span><span class="token keyword" style="font-style:italic">ORDER</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">BY</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">order</span><span class="token plain"> expression</span><span class="token punctuation" style="color:rgb(199, 146, 234)">]</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> </span><span class="token punctuation" style="color:rgb(199, 146, 234)">[</span><span class="token punctuation" style="color:rgb(199, 146, 234)">[</span><span class="token keyword" style="font-style:italic">ROWS</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> RANGE</span><span class="token punctuation" style="color:rgb(199, 146, 234)">]</span><span class="token plain"> </span><span class="token operator" style="color:rgb(137, 221, 255)">BETWEEN</span><span class="token plain"> range </span><span class="token keyword" style="font-style:italic">start</span><span class="token plain"> </span><span class="token operator" style="color:rgb(137, 221, 255)">AND</span><span class="token plain"> range </span><span class="token keyword" style="font-style:italic">end</span><span class="token punctuation" style="color:rgb(199, 146, 234)">]</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"></span><span class="token keyword" style="font-style:italic">FROM</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">table</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"></span><span class="token keyword" style="font-style:italic">GROUP</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">BY</span><span class="token plain"> dimensions</span><br></span></code></pre><div class="buttonGroup__atx"><button type="button" aria-label="Copy code to clipboard" title="Copy" class="clean-btn"><span class="copyButtonIcons_eSgA" aria-hidden="true"><svg viewBox="0 0 24 24" class="copyButtonIcon_y97N"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewBox="0 0 24 24" class="copyButtonSuccessIcon_LjdS"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button></div></div></div> |
| <div class="language-sql codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#bfc7d5;--prism-background-color:#292d3e"><div class="codeBlockContent_biex"><pre tabindex="0" class="prism-code language-sql codeBlock_bY9V thin-scrollbar" style="color:#bfc7d5;background-color:#292d3e"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#bfc7d5"><span class="token plain">window_function</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">OVER</span><span class="token plain"> w</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"></span><span class="token keyword" style="font-style:italic">FROM</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">table</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">WINDOW w </span><span class="token keyword" style="font-style:italic">AS</span><span class="token plain"> </span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token punctuation" style="color:rgb(199, 146, 234)">[</span><span class="token keyword" style="font-style:italic">PARTITION</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">BY</span><span class="token plain"> partitioning expression</span><span class="token punctuation" style="color:rgb(199, 146, 234)">]</span><span class="token plain"> </span><span class="token punctuation" style="color:rgb(199, 146, 234)">[</span><span class="token keyword" style="font-style:italic">ORDER</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">BY</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">order</span><span class="token plain"> expression</span><span class="token punctuation" style="color:rgb(199, 146, 234)">]</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> </span><span class="token punctuation" style="color:rgb(199, 146, 234)">[</span><span class="token punctuation" style="color:rgb(199, 146, 234)">[</span><span class="token keyword" style="font-style:italic">ROWS</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> RANGE</span><span class="token punctuation" style="color:rgb(199, 146, 234)">]</span><span class="token plain"> </span><span class="token operator" style="color:rgb(137, 221, 255)">BETWEEN</span><span class="token plain"> range </span><span class="token keyword" style="font-style:italic">start</span><span class="token plain"> </span><span class="token operator" style="color:rgb(137, 221, 255)">AND</span><span class="token plain"> range </span><span class="token keyword" style="font-style:italic">end</span><span class="token punctuation" style="color:rgb(199, 146, 234)">]</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"></span><span class="token keyword" style="font-style:italic">GROUP</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">BY</span><span class="token plain"> dimensions</span><br></span></code></pre><div class="buttonGroup__atx"><button type="button" aria-label="Copy code to clipboard" title="Copy" class="clean-btn"><span class="copyButtonIcons_eSgA" aria-hidden="true"><svg viewBox="0 0 24 24" class="copyButtonIcon_y97N"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewBox="0 0 24 24" class="copyButtonSuccessIcon_LjdS"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button></div></div></div> |
| <p>The OVER clause defines the query windows for window functions as follows:</p> |
| <ul> |
| <li>PARTITION BY indicates the dimension that defines window boundaries</li> |
| <li>ORDER BY specifies the order of the rows within the windows</li> |
| </ul> |
| <p>An empty OVER clause or the absence of a PARTITION BY clause indicates that all data belongs to a single window.</p> |
| <p>In the following example, the following OVER clause example sets the window dimension to <code>channel</code> and orders the results by the absolute value of <code>delta</code> ascending:</p> |
| <div class="language-sql codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#bfc7d5;--prism-background-color:#292d3e"><div class="codeBlockContent_biex"><pre tabindex="0" class="prism-code language-sql codeBlock_bY9V thin-scrollbar" style="color:#bfc7d5;background-color:#292d3e"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#bfc7d5"><span class="token punctuation" style="color:rgb(199, 146, 234)">.</span><span class="token punctuation" style="color:rgb(199, 146, 234)">.</span><span class="token punctuation" style="color:rgb(199, 146, 234)">.</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">RANK</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">OVER</span><span class="token plain"> </span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token keyword" style="font-style:italic">PARTITION</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">BY</span><span class="token plain"> channel </span><span class="token keyword" style="font-style:italic">ORDER</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">BY</span><span class="token plain"> ABS</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">delta</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">ASC</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"></span><span class="token punctuation" style="color:rgb(199, 146, 234)">.</span><span class="token punctuation" style="color:rgb(199, 146, 234)">.</span><span class="token punctuation" style="color:rgb(199, 146, 234)">.</span><br></span></code></pre><div class="buttonGroup__atx"><button type="button" aria-label="Copy code to clipboard" title="Copy" class="clean-btn"><span class="copyButtonIcons_eSgA" aria-hidden="true"><svg viewBox="0 0 24 24" class="copyButtonIcon_y97N"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewBox="0 0 24 24" class="copyButtonSuccessIcon_LjdS"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button></div></div></div> |
| <p>Window frames, set in ROWS and RANGE expressions, limit the set of rows used for the windowed aggregation.</p> |
| <p>ROWS and RANGE accept the following values for <code>range start</code> and <code>range end</code>:</p> |
| <ul> |
| <li>UNBOUNDED PRECEDING: from the beginning of the window as ordered by the order expression</li> |
| <li><em>N</em> ROWS PRECEDING: <em>N</em> rows before the current row as ordered by the order expression</li> |
| <li>CURRENT ROW: the current row</li> |
| <li><em>N</em> ROWS FOLLOWING: <em>N</em> rows after the current row as ordered by the order expression</li> |
| <li>UNBOUNDED FOLLOWING: to the end of the window as ordered by the order expression</li> |
| </ul> |
| <p>See <a href="#example-with-window-frames">Example with window frames</a> for more detail.</p> |
| <p>Druid applies the GROUP BY dimensions before calculating all non-window aggregation functions. Then it applies the window function over the aggregated results.</p> |
| <div class="theme-admonition theme-admonition-note admonition_xJq3 alert alert--secondary"><div class="admonitionHeading_Gvgb"><span class="admonitionIcon_Rf37"><svg viewBox="0 0 14 16"><path fill-rule="evenodd" d="M6.3 5.69a.942.942 0 0 1-.28-.7c0-.28.09-.52.28-.7.19-.18.42-.28.7-.28.28 0 .52.09.7.28.18.19.28.42.28.7 0 .28-.09.52-.28.7a1 1 0 0 1-.7.3c-.28 0-.52-.11-.7-.3zM8 7.99c-.02-.25-.11-.48-.31-.69-.2-.19-.42-.3-.69-.31H6c-.27.02-.48.13-.69.31-.2.2-.3.44-.31.69h1v3c.02.27.11.5.31.69.2.2.42.31.69.31h1c.27 0 .48-.11.69-.31.2-.19.3-.42.31-.69H8V7.98v.01zM7 2.3c-3.14 0-5.7 2.54-5.7 5.68 0 3.14 2.56 5.7 5.7 5.7s5.7-2.55 5.7-5.7c0-3.15-2.56-5.69-5.7-5.69v.01zM7 .98c3.86 0 7 3.14 7 7s-3.14 7-7 7-7-3.12-7-7 3.14-7 7-7z"></path></svg></span>note</div><div class="admonitionContent_BuS1"><p>Sometimes windows are called partitions. However, the partitioning for window functions are a shuffle (partition) of the result set created at query time and is not to be confused with Druid's segment partitioning feature which partitions data at ingest time.</p></div></div> |
| <h3 class="anchor anchorWithStickyNavbar_LWe7" id="order-by-windows">ORDER BY windows<a href="#order-by-windows" class="hash-link" aria-label="Direct link to ORDER BY windows" title="Direct link to ORDER BY windows"></a></h3> |
| <p>When the window definition only specifies ORDER BY and not PARTITION BY, it sorts the aggregate data set and applies the function in that order.</p> |
| <p>The following query uses <code>ORDER BY SUM(delta) DESC</code> to rank user hourly activity from the most changed the least changed within an hour:</p> |
| <div class="language-sql codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#bfc7d5;--prism-background-color:#292d3e"><div class="codeBlockContent_biex"><pre tabindex="0" class="prism-code language-sql codeBlock_bY9V thin-scrollbar" style="color:#bfc7d5;background-color:#292d3e"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#bfc7d5"><span class="token keyword" style="font-style:italic">SELECT</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> TIME_FLOOR</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">__time</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> </span><span class="token string" style="color:rgb(195, 232, 141)">'PT1H'</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">as</span><span class="token plain"> time_hour</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> </span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> channel</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> </span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> </span><span class="token keyword" style="font-style:italic">user</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> </span><span class="token function" style="color:rgb(130, 170, 255)">SUM</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">delta</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> net_user_changes</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> RANK</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">OVER</span><span class="token plain"> </span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token keyword" style="font-style:italic">ORDER</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">BY</span><span class="token plain"> </span><span class="token function" style="color:rgb(130, 170, 255)">SUM</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">delta</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">DESC</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">AS</span><span class="token plain"> editing_rank</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"></span><span class="token keyword" style="font-style:italic">FROM</span><span class="token plain"> </span><span class="token string" style="color:rgb(195, 232, 141)">"wikipedia"</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"></span><span class="token keyword" style="font-style:italic">WHERE</span><span class="token plain"> channel </span><span class="token operator" style="color:rgb(137, 221, 255)">IN</span><span class="token plain"> </span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token string" style="color:rgb(195, 232, 141)">'#kk.wikipedia'</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> </span><span class="token string" style="color:rgb(195, 232, 141)">'#lt.wikipedia'</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> </span><span class="token operator" style="color:rgb(137, 221, 255)">AND</span><span class="token plain"> __time </span><span class="token operator" style="color:rgb(137, 221, 255)">BETWEEN</span><span class="token plain"> </span><span class="token string" style="color:rgb(195, 232, 141)">'2016-06-27'</span><span class="token plain"> </span><span class="token operator" style="color:rgb(137, 221, 255)">AND</span><span class="token plain"> </span><span class="token string" style="color:rgb(195, 232, 141)">'2016-06-28'</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"></span><span class="token keyword" style="font-style:italic">GROUP</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">BY</span><span class="token plain"> TIME_FLOOR</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">__time</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> </span><span class="token string" style="color:rgb(195, 232, 141)">'PT1H'</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> channel</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">user</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"></span><span class="token keyword" style="font-style:italic">ORDER</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">BY</span><span class="token plain"> </span><span class="token number" style="color:rgb(247, 140, 108)">5</span><span class="token plain"> </span><br></span></code></pre><div class="buttonGroup__atx"><button type="button" aria-label="Copy code to clipboard" title="Copy" class="clean-btn"><span class="copyButtonIcons_eSgA" aria-hidden="true"><svg viewBox="0 0 24 24" class="copyButtonIcon_y97N"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewBox="0 0 24 24" class="copyButtonSuccessIcon_LjdS"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button></div></div></div> |
| <details class="details_lb9f alert alert--info details_b_Ee" data-collapsed="true"><summary> View results </summary><div><div class="collapsibleContent_i85q"><table><thead><tr><th><code>time_hour</code></th><th><code>channel</code></th><th><code>user</code></th><th><code>net_user_changes</code></th><th><code>editing_rank</code></th></tr></thead><tbody><tr><td><code>2016-06-27T15:00:00.000Z</code></td><td><code>#kk.wikipedia</code></td><td><code>Nurkhan</code></td><td>6900</td><td>1</td></tr><tr><td><code>2016-06-27T19:00:00.000Z</code></td><td><code>#lt.wikipedia</code></td><td><code>77.221.66.41</code></td><td>4358</td><td>2</td></tr><tr><td><code>2016-06-27T09:00:00.000Z</code></td><td><code>#kk.wikipedia</code></td><td><code>Салиха</code></td><td>2702</td><td>3</td></tr><tr><td><code>2016-06-27T04:00:00.000Z</code></td><td><code>#kk.wikipedia</code></td><td><code>Nurkhan</code></td><td>2440</td><td>4</td></tr><tr><td><code>2016-06-27T09:00:00.000Z</code></td><td><code>#lt.wikipedia</code></td><td><code>80.4.147.222</code></td><td>894</td><td>5</td></tr><tr><td><code>2016-06-27T09:00:00.000Z</code></td><td><code>#lt.wikipedia</code></td><td><code>178.11.203.212</code></td><td>447</td><td>6</td></tr><tr><td><code>2016-06-27T11:00:00.000Z</code></td><td><code>#kk.wikipedia</code></td><td><code>Нұрлан Рахымжанов</code></td><td>126</td><td>7</td></tr><tr><td><code>2016-06-27T06:00:00.000Z</code></td><td><code>#kk.wikipedia</code></td><td><code>Шокай</code></td><td>91</td><td>8</td></tr><tr><td><code>2016-06-27T11:00:00.000Z</code></td><td><code>#lt.wikipedia</code></td><td><code>MaryroseB54</code></td><td>59</td><td>9</td></tr><tr><td><code>2016-06-27T04:00:00.000Z</code></td><td><code>#kk.wikipedia</code></td><td><code>Нұрлан Рахымжанов</code></td><td>56</td><td>10</td></tr><tr><td><code>2016-06-27T12:00:00.000Z</code></td><td><code>#lt.wikipedia</code></td><td><code>Karoliuk</code></td><td>53</td><td>11</td></tr><tr><td><code>2016-06-27T12:00:00.000Z</code></td><td><code>#lt.wikipedia</code></td><td><code>Powermelon</code></td><td>28</td><td>12</td></tr><tr><td><code>2016-06-27T07:00:00.000Z</code></td><td><code>#lt.wikipedia</code></td><td><code>Powermelon</code></td><td>13</td><td>13</td></tr><tr><td><code>2016-06-27T10:00:00.000Z</code></td><td><code>#lt.wikipedia</code></td><td><code>80.4.147.222</code></td><td>1</td><td>14</td></tr><tr><td><code>2016-06-27T07:00:00.000Z</code></td><td><code>#kk.wikipedia</code></td><td><code>Салиха</code></td><td>-1</td><td>15</td></tr><tr><td><code>2016-06-27T06:00:00.000Z</code></td><td><code>#lt.wikipedia</code></td><td><code>Powermelon</code></td><td>-2</td><td>16</td></tr></tbody></table></div></div></details> |
| <h3 class="anchor anchorWithStickyNavbar_LWe7" id="partition-by-windows">PARTITION BY windows<a href="#partition-by-windows" class="hash-link" aria-label="Direct link to PARTITION BY windows" title="Direct link to PARTITION BY windows"></a></h3> |
| <p>When a window only specifies PARTITION BY partition expression, Druid calculates the aggregate window function over all the rows that share a value within the selected dataset.</p> |
| <p>The following example demonstrates a query that uses two different windows—<code>PARTITION BY channel</code> and <code>PARTITION BY user</code>—to calculate the total activity in the channel and total activity by the user so that they can be compared to individual hourly activity:</p> |
| <div class="language-sql codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#bfc7d5;--prism-background-color:#292d3e"><div class="codeBlockContent_biex"><pre tabindex="0" class="prism-code language-sql codeBlock_bY9V thin-scrollbar" style="color:#bfc7d5;background-color:#292d3e"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#bfc7d5"><span class="token keyword" style="font-style:italic">SELECT</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> TIME_FLOOR</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">__time</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> </span><span class="token string" style="color:rgb(195, 232, 141)">'PT1H'</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">as</span><span class="token plain"> time_hour</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> channel</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> </span><span class="token keyword" style="font-style:italic">user</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> </span><span class="token function" style="color:rgb(130, 170, 255)">SUM</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">delta</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">AS</span><span class="token plain"> hourly_user_changes</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> </span><span class="token function" style="color:rgb(130, 170, 255)">SUM</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token function" style="color:rgb(130, 170, 255)">SUM</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">delta</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">OVER</span><span class="token plain"> </span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token keyword" style="font-style:italic">PARTITION</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">BY</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">user</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">AS</span><span class="token plain"> total_user_changes</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> </span><span class="token function" style="color:rgb(130, 170, 255)">SUM</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token function" style="color:rgb(130, 170, 255)">SUM</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">delta</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">OVER</span><span class="token plain"> </span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token keyword" style="font-style:italic">PARTITION</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">BY</span><span class="token plain"> channel</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">AS</span><span class="token plain"> total_channel_changes</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"></span><span class="token keyword" style="font-style:italic">FROM</span><span class="token plain"> </span><span class="token string" style="color:rgb(195, 232, 141)">"wikipedia"</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"></span><span class="token keyword" style="font-style:italic">WHERE</span><span class="token plain"> channel </span><span class="token operator" style="color:rgb(137, 221, 255)">IN</span><span class="token plain"> </span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token string" style="color:rgb(195, 232, 141)">'#kk.wikipedia'</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> </span><span class="token string" style="color:rgb(195, 232, 141)">'#lt.wikipedia'</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> </span><span class="token operator" style="color:rgb(137, 221, 255)">AND</span><span class="token plain"> __time </span><span class="token operator" style="color:rgb(137, 221, 255)">BETWEEN</span><span class="token plain"> </span><span class="token string" style="color:rgb(195, 232, 141)">'2016-06-27'</span><span class="token plain"> </span><span class="token operator" style="color:rgb(137, 221, 255)">AND</span><span class="token plain"> </span><span class="token string" style="color:rgb(195, 232, 141)">'2016-06-28'</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"></span><span class="token keyword" style="font-style:italic">GROUP</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">BY</span><span class="token plain"> TIME_FLOOR</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">__time</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> </span><span class="token string" style="color:rgb(195, 232, 141)">'PT1H'</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> </span><span class="token number" style="color:rgb(247, 140, 108)">2</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> </span><span class="token number" style="color:rgb(247, 140, 108)">3</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"></span><span class="token keyword" style="font-style:italic">ORDER</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">BY</span><span class="token plain"> channel</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> TIME_FLOOR</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">__time</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> </span><span class="token string" style="color:rgb(195, 232, 141)">'PT1H'</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">user</span><br></span></code></pre><div class="buttonGroup__atx"><button type="button" aria-label="Copy code to clipboard" title="Copy" class="clean-btn"><span class="copyButtonIcons_eSgA" aria-hidden="true"><svg viewBox="0 0 24 24" class="copyButtonIcon_y97N"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewBox="0 0 24 24" class="copyButtonSuccessIcon_LjdS"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button></div></div></div> |
| <details class="details_lb9f alert alert--info details_b_Ee" data-collapsed="true"><summary> View results </summary><div><div class="collapsibleContent_i85q"><table><thead><tr><th><code>time_hour</code></th><th><code>channel</code></th><th><code>user</code></th><th><code>hourly_user_changes</code></th><th><code>total_user_changes</code></th><th><code>total_channel_changes</code></th></tr></thead><tbody><tr><td><code>2016-06-27T04:00:00.000Z</code></td><td><code>#kk.wikipedia</code></td><td><code>Nurkhan</code></td><td>2440</td><td>9340</td><td>12314</td></tr><tr><td><code>2016-06-27T04:00:00.000Z</code></td><td><code>#kk.wikipedia</code></td><td><code>Нұрлан Рахымжанов</code></td><td>56</td><td>182</td><td>12314</td></tr><tr><td><code>2016-06-27T06:00:00.000Z</code></td><td><code>#kk.wikipedia</code></td><td><code>Шокай</code></td><td>91</td><td>91</td><td>12314</td></tr><tr><td><code>2016-06-27T07:00:00.000Z</code></td><td><code>#kk.wikipedia</code></td><td><code>Салиха</code></td><td>-1</td><td>2701</td><td>12314</td></tr><tr><td><code>2016-06-27T09:00:00.000Z</code></td><td><code>#kk.wikipedia</code></td><td><code>Салиха</code></td><td>2702</td><td>2701</td><td>12314</td></tr><tr><td><code>2016-06-27T11:00:00.000Z</code></td><td><code>#kk.wikipedia</code></td><td><code>Нұрлан Рахымжанов</code></td><td>126</td><td>182</td><td>12314</td></tr><tr><td><code>2016-06-27T15:00:00.000Z</code></td><td><code>#kk.wikipedia</code></td><td><code>Nurkhan</code></td><td>6900</td><td>9340</td><td>12314</td></tr><tr><td><code>2016-06-27T06:00:00.000Z</code></td><td><code>#lt.wikipedia</code></td><td><code>Powermelon</code></td><td>-2</td><td>39</td><td>5851</td></tr><tr><td><code>2016-06-27T07:00:00.000Z</code></td><td><code>#lt.wikipedia</code></td><td><code>Powermelon</code></td><td>13</td><td>39</td><td>5851</td></tr><tr><td><code>2016-06-27T09:00:00.000Z</code></td><td><code>#lt.wikipedia</code></td><td><code>178.11.203.212</code></td><td>447</td><td>447</td><td>5851</td></tr><tr><td><code>2016-06-27T09:00:00.000Z</code></td><td><code>#lt.wikipedia</code></td><td><code>80.4.147.222</code></td><td>894</td><td>895</td><td>5851</td></tr><tr><td><code>2016-06-27T10:00:00.000Z</code></td><td><code>#lt.wikipedia</code></td><td><code>80.4.147.222</code></td><td>1</td><td>895</td><td>5851</td></tr><tr><td><code>2016-06-27T11:00:00.000Z</code></td><td><code>#lt.wikipedia</code></td><td><code>MaryroseB54</code></td><td>59</td><td>59</td><td>5851</td></tr><tr><td><code>2016-06-27T12:00:00.000Z</code></td><td><code>#lt.wikipedia</code></td><td><code>Karoliuk</code></td><td>53</td><td>53</td><td>5851</td></tr><tr><td><code>2016-06-27T12:00:00.000Z</code></td><td><code>#lt.wikipedia</code></td><td><code>Powermelon</code></td><td>28</td><td>39</td><td>5851</td></tr><tr><td><code>2016-06-27T19:00:00.000Z</code></td><td><code>#lt.wikipedia</code></td><td><code>77.221.66.41</code></td><td>4358</td><td>4358</td><td>5851</td></tr></tbody></table></div></div></details> |
| <p>In this example, the dataset is filtered for a single day. Therefore the window function results represent the total activity for the day, for the <code>user</code> and for the <code>channel</code> dimensions respectively.</p> |
| <p>This type of result helps you analyze the impact of an individual user's hourly activity:</p> |
| <ul> |
| <li>the impact to the channel by comparing <code>hourly_user_changes</code> to <code>total_channel_changes</code></li> |
| <li>the impact of each user over the channel by <code>total_user_changes</code> to <code>total_channel_changes</code></li> |
| <li>the progress of each user's individual activity by comparing <code>hourly_user_changes</code> to <code>total_user_changes</code></li> |
| </ul> |
| <h4 class="anchor anchorWithStickyNavbar_LWe7" id="window-frame-guardrails">Window frame guardrails<a href="#window-frame-guardrails" class="hash-link" aria-label="Direct link to Window frame guardrails" title="Direct link to Window frame guardrails"></a></h4> |
| <p>Druid has guardrail logic to prevent you from executing window function queries with window frame expressions that might return unexpected results.</p> |
| <p>For example:</p> |
| <ul> |
| <li>You cannot set expressions as bounds for window frames.</li> |
| <li>You can only use a RANGE frames when both endpoints are unbounded or current row.</li> |
| </ul> |
| <h2 class="anchor anchorWithStickyNavbar_LWe7" id="window-function-reference">Window function reference<a href="#window-function-reference" class="hash-link" aria-label="Direct link to Window function reference" title="Direct link to Window function reference"></a></h2> |
| <table><thead><tr><th>Function</th><th>Notes</th></tr></thead><tbody><tr><td><code>ROW_NUMBER()</code></td><td>Returns the number of the row within the window starting from 1</td></tr><tr><td><code>RANK()</code></td><td>Returns the rank with gaps for a row within a window. For example, if two rows tie for rank 1, the next rank is 3</td></tr><tr><td><code>DENSE_RANK()</code></td><td>Returns the rank for a row within a window without gaps. For example, if two rows tie for rank of 1, the subsequent row is ranked 2.</td></tr><tr><td><code>PERCENT_RANK()</code></td><td>Returns the relative rank of the row calculated as a percentage according to the formula: <code>RANK() OVER (window) / COUNT(1) OVER (window)</code></td></tr><tr><td><code>CUME_DIST()</code></td><td>Returns the cumulative distribution of the current row within the window calculated as number of window rows at the same rank or higher than current row divided by total window rows. The return value ranges between <code>1/number of rows</code> and 1</td></tr><tr><td><code>NTILE(tiles)</code></td><td>Divides the rows within a window as evenly as possible into the number of tiles, also called buckets, and returns the value of the tile that the row falls into</td></tr><tr><td><code>LAG(expr[, offset])</code></td><td>If you do not supply an <code>offset</code>, returns the value evaluated at the row preceding the current row. Specify an offset number, <code>n</code>, to return the value evaluated at <code>n</code> rows preceding the current one</td></tr><tr><td><code>LEAD(expr[, offset])</code></td><td>If you do not supply an <code>offset</code>, returns the value evaluated at the row following the current row. Specify an offset number <code>n</code> to return the value evaluated at <code>n</code> rows following the current one; if there is no such row, returns the given default value</td></tr><tr><td><code>FIRST_VALUE(expr)</code></td><td>Returns the value evaluated for the expression for the first row within the window</td></tr><tr><td><code>LAST_VALUE(expr)</code></td><td>Returns the value evaluated for the expression for the last row within the window</td></tr></tbody></table> |
| <h2 class="anchor anchorWithStickyNavbar_LWe7" id="examples">Examples<a href="#examples" class="hash-link" aria-label="Direct link to Examples" title="Direct link to Examples"></a></h2> |
| <p>The following example illustrates all of the built-in window functions to compare the number of characters changed per event for a channel in the Wikipedia data set.</p> |
| <div class="language-sql codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#bfc7d5;--prism-background-color:#292d3e"><div class="codeBlockContent_biex"><pre tabindex="0" class="prism-code language-sql codeBlock_bY9V thin-scrollbar" style="color:#bfc7d5;background-color:#292d3e"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#bfc7d5"><span class="token keyword" style="font-style:italic">SELECT</span><span class="token plain"> FLOOR</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">__time </span><span class="token keyword" style="font-style:italic">TO</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">DAY</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">AS</span><span class="token plain"> event_time</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> channel</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> ABS</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">delta</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">AS</span><span class="token plain"> change</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> ROW_NUMBER</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">OVER</span><span class="token plain"> w </span><span class="token keyword" style="font-style:italic">AS</span><span class="token plain"> row_no</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> RANK</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">OVER</span><span class="token plain"> w </span><span class="token keyword" style="font-style:italic">AS</span><span class="token plain"> rank_no</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> DENSE_RANK</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">OVER</span><span class="token plain"> w </span><span class="token keyword" style="font-style:italic">AS</span><span class="token plain"> dense_rank_no</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> PERCENT_RANK</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">OVER</span><span class="token plain"> w </span><span class="token keyword" style="font-style:italic">AS</span><span class="token plain"> pct_rank</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> CUME_DIST</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">OVER</span><span class="token plain"> w </span><span class="token keyword" style="font-style:italic">AS</span><span class="token plain"> cumulative_dist</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> NTILE</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token number" style="color:rgb(247, 140, 108)">4</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">OVER</span><span class="token plain"> w </span><span class="token keyword" style="font-style:italic">AS</span><span class="token plain"> ntile_val</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> LAG</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">ABS</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">delta</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> </span><span class="token number" style="color:rgb(247, 140, 108)">1</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> </span><span class="token number" style="color:rgb(247, 140, 108)">0</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">OVER</span><span class="token plain"> w </span><span class="token keyword" style="font-style:italic">AS</span><span class="token plain"> lag_val</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> LEAD</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">ABS</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">delta</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> </span><span class="token number" style="color:rgb(247, 140, 108)">1</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> </span><span class="token number" style="color:rgb(247, 140, 108)">0</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">OVER</span><span class="token plain"> w </span><span class="token keyword" style="font-style:italic">AS</span><span class="token plain"> lead_val</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> FIRST_VALUE</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">ABS</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">delta</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">OVER</span><span class="token plain"> w </span><span class="token keyword" style="font-style:italic">AS</span><span class="token plain"> first_val</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> LAST_VALUE</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">ABS</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">delta</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">OVER</span><span class="token plain"> w </span><span class="token keyword" style="font-style:italic">AS</span><span class="token plain"> last_val</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"></span><span class="token keyword" style="font-style:italic">FROM</span><span class="token plain"> wikipedia</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"></span><span class="token keyword" style="font-style:italic">WHERE</span><span class="token plain"> channel </span><span class="token operator" style="color:rgb(137, 221, 255)">IN</span><span class="token plain"> </span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token string" style="color:rgb(195, 232, 141)">'#kk.wikipedia'</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> </span><span class="token string" style="color:rgb(195, 232, 141)">'#lt.wikipedia'</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"></span><span class="token keyword" style="font-style:italic">GROUP</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">BY</span><span class="token plain"> channel</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> ABS</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">delta</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> FLOOR</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">__time </span><span class="token keyword" style="font-style:italic">TO</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">DAY</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">WINDOW w </span><span class="token keyword" style="font-style:italic">AS</span><span class="token plain"> </span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token keyword" style="font-style:italic">PARTITION</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">BY</span><span class="token plain"> channel </span><span class="token keyword" style="font-style:italic">ORDER</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">BY</span><span class="token plain"> ABS</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">delta</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">ASC</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><br></span></code></pre><div class="buttonGroup__atx"><button type="button" aria-label="Copy code to clipboard" title="Copy" class="clean-btn"><span class="copyButtonIcons_eSgA" aria-hidden="true"><svg viewBox="0 0 24 24" class="copyButtonIcon_y97N"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewBox="0 0 24 24" class="copyButtonSuccessIcon_LjdS"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button></div></div></div> |
| <details class="details_lb9f alert alert--info details_b_Ee" data-collapsed="true"><summary> View results </summary><div><div class="collapsibleContent_i85q"><table><thead><tr><th><code>event_time</code></th><th><code>channel</code></th><th><code>change</code></th><th><code>row_no</code></th><th><code>rank_no</code></th><th><code>dense_rank_no</code></th><th><code>pct_rank</code></th><th><code>cumulative_dist</code></th><th><code>ntile_val</code></th><th><code>lag_val</code></th><th><code>lead_val</code></th><th><code>first_val</code></th><th><code>last_val</code></th></tr></thead><tbody><tr><td><code>2016-06-27T00:00:00.000Z</code></td><td><code>#kk.wikipedia</code></td><td>1</td><td>1</td><td>1</td><td>1</td><td>0.0</td><td>0.125</td><td>1</td><td>null</td><td>7</td><td>1</td><td>6900</td></tr><tr><td><code>2016-06-27T00:00:00.000Z</code></td><td><code>#kk.wikipedia</code></td><td>7</td><td>2</td><td>2</td><td>2</td><td>0.14285714285714285</td><td>0.25</td><td>1</td><td>1</td><td>56</td><td>1</td><td>6900</td></tr><tr><td><code>2016-06-27T00:00:00.000Z</code></td><td><code>#kk.wikipedia</code></td><td>56</td><td>3</td><td>3</td><td>3</td><td>0.2857142857142857</td><td>0.375</td><td>2</td><td>7</td><td>63</td><td>1</td><td>6900</td></tr><tr><td><code>2016-06-27T00:00:00.000Z</code></td><td><code>#kk.wikipedia</code></td><td>63</td><td>4</td><td>4</td><td>4</td><td>0.42857142857142855</td><td>0.5</td><td>2</td><td>56</td><td>91</td><td>1</td><td>6900</td></tr><tr><td><code>2016-06-27T00:00:00.000Z</code></td><td><code>#kk.wikipedia</code></td><td>91</td><td>5</td><td>5</td><td>5</td><td>0.5714285714285714</td><td>0.625</td><td>3</td><td>63</td><td>2440</td><td>1</td><td>6900</td></tr><tr><td><code>2016-06-27T00:00:00.000Z</code></td><td><code>#kk.wikipedia</code></td><td>2440</td><td>6</td><td>6</td><td>6</td><td>0.7142857142857143</td><td>0.75</td><td>3</td><td>91</td><td>2703</td><td>1</td><td>6900</td></tr><tr><td><code>2016-06-27T00:00:00.000Z</code></td><td><code>#kk.wikipedia</code></td><td>2703</td><td>7</td><td>7</td><td>7</td><td>0.8571428571428571</td><td>0.875</td><td>4</td><td>2440</td><td>6900</td><td>1</td><td>6900</td></tr><tr><td><code>2016-06-27T00:00:00.000Z</code></td><td><code>#kk.wikipedia</code></td><td>6900</td><td>8</td><td>8</td><td>8</td><td>1</td><td>1</td><td>4</td><td>2703</td><td>null</td><td>1</td><td>6900</td></tr><tr><td><code>2016-06-27T00:00:00.000Z</code></td><td><code>#lt.wikipedia</code></td><td>1</td><td>1</td><td>1</td><td>1</td><td>0</td><td>0.1</td><td>1</td><td>null</td><td>2</td><td>1</td><td>4358</td></tr><tr><td><code>2016-06-27T00:00:00.000Z</code></td><td><code>#lt.wikipedia</code></td><td>2</td><td>2</td><td>2</td><td>2</td><td>0.1111111111111111</td><td>0.2</td><td>1</td><td>1</td><td>13</td><td>1</td><td>4358</td></tr><tr><td><code>2016-06-27T00:00:00.000Z</code></td><td><code>#lt.wikipedia</code></td><td>13</td><td>3</td><td>3</td><td>3</td><td>0.2222222222222222</td><td>0.3</td><td>1</td><td>2</td><td>28</td><td>1</td><td>4358</td></tr><tr><td><code>2016-06-27T00:00:00.000Z</code></td><td><code>#lt.wikipedia</code></td><td>28</td><td>4</td><td>4</td><td>4</td><td>0.3333333333333333</td><td>0.4</td><td>2</td><td>13</td><td>53</td><td>1</td><td>4358</td></tr><tr><td><code>2016-06-27T00:00:00.000Z</code></td><td><code>#lt.wikipedia</code></td><td>53</td><td>5</td><td>5</td><td>5</td><td>0.4444444444444444</td><td>0.5</td><td>2</td><td>28</td><td>56</td><td>1</td><td>4358</td></tr><tr><td><code>2016-06-27T00:00:00.000Z</code></td><td><code>#lt.wikipedia</code></td><td>56</td><td>6</td><td>6</td><td>6</td><td>0.5555555555555556</td><td>0.6</td><td>2</td><td>53</td><td>59</td><td>1</td><td>4358</td></tr><tr><td><code>2016-06-27T00:00:00.000Z</code></td><td><code>#lt.wikipedia</code></td><td>59</td><td>7</td><td>7</td><td>7</td><td>0.6666666666666666</td><td>0.7</td><td>3</td><td>56</td><td>391</td><td>1</td><td>4358</td></tr><tr><td><code>2016-06-27T00:00:00.000Z</code></td><td><code>#lt.wikipedia</code></td><td>391</td><td>8</td><td>8</td><td>8</td><td>0.7777777777777778</td><td>0.8</td><td>3</td><td>59</td><td>894</td><td>1</td><td>4358</td></tr><tr><td><code>2016-06-27T00:00:00.000Z</code></td><td><code>#lt.wikipedia</code></td><td>894</td><td>9</td><td>9</td><td>9</td><td>0.8888888888888888</td><td>0.9</td><td>4</td><td>391</td><td>4358</td><td>1</td><td>4358</td></tr><tr><td><code>2016-06-27T00:00:00.000Z</code></td><td><code>#lt.wikipedia</code></td><td>4358</td><td>10</td><td>10</td><td>10</td><td>1</td><td>1</td><td>4</td><td>894</td><td>null</td><td>1</td><td>4358</td></tr></tbody></table></div></div></details> |
| <p>The following example demonstrates applying the SUM() function over the values in a window to calculate the cumulative changes to a channel over time:</p> |
| <div class="language-sql codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#bfc7d5;--prism-background-color:#292d3e"><div class="codeBlockContent_biex"><pre tabindex="0" class="prism-code language-sql codeBlock_bY9V thin-scrollbar" style="color:#bfc7d5;background-color:#292d3e"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#bfc7d5"><span class="token keyword" style="font-style:italic">SELECT</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> FLOOR</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">__time </span><span class="token keyword" style="font-style:italic">TO</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">MINUTE</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">as</span><span class="token plain"> </span><span class="token string" style="color:rgb(195, 232, 141)">"time"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> channel</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> ABS</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">delta</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">AS</span><span class="token plain"> changes</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> </span><span class="token function" style="color:rgb(130, 170, 255)">sum</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">ABS</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">delta</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">OVER</span><span class="token plain"> </span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token keyword" style="font-style:italic">PARTITION</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">BY</span><span class="token plain"> channel </span><span class="token keyword" style="font-style:italic">ORDER</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">BY</span><span class="token plain"> FLOOR</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">__time </span><span class="token keyword" style="font-style:italic">TO</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">MINUTE</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">ASC</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">AS</span><span class="token plain"> cum_changes</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"></span><span class="token keyword" style="font-style:italic">FROM</span><span class="token plain"> wikipedia</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"></span><span class="token keyword" style="font-style:italic">WHERE</span><span class="token plain"> channel </span><span class="token operator" style="color:rgb(137, 221, 255)">IN</span><span class="token plain"> </span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token string" style="color:rgb(195, 232, 141)">'#kk.wikipedia'</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> </span><span class="token string" style="color:rgb(195, 232, 141)">'#lt.wikipedia'</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"></span><span class="token keyword" style="font-style:italic">GROUP</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">BY</span><span class="token plain"> channel</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> __time</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> delta</span><br></span></code></pre><div class="buttonGroup__atx"><button type="button" aria-label="Copy code to clipboard" title="Copy" class="clean-btn"><span class="copyButtonIcons_eSgA" aria-hidden="true"><svg viewBox="0 0 24 24" class="copyButtonIcon_y97N"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewBox="0 0 24 24" class="copyButtonSuccessIcon_LjdS"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button></div></div></div> |
| <details class="details_lb9f alert alert--info details_b_Ee" data-collapsed="true"><summary> View results </summary><div><div class="collapsibleContent_i85q"><table><thead><tr><th><code>time</code></th><th><code>channel</code></th><th><code>changes</code></th><th><code>cum_changes</code></th></tr></thead><tbody><tr><td><code>2016-06-27T04:20:00.000Z</code></td><td><code>#kk.wikipedia</code></td><td>56</td><td>56</td></tr><tr><td><code>2016-06-27T04:35:00.000Z</code></td><td><code>#kk.wikipedia</code></td><td>2440</td><td>2496</td></tr><tr><td><code>2016-06-27T06:15:00.000Z</code></td><td><code>#kk.wikipedia</code></td><td>91</td><td>2587</td></tr><tr><td><code>2016-06-27T07:32:00.000Z</code></td><td><code>#kk.wikipedia</code></td><td>1</td><td>2588</td></tr><tr><td><code>2016-06-27T09:00:00.000Z</code></td><td><code>#kk.wikipedia</code></td><td>2703</td><td>5291</td></tr><tr><td><code>2016-06-27T09:24:00.000Z</code></td><td><code>#kk.wikipedia</code></td><td>1</td><td>5292</td></tr><tr><td><code>2016-06-27T11:00:00.000Z</code></td><td><code>#kk.wikipedia</code></td><td>63</td><td>5355</td></tr><tr><td><code>2016-06-27T11:05:00.000Z</code></td><td><code>#kk.wikipedia</code></td><td>7</td><td>5362</td></tr><tr><td><code>2016-06-27T11:32:00.000Z</code></td><td><code>#kk.wikipedia</code></td><td>56</td><td>5418</td></tr><tr><td><code>2016-06-27T15:21:00.000Z</code></td><td><code>#kk.wikipedia</code></td><td>6900</td><td>12318</td></tr><tr><td><code>2016-06-27T06:17:00.000Z</code></td><td><code>#lt.wikipedia</code></td><td>2</td><td>2</td></tr><tr><td><code>2016-06-27T07:55:00.000Z</code></td><td><code>#lt.wikipedia</code></td><td>13</td><td>15</td></tr><tr><td><code>2016-06-27T09:05:00.000Z</code></td><td><code>#lt.wikipedia</code></td><td>894</td><td>909</td></tr><tr><td><code>2016-06-27T09:12:00.000Z</code></td><td><code>#lt.wikipedia</code></td><td>391</td><td>1300</td></tr><tr><td><code>2016-06-27T09:23:00.000Z</code></td><td><code>#lt.wikipedia</code></td><td>56</td><td>1356</td></tr><tr><td><code>2016-06-27T10:59:00.000Z</code></td><td><code>#lt.wikipedia</code></td><td>1</td><td>1357</td></tr><tr><td><code>2016-06-27T11:49:00.000Z</code></td><td><code>#lt.wikipedia</code></td><td>59</td><td>1416</td></tr><tr><td><code>2016-06-27T12:41:00.000Z</code></td><td><code>#lt.wikipedia</code></td><td>53</td><td>1469</td></tr><tr><td><code>2016-06-27T12:58:00.000Z</code></td><td><code>#lt.wikipedia</code></td><td>28</td><td>1497</td></tr><tr><td><code>2016-06-27T19:03:00.000Z</code></td><td><code>#lt.wikipedia</code></td><td>4358</td><td>5855</td></tr></tbody></table></div></div></details> |
| <h3 class="anchor anchorWithStickyNavbar_LWe7" id="example-with-window-frames">Example with window frames<a href="#example-with-window-frames" class="hash-link" aria-label="Direct link to Example with window frames" title="Direct link to Example with window frames"></a></h3> |
| <p>The following query uses a few different window frames to calculate overall activity by channel:</p> |
| <div class="language-sql codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#bfc7d5;--prism-background-color:#292d3e"><div class="codeBlockContent_biex"><pre tabindex="0" class="prism-code language-sql codeBlock_bY9V thin-scrollbar" style="color:#bfc7d5;background-color:#292d3e"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#bfc7d5"><span class="token keyword" style="font-style:italic">SELECT</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> channel</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> </span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> TIME_FLOOR</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">__time</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> </span><span class="token string" style="color:rgb(195, 232, 141)">'PT1H'</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">AS</span><span class="token plain"> time_hour</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> </span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> </span><span class="token function" style="color:rgb(130, 170, 255)">SUM</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">delta</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">AS</span><span class="token plain"> hourly_channel_changes</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> </span><span class="token function" style="color:rgb(130, 170, 255)">SUM</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token function" style="color:rgb(130, 170, 255)">SUM</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">delta</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">OVER</span><span class="token plain"> cumulative </span><span class="token keyword" style="font-style:italic">AS</span><span class="token plain"> cumulative_activity_in_channel</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> </span><span class="token function" style="color:rgb(130, 170, 255)">SUM</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token function" style="color:rgb(130, 170, 255)">SUM</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">delta</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">OVER</span><span class="token plain"> moving5 </span><span class="token keyword" style="font-style:italic">AS</span><span class="token plain"> csum5</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> </span><span class="token function" style="color:rgb(130, 170, 255)">COUNT</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token number" style="color:rgb(247, 140, 108)">1</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">OVER</span><span class="token plain"> moving5 </span><span class="token keyword" style="font-style:italic">AS</span><span class="token plain"> count5</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"></span><span class="token keyword" style="font-style:italic">FROM</span><span class="token plain"> </span><span class="token string" style="color:rgb(195, 232, 141)">"wikipedia"</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"></span><span class="token keyword" style="font-style:italic">WHERE</span><span class="token plain"> channel </span><span class="token operator" style="color:rgb(137, 221, 255)">=</span><span class="token plain"> </span><span class="token string" style="color:rgb(195, 232, 141)">'#en.wikipedia'</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> </span><span class="token operator" style="color:rgb(137, 221, 255)">AND</span><span class="token plain"> __time </span><span class="token operator" style="color:rgb(137, 221, 255)">BETWEEN</span><span class="token plain"> </span><span class="token string" style="color:rgb(195, 232, 141)">'2016-06-27'</span><span class="token plain"> </span><span class="token operator" style="color:rgb(137, 221, 255)">AND</span><span class="token plain"> </span><span class="token string" style="color:rgb(195, 232, 141)">'2016-06-28'</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"></span><span class="token keyword" style="font-style:italic">GROUP</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">BY</span><span class="token plain"> </span><span class="token number" style="color:rgb(247, 140, 108)">1</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> TIME_FLOOR</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">__time</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> </span><span class="token string" style="color:rgb(195, 232, 141)">'PT1H'</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">WINDOW cumulative </span><span class="token keyword" style="font-style:italic">AS</span><span class="token plain"> </span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain"> </span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> </span><span class="token keyword" style="font-style:italic">PARTITION</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">BY</span><span class="token plain"> channel </span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> </span><span class="token keyword" style="font-style:italic">ORDER</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">BY</span><span class="token plain"> TIME_FLOOR</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">__time</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> </span><span class="token string" style="color:rgb(195, 232, 141)">'PT1H'</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> </span><span class="token keyword" style="font-style:italic">ROWS</span><span class="token plain"> </span><span class="token operator" style="color:rgb(137, 221, 255)">BETWEEN</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">UNBOUNDED</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">PRECEDING</span><span class="token plain"> </span><span class="token operator" style="color:rgb(137, 221, 255)">AND</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">CURRENT</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">ROW</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> </span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> </span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> moving5 </span><span class="token keyword" style="font-style:italic">AS</span><span class="token plain"> </span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain"> </span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> </span><span class="token keyword" style="font-style:italic">PARTITION</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">BY</span><span class="token plain"> channel </span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> </span><span class="token keyword" style="font-style:italic">ORDER</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">BY</span><span class="token plain"> TIME_FLOOR</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">__time</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> </span><span class="token string" style="color:rgb(195, 232, 141)">'PT1H'</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> </span><span class="token keyword" style="font-style:italic">ROWS</span><span class="token plain"> </span><span class="token operator" style="color:rgb(137, 221, 255)">BETWEEN</span><span class="token plain"> </span><span class="token number" style="color:rgb(247, 140, 108)">4</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">PRECEDING</span><span class="token plain"> </span><span class="token operator" style="color:rgb(137, 221, 255)">AND</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">CURRENT</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">ROW</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> </span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><br></span></code></pre><div class="buttonGroup__atx"><button type="button" aria-label="Copy code to clipboard" title="Copy" class="clean-btn"><span class="copyButtonIcons_eSgA" aria-hidden="true"><svg viewBox="0 0 24 24" class="copyButtonIcon_y97N"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewBox="0 0 24 24" class="copyButtonSuccessIcon_LjdS"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button></div></div></div> |
| <details class="details_lb9f alert alert--info details_b_Ee" data-collapsed="true"><summary> View results </summary><div><div class="collapsibleContent_i85q"><p>| <code>channel</code> | <code>time_hour</code> | <code>hourly_channel_changes</code> | <code>cumulative_activity_in_channel</code> | <code>csum5</code> | <code>count5</code> | |
| | --- | --- | --- | --- | --- | --- | --- | --- | |
| | <code>#en.wikipedia</code> | <code>2016-06-27T00:00:00.000Z</code> | 74996 | 74996 | 74996 | 1 | |
| | <code>#en.wikipedia</code> | <code>2016-06-27T01:00:00.000Z</code> | 24150 | 99146 | 99146 | 2 | |
| | <code>#en.wikipedia</code> | <code>2016-06-27T02:00:00.000Z</code> | 102372 | 201518 | 201518 | 3 | |
| | <code>#en.wikipedia</code> | <code>2016-06-27T03:00:00.000Z</code> | 61362 | 262880 | 262880 | 4 | |
| | <code>#en.wikipedia</code> | <code>2016-06-27T04:00:00.000Z</code> | 61666 | 324546 | 324546 | 5 | |
| | <code>#en.wikipedia</code> | <code>2016-06-27T05:00:00.000Z</code> | 144199 | 468745 | 393749 | 5 | |
| | <code>#en.wikipedia</code> | <code>2016-06-27T06:00:00.000Z</code> | 33414 | 502159 | 403013 | 5 | |
| | <code>#en.wikipedia</code> | <code>2016-06-27T07:00:00.000Z</code> | 79397 | 581556 | 380038 | 5 | |
| | <code>#en.wikipedia</code> | <code>2016-06-27T08:00:00.000Z</code> | 104436 | 685992 | 423112 | 5 | |
| | <code>#en.wikipedia</code> | <code>2016-06-27T09:00:00.000Z</code> | 58020 | 744012 | 419466 | 5 | |
| | <code>#en.wikipedia</code> | <code>2016-06-27T10:00:00.000Z</code> | 93904 | 837916 | 369171 | 5 | |
| | <code>#en.wikipedia</code> | <code>2016-06-27T11:00:00.000Z</code> | 74436 | 912352 | 410193 | 5 | |
| | <code>#en.wikipedia</code> | <code>2016-06-27T12:00:00.000Z</code> | 83491 | 995843 | 414287 | 5 | |
| | <code>#en.wikipedia</code> | <code>2016-06-27T13:00:00.000Z</code> | 103051 | 1098894 | 412902 | 5 | |
| | <code>#en.wikipedia</code> | <code>2016-06-27T14:00:00.000Z</code> | 211411 | 1310305 | 566293 | 5 | |
| | <code>#en.wikipedia</code> | <code>2016-06-27T15:00:00.000Z</code> | 101247 | 1411552 | 573636 | 5 | |
| | <code>#en.wikipedia</code> | <code>2016-06-27T16:00:00.000Z</code> | 189765 | 1601317 | 688965 | 5 | |
| | <code>#en.wikipedia</code> | <code>2016-06-27T17:00:00.000Z</code> | 74404 | 1675721 | 679878 | 5 | |
| | <code>#en.wikipedia</code> | <code>2016-06-27T18:00:00.000Z</code> | 104824 | 1780545 | 681651 | 5 | |
| | <code>#en.wikipedia</code> | <code>2016-06-27T19:00:00.000Z</code> | 71268 | 1851813 | 541508 | 5 | |
| | <code>#en.wikipedia</code> | <code>2016-06-27T20:00:00.000Z</code> | 88185 | 1939998 | 528446 | 5 | |
| | <code>#en.wikipedia</code> | <code>2016-06-27T21:00:00.000Z</code> | 42584 | 1982582 | 381265 | 5 |</p></div></div></details> |
| <p>The example defines multiple window specifications in the WINDOW clause that you can use for various window function calculations.</p> |
| <p>The query uses two windows:</p> |
| <ul> |
| <li><code>cumulative</code> is partitioned by channel and includes all rows from the beginning of partition up to the current row as ordered by <code>__time</code> to enable cumulative aggregation</li> |
| <li><code>moving5</code> is also partitioned by channel but only includes up to the last four rows and the current row as ordered by time</li> |
| </ul> |
| <p>The number of rows considered for the <code>moving5</code> window for the <code>count5</code> column:</p> |
| <ul> |
| <li>starts at a single row because there are no rows before the current one</li> |
| <li>grows up to five rows as defined by <code>ROWS BETWEEN 4 ROWS PRECEDING AND CURRENT ROW</code></li> |
| </ul> |
| <h2 class="anchor anchorWithStickyNavbar_LWe7" id="known-issues">Known issues<a href="#known-issues" class="hash-link" aria-label="Direct link to Known issues" title="Direct link to Known issues"></a></h2> |
| <p>The following are known issues with window functions:</p> |
| <ul> |
| <li>SELECT * queries without a WHERE clause are not supported. If you want to retrieve all columns in this case, specify the column names.</li> |
| </ul></div></article><nav class="pagination-nav docusaurus-mt-lg" aria-label="Docs pages"><a class="pagination-nav__link pagination-nav__link--prev" href="/docs/latest/querying/sql-aggregations"><div class="pagination-nav__sublabel">Previous</div><div class="pagination-nav__label">Aggregation functions</div></a><a class="pagination-nav__link pagination-nav__link--next" href="/docs/latest/querying/sql-array-functions"><div class="pagination-nav__sublabel">Next</div><div class="pagination-nav__label">Array functions</div></a></nav></div></div><div class="col col--3"><div class="tableOfContents_bqdL thin-scrollbar theme-doc-toc-desktop"><ul class="table-of-contents table-of-contents__left-border"><li><a href="#window-function-syntax" class="table-of-contents__link toc-highlight">Window function syntax</a><ul><li><a href="#order-by-windows" class="table-of-contents__link toc-highlight">ORDER BY windows</a></li><li><a href="#partition-by-windows" class="table-of-contents__link toc-highlight">PARTITION BY windows</a></li></ul></li><li><a href="#window-function-reference" class="table-of-contents__link toc-highlight">Window function reference</a></li><li><a href="#examples" class="table-of-contents__link toc-highlight">Examples</a><ul><li><a href="#example-with-window-frames" class="table-of-contents__link toc-highlight">Example with window frames</a></li></ul></li><li><a href="#known-issues" class="table-of-contents__link toc-highlight">Known issues</a></li></ul></div></div></div></div></main></div></div></div><footer class="footer"><div class="container container-fluid"><div class="footer__bottom text--center"><div class="margin-bottom--sm"><img src="/img/favicon.png" class="footer__logo themedComponent_mlkZ themedComponent--light_NVdE"><img src="/img/favicon.png" class="footer__logo themedComponent_mlkZ themedComponent--dark_xIcU"></div><div class="footer__copyright">Copyright © 2025 Apache Software Foundation. Except where otherwise noted, licensed under CC BY-SA 4.0. Apache Druid, Druid, and the Druid logo are either registered trademarks or trademarks of The Apache Software Foundation in the United States and other countries.</div></div></div></footer></div> |
| </body> |
| </html> |
