PostgreSQL如何获得特定行的总和平均值

Question

如果标题不够恰当，我很抱歉，我有点难以准确表达。无论如何，我有一个记录人员状态的表格，它长这样:

id, login, state, duration, started_at
1, pdiddy, working, 1200, 2018-05-25 08:30:00
2, pdiddy, lunch, 120, 2018-05-25 9:00:00
3, pdiddy, on_call, 65, 2018-05-25 12:30:00
4, pdiddy, available, 1115, 2018-05-25 12:30:00
5, pdiddy, working, 143, 2018-05-25 12:30:00
6, pdiddy, break1, 150, 2018-05-25 12:30:00
7, pdiddy, working, 2400, 2018-05-25 12:30:00
8, pdiddy, break2, 110, 2018-05-25 12:30:00

我需要按照每个用户和每天来计算除了“lunch”、“break1”和“break2”之外的所有劳动相关持续时间的平均值。所以基本上我需要将任何给定日期中除了 “lunch”、 “break1” 和 “break2” 之外的所有操作记录的持续时间相加，并取其平均值。

我试图像这样做，但问题是它没有在计算平均值之前将与劳动相关的操作记录相加。我无法想出如何做到这一点。

SELECT
    log.login,
    AVG(log.duration) FILTER (WHERE log.state NOT IN ('lunch', 'break1', 'break2')) AS "labor_average"
FROM
    log
GROUP BY 1

显然我并不期望有人为我做这件事。我只需要指引正确的方向。显然我距离解决方案还有很长的路要走，所以我只需要在正确的方向上得到一些推动。非常感谢您提前的帮助！

- Luke Visinoni

---

什么的平均值？持续时间吗？ - The Impaler

不包括“午餐”，“休息1”或“休息2”的持续时间总和的平均数。 - Luke Visinoni

抱歉，我修正了我的描述。现在应该不那么荒谬了。 - Luke Visinoni

Answer 1

我认为您想要总和除以天数的结果：

SELECT l.login,
       (SUM(l.duration) FILTER (WHERE l.state NOT IN ('lunch', 'break1', 'break2')) /
        COUNT(DISTINCT date_trunc('day', l.started_at)
       ) AS labor_average
FROM log l
GROUP BY l.login

- Gordon Linoff

---

COUNT 是否应该与 SUM 使用相同的 FILTER？为什么要使用 FILTER？为什么不使用简单的 WHERE？ - Vladimir Baranov

这个结果几乎与其他回复的结果完全一样。我认为这是我必须采用的解决方案，因为不幸的是，我不认为我能够使用所使用的工具进行子查询。谢谢！ - Luke Visinoni

@VladimirBaranov...我猜每天都有“工作”状态并休息。如果没有，不清楚OP是否想要计算它们。 - Gordon Linoff

Answer 2

首先按登录和每天计算总和。

然后计算每个登录的平均值。

SELECT
    login, AVG(SumDuration) AS AvgDuration
FROM
    (
    SELECT
        login, started_at::date, SUM(duration) AS SumDuration
    FROM log
    WHERE log.state NOT IN ('lunch', 'break1', 'break2')
    GROUP BY login, started_at::date
    ) AS T
GROUP BY login;

- Vladimir Baranov

---

抱歉，由于某些原因，我的服务器超时了。我正在尝试测试你们的答案，我会尽快回复你们。感谢迄今为止的回复。 - Luke Visinoni