PostgreSQL 11 新特性之窗口函数（window function）增强

文章目录

PostgreSQL 11 针对窗口函数（window function）进行了增强，添加了 SQL:2011 标准中的所有窗口范围（window frame）子句选项。具体来说，新增了以下功能：

    允许在RANGE窗口模式中使用off_set PRECEDING/FOLLOWING限定窗口范围
    支持GROUPS窗口模式，使用基于组的方式限定窗口范围
    支持窗口范围子句的排除选项，用于排除窗口内的某些数据行

首先，对于RANGE窗口模式，增加了off_set PRECEDING/FOLLOWING子句，现在可以支持所有的选项：

RANGE frame_start
RANGE BETWEEN frame_start AND frame_end

   

其中，frame_start 和 frame_end 可以是以下选项之一：

UNBOUNDED PRECEDING
off_set PRECEDING
CURRENT ROW
off_set FOLLOWING
UNBOUNDED FOLLOWING

    
创建一个测试表：

CREATE TABLE test
(
  id      SERIAL PRIMARY KEY,
  val     INT4,
  logtime TIMESTAMP
);

INSERT INTO test(val, logtime)
VALUES
(1, '2019-01-15 08:08:17'),
(1, '2019-01-15 08:14:30'),
(3, '2019-01-15 08:36:00'),
(6, '2019-01-15 09:20:56'),
(6, '2019-01-15 10:15:41');

以下示例比较了ROWS模式和RANGE模式的区别：

SELECT id, val, logtime,
       sum(val) OVER (ORDER BY val ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as sum_rows,
       sum(val) OVER (ORDER BY val RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) as sum_range,
       sum(val) OVER (ORDER BY logtime RANGE BETWEEN INTERVAL '10 minute' PRECEDING AND '10 minute' FOLLOWING) as sum_range_time
  FROM test;
 id | val |       logtime       | sum_rows | sum_range | sum_range_time
----+-----+---------------------+----------+-----------+----------------
  1 |   1 | 2019-01-15 08:08:17 |        2 |         2 |              2
  2 |   1 | 2019-01-15 08:14:30 |        5 |         2 |              2
  3 |   3 | 2019-01-15 08:36:00 |       10 |         3 |              3
  4 |   6 | 2019-01-15 09:20:56 |       15 |        12 |              6
  5 |   6 | 2019-01-15 10:15:41 |       12 |        12 |              6
(5 rows)

   
其中，

    sum_rows 列计算按照字段 val 排序后，每一行以及前后各一行的合计值；
    sum_range 列计算按照字段 val 排序后，每一行以及前后和它的值相差小于等于 1 的那些行的合计值；
    sum_range_time 列计算按照字段 logtime 排序后，每一行以及前后和它的值相差小于等于 10 分钟的那些行的合计值。
 

更多请见：http://www.mark-to-win.com/tutorial/51628.html