Skip to content

3️⃣ Продвинутый SQL

Числовой тип данных в SQL

Создание числовых данных в SQL достаточно простое: можно ввести число как литерал, можно получить его из столбца таблицы или сгенерировать его путём вычислений.

При вычислениях можно использовать все стандартные арифметические операции (+, -, *, / и прочие) и изменять приоритеты вычислений с помощью скобок.

Математические функции

Для большинства математических вычислений, таких как получение степени числа или получение квадратного корня, в SQL есть встроенные числовые функции. Вот некоторые примеры данных функций:

Список всех числовых функций, их описание и примеры можно посмотреть в справочнике.

Округление чисел

При работе с числами с плавающей точкой не всегда требуется сохранение или отображение чисел с полной точностью. Так, денежные транзакции можно хранить с точностью до 6 знаков после запятой, а отображать до 2, с точностью до копеек.

Для округления числовых данных в SQL предусмотрены следующие 4 функции: CEIL, FLOOR, ROUND, TRUNCATE.

Функции CEIL, FLOOR направлены на то, чтобы округлять число к ближайшему целому числу в большую и в меньшую сторону соответственно.

Функция ROUND также позволяет округлять число до некоторой части десятичных знаков после запятой. Для этого функция принимает второй необязательный аргумент с указанием количества знаков после запятой, которые требуется оставить.

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

Функция TRUNCATE аналогична функции ROUND, она также способна принимать 2-й необязательный параметр, только вместо округления она просто отбрасывает ненужные цифры.

Работа со знаковыми числами

При работе с числовыми данными, в которых возможно наличие отрицательных значений, могут быть полезными функции SIGN и ABS.

Функция SIGN возвращает значение -1, если число отрицательно, 0, если число нулевое и 1, если число положительное.

Функция ABS возвращает абсолютное значение числа.

Дата и время в SQL

Из всех типов данных в SQL временны́е данные являются наиболее сложными 🤯. Сложность возникает по нескольким причинам, и вот некоторые из них:

  • множество способов задания даты и времени
  • наличие временных зон
  • неочевидность вычислений некоторых значений на основании временных данных. Например, сложность вычисления возраста.

Генерация временных данных

Временные данные можно получить одним из следующих способов:

  • скопировать данные из существующего столбца с времéнным типом данных
  • задать дату и время через строковое представление
  • получить временны́е данные путём вызова встроенных функций, возвращающих временной тип данных

Строковое представление временных данных

Для задания даты и времени используются следующие форматы:

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

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

В запросе выше для принудительного преобразования строки в дату и время была использована функция CAST. Она необходима, если сервер не ожидает временного значения и, соответственно, автоматически не преобразует строку к нужному типу. С преобразованием типов мы более подробно познакомимся в статье «Функции преобразования типов, CAST».

Функции генерации дат

Если необходимо получить временные данные из строки, которая не соответствует ни одному формату, который принимает функция CAST, то можно использовать встроенную функцию STR_TO_DATE, которая принимает произвольную строку, содержащую дату, и формат, описывающий её.

Более подробное описание функции STR_TO_DATE и её аргументов можно посмотреть в справочнике.

Для генерации же текущей даты или времени нет необходимости создавать строку для последующего её преобразования в дату, потому что есть встроенные функции для получения данных значений: CURDATE, CURTIME и NOW.

Часовые пояса

Так как люди во всем мире хотят, чтобы полдень примерно соответствовал максимальному подъёму Солнца, то никогда не было задачи использовать универсальное время и мир был разделён на 24 часовых пояса.

В качестве точки отсчёта времени используется UTC (Coordinated Universal Time). Все остальные часовые пояса можно описать количеством часов сдвига от UTC. Для примера, часовой пояс Москвы может быть описан как UTC+3.

Часовой пояс является одной из настроек сервера баз данных и может задаваться:

  • глобально
  • для текущего пользователя
  • для текущей пользовательской сессии
SET GLOBAL time_zone = '+03:00';    // глобально
SET time_zone = '+03:00';           // для текущего пользователя
SET @@session.time_zone = '+03:00'; // для текущей пользовательской сессии

Соответственно, при изменении временной зоны все значения с типом TIMESTAMP будут выводиться с учётом текущей активной временной зоны.

Функции преобразования типов, CAST

