intagg — агрегатор и нумератор целых чисел

примечание

Эта страница переведена при помощи нейросети GigaChat.

Модуль intagg предоставляет агрегатор и нумератор целых чисел. На данный момент intagg устарел, потому что есть встроенные функции, которые предоставляют более широкие возможности. Однако модуль все еще поставляется для обратной совместимости.

Функции

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

Нумератор – это функция int_array_enum(integer[]), которая возвращает setof integer. По сути, это обратная операция агрегата: данная целочисленная матрица расширяется до набора строк. Это оболочка вокруг unnest, который делает то же самое для любого типа массива.

Примеры использования

Многие системы баз данных имеют понятие таблицы «один ко многим». Такая таблица обычно находится между двумя индексированными таблицами, например:

CREATE TABLE left_table  (id INT PRIMARY KEY, ...);
CREATE TABLE right_table (id INT PRIMARY KEY, ...);
CREATE TABLE many_to_many(id_left  INT REFERENCES left_table,
                          id_right INT REFERENCES right_table);

Обычно это используется следующим образом:

SELECT right_table.*
FROM right_table JOIN many_to_many ON (right_table.id = many_to_many.id_right)
WHERE many_to_many.id_left = item;

Запрос вернет все элементы в правой таблице для записи в левой таблице. Это очень распространенная конструкция в SQL.

Однако эта методология может быть громоздкой с очень большим количеством записей в таблице many_to_many. Часто такое соединение приводит к сканированию индекса и выборке для каждой записи справа в таблице для конкретной записи слева. Если система очень динамичная, то мало что можно с этим сделать. Однако если есть некоторые данные, которые являются довольно статичными, то можно создать сводную таблицу с агрегатором.

CREATE TABLE summary AS
  SELECT id_left, int_array_aggregate(id_right) AS rights
  FROM many_to_many
  GROUP BY id_left;

Это создаст таблицу с одной строкой для каждого элемента слева и массивом элементов справа. Теперь это совершенно бесполезно без какого-либо способа использования массива, вот почему существует нумератор массивов. Можно сделать:

SELECT id_left, int_array_enum(rights) FROM summary WHERE id_left = item;

Запрос выше, используя int_array_enum, дает те же результаты, что и:

SELECT id_left, id_right FROM many_to_many WHERE id_left = item;

Разница заключается в том, что запрос к сводной таблице должен получить только одну строку из таблицы, тогда как прямой запрос к many_to_many должен выполнить сканирование индекса и извлечь строку для каждой записи.

В тестовой системе команда EXPLAIN показала что стоимость запроса 8488 была уменьшена до 329. Исходный запрос представлял собой соединение, включающее таблицу many_to_many, которая была заменена:

SELECT id_right, count(id_right) FROM
  ( SELECT id_left, int_array_enum(rights) AS id_right
    FROM summary
    JOIN (SELECT id FROM left_table
          WHERE id = item) AS lefts
    ON (summary.id_left = lefts.id)
  ) AS list
  GROUP BY id_right
  ORDER BY count DESC;