SQL, Анализ данных

SQL Server: деление на 0 без ошибки

Время прочтения: 4 мин.

В процессе работы c данными в SQL Server, мы столкнулись с такой ситуацией. Одним из промежуточных шагов в нашей задаче было выполнение простого арифметического действия, из данных в виде целых чисел, загружаемых в таблицу SQL, значения которых   участвовали в делении. В какой-то момент времени выполнение всего кода могло быть прервано сообщением об ошибке из-за того, что знаменатель принимал значение 0. Как этого избежать подобной ситуации, я расскажу в этой статье.

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

declare @vol_1 int;
declare @vol_2 int;
set @vol_1 = 10;
set @vol_2 = 5;
select @vol_1/@vol_2 ratio_vol;

Соотношение значений вычисляется корректно:

При значении @vol_2 = 0 получаем сообщение об ошибке:

declare @vol_1 int;
declare @vol_2 int;
set @vol_1 = 10;
set @vol_2 = 0;
select @vol_1/@vol_2 ratio_vol;

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

1. Применение функции NULLIF.

Синтаксис NULLIF следующий:

NULLIF(expr1, expr2)

При равенстве значений двух аргументов, возвращается значение NULL.

Например:

select NULLIF (55, 55) result;

Результат запроса:

Если значения аргументов не равны, возвращается значение первого аргумента (expr1).

select NULLIF (12, 55) result;

Изменим этот запрос, добавив в него NULLIF, для обхода ошибки деления на ноль.

Логика использования функции NULLIF для задачи деления на ноль следующая:

  • используем в знаменателе функцию NULLIF с нулевым значением ее второго аргумента
  • если значение первого аргумента функции NULLIF также равно нулю, то возвращается значение NULL, и тогда в SQL Server, если мы разделим число на значение NULL, на выходе получим NULL.
  • если значение первого аргумента не равно нулю, возвращается значение первого аргумента функции NULLIF, и деление выполняется как стандартная операция деления.
declare @vol_1 int;
declare @vol_2 int;
set @vol_1 = 10;
set @vol_2 = 0;
select @vol_1/NULLIF (@vol_2, 0) ratio_vol;

Ниже, результат работы такого кода (в знаменателе – значение NULL):

Добавим в код функцию ISNULL, для того, чтобы вместо значения NULL в выводе результата вычисления получать 0.

Эта функция заменяет NULL значение в expr1 и возвращает значение expr2 в качестве вывода.

Логика запроса с функциями ISNULL и NULLIF такая:

  • первый аргумент ((@vol_1/ NULLIF (@vol_2,0)) вернет значение NULL;
  • для функции ISNULL указываем нулевое значение второго аргумента;
  • так как первый аргумент — NULL, то вывод всего запроса равен нулю, т.е. значению второго аргумента.

Пример кода с функциями ISNULL и NULLIF:

declare @vol_1 int;
declare @vol_2 int;
set @vol_1 = 10;
set @vol_2 = 0;
select ISNULL (@vol_1/NULLIF (@vol_2, 0),0) ratio_vol;

Вывод результата успешного выполнения запроса:

2. Использование оператора CASE.

Посмотрим, как использовать для нашей задачи оператор CASE для возврата значений на основе определенных условий.

Оператор CASE проверит значение параметра @vol_2:

  • если значение @vol_2 равно нулю, возвращается значение NULL;
  • если это условие не выполняется, то производится арифметическая операция деления (@vol_1/@vol_2) и возвращается ее результат.
declare @vol_1 int;
declare @vol_2 int;
set @vol_1 = 10;
set @vol_2 = 0;
select CASE
	    when @vol_2 = 0
	    then NULL
	    else @vol_1/@vol_2
       end as ratio_vol;

Успешный результат запроса:

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

Советуем почитать