Window functions

window_func() over (partition by [col1,col2…]
[order by [col1[asc|desc], col2[asc|desc]…]] windowing_clause)

• PARTITION BY specifies partitioning columns.
• Rows with the same partitioning column value are considered in the same window. A window can contain a maximum of 100 million rows of data. However, we recommend that the number of rows in a window not exceed 5 million. If the number of rows exceeds the upper limit, an error is returned. This limit applies to all built-in window functions.
  Note For example, GROUP BY UID indicates that records with the same UID are considered in the same window. Therefore, the records with the same UID can contain a maximum of 100 million rows of data. However, we recommend that the number of rows in a window not exceed 5 million.
• ORDER BY specifies the rule for sorting data in a window.
  Note If some values in ORDER BY are the same, the sorting result is unstable. To reduce randomness, keep each value in ORDER BY unique.
• You can use ROWS in windowing_clause to specify the partitioning method. There are two methods:
  • rows between x preceding|following and y preceding|following: indicates a window range from the x ^th row preceding or following the current row to the y ^th
  • rows x preceding|following: indicates a window range from the x ^th row preceding or following the current row to the current row.
  Note

  • x and y must be integer constants greater than or equal to 0. Their values range from 0 to 10000. 0 indicates the current row. You must specify ORDER BY before you use ROWS to specify a window range.
  • Only the following window functions can use ROWS to specify the partitioning method: AVG, COUNT, MAX, MIN, STDDEV, and SUM.

• Syntax

  Bigint count([distinct] expr) over(partition by [col1, col2…]
  [order by [col1[asc|desc], col2[asc|desc]…]] [windowing_clause])

• Description

  Calculates the number of rows that meet expr.

• Parameters
  • expr: a value of any data type. If the value for a row is NULL, the row is not included in the calculation. If the distinct keyword is specified, this parameter indicates that only distinct values are counted.
  • partition by [col1, col2…]: specifies partitioning columns.
  • order by col1 [asc|desc], col2[asc|desc]: If ORDER BY is not specified, the number of rows that meet expr in the current window is returned. If ORDER BY is specified, the returned results are sorted in the specified order and the number of rows from the starting row to the current row in the current window is returned.
  Note ORDER BY cannot be used if the distinct keyword is specified.
• Return value

  Returns a value of the BIGINT type.

• Example
  Assume that the test_src table contains the user_id column of the BIGINT type.

  select user_id,count(user_id) over (partition by user_id) as count
  from test_src;
  +---------+------------+
  | user_id |  count     |
  +---------+------------+
  | 1       | 3          |
  | 1       | 3          |
  | 1       | 3          |
  | 2       | 1          |
  | 3       | 1          |
  +---------+------------+    
  -- If ORDER BY is not specified, the number of rows of the user_id column from the current window is returned.
  select user_id,count(user_id) over (partition by user_id order by user_id) as count
  from test_src;
  +---------+------------+
  | user_id | count      |
  +---------+------------+
  | 1       | 1          |      -- This row is the starting row of this window.
  | 1       | 2          |      -- Two records exist from the starting row to the current row. The number 2 is returned.
  | 1       | 3          |
  | 2       | 1          |
  | 3       | 1          |
  +---------+------------+
  -- If ORDER BY is specified, the number of rows from the starting row to the current row in the current window is returned.

• If MaxCompute is incompatible with Hive, the return value is the value of COUNT for each row.

  set odps.sql.hive.compatible=false;
  select user_id, price, count(price) over
    (partition by user_id order by price) as count from test_src;
  
  +------------+------------+------------+
  | user_id    | price      | count      |
  +------------+------------+------------+
  | 1          | 4.5        | 1          |  -- This row is the starting row of this window.
  | 1          | 5.5        | 2          |  -- The value of COUNT for the second row is 2.
  | 1          | 5.5        | 3          |  -- The value of COUNT for the third row is 3.
  | 1          | 6.5        | 4          |
  | 2          | NULL       | 0          |
  | 2          | 3.0        | 1          |
  | 3          | NULL       | 0          |
  | 3          | 4.0        | 1          |
  +------------+------------+------------+

• If MaxCompute is compatible with Hive, the return value is the value of COUNT for the last row of the rows with same values.

  set odps.sql.hive.compatible=true;
  select user_id, price, count(price) over
    (partition by user_id order by price) as count from test_src;
  
  +------------+------------+------------+
  | user_id    | price      | count      |
  +------------+------------+------------+
  | 1          | 4.5        | 1          | -- This row is the starting row of this window.
  | 1          | 5.5        | 3          | -- The value of COUNT for the second row is the value of COUNT for the third row because the prices of the two rows are the same.
  | 1          | 5.5        | 3          | -- The value of COUNT for the third row is 3.
  | 1          | 6.5        | 4          |
  | 2          | NULL       | 0          |
  | 2          | 3.0        | 1          |
  | 3          | NULL       | 0          |
  | 3          | 4.0        | 1          |
  +------------+------------+------------+

