Автор Бен Надел. Веб-разработчик с 25 летним стажем
На работе мы недавно обновились до MySQL 8. И это, наконец, дало мне возможность начать экспериментировать с более продвинутыми концепциями из-за большого количества разовых отчетов, которые мне приходится запускать. Несколько недель назад я изучил использование конструкций VALUES и ROW для создания обобщенных табличных выражений (Common Table Expression, CTE). И, как продолжение этого, я только что узнал, что в одном запросе MySQL можно иметь несколько CTE. И что эти CTE могут ссылаться друг на друга. Так круто!
Чтобы это продемонстрировать, я собираюсь построить SQL-запрос с использованием CTE. Сначала мы начнем со списка поддельных адресов электронной почты. Это то, что я часто делаю, копируя их из файла CSV (Comma Separated Values). Следующее CTE просто приводит адреса электронной почты в удобный формат:
Создается производная таблица emails с одним столбцом, email. CTE могут быть задействованы в основном SQL-запросе, но также могут быть использованы в других CTE в том же запросе. И именно это мы сделаем дальше — создадим ещё одно CTE, которое будет основываться на предыдущем CTE и извлечёт домен из каждой строки.
Как вы можете видеть, этот CTE запрашивает данные из предыдущего CTE, emails. Затем он использует функцию SUBSTRING_INDEX(), чтобы извлечь домен из каждого адреса электронной почты, создавая еще одну производную таблицу/CTE.
Далее, мы будем использовать это новое CTE для создания еще одного CTE, которое группирует адреса электронной почты по доменам и записывает статистику COUNT():
Как вы можете видеть, это CTE запрашивает данные из предыдущего CTE, deconstructed.
Далее, мы создадим еще одно CTE, которое снова ссылается на предыдущее CTE, deconstructed. Но на этот раз, вместо получения количества, мы соберем адреса электронной почты — по доменам — в JSON-агрегацию.
Примечание: я мог бы объединить это CTE с предыдущим CTE и использовать как COUNT(), так и JSON_ARRAYAGG() в одном запросе; однако, разделяя их, я мог более детально изучить эту область.
До сих пор мы создавали только CTE, которые ссылаются на другие CTE. Но эти CTE просто создают служебные таблицы, они не возвращают данные клиенту. Теперь пришло время написать SQL-запрос, который возвращает реальные данные. Этот SQL-запрос объединит несколько CTE из вышеперечисленных:
Как вы можете видеть, мы берем два CTE, stats и aggregated, и объединяем их с помощью JOIN.
Мы рассмотрели отдельные части. Вот весь SQL-запрос:
И когда мы запустим этот запрос в MySQL, мы получим следующий результат:
Когда дело доходит до написания простых бизнес-приложений, мне не нужны эти "модные" механизмы SQL. Но когда вопрос касается отчетности и других типов задач по разовому анализу данных, такие функции, как VALUES/ROW, итеративно улучшенные CTE и JSON-агрегации просто удивительны! SQL — действительно замечательный язык.
Оригинал статьи: https://www.bennadel.com/blog/4652-using-multiple-common-table-expressions-in-one-sql-query-in-mysql.htm