主页 / dba / 问题 / 254004
Accepted
dotconnor
Asked: 2019-11-24 18:50:14 +0800 CST

查询一段时间内的条件

  • 772

假设我们有这样的表设置：

+---------+--------------+---------------+
| id text | time integer | value decimal |
+---------+--------------+---------------+
|       1 |            1 |            10 |
|       1 |            2 |            10 |
|       1 |            3 |             7 |
|       1 |            4 |             8 |
|       1 |            5 |             6 |
|       1 |            6 |             5 |
|       1 |            7 |             4 |
|       1 |            8 |             3 |
|       1 |            9 |             7 |
|       1 |           10 |            11 |
|       1 |           11 |             3 |
|       1 |           12 |             8 |
|       1 |           13 |             2 |
|       1 |           14 |             4 |
|       1 |           15 |             1 |
+---------+--------------+---------------+

是否可以在id一定时间内选择相同条件下的开始时间和结束时间？假设我们想查看1值所在的ID< 10至少几秒钟。所以结果查询将返回：

+------------+----------+
| start_time | end_time |
+------------+----------+
|         11 |       15 |
|          3 |        9 |
+------------+----------+
postgresql gaps-and-islands

2 个回答

  1. Best Answer

    Vérace的细微变化非常详细的答案。如果条件成立，想法是枚举每个 id 和每个 id plus 的行。我将差异命名为grp。如果 grp 发生变化，则意味着条件是否成立发生了变化。即按 grp 分组的 min 和 max 将适用于条件成立或失败的时间间隔。

    我对持续时间条件使用了有子句。

    select id, min(the_time), max(the_time) -- min and max per interval
from (
    select id, the_value, the_time
         -- grp is an id for each interval
         , row_number() over (partition by id order by the_time) 
         - row_number() over (partition by id
                                 , case when the_value < 10 
                                        then 1
                                        else 0 
                                   end 
                              order by the_time) as grp
    from tab
) as x
where the_value < 10 -- intervals where condition holds
group by id, grp
having max(the_time) - min(the_time) >= 4; -- duration

    添加的样本数据导致：

    1   3   9
1   11  15

    可以通过嵌套查询在一个地方确定条件，但我不确定这是否有用：

    select id, MIN(the_time) AS min, MAX(the_time) AS max -- min and max per interval
from (
    select id, the_value, the_time, condition
         -- grp is an id for each interval
         , row_number() over (partition by id order by the_time) 
         - row_number() over (partition by id, condition 
                              order by the_time) as grp
   from (
       select id, the_value, the_time, the_value < 10 as condition
       from tab
   ) as a
) as x
where condition -- intervals where condition holds
group by id, grp
having max(the_time) - min(the_time) >= 4 -- duration
ORDER BY id, min;
    • 3

  2. 为了解决这个问题，我做了以下事情（在这里摆弄- DDL、DML 和最终 SQL 也在这个答案的末尾）：

    编辑：简化且正确（无论如何正确答案！） SQL 发布到 dbfiddle here。SQL 的内部循环已略微修改（并简化），但对于那些有兴趣通过它的人来说，仍然可以在原始小提琴中遵循思路，将新小提琴的内部循环交换为旧小提琴的内部循环和然后用新循环运行旧小提琴的查询！@Lennart 的解决方案比我的更优雅。

    创建表：

    CREATE TABLE tab
(
  id TEXT NOT NULL,
  the_time INTEGER NOT NULL,
  the_value INTEGER NOT NULL
);

    并填充它：

    INSERT INTO tab
VALUES
(1, 1, 10), (1, 2, 10), (1, 3, 7), (1, 4, 8), (1, 5, 6), (1, 6, 5), (1, 7, 4),
(1, 8, 3), (1, 9, 7), (1, 10, 10), (1, 11, 12),
(2, 1, 10), (2, 2, 10), (2, 3, 10), (2, 4, 13), (2, 5, 6), (2, 6, 5), (2, 7, 4),
(2, 8, 3), (2, 9, 7),
(2, 10, 10), (2, 11, 12);

    您会注意到我添加了另一个数据略有不同的 id - 我想无论如何都会有多个 id，并且无论如何，它有助于测试。我还在原件中添加了一个基准，(1, 11, 12)因为这帮助我摆脱了NULLs 的棘手情况。由于这是时间序列类型的数据，我想不会有这样的“终点”，所以没关系。无论如何，NULL如果这是一个问题，我会让你来处理逻辑，或者只是标记它以引起我的注意。

    我不只是将解决方案丢在你的腿上，而是一步一步地完成它 - 这不仅希望能帮助你，而且还能帮助我记住！我以前做过这种事情，但我很少看到它像这里这样用英语表达得这么好。我没有使用我生成的所有额外字段，但我将它们作为“化石”保留，以便更容易遵循我的思路并了解解决方案如何发展。

    首先，我运行了这个 SQL：

    SELECT 
  id, the_time, 
  LAG(the_value, 1) OVER (PARTITION BY id ORDER BY id, the_time),
  the_value,
  LEAD(the_value, 1) OVER (PARTITION BY id ORDER BY id, the_time),
  CASE 
    WHEN (LAG(the_value, 1) OVER (PARTITION BY id ORDER BY id, the_time) >= 10 
      AND     the_value < 10) 
    THEN 1
    WHEN (LEAD(the_value, 1) OVER (PARTITION BY id ORDER BY id, the_time) >= 10
      AND      the_value >= 10)
    THEN 1
    ELSE 0
  END AS change
