sql - PostgreSQL How to get average of sum of certain rows

Question

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!

标签： sqlpostgresql

Answer 1

Calculate sum per login and per day first.

Then calculate average per login.

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;