| Windows functions are not currently supported by multi-stage-query engine so you cannot use them in SQL-based ingestion. </p><p>Set the context parameter <code>enableWindowing: true</code> to use window functions.</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>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>delta</code> value.</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><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"><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/28.0.1/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="define-a-window-with-the-over-clause">Define a window with the OVER clause<a href="#define-a-window-with-the-over-clause" class="hash-link" aria-label="Direct link to Define a window with the OVER clause" title="Direct link to Define a window with the OVER clause"></a></h2><p>The OVER clause defines the query windows for window functions as follows:</p><ul><li>PARTITION BY indicates the dimension that defines the rows within the window</li><li>ORDER BY specifies the order of the rows within the windows.</li></ul><div class="theme-admonition theme-admonition-note alert alert--secondary admonition_LlT9"><div class="admonitionHeading_tbUL"><span class="admonitionIcon_kALy"><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_S0QG"><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><p>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"><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><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</td></tr><tr><td><code>RANK()</code></td><td>Returns the rank for a row within a window</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 rank of the row calculated as a percentage according to the formula: <code>(rank - 1) / (total window rows - 1)</code></td></tr><tr><td><code>CUME_DIST()</code></td><td>Returns the cumulative distribution of the current row within the window calculated as <code>number of window rows at the same rank or higher than current row</code> / <code>total window rows</code></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>Returns the value evaluated at the row that precedes the current row by the offset number within the window. <code>offset</code> defaults to 1 if not provided</td></tr><tr><td><code>LEAD(expr[, offset])</code></td><td>Returns the value evaluated at the row that follows the current row by the offset number within the window; if there is no such row, returns the given default value. <code>offset</code> defaults to 1 if not provided</td></tr><tr><td><code>FIRST_VALUE(expr)</code></td><td>Returns the value for the expression for the first row within the window</td></tr><tr><td><code>LAST_VALUE(expr)</code></td><td>Returns the value 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"><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"><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><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>Aggregates with ORDER BY specified are processed in the window: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW<br>This behavior differs from other databases that use the default of RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.<br>In cases where the order column is unique there is no difference between RANGE / ROWS; windows with RANGE specifications are handled as ROWS.</li><li>LEAD/LAG ignores the default value</li><li>LAST_VALUE returns the last value of the window even when you include an ORDER BY clause</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/28.0.1/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/28.0.1/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="#define-a-window-with-the-over-clause" class="table-of-contents__link toc-highlight">Define a window with the OVER clause</a></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></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><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="themedImage_ToTc themedImage--light_HNdA footer__logo"><img src="/img/favicon.png" class="themedImage_ToTc themedImage--dark_i4oU footer__logo"></div><div class="footer__copyright">Copyright © 2023 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> |
