HAVING SQL. Введение
HAVING — оператор фильтрации агрегированных строк, который применяется после группировки. По запросу having sql разработчики обычно ищут способ отобрать группы по агрегатным условиям: по результатам функций COUNT, SUM, AVG, MIN, MAX. В отличие от WHERE, который фильтрует сырые строки до агрегирования, HAVING работает уже над результатами GROUP BY.
SELECT с GROUP BY и HAVING — фундамент аналитических запросов. Оператор одинаково полезен в отчётах,
дашбордах и бэкенд‑сценариях: от продажи и учёта до медицины и IoT. Ниже — 80 живых примеров с аккуратным форматированием,
кликабельными ключевыми словами (кроме темы статьи) и универсальным синтаксисом.
Синтаксис
SELECT
key_columns,
COUNT(*) AS cnt
FROM
table_name
GROUP BY
key_columns
HAVING
COUNT(*) > 0;
Типовая конструкция
SELECT
c.customer_id,
SUM(o.total_amount) AS total_sum
FROM
customers AS c
INNER JOIN orders AS o
ON o.customer_id = c.customer_id
GROUP BY
c.customer_id
HAVING
SUM(o.total_amount) >= 1000
ORDER BY
total_sum DESC;
Базовые паттерны фильтрации агрегатов
1. Клиенты с количеством заказов ≥ 3: SELECT + GROUP BY + COUNT:
SELECT
o.customer_id,
COUNT(*) AS orders_cnt
FROM
orders AS o
GROUP BY
o.customer_id
HAVING
COUNT(*) >= 3;2. Категории с суммарной выручкой > 10000: SUM после GROUP BY:
SELECT
p.category_id,
SUM(oi.qty * oi.price) AS revenue
FROM
order_items AS oi
INNER JOIN products AS p
ON p.product_id = oi.product_id
GROUP BY
p.category_id
HAVING
SUM(oi.qty * oi.price) > 10000;3. Фильмы с средним рейтингом ≥ 8: AVG в связке с HAVING:
SELECT
f.film_id,
AVG(r.rating) AS avg_rating
FROM
films AS f
LEFT JOIN reviews AS r
ON r.film_id = f.film_id
GROUP BY
f.film_id
HAVING
AVG(r.rating) >= 8;4. Склады с остатками > 500 единиц: SUM по товарам:
SELECT
s.warehouse_id,
SUM(s.qty_on_hand) AS stock_sum
FROM
stock AS s
GROUP BY
s.warehouse_id
HAVING
SUM(s.qty_on_hand) > 500;5. Авторы с ≥ 5 постами: счёт через COUNT:
SELECT
p.author_id,
COUNT(*) AS posts_cnt
FROM
posts AS p
GROUP BY
p.author_id
HAVING
COUNT(*) >= 5;6. Города с максимальной температурой > 35°C: проверка MAX:
SELECT
t.city_id,
MAX(t.celsius) AS t_max
FROM
telemetry_city AS t
GROUP BY
t.city_id
HAVING
MAX(t.celsius) > 35;7. Департаменты со средней зарплатой > 4000: AVG + зарплаты:
SELECT
e.dept_id,
AVG(e.salary) AS avg_salary
FROM
employees AS e
GROUP BY
e.dept_id
HAVING
AVG(e.salary) > 4000;8. Партнёры с оборотом в валюте USD ≥ 50k: условие по SUM:
SELECT
p.partner_id,
SUM(t.amount) AS usd_turnover
FROM
transfers AS t
WHERE
t.currency = 'USD'
GROUP BY
p.partner_id
HAVING
SUM(t.amount) >= 50000;9. Факультеты с ≥ 10 курсами: COUNT после связки:
SELECT
f.faculty_id,
COUNT(c.course_id) AS course_cnt
FROM
faculties AS f
LEFT JOIN courses AS c
ON c.faculty_id = f.faculty_id
GROUP BY
f.faculty_id
HAVING
COUNT(c.course_id) >= 10;10. Пациенты с числом визитов за год > 12: ограничение через HAVING:
SELECT
v.patient_id,
COUNT(*) AS visits_year
FROM
visits AS v
WHERE
v.visit_date >= DATE '2024-01-01'
AND v.visit_date < DATE '2025-01-01' GROUP BY v.patient_id HAVING COUNT(*) > 12;Вариации с COUNT
11. Заказы с количеством позиций ≥ 3: COUNT по строкам заказа:
SELECT
oi.order_id,
COUNT(*) AS item_cnt
FROM
order_items AS oi
GROUP BY
oi.order_id
HAVING
COUNT(*) >= 3;12. Кандидаты с ≥ 2 интервью: фильтр по COUNT:
SELECT
i.candidate_id,
COUNT(*) AS interviews
FROM
interviews AS i
GROUP BY
i.candidate_id
HAVING
COUNT(*) >= 2;13. Пользователи с ≥ 1 успешным логином: COUNT с условием в WHERE:
SELECT
l.user_id,
COUNT(*) AS success_logins
FROM
logins AS l
WHERE
l.is_success = 1
GROUP BY
l.user_id
HAVING
COUNT(*) >= 1;14. Товары с числом отзывов ≥ 20: COUNT после JOIN:
SELECT
p.product_id,
COUNT(r.review_id) AS review_cnt
FROM
products AS p
LEFT JOIN reviews AS r
ON r.product_id = p.product_id
GROUP BY
p.product_id
HAVING
COUNT(r.review_id) >= 20;15. Клиники с числом услуг ≥ 15: COUNT по прайсу:
SELECT
cl.clinic_id,
COUNT(s.service_code) AS service_cnt
FROM
clinics AS cl
LEFT JOIN services AS s
ON s.clinic_id = cl.clinic_id
GROUP BY
cl.clinic_id
HAVING
COUNT(s.service_code) >= 15;16. Города с количеством событий за сутки ≥ 1000:
SELECT
e.city_id,
COUNT(*) AS events_cnt
FROM
city_events AS e
WHERE
e.event_ts >= CURRENT_DATE
AND e.event_ts < CURRENT_DATE + INTERVAL '1' DAY GROUP BY e.city_id HAVING COUNT(*) >= 1000;17. Авторы с ≥ 2 публикациями за месяц:
SELECT
p.author_id,
COUNT(*) AS posts_month
FROM
posts AS p
WHERE
p.published_on >= DATE_TRUNC('month', CURRENT_DATE)
GROUP BY
p.author_id
HAVING
COUNT(*) >= 2;18. Преподаватели с количеством курсов > 3:
SELECT
t.teacher_id,
COUNT(ct.course_id) AS course_cnt
FROM
teachers AS t
LEFT JOIN course_teachers AS ct
ON ct.teacher_id = t.teacher_id
GROUP BY
t.teacher_id
HAVING
COUNT(ct.course_id) > 3;19. Пассажиры с ≥ 2 пересадками:
SELECT
seg.passenger_id,
COUNT(*) AS leg_cnt
FROM
flight_segments AS seg
GROUP BY
seg.passenger_id
HAVING
COUNT(*) >= 2;20. Проекты с двузначным числом задач (≥ 10):
SELECT
t.project_id,
COUNT(*) AS task_cnt
FROM
tasks AS t
GROUP BY
t.project_id
HAVING
COUNT(*) >= 10;Вариации с SUM
21. Клиенты с суммой покупок ≥ 2000: SUM по чекам:
SELECT
o.customer_id,
SUM(o.total_amount) AS total_sum
FROM
orders AS o
GROUP BY
o.customer_id
HAVING
SUM(o.total_amount) >= 2000;22. Склады с суммой остатков > 10000: SUM по количеству:
SELECT
s.warehouse_id,
SUM(s.qty_on_hand) AS stock_total
FROM
stock AS s
GROUP BY
s.warehouse_id
HAVING
SUM(s.qty_on_hand) > 10000;23. Отделы с суммой зарплат > 1 млн:
SELECT
e.dept_id,
SUM(e.salary) AS payroll_sum
FROM
employees AS e
GROUP BY
e.dept_id
HAVING
SUM(e.salary) > 1000000;24. Магазины с оборотом > 50k за неделю:
SELECT
tx.store_id,
SUM(tx.amount) AS weekly_turnover
FROM
transactions AS tx
WHERE
tx.tx_date >= CURRENT_DATE - INTERVAL '7' DAY
GROUP BY
tx.store_id
HAVING
SUM(tx.amount) > 50000;25. Пациенты с суммой платежей за услуги > 500:
SELECT
pay.patient_id,
SUM(pay.amount) AS paid_sum
FROM
payments AS pay
GROUP BY
pay.patient_id
HAVING
SUM(pay.amount) > 500;26. Проекты с суммой расходов >= бюджету (сравнение):
SELECT
e.project_id,
SUM(e.amount) AS spent
FROM
expenses AS e
GROUP BY
e.project_id
HAVING
SUM(e.amount) >= (
SELECT
p.budget
FROM
projects AS p
WHERE
p.project_id = e.project_id
);27. Категории с выручкой по товарам > 25k:
SELECT
p.category_id,
SUM(oi.qty * oi.price) AS cat_revenue
FROM
products AS p
INNER JOIN order_items AS oi
ON oi.product_id = p.product_id
GROUP BY
p.category_id
HAVING
SUM(oi.qty * oi.price) > 25000;28. Учителя с суммой часов занятий > 40:
SELECT
l.teacher_id,
SUM(l.hours) AS hours_sum
FROM
lessons AS l
GROUP BY
l.teacher_id
HAVING
SUM(l.hours) > 40;29. Устройства с суммой потреблённой энергии > 1kWh:
SELECT
m.device_id,
SUM(m.kwh) AS energy
FROM
meter AS m
GROUP BY
m.device_id
HAVING
SUM(m.kwh) > 1000;30. Каналы маркетинга с суммой лидов > 500:
SELECT
a.channel,
SUM(a.leads) AS leads_sum
FROM
acquisition AS a
GROUP BY
a.channel
HAVING
SUM(a.leads) > 500;Вариации с AVG / MIN / MAX
31. Средняя сумма транзакции по счёту ≥ 200: AVG:
SELECT
t.account_no,
AVG(t.amount) AS avg_amount
FROM
transactions AS t
GROUP BY
t.account_no
HAVING
AVG(t.amount) >= 200;32. Средний балл студента >= 70: AVG по оценкам:
SELECT
r.student_id,
AVG(r.score) AS avg_score
FROM
results AS r
GROUP BY
r.student_id
HAVING
AVG(r.score) >= 70;33. Минимальная цена в категории ≤ 5: MIN:
SELECT
p.category_id,
MIN(p.price) AS min_price
FROM
products AS p
GROUP BY
p.category_id
HAVING
MIN(p.price) <= 5;34. Максимальная температура датчика ≥ 90°C: MAX:
SELECT
t.sensor_id,
MAX(t.celsius) AS max_c
FROM
telemetry AS t
GROUP BY
t.sensor_id
HAVING
MAX(t.celsius) >= 90;35. Средняя длительность сеанса пользователя ≥ 5 мин:
SELECT
s.user_id,
AVG(s.duration_min) AS avg_duration
FROM
sessions AS s
GROUP BY
s.user_id
HAVING
AVG(s.duration_min) >= 5;36. Максимальный чек по магазину ≥ 1000:
SELECT
o.store_id,
MAX(o.total_amount) AS max_bill
FROM
orders AS o
GROUP BY
o.store_id
HAVING
MAX(o.total_amount) >= 1000;37. Минимальная скорость сети < 10 Mbps:
SELECT
n.site_id,
MIN(n.mbps) AS min_speed
FROM
net_metrics AS n
GROUP BY
n.site_id
HAVING
MIN(n.mbps) < 10;38. Средний рейтинг фильма < 5 (кандидаты на снятие):
SELECT
f.film_id,
AVG(r.rating) AS avg_rating
FROM
films AS f
LEFT JOIN reviews AS r
ON r.film_id = f.film_id
GROUP BY
f.film_id
HAVING
AVG(r.rating) < 5;39. Максимальная задержка API ≥ 1000 мс:
SELECT
a.endpoint,
MAX(a.latency_ms) AS p100
FROM
api_calls AS a
GROUP BY
a.endpoint
HAVING
MAX(a.latency_ms) >= 1000;40. Среднее число товаров в корзине ≥ 3:
SELECT
cart.user_id,
AVG(cart.items_cnt) AS avg_items
FROM
carts_daily AS cart
GROUP BY
cart.user_id
HAVING
AVG(cart.items_cnt) >= 3;Условные агрегаты через CASE
41. Только оплаченные суммы: CASE внутри SUM:
SELECT
o.customer_id,
SUM(CASE WHEN o.status = 'PAID' THEN o.total_amount ELSE 0 END) AS paid_sum
FROM
orders AS o
GROUP BY
o.customer_id
HAVING
SUM(CASE WHEN o.status = 'PAID' THEN o.total_amount ELSE 0 END) > 1000;42. Количество успешных логинов: COUNT с CASE:
SELECT
l.user_id,
COUNT(CASE WHEN l.is_success = 1 THEN 1 END) AS ok_logins
FROM
logins AS l
GROUP BY
l.user_id
HAVING
COUNT(CASE WHEN l.is_success = 1 THEN 1 END) >= 5;43. Сумма заказов по USD: SUM c фильтром в CASE:
SELECT
o.customer_id,
SUM(CASE WHEN o.currency = 'USD' THEN o.total_amount ELSE 0 END) AS usd_sum
FROM
orders AS o
GROUP BY
o.customer_id
HAVING
SUM(CASE WHEN o.currency = 'USD' THEN o.total_amount ELSE 0 END) > 500;44. Средний балл по предмету Math: AVG с CASE:
SELECT
r.student_id,
AVG(CASE WHEN r.subject = 'Math' THEN r.score END) AS avg_math
FROM
results AS r
GROUP BY
r.student_id
HAVING
AVG(CASE WHEN r.subject = 'Math' THEN r.score END) >= 80;45. Максимальная цена только для активных товаров: MAX + CASE:
SELECT
p.category_id,
MAX(CASE WHEN p.is_active = 1 THEN p.price END) AS max_active_price
FROM
products AS p
GROUP BY
p.category_id
HAVING
MAX(CASE WHEN p.is_active = 1 THEN p.price END) >= 100;46. Минимальная температура при статусе OK: MIN + CASE:
SELECT
t.sensor_id,
MIN(CASE WHEN t.state = 'OK' THEN t.celsius END) AS min_ok
FROM
telemetry AS t
GROUP BY
t.sensor_id
HAVING
MIN(CASE WHEN t.state = 'OK' THEN t.celsius END) >= 10;47. Сумма возвратов: SUM с CASE по типу операции:
SELECT
tx.store_id,
SUM(CASE WHEN tx.kind = 'REFUND' THEN tx.amount ELSE 0 END) AS refund_sum
FROM
transactions AS tx
GROUP BY
tx.store_id
HAVING
SUM(CASE WHEN tx.kind = 'REFUND' THEN tx.amount ELSE 0 END) > 1000;48. Подсчёт VIP‑клиентов по флагу: COUNT + CASE:
SELECT
c.manager_id,
COUNT(CASE WHEN c.is_vip = 1 THEN 1 END) AS vip_cnt
FROM
customers AS c
GROUP BY
c.manager_id
HAVING
COUNT(CASE WHEN c.is_vip = 1 THEN 1 END) >= 10;49. Среднее время решения только по критичным инцидентам: AVG + CASE:
SELECT
i.team_id,
AVG(CASE WHEN i.severity = 'CRITICAL' THEN i.resolve_hours END) AS avg_crit
FROM
incidents AS i
GROUP BY
i.team_id
HAVING
AVG(CASE WHEN i.severity = 'CRITICAL' THEN i.resolve_hours END) < 8;50. Максимальный чек по онлайн‑каналу: MAX + CASE:
SELECT
o.store_id,
MAX(CASE WHEN o.channel = 'ONLINE' THEN o.total_amount END) AS max_online
FROM
orders AS o
GROUP BY
o.store_id
HAVING
MAX(CASE WHEN o.channel = 'ONLINE' THEN o.total_amount END) >= 500;Соединения и фильтрация агрегатов
51. INNER JOIN счета и транзакции: фильтр сумм через HAVING:
SELECT
a.account_no,
SUM(t.amount) AS total_sum
FROM
accounts AS a
INNER JOIN transactions AS t
ON t.account_no = a.account_no
GROUP BY
a.account_no
HAVING
SUM(t.amount) > 1000;52. LEFT JOIN категории и товары: отбор по COUNT:
SELECT
c.category_id,
COUNT(p.product_id) AS items_cnt
FROM
categories AS c
LEFT JOIN products AS p
ON p.category_id = c.category_id
GROUP BY
c.category_id
HAVING
COUNT(p.product_id) >= 50;53. JOIN пользователей и логинов: успехов ≥ 3:
SELECT
u.user_id,
COUNT(l.login_id) AS ok_logins
FROM
users AS u
LEFT JOIN logins AS l
ON l.user_id = u.user_id AND l.is_success = 1
GROUP BY
u.user_id
HAVING
COUNT(l.login_id) >= 3;54. JOIN фильмы и отзывы: средний рейтинг > 7:
SELECT
f.film_id,
AVG(r.rating) AS avg_rating
FROM
films AS f
LEFT JOIN reviews AS r
ON r.film_id = f.film_id
GROUP BY
f.film_id
HAVING
AVG(r.rating) > 7;55. JOIN склады и остатки: сумма по складу > 2000:
SELECT
w.warehouse_id,
SUM(s.qty_on_hand) AS qty_sum
FROM
warehouses AS w
LEFT JOIN stock AS s
ON s.warehouse_id = w.warehouse_id
GROUP BY
w.warehouse_id
HAVING
SUM(s.qty_on_hand) > 2000;56. JOIN курсы и экзамены: количество экзаменов ≥ 2:
SELECT
c.course_id,
COUNT(e.exam_id) AS exam_cnt
FROM
courses AS c
LEFT JOIN exams AS e
ON e.course_id = c.course_id
GROUP BY
c.course_id
HAVING
COUNT(e.exam_id) >= 2;57. JOIN пациенты и визиты: среднее по визитам ≥ 30 минут:
SELECT
p.patient_id,
AVG(v.minutes) AS avg_minutes
FROM
patients AS p
LEFT JOIN visits AS v
ON v.patient_id = p.patient_id
GROUP BY
p.patient_id
HAVING
AVG(v.minutes) >= 30;58. JOIN сотрудники и задачи: максимальный срок > 14 дней:
SELECT
e.emp_id,
MAX(t.due_days) AS max_due
FROM
employees AS e
LEFT JOIN tasks AS t
ON t.owner_id = e.emp_id
GROUP BY
e.emp_id
HAVING
MAX(t.due_days) > 14;59. JOIN магазины и транзакции: возвраты > 5% (доля):
SELECT
s.store_id,
SUM(CASE WHEN tx.kind = 'REFUND' THEN tx.amount ELSE 0 END) / NULLIF(SUM(tx.amount), 0) AS refund_ratio
FROM
stores AS s
LEFT JOIN transactions AS tx
ON tx.store_id = s.store_id
GROUP BY
s.store_id
HAVING
SUM(CASE WHEN tx.kind = 'REFUND' THEN tx.amount ELSE 0 END) / NULLIF(SUM(tx.amount), 0) > 0.05;60. JOIN банки и отделения: число сотрудников ≥ 20:
SELECT
b.bank_id,
COUNT(e.emp_id) AS staff_cnt
FROM
banks AS b
LEFT JOIN employees AS e
ON e.bank_id = b.bank_id
GROUP BY
b.bank_id
HAVING
COUNT(e.emp_id) >= 20;Подзапросы и коррелированные условия
61. Клиенты, чья сумма > среднего по всем: вложенный SELECT:
SELECT
o.customer_id,
SUM(o.total_amount) AS total_sum
FROM
orders AS o
GROUP BY
o.customer_id
HAVING
SUM(o.total_amount) > (
SELECT
AVG(o2.total_amount)
FROM
orders AS o2
);62. Категории, где число товаров > средне‑категориального COUNT:
SELECT
p.category_id,
COUNT(*) AS items_cnt
FROM
products AS p
GROUP BY
p.category_id
HAVING
COUNT(*) > (
SELECT
AVG(cnt)
FROM
(
SELECT
COUNT(*) AS cnt
FROM
products
GROUP BY
category_id
) AS t
);63. Магазины с оборотом > среднего по региону:
SELECT
s.store_id,
SUM(tx.amount) AS store_sum
FROM
stores AS s
INNER JOIN transactions AS tx
ON tx.store_id = s.store_id
GROUP BY
s.store_id
HAVING
SUM(tx.amount) > (
SELECT
AVG(sum_by_store)
FROM
(
SELECT
st.store_id,
SUM(t.amount) AS sum_by_store
FROM
stores AS st
INNER JOIN transactions AS t
ON t.store_id = st.store_id
GROUP BY
st.store_id
) AS sub
);64. Курсы, у которых средняя оценка > средней по факультету:
SELECT
c.course_id,
AVG(r.score) AS avg_score
FROM
courses AS c
LEFT JOIN results AS r
ON r.course_id = c.course_id
GROUP BY
c.course_id
HAVING
AVG(r.score) > (
SELECT
AVG(r2.score)
FROM
courses AS c2
LEFT JOIN results AS r2
ON r2.course_id = c2.course_id
WHERE
c2.faculty_id = c.faculty_id
);65. Сотрудники с затратами > средней по отделу:
SELECT
e.emp_id,
SUM(x.amount) AS emp_spent
FROM
employees AS e
LEFT JOIN expenses AS x
ON x.emp_id = e.emp_id
GROUP BY
e.emp_id
HAVING
SUM(x.amount) > (
SELECT
AVG(dept_sum)
FROM
(
SELECT
e2.dept_id,
SUM(x2.amount) AS dept_sum
FROM
employees AS e2
LEFT JOIN expenses AS x2
ON x2.emp_id = e2.emp_id
GROUP BY
e2.dept_id
) AS s
WHERE
s.dept_id = e.dept_id
);66. Поставщики, чьи поставки > среднего по всем поставщикам:
SELECT
s.supplier_id,
SUM(po.amount) AS supply_sum
FROM
suppliers AS s
LEFT JOIN purchase_orders AS po
ON po.supplier_id = s.supplier_id
GROUP BY
s.supplier_id
HAVING
SUM(po.amount) > (
SELECT
AVG(po2.amount)
FROM
purchase_orders AS po2
);67. Страны с числом городов > среднего:
SELECT
c.country_code,
COUNT(*) AS city_cnt
FROM
cities AS c
GROUP BY
c.country_code
HAVING
COUNT(*) > (
SELECT
AVG(cnt)
FROM
(
SELECT
country_code,
COUNT(*) AS cnt
FROM
cities
GROUP BY
country_code
) AS t
);68. Аккаунты, где доля отказов > среднего по системе:
SELECT
a.account_no,
SUM(CASE WHEN tx.kind = 'DECLINED' THEN 1 ELSE 0 END) * 1.0 / NULLIF(COUNT(*), 0) AS decline_ratio
FROM
transactions AS tx
INNER JOIN accounts AS a
ON a.account_no = tx.account_no
GROUP BY
a.account_no
HAVING
SUM(CASE WHEN tx.kind = 'DECLINED' THEN 1 ELSE 0 END) * 1.0 / NULLIF(COUNT(*), 0) > (
SELECT
AVG(ratio)
FROM
(
SELECT
SUM(CASE WHEN t.kind = 'DECLINED' THEN 1 ELSE 0 END) * 1.0 / NULLIF(COUNT(*), 0) AS ratio
FROM
transactions AS t
GROUP BY
t.account_no
) AS x
);69. Эндпоинты API, где p95 задержки > среднего p95 (эмуляция MAX):
SELECT
a.endpoint,
MAX(a.latency_ms) AS p_max
FROM
api_calls AS a
GROUP BY
a.endpoint
HAVING
MAX(a.latency_ms) > (
SELECT
AVG(p95)
FROM
(
SELECT
MAX(a2.latency_ms) AS p95
FROM
api_calls AS a2
GROUP BY
a2.endpoint
) AS m
);70. Города, где средняя температура > средней по стране:
SELECT
t.city_id,
AVG(t.celsius) AS avg_c
FROM
telemetry_city AS t
GROUP BY
t.city_id
HAVING
AVG(t.celsius) > (
SELECT
AVG(t2.celsius)
FROM
telemetry_city AS t2
WHERE
t2.country_code = (
SELECT
c.country_code
FROM
cities AS c
WHERE
c.city_id = t.city_id
)
);Прикладные сценарии в разных доменах
71. E‑commerce: категории с возвратами > 3% по SUM и HAVING:
SELECT
p.category_id,
SUM(CASE WHEN tx.kind = 'REFUND' THEN oi.qty * oi.price ELSE 0 END) / NULLIF(SUM(oi.qty * oi.price), 0) AS refund_ratio
FROM
order_items AS oi
INNER JOIN products AS p
ON p.product_id = oi.product_id
INNER JOIN transactions AS tx
ON tx.order_id = oi.order_id
GROUP BY
p.category_id
HAVING
SUM(CASE WHEN tx.kind = 'REFUND' THEN oi.qty * oi.price ELSE 0 END) / NULLIF(SUM(oi.qty * oi.price), 0) > 0.03;72. Финансы: счета с долей отклонённых транзакций > 10%:
SELECT
tx.account_no,
SUM(CASE WHEN tx.status = 'DECLINED' THEN 1 ELSE 0 END) * 1.0 / NULLIF(COUNT(*), 0) AS decline_ratio
FROM
transactions AS tx
GROUP BY
tx.account_no
HAVING
SUM(CASE WHEN tx.status = 'DECLINED' THEN 1 ELSE 0 END) * 1.0 / NULLIF(COUNT(*), 0) > 0.10;73. HR: менеджеры с числом прямых подчинённых ≥ 8:
SELECT
e.manager_id,
COUNT(*) AS direct_reports
FROM
employees AS e
WHERE
e.manager_id IS NOT NULL
GROUP BY
e.manager_id
HAVING
COUNT(*) >= 8;74. Медицина: врачи со средней длительностью приёма < 15 минут:
SELECT
v.doctor_id,
AVG(v.minutes) AS avg_visit
FROM
visits AS v
GROUP BY
v.doctor_id
HAVING
AVG(v.minutes) < 15;75. Образование: курсы со средним баллом >= 85:
SELECT
r.course_id,
AVG(r.score) AS avg_score
FROM
results AS r
GROUP BY
r.course_id
HAVING
AVG(r.score) >= 85;76. IoT: сенсоры с долей ошибок > 1%:
SELECT
t.sensor_id,
SUM(CASE WHEN t.state = 'ERR' THEN 1 ELSE 0 END) * 1.0 / NULLIF(COUNT(*), 0) AS err_ratio
FROM
telemetry AS t
GROUP BY
t.sensor_id
HAVING
SUM(CASE WHEN t.state = 'ERR' THEN 1 ELSE 0 END) * 1.0 / NULLIF(COUNT(*), 0) > 0.01;77. CRM: менеджеры с суммой сделок > 100k:
SELECT
d.owner_id,
SUM(d.amount) AS total_amount
FROM
deals AS d
GROUP BY
d.owner_id
HAVING
SUM(d.amount) > 100000;78. Логистика: маршруты со средним временем в пути > 5 часов:
SELECT
r.route_id,
AVG(r.travel_hours) AS avg_hours
FROM
routes AS r
GROUP BY
r.route_id
HAVING
AVG(r.travel_hours) > 5;79. Retail: магазины с долей скидочных продаж > 40%:
SELECT
s.store_id,
SUM(CASE WHEN oi.discount_pct > 0 THEN oi.qty * oi.price ELSE 0 END) / NULLIF(SUM(oi.qty * oi.price), 0) AS disc_ratio
FROM
order_items AS oi
INNER JOIN orders AS o
ON o.order_id = oi.order_id
INNER JOIN stores AS s
ON s.store_id = o.store_id
GROUP BY
s.store_id
HAVING
SUM(CASE WHEN oi.discount_pct > 0 THEN oi.qty * oi.price ELSE 0 END) / NULLIF(SUM(oi.qty * oi.price), 0) > 0.40;80. Безопасность: IP‑адреса с числом неудачных попыток входа ≥ 10:
SELECT
l.ip_address,
COUNT(*) AS failed_cnt
FROM
logins AS l
WHERE
l.is_success = 0
GROUP BY
l.ip_address
HAVING
COUNT(*) >= 10;Заключение
having sql — это точечный контроль качества агрегатов. Используйте HAVING всякий раз, когда нужно фильтровать результат GROUP BY по агрегатам (COUNT, SUM, AVG, MIN, MAX) или их комбинациям с CASE. Следите за читаемостью (выравнивание, алиасы), индексируйте поля группировки, переносите тяжёлые вычисления в подзапросы и анализируйте планы выполнения.