Поиск по сайту:
Глаза дружбы редко ошибаются (Ф. Вольтер).

Нумерация строк, рейтинг: как использовать пользовательскую переменную LESS в запросах MySQL

1 Звезда2 Звезды3 Звезды4 Звезды5 Звезд (Пока оценок нет)
Загрузка...
14.03.2018
Нумерация строк, рейтинг: как использовать пользовательскую переменную LESS в запросах MySQL

Пользовательские переменные, хотя и не входят в стандартный SQL, большие объекты в MySQL. Они позволяют сохранить некоторое «состояние» для жизни сеанса: сеанс может выполнить инструкцию для вычисления значения, хранить это значение в переменной пользователя и использовать его во всех последующих заявлениях. Это позволяет избежать перерасчет одинакового значения в каждом из следующих утверждений, таким образом, улучшить эффективность.

Например, давайте моделировать лотерею: подобрать случайного пользователя, хранить его в таблице победителей, и отправить по электронной почте ему поздравление:

 

Использование @winner позволило нам запустить подзапрос один раз, а не два раза; что быстрее и правильнее: работает она в два раза, что привело бы к потенциально двум разным победителям для вставки и отправки по электронной почте, в результате чего появилась бы логическая ошибка в нашем проекте.

Это все красиво и аккуратно. Выше пример ясно отделяет фазу:

  • Фаза 1 устанавливает переменную @winner
  • Фаза 2 считает переменную @winner в следующих двух утверждениях.

Но это также может устанавливать и считывать пользовательскую переменную в одном операторе. Случай использования: иметь «движущееся значение», которое изменяется для каждой строки, на основе значения, которое она имела в предыдущем ряду (это предполагает, что понятие «предыдущего ряда», определяемого некоторой упорядоченности).

Вот классический пример, который генерирует номера строк (1, 2, …) в соответствии с каким-то порядком. У нас есть таблица известных людей:

Мы хотим отредактировать и пронумеровать их по дате рождения:

Оно работает.

Мы добавим: удивительно. Теперь объясним.

Стандарт SQL говорит, что мы должны объединить таблицы (здесь мы имеем только одну, people), положить результат объединения в воображаемой таблице, затем оценить выбранные выражения для каждой строки, образуя еще одну воображаемую таблицу, а затем отсортировать эту таблицу и вернуть его пользователю.

Да, в теории упорядочения является очень заключительным этапом выполнения, после того, как вычислены выбранные выражения для всех соответствующих строк (что логично, поскольку ORDER BY разрешено ссылаться на отдельные выражения с помощью псевдонима). Таким образом, если мы будем следовать этому, мы читаем people, оцениваем отдельные выражения для каждой строки, вычислим номер строки для каждой строки, и, наконец, мы упорядочиваем результат по дате рождения. Вычисляется перед упорядочиванием, номера строк не будет иметь никаких оснований, чтобы быть в соответствии с порядком.

К счастью, здесь, MySQL видит, что есть только одна таблица и что выражение упорядочения является столбец этой таблицы, так что делает оптимизацию: это первый orders ряда people, а затем читает этот сортированный результат, и для каждой строки вычисляет выбранные выражения ( в том числе @rownum). Так @rownum увеличивается в порядке, по желанию. Но, как уже отмечалось, опираясь на ORDER BY оптимизации проделанной MySQL, которая не гарантированно остается постоянным в будущем (кто знает? Мы вновь эвристические и экономически обоснованных решений по оптимизации время от времени). Более того, если в дальнейшем разработчик приложения изменяет наш запрос, эта модификация может привести MySQL изменить свою стратегию оптимизации и сделать сортировку после оценки выбранных выражений, полностью нарушая логику @RowNum. Чтобы продемонстрировать это, давайте говорить, что об этом не знает разработчик, просто хочет добавить в наш запрос столбец, отображающий полное название страны гражданства – довольно невинно, да? Он первым делает таблицу с полными именами:

CREATE TABLE country(code CHAR(2), name VARCHAR(100));
 
INSERT INTO country VALUES
("GT", "Guatemala"),
("DE", "Germany"),
("FR", "France"),
("UK", "United Kingdom"),
("RU", "Russia");

 

