The window functions have three types: aggregate functions, ranking functions and analytic functions. Aggregate functions: COUNT, AVERAGE, SUM, MAX, MIN, etc. Ranking functions: WRANK, ROWNUMBER, DENSERANK, WPERCENTRANK, CUMEDIST. Analytic functions: FIRSTVALUE, LASTVALUE, NTHVALUE, LEAD, LAG, NTILE. The aggregate functions don’t require ORDERBY, but they accept the window frame definition. ROWNUMBER Syntax Usage notes Returns the number of the current row within its partition. Row numbers range from 1 to the number of partition rows. It neither requires the ORDERBY nor accepts window frame definition. W_RANK Syntax Usage notes Returns the rank of each row within the partition of a result set. Peers are considered ties and receive the same rank. Requires ORDERBY, does not accept window frame definition. DENSERANK Syntax Usage notes Returns the rank of the current row within its partition, without gaps. Peers are considered ties and receive the same rank. Requires ORDERBY, does not accept window frame definition. W_PERCENTRANK Syntax Usage notes Calculates the percentage of the relative rank of a row within a partition of rows. Using the rank of the row and the number of the total rows to compute the percentage: rank-1 / rows -1. And the range is [0,1]. Requires ORDERBY, does not accept window frame definition. CUMEDIST Syntax Usage notes Cumulative distribution value. The number of rows with values be less than or equal to the current row’s value is divided by the total number of rows within the partition. And the range is (0,1]. Requires ORDERBY, does not accept window frame definition. LEAD Syntax Argument Description value_function (Required) The field name or formula. [offset_value] (Optional) Row offset after the current, offset = 1. [default_value] (Optional) default value, default = null Usage notes Provides access to the value from a row at a given physical offset that follows the current row. If these is no such row, the return value is the default value. Does not accept window frame definition. LAG Syntax Argument Description value_function (Required) The field name or formula. [offset_value] (Optional) Row offset before the current, offset = 1. [default_value] (Optional) default value, default = null Usage notes Provides access to the value from a row at a given physical offset that before the current row. If these is no such row, the return value is the default value. Does not accept window frame definition. NTILE Syntax Argument Description n (Required) The count of buckets. Usage notes Divides a partition into N buckets, assigns each row in the partition its bucket number. Does not accept window frame definition. FIRSTVALUE Syntax Argument Description value_function (Required) The field name or formula. Usage notes The value for the first row within the window frame. It does not require ORDERBY. LASTVALUE Syntax Argument Description value_function (Required) The field name or formula. Usage notes The value for the last row within the window frame. It does not require ORDERBY. NTHVALUE Syntax Argument Description value_function (Required) The field name or formula. n (Required) The n-th row within the window frame. Usage notes The value for the n-th row within the window frame. If there is no such row, the return value is null. It does not require ORDERBY.