Как избежать ошибки деления на ноль в SQL?

Чтобы избежать ошибки «Деление на ноль», мы запрограммировали это так:

Select Case when divisor=0 then null
Else dividend / divisor
End ,,,

Но вот способ сделать это гораздо лучше:

Select dividend / NULLIF(divisor, 0) ...

Теперь единственная проблема - запомнить бит NullIf, если я использую клавишу «/».

Если вы хотите вернуть ноль, в случае, если произойдет нулевое отклонение, вы можете использовать:

SELECT COALESCE(dividend / NULLIF(divisor,0), 0) FROM sometable

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

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

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

SELECT club_id, males, females, males/females AS ratio
  FROM school_clubs;

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

Перепишите запрос как:

SELECT club_id, males, females, males/NULLIF(females, 0) AS ratio
  FROM school_clubs;

Любое число, разделенное на NULL, дает NULL, и никаких ошибок не возникает.

Вы также можете сделать это в начале запроса:

SET ARITHABORT OFF 
SET ANSI_WARNINGS OFF

Поэтому, если у вас есть что-то вроде 100/0, он вернет NULL. Я сделал это только для простых запросов, поэтому не знаю, как это повлияет на более длинные / сложные.

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

SELECT a / NULLIF(b, 0) FROM t 

Однако я бы НИКОГДА не преобразовал это значение в ноль с помощью coalesce, как показано в том другом ответе, который получил много голосов. Это совершенно неверно с математической точки зрения и даже опасно, поскольку ваше приложение, скорее всего, вернет неправильные и вводящие в заблуждение результаты.

РЕДАКТИРОВАТЬ: Я получаю много отрицательных голосов по этому поводу в последнее время ... поэтому я подумал, что просто добавлю примечание, что этот ответ был написан до того, как вопрос подвергся самому последнему редактированию, где возвращение null было выделено как вариант .. ., что кажется очень приемлемым. Часть моего ответа была адресована таким опасениям, как Эдвардо в комментариях, который, казалось, выступал за возврат 0. Это тот случай, против которого я выступал.

ОТВЕТ: Я думаю, здесь есть основная проблема: деление на 0 незаконно. Это признак того, что что-то не так. Если вы делите на ноль, вы пытаетесь сделать что-то, что не имеет математического смысла, поэтому никакой числовой ответ, который вы можете получить, не будет действительным. (Использование null в этом случае является разумным, поскольку это значение не будет использоваться в более поздних математических вычислениях).

Итак, Эдвардо спрашивает в комментариях: «А что, если пользователь поставит 0?», И выступает за то, чтобы получить 0 взамен было нормально. Если пользователь ставит ноль в сумму, и вы хотите, чтобы 0 возвращался, когда они это делают, тогда вам следует ввести код на уровне бизнес-правил, чтобы поймать это значение и вернуть 0 ... нет какого-то особого случая, когда деление на 0 = 0.

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

Представьте, что я что-то кодирую, и я облажался. Я должен был прочитать масштабное значение измерения радиации, но в странном крайнем случае, которого я не ожидал, я прочитал 0. Затем я опускаю свое значение в вашу функцию ... вы возвращаете мне 0! Ура, без радиации! За исключением того, что это действительно так, и я просто передавал плохую ценность ... но я понятия не имею. Я хочу, чтобы деление выдавало ошибку, потому что это признак того, что что-то не так.

SELECT Dividend / ISNULL(NULLIF(Divisor,0), 1) AS Result from table

Поймав ноль с помощью nullif (), а затем получившийся нуль с помощью isnull (), вы можете обойти свою ошибку деления на ноль.

Замена «делить на ноль» на ноль спорна, но это также не единственный вариант. В некоторых случаях замена на 1 (разумно) уместна. Я часто использую

 ISNULL(Numerator/NULLIF(Divisor,0),1)

когда я смотрю на сдвиги в счетах / счетах и ​​хочу установить по умолчанию 1, если у меня нет данных. Например

NewScore = OldScore *  ISNULL(NewSampleScore/NULLIF(OldSampleScore,0),1) 

Чаще всего я фактически вычислял это соотношение где-то еще (не в последнюю очередь потому, что он может привести к очень большим поправочным коэффициентам для низких знаменателей. В этом случае я бы обычно проверял, что OldSampleScore больше порогового значения; что затем исключает нулевое значение). Но иногда уместно «взломать».

Некоторое время назад я написал функцию для ее обработки для моих хранимых процедур:

print 'Creating safeDivide Stored Proc ...'
go

if exists (select * from dbo.sysobjects where  name = 'safeDivide') drop function safeDivide;
go

create function dbo.safeDivide( @Numerator decimal(38,19), @divisor decimal(39,19))
   returns decimal(38,19)