FROM tab;

    结果（为简洁起见）：

    id  the_time    lag the_value   lead    change
 1         1     10        10     10         1
 1         2     10        10      7         0
 1         3     10         7      8         1  << Major change of interest happens here!
 1         4      7         8      6         0
 1         5      8         6      5         0

    编辑以回应OP在评论中的问题：

    第一段 SQL 的关键是：

      CASE 
    WHEN (LAG(the_value, 1) OVER (PARTITION BY id ORDER BY id, the_time) >= 10 -- 1st CASE
      AND     the_value < 10) 
    THEN 1
    WHEN (LEAD(the_value, 1) OVER (PARTITION BY id ORDER BY id, the_time) >= 10
      AND      the_value >= 10) -- the 2nd CASE
    THEN 1
    ELSE 0
  END AS change

    change这样做的目的是每当 the_value 从（大于或等于 10）转换到小于 10 时将值设置为 1 - 第一个CASE，然后当它从（低于 10）转换回（10 或以上）时再次设置 - 第二个CASE。

    然后，在这个“内部循环”中，我运行了以下命令：

    SELECT id, the_time, SUM(change) OVER (PARTITION BY id ORDER BY id, the_time) AS the_sum
FROM
(
  SELECT 
    id, the_time, 
    LAG(the_value, 1) OVER (PARTITION BY id ORDER BY id, the_time),
    the_value,
    LEAD(the_value, 1) OVER (PARTITION BY id ORDER BY id, the_time),
    CASE 
      WHEN (LAG(the_value, 1) OVER (PARTITION BY id ORDER BY id, the_time) >= 10 
        AND     the_value < 10) 
      THEN 1
      WHEN (LEAD(the_value, 1) OVER (PARTITION BY id ORDER BY id, the_time) >= 10
        AND      the_value >= 10)
      THEN 1
      ELSE 0
    END AS change
  FROM tab
) AS t1
ORDER BY id, the_time;

    结果（再次，为简洁起见）：

    id  the_time    the_sum
 1         1          1
 1         2          1
 1         3          2   <<-- Again, the change of interest at time = 3!
 1         4          2
 1         5          2

    编辑以响应 OP 的查询：

    这样做是在 SUM 从查询 1 向下移动通过派生表时递增 SUM，在存在上述更改之一的边界处递增 - 从高于或等于 10 到低于 10 或相反。

    使用这个派生表，我运行了这个 SQL：

    SELECT id, MIN(the_time) AS the_min, MAX(the_time) AS the_max, the_sum
FROM
(
  SELECT id, the_time, SUM(change) OVER (PARTITION BY id ORDER BY id, the_time) AS the_sum
  FROM
  (
    SELECT 
      id, the_time, 
      LAG(the_value, 1) OVER (PARTITION BY id ORDER BY id, the_time),
      the_value,
      LEAD(the_value, 1) OVER (PARTITION BY id ORDER BY id, the_time),
      CASE 
        WHEN (LAG(the_value, 1) OVER (PARTITION BY id ORDER BY id, the_time) >= 10 
          AND     the_value < 10) 
        THEN 1
        WHEN (LEAD(the_value, 1) OVER (PARTITION BY id ORDER BY id, the_time) >= 10
          AND      the_value >= 10)
        THEN 1
        ELSE 0
      END AS change
    FROM tab
    ) AS t1
    ORDER BY id, the_time
  ) AS t2 
