代码之家 › 专栏 › 技术社区 › Mateusz UrbaÅski

PostgreSQL中时间间隔的计算

postgresql sql

Mateusz UrbaÅski · 技术社区 · 6 年前

在我的PostgreSQL数据库中,我有以下模式:

CREATE TABLE programs (
    id integer,
    description text
);

CREATE TABLE public.messages (
    id integer,
    program_id integer,
    text text,
    created_at timestamp with time zone
);

INSERT INTO programs VALUES(1, 'Test program');

INSERT INTO messages VALUES(1,1, 'Test message 1', now() - interval '7 days');
INSERT INTO messages VALUES(2,1, 'Test message 2', now() - interval '4 days');
INSERT INTO messages VALUES(3,1, 'Test message 3', now() - interval '1 days');

我想计算 created_at 在消息表中。它应该这样工作:

计算间隙 创造在 第一条和第二条消息。
计算间隙 创造在 第二条和第三条消息。
根据这些值计算平均间隙。

在PostgreSQL中有没有什么方法可以做到这一点?

https://www.db-fiddle.com/f/gvxijmp8u6wr6mYcSoAeVV/0

2 回复 | 直到 6 年前

Lukasz Szozda 6 年前

使用 LAG 加窗 AVG 要同时获得差异和平均差距:

WITH cte AS (
  SELECT *, 
  created_at-LAG(created_at) OVER(PARTITION BY program_id ORDER BY created_at) gap
  FROM messages
)
SELECT *, AVG(gap) OVER(PARTITION BY program_id) AS avg_gap
FROM cte;

db<>fiddle demo

Gordon Linoff 6 年前

如果您想要消息之间的平均时间,就不需要求出连续的差异。只需查看最早和最新的消息:

select program_id,
       (max(created_at) - min(created_at)) / nullif(count(*) - 1, 0)
from messages
group by program_id;

推荐文章

Johnny T · 基于当前值的SQL合并表[重复]

6 月前

Kallydi · jdbc无法连接到docker中的postgresql数据库

6 月前

dietzi96 · Pandas DataFrame.to_sql随机和静默地失败,没有错误消息

6 月前

Peter Schofield · 类型转换Postgresql

6 月前

BarÄ±Å UÅaklÄ± · 如何在ON冲突更新中使用CTE中的值

6 月前

Kevin Smeeks · Pyspark JDBC分区读取

6 月前

Micah Armantrout · 如何使用sql脚本将数据插入到代码优先的数据库中?

6 月前

Vanortton · 使用Docker开始应用程序开发还是在开发过程中配置Docker?[关闭]

6 月前

Andrus · 如何在sql中查找第二个匹配项

7 月前

MWood · Postgres jsonpath用于引用单例元素或元素数组

7 月前