begin
 -- **************************************************************************
 --  Procedure: safeDivide()
 --     Author: Ron Savage, Central, ex: 1282
 --       Date: 06/22/2004
 --
 --  Description:
 --  This function divides the first argument by the second argument after
 --  checking for NULL or 0 divisors to avoid "divide by zero" errors.
 -- Change History:
 --
 -- Date        Init. Description
 -- 05/14/2009  RS    Updated to handle really freaking big numbers, just in
 --                   case. :-)
 -- 05/14/2009  RS    Updated to handle negative divisors.
 -- **************************************************************************
   declare @p_product    decimal(38,19);

   select @p_product = null;

   if ( @divisor is not null and @divisor <> 0 and @Numerator is not null )
      select @p_product = @Numerator / @divisor;

   return(@p_product)
end
go

1. Добавьте ограничение CHECK, которое заставляет Divisor быть ненулевым
2. Добавьте в форму валидатор, чтобы пользователь не мог вводить в это поле нулевые значения.

Для обновления SQL:

update Table1 set Col1 = Col2 / ISNULL(NULLIF(Col3,0),1)

Нет никакой волшебной глобальной настройки «выключить деление на 0 исключений». Операция должна быть выбрана, поскольку математическое значение x / 0 отличается от значения NULL, поэтому она не может вернуть NULL. Я предполагаю, что вы заботитесь об очевидном, и в ваших запросах есть условия, которые должны исключать записи с делителем 0 и никогда не оценивать деление. Обычная «ошибка» - это то, что большинство разработчиков ожидают от SQL, чтобы вести себя как процедурные языки и предлагать короткое замыкание логического оператора, но это НЕ. Я рекомендую вам прочитать эту статью: http://www.sqlmag.com/Articles/ArticleID/9148/pg/2/2.html

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

Я собираюсь подсчитать количество оборачиваемости запасов за трехмесячный период. Я подсчитал, что у меня стоимость проданных товаров за трехмесячный период составляет 1000 долларов. Годовая норма продаж составляет 4000 долларов США (1000 долларов США / 3 шт.) * 12. Начальный запас равен 0. Конечный запас равен 0. Мой средний запас сейчас равен 0. У меня продажи составляют 4000 долларов в год, а запасов нет. Это дает бесконечное количество оборотов. Это означает, что весь мой инвентарь конвертируется и покупается покупателями.

Это бизнес-правило расчета оборачиваемости запасов.

Отфильтруйте данные с помощью предложения where, чтобы не получить 0 значений.

Иногда 0 может не подходить, но иногда и 1 не подходит. Иногда скачок от 0 до 100000000, описываемый как 1 или 100-процентное изменение, также может вводить в заблуждение. В этом сценарии может быть уместно 100000000 процентов. Это зависит от того, какие выводы вы собираетесь сделать на основе процентов или соотношений.

Например, очень мелко продаваемый предмет, проданный с 2 ​​до 4, и очень крупный предмет, который продается с 1 000 000 до 2 000 000 проданных, могут означать совсем разные вещи для аналитика или руководства, но и то, и другое будет реализовано как 100% или 1. изменение.

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

CASE
     WHEN [Denominator] = 0
     THEN NULL --or any value or sub case
     ELSE [Numerator]/[Denominator]
END as DivisionProblem

Вот как я это исправил:

IIF(ValueA != 0, Total / ValueA, 0)

Его можно завернуть в апдейт:

SET Pct = IIF(ValueA != 0, Total / ValueA, 0)

Или в избранном:

SELECT IIF(ValueA != 0, Total / ValueA, 0) AS Pct FROM Tablename;

Мысли?

Вы можете обработать ошибку соответствующим образом, когда она передается обратно в вызывающую программу (или игнорировать ее, если вы этого хотите). В C # любые ошибки, возникающие в SQL, вызовут исключение, которое я могу перехватить и затем обработать в своем коде, как и любую другую ошибку.

Я согласен с Beska в том, что вы не хотите скрывать ошибку. Возможно, вы имеете дело не с ядерным реактором, но в целом скрывать ошибки - плохая практика программирования. Это одна из причин, по которой большинство современных языков программирования реализуют структурированную обработку исключений, чтобы отделить фактическое возвращаемое значение от кода ошибки / состояния. Это особенно верно, когда вы занимаетесь математикой. Самая большая проблема заключается в том, что вы не можете различить правильно вычисленный 0, возвращаемый в результате ошибки. Вместо этого любое возвращаемое значение является вычисленным значением, и если что-то пойдет не так, выдается исключение. Это, конечно, будет отличаться в зависимости от того, как вы обращаетесь к базе данных и какой язык вы используете, но вы всегда должны иметь возможность получить сообщение об ошибке, с которым вы можете справиться.

try
{
    Database.ComputePercentage();
}
catch (SqlException e)
{
    // now you can handle the exception or at least log that the exception was thrown if you choose not to handle it
    // Exception Details: System.Data.SqlClient.SqlException: Divide by zero error encountered.
}

Используйте NULLIF(exp,0), но так - NULLIF(ISNULL(exp,0),0)

NULLIF(exp,0) прерывается, если exp равно null, но NULLIF(ISNULL(exp,0),0) не прерывается