PostgreSQL 如何获得某些行的总和的平均值 [英] PostgreSQL How to get average of sum of certain rows

问题描述

如果标题不太恰当，我深表歉意，但我在制定简洁的标题时遇到了一些麻烦.无论如何，我有一个表格记录了一个人所处的状态.它看起来像:

<前>id、登录、状态、持续时间、started_at1, pdiddy, 工作, 1200, 2018-05-25 08:30:002, pdiddy, 午餐, 120, 2018-05-25 9:00:003, pdiddy, on_call, 65, 2018-05-25 12:30:004, pdiddy, 可用, 1115, 2018-05-25 12:30:005, pdiddy, 工作, 143, 2018-05-25 12:30:006, pdiddy, break1, 150, 2018-05-25 12:30:007, pdiddy, 工作, 2400, 2018-05-25 12:30:008, pdiddy, break2, 110, 2018-05-25 12:30:00

我只需要获取每个用户每天与劳动相关的持续时间的平均值.因此，基本上我需要将任何一天的午餐"、休息 1"和休息 2"以外的其他所有时间的持续时间相加，并得到它的平均值.

我尝试这样做，但问题在于它没有在平均之前将与人工相关的日志相加.我不知道该怎么做.

SELECT登录，登录，AVG(log.duration) FILTER (WHERE log.state NOT IN ('lunch', 'break1', 'break2')) AS "labor_average"从日志按 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从日志 lGROUP BY l.login

I apologize if the title isn't very apt, but I had a little trouble formulating a concise title. Anyway, I have a table that keeps a log of states a person is in. It looks like:

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

I need to get an average for only labor-related durations on a day-by-day basis for each user. So basically I need to add up the durations for everything other than "lunch", "break1", and "break2" for any given day and get the average for it.

I tried to do this like this, but the problem with this is that it doesn't add up the labor-related logs before averaging. I can't figure out how to do that.

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

Obviously I don't expect anybody to just do this for me. I just need to be pointed in the right direction. I'm obviously quite a ways from the solution, so I just need a push in the right direction. Thank you very much in advance!

解决方案

I think you want the total sum divided by the number of days:

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

这篇关于PostgreSQL 如何获得某些行的总和的平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！