PostgreSQL | Какое значение чаще всего повторяется в столбце? — efim360.ru

PostgreSQL | Какое значение чаще всего повторяется в столбце?

У нас есть простая оригинальная таблица. SQL команды для её создания такие:

 

-- Тестовая таблица

CREATE TABLE IF NOT EXISTS
test.t_2022_11_16 (

    id bigint NOT NULL GENERATED ALWAYS AS IDENTITY (INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),

    username text,

    CONSTRAINT t_2022_11_16_id_pkey PRIMARY KEY (id)

);

 

-- Вставляем тестовые данные

INSERT INTO test.t_2022_11_16 (username) VALUES (unnest(ARRAY['M','M','S','S','S','F','F','F','F','F','F','F']));

-- Смотрим на результат вставки

SELECT * FROM test.t_2022_11_16;

 

 

Смотрим на оригинальную таблицу визуально:

Таблица с повторяющимися значениями в столбце, которые нужно посчитать - PostgreSQL
Таблица с повторяющимися значениями в столбце, которые нужно посчитать - PostgreSQL

Мы видим, что в столбце "username" существует всего три уникальных значения. Это "M", "S" и "F".

Мы хотим понять, какое значение чаще всего встречается (повторяется) в столбце "username". Как это сделать?

 

Логика решения задачи

В PostgreSQL существуют специальные функции, которые умеют работать с таблицами (с табличными столбцами). Они называются "агрегатные функции".

Одной из таких является агрегатная функция count(). Она подсчитывает общее количество передаваемых в неё значений. В качестве параметра мы передадим в неё столбец "username". По итогу функция всегда будет возвращать нам числовые значения.

Также нам понадобится группировка результатов по уникальным значениям из столбца "username". За это отвечает предложение GROUP BY для команды SELECT. Без группировки мы не сможем понять сколько раз повторяется какое-либо из значений.

 

-- Получаем таблицу с количеством повторений значений столбца

SELECT username, count(username) FROM test.t_2022_11_16 GROUP BY username;

 

 Результирующая таблица выглядит так:

Таблица с подсчитанным количеством повторяющихся значений - PostgreSQL
Таблица с подсчитанным количеством повторяющихся значений - PostgreSQL

Обратите внимание в каком виде PostgreSQL вернул результат. Это таблица без сортировки. Данные хаотично разбросаны. Причём нет никакой логики.

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

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

 

-- Сортируем результаты повторений в порядке убывания

SELECT username, count(username) FROM test.t_2022_11_16 GROUP BY username ORDER BY count DESC;

 

 После сортировки мы получаем более подходящий вариант таблицы:

Сортированная таблица после группировки и подсчёта количества уникальных значений - PostgreSQL
Сортированная таблица после группировки и подсчёта количества уникальных значений - PostgreSQL

В этой версии мы уже наглядно видим, что username со значением F встречается чаще всего.

Теперь нам нужно просто получить эту запись из результирующей таблицы.

 

-- Получаем запись с максимальным значением

SELECT username, count(username)

FROM test.t_2022_11_16

GROUP BY username

ORDER BY count DESC

LIMIT 1;

 

 

После этой манипуляции мы получаем:

Получили отобранную запись с максимальным значением - PostgreSQL
Получили отобранную запись с максимальным значением - PostgreSQL

Когда мы получим нужную нам запись, тогда сможем выдернуть из неё само значение из столбца username:

 

-- Вытаскиваем само значение из username

SELECT username FROM (SELECT username, count(username)

FROM test.t_2022_11_16

GROUP BY username

ORDER BY count DESC

LIMIT 1) AS t;

 

 Мы используем подзапрос в круглых скобках и даём ему имя "t".

Итог такой:

Получили самое пвторяющееся значение из username - PostgreSQL
Получили самое пвторяющееся значение из username - PostgreSQL

И в итоге мы получаем заветное значение "F" из ячейки. Задача решена. Самое повторяющееся значение в столбце таблицы найдено.

 

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

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

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

Раздел "Агрегатные функции" - https://postgrespro.ru/docs/postgresql/15/functions-aggregate

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

Команда INSERT - https://postgrespro.ru/docs/postgresql/15/sql-insert