postgresql - 如何优化需要时间戳规范化的查询

Question

我有以下数据源，其中有多个物理值(每列一个)来自不同时间的多个设备:

+-----------+------------+---------+-------+
| id_device | timestamp  |  Vln1   | kWl1  |
+-----------+------------+---------+-------+
|       123 | 1495696500 |         |       |
|       122 | 1495696800 |         |       |
|       122 | 1495697100 | 230     | 5.748 |
|       122 | 1495697100 | 230     | 5.185 |
|       124 | 1495700100 | 226.119 | 0.294 |
|       122 | 1495713900 | 230     |       |
|       122 | 1495716000 |         |       |
|       122 | 1495716300 | 230     |       |
|       122 | 1495716300 |         |       |
|       122 | 1495716300 |         |       |
|       122 | 1495716600 | 230     | 4.606 |
|       122 | 1495716600 |         |       |
|       124 | 1495739100 |         |       |
|       123 | 1495739400 |         |       |
+-----------+------------+---------+-------+

timestamp 是(不幸的是)bigint 并且每个设备在不同时间以不同频率发送数据:一些设备每 5 分钟推送一次，其他设备每 10 分钟推送一次，其他设备每 15 分钟一次物理值可以是 NULL。

前端应用程序需要绘制特定时间戳的图表 - 让我们说折线图，每分钟都有时间滴答。时间标记由用户选择。图表可以由多个设备的多个物理值组成，每一行都是对后端的独立请求。

让我们考虑一个案例:

1. 选择的时间刻度为 10 分钟
2. 选择要绘制的两条线，在两个不同的设备上具有两个不同的物理值(列):
   1. 设备每 5 分钟推送一次
   2. 另一个每 10 分钟一次

前端应用期望的是标准化结果:

<timestamp>, <value>

在哪里

1. timestamp 表示舍入时间(00:00、00:10、00:20 等等)
2. 如果每个“时间框”中有多个值(例如:在 00:00 和 00:10 之间每 5 分钟推送一次的设备将有 2 个值)，一个将返回值，这是一个聚合值(AVG)

为了完成这个，我创建了一些 plpgsql 函数来帮助我，但我不确定我正在做的在性能方面是最好的。

基本上我做的是:

1. 在选定的时间跨度内获取特定设备和物理指标的数据
2. 标准化返回的数据:每个时间戳都四舍五入到所选的时间刻度(即 10:12:23 -> 10:10:00)。这样，每个元组将代表“时间段”内的一个值
3. 根据用户选择的时间标记创建时间段的范围
4. JOIN 具有范围的时间戳标准化数据。如果同一范围内有多个值，则聚合

这是我的功能:

create  or replace function app_iso50k1.blkGetTimeSelParams(
      t_end bigint,
      t_granularity integer,
      t_span bigint,
  OUT delta_time_bucket interval,
  OUT b_timebox timestamp,
  OUT e_timebox timestamp)
as
$$
DECLARE
  delta_time interval;
BEGIN
  /* normalization: no minutes */
  t_end = extract('epoch' from date_trunc('minute', (to_timestamp(t_end) at time zone 'UTC')::timestamp));

  delta_time =  app_iso50k1.blkGetDeltaTimeBucket(t_end, t_granularity);
  e_timebox = date_trunc('minute', (to_timestamp(t_end - extract('epoch' from delta_time)) at time zone 'UTC'))::timestamp;
  b_timebox = (to_timestamp(extract('epoch' from e_timebox) - t_span) at time zone 'UTC')::timestamp;

  delta_time_bucket = delta_time;
END
$$ immutable language 'plpgsql' security invoker;


create or replace function app_iso50k1.getPhyMetData(
  tablename character varying,
  t_span bigint,
  t_end bigint,
  t_granularity integer,
  idinstrum integer,
  id_device integer,
  varname character varying,
  op character varying,
  page_size int,
  page int)
  RETURNS TABLE(times bigint , val double precision) as
$$
DECLARE
  series REFCURSOR;
  serie RECORD;
  first_notnull bool = false;
  prev_val double precision;
  time_params record;
  q_offset int;
BEGIN
  time_params = app_iso50k1.blkGetTimeSelParams(t_end, t_granularity, t_span);
  if(page = 1) then
    q_offset = 0;
  else
    q_offset = page_size * (page -1);
  end if;

  if not public.blkIftableexists('resgetphymetdata')
  THEN
    create temporary table resgetphymetdata (times bigint, val double precision);
  ELSE
    truncate table resgetphymetdata;
  END IF;

  execute format($ff$
  insert into resgetphymetdata (
    /* generate every possible range between these dates */
    with ranges as (
        select generate_series($1, $2, interval '$5 minutes') as range_start
    ),
      /* normalize your data to which <t_granularity>-minute interval it belongs to */
    rounded_hst as (
      select
        date_trunc ('minutes', (to_timestamp("timestamp") at time zone 'UTC')::timestamp)::timestamp -
        mod (extract ('minutes' from ((to_timestamp("timestamp") at time zone 'UTC')::timestamp))::int, $5) * interval '1 minute' as round_time,
        *
      from public.%I
      where
        idinstrum = $3 and
        id_device = $4 and
        timestamp <= $8
    )
    select
      extract('epoch' from r.range_start)::bigint AS times,
      %s (hd.%I) AS val
    from
      ranges r
      left join rounded_hst hd on r.range_start = hd.round_time
    group by
      r.range_start
    order by
      r.range_start
    LIMIT $6 OFFSET $7
  );
  $ff$, tablename, op, varname) using time_params.b_timebox, time_params.e_timebox, idinstrum, id_device, t_granularity, page_size, q_offset, t_end;

  /* data cleansing: val holes between not-null values are filled with the previous value */
  open series no scroll for select * from resgetphymetdata;
  loop
    fetch series into serie;
    exit when not found;

    if NOT first_notnull then
      if serie.val NOTNULL then
        first_notnull = true;
        prev_val = serie.val;
      end if;
    else
      if serie.val is NULL then
        update resgetphymetdata
        set val = prev_val
        where current of series;
      else
        prev_val = serie.val;
      end if;
    end if;
  end loop;
  close series;

  return query select * from resgetphymetdata;
END;
$$ volatile language 'plpgsql' security invoker;

您是否看到我编写的代码有好的替代方案？有改进的余地吗？谢谢!

Answer 1

您可以使用纯 SQL 查询完全转换您的迭代逻辑。您可以使用函数参数化查询。为了获得更好的性能，请为您的函数使用 sql 语言。

您可以使用此处解释的 Window 函数在时间序列间隔上构建部分总和
Window function trailing dates in PostgreSQL

其他建议

• 使用 coalesce 管理空值
• 使用专用计算列避免时间戳转换
• 您可以使用小型计算 View 并将它们加入最终查询或使用 LATERAL JOIN