Решение задачи SQL-ex: выбор баллончиков с красной краской и голубой компонентой

Задача с сайта SQL-ex на тему SELECT (обучающий этап) часто вызывает трудности из-за неочевидных условий проверки. Разберём корректный подход, который проходит тесты на проверочной базе, и объясним, почему популярные варианты могут давать сбой.

Условие задачи

Дана схема базы данных из трёх таблиц: utQ (квадраты), utV (баллончики с краской), utB (операции окраски). Баллончики могут быть красного (V_COLOR='R'), зелёного ('G') или голубого ('B') цвета. Объём каждого баллончика - 255, и он изначально полный. Каждая запись в utB уменьшает краску в баллончике на B_VOL и увеличивает её в квадрате. Цвет квадрата формируется по RGB: красная, зелёная и голубая компоненты могут быть от 0 до 255.

Требуется: при условии, что баллончики с красной краской использовались более одного раза, выбрать из них такие, которыми окрашены квадраты, имеющие голубую компоненту. Вывести название баллончика.

Ключевые моменты для правильного решения

• Красные баллончики, использованные более одного раза: нужно посчитать количество различных операций (записей в utB) для каждого красного баллончика. Если их больше одной - подходит.
• Квадраты с голубой компонентой: квадрат имеет голубую компоненту, если хотя бы один раз был окрашен голубым баллончиком (V_COLOR='B').
• Пересечение: итоговый баллончик должен быть красным, использованным более одного раза, и при этом хотя бы один квадрат, который он окрашивал, содержит голубую компоненту.

Типичные ошибки в решениях

Ошибка 1: Неправильный подсчёт «использован более одного раза»

Некоторые варианты считают количество записей по V_ID внутри окна, но не учитывают, что один и тот же баллончик может красить разные квадраты. Условие «использовался более одного раза» означает, что количество строк в utB для данного баллончика должно быть больше 1.

Ошибка 2: Смешение цветов в оконных функциях

Встречаются запросы, где в одном оконном выражении проверяют и красный, и голубой цвет, что приводит к неверной логике. Каждый цвет нужно анализировать отдельно.

Ошибка 3: Игнорирование проверочной базы

Создатели сайта добавили дополнительные тесты, которые проверяют краевые случаи: баллончики, которые красили только один квадрат, но с голубой компонентой; квадраты с несколькими цветами и т.д. Решение должно быть универсальным.

Корректное решение

Приведём рабочий запрос, который прошёл проверку на основной и проверочной базах:

WITH red_balloons AS (
    SELECT DISTINCT b.B_V_ID
    FROM utB b
    JOIN utV v ON v.V_ID = b.B_V_ID
    WHERE v.V_COLOR = 'R'
    GROUP BY b.B_V_ID
    HAVING COUNT(*) > 1
),
blue_squares AS (
    SELECT DISTINCT b.B_Q_ID
    FROM utB b
    JOIN utV v ON v.V_ID = b.B_V_ID
    WHERE v.V_COLOR = 'B'
)
SELECT DISTINCT v.V_NAME
FROM utV v
JOIN utB b ON v.V_ID = b.B_V_ID
WHERE v.V_ID IN (SELECT B_V_ID FROM red_balloons)
  AND b.B_Q_ID IN (SELECT B_Q_ID FROM blue_squares);

Пояснение запроса

• red_balloons: выбираем все красные баллончики, которые встречаются в utB более одного раза (HAVING COUNT(*) > 1).
• blue_squares: выбираем все квадраты, которые окрашивались голубым баллончиком.
• Основной запрос: из таблицы utV берём названия баллончиков, которые одновременно входят в red_balloons и имеют хотя бы одну операцию окраски квадрата из blue_squares.

Почему это решение работает на проверочной базе?

Оно использует простые подзапросы без оконных функций, что гарантирует корректную агрегацию. Условие HAVING COUNT(*) > 1 надёжно отсекает баллончики, использованные только один раз. Отбор квадратов с голубой компонентой выполняется независимо, поэтому не возникает путаницы с цветами. Результат выводится через DISTINCT, чтобы избежать дублирования названий.

Вывод

Для успешного решения задачи на SQL-ex важно чётко разделять логические этапы: найти красные баллончики с несколькими использованиями, найти квадраты с голубой краской, затем пересечь их. Избегайте излишне сложных конструкций с оконными функциями, если они не обязательны. Представленный запрос надёжен и проходит все тесты.