本文介绍AnalyticDB PostgreSQL版7.0版本支持的GROUPS模式的窗口框架以及Frame exclusion特性。

背景信息

窗口函数是指在和当前行相关的一个窗口上执行计算，对应每一行输出一个在对应窗口的计算结果的函数。

GROUPS模式的窗口框架

GROUPS模式的窗口框架，支持以组（Group）为单位选取窗口，让选取窗口的方式不再受限于“RANGE”的值范围与“ROWS”的行数范围，更加灵活。例如：选取从该行所在组的上一组到该行所在组的下一组这三组作为窗口。

Frame exclusion特性

Frame exclusion特性支持选择窗口排除当前行（EXCLUDE CURRENT ROW）、排除当前行所在组（EXCLUDE GROUP）、排除当前行所在组的其他行（EXCLUDE TIES）以及没有排除规则（EXCLUDE NO OTHERS），能更好得控制当前行与当前组是否在窗口中。

语法

窗口函数的语法，请参考社区文档。

示例

准备测试表和测试数据，用于窗口函数的查询测试。
create table test(a int, b int,c int);
其中表test包含如下数据：
 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)
  • 示例1：以c排序分组后，计算上一组至下一组共三个组的b的和。
    select a,b,c,sum(b) over (order by c groups between 1 PRECEDING and 1 FOLLOWING) from test order by c;
    返回结果如下：
     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)
  • 示例2： 以c排序分组后，计算上一组数据以及当前组数据（不包含当前行）的b的和。
    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;
    返回结果如下
     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)
  • 示例3：以c排序分组后，计算上一组和下一组共两组数据的b的和。
    select a,b,c,sum(b) over (order by c groups between 1 PRECEDING and 1 FOLLOWING EXCLUDE GROUP) from test order by c;
    返回结果如下：
     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)
  • 示例4：以c排序分组后，上一组数据以及当前行数据的b的和。
    select a,b,c,sum(b) over (order by c groups between 1 PRECEDING and CURRENT ROW EXCLUDE TIES) from test order by c;
    返回结果如下：
     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)
  • 示例5：以c排序分组后，计算上一组数据的b的和。
    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;
    返回结果如下：
     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)