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屋!