PostgeSQL 实现相邻分组

问题

比如我们有以下数据：

CREATE TABLE sensor (id INTEGER PRIMARY KEY, status TEXT, num DOUBLE PRECISION);
INSERT INTO sensor VALUES (1,'a',3.1), (2,'a',2.6), (3,'a',3.2);
INSERT INTO sensor VALUES (4,'b',7.4), (5,'b',6.8), (6,'c',9.2);
INSERT INTO sensor VALUES (7,'c',8.9), (8,'b',7.2), (9,'b',6.6);
INSERT INTO sensor VALUES (10,'a',3.0);

假设上表是某种传感器每隔一定时间记录一次状态（status）和数值（num）。问，每次处于一种状态的时候，这个数值的平均是多少，这段状态对应的 id 范围是多少。

分析

这个问题用各种编程语言都很好做。如果只要求每次出现的状态列表，用 uniq 命令就能完成。但是，如果用 SQL 的 GROUP BY event，会把 a b c 就分为三组，无法求得每次的状态切换。

PostgreSQL 以及很多数据库引擎都支持窗口函数，长得像 function(column) OVER (PARTITION BY ... ORDER BY ...)，可以不把数据合在一起实现类似 GROUP BY 的操作。

例如，求每种状态的平均数值而不合并结果：

SELECT id, status, avg(num) OVER (PARTITION BY status) FROM sensor ORDER BY id;

 id | status |  avg
----+--------+-------
  1 | a      | 2.975
  2 | a      | 2.975
  3 | a      | 2.975
  4 | b      |     7
  5 | b      |     7
  6 | c      |  9.05
  7 | c      |  9.05
  8 | b      |     7
  9 | b      |     7
 10 | a      | 2.975
(10 行记录)

例如，把每种状态的数值逐个累加：

SELECT id, status, sum(num) OVER (PARTITION BY status ORDER BY id)
FROM sensor ORDER BY id;

 id | status | sum
----+--------+------
  1 | a      |  3.1
  2 | a      |  5.7
  3 | a      |  8.9
  4 | b      |  7.4
  5 | b      | 14.2
  6 | c      |  9.2
  7 | c      | 18.1
  8 | b      | 21.4
  9 | b      |   28
 10 | a      | 11.9
(10 行记录)

所以我们看到，要把每次的相同状态分出组来，需要加一个分组依据，那就给每次的状态编上号。要把每次的状态编上号，先找出切换状态的那几行。

SELECT
  id, status, num,
  status IS DISTINCT FROM
    lag(status) OVER (ORDER BY id) status_changed
FROM sensor ORDER BY id;

 id | status | num | status_changed
----+--------+-----+----------------
  1 | a      | 3.1 | t
  2 | a      | 2.6 | f
  3 | a      | 3.2 | f
  4 | b      | 7.4 | t
  5 | b      | 6.8 | f
  6 | c      | 9.2 | t
  7 | c      | 8.9 | f
  8 | b      | 7.2 | t
  9 | b      | 6.6 | f
 10 | a      |   3 | t
(10 行记录)

然后通过 sum 对这个 status_changed 求和（TRUE 就是 1，FALSE 就是 0，PostgreSQL 需要显式类型转换）。

SELECT
  id, status, num,
  sum(status_changed::integer) OVER (ORDER BY id) status_no
FROM (
  SELECT
    id, status, num,
    status IS DISTINCT FROM
      lag(status) OVER (ORDER BY id) status_changed
  FROM sensor
) q1 ORDER BY id;

 id | status | num | status_no
----+--------+-----+-----------
  1 | a      | 3.1 |         1
  2 | a      | 2.6 |         1
  3 | a      | 3.2 |         1
  4 | b      | 7.4 |         2
  5 | b      | 6.8 |         2
  6 | c      | 9.2 |         3
  7 | c      | 8.9 |         3
  8 | b      | 7.2 |         4
  9 | b      | 6.6 |         4
 10 | a      |   3 |         5
(10 行记录)

然后按 status_no 分组就可以了。

SELECT
  min(id) id_first, max(id) id_last, status, avg(num) num_avg
FROM (
  SELECT
    id, status, num,
    sum(status_changed::integer) OVER (ORDER BY id) status_no
  FROM (
    SELECT
      id, status, num,
      status IS DISTINCT FROM
        lag(status) OVER (ORDER BY id) status_changed
    FROM sensor
  ) q1
) q2
GROUP BY status_no, status
ORDER BY id_first;

 id_first | id_last | status |     num_avg
----------+---------+--------+------------------
        1 |       3 | a      | 2.96666666666667
        4 |       5 | b      |              7.1
        6 |       7 | c      |             9.05
        8 |       9 | b      |              6.9
       10 |      10 | a      |                3
(5 行记录)

当然，如果需要每组中第一个和最后一个其他类型的值，而不是最大和最小，可以用 first_value 和 last_value 窗口函数。

扩展应用

比如这个表：

CREATE TABLE sensor2 (id INTEGER PRIMARY KEY, status TEXT);
INSERT INTO sensor2 VALUES (1,'a'), (2,NULL), (3,NULL);
INSERT INTO sensor2 VALUES (4,'b'), (5,NULL), (6,'c');
INSERT INTO sensor2 VALUES (7,NULL), (8,'b'), (9,NULL);
INSERT INTO sensor2 VALUES (10,'a');

我们要拿上一行的数据填充 NULL 值。

首先对 status 分组编号：

SELECT id, status, count(status) OVER (ORDER BY id) status_no
FROM sensor2 ORDER BY id;

 id | status | status_no
----+--------+-----------
  1 | a      |         1
  2 |        |         1
  3 |        |         1
  4 | b      |         2
  5 |        |         2
  6 | c      |         3
  7 |        |         3
  8 | b      |         4
  9 |        |         4
 10 | a      |         5
(10 行记录)

然后就能给 status 填上之前的值：

SELECT
  id, first_value(status) OVER (PARTITION BY status_no ORDER BY id) status
FROM (
  SELECT id, status, count(status) OVER (ORDER BY id) status_no
  FROM sensor2
) q1
ORDER BY id;

 id | status
----+--------
  1 | a
  2 | a
  3 | a
  4 | b
  5 | b
  6 | c
  7 | c
  8 | b
  9 | b
 10 | a
(10 行记录)

总结

通过窗口函数的扩展，我们可以利用 SQL 按顺序分组处理数据，实现类似 sed、awk、uniq 工具的功能，达到更高效地分析数据的目的。