The traditional GROUP BY function organizes data into groups and aggregates query results based on groups. In this case, GROUP BY returns only one row for each data group. However, window functions, also called online analytical processing (OLAP) functions, can return multiple rows for each data group without aggregating query results. This is different from the traditional GROUP BY function. This topic describes how to use window functions.

Prerequisites

The PolarDB-X 1.0 instance version is 5.4.8 or later.

Limits

  • Window functions can be used only in SELECT statements.
  • Window functions cannot be used in conjunction with the separate aggregate functions.

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

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

    To implement the preceding query, use 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:
  • Window functions that consist of aggregate functions and the OVER keyword:
    • SUM()
    • COUNT()
    • AVG()
    • MAX()
    • MIN()
  • Dedicated window functions:
    • ROW_NUMBER()
    • RANK()
    • DESNCE_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 dedicated window functions, see Window function descriptions.
  • Only instances whose version is 5.4.9 or later ( If your instance version is earlier than 5.4.9, upgrade the version. For more information, see Upgrade the version) support the following dedicated window functions.
    • PERCENT_RANK()
    • CUME_DIST()
    • FIRST_VALUE()
    • LAST_VALUE()
    • LAG()
    • LEAD()
    • NTH_VALUE()
[partition by column_some1] The partition rule for the window function. This clause divides input rows into different partitions. The process is similar to the division process of the GROUP BY clause.
Note You cannot reference complex expressions in the PARTITION BY clause. For example, you can reference column_some1, but cannot reference column_some1 + 1.
[order by column_some2] The sorting rule for the window function. 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, but 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 the value range for the computed column. ROWS indicates that the frame is defined by the number of rows for the computed column.

You can use the BETWEEN start AND end option to specify the boundary rows in the window.

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

Use cases

Assume that the following raw data has been created.

| 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 of each country:
    select
        country,
        sum(profit) over (partition by country) sum_profit
    from test_window;
    The following result is returned:
    | country | sum_profit |
    |---------|------------|
    | India   |        229 |
    | India   |        229 |
    | India   |        229 |
    | USA     |       1550 |
    | USA     |       1550 |
    | Finland |       1510 |
    | Finland |       1510 |
  • Use the following dedicated 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 result is 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 option to calculate a cumulative sum of profits for each row 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 result is 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 |