• Syntax

  avg([distinct] expr) over(partition by [col1, col2…]
  [order by [col1[asc|desc], col2[asc|desc]…]] [windowing_clause])

• Description

  Calculates the average value of input values.

• Parameters
  • distinct: If the distinct keyword is specified, this parameter indicates that the average value of distinct values is calculated.
  • expr: a value of the DOUBLE or DECIMAL type.
    • If the input value is of the STRING or BIGINT type, it is implicitly converted into the DOUBLE type before calculation. If it is of another data type, an error is returned.
    • If the value for a row is NULL, the row is not included in the calculation.
    • The values of the BOOLEAN type are not included in the calculation.
  • partition by [col1, col2...] specifies partitioning columns.
  • order by col1[asc|desc], col2[asc|desc]: If ORDER BY is not specified, the average value of all values in the current window is returned. If ORDER BY is specified, the returned results are sorted in the specified order and the average value from the starting row to the current row in the current window is returned.
  Note ORDER BY cannot be used if the distinct keyword is specified.
• Return value

  Returns a value of the DOUBLE type.

• If MaxCompute is incompatible with Hive, the return value is the average value for each row.

  set odps.sql.hive.compatible=false;
  select user_id, price, avg(price) over
    (partition by user_id order by price) from test_src;
  
  +------------+------------+-------------------+
  | user_id    | price      | _c2               |
  +------------+------------+-------------------+
  | 1          | 4.5        | 4.5               |    -- This row is the starting row of this window.
  | 1          | 5.5        | 5.0               |    -- The return value is the average value of the values for the first and second rows.
  | 1          | 5.5        | 5.166666666666667 |    -- The return value is the average value of the values from the first row to the third row.
  | 1          | 6.5        | 5.5               |
  | 2          | NULL       | NULL              |
  | 2          | 3.0        | 3.0               |
  | 3          | NULL       | NULL              |
  | 3          | 4.0        | 4.0               |
  +------------+------------+-------------------+

• If MaxCompute is compatible with Hive, the return value is the average value of values for the last row of the rows with same values.

  set odps.sql.hive.compatible=true;
  select user_id, price, avg(price) over
    (partition by user_id order by price) from test_src;
  
  +------------+------------+-------------------+
  | user_id    | price      | _c2               |
  +------------+------------+-------------------+
  | 1          | 4.5        | 4.5               |  -- This row is the starting row of this window.
  | 1          | 5.5        | 5.166666666666667 |  -- The return value is the average value of values from the first row to the third row.
  | 1          | 5.5        | 5.166666666666667 |  -- The return value is the average value of values from the first row to the third row.
  | 1          | 6.5        | 5.5               |
  | 2          | NULL       | NULL              |
  | 2          | 3.0        | 3.0               |
  | 3          | NULL       | NULL              |
  | 3          | 4.0        | 4.0               |
  +------------+------------+-------------------+

• Syntax

  Double stddev([distinct] expr) over(partition by [col1, col2…]
  [order by [col1[asc|desc], col2[asc|desc]…]] [windowing_clause])
  Decimal stddev([distinct] expr) over(partition by [col1, col2…] 
  [order by [col1[asc|desc], col2[asc|desc]…]] [windowing_clause])

• Description

  Calculates the population standard deviation.

• Parameters
  • expr: a value of the DOUBLE or DECIMAL type.
    • If the input value is of the STRING or BIGINT type, it is implicitly converted into the DOUBLE type before calculation. If it is of another data type, an error is returned.
    • If the value for a row is NULL, the row is not included in the calculation.
    • If the distinct keyword is specified, this parameter indicates that the population standard deviation of distinct values is calculated.
    Note

    • ORDER BY cannot be used if the distinct keyword is specified.
    • stddev has an alias function stddev_pop, which is used the same as stddev.
  • partition by [col1, col2..]: specifies partitioning columns.
  • order by col1[asc|desc], col2[asc|desc]: If ORDER BY is not specified, the population standard deviation of the current window is returned. If ORDER BY is specified, the returned results are sorted in the specified order and the population standard deviation from the starting row to the current row in the current window is returned.
• Return value

  If the input value is of the DECIMAL type, a value of the DECIMAL type is returned. Otherwise, a value of the DOUBLE type is returned.

