Групповая обработка данных с использованием window-функций first_value/last_value

Часто бывает необходимо обработать список объектов, сгруппированных по определенному признаку. И сохранить результат обработки группы.

К примеру, имеется список сотрудников, сгруппированных по отделам. Необходимо просуммировать полезную работу сделанную сотрудниками каждого отдела и записать результат в отдельную таблицу.

Список сотрудников:

SELECT * FROM employee;

 department_id | employee_id
---------------+-------------
             3 |        1002
             4 |        1006
             2 |        1001
             4 |        1003
             2 |        1004
             2 |        1005
(6 rows)

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

# Обнуление переменных для агрегации данных
department_id = None
useful_effect = 0

# Цикл обхода сотрудников, упорядоченных по отделу
for employee in employee_list(order by department_id):
    if employee.department_id <> department_id:
        # В цикле появился новый отдел. Надо сохранить данных, накопленные по "старому" отделу
        if department_id is None:
            save_useful_effect(department_id, useful_effect)
        # Сохранить информацию о текущем обрабатываемом отделе
        department_id = employee.department_id
        # Обнулить полезную информацию об отделе
        useful_effect = 0
    # Накапливаю полезную информацию по отделу
    useful_effect = useful_effect + calc_useful_effect(employee.id)

# После цикла надо проверить, что остались необработанные данные и сохранить их, если они есть
if department_id is not None:
    save_useful_effect(department_id, useful_effect)

Приведенный псевдокод достаточно понятен и прямолинеен. Можно ли его сделать ещё более "прямым" и понятным? Можно!

Проблема данного кода состоит в том, что надо постоянно искать место смены группы, чтобы сохранить накопленную информацию о предыдущей группе. Кроме того, в коде есть повторяющийся блок кода, который сохранять накопленную информацию:

if department_id is not None:
    save_useful_effect(department_id, useful_effect)

Хорошо, если код представляет из себя одну строку вызова какой-нибудь процедуры. А если это несколько строк кода или даже их больше десятка и их нельзя вынести в отдельную процедуру. Возникает проблема копипаста.

Проблему можно решить, если первый и последний сотрудник в отделе будут имеет соответствующие признаки first_in_department/last_in_department. В этом случае псевдокод будет таким:

# Цикл обхода сотрудников, упорядоченных по отделу
for employee in employee_list(order by department_id):
    if employee.first_in_department:
        # Обнулить полезную информацию об отделе
        useful_effect = 0

    # Накапливаю полезную информацию по отделу
    useful_effect = useful_effect + calc_useful_effect(employee.id)

    if employee.last_in_department:
        save_useful_effect(employee.department_id, useful_effect)

Думаю, последний блок псевдокода получился во всех отношениях лучше, чем предыдущий. Как этого можно добиться?

Для этого отлично подойдут window-функции first_value/last_value c группировкой по department_id. Для получения признаков first_in_department / last_in_department использовалось сравнение результатов window-функции по полю employee_id и текущего значения этого поля.

Вот пример запроса и полученный результат.

SELECT *,
  LAST_VALUE(employee_id) OVER (PARTITION BY department_id) = employee_id AS last_in_department,
  FIRST_VALUE(employee_id) OVER (PARTITION BY department_id) = employee_id AS first_in_department
FROM employee;

 department_id | employee_id | last_in_department | first_in_department
---------------+-------------+--------------------+---------------------
             2 |        1001 |    False           |      True              -- Первый сотрудник в отделе 2
             2 |        1004 |    False           |      False
             2 |        1005 |    True            |      False             -- Последний сотрудник в отделе 2
             3 |        1002 |    True            |      True              -- Первый и последний сотрудник в отделе 3
             4 |        1006 |    False           |      True              -- Первый сотрудник в отделе 4
             4 |        1003 |    True            |      False             -- Последний сотрудник в отделе 4
(6 rows)

Этот результат можно смело обрабатывать, используя, как шаблон, последний приведенный пример кода.

Предупреждение

Если в тексте запроса есть сортировка, то результат выборки может быть неожиданным, так как PARTITION переупорядочивает поля, группируя их про заданным признакам (в приведенном примере по полю department_id), а дополнительная сортировка может нарушить эту группировку, изменив положение вычисленных полей.

Дополнительная информация

PostgreSQL Documentation: Window Functions

comments powered by Disqus