在滑动窗口框架中查找最大列值和最小列值-解网

问：

从过程编程开始，我正在考虑一个循环来解决这个问题。示例逻辑：

按值和日期列降序排序。查询类似以下内容：column1

select * 
from table1 
where column1 in ('cpu1') 
order by date_time desc;

从选择查询中查找最后一行以及其上方的 22 行，以定义要查看的第一组数据的循环起点。我目前没有序列化的行，但知道每个行条目之间的时间将是 5 分钟，因此我可以使用时间戳来确定 22 行。我认为查询看起来像这样：

select * 
from table1 
where date_time between x and y;

在 Between 语句中选择的数据中找到第 10 行，并确定第 10 行中的第 2 列是否大于上面的所有 12 行和下面的所有 9 行，或者第 3 列第 10 行是否小于上面的 12 行和下面的 9 行。如果它大于或小于将第 10 行打印到新表。

我还没有这个查询的任何内容，因为我不了解如何以使用 Javascript 数组或 Python 字典的方式导航表索引。

递增 X、Y 和第 10 行值 -1 并再次运行循环。

问题

我正在努力理解如何将 PostgreSQL 脚本放在一起。我已经用 Python 或 Javascript 设计了类似的样式循环，但是我读到的关于在 SQL 中实现它的可能方法的越多，我就越感到困惑。执行此 CTE 的最佳方法是 for、while 还是光标？

表数据示例：

列 1	列 2	第 3 列	日期
中央处理器1	3.2	1.1	01:45:00
中央处理器1	3.5	1.3	01:40:00
中央处理器1	5.9	0.1	01:35:00
中央处理器1	1.7	1.2	01:30:00
中央处理器1	2.5	2.3	01:25:00
中央处理器1	5.2	4.1	01:20:00
中央处理器3	4.2	1.1	01:45:00
中央处理器3	3.1	1.0	01:40:00
中央处理器3	5.0	3.3	01:35:00
中央处理器3	4.7	3.2	01:30:00
中央处理器3	2.7	2.1	01:25:00
中央处理器3	6.2	4.8	01:20:00

column1将是将时间序列写入数据库的设备名称
column2并将记录来自设备的值column3
日期将始终以 5 分钟为增量
table1 有数百万行

sql postgresql 每个组最大的 n-n- 窗口函数

SELECT *
FROM  (
   SELECT *, col2 > max(col2) OVER (PARTITION BY device ORDER BY ts ROWS BETWEEN 12 PRECEDING AND 9 FOLLOWING EXCLUDE CURRENT ROW)
          OR col3 < min(col3) OVER (PARTITION BY device ORDER BY ts ROWS BETWEEN 12 PRECEDING AND 9 FOLLOWING EXCLUDE CURRENT ROW) AS bingo
   FROM   tbl
   ) sub
WHERE  bingo;

对于单个给定设备：

SELECT *
FROM  (
   SELECT *
        , col2 > max(col2) OVER (ORDER BY ts ROWS BETWEEN 12 PRECEDING AND 9 FOLLOWING EXCLUDE CURRENT ROW) OR
          col3 < min(col3) OVER (ORDER BY ts ROWS BETWEEN 12 PRECEDING AND 9 FOLLOWING EXCLUDE CURRENT ROW) AS bingo
   FROM   tbl
   WHERE  device = 'cpu3'
   ) sub
WHERE  bingo;

小提琴

假设所有列都已定义，并且时间戳（在我的查询中）也是每个设备的。或者，您必须定义如何处理排序顺序中的空值和重复项。NOT NULLtsUNIQUE

这使用了一整套高级窗口功能。“帧排除”选项需要 Postgres 11 或更高版本。在此处阅读手册。相关：EXCLUDE CURRENT ROW

聚合不在同一组中的所有值

如果您习惯于程序语言，这不是一件容易的事。

在滑动窗口框架中查找最大列值和最小列值

Find greatest and least column values in a sliding window frame

评论

评论