GROUP BY id, the_sum
ORDER BY id, the_sum;

    结果（未剪断）：

    id  the_min the_max the_sum
 1        1       2       1
 1        3       9       2
 1       10      11       3
 2        1       1       1
 2        2       2       2
 2        3       4       3
 2        5       9       4
 2       10      11       5

    由此我们拥有数据集中的所有“岛屿”。我们感兴趣的两个是3 - 9 for id = 1和5 - 9 for id = 2（我选择>= 5了我们感兴趣的岛屿的长度，因为问题中没有指定一个）。

    编辑以响应 OP 的查询：

    通过获取 the_time 的MIN()和MAX()，然后执行GROUP BYthe_sum（和 id，但这在这里并不重要），我们得到了给定“孤岛转换”开始的最短时间和“孤岛转换”完成的最长时间。从此处的派生表中获取此记录：

     1        3       9       2

    这告诉我们，对于，从 3 到 9 次，有一次总和为 2。两者id = 1都低于 10的事实告诉我们，这是一次低于 10 的运行——正是我们正在寻找的数据。

    所以，然后我用这个包围了上面的 SQL（关于小提琴和问题底部的完整工作 SQL）：

    SELECT 
  id, 
  the_min AS start_time, 
  the_max AS end_time, 
  (the_max - the_min) + 1 AS the_run_length  -- Optional, not requested
FROM
(
   Inner SQL above
)
WHERE ((the_max - the_min) + 1) >= 5  -- always brackets, operator precedence can be tricky!
AND (the_min < 10)
AND (the_max < 10)  -- don't want runs of greater than or = 10, only less than 10!
ORDER BY id, the_min;

    结果（完整）：

    id  start_time  end_time    the_run_length
 1           3         9                 7
 2           5         9                 5

    编辑以响应 OP 的查询：

    因此，这里我们通过使用WHERE子句消除所有不低于 10或不 >= 5 的孤岛来消除所有不重要的孤岛。

    而且，我们可以从原始数据的检查中看到，只有两次运行 the_value < 10，其中运行的长度 >= 5。您可能希望使用公用表表达式（也称为WITH子句）“整理”SQL ）。它们可以帮助使 SQL 更具可读性 - YMMV，但我会把它留给你！

    几点。您永远不应该使用 SQL 关键字（即time）作为表名或列名 - 如果您想移植 SQL，您只会在以后自找麻烦，而且这会使 SQL 难以阅读并且容易出错！其次，如果您以后要问问题，能否请您以 DDL 和 DML 的形式提供您的表格数据 - 手动输入内容也容易出错且棘手。帮助我们来帮助你！+1 一个有趣的问题！

    编辑以响应 OP 的查询：

    如果您对此有困难，我建议您从头开始，从内到外检查不同的查询 - 尝试删除WHERE条件 - 添加更多数据并查看发生了什么。窗口（或分析）函数非常强大，并且会以数量级回报您学习它们所付出的努力！

    =================== DDL、DML和最终SQL ====================

    DDL：

    CREATE TABLE tab
(
  id TEXT NOT NULL,
  the_time INTEGER NOT NULL,
  the_value INTEGER NOT NULL
);

    DML：

    INSERT INTO tab
VALUES
(1, 1, 10), (1, 2, 10), (1, 3, 7), (1, 4, 8), (1, 5, 6), (1, 6, 5), (1, 7, 4),
(1, 8, 3), (1, 9, 7), (1, 10, 10), (1, 11, 12),
(2, 1, 10), (2, 2, 10), (2, 3, 10), (2, 4, 13), (2, 5, 6), (2, 6, 5), (2, 7, 4),
(2, 8, 3), (2, 9, 7),
(2, 10, 10), (2, 11, 12);

    最终 SQL：

    SELECT 
  id, 
  the_min AS start_time, 
  the_max AS end_time, 
  (the_max - the_min) + 1 AS the_run_length  -- Optional, not requested
FROM
(
  SELECT id, MIN(the_time) AS the_min, MAX(the_time) AS the_max, the_sum
  FROM 
  (
    SELECT id, the_time, SUM(change) OVER (PARTITION BY id ORDER BY id, the_time) AS the_sum
    FROM
    (
      SELECT 
        id, the_time, 
        LAG(the_value, 1) OVER (PARTITION BY id ORDER BY id, the_time),
        the_value,
        LEAD(the_value, 1) OVER (PARTITION BY id ORDER BY id, the_time),
        CASE 
          WHEN (LAG(the_value, 1) OVER (PARTITION BY id ORDER BY id, the_time) >= 10 
            AND     the_value < 10) 
          THEN 1
          WHEN (LEAD(the_value, 1) OVER (PARTITION BY id ORDER BY id, the_time) >= 10
            AND      the_value >= 10)
          THEN 1
          ELSE 0
        END AS change
      FROM tab
      ) AS t1
      ORDER BY id, the_time
    ) AS t2 
    GROUP BY id, the_sum
    ORDER BY id, the_sum
  ) AS t3
WHERE ((the_max - the_min) + 1) >= 5  -- always brackets, operator precedence can be tricky!
AND (the_min < 10)
AND (the_max < 10)  -- don't want runs of `>` than or `=` 10, only less than!
ORDER BY id, the_min;
    • 1

相关问题