代码之家 › 专栏 › 技术社区 › lugger1

如何获取Postgres中时间间隔的平均值

generate-series average group-by postgresql sql

lugger1 · 技术社区 · 6 年前

我正在使用PostgreSQL 9.6。我有一张这样的桌子:

mac   sn         loc   time     date      vin1    vin2    vin3
1a34 4as11111111 aaaa  7:06:18  1/1/2018  447.42  472.32  682.59
1a34 4as11111111 aaaa  7:06:43  1/1/2018  455.97  476.25  682.59
1a34 4as11111111 aaaa  7:07:35  1/1/2018  470.88  484.2   682.5

我需要计算 vin1 ,则, vin2 ,则, vin3 在300秒(5分钟)的时间间隔内。例如,从第一次开始(7:06:18-7:11:18),对于范围内的日期。我可以通过此查询选择所需的数据:

select * from table
where sn='4as11111111' and date between '2018-01-01' and '2018-01-02';

但我不知道如何按300秒的时间间隔将其分组,并计算 VIN 1 ,则, VIN 2 ,则, VIN 3 5分钟间隔的列,得到如下结果:

mac  sn          loc     time     date      vin1_av  vin2_av  vin3_av
1a34 4as11111111 aaaa   7:06:18  1/1/2018  450.0    480.32   600.59
1a34 4as11111111 aaaa   7:11:18  1/1/2018  460.0    490.25   782.59
1a34 4as11111111 aaaa   7:16:18  1/1/2018  470.88   500.2    600.5

任何帮助都将不胜感激。

1 回复 | 直到 6 年前

Community CDub 4 年前

DB设计

而你可以使用单独的 date 和 time 列,与单个 timestamp 柱我会适应:

ALTER TABLE tbl ADD column ts timestamp;
UPDATE tbl SET ts = date + time;  -- assuming actual date and time types
ALTER TABLE tbl DROP column date, DROP column time;

如果日期和时间不实际 日期 和 时间 数据类型,使用 to_timestamp() .相关:

查询

那么查询就简单了一点:

SELECT *
FROM  (
   SELECT sn, generate_series(min(ts), max(ts), interval '5 min') AS ts
   FROM   tbl
   WHERE  sn = '4as11111111'
   AND    ts >= '2018-01-01'
   AND    ts <  '2018-01-02'
   GROUP  BY 1
   ) grid
CROSS  JOIN LATERAL (
   SELECT round(avg(vin1), 2) AS vin1_av
        , round(avg(vin2), 2) AS vin2_av
        , round(avg(vin3), 2) AS vin3_av
   FROM   tbl
   WHERE  sn =  grid.sn
   AND    ts >= grid.ts
   AND    ts <  grid.ts + interval '5 min'
   ) avg;

数据库(&L)&燃气轮机;不停摆弄 here

在第一个子查询中生成开始时间网格 grid ,从第一个运行到最后一个 排位赛 给定时间范围内的行。

使用 LATERAL 联接并立即聚合子查询中的平均值 avg .由于骨料的原因总是即使找不到条目,也返回一行。平均值默认为 NULL 在这种情况下。

结果包括给定时间范围内第一行和最后一行之间的所有时隙。其他各种结果组合也有意义。喜欢包括 全部的 给定时间帧中的时隙或仅具有实际值的时隙。在所有可能的情况下,我必须选择一种解释。

指数

至少具有以下多列索引:

CRATE INDEX foo_idx ON tbl (sn, ts);

或打开 (sn, ts, vin1, vin2, vin3) 如果满足某些前提条件,尤其是如果表行比演示中的要宽得多,则允许只进行索引扫描。

密切相关:

基于原始表格

As requested and clarified in the comment ,然后在问题中再次更新,以包含列 mac 和 loc .我想你希望每个 (mac, loc) 。

日期 和 时间 仍然是单独的列,vin*列是类型 float ,并排除没有行的时隙:

更新后的查询还移动了set returning函数 generate_series() 到 FROM 在Postgres 10之前更干净的列表:

SELECT t.mac, sn.sn, t.loc, ts.ts::time AS time, ts.ts::date AS date
     , t.vin1_av, t.vin2_av, t.vin3_av
FROM  (SELECT text '4as11111111') sn(sn)  -- provide sn here once
CROSS  JOIN LATERAL (
   SELECT min(date+time) AS min_ts, max(date+time) AS max_ts
   FROM   tbl
   WHERE  sn = sn.sn
   AND    date+time >= '2018-01-01 0:0'   -- provide time frame here
   AND    date+time <  '2018-01-02 0:0'
   ) grid
CROSS  JOIN LATERAL generate_series(min_ts, max_ts, interval '5 min') ts(ts)
CROSS  JOIN LATERAL (
   SELECT mac, loc
        , round(avg(vin1)::numeric, 2) AS vin1_av  -- cast to numeric for round()
        , round(avg(vin2)::numeric, 2) AS vin2_av  -- but rounding is optional
        , round(avg(vin3)::numeric, 2) AS vin3_av
   FROM   tbl
   WHERE  sn = sn.sn
   AND    date+time >= ts.ts
   AND    date+time <  ts.ts + interval '5 min'
   GROUP  BY mac, loc
   HAVING count(*) > 0  -- exclude empty slots
   ) t;

创建多列表达式索引以支持此操作:

CRATE INDEX bar_idx ON tbl (sn, (date+time));

数据库(&L)&燃气轮机;不停摆弄 here

但我宁愿使用 时间戳 一直以来。