提问人：walker967 提问时间：9/12/2023 最后编辑：nbkwalker967 更新时间：9/12/2023 访问量：28
PostgreSQL - 如何使用 LAG（） 跟踪条件为真的时间？
PostgreSQL - How to keep track of time since a condition was true using LAG()?
问：
我有一张这样的表：
| 日期 | 价值 |
|---|---|
| 2023-08-05 00:00:45.000 -0500 | 174 |
| 2023-08-05 00:01:46.000 -0500 | 175 |
| 2023-08-05 00:02:46.000 -0500 | 175 |
| 2023-08-05 00:03:45.000 -0500 | 174 |
| 2023-08-05 00:05:59.000 -0500 | 179 |
| 2023-08-05 00:18:45.000 -0500 | 178 |
| 2023-08-05 00:21:55.000 -0500 | 178 |
我想制作一个 SELECT 查询来添加一个新列“output”，该列是“SKIP”或“KEEP”，具体取决于几个不同的逻辑片段。一个特别麻烦的部分是：
如果当前行和最后一个“KEEP”行之间经过的时间大于 5 分钟，则将当前行从 SKIP 更改为 KEEP。
期望输出：
| 日期 | 价值 | 输出 |
|---|---|---|
| 2023-08-05 00:00:45.000 -0500 | 174 | 保持 |
| 2023-08-05 00:01:46.000 -0500 | 175 | 跳 |
| 2023-08-05 00:02:46.000 -0500 | 175 | 跳 |
| 2023-08-05 00:03:45.000 -0500 | 174 | 跳 |
| 2023-08-05 00:05:59.000 -0500 | 179 | 保持 |
| 2023-08-05 00:18:45.000 -0500 | 178 | 保持 |
| 2023-08-05 00:21:55.000 -0500 | 178 | 跳 |
我尝试过的：
SELECT *,
CASE
WHEN (date - LAG(date) OVER (ORDER BY date ASC)) > INTERVAL '5 minutes' THEN 'KEEP',
WHEN LAG(date) OVER (ORDER BY date) IS NULL THEN 'PUSH'
ELSE 'SKIP'
END AS output
from table;
我在 CASE 语句中使用了 LAG（） 函数。这可以将当前行与前一行进行比较，但我不知道如何修改它以与上一行而不是前一行进行比较。（我还使用了 IS NULL 部分来确保第一个值是 KEEP。
例如，上面的查询不会将第 5 行标记为 KEEP，因为它将 03：45 时间戳与 05：59 进行比较，两者相差不到 5 分钟。我希望它是 KEEP，因为最后一次 KEEP 发生在 （05：59 - 00：45 = 5 分 14 秒）前。
我希望一切都是有道理的。我已经为此苦苦挣扎了一段时间，所以任何帮助将不胜感激！非常感谢。
答：
1赞
Erwin Brandstetter
9/12/2023
#1
你不能用 - 或 Postgres 中任何可用的窗口函数来解决这个问题。lag()
每行的状态可能取决于之前的所有行（而相邻行之间的间隙小于 5 分钟）。超过 5 分钟的间隙会重置计数，但是虽然我们不能对以前的间隙做出假设，但我们确实必须遍历整个表才能可靠地计算当前状态。因此，这有利于采用过程方法，而不是基于集合的解决方案。在一个简单的集合返回 PL/pgSQL 函数中建议一个循环：
创建一次：
CREATE OR REPLACE FUNCTION public.f_keepers()
RETURNS TABLE (ts timestamptz, value int, keep bool)
LANGUAGE plpgsql AS
$func$
DECLARE
threshold timestamptz;
BEGIN
FOR ts, value IN
SELECT t.ts, t.value
FROM tbl t
ORDER BY t.ts
LOOP
IF ts <= threshold THEN -- also covers null in first iteration
keep := false;
ELSE
keep := true;
threshold := ts + interval '5 min';
END IF;
RETURN NEXT;
END LOOP;
END
$func$;
叫：
SELECT * FROM public.f_keepers();
如果一个状态在绝大多数行中占主导地位，我会立即分配该状态，并且只重新分配稀有状态。
相关：
旁白：
使用列的列名。“日期”太具有误导性了。
结果以 命名 ，即拟合数据类型。tstimestamptzbooleankeep
评论