主页 / user-144064

Roberto Ribeiro's questions

Martin Hope
Roberto Ribeiro
Asked: 2018-03-10 11:17:19 +0800 CST
  • 4

我有两个表，其中两列保留分类值的选项卡，例如：

Table 1
+----+-------+-----+-----------+
| ID | Begin | End | Condition |
+----+-------+-----+-----------+
|  1 |     1 |   8 |    Normal |
|  2 |     8 |  23 |  Critical |
|  3 |    23 |  30 |    Normal |
+----+-------+-----+-----------+

Table 2
+----+-------+-----+------------+
| ID | Begin | End | Supervisor |
+----+-------+-----+------------+
|  1 |     1 |  14 |       John |
|  2 |    14 |  30 |     Janice |
+----+-------+-----+------------+

这些 Begin 和 End 列表示值有效的连续间隔。在上面的示例中，时间间隔是一个月中的几天，因此根据表 1，我从第 1 天到第 8 天处于正常状态，从第 8 天到第 23 天处于严重状态，从第 23 天到第 30 天再次正常. 我从表 2 中得知，John 从 1 号到 14 号负责监督，而 Janice 从 14 号到 30 号接任。

我想要的是合并这两个表，在同一个表中同时具有 Condition 和 Supervisor 值，每对值的间隔最小。所以这：

Merged Table
+----+-------+-----+-----------+------------+
| ID | Begin | End | Condition | Supervisor |
+----+-------+-----+-----------+------------+
|  1 |     1 |   8 |    Normal |       John |
|  2 |     8 |  14 |  Critical |       John |
|  3 |    14 |  23 |  Critical |     Janice |
|  4 |    23 |  30 |    Normal |     Janice |
+----+-------+-----+-----------+------------+

这些表可以保证的是：

  • 每个都有其各自的“开始”和“结束”字段。
  • 这些将具有相同的跨度（第一行的“开始”值和最后一行的“结束”值对于每个表都是相同的）。
  • 每个间隔都是连续的（第 n 行的“结束”值始终等于第 n+1 行的“开始”值）。
  • 对于给定行，“结束”的值将始终高于“开始”。

我在 python 中以编程方式解决了这个问题，但我试图从我的工作流程中删除该脚本并直接在数据库中执行所有操作。最终我可以在 plpgsql 中复制我的 python 函数，但我想知道是否有更多类似 SQL 的方法来实现这一点？

postgresql gaps-and-islands
Martin Hope
Roberto Ribeiro
Asked: 2018-02-06 11:36:09 +0800 CST
  • 3

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

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