Реляционное деление в SQL (часть 1)

• Обсуждение новой темы - революционное деление в SQL.  

• Тема обширная и требует нескольких серий для полного раскрытия.  

• Ведущий - Федор Самородов, преподаватель в учебном центре "Специалист".  

• В SQL используются операции алгебры множеств.  

• Таблица рассматривается как множество строк.  

• Операции сложения, вычитания, умножения и деления применимы к таблицам.  

• Сложение таблиц создает новую таблицу с общими строками.  

• Вычитание таблиц оставляет только строки, которых нет в другой таблице.  

• Умножение таблиц кросс-джойн создает новую таблицу с комбинациями строк из двух исходных.  

• Деление таблиц возможно и часто используется на практике.  

• В SQL нет отдельного оператора для деления, но можно использовать обходные пути.  

• Деление таблиц аналогично делению чисел, но с таблицами.  

• Пример с преподавателями и курсами.  

• Деление одной таблицы на другую для нахождения подходящих преподавателей.  

• Результат деления - подмножество исходной таблицы.  

• Деление с остатком: результат содержит преподавателей с дополнительными курсами.  

• Деление без остатка: результат содержит только нужные курсы.  

• В практических задачах деление с остатком и без остатка не имеет принципиальной разницы.  

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

• Примеры задач, где требуется революционное деление: покупка товаров в одном магазине, анализ связанных продаж.  

• Пример с магазинами: покупка холодильника, телефона и батареек в одном месте.  

• Магазины Media Markt и М-Видео подходят для этой задачи.  

• Пример с историей покупок: анализ покупок покупателей для рекламы товаров для девочек.  

• Задача: определить покупателей, которые одновременно покупали подгузники и куклы Барби.  

• Признаки: элементы одного списка связаны с несколькими элементами другого списка.  

• Примеры: поставщики, удовлетворяющие всем требованиям вакансии, пользователи службы знакомств, удовлетворяющие всем пожеланиям.  

• Методы: метод двойного отрицания, метод пересечения множеств.  

• Метод двойного отрицания: перебор курсов и преподавателей для проверки соответствия.  

• Метод двойного отрицания: перебор курсов и преподавателей для проверки отсутствия несовпадений.  

• Проблемы: медленное выполнение, невозможность трансформации в решение для деления без остатка, неправильное деление на ноль.  

• Метод двойного отрицания интересен как интеллектуальное упражнение, но не рекомендуется для практического использования.  

• Метод аналогичен первому, но использует операцию разности множеств.  

• Поддерживается не во всех СУБД, но технически не отличается от первого метода.  

• Синтаксически проще и приятнее для написания.  

• Копирование предыдущего запроса и замена подзапроса на разность множеств.  

• Разность множеств заменяет корреляцию и корреляционные условия.  

• План запроса остается прежним, но метод более точно выражает задачу.  

• Подсчет количества курсов, которые читает преподаватель.  

• Группировка по преподавателям и применение агрегатной функции.  

• Условие: если преподаватель читает два или меньше курсов, он подходит.  

• Сравнение агрегатной функции с двойкой для определения подходящих преподавателей.  

• Перенос фильтра в секцию FROM для универсальности.  

• Использование универсального запроса для подсчета количества курсов.  

• Использование DISTINCT для предотвращения дублирования данных.  

• Анализ производительности: таблица с расписанием затрагивается только один раз.  

• Корректная работа при делении на ноль.  

• Замена INNER JOIN на LEFT JOIN для точного деления.  

• Проверка, добавляет ли LEFT JOIN новые строки.  

• Сравнение количества курсов в левой и правой частях результирующей таблицы.  

• Третий метод признан лучшим по всем параметрам.  

• Исследование производительности методов для практических применений.  

• Анализ скорости работы и масштабируемости методов.  

• Созданы две таблицы: одна с миллионом строк, другая со ста миллионами строк.  

• Делитель - корзина покупательская или курсы, которые совместно куплены.  

• Графики представлены в логарифмической шкале для лучшего отображения трендов.  

• Логарифмическая шкала подчеркивает тренды и сглаживает несуществующие.  

• Размер делимого фиксирован, делитель обычно маленький.  

• В большинстве задач делитель равен двум или трем строкам.  

• Первый и второй методы компилируются в одинаковые планы.  

• Производительность низкая, даже при делении миллиона строк на две строки.  

• Алгоритмы хорошо масштабируются по размеру делителя, но плохо по размеру делимого.  

• В третьем методе таблица с делимым поднимается один раз, что улучшает производительность.  

• Время выполнения запроса составляет миллисекунды, что позволяет работать в реальном времени.  

• Метод начинает подтормаживать на больших делителях из-за соединения джойн.  

• Алгоритм номер три хорош, но на больших делителях требует улучшений.  

• Подведены промежуточные итоги: из трех методов годен только один - джойн и грубай.  

• Переход ко второй серии для лучшего понимания революционного деления.  

• Рассматриваются практические задачи, где революционное деление применяется в реальных условиях.  

• Пример: реклама нового товара среди покупателей аналогов.  

• Цель: выбрать покупателей, которые покупают аналоги, и найти подходящий рекламный носитель.  

• Рекламный носитель выбирается среди журналов, которые покупают покупатели аналогов.  

• Важно найти журналы, которые часто покупают вместе с аналогами.  

• Цель: найти журнал, который чаще всего покупают клиенты, покупающие аналоги, но не новый товар.  

• Задача переводится на математический язык: история покупок клиентов.  

• Корзина делится на два товара: аналог и журнал.  

• Цель: найти максимальное количество покупателей, которые не пробовали новый товар.  

• Рассматриваются товары: конкуренты и новый товар.  

• Используются журналы: наука и жизнь, знание-сила, юный техник.  

• Начинается революционное деление фактической таблицы продаж.  

• Группировка покупок по покупателям и анализ широты ассортимента.  

• Выделение покупателей, которые покупают и аналог, и журнал.  

• Исключение покупателей, которые уже пробовали новый товар.  

• Создание табличных выражений для упрощения запросов.  

• Выполнение запросов для каждого сочетания аналога и журнала.  

• Группировка по журналам для определения самого перспективного для рекламы.  

• Самый перспективный журнал для рекламы - наука и жизнь.  

• Решение задачи с использованием революционного деления.  

• Призыв к обучению на курсе по аналитике на языке SQL.  

• Анонс следующих серий и приглашение на вебинары.