魔琴编程网
  • postgresql/
  • 在PostgreSQL中基于瞬时时间前后2分钟创建小时平均值

在PostgreSQL中基于瞬时时间前后2分钟创建小时平均值

postgresql

在PostgreSQL中基于瞬时时间前后2分钟创建小时平均值,postgresql,average,temporal-database,Postgresql,Average,Temporal Database,我有一个2分钟采样频率的时态数据库，我想提取瞬时小时值，如00:00，01:00，02。。。每天23英镑。 所以，我想从平均值中得到平均值： HH-1:58、HH:00和HH:02=小时点的平均值 或 HH-1:59，HH:01和HH:03=小时点的平均值 样本数据1： 9/28/2007 23:51 -1.68 9/28/2007 23:53 -1.76 9/28/2007 23:55 -1.96 9/28/2007 23:57 -2.02 9/28/2007 23:59 -1.92 9/29

我有一个2分钟采样频率的时态数据库，我想提取瞬时小时值，如00:00，01:00，02。。。每天23英镑。
所以，我想从平均值中得到平均值：

HH-1:58、HH:00和HH:02=小时点的平均值

或

HH-1:59，HH:01和HH:03=小时点的平均值

样本数据1：

9/28/2007 23:51 -1.68
9/28/2007 23:53 -1.76
9/28/2007 23:55 -1.96
9/28/2007 23:57 -2.02
9/28/2007 23:59 -1.92
9/29/2007 0:01  -1.64
9/29/2007 0:03  -1.76
9/29/2007 0:05  -1.83
9/29/2007 0:07  -1.86
9/29/2007 0:09  -1.94
预期结果：

午夜零时：

（-1.92+-1.64+-1.76）/3

样本数据2：

9/28/2007 23:54 -1.44
9/28/2007 23:56 -1.58
9/28/2007 23:58 -2.01
9/29/2007 0:00  -1.52
9/29/2007 0:02  -1.48
9/29/2007 0:04  -1.46
预期成果：

（-2.01+-1.52+-1.48）/3

PostgreSQL使任何涉及相邻行的操作都比以前简单得多。未经试验，但大致正确：

select
  date_trunc('hour', newest_time) as average_time,
  (oldest_temp + middle_temp + newest_temp) / 3 as average_temp
from (
  select
    date_trunc('hour', sample_time) as average_time,
    lag(sample_time, 2) over w as oldest_time,
    lag(sample_time, 1) over w as middle_time,
    sample_time as newest_time,
    lag(sample_temp, 2) over w as oldest_temp,
    lag(sample_temp, 1) over w as middle_temp,
    sample_temp as newest_temp
  from
    samples
  window
    w as (order by sample_time)
) as s
where
  oldest_time = newest_time - '4 minutes'::interval and
  middle_time = newest_time - '2 minutes'::interval and
  extract(minute from newest_time) in (2, 3);
我在
where
子句中将此限制为您所描述的场景-最新值为：02或：03，之前的2个值为2分钟和4分钟。以防丢失一些数据，否则会产生奇怪的结果，比如在更长的时间间隔内求平均值。

PostgreSQL使涉及相邻行的任何操作都比以前简单得多。未经试验，但大致正确：

select
  date_trunc('hour', newest_time) as average_time,
  (oldest_temp + middle_temp + newest_temp) / 3 as average_temp
from (
  select
    date_trunc('hour', sample_time) as average_time,
    lag(sample_time, 2) over w as oldest_time,
    lag(sample_time, 1) over w as middle_time,
    sample_time as newest_time,
    lag(sample_temp, 2) over w as oldest_temp,
    lag(sample_temp, 1) over w as middle_temp,
    sample_temp as newest_temp
  from
    samples
  window
    w as (order by sample_time)
) as s
where
  oldest_time = newest_time - '4 minutes'::interval and
  middle_time = newest_time - '2 minutes'::interval and
  extract(minute from newest_time) in (2, 3);

SELECT  hr, ts, aval
FROM    (
        SELECT  *, ROW_NUMBER() OVER (PARTITION BY hr ORDER BY ts) rn
        FROM    (
                SELECT  *,
                        DATE_TRUNC('hour', ts) AS hr,
                        AVG(value) OVER (ORDER BY ts ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS aval
                FROM    mytable
                ) q
        ) q
WHERE   rn = 1
我在
where
子句中将此限制为您所描述的场景-最新值为：02或：03，之前的2个值为2分钟和4分钟。以防丢失一些数据，否则会产生奇怪的结果，比如在更长的时间间隔内求平均值。

我发现了这个错误。错误：SQL状态为或接近“over”时出现语法错误：42601字符：110请尝试此版本，它为我运行（尽管我没有输入任何数据来尝试）。我好像有点误解了
；它将范围应用于单个
select
子句，而不是查询中的每个窗口函数。我不确定是否可以将其应用于
where
，因此我使用了一个子查询来实现这一点。啊，不可能从where使用窗口函数，因为where过滤首先发生：“窗口函数考虑的行是”虚拟表的行“由查询的FROM子句生成，按WHERE、GROUP by和HAVING子句（如果有）进行筛选”。因此子查询是必要的。我发现了此错误。错误：SQL状态为“over”或接近“over”的语法错误：42601字符：110请尝试此版本，该版本为我运行（尽管我没有输入任何数据来尝试）。似乎我对
有点误解了
；它将作用域应用于单个
select
子句，而不是查询中的每个窗口函数。我不确定是否可以将其应用于
where
，因此我使用子查询来实现。啊，不可能从where使用窗口函数，因为where过滤器首先发生的是：“窗口函数所考虑的行是由查询的FROM子句生成的“虚拟表”的行，该子句通过WHERE、GROUP by和HAVING子句（如果有）进行过滤”。因此，子查询是必要的。

SELECT  hr, ts, aval
FROM    (
        SELECT  *, ROW_NUMBER() OVER (PARTITION BY hr ORDER BY ts) rn
        FROM    (
                SELECT  *,
                        DATE_TRUNC('hour', ts) AS hr,
                        AVG(value) OVER (ORDER BY ts ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS aval
                FROM    mytable
                ) q
        ) q
WHERE   rn = 1