时间戳平均

代码片段

时间戳平均

适用于 PostgreSQL

9.1

使用语言

PL/pgSQL

依赖于

无

以下是有效计算时间戳列平均值的代码。我只在 9.1 上测试过，但它可能也能在早期版本上运行。请注意，您需要将列转换为普通时间戳（例如 SELECT avg(tstz_col AT TIME ZONE 'UTC') FROM mytable）才能将其用于类型为“带有时区的 timestamp”的列。

作者：Josh Kupershmidt

-- In order to have a reasonably efficient accumulator
-- function, we need a state variable keeping a running
-- total of seconds since the epoch, along with the number
-- of elements processed already.
CREATE TYPE ts_accum_typ AS (
  running_total numeric,
  num_elems bigint
);

-- Accumulator function. Keep a running total of the
-- number of seconds since the epoch (1970-01-01), as well
-- as the number of elements we have processed already.
CREATE OR REPLACE FUNCTION ts_accum (existing ts_accum_typ, newval timestamp)
RETURNS ts_accum_typ AS $$
DECLARE
  retval ts_accum_typ;
BEGIN

  IF newval IS NULL THEN
    RETURN existing;
  END IF;

  IF existing IS NULL THEN
    retval.running_total = EXTRACT(epoch FROM newval);
    retval.num_elems = 1;
    RETURN retval;
  ELSE
    existing.running_total = existing.running_total + EXTRACT(epoch FROM newval);
    existing.num_elems = existing.num_elems + 1;
    RETURN existing;
  END IF;
END;
$$
LANGUAGE PLPGSQL IMMUTABLE;

-- Final function for the timestamp 'avg' aggregate.
CREATE OR REPLACE FUNCTION ts_avg (existing ts_accum_typ) RETURNS timestamp AS $$
DECLARE
   since_epoch numeric;
BEGIN
  -- Handle the case when avg() is called with no rows: answer should be NULL.
  IF existing IS NULL THEN
    RETURN NULL;
  END IF;

  since_epoch = existing.running_total / existing.num_elems;
  RETURN to_timestamp(since_epoch);
END;
$$
LANGUAGE PLPGSQL IMMUTABLE;

CREATE AGGREGATE avg (timestamp)
(
	sfunc = ts_accum,
	stype = ts_accum_typ,
	finalfunc = ts_avg
);