主页 / dba / 问题 / 197111
Accepted
Roberto Ribeiro
Asked: 2018-02-06 11:36:09 +0800 CST

对序列中的分类值进行分组

  • 772

我有一个按顺序排列的分类值表，如下所示：

CREATE TABLE cat (name, v1, v2)
AS VALUES
    ('John', 1::int, 3::int),
    ('John', 3, 4),
    ('John', 4, 9),
    ('Mike', 9, 11),
    ('Mike', 11, 17),
    ('John', 17, 24),
    ('John', 24, 25),
    ('Dave', 25, 29);

我想按名称列对它们进行分组，获取最小值_1 和最大值_2，但是将它们分组为名称以不间断的顺序出现，如下所示：

+-----------+---------+---------+
| name      | value_1 | value_2 |
+-----------+---------+---------+
| John      | 1       | 9       |
| Mike      | 9       | 17      |
| John      | 17      | 25      |
| Dave      | 25      | 29      |
+-----------+---------+---------+

但是，执行正常的 GROUP BY 选择会同时返回特定名称的所有条目，无论它们是否一起出现：

SELECT name, MIN(value_1), MAX(value_2)
FROM table
GROUP BY name;

+-----------+---------+---------+
| name      | value_1 | value_2 |
+-----------+---------+---------+
| John      | 1       | 25      |
| Mike      | 9       | 17      |
| Dave      | 25      | 29      |
+-----------+---------+---------+

现在，我可能可以用 plpgsql 来做到这一点，使用带有变量、循环等的传统编程逻辑，但我想知道是否有更多 SQL 风格的方法来解决这个问题。

软件：PostgreSQL 9.6.3

postgresql group-by

2 个回答

  1. Best Answer

    使用value_1对行进行排序，您可以在每次更改名称时进行分组。

    然后使用这个组来获取max和min值。

    select name, min(v1) v1, max(v2) v2
from (select name, v1, v2,
             sum(rst) over (order by v1) grp
      from (select name, v1, v2,
                   case when coalesce(lag(name) over (order by v1), '') <> name then 1 end rst
            from cat
            ) y
      ) z
group by name, grp
order by v1;

    姓名 | v1 | v2
:--- | -: | -:
约翰 | 1 | 9
迈克 | 9 | 17
约翰 | 17 | 25
戴夫 | 25 | 29

    dbfiddle在这里

    • 4

  2. 我读这个不同。我假设范围是链接的。例如

    bob, 2, 5
-- no bob 5,7
bob, 7,  10
bob, 11, 12

    应该导致两个不同的范围。为此有一个问题，您的模式是否允许重叠范围？如果没有，我的假设是，这应该有效。bob, 0, 10警告，如果你有一个像上面那样的范围，这将不起作用，

    在这里，我们计算与另一列相比滞后的重置。

    SELECT name, v1, v2, COALESCE(v1<>lag(v2) OVER (
  PARTITION BY name
  ORDER BY v1, v2
), true) AS rst
FROM cat;
 name | v1 | v2 | rst 
------+----+----+-----
 Dave | 25 | 29 | t
 John |  1 |  3 | t
 John |  3 |  4 | f
 John |  4 |  9 | f
 John | 17 | 24 | t
 John | 24 | 25 | f
 Mike |  9 | 11 | t
 Mike | 11 | 17 | f
(8 rows)

    然后我们计数得到组( grp)，

    SELECT name, v1, v2, count(rst OR null) OVER (ORDER BY name, v1, v2) AS grp
FROM (
        SELECT name, v1, v2, COALESCE(v1<>lag(v2) OVER (
                PARTITION BY name
                ORDER BY v1, v2
        ), true) AS rst
        FROM cat
) AS t;
 name | v1 | v2 | grp 
------+----+----+-----
 Dave | 25 | 29 |   1
 John |  1 |  3 |   2
 John |  3 |  4 |   2
 John |  4 |  9 |   2
 John | 17 | 24 |   3
 John | 24 | 25 |   3
 Mike |  9 | 11 |   4
 Mike | 11 | 17 |   4
(8 rows)

    然后我们按grp专栏分组以完成它。

    SELECT name, min(v1), max(v2)
FROM (
        SELECT name, v1, v2, count(rst OR null) OVER (ORDER BY name, v1, v2) AS grp
        FROM (
                SELECT name, v1, v2, COALESCE(v1<>lag(v2) OVER (
                        PARTITION BY name
                        ORDER BY v1, v2
                ), true) AS rst
                FROM cat
        ) AS t1
) AS t2
GROUP BY grp, name;
 name | min | max 
------+-----+-----
 Dave |  25 |  29
 John |   1 |   9
 John |  17 |  25
 Mike |   9 |  17
(4 rows)

    行为差异..

    INSERT INTO cat (name, v1, v2)
VALUES ('Dave', 31, 35);

 name | min | max 
------+-----+-----
 Dave |  25 |  29
 Mike |   9 |  17
 Dave |  31 |  35
 John |   1 |   9
 John |  17 |  25
(5 rows)
    • 3

相关问题