PostgreSQL窗口函数实现用户任务平均耗时统计问题求助

你之前的SQL写法出现重复行的核心原因是未添加GROUP BY子句：窗口函数的特性是不会压缩结果行数，只会在当前结果集的每一行上计算对应分区内的聚合值，因此每个用户关联了多少个任务就会返回多少行结果，自然出现重复。

这个场景不需要使用窗口函数，直接在你原有正确的分组统计SQL基础上，新增普通AVG聚合函数即可实现需求，实现逻辑更简单、执行效率也更高。
另外注意你原有单任务时长的计算存在运算优先级错误：需要先计算两个时间戳的秒数差值，再整体除以3600才能得到正确的小时数，原写法是结束时间秒数减去（开始时间秒数/3600），计算结果不符合预期。

最优实现写法（普通聚合函数） #

SELECT 
    users.id AS u_id,
    concat(users.last_name, ', ', users.first_name) AS u_name, 
    COUNT(*) AS occurrences,
    -- 修正时长计算逻辑，差值整体除以3600得到小时数，可按需调整ROUND的小数保留位数
    ROUND(AVG((EXTRACT(EPOCH FROM tasks.closed_at) - EXTRACT(EPOCH FROM tasks.started_at)) / 3600), 1) AS average_duration
FROM tasks
INNER JOIN task_users ON task_users.task_id = tasks.id
INNER JOIN users ON users.id = task_users.user_id
WHERE tasks.closed_at IS NOT NULL 
-- 分组字段补充u_name适配所有数据库规范，避免部分数据库因非聚合字段未分组报错
GROUP BY u_id, u_name
ORDER BY occurrences DESC

窗口函数实现写法（不推荐，仅作原理参考） #

如果你需要用窗口函数实现也可以，需要搭配DISTINCT去重，本质是过滤掉同用户重复的计算行：

SELECT DISTINCT
    users.id AS u_id,
    concat(users.last_name, ', ', users.first_name) AS u_name, 
    COUNT(*) OVER(PARTITION BY users.id) AS occurrences,
    ROUND(AVG((EXTRACT(EPOCH FROM tasks.closed_at) - EXTRACT(EPOCH FROM tasks.started_at)) / 3600) OVER(PARTITION BY users.id), 1) AS average_duration
FROM tasks
INNER JOIN task_users ON task_users.task_id = tasks.id
INNER JOIN users ON users.id = task_users.user_id
WHERE tasks.closed_at IS NOT NULL 
ORDER BY occurrences DESC

该写法需要先关联全量任务数据再去重，执行效率远低于直接分组聚合，因此仅作窗口函数的用法参考，生产环境优先使用第一种写法。

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