ByteHouse supports the standard syntax for window functions. The following explains the list of functions related to windows.

| | | \

• The definition of peer group differs between ANSI/MySQL dialect and ClickHouse. For details, refer to the ANSI section.

row_number() #

Returns the number of the row in the current partition. Rows are numbered starting from 1, according to the order specified in the ORDER BY clause of the window definition. If there is no ORDER BY clause, rows are ordered arbitrarily.

rank() #

Returns the number of the first peer row in each group. The ranking of the current row will have gaps. If there is no ORDER BY clause, all rows are considered peer rows, and this function always returns 1.

dense_rank() #

Returns the number of the peer group of the current row within its partition. The ranking does not have gaps. Rows are numbered starting from 1, according to the order specified in the ORDER BY clause of the window definition. If there is no ORDER BY clause, all rows are considered peer rows, and this function always returns 1.

percent_rank() #

Despite its name, this function always returns a value between 0.0 and 1.0. The calculation formula is (rank - 1)/(partition-rows - 1), where rank is the value returned by the built-in window function rank(), and partition-rows is the total number of rows in the partition. If there is only one row in the partition, this function returns 0.0.

cume_dist() #

Cumulative distribution. The calculation formula is row-number/partition-rows, where row-number is the value returned by the row_number() function for the last peer row in the group, and partition-rows is the number of rows in the partition.

ntile(N) #

The parameter N is treated as an integer. This function divides the partition into N groups as evenly as possible, and assigns an integer between 1 and N to each group in the order defined by the ORDER BY clause. Otherwise, assignment is arbitrary. If necessary, larger groups appear first. This function returns the integer value of the group to which the current row belongs.

lag(expr) / lag(expr, offset) / lag(expr, offset, default) #

The first form of the lag() function returns the result of evaluating the expression expr for the previous row in the partition. If there is no previous row (because the current row is the first row), it returns NULL.
If the offset parameter is provided, it must be a non-negative integer. In this case, the returned value is the result of evaluating the expression expr for the row offset rows before the current row in the partition. If offset is 0, expr is calculated for the current row. If there are not offset rows before the current row, Back NULL.
If the default parameter is also provided, then if the row specified by offset does not exist, Back Default instead of NULL.
Compared to lagInFrame, the Frame definition provided by the user under the lag function is ignored, using the Default rows between unbounded preceding and unbounded following.

lead(expr) / lead(expr, offset) / lead(expr, offset, default) #

The first form of the lead() function Backs the result of calculating the expression expr for the next row in the Partition. Or, if there is no next row (because the current row is the last row), Back NULL.
If the offset parameter is provided, it must be a non-negative integer. In this case, the returned value is the result of calculating the expression expr for the offset row after the current row in the Partition. If offset is 0, expr is calculated for the current row. If there are not offset rows after the current row, Back NULL.
If the default parameter is also provided, then if the row specified by offset does not exist, Back Default instead of NULL.
Compared to leadInFrame, the Frame definition provided by the user under the lead function is ignored, using the Default rows between unbounded preceding and unbounded following.

first_value(expr) #

This built-in window function calculates the window frame for each row in the same way as the aggregate window function. It Backs the result of calculating the expression expr for the first row in the window frame.

last_value(expr) #

This built-in window function calculates the window frame for each row in the same way as the aggregate window function. It Backs the result of calculating the expression expr for the last row in the window frame.

nth_value(expr, N) #

This built-in window function calculates the window frame for each row in the same way as the aggregate window function. It Backs the result of calculating the expression expr for the N-th row in the window frame. Rows in the window frame are numbered starting from 1, according to the order defined by the ORDER BY clause (if any). If there is no N-th row in the Partition, Back NULL.

lagInFrame(expr) / lagInFrame(expr, offset) / lagInFrame(expr, offset, default) #

The first form of the lagInFrame function Backs the result of calculating the expression expr for the previous row in the window frame. Or, if there is no previous row (because the current row is the first row), Back NULL.
If the offset parameter is provided, it must be a non-negative integer. In this case, the returned value is the result of calculating the expression expr for the offset row before the current row in the window frame. If offset is 0, Then Calculate expr for the current row. If there are not offset rows Before the current row, Back NULL.
If the default Param is also provided, Then if the row specified by offset does not exist, Back default instead of NULL.

leadInFrame(expr) / leadInFrame(expr, offset) / leadInFrame(expr, offset, default) #

The first Form of the leadInFrame function Backs the As a result of calculating the Expression expr for the next row in the window frame. or, if there is no next row (because the current row is the last row), Back NULL.
If the offset Param is provided, it must be a non-negative Integer. In this case, the Value Back is the As a result of calculating the Expression expr for the row offset rows After the current row in the window frame. If offset is 0, Then Calculate expr for the current row. If there are not offset rows After the current row, Back NULL.
If the default Param is also provided, Then if the row specified by offset does not exist, Back default instead of NULL.

nonNegativeDerivative(value) #

The nonNegativeDerivative function calculates the difference between adjacent Value in the Input sequence value, but does not Back negative Value. Specifically, if the difference between two adjacent Value is positive, Back that difference; if it is negative or Zero, Back Zero.
This function is often used for processing time series data, especially when the Data represents cumulative counters, as it can effectively handle counter Reset situations.