SQL 查询迭代表以计算在其中一个字段中具有相同值的第 1 条记录和第 2 条记录之间的时间差？

Question

我有一个 postgresql 表存储来自在线论坛的帖子。每个帖子都属于一个线程。我想计算启动线程的帖子获得第一个响应所需的时间(有时线程永远不会得到响应，因此必须考虑这一点)

posts 表有这些字段:

post_id, post_timestamp, thread_id

每个 thread_id 可以有一个或多个帖子。例如，此查询返回 ID 为 1234 的线程的第一篇和第二篇文章:

select * from posts where thread_id = 1234 order by post_timestamp limit 2

我想计算第一篇文章和第二篇文章之间的时间差，并将其存储在包含以下字段的单独表中:

thread_id, seconds_between_1s_and_2nd

Answer 1

SELECT  (
        SELECT  post_timestamp
        FROM    posts
        WHERE   thread_id = t.id
        ORDER BY
                post_timestamp
        LIMIT 1 OFFSET 1
        ) - 
        (
        SELECT  post_timestamp
        FROM    posts
        WHERE   thread_id = t.id
        ORDER BY
                post_timestamp
        LIMIT 1 OFFSET 0
        )
FROM    threads t

，或者，在 PostgreSQL 8.4+ 中:

SELECT  (
        SELECT  post_timestamp - LAG(post_timestamp) OVER (ORDER BY post_timestamp)
        FROM    posts
        WHERE   thread_id = t.id
        ORDER BY
                post_timestamp
        LIMIT 1 OFFSET 1
        )
FROM    threads t

要以秒为单位表示，请使用 EXTRACT(epoch FROM AGE(time1, time2))