и теперь он редактирует наш запрос пронумерованных строк, соединяя людей со страной:

num совершенно неправильно! Поскольку оптимизатор выбрал план, который делает заказ в прошлом, таким образом, после оценки пользовательских переменных. Перечень факторов, которые могут нарушить нелимитирующий Num являются: порядок соединения, использование присоединения буферизации, использование временной таблицы, GROUP BY …

Идем дальше … чтобы избежать туда-обратно, который посылает инициализирующее заявление SET, некоторые умные пользователи (без иронии на «умной») связывают два в одном с этим трюком:

Оно работает. Он основан на том, что MySQL оценивает содержание производной таблицы с именем initialization. Но в будущем, MySQL может стать умнее и сказать, что «эта производная таблица не используется в инструкции, так что я могу просто пропустить его оценку». И, таким образом, пропустить инициализацию.

Эти запросы делают предположения, которые могут держаться сегодня, но не являются будущими. Если они удерживаются сегодня, у нас нет сознательного намерения нарушать их, но однажды мы неизбежно сделаем, как старый код получает рефакторинг, как новые функции SQL реализуются, как мы приближаемся к стандарту SQL. То, что никогда не было явно гарантировано, может измениться. Случайное изменение может произойти с нашей стороны или с вашей стороны (см. пример с полным именем гражданина и вступлением).

Наше руководство осторожно предупреждает об этом :

«Как правило, кроме как в отчетности SET, вы никогда не должны присвоить значение переменной пользователя и читать значение в том же заявлении. Например, чтобы увеличить переменную, это нормально: SET @a = @a + 1. Для других заявлений, таких как SELECT, вы можете получить результаты, которые вы ожидаете, но это не гарантировано».

Мы слышали, что кто – то сказал: « Вы критикуете, но нет никакого другого способа получить пронумерованные строки в MySQL!». Вот хорошие новости: в MySQL 8.0, есть, наконец, еще один способ: оконные функции. Вот запрос, использует оконную функцию row_number:

Это дает правильный результат. Если вы новичок в синтаксисе функций окна, вы можете проверить руководство.

В отличие от запроса с пользовательской переменной, добавление присоединения к запросу с оконной функцией не нарушает num:

Подобный случай использования получает номер строки внутри группы; допустим я хочу группу по полу; для этого, умные пользователи написали следующий запрос, который обнаруживает, если пол так же, как в предыдущей строке; если это так, мы в той же группе, что и предыдущие строки, увеличиваем счетчик, в противном случае мы открываем новую группу, чтобы сбросить его до 1:

Оно работает. Но это зависит от того, что MySQL устанавливает в @rownum перед установкой @prev_sex, т.е. оценивает выбранное выражение слева направо. Это касается данного запроса, но не для всех запросов. Как сказано в руководстве: «порядок вычисления выражений, включающих пользовательские переменные не определен». Тот же результат теперь достижим с вложенным предложением PARTITION для окна:

Каковы преимущества нового подхода с оконными функциями? Во- первых, гораздо короче запрос. Во- вторых, это стандарт совместимый так:

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

Возможно, некоторым особенно сложным и интеллектуальным запросам еще нужны пользовательские переменные методы чтения и набор, или, возможно, некоторые работают лучше, чем их эквивалент с оконными функциями.

Но, как правило, этот старая практика черной магии не должно быть больше необходимым, и преимущественно заменен оконными функции ROW_NUMBER, RANK, LEAD, LAG, FIRST_VALUE…

Вот и на сегодняшний день. Благодарим за использование MySQL!

Нумерация строк, рейтинг: как использовать пользовательскую переменную LESS в запросах MySQL

Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.

Если статья понравилась, то поделитесь ей в социальных сетях:

Читайте также

0 0 голоса
Рейтинг статьи
Подписаться
Уведомить о
guest

**ссылки nofollow

0 комментариев
Старые
Новые Популярные
Межтекстовые Отзывы
Посмотреть все комментарии

Сообщить об опечатке

Текст, который будет отправлен нашим редакторам: