Після того, як ти зв'язав усі таблиці своєї бази разом, настав час написати пару запитів. Хоча пару — це ж для новачків. Ти вже профі, тому тобі треба буде написати 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 або телефону
Навіщо треба:
Використовується для реєстрації і відновлення паролю, запобігає дублюванню логіки на фронті і бекенді.
Примітка
Цей набір функцій покриває ключові сценарії бізнесу, підвищує зручність роботи з даними, оптимізує логіку і прискорює розробку фронту і інтеграцій. Сподіваюсь, тобі сподобалось :)
Файли з рішенням
ПЕРЕЙДІТЬ В ПОВНУ ВЕРСІЮ