Когда мы выполняем операции со значениями с разными типами данных, СУБД пытается выполнить преобразование и привести используемые значения к нужному типу. Например, в примере ниже мы сравниваем значения с типами STRING и INT. Чтобы выполнить это сравнение MySQL автоматически сконвертирует строковое значение в числовое.

Но не все преобразования СУБД может сделать автоматически, и тогда необходимо делать явное преобразование типов. Для этого в MySQL есть две очень схожие функции CAST и CONVERT.

Синтаксис

CAST(значение AS тип_для_конвертации);
CONVERT(значение, тип_для_конвертации);

Например,

SELECT CAST(12005.6 AS DECIMAL), CONVERT(12005.4, DECIMAL);
CAST(12005.6 AS DECIMAL) CONVERT(12005.4, DECIMAL)
12006 12005

Функция CAST умеет конвертировать переданное значение в любой из следующих типов:

Тип Описание
DATE Конвертирует значение в DATE. Формат: "YYYY-MM-DD".
DATETIME Конвертирует значение в DATETIME. Формат: "YYYY-MM-DD hh:mm:ss".
TIME Конвертирует значение в TIME. Формат: "hh:mm:ss".
DECIMAL[(M[,D])] Конвертирует значение в DECIMAL. Имеет два необязательных аргумента M и D, определяющих максимальное количество знаков до и после запятой соответственно. По умолчанию, D равен 0, а M равен 10.
CHAR[(N)] Конвертирует значение в CHAR. В качестве необязательного аргумента можно передать максимальную длину строки.
SIGNED Конвертирует значение в значение BIGINT.
UNSIGNED Конвертирует значение в беззнаковое значение BIGINT.
BINARY Конвертирует значение в BINARY.
YEAR Конвертирует значение в год.

Оконные функции SQL

Оконные функции — мощный инструмент языка SQL, позволяющий проводить сложные вычисления по группам строк, которые связаны с текущей строкой.

Принцип работы

Возможно, вы зададитесь вопросом: «Что значит оконные?».

В стандартном SQL-запросе все наборы строк рассматриваются как один сплошной блок данных, для которого и вычисляются агрегатные значения.

Однако, когда применяются оконные функции, запрос сегментируется на группы строк (или «окна»), и для каждого такого сегмента подсчитываются индивидуальные агрегатные значения.

Это окно, которое подаётся в оконную функцию, может быть:

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

Синтаксис оконной функции

SELECT <оконная_функция>(<поле_таблицы>)
OVER (
      [PARTITION BY <столбцы_для_разделения>]
      [ORDER BY <столбцы_для_сортировки>]
      [ROWS|RANGE <определение_диапазона_строк>]
)

Где:

  • <оконная_функция>(<поле_таблицы>) — используемая оконная функция. Например AVG(price).
  • Далее следует OVER, который определяет окно (группу строк), которое будет передаваться в оконную функцию. Если конструкцию OVER () оставить без параметров, то окном будет выступать вся таблица.

Далее внутри OVER следуют 3 необязательных параметра, с помощью которых можно гибко настраивать окно:

  • с помощью PARTITION BY <столбцы_для_разделения> выборка делится на непересекающиеся подмножества, где каждое подмножество содержит строки с одинаковыми значениями в одном или нескольких столбцах, образуются партиции.
  • с помощью ORDER BY <столбцы_для_сортировки> устанавливается порядок строк внутри окна, особо важную роль играет в оконных функциях ранжирования.
  • с помощью ROWS|RANGE <определение_диапазона_строк> формируются диапазоны строк. С помощью этого параметра можно указать сколько строк брать до и после текущей в окно.

:::info https://habr.com/ru/articles/664000/

:::

Понятие партиции

Партиции — подмножества строк, выделенные для оконной функции на основе одного или нескольких столбцов в таблице.

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

Применение партиций в SQL

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

SELECT <оконная_функция>(<поле_таблицы>)
OVER (
    PARTITION BY <столбцы_для_разделения>
)

Сортировка внутри окна

В предыдущих статьях мы рассмотрели оконные функции и партиционирование в SQL. Теперь давайте перейдём к ещё одной важной особенности оконных функций — сортировке внутри окна.

Зачем нужна сортировка в окне?

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

Основные оконные функции

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

