本文介绍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)