После того, как вы связали все таблицы своей базы вместе, настало время написать пару запросов. Хотя пару - это же для новичков. Вы уже профессионал, поэтому вам будет нужно написать 50(!) запросов к вашей базе денных. И это только самые нужные.
Запросы к базе данных
1. Получение списка товаров для витрины
Запрос возвращает все активные товары с их основной ценой и изображением для отображения на главной странице и в каталоге. Это позволяет быстро формировать витрину и поддерживать актуальность информации о товарах.
2. Поиск товаров по ключевому слову
Позволяет пользователям находить интересующие их товары по совпадению в названии или описании. Это важная часть пользовательского функционала для быстрого поиска по каталогу.
3. Карточка товара по ID
Возвращает расширенную информацию по конкретному товару, включая бренд и категорию. Необходимо для отображения страницы товара в деталях.
4. Список вариантов товара
Выводит все доступные варианты (SKU) товара: размеры, цвета, остатки, цены. Используется для выбора нужной модификации на странице товара.
5. Галерея изображений товара
Для полноценного отображения карточки товара важны все его фотографии. Запрос возвращает их с указанием главного изображения.
6. Средний рейтинг и число отзывов о товаре
Используется для отображения оценки товара и количества отзывов, что важно для репутации и доверия покупателей.
7. Детальный список отзывов к товару
Для раздела отзывов в карточке товара: рейтинг, текст, автор и дата отзыва. Помогает новым покупателям принять решение о покупке.
8. Вопросы и ответы по товару
Запрос для получения вопросов с ответами по каждому товару, что важно для блока Frequently Asked Questions на карточке товара.
9. Категории товаров с иерархией
Позволяет визуализировать структуру каталога, строить навигационное дерево для фильтров и меню.
10. Товары по категории и подкатегориям
Помогает вывести все товары из выбранной категории или её "дочерних" категорий (уровень вложенности).
11. Список брендов
Для фильтрации по брендам, создания бренд-листинга и лендингов.
12. Популярные теги и их количество товаров
Анализирует наиболее используемые теги для отображения трендовых товаров и построения облака тегов.
13. История изменения цен по товару
Для аналитики и отображения динамики цен (старая/новая цена, акции).
14. История изменения статуса товара
Позволяет отследить жизненный цикл товара, причину исчезновения с витрины или возврата.
15. Поиск по сертификатам и лицензиям
Критически важно для профессиональных покупателей и B2B сегмента (качество и законность продукции).
16. Данные о поставщиках товара
Важно для администрирования, контроля качества и связи с поставщиками.
17. Остатки товара по складам
Контроль и учёт актуальных остатков по складам. Необходимо для системы логистики и предотвращения "out of stock".
18. Товары с запасом ниже порога
Автоматизация пополнения склада, предотвращение потери продаж из-за отсутствия товара.
19. Движение товара по складу (аудит)
Отслеживание всех движений товара за выбранный период: поступления, списания и коррекции, что важно для инвентаризации и предотвращения потерь.
20. Логистика перемещений между складами
Позволяет видеть историю и статус внутренних перемещений товара между логистическими центрами.
21. Доставка: способы и тарифы
Для расчёта стоимости доставки и информирования пользователя при оформлении заказа.
22. История заказов пользователя
Важнейшая часть личного кабинета — все совершённые заказы, их статус и сумма.
23. Детали заказа с позициями
Позволяет получить полную структуру заказа — состав, цены, количество — для отображения на фронте или для поддержки.
24. Отчёт по заказам за период и по статусу
Аналитика и отчётность по продажам, возвращает заказы по периоду и нужному статусу (например, "завершён").
25. "Покинутые" корзины
Аналитика для маркетологов: корзины, по которым пользователь не оформил заказ — потенциально для ретаргетинга.
26. Топ продаж
Аналитика для блока "Хиты продаж" и маркетинговых подборок: какие товары чаще всего покупают.
27. Продажи по дням (для графиков)
Отчёт по дневной выручке — основа для анализа динамики бизнеса и построения графиков.
28. Список возвратов
Отображает возвраты по всем заказам с причиной и статусом, что помогает анализировать причины возвратов.
29. Список отмен заказов
Контроль потерь и причин отмен: отображает отмены с указанием причины, кто отменил и когда.
30. Заказы, ожидающие отправки
Для склада и службы доставки — заказы, которые нужно укомплектовать и отправить, с деталями по доставке.
31. Средний чек
Показатель "Average Order Value" — ключевая метрика для оценки эффективности маркетинга и ассортимента.
32. Заказы с применением промокодов
Аналитика эффективности акций: какие промокоды применялись и насколько часто.
33. Использование скидок по категориям и брендам
Позволяет оценить, какие акции работают, и мониторить популярность скидок по категориям товаров и брендам.
34. Применённые промокоды и их пользователи
Контроль за использованием промокодов, выявление аномалий и злоупотреблений.
35. История платежей по заказу
Для поддержки и бухгалтерии: отображает все транзакции оплаты, их статусы и применяемые платёжные методы.
36. Заказы с возвратом средств
Для анализа возвратов, генерации бухгалтерских отчётов и предотвращения мошенничества.
37. Баланс кошелька пользователя и история транзакций
Контроль и отображение бонусных или кэшбэк-средств пользователя, история их движения.
38. Заявки пользователя в поддержку
Позволяет пользователю видеть свои обращения и статусы их рассмотрения.
39. SLA-аналитика по заявкам поддержки
Анализирует среднее время ответа и решения проблем по каждому приоритету, что важно для контроля SLA.
40. Сообщения по заявке в поддержку
Позволяет видеть всю переписку по выбранной заявке, что важно для пользователя и поддержки.
41. Активные FAQ по категориям
Выводит часто задаваемые вопросы для базы знаний клиента, помогает снизить нагрузку на поддержку.
42. Активные маркетинговые кампании и баннеры
Для отображения актуальных рекламных предложений на сайте.
43. Избранные товары на главной странице
Для блока "Избранное": товары, которые нужно акцентировать на главной.
44. История A/B тестов
Анализ проведённых экспериментов для оптимизации UX и маркетинга.
45. История просмотров товара конкретным пользователем
Отображает "Вы смотрели" или используется для персонализированных рекомендаций.
46. Популярные поисковые запросы пользователей
Анализ спроса пользователей, помогает оптимизировать поиск и подсказки.
47. Аналитика по источникам трафика
Позволяет оценить, какие рекламные каналы приводят трафик и конверсии.
48. Удержание пользователей по когортам
Ключевая метрика для оценки лояльности и повторных покупок.
49. Новости/статьи для главной
Для вывода новостей и статей в блогах, повышения вовлечённости пользователей.
50. Активные страницы сайта и связанные блоки контента
Для проверки целостности контента сайта, работы CMS и отображения данных на страницах.`
Добавляем индексы
Запросы – это конечно хорошо, но только если они быстро работают. Поэтому вам нужно будет добавить немножко индексов в вашу базу. Вам следует добавить к основным таблицам проекта 40 индексов для повышения производительности запросов и удобства эксплуатации.
1. Индекс на product.product(status)
Почти все запросы к товарам фильтруются по статусу (например, активные товары для витрины, поиска и пр.). Индекс ускоряет выборку товаров с определённым статусом, минимизируя сканирование всей таблицы.
2. Индекс на product.variant(product_id, is_active)
Запросы к вариантам товара (SKU) и для витрины используют фильтрацию по связи с товаром и по активности варианта. Данный составной индекс позволит оптимально выбирать все активные варианты конкретного товара.
3. Индекс на product.image(product_id, is_main DESC)
Для получения главного изображения товара (или всего списка) используется фильтрация по товару и сортировка по признаку “главное”. Индекс ускоряет такие выборки и обеспечивает быструю отдачу данных для галерей.
4. Индекс на product.product(name text_pattern_ops)
Для быстрого поиска товаров по ключевому слову в названии через выражение ILIKE '%...%'. Специализированный индекс на выражении name text_pattern_ops улучшает работу поиска по подстроке, особенно на больших объёмах.
5. Индекс на product.product(description gin_trgm_ops)
Аналогично п.4 — поиск по описанию товара (поиск ILIKE или полнотекстовый). GIN-индекс с триграммами ускорит фильтрацию по текстовым полям.
6. Индекс на product.product(category_id)
Часто идет выборка по категории или по прямым/дочерним категориям (см. запросы фильтрации по категориям каталога). Индекс позволяет быстро находить все товары заданной категории.
7. Индекс на product.category(parent_id)
Для построения иерархии категорий и визуализации дерева навигации часто делаются выборки по parent_id. Индекс ускорит эти рекурсивные иерархические запросы.
8. Индекс на product.review(product_id)
Все обращения к отзывам на товар фильтруются по product_id (как для среднего рейтинга, так и для вывода списка отзывов). Индекс на этом поле сделает агрегацию и выборку отзывов значительно быстрее.
9. Индекс на product.review(product_id, created_at DESC)
Для быстрого получения последних отзывов о товаре (ORDER BY createdat DESC), особенно вместе с фильтрацией по productid, помогает составной индекс.
10. Индекс на product.question(product_id, created_at DESC)
Популярный запрос на ответы по конкретному товару, с сортировкой по времени создания. Индекс покрывает оба условия и ускоряет вывод секции Q&A в карточке товара.
11. Индекс на product.answer(question_id, created_at)
Для поиска ответов на вопросы по товару нужен быстрый доступ по внешнему ключу question_id, часто с сортировкой по дате. Данный индекс минимизирует задержку при генерации Q&A.
12. Индекс на product.price_history(variant_id, changed_at DESC)
История изменения цен быстро извлекается по варианту товара и по недавним изменениям. Такой индекс ускоряет аналитические запросы по динамике цен и “старая/новая цена”.
13. Индекс на product.status_history(product_id, changed_at DESC)
Выборка истории изменения статусов по товару с сортировкой по времени востребована для аудита и контроля жизненного цикла товаров. Составной индекс значительно ускоряет такие запросы.
14. Индекс на product.certificate(product_id)
Поиск сертификатов товара по его id – типичная операция для B2B и сертифицированных витрин. Индекс ускоряет такие проверки.
15. Индекс на product.license(product_id)
Для поиска лицензий по товарам, особенно в запросах с фильтрацией по типу лицензии.
16. Индекс на product.product_tag(tag_id)
Частый запрос — получить все товары по определённому тегу (и обратное). Индекс позволяет быстро пересекать товары и теги для облака тегов или фильтров.
17. Индекс на product.product_tag(product_id)
Позволяет быстро определить, какие теги привязаны к конкретному товару, ускоряя подборку по тегам.
18. Индекс на logistics.inventory(product_id, warehouse_id)
Для мгновенного доступа к остаткам товара на складе (или для расчёта по всем складам) — критично для логистики, проверки stock level и витрин в реальном времени.
19. Индекс на logistics.inventory(variant_id)
Для учёта запасов по конкретному варианту товара (цвет/размер) и для сквозных отчётов.
20. Индекс на logistics.stock_level(product_id, warehouse_id)
Быстрая проверка минимального порога для товара на складе (например, для автозаказа или сигнализации о низком уровне). Такой индекс нужен для сравнения с inventory.
21. Индекс на logistics.inventory_movement(product_id, changed_at DESC)
Позволяет быстро получать историю перемещений товара (аудит) за последние периоды — полезно для предотвращения ошибок, анализа потерь и контроля поставок.
22. Индекс на logistics.transfer(product_id, requested_at DESC)
Для анализа логистики перемещений между складами, фильтрации по товару и сортировке по дате запроса.
23. Индекс на logistics.shipping_rate(shipping_method_id, destination_zone)
При расчёте стоимости доставки часто выбирается тариф по id способа и зоне назначения. Индекс ускоряет вычисления для расчёта клиента при оформлении заказа.
24. Индекс на "order".order(user_id, placed_at DESC)
Все обращения к истории заказов пользователя используют фильтрацию по user_id и сортировку по дате оформления. Составной индекс обеспечивает быструю отдачу истории заказов для личного кабинета.
25. Индекс на "order".order(status, placed_at)
Для аналитики и отчётов по заказам за периоды, а также поиска по статусу (например, "в обработке"/"завершено").
26. Индекс на "order".order_item(order_id)
Извлечение всех позиций заказа по id заказа – одна из самых частых операций для детализации заказов.
27. Индекс на "order".order_item(product_id)
Аналитика продаж и статистика по товарам требуют быстрых выборок позиций заказов по id товара.
28. Индекс на "order".return(order_id)
Связь возвратов с заказами используется для поддержки и аналитики возвратов. Индекс ускоряет поиск возвратов по номеру заказа.
29. Индекс на "order".cancellation(order_id)
Аналогично возвратам — ускоряет выявление отмен заказа для аналитики и поддержки.
30. Индекс на "order".cart(user_id, updated_at DESC)
Для поиска последних корзин пользователя (например, поиски “брошенных” корзин), удобно иметь индекс по user_id с сортировкой по дате последнего обновления.
31. Индекс на payment.payment_transaction(order_id)
Большинство запросов по истории платежей используют фильтрацию по конкретному заказу. Индекс обеспечивает моментальный доступ к транзакциям заказа.
32. Индекс на payment.refund(transaction_id)
Позволяет эффективно находить возвраты по конкретной транзакции для поддержки, отчётности и контроля мошенничества.
33. Индекс на payment.wallet(user_id)
Быстрый доступ к кошельку пользователя для проверки баланса и истории операций.
34. Индекс на payment.wallet_transaction(wallet_id, created_at DESC)
Выборка по транзакциям кошелька пользователя с сортировкой по дате (например, отображение истории операций).
35. Индекс на support.support_ticket(user_id, created_at DESC)
История обращений конкретного пользователя в поддержку (личный кабинет/клиентский сервис). Составной индекс оптимизирует такие выборки.
36. Индекс на support.ticket_message(ticket_id, sent_at)
Для вывода всей переписки по тикету удобно иметь индекс по тикету и дате – это ускоряет сортировку сообщений по времени.
37. Индекс на support.ticket_sla_tracking(ticket_id)
Для SLA-аналитики и контроля по каждому тикету, быстрый доступ к SLA-данным достигается за счет индекса по ticket_id.
38. Индекс на marketing.promo_usage(user_id, used_at DESC)
Для анализа активности пользователей по промокодам (и аналитики, и защиты от злоупотреблений), требуется быстрый поиск по user_id с сортировкой по времени.
39. Индекс на analytics.product_view(user_id, viewed_at DESC)
Хранение и анализ истории просмотров товаров пользователем (персонализация, рекомендации) требует быстрого доступа по user_id с сортировкой по времени просмотра.
40. Индекс на analytics.search_query_log(query_text)
Популярные запросы и частота их использования – ключевой аналитический инструмент поиска. Индекс ускоряет агрегации и подсчёты по тексту запроса.
Примечание
Для текстовых поисков по ILIKE рекомендуется использовать GIN-индексы с расширением pg_trgm, которые эффективны для поиска подстрок и нечёткого поиска. Для больших таблиц с агрегацией по датам или сортировкой по дате рекомендуется индекс DESC на дате — это ускоряет выборку последних записей.
Имеет смысл настраивать индексы согласно реальным планам выполнения и статистике нагрузки, но перечисленные выше индексы покрывают основные производственные сценарии запросов к нашему маркетплейсу.
Добавляем функции
Вы еще не устали? Тогда давайте напишем еще несколько функцию чтобы упростить написание наших существующих и будущих запросов. Так сказать для ускорения реализации ключевых запросов, снижения дублирования кода в приложении и централизации бизнес-логики на стороне базы данных
1. Поиск товаров по ключевому слову с учетом тегов и брендов
Зачем нужна:
Обычный поиск по названию и описанию ограничен. Часто нужно искать товары также по тегам и брендам. Универсальная функция централизует логику расширенного поиска, уменьшает дублирование кода и упрощает интеграцию с фронтендом.
2. Получение полной карточки товара по ID (все данные для карточки)
Зачем нужна:
На фронте часто требуется сразу вся информация по товару: основные поля, бренд, категория, изображения, теги, атрибуты, средний рейтинг и количество отзывов. Функция формирует полную карточку товара одним вызовом, сокращая число обращений к БД.
3. Получение иерархии категорий с вложенностью
Зачем нужна:
Построение дерева (или пути) категорий требуется для витрины, фильтров и breadcrumbs. Вместо рекурсивных запросов в клиентском коде функция возвращает всю иерархию разом.
4. Расчет средней цены и минимального/максимального значения по категории
Зачем нужна:
Для фильтров в каталоге и аналитики удобно получать агрегированную статистику по товарам в категории: диапазон цен, среднее значение. Функция избавляет от повторяющихся подзапросов.
5. Проверка и автоматический подсчет остатков товара по всем складам
Зачем нужна:
Позволяет моментально узнать общий остаток по товару (и по каждому варианту), что полезно для витрины, склада и логистики. Централизует расчёт, предотвращая дублирование бизнес-логики.
6. Получение истории заказов пользователя с деталями
Зачем нужна:
Функция возвращает список заказов пользователя, в т.ч. позиции заказа, суммы, статусы, позволяя фронтенду получать историю одним вызовом и сразу строить кабинет пользователя.
7. Получение средней оценки пользователя как продавца/покупателя
Зачем нужна:
Для отображения доверия и репутации пользователя на платформе важно знать его средний рейтинг как продавца или покупателя. Функция делает агрегатный расчёт.
8. Использование промокода пользователем (валидатор со всеми условиями)
Зачем нужна:
Вся бизнес-логика проверки и списания промокода (активность, лимиты, дата и т.д.) централизована в одной функции. Это упрощает логику приложения и защищает от ошибок при дублировании условий.
9. Универсальная функция логирования пользовательских событий
Зачем нужна:
Для сквозной аналитики и аудита централизованное логирование событий снижает дублирование кода и снижает риск потери данных о действиях пользователей.
10. Функция получения баланса бонусного кошелька и суммы начислений за всё время
Зачем нужна:
Один вызов позволяет сразу получить текущий баланс пользователя и сумму суммарных начислений по кошельку. Это удобно для отображения на дашборде и уменьшает количество SQL-запросов.
11. Универсальная функция смены статуса заказа с логированием
Зачем нужна:
Меняет статус заказа, добавляет запись в лог истории статусов и минимизирует ошибки при изменении статусов в разных частях приложения.
12. Получение всех сообщений диалога поддержки (тикет + все сообщения)
Зачем нужна:
Функция возвращает всю переписку по тикету, включая детали заявки и каждое сообщение. Это облегчает построение истории тикета на фронте.
13. Проверка существования пользователя по email или телефону
Зачем нужна:
Используется для регистрации и восстановления пароля, предотвращает дублирование логики на фронте и бэке.
Примечание
Этот набор функций покрывает ключевые сценарии бизнеса, повышает удобство работы с данными, оптимизирует логику и ускоряет разработку фронтенда и интеграций. Надеюсь вам понравилось :)
Файлы с решением
ПЕРЕЙДИТЕ В ПОЛНУЮ ВЕРСИЮ