Подсчет строк связанной таблицы в SELECT PostgreSQL 9

Быстрый ответ

SELECT
  id,
  (SELECT COUNT(*) FROM related_table WHERE related_table.foreign_key = main_table.id) AS related_count
FROM main_table;

Этот код описывает использование коррелированного подзапроса с функцией COUNT для подсчёта количества актуальных записей в другой таблице во время работе SELECT запроса в Postgres.

Коррелированный подзапрос в сравнении с JOIN

Такой вариант как коррелированный подзапрос может прийти в голову, когда подсчёт связанных записей встаёт как задача. Но использование JOIN может быть не менее эффективным:

SELECT a.id, COUNT(b.id) AS related_count
FROM main_table a
LEFT JOIN related_table b ON a.id = b.foreign_key
GROUP BY a.id;

Использование LEFT JOIN позволяет учесть и те записи из main_table, для которых нет соответствующих в related_table, присваивая им значение счётчика равное нулю. Учтите, что применение GROUP BY необходимо для корректной работы с агрегатными функциями при выборке данных.

Оптимизация производительности

Перформанс играет важную роль. Подзапросы удобны для работы с небольшими объемами данных, но при их возрастании могут замедлить вашу базу данных. Стоит упомянуть про индексацию для ускорения операций подсчёта. Обеспечьте индексацию foreign_key в related_table для повышения производительности.

Подробнее об этом расскажет наш спикер на видео

Особенности работы с чтением и записью

Если ваша база данных нацелена на интенсивное чтение и часто требует подсчётов, то следует обратить внимание на триггеры-кэши. Триггерная функция может обновлять специальный столбец счетчика в main_table при изменениях в related_table.

CREATE OR REPLACE FUNCTION update_count()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE main_table
  SET related_count = related_count + 1
  WHERE id = NEW.foreign_key;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_related_count
AFTER INSERT ON related_table
FOR EACH ROW EXECUTE FUNCTION update_count();

Визуализация

Давайте рассмотрим пример со столами в двух столовых A и B:

Ваш стол (Столовая A) 🍽️: [Вы, Друг 1, Друг 2]
Соседняя столовая (Столовая B) 🚪🍴: ???

Сколько гостей в столовой B?

SELECT a.*, 
       (SELECT COUNT(*) FROM DiningRoomB b WHERE b.table_id = a.id) AS GuestCountInRoomB
FROM DiningRoomA a;

Результаты запроса:

🍽️ Ваш стол (1) | 🚪👥 Количество гостей в столовой B (7)
🍽️ Ваш стол (2) | 🚪👥 Количество гостей в столовой B (4)
🍽️ Ваш стол (3) | 🚪👥 Количество гостей в столовой B (5)

Этот запрос эффективно подсчитывает количество связанных записей, словно вы, не вставая со своего стола, пытаетесь узнать количество людей в столовой B.

Работа с большими объёмами данных

При работе с большими объёмами данных, важно уделять внимание оптимизации:

• Фильтрация перед подсчётом: Воспользуйтесь условием WHERE для исключения ненужных записей до подсчёта.
• Сортировка и фильтрация агрегатных данных: Сортировка через ORDER BY и фильтрация с помощью HAVING могут быть полезными, однако они также могут увеличить нагрузку на систему.
• Совместимость с версией PostgreSQL: Убедитесь, что все используемые функции поддерживаются вашей версией PostgreSQL.

ORM в веб-фреймворках

Веб-разработчикам работающим на фреймворках с ORM, например Django, доступны встроенные методы для аннотации запросов с информацией о количестве записей из связанных таблиц:

from django.db.models import Count
queryset = MainTable.objects.annotate(related_count=Count('related_table'))

Профилирование запросов

Для анализа выполнения запросов используйте EXPLAIN. Этот инструмент профилирования позволяет увидеть, не читает ли запрос больше данных, чем требуется и эффективно ли используется индексация.

Полезные материалы

1. PostgreSQL: Документация по SELECT — Официальное руководство PostgreSQL по подзапросам.
2. PostgreSQL: Документация по агрегатным функциям — Информация об функции COUNT и о других агрегатных функциях в PostgreSQL.
3. PostgreSQL – использование JOIN на Tutorialspoint — Обзор применения JOIN в запросах PostgreSQL.
4. Join против подзапроса на Stack Overflow — Дискуссия о эффективности и применении JOIN и подзапросов в сообществе Stack Overflow.