PostgreSQL 10.3中计算平均时间间隔长度的技术问询

我来帮你解决这个计算平均操作时间间隔的问题～首先得补全你没写完的moves表结构，毕竟要计算时间间隔，肯定需要一个记录操作时间的字段。假设完整的moves表定义是这样的：

CREATE TABLE moves (
    mid BIGSERIAL PRIMARY KEY,
    uid integer NOT NULL REFERENCES players ON DELETE CASCADE,
    gid integer NOT NULL REFERENCES games ON DELETE CASCADE, -- 关联到具体对局
    move_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, -- 记录操作发生的时间
    move_content text -- 可选，存储具体操作内容，不影响时间计算
);

接下来，要计算所有对局中连续操作的平均时间间隔，我们可以用PostgreSQL的窗口函数LAG()来实现，它能帮我们获取同一对局中上一步操作的时间。具体SQL查询如下：

WITH game_move_intervals AS (
    SELECT
        gid,
        -- 计算当前操作与上一步操作的时间差
        move_time - LAG(move_time) OVER (PARTITION BY gid ORDER BY move_time) AS interval_length
    FROM moves
)
-- 计算所有有效间隔的平均值
SELECT AVG(interval_length) AS average_move_interval
FROM game_move_intervals
WHERE interval_length IS NOT NULL; -- 排除每个对局的第一步（没有上一步，间隔为NULL）

代码解释： #

1. CTE部分（game_move_intervals）：

   • PARTITION BY gid：按对局分组，确保我们只计算同一局内的操作间隔。
   • ORDER BY move_time：每个对局内的操作按时间顺序排序，保证我们拿到的是连续的上一步操作。
   • LAG(move_time)：获取当前操作的上一步操作时间，两者相减得到时间间隔（类型为interval）。

2. 主查询部分：

   • AVG(interval_length)：计算所有非空间隔的平均值，PostgreSQL会自动处理interval类型的平均值计算。
   • WHERE interval_length IS NOT NULL：每个对局的第一步操作没有上一步，所以间隔为NULL，这些记录不需要纳入计算。

如果你的需求是计算每个玩家自己操作的平均时间间隔（比如玩家A两次操作之间的平均间隔），可以调整窗口函数的分组方式：

WITH player_move_intervals AS (
    SELECT
        uid,
        move_time - LAG(move_time) OVER (PARTITION BY uid ORDER BY move_time) AS interval_length
    FROM moves
)
SELECT
    p.name,
    AVG(pi.interval_length) AS average_player_interval
FROM player_move_intervals pi
JOIN players p ON pi.uid = p.uid
WHERE pi.interval_length IS NOT NULL
GROUP BY p.uid, p.name;

内容的提问来源于stack exchange，提问作者Alexander Farber