This topic describes the GROUPS window frame option and the frame exclusion clauses supported by AnalyticDB for PostgreSQL V7.0.

Background information

A window function produces a result value for each input row. The value is computed based on the records in the current window.

GROUPS window frame option

The GROUPS window frame option allows you to select windows by group. This option is more flexible than RANGE and ROWS. For example, you can select three groups as a window: the group that contains the current row, the previous group, and the next group.

Frame exclusion clauses

The following clauses can be used to exclude the current row or group from the window frame: EXCLUDE CURRENT ROW, EXCLUDE GROUP, EXCLUDE TIES, and EXCLUDE NO OTHERS. EXCLUDE CURRENT ROW excludes the current row. EXCLUDE GROUP excludes all rows in the current group. EXCLUDE TIES excludes the rows in the current group other than the current row. EXCLUDE NO OTHERS does not exclude the current row or its peers.

Syntax

For information about the syntax of window functions, see Window Function Calls.

Examples

Create a test table that contains data to perform query tests on window functions. 
create table test(a int, b int,c int);
The test table contains the following data:
 a  | b | c | 
----+---+---+
  1 | 0 | 0 |
  2 | 0 | 1 |
  3 | 0 | 1 |
  4 | 0 | 2 |
  5 | 1 | 2 |
  6 | 1 | 3 |
  7 | 1 | 3 |
  8 | 1 | 4 |
  9 | 1 | 4 |
 10 | 2 | 5 |
----+---+---+
(10 rows)
  • Example 1: Order the grouped data by column c and compute the sum of column b in the previous group to the next group. 
    select a,b,c,sum(b) over (order by c groups between 1 PRECEDING and 1 FOLLOWING) from test order by c;
    The following information is returned:
     a  | b | c | sum
----+---+---+-----
  1 | 0 | 0 |   0
  2 | 0 | 1 |   1
  3 | 0 | 1 |   1
  4 | 0 | 2 |   3
  5 | 1 | 2 |   3
  6 | 1 | 3 |   5
  7 | 1 | 3 |   5
  8 | 1 | 4 |   6
  9 | 1 | 4 |   6
 10 | 2 | 5 |   4
----+---+---+----
(10 rows)
  • Example 2: Order the grouped data by column c and compute the sum of column b in the previous group and the current group (excluding the current row). 
    select a,b,c,sum(b) over (order by c groups between 1 PRECEDING and CURRENT ROW EXCLUDE CURRENT ROW) from test order by c;
    The following information is returned:
     a  | b | c | sum
----+---+---+-----
  1 | 0 | 0 |
  2 | 0 | 1 |   0
  3 | 0 | 1 |   0
  4 | 0 | 2 |   1
  5 | 1 | 2 |   0
  6 | 1 | 3 |   2
  7 | 1 | 3 |   2
  8 | 1 | 4 |   3
  9 | 1 | 4 |   3
 10 | 2 | 5 |   2
----+---+---+-----
(10 rows)
  • Example 3: Order the grouped data by column c and compute the sum of column b in the previous group and the next group. 
    select a,b,c,sum(b) over (order by c groups between 1 PRECEDING and 1 FOLLOWING EXCLUDE GROUP) from test order by c;
    The following information is returned:
     a  | b | c | sum
----+---+---+-----
  1 | 0 | 0 |   0
  2 | 0 | 1 |   1
  3 | 0 | 1 |   1
  4 | 0 | 2 |   2
  5 | 1 | 2 |   2
  6 | 1 | 3 |   3
  7 | 1 | 3 |   3
  8 | 1 | 4 |   4
  9 | 1 | 4 |   4
 10 | 2 | 5 |   2
----+---+---+-----
(10 rows)
  • Example 4: Order the grouped data by column c and compute the sum of column b in the previous group and the current row. 
    select a,b,c,sum(b) over (order by c groups between 1 PRECEDING and CURRENT ROW EXCLUDE TIES) from test order by c;
    The following information is returned:
     a  | b | c | sum
----+---+---+-----
  1 | 0 | 0 |   0
  2 | 0 | 1 |   0
  3 | 0 | 1 |   0
  4 | 0 | 2 |   0
  5 | 1 | 2 |   1
  6 | 1 | 3 |   2
  7 | 1 | 3 |   2
  8 | 1 | 4 |   3
  9 | 1 | 4 |   3
 10 | 2 | 5 |   4
----+---+---+-----
(10 rows)
  • Example 5: Order the grouped data by column c and compute the sum of column b in the previous group. 
    select a,b,c,sum(b) over (order by c groups between 1 PRECEDING and 1 PRECEDING EXCLUDE NO OTHERS) from test order by c;
    The following information is returned:
     a  | b | c | sum
----+---+---+-----
  1 | 0 | 0 |
  2 | 0 | 1 |   0
  3 | 0 | 1 |   0
  4 | 0 | 2 |   0
  5 | 1 | 2 |   0
  6 | 1 | 3 |   1
  7 | 1 | 3 |   1
  8 | 1 | 4 |   2
  9 | 1 | 4 |   2
 10 | 2 | 5 |   2
----+---+---+-----
(10 rows)