> For the complete documentation index, see [llms.txt](https://docs.e6data.com/ingestion-engine/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.e6data.com/ingestion-engine/sql/aggregate-and-window-functions/window.md). # Window *The e6 Ingestion Engine window functions are based on* [*Apache DataFusion*](https://arrow.apache.org/datafusion/) *and these docs are derived from the DataFusion function reference.* ## Quick reference | Function | Description | | ----------------------------- | -------------------------------------- | | `row_number()` | Sequential row number within partition | | `rank()` | Rank with gaps for ties | | `dense_rank()` | Rank without gaps | | `ntile(n)` | Distribute rows into n buckets | | `cume_dist()` | Cumulative distribution | | `percent_rank()` | Relative rank as percentage | | `lag(expr, offset, default)` | Value from previous row | | `lead(expr, offset, default)` | Value from following row | | `first_value(expr)` | First value in window frame | | `last_value(expr)` | Last value in window frame | | `nth_value(expr, n)` | Nth value in window frame | ## Worked example Given `bids` data (all within same 10-second hop window): | auction\_id | price | | ----------- | ----- | | 1 | 10.00 | | 1 | 15.00 | | 1 | 12.00 | | 2 | 25.00 | | 2 | 28.00 | ```sql SELECT *, ROW_NUMBER() OVER (PARTITION BY window, auction_id ORDER BY price DESC) as rank, lag(price, 1) OVER (PARTITION BY window, auction_id ORDER BY price DESC) as prev_price FROM ( SELECT auction_id, price, HOP(interval '1' second, interval '10' second) as window FROM bids ) ``` Output: | auction\_id | price | rank | prev\_price | | ----------- | ----- | ---- | ----------- | | 1 | 15.00 | 1 | NULL | | 1 | 12.00 | 2 | 15.00 | | 1 | 10.00 | 3 | 12.00 | | 2 | 28.00 | 1 | NULL | | 2 | 25.00 | 2 | 28.00 | A *window function* performs a calculation across a set of table rows that are somehow related to the current row. This is comparable to the type of calculation that can be done with an aggregate function. However, window functions do not cause rows to become grouped into a single output row like non-window aggregate calls would. Instead, the rows retain their separate identities. Behind the scenes, the window function is able to access more than just the current row of the query result. For example, to calculate the most common auction in a dataset over the last 10 minutes every second, you could use the following query: ```sql SELECT * FROM ( SELECT ROW_NUMBER() OVER ( PARTITION BY window ORDER BY count DESC) as row_number, auction, count FROM ( SELECT auction, hop(INTERVAL '1' second, INTERVAL '10' minute) as window, count(*) as count FROM bids GROUP BY 1, 2)) where row_number = 1; ``` Note that this is a distinct feature from e6 Ingestion Engine's [streaming windows](/ingestion-engine/sql/streaming/windows.md), like `hop` and `slide`. Streaming windows allow aggregating over records with a time bound, while SQL window functions allow you to reference other rows without aggregating. A window function call always contains an OVER clause directly following the window function's name and argument(s). This is what syntactically distinguishes it from a normal function or non-window aggregate. The OVER clause determines exactly how the rows of the query are split up for processing by the window function. The PARTITION BY clause within OVER divides the rows into groups, or partitions, that share the same values of the PARTITION BY expression(s). For each row, the window function is computed across the rows that fall into the same partition as the current row. The previous example showed how to count the average of a column per partition. You can also control the order in which rows are processed by window functions using ORDER BY within OVER. (The window ORDER BY does not even have to match the order in which the rows are output.) Note that currently window functions must contain a streaming window in the PARTITION BY. ## Syntax The syntax for the OVER-clause is ``` function([expr]) OVER( [PARTITION BY expr[, ...]] [ORDER BY expr [ ASC | DESC ][, ...]] ) ``` ## Aggregate functions All [aggregate functions](/ingestion-engine/sql/aggregate-and-window-functions/aggregates.md) can be used as window functions. ## Ranking functions ### `row_number` Number of the current row within its partition, counting from 1. ```sql row_number() ``` ### `rank` Rank of the current row with gaps; same as row\_number of its first peer. ```sql rank() ``` ### `dense_rank` Rank of the current row without gaps; this function counts peer groups. ```sql dense_rank() ``` ### `ntile` Integer ranging from 1 to the argument value, dividing the partition as equally as possible. ```sql ntile(expression) ``` **Arguments** * **expression**: An integer describing the number groups the partition should be split into ## Analytical functions ### `cume_dist` Relative rank of the current row: (number of rows preceding or peer with current row) / (total rows). ```sql cume_dist() ``` ### `percent_rank` Relative rank of the current row: (rank - 1) / (total rows - 1). ```sql percent_rank() ``` ### `lag` Returns value evaluated at the row that is offset rows before the current row within the partition; if there is no such row, instead return default (which must be of the same type as value). Both offset and default are evaluated with respect to the current row. If omitted, offset defaults to 1 and default to null. ```sql lag(expression, offset, default) ``` **Arguments** * **expression**: Expression to operate on * **offset**: Integer. Specifies how many rows back the value of *expression* should be retrieved. Defaults to 1. * **default**: The default value if the offset is not within the partition. Must be of the same type as *expression*. ### `lead` Returns value evaluated at the row that is offset rows after the current row within the partition; if there is no such row, instead return default (which must be of the same type as value). Both offset and default are evaluated with respect to the current row. If omitted, offset defaults to 1 and default to null. ```sql lead(expression, offset, default) ``` **Arguments** * **expression**: Expression to operate on * **offset**: Integer. Specifies how many rows forward the value of *expression* should be retrieved. Defaults to 1. * **default**: The default value if the offset is not within the partition. Must be of the same type as *expression*. ### `first_value` Returns value evaluated at the row that is the first row of the window frame. ```sql first_value(expression) ``` **Arguments** * **expression**: Expression to operate on ### `last_value` Returns value evaluated at the row that is the last row of the window frame. ```sql last_value(expression) ``` **Arguments** * **expression**: Expression to operate on ### `nth_value` Returns value evaluated at the row that is the nth row of the window frame (counting from 1); null if no such row. ```sql nth_value(expression, n) ``` **Arguments** * **expression**: The name the column of which nth value to retrieve * **n**: Integer. Specifies the *n* in nth --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter: ``` GET https://docs.e6data.com/ingestion-engine/sql/aggregate-and-window-functions/window.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.