PostgreSQL | Как получить по 3 записи на одно уникальное значение в столбце из таблицы?

Есть таблица:

-- Тестовая таблица
CREATE TABLE IF NOT EXISTS test.t_2022_12_15
(
  id bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
  username text COLLATE pg_catalog."default",
  randomcode text DEFAULT substr(md5(random()::text), 1, 5),
  CONSTRAINT t_2022_12_15_id_pkey PRIMARY KEY (id)
);

-- Вставляем тестовые данные
INSERT INTO test.t_2022_12_15 (username)
VALUES (unnest(ARRAY['Вася','Вася','Вася','Вася','Маша','Маша','Маша','Маша','Маша','Маша','Ян','Ян','Ян','Ян','Лена','Петя','Петя']));

-- Смотрим на результат вставки
SELECT * FROM test.t_2022_12_15;

Выглядит она так:

Таблица с повторяющимися именами на 3 столбца - PostgreSQL — Таблица с повторяющимися именами на 3 столбца — PostgreSQL

В таблице повторяются имена в столбце «username«.

Задача

Мы хотим получить часть записей этой таблицы, где каждое уникальное значение имени повторяется максимум 3 раза.

Нам нет нужды видеть все записи пользователя. Мы просто хотим понять о составах записей под каждого из них.

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

Задача частая в разных сферах.

Как выбрать несколько записей из каждой группы по условию в таблице PostgreSQL?

WITH qqqq AS (
   SELECT DISTINCT username AS fff FROM test.t_2022_12_15
)
SELECT test.t_2022_12_15.* FROM test.t_2022_12_15, qqqq
WHERE id IN (SELECT id FROM test.t_2022_12_15 WHERE username = qqqq.fff LIMIT 3);

Табличный вид:

Отобрали максимум по 3 записи на одно условие из таблицы PostgreSQL

По итогу мы получаем до трёх записей на каждый уникальный вид имени. Имя «Лена» было всего в одной записи из всей таблицы, поэтому мы получили все записи, где есть «Лена» — то есть одну запись. Похожая ситуация с именем «Петя». Он встречается всего в двух записях.

При этом столбцы переносятся все, как в оригинальной таблице.

Стоит обратить внимание на то, что в предложении FROM мы используем две таблицы:

test.t_2022_12_15
qqqq

Первая — это та, которую мы создавали.

Вторая — это сгенерированная таблица в один столбец из уникальных значений имён оригинальной таблицы.

Также важно отметить к каким именно столбцам мы обращаемся. В выражении «SELECT test.t_2022_12_15.*» мы используем только столбцы оригинальной таблицы и не используем столбец из qqqq.

В нашей задаче сортировка не важна. Главное получить до трёх записей на одно уникальное условие.

Но данный способ очень медленный, если данных в таблице очень много.

Функция для получения записей таблицы, где на каждое уникальное значение в условии мы получаем до трёх записей

Чтобы обегать большие таблицы, нужно выходить за пределы вложенных запросов и писать отдельную функцию.

CREATE OR REPLACE FUNCTION test.function_20221215()
RETURNS SETOF test.t_2022_12_15
LANGUAGE 'plpgsql'
AS $$
DECLARE
  -- Получаем массив уникальных имён из таблицы
  arr_uniq text[] := (SELECT ARRAY(SELECT DISTINCT username FROM test.t_2022_12_15));
  -- Получаем количество уникальных имён
  arr_uniq_length bigint := array_length(arr_uniq, 1);
  -- Устанавливаем стартовый шаг для работы цикла
  -- Он одновременно будет служить индексом элемента в массиве для обращения
  current_step bigint := 1;
  -- Количество отбираемых записей на уникальное значение
  max_records bigint := 3;
  -- Массив из будущих (итоговых) идентификаторов
  arr bigint[];
BEGIN
  LOOP -- Запускаем цикл по массиву
    IF current_step > arr_uniq_length THEN EXIT; END IF; -- обязательное условие выхода из цикла
    arr := arr || ARRAY(SELECT id FROM test.t_2022_12_15 WHERE username = arr_uniq[current_step] LIMIT max_records);
    current_step := current_step + 1;
  END LOOP;
  -- Возвращаем итоговый результат в виде таблицы
  RETURN QUERY SELECT * FROM test.t_2022_12_15 WHERE id IN (SELECT unnest(arr));
END;
$$

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

Мы просто складываем уникальные идентификаторы в один массив, а уже потом отбираем все записи по идентификаторам из таблицы.

Вызов функции:

SELECT * FROM test.function_20221215();

Результат вызова функции:

Получили таблицу из вызова функции в PostgreSQL

В написании функции мы использовали язык ‘plpgsql‘. Чтобы нам возвращать результат вычислений в виде таблицы, нам нужно прописывать выражение «RETURNS SETOF test.t_2022_12_15″ в блоке настроек функции, а уже в теле возвращать результат в виде множества через выражение «RETURN QUERY«.

Информационные ссылки

Официальный сайт WEB-оболочки pgAdmin — https://www.pgadmin.org

Официальный сайт СУБД PostgreSQL — https://www.postgresql.org

Команды SQL — https://postgrespro.ru/docs/postgresql/15/sql-commands

Функции для работы со строками — https://postgrespro.ru/docs/postgresql/15/functions-string