Транзакции

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

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

Транзакция — это последовательность операций с базой данных, которые выполняются как единое целое.

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

Блокировки в СУБД

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

Блокировка

Блокировка — это метод ограничения доступа к данным для обеспечения корректной обработки транзакций.

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

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

Гранулярность блокировок

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

  • Блокировка таблиц\nНе позволяет нескольким пользователям одновременно изменять данные в одной таблице.
  • Блокировка страниц\nНе позволяет нескольким пользователям изменять данные в одной и той же странице (страница — это сегмент памяти, обычно в диапазоне от 2 до 16 Кбайт) таблицы одновременно.
  • Блокировка строк\nНе позволяет нескольким пользователям одновременно изменять одну и ту же строку в таблице.

Создание транзакций

Если вы попытаетесь перевести 1000 долларов с вашего сберегательного счёта на текущий и вдруг обнаружите, что деньги были списаны, но не зачислены на текущий счёт, вы, скорее всего, расстроитесь. 😿

Для защиты от такого рода ошибок программа, обрабатывающая ваш запрос на перевод денег, сначала начинает транзакцию, затем запускает SQL-запросы, необходимые для перевода денег с одного счёта на другой, и, если все проходит успешно, завершает транзакцию, выполнив команду COMMIT — фиксации изменений.

Однако, если возникнут какие-либо проблемы, будет выполнена команда ROLLBACK, которая указывает серверу отменить все действия, совершённые с начала транзакции.

Процесс может выглядеть следующим образом:

-- Начало транзакции
START TRANSACTION;

-- Проверка наличия достаточного баланса у отправителя
SELECT @balance := user_balance FROM accounts WHERE user_id = 1;

-- Если средств недостаточно, отмена транзакции
IF @balance < 1000 THEN
ROLLBACK;
END IF;

-- Проверка на существование получателя
SELECT @exists := COUNT(*) FROM accounts WHERE user_id = 2;
IF @exists = 0 THEN
ROLLBACK;
END IF;

-- Обновление баланса счетов, если все проверки пройдены
UPDATE accounts SET user_balance = user_balance - 1000 WHERE user_id = 1;
UPDATE accounts SET user_balance = user_balance + 1000 WHERE user_id = 2;

-- Применение изменений
COMMIT;

Запуск и завершение транзакций

Каждая явная транзакция в MySQL начинается с использования оператора START TRANSACTION.

Завершение же транзакции возможно:

  • с помощью команды COMMIT, которая даёт указание серверу пометить изменения как постоянные и освободить все ресурсы (т.е. блокировки строк), использовавшиеся во время транзакции
  • с помощью команды ROLLBACK, которая требует от сервера вернуть данные в состояние до начала транзакции. После завершения отката также любые ресурсы, используемые транзакцией, освобождаются.

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

Точки сохранения транзакции

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

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

SAVEPOINT my_savepoint;

Для отката к определённой точке сохранения просто вводится команда ROLLBACK, за которой следуют ключевые слова TO SAVEPOINT и имя точки сохранения, например:

START TRANSACTION;

-- Создаем точку сохранения перед изменением баланса первого пользователя
SAVEPOINT before_updating_user_1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 1;

-- Проверка условия для первого пользователя
-- например, проверяем логику бизнес-правил

-- Здесь мы предполагаем, что условие не выполнилось, и нам нужно отменить изменение баланса
ROLLBACK TO SAVEPOINT before_updating_user_1;

-- Обновляем баланс для второго пользователя
UPDATE accounts SET balance = balance + 200 WHERE user_id = 2;

-- Завершаем транзакцию
COMMIT;

В результате этой транзакции баланс первого пользователя останется без изменений из-за отката к точке сохранения, а баланс второго пользователя увеличится на 200. Это показывает, как можно управлять изменениями в базе данных с высоким уровнем контроля при помощи транзакций и точек сохранения.

Когда вы используете точки сохранения, помните следующие моменты:

  • Несмотря на название, при создании точки сохранения ничего не сохраняется. Чтобы ваши изменения в рамках транзакции стали постоянными, необходимо выполнить команду COMMIT.
  • При выполнении отката транзакции без указания конкретной точки сохранения, все ранее установленные точки сохранения будут проигнорированы, и будет произведён откат всей транзакции.