sql - 在 PostgreSQL 中计算时间间隔

Question

在我的 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 之间的差距。它应该以这种方式工作:

1. 计算第一条消息和第二条消息的 created_at 之间的差距。
2. 计算第二条和第三条消息的 created_at 之间的差距。
3. 根据这些值计算平均差距。

有没有办法在 PostgreSQL 中做这样的事情？

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

Answer 1

使用 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