PostgreSQL如何计算指定行求和后的平均值

你的问题核心是没有先按日期聚合每日的有效劳动时长，就直接对单条记录的duration求平均，这和需求里“先每日求和，再求日总和的平均值”不符。我们可以分两步来实现：

第一步：计算每个用户每日的有效劳动时长总和 #

先把日志按用户和日期分组，筛选掉休息类状态，计算当天所有有效状态的duration总和：

SELECT
  login,
  DATE(started_at) AS work_date,
  SUM(duration) FILTER (WHERE state NOT IN ('lunch', 'break1', 'break2')) AS daily_labor_total
FROM log
GROUP BY login, DATE(started_at)

这一步会得到每个用户每天的有效劳动时长总和，比如pdiddy在2018-05-25的总和就是1200 + 65 + 1115 + 143 + 2400（排除了lunch、break1、break2的记录）。

第二步：对每日总和求平均值 #

基于上面的结果，再按用户分组，对daily_labor_total求平均就是最终需要的劳动时长平均值：

SELECT
  login,
  AVG(daily_labor_total) AS labor_average
FROM (
  SELECT
    login,
    DATE(started_at) AS work_date,
    SUM(duration) FILTER (WHERE state NOT IN ('lunch', 'break1', 'break2')) AS daily_labor_total
  FROM log
  GROUP BY login, DATE(started_at)
) AS daily_totals
GROUP BY login

用CTE（公共表表达式）优化可读性 #

如果你觉得子查询有点绕，也可以用CTE来拆分逻辑，可读性更强：

WITH daily_labor_totals AS (
  SELECT
    login,
    DATE(started_at) AS work_date,
    SUM(duration) FILTER (WHERE state NOT IN ('lunch', 'break1', 'break2')) AS daily_total
  FROM log
  GROUP BY login, DATE(started_at)
)
SELECT
  login,
  AVG(daily_total) AS labor_average
FROM daily_labor_totals
GROUP BY login

这样就完全符合你的需求了：先按天汇总有效时长，再计算这些日汇总值的平均值。

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