The GROUP BY function groups the queried data and aggregates the query results for each group. This function returns only one row for each group. Window functions are different from the GROUP BY function. A window function can return multiple rows for each group. The number of rows in the output is the same as that in the input of the function. Window functions are known as online analytical processing (OLAP) functions. This topic describes how to use window functions.

Limits

  • Window functions can be used only in the SELECT statements.
  • Window functions can be used in conjunction with an aggregate function only if the aggregate function is included in a clause.

    In the following sample statement, the SUM function is an aggregate function that does not include the OVER keyword. In this case, the statement cannot be executed. 

    SELECT SUM(NAME),COUNT() OVER(...) FROM SOME_TABLE

    If you need to retrieve the preceding query results, execute the following statement:

    SELECT SUM(NAME),WIN1 FROM (SELECT NAME,COUNT() OVER(...) AS WIN1 FROM SOME_TABLE) alias

Syntax

function OVER ([[partition by column_some1] [order by column_some2] [RANGE|ROWS BETWEEN start AND end]])
Parameter Description
function The window function that you can specify. The following functions are supported:
  • The following aggregate functions that include the OVER keyword can be used as window functions:
    • SUM()
    • COUNT()
    • AVG()
    • MAX()
    • MIN()
  • The following functions can be used only as window functions:
    • ROW_NUMBER()
    • RANK()
    • DENSE_RANK()
    • PERCENT_RANK()
    • CUME_DIST()
    • FIRST_VALUE()
    • LAST_VALUE()
    • LAG()
    • LEAD()
    • NTH_VALUE()
Note
  • When you use the RANK() or DENSE_RANK() window function, the ORDER BY clause cannot be omitted. For more information about the functions that can be used only as window functions, see Window function descriptions.
  • The following functions can be used only as window functions and are supported by Distributed Relational Database Service (DRDS):
    • PERCENT_RANK()
    • CUME_DIST()
    • FIRST_VALUE()
    • LAST_VALUE()
    • LAG()
    • LEAD()
    • NTH_VALUE()
[partition by column_some1] Specifies the column by which the query result set is partitioned. This clause is used to divide input rows into different partitions to which the window function is applied. This clause works in a similar manner as the GROUP BY clause.
Note You cannot reference complex expressions in the PARTITION BY clause. For example, you can reference column_some1. You cannot reference column_some1 + 1.
[order by column_some2] Specifies the order in which the input rows of the window function are calculated. This clause defines the order in which the input rows are calculated in the window function.
Note You cannot reference complex expressions in the ORDER BY clause. For example, you can reference column_some2. You cannot reference column_some2 + 1.
[RANGE|ROWS BETWEEN start AND end] The window frame of the window function. You can use RANGE or ROWS to define the frame. RANGE indicates that the frame is defined by rows within a value range for the computed column. ROWS indicates that the frame is defined by the first and last row positions for the computed column.

You can use the BETWEEN start AND end clause to specify the first and last rows of the window frame.

  • Valid values of start:
    • CURRENT ROW: The frame starts at the current row.
    • N PRECEDING: The frame starts at the preceding Nth row.
    • UNBOUNDED PRECEDING: The frame starts at the first row.
  • Valid values of end:
    • CURRENT ROW: The frame ends at the current row.
    • N FOLLOWING: The frame ends at the following Nth row.
    • UNBOUNDED FOLLOWING: The frame ends at the last row.

Examples

For example, the following raw data is available.

| year | country | product    | profit |
|------|---------|------------|--------|
| 2001 | Finland | Phone      |     10 |
| 2000 | Finland | Computer   |   1500 |
| 2001 | USA     | Calculator |     50 |
| 2001 | USA     | Computer   |   1500 |
| 2000 | India   | Calculator |     75 |
| 2000 | India   | Calculator |     75 |
| 2001 | India   | Calculator |     79 |
  • Use the following aggregate function to calculate the total profit for each country:
    select
    country,
    sum(profit) over (partition by country) sum_profit
from test_window;
    The following results are returned:
    | country | sum_profit |
|---------|------------|
| India   |        229 |
| India   |        229 |
| India   |        229 |
| USA     |       1550 |
| USA     |       1550 |
| Finland |       1510 |
| Finland |       1510 |
  • Use the following function that can be used only as a window function to group data by country and rank the products of each country by profit in ascending order:
    select
    'year',
    country,
    product,
    profit,
    rank() over (partition by country order by profit) as rank
from test_window;
    The following results are returned:
    | year | country | product    | profit | rank |
|------|---------|------------|--------|------|
| 2001 | Finland | Phone      |     10 |    1 |
| 2000 | Finland | Computer   |   1500 |    2 |
| 2001 | USA     | Calculator |     50 |    1 |
| 2001 | USA     | Computer   |   1500 |    2 |
| 2000 | India   | Calculator |     75 |    1 |
| 2000 | India   | Calculator |     75 |    1 |
| 2001 | India   | Calculator |     79 |    3 |
  • Execute the following statement that contains the ROWS clause to calculate the sum of the values in the profit column in the current window:
    select 
    'year',
    country,
    profit,
    sum(profit) over (partition by country order by 'year' ROWS BETWEEN UNBOUNDED PRECEDING and CURRENT ROW) as sum_win 
from test_window;
    The following results are returned:
    +------+---------+--------+-------------+
| year | country | profit |   sum_win   |
+------+---------+--------+-------------+
| 2001 | USA     |     50 |          50 |
| 2001 | USA     |   1500 |        1550 |
| 2000 | India   |     75 |          75 |
| 2000 | India   |     75 |         150 |
| 2001 | India   |     79 |         229 |
| 2000 | Finland |   1500 |        1500 |
| 2001 | Finland |     10 |        1510 |