时间戳

首页 > 解决方案 > 数组中时间戳之间的平均间隔

sql - 数组中时间戳之间的平均间隔

问题描述

在 PostgreSQL 9.x 数据库中，我有一列是时间戳类型的数组。每个数组都有 1..n 个时间戳。

我正在尝试提取每个数组中所有元素之间的平均间隔。

我了解在源表上使用窗口函数可能是解决此问题的理想方法，但在这种情况下，我试图将其作为对数组的操作来执行。

我查看了其他几个问题，这些问题试图计算另一列的移动平均值等或 avg（时间戳列表的中位日期）。

例如，我在一个包含 3 个元素的数组上寻找的平均间隔是这样的：

'{"2012-10-09 17:04:05.710887"
 ,"2013-10-18 22:30:08.973749"
 ,"2014-10-22 22:18:18.885973"}'::timestamp[]

将会：

-368d

想知道我是否需要通过函数解包数组？

标签： sqlarrayspostgresqlaverageintervals

解决方案

许多可能的一种方式：横向子查询中的unnest，join，avg：

SELECT *
FROM   tbl t
LEFT   JOIN LATERAL (
   SELECT avg(a2.ts - a1.ts) AS avg_intv
   FROM   unnest(t.arr) WITH ORDINALITY a1(ts, ord)
   JOIN   unnest(t.arr) WITH ORDINALITY a2(ts, ord) ON (a2.ord = a1.ord + 1)
   ) avg ON true;

db<>在这里摆弄

子查询中的[INNER] JOIN恰好产生与元素之间的间隔相关的组合集。

顺便说一句，我得到了371 days 14:37:06.587543，而不是'-368d'。

相关，有更多解释：

您也可以只取消嵌套一次并使用窗口函数lead()或lag()，但您试图避免窗口函数。在任何情况下，您都需要确保元素的原始顺序......

（没有可以直接使用的数组函数来获得所需的内容 - 如果您希望这样做。）

CTE 的替代品

可能只吸引一次 unnest （即使在避免窗口函数的情况下）：

SELECT *
FROM   tbl t
LEFT   JOIN LATERAL (
   WITH   a AS (SELECT * FROM unnest(t.arr) WITH ORDINALITY a1(ts, ord))
   SELECT avg(a2.ts - a1.ts) AS avg_intv
   FROM   a a1
   JOIN   a a2 ON (a2.ord = a1.ord +1)
   ) avg ON true;

但我预计增加的 CTE 开销将超过取消嵌套的两倍。主要只是在子查询中演示一个WITH子句。

推荐阅读