sql - Postgres : Statistical functions on date time intervals

Question

我需要对时间间隔进行一些统计分析，即表中两个日期时间字段之间的差异。

根据聚合函数文档here .骨料MAX、MIN、AVG 等函数，即一般聚合函数接受日期时间和间隔字段的参数。

然而，对于更高级的统计函数，如 stddev_pop、var_pop、var_sam 和 std_samp，支持的输入似乎只能是数字或类似的。尽管文档表明这两种功能之间没有区别

... (These are separated out merely to avoid cluttering the listing of more-commonly-used aggregates.)...

有什么简单的方法可以计算这些参数吗？为什么不支持间隔类型作为参数？这些类型的统计聚合函数是单位不变的？

附言我不确定我是否可以提取纪元并使用它，因为某些值可能是负数。

Answer 1

正如我在评论中所说，要手动计算样本标准差，您需要在某个时候将一个区间乘以一个区间。 PostgreSQL 不支持。

要解决该问题，请将时间间隔缩短为数小时、数分钟或数秒(或其他任何时间)。事实证明，这比手动计算要简单得多，这也说明了为什么 PostgreSQL 不支持这种开箱即用的计算。

首先，来自 PostgreSQL general mailing list 的函数

CREATE OR REPLACE FUNCTION interval_to_seconds(interval)
RETURNS double precision AS $$
    SELECT (extract(days from $1) * 86400)
         + (extract(hours from $1) * 3600)
         + (extract(minutes from $1) * 60)
         + extract(seconds from $1);
$$ LANGUAGE SQL;

现在我们可以取一组简单区间的标准差。

with intervals (i) as (
  values (interval '1 hour'), (interval '2 hour'), (interval '3 hour'), 
         (interval '4 hour'), (interval '5 hour') 
)
, intervals_as_seconds as (
  select interval_to_seconds(i) as seconds
  from intervals
)
select stddev(seconds), stddev(seconds)/60
from intervals_as_seconds

in_sec             in_mindouble precision   double precision--5692.09978830308   94.8683298050514

You can verify the results however you like.

Now let's say you wanted hour granularity instead of seconds. Clearly, the choice of granularity is highly application dependent. You might define another function, interval_to_hours(interval). You can use a very similar query to calculate the standard deviation.

with intervals (i) as (
  values (interval '1 hour'), (interval '2 hour'), (interval '3 hour'), 
         (interval '4 hour'), (interval '5 hour') 
)
, intervals_as_hours as (
  select interval_to_hours(i) as hours
  from intervals
)
select stddev(hours) as stddev_in_hrs
from intervals_as_hours

stddev_in_hrsdouble precision--1.58113883008419

The value for standard deviation in hours is clearly different from the value in minutes or in seconds. But they measure exactly the same thing. The point is that the "right" answer depends on the granularity (units) you want to use, and there are a lot of choices. (From microseconds to centuries, I imagine.)

Also, consider this statement.

select interval_to_hours(interval '45 minutes')

interval_to_hoursdouble precision--0

这是正确答案吗？你不能说；正确答案取决于应用程序。我可以想象将 45 分钟视为 1 小时的应用程序。我还可以想象应用程序希望将 45 分钟视为 1 小时用于一些 计算，而作为 0 小时用于其他 计算。

然后想想这个问题。一个月有多少秒？表达式 select interval '1' month; 有效；秒数取决于一个月中有多少天。

我认为这就是 PostgreSQL 不支持这种开箱即用的计算的原因。使用区间参数的正确方法过于依赖于应用程序。

稍后。 . .

我在其中一个 PostgreSQL 邮件列表上找到了这个讨论。

No stddev() for interval?