Блог

Я читаю ЭТО уже много лет, но это мой первый пост. Надеюсь, кто-нибудь сможет помочь мне решить эту проблему.

Я новичок в оконных функциях, но, насколько я понимаю, это то, что я ищу. У меня есть 3 таблицы пользователей, задач и пользователей задач. Одному или нескольким пользователям может быть назначена задача (через task_users). То, что я хотел бы видеть, — это таблица, показывающая следующее:

идентификатор
пользователя полное имя
пользователя сколько задач было выдано этому пользователю (вхождений)
средняя продолжительность всех задач, выданных этому пользователю (average_duration)

Текущий метод, который я использую для извлечения продолжительности для одной задачи, таков:

ИЗВЛЕЧЕНИЕ(ЭПОХА из closed_at) — ИЗВЛЕЧЕНИЕ(ЭПОХА из started_at)/3600 КАК продолжительность

Вот столбцы, представляющие интерес в каждой таблице:

Пользователи
идентификатор
фамилия имя
отчество

задачи
идентификатор
started_at (метка времени без tz)
closed_at (метка времени без tz)

пользователи задач
идентификатор задачи (ссылки tasks.id)
идентификатор пользователя (ссылки users.id)

Используя приведенный ниже sql, я могу сгенерировать таблицу, показывающую каждого пользователя, его идентификатор и количество задач, отправленных этому пользователю:

 SELECT 
    users.id AS u_id,
    concat(users.last_name, ', ', users.first_name) AS u_name, 
    COUNT(*) AS occurrences
FROM tasks
INNER JOIN task_users ON task_users.task_id = tasks.id
INNER JOIN users ON users.id = task_users.user_id
WHERE tasks.closed_at IS NOT NULL 
GROUP BY u_id
ORDER BY occurrences DESC
 

Этот запрос показывает:

 ----------------------------------
id    u_name           occurrences
----------------------------------
1  |  Mike Smith     | 10
2  |  Dave Johnson   | 5
3  |  George Wilson  | 3
etc...
 

Я хотел бы создать ту же таблицу, что и выше, но со средней продолжительностью (часы, которые потребовались для выполнения каждой задачи) для всех задач, отправленных каждому пользователю. Что-то вроде следующего:

 ------------------------------------------------------
id    u_name           occurrences    average_duration
------------------------------------------------------
1  |  Mike Smith     | 10           | 32.7
2  |  Dave Johnson   | 5            | 15.2
3  |  George Wilson  | 3            | 10.0
etc...
 

Я попробовал следующую функцию подзапроса и окна, но она разбивает пользователей на несколько строк (количество строк, отображаемых для каждого пользователя, равно вхождениям).

 SELECT 
    users.id AS u_id,
    concat(users.last_name, ', ', users.first_name) AS u_name, 
    COUNT(*) AS occurrences,
    AVG(tsk.duration) OVER(PARTITION BY users.id) AS average_duration
FROM 
    (SELECT id, (EXTRACT(EPOCH from closed_at) - EXTRACT(EPOCH from started_at)/3600) AS duration FROM tasks) tsk
INNER JOIN task_users ON tsk.id = task_users.task_id
INNER JOIN users ON users.id = task_users.user_id
 

Я новичок в оконных функциях и не гуру SQL, но мне кажется, что оконная функция-лучшее решение?

Если кто-нибудь может указать мне правильное направление или предложить какое-либо предложение, я был бы очень признателен.

Спасибо!

Функция окна предоставит значение для каждой строки. В вашем сценарии у пользователя несколько задач, и поэтому объединение приведет к созданию нескольких строк для каждого пользователя.

Вы можете попробовать следующий подход, который изменяет ваш первоначальный подход, группирующий данные по пользователям:

 SELECT 
    users.id AS u_id,
    concat(users.last_name, ', ', users.first_name) AS u_name, 
    COUNT(*) AS occurrences,
    SUM(
        EXTRACT(EPOCH from closed_at) - EXTRACT(EPOCH from started_at)/3600
    ) / COUNT(*) as average_duration
FROM tasks
INNER JOIN task_users ON task_users.task_id = tasks.id
INNER JOIN users ON users.id = task_users.user_id
WHERE tasks.closed_at IS NOT NULL 
GROUP BY u_id
ORDER BY occurrences DESC
 

или

 SELECT 
    users.id AS u_id,
    concat(users.last_name, ', ', users.first_name) AS u_name, 
    COUNT(*) AS occurrences,
    AVG(
        EXTRACT(EPOCH from closed_at) - EXTRACT(EPOCH from started_at)/3600
    ) as average_duration
FROM tasks
INNER JOIN task_users ON task_users.task_id = tasks.id
INNER JOIN users ON users.id = task_users.user_id
WHERE tasks.closed_at IS NOT NULL 
GROUP BY u_id
ORDER BY occurrences DESC
 

Дайте мне знать, если это сработает для вас.