• Example

  select window, seq, stddev_pop('1\01') over (partition by window order by seq) from dual;

• If MaxCompute is incompatible with Hive, the return value is the value of STDDEV for each row.

  set odps.sql.hive.compatible=false;
  select user_id, price, stddev(price) over
    (partition by user_id order by price) from test_src;
  
  +------------+------------+--------------------+
  | user_id    | price      | _c2                |
  +------------+------------+--------------------+
  | 1          | 4.5        | 0.0                |  -- This row is the starting row of this window.
  | 1          | 5.5        | 0.5                |  -- The return value is the value of STDDEV for the first and second rows.
  | 1          | 5.5        | 0.4714045207910316 |  -- The return value is the value of STDDEV from the first row to the third row.
  | 1          | 6.5        | 0.7071067811865475 |
  | 2          | NULL       | NULL               |
  | 2          | 3.0        | 0.0                |
  | 3          | NULL       | NULL               |
  | 3          | 4.0        | 0.0                |
  +------------+------------+--------------------+

• If MaxCompute is compatible with Hive, the return value is the value of STDDEV for the last row of the rows with same values.

  set odps.sql.hive.compatible=true;
  select user_id, price, stddev(price) over
    (partition by user_id order by price) from test_src;
  
  +------------+------------+--------------------+
  | user_id    | price      | _c2                |
  +------------+------------+--------------------+
  | 1          | 4.5        | 0.0                | -- This row is the starting row of this window.
  | 1          | 5.5        | 0.4714045207910316 | -- The return value is the value of STDDEV from the first row to the third row.
  | 1          | 5.5        | 0.4714045207910316 | -- The return value is the value of STDDEV from the first row to the third row.
  | 1          | 6.5        | 0.7071067811865475 |
  | 2          | NULL       | NULL               |
  | 2          | 3.0        | 0.0                |
  | 3          | NULL       | NULL               |
  | 3          | 4.0        | 0.0                |
  +------------+------------+--------------------+

• Syntax

  sum([distinct] expr) over(partition by [col1, col2…]
  [order by [col1[asc|desc], col2[asc|desc]…]] [windowing_clause])

• Description

  Calculates the sum of input values.

• Parameters
  • expr: a value of the DOUBLE, DECIMAL, or BIGINT type.
    • If the input value is of the STRING type, it is implicitly converted into the DOUBLE type before calculation. If it is of another data type, an error is returned.
    • If the value for a row is NULL, the row is not included in the calculation.
    • If the distinct keyword is specified, this parameter indicates that the sum of distinct values is calculated.
  • partition by [col1, col2..]: specifies partitioning columns.
  • order by col1[asc|desc], col2[asc|desc]: If ORDER BY is not specified, the sum of the expr value in the current window is returned. If ORDER BY is specified, the returned results are sorted in the specified order and the sum of values from the first row to the current row in the current window is returned.
  Note ORDER BY cannot be used if the distinct keyword is specified.
• Return value
  • If the input value is of the BIGINT type, a value of the BIGINT type is returned.
  • If the input value is of the DECIMAL type, a value of the DECIMAL type is returned.
  • If the input value is of the DOUBLE or STRING type, a value of the DOUBLE type is returned.

• If MaxCompute is incompatible with Hive, the return values are different. The return value is the sum of values for each row.

  set odps.sql.hive.compatible=false;
  select user_id, price, sum(price) over
    (partition by user_id order by price) from test_src;
  
  +------------+------------+------------+
  | user_id    | price      | _c2        |
  +------------+------------+------------+
  | 1          | 4.5        | 4.5        |  -- This row is the starting row of this window.
  | 1          | 5.5        | 10.0       |  -- The return value is the sum of values for the first and second rows.
  | 1          | 5.5        | 15.5       |  -- The return value is the sum of values from the first row to the third row.
  | 1          | 6.5        | 22.0       |
  | 2          | NULL       | NULL       |
  | 2          | 3.0        | 3.0        |
  | 3          | NULL       | NULL       |
  | 3          | 4.0        | 4.0        |
  +------------+------------+------------+

• If MaxCompute is compatible with Hive, the return values are the same. The return value is the sum of values for the last row of the rows with same values.

  set odps.sql.hive.compatible=true;
  select user_id, price, sum(price) over
    (partition by user_id order by price) from test_src;
  +------------+------------+------------+
  | user_id    | price      | _c2        |
  +------------+------------+------------+
  | 1          | 4.5        | 4.5        |  -- This row is the starting row of this window.
  | 1          | 5.5        | 15.5       |  -- The return value is the sum of values from the first row to the third row.
  | 1          | 5.5        | 15.5       |  -- The return value is the sum of values from the first row to the third row.
  | 1          | 6.5        | 22.0       |
  | 2          | NULL       | NULL       |
  | 2          | 3.0        | 3.0        |
  | 3          | NULL       | NULL       |
  | 3          | 4.0        | 4.0        |
  +------------+------------+------------+

• Syntax

  first_value(expr) over(partition by col1[, col2…]
          [order by col1 [asc|desc][, col2[asc|desc]…]] [windowing_clause])

• Description

  Calculates the first value of input values.

• Parameters
  • expr: a value of any basic data type.
  • partition by col1[, col2…]: specifies partitioning columns.
  • order by col1[asc|desc], col2[asc|desc]: If ORDER BY is not specified, the value of expr of the starting row in the current window is returned. If ORDER BY is specified, the returned results are sorted in the specified order and the value of expr of the starting row in the current window is returned.
• Return value

  Returns a value of the same data type as expr.

• Example

  select user_id, price, first_value(price) over
    (partition by user_id) as first_value from test_src;
  
  +------------+------------+-------------+
  | user_id    | price      | first_value |
  +------------+------------+-------------+
  | 1          | 5.5        | 5.5         | -- This row is the starting row of this window.
  | 1          | 4.5        | 5.5         |
  | 1          | 6.5        | 5.5         |
  | 1          | 5.5        | 5.5         |
  | 2          | NULL       | NULL        | -- This row is the starting row of this window.
  | 2          | 3.0        | NULL        |
  | 3          | 4.0        | 4.0         | -- This row is the starting row of this window.
  | 3          | NULL       | 4.0         |
  +------------+------------+-------------+
  -- If ORDER BY is not specified, rows from the first row to the last row belong to the current window. The value of the starting row in the current window is returned.
  
  
  select user_id, price, first_value(price) over
    (partition by user_id order by price) as first_value from test_src;
  
  +------------+------------+-------------+
  | user_id    | price      | first_value |
  +------------+------------+-------------+
  | 1          | 4.5        | 4.5         | -- This row is the starting row of this window.
  | 1          | 5.5        | 4.5         |
  | 1          | 5.5        | 4.5         |
  | 1          | 6.5        | 4.5         |
  | 2          | NULL       | NULL        | -- This row is the starting row of this window.
  | 2          | 3.0        | NULL        |
  | 3          | NULL       | NULL        | -- This row is the starting row of this window.
  | 3          | 4.0        | NULL        |
  +------------+------------+-------------+
  -- If ORDER BY is specified, rows from the first row to the current row belong to the current window. The value of the starting row in the current window is returned.

• Syntax

  last_value(expr) over(partition by col1[, col2…]
          [order by col1 [asc|desc][, col2[asc|desc]…]] [windowing_clause])

• Syntax

  Calculates the last value of input values.

• Parameters
  • expr: a value of any basic data type.
  • partition by col1[, col2…]: specifies partitioning columns.
  • order by col1[asc|desc], col2[asc|desc]: If ORDER BY is not specified, the value of expr of the last row in the current window is returned. If ORDER BY is specified, the returned results are sorted in the specified order and the value of expr of the current row in the current window is returned.
• Return value

  Returns a value of the same data type as expr.

• Example

  select user_id, price, last_value(price) over
    (partition by user_id) as last_value from test_src;
  
  +------------+------------+------------+
  | user_id    | price      | last_value |
  +------------+------------+------------+
  | 1          | 5.5        | 5.5        |
  | 1          | 4.5        | 5.5        |
  | 1          | 6.5        | 5.5        |
  | 1          | 5.5        | 5.5        | -- This row is the last row in this window.
  | 2          | NULL       | 3.0        |
  | 2          | 3.0        | 3.0        | -- This row is the last row in this window.
  | 3          | 4.0        | NULL       |
  | 3          | NULL       | NULL       | -- This row is the last row in this window.
  +------------+------------+------------+
  -- If ORDER BY is not specified, the rows from the first row to the last row belong to the current window, and the value of the last row in the current window is returned.
  
  
  select user_id, price, last_value(price) over
    (partition by user_id order by price) as last_value from test_src;
  
  +------------+------------+------------+
  | user_id    | price      | last_value |
  +------------+------------+------------+
  | 1          | 4.5        | 4.5        | -- This row is the current row in the current window.
  | 1          | 5.5        | 5.5        | -- This row is the current row in the current window.
  | 1          | 5.5        | 5.5         | -- This row is the current row in the current window.
  | 1          | 6.5        | 6.5        | -- This row is the current row in the current window.
  | 2          | NULL       | NULL       | -- This row is the current row in the current window.
  | 2          | 3.0        | 3.0        | -- This row is the current row in the current window.
  | 3          | NULL       | NULL       | -- This row is the current row in the current window.
  | 3          | 4.0        | 4.0        | -- This row is the current row in the current window.
  +------------+------------+------------+
  -- If ORDER BY is specified, rows from the first row to the current row belong to the current window. The value of the current row in the current window is returned.

• Syntax

  nth_value(expr, number [, skipNull]) over(partition by col1[, col2…]
      [order by col1 [asc|desc][, col2[asc|desc]…]] [windowing_clause])

• Description

  Calculates the n ^th value of input values. If the value of n exceeds the total number of rows in the window, NULL is returned.

• Parameters
  • expr: a value of any basic data type.
  • number: an integer greater than or equal to 1.
  • skipNull: indicates whether to skip NULL when the n ^th value is calculated. The default value is false.
  • partition by col1[, col2…]: specifies partitioning columns.
  • order by col1[asc|desc], col2[asc|desc]: If ORDER BY is not specified, the value of expr of the n ^th row in the current window is returned. If ORDER BY is specified, the returned results are sorted in the specified order and the value of expr of the nth row from the starting row to the current row in the current window is returned.
• Return value

  Returns a value of the same data type as expr.

• Example

  select user_id, price, nth_value(price, 2) over
    (partition by user_id) as nth_value from test_src;
  
  +------------+------------+------------+
  | user_id    | price      | nth_value  |
  +------------+------------+------------+
  | 1          | 5.5        | 4.5        |
  | 1          | 4.5        | 4.5        | -- This row is the second row in the current window.
  | 1          | 6.5        | 4.5        |
  | 1          | 5.5        | 4.5        |
  | 2          | NULL       | 3.0        |
  | 2          | 3.0        | 3.0        | -- This row is the second row in the current window.
  | 3          | 4.0        | NULL       |
  | 3          | NULL       | NULL       | -- This row is the second row in the current window.
  +------------+------------+------------+
  -- If ORDER BY is not specified, rows from the first row to the last row belong to the current window. The value of the second row is returned.
  
  select user_id, price, nth_value(price, 3) over
    (partition by user_id) as nth_value from test_src;
  +------------+------------+------------+
  | user_id    | price      | nth_value  |
  +------------+------------+------------+
  | 1          | 5.5        | 6.5        |
  | 1          | 4.5        | 6.5        |
  | 1          | 6.5        | 6.5        | -- This row is the third row in the current window.
  | 1          | 5.5        | 6.5        |
  | 2          | NULL       | NULL       |
  | 2          | 3.0        | NULL       |
  | 3          | 4.0        | NULL       |
  | 3          | NULL       | NULL       |
  +------------+------------+------------+
  -- If ORDER BY is not specified, rows from the first row to the last row belong to the current window. The value of the third row is returned.
  -- The second and third windows have only two rows.
  
  
  select user_id, price, nth_value(price, 2) over
    (partition by user_id order by price) as nth_value from test_src;
  +------------+------------+------------+
  | user_id    | price      | nth_value  |
  +------------+------------+------------+
  | 1          | 4.5        | NULL       | -- The current window has only one row. The second row exceeds the window length.
  | 1          | 5.5        | 5.5        |
  | 1          | 5.5        | 5.5        |
  | 1          | 6.5        | 5.5        |
  | 2          | NULL       | NULL       |
  | 2          | 3.0        | 3.0        |
  | 3          | NULL       | NULL       |
  | 3          | 4.0        | 4.0        |
  +------------+------------+------------+
  -- If ORDER BY is specified, rows from the first row to the current row belong to the current window. The value of the second row is returned.
  
  
  select user_id, price, nth_value(price, 1, true) over
    (partition by user_id) as nth_value from test_src;
  +------------+------------+------------+
  | user_id    | price      | nth_value  |
  +------------+------------+------------+
  | 1          | 5.5        | 5.5        |
  | 1          | 4.5        | 5.5        |
  | 1          | 6.5        | 5.5        |
  | 1          | 5.5        | 5.5        |
  | 2          | NULL       | 3.0        | -- The value of the first row is NULL, and therefore this row is skipped.
  | 2          | 3.0        | 3.0        |
  | 3          | 4.0        | 4.0        |
  | 3          | NULL       | 4.0        |
  +------------+------------+------------+
  -- If ORDER BY is not specified, rows from the first row to the last row belong to the current window.
  -- The value of the first row is returned. skipNull is set to true.