SQL-Ex blog

Рассмотрим такой пример: 0.1 + 0.2 должно давать 0.3, правильно? Но проверьте вывод при использовании типа данных SQL FLOAT.

DECLARE @f1 FLOAT = 0.1

DECLARE @f2 FLOAT = 0.2

SELECT CASE WHEN @f1 + @f2 = .3 THEN 1 ELSE 0 END

Правильным результатом является 1. Но посмотрите на рисунок 1.


Рис.1 Использование SQL FLOAT, 0.1 + 0.2 не равно 0.3!

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

Есть над чем поработать. Нужно начать с того, что представляет собой тип данных FLOAT.

Что это за тип данных SQL FLOAT?

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

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

Как объявить тип данных SQL FLOAT

Синтаксис - FLOAT[(n)], где n - число бит, используемых для хранения мантиссы числа с плавающей точкой в научной нотации. Этим также определяется точность и размер хранилища. Возможными значениями для n являются числа от 1 до 53. Заметим, что параметр n является необязательным.

Пример:

DECLARE @floatValue1 FLOAT;   -- переменная Float без числа бит

DECLARE @floatValue2 FLOAT(3) -- переменная Float с 3 битами 

Если n не указывается, по умолчанию принимается 53. Это также является максимальным значением. Кроме того, FLOAT(53) - это число двойной точности с плавающей точкой или binary64. Вместо указания FLOAT(53), вы также можете объявить его как DOUBLE PRECISION.

Следующие 3 объявления функционально эквивалентны:

DECLARE @double1 FLOAT(53); 

DECLARE @double2 FLOAT;

DECLARE @double3 DOUBLE PRECISION;

В таблице показано число бит и соответствующий размер хранилища.

SQL FLOAT и REAL - это одно и то же?

REAL - это FLOAT(24). Это также называется одинарной точностью или binary32.

Почему это важно знать

Знание того, что это приближенный числовой тип, остановит вас от использования его для вычислений, требующих точности. Вас также волнует хранение и память? Используйте REAL или FLOAT(24), если вам не нужны очень большие или очень малые значения.

Какая разница между FLOAT и DECIMAL?

FLOAT является приближенным числовым типом. DECIMAL - это точный числовой тип. Вот сводка различий:



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

DECLARE @d1 DECIMAL(2,1) = 0.1

DECLARE @d2 DECIMAL(2,1) = 0.2

SELECT CASE WHEN @d1 + @d2 = 0.3 THEN 1 ELSE 0 END 

Использование оператора "не равно" также вызывает проблемы. Проверьте нижеприведенный цикл.

DECLARE @floatValue FLOAT(1) = 0.0

WHILE @floatValue <> 5.0

BEGIN

	PRINT @floatValue;

	SET @floatValue += 0.1;

END 

Что вы думаете? Посмотрите рисунок 2 ниже.


Рис.2 Использование FLOAT в качестве счетчика приводит к бесконечному циклу.

Бум! Бесконечный цикл! Условие неравенства всегда будет true. Поэтому логично изменить тип данных на DECIMAL.

DECLARE @decimalValue DECIMAL(2,1) = 0.0

WHILE @decimalValue <> 5.0

BEGIN

	PRINT @decimalValue;

	SET @decimalValue += 0.1;

END

Вышеприведенный код будет четко останавливаться, когда @decimalValue равна 5.0. Посмотрите на рисунке 3 ниже.


Рис.3 Цикл останавливается при использовании DECIMAL, когда условие неравенства больше не true.

Отлично! Но если вы настаиваете на FLOAT, следующий код будет исправно работать, не входя в бесконечный цикл.

DECLARE @floatValue FLOAT(1) = 0.0

WHILE @floatValue < 5.0

BEGIN

	PRINT @floatValue;

	SET @floatValue += 0.1;

END

Между тем, округление тоже отказывает. Посмотрите следующий код:

DECLARE @value FLOAT(2) = 1.15

SELECT ROUND(@value, 1)  -- Результатом будет 1.1

Вместо 1.20 этот код дает 1.1. Но если использовать DECIMAL, результат будет правильным.

DECLARE @value DECIMAL(3,2) = 1.15

SELECT ROUND(@value, 1)  -- Будет получено 1.2 или 1.20

Когда FLOAT дает правильный результат, а DECIMAL - нет

Разве точные числа НЕ всегда точны? Для воспроизведения проблемы мы будем использовать вычисления, а затем обращать их. Сначала подготовим данные.

CREATE TABLE ExactNumerics1

(

	fixed1 DECIMAL(8,4),

	fixed2 DECIMAL(8,4),

	fixed3 DECIMAL(8,4),

	calcValue1 AS fixed3 / fixed1 * fixed2

)

GO

INSERT INTO ExactNumerics1

(fixed1,fixed2,fixed3)

VALUES

(54,0.03,1*54/0.03)

Вышеприведенная таблица будет использовать фиксированные значения для первых двух столбцов. Третий столбец будет вычисляться. Наконец, четвертый столбец, который является вычисляемым, выполняет обратное вычисление. Правильным результатом вычисляемого столбца должно быть 1.

Теперь для сравнения с FLOAT создадим подобную таблицу и данные.

CREATE TABLE ApproxNumerics1

(

	float1 FLOAT(2),

	float2 FLOAT(2),

	float3 FLOAT(2),

	calcValue1 AS float3 / float1 * float2 

)

INSERT INTO ApproxNumerics1

(float1, float2, float3)

VALUES

(54,0.03,1*54/0.03)

Теперь запрос.

SELECT * FROM ApproxNumerics1

SELECT * FROM ExactNumerics1

Результаты? Смотрите рисунок 4.


Рис.4 Обратное вычисление показывает, что FLOAT дает правильный результат, а DECIMAL - нет.

Что произошло? FLOAT дал правильный ответ, а DECIMAL - нет. Что-то сделано неверно.

Опять неявное преобразование

Неявное преобразование происходит потому, что SQL прощает. Когда при вычислении используются данные разных типов, SQL Server пытается преобразовать это у нас за спиной, выполняя неявное преобразование.

Действительно ли было выполнено преобразование? Помимо прочего, каждый столбец в таблице ExactNumerics1 имеет тип DECIMAL.

Давайте проверим структуру таблицы ExactNumerics1 в SQL Server Management Studio:


Рис.5 Вычисляемый столбец имеет тип DECIMAL(30,17), а не DECIMAL(8,4).

Обратите внимание на красный прямоугольник на рисунке 5. Вычисляемый столбец имеет тип DECIMAL(30,17), а не DECIMAL(8,4). Согласно официальной документации, два столбца DECIMAL с различными точностью и масштабом являются двумя различными типами данных. Посмотрите здесь. Из-за разницы требуется преобразование. Поэтому происходит неявное преобразование.

Что если они различны, и произошло неявное преобразование?

Опять таки на основании официальной документации при неявном преобразовании может произойти потеря точности или масштаба. Таким образом, требуется явное преобразование CAST.

Здесь просто произошла некоторая потеря. Если вычисляемый столбец имеет также тип DECIMAL(8,4), неявное преобразование не происходит.

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

CREATE TABLE ExactNumerics2

(

	fixed1 DECIMAL(8,4),

	fixed2 DECIMAL(8,4),

	fixed3 DECIMAL(8,4),

	calcValue1 AS CAST(fixed3 / fixed1 * fixed2 AS DECIMAL(8,4)) -- явный CAST

)

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


Рис.6 Теперь результаты для FLOAT и DECIMAL одинаковы.

Итак, точные числа могут оказаться неточными, если имеет место неявное преобразование между 2 и более значениями DECIMAL.

Почему эти знания важны

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

Следует ли использовать SQL FLOAT для финансовых данных?

При вычислении процентов секторов на круговой диаграмме сумма должна составлять 100%. Итоговые данные и детализированные отчеты также должны быть согласованы. Если точность результатов является ключевым моментом, приближенные типы данных, подобные FLOAT, не должны использоваться. Для этого логично выбирать DECIMAL.

Но вопросы остаются.

Когда следует использовать FLOAT?

Используйте FLOAT для данных, которые требуют астрономических значений типа расстояния между галактиками. При этом тип данных DECIMAL будет приводить к арифметическому переполнению для данных такого типа. Небольшие значения типа диаметра атомного ядра также подходят для использования FLOAT. Научные данные и другие значения, которые не требуют точности могут также с пользой использовать FLOAT.

Почему важно это знать

Мы не говорим, что FLOAT - плохой, а DECIMAL - хороший и наоборот. Знание правильного использования каждого типа даст вам и вашим пользователям надежные результаты. Ну, вы же хотите сделать ваших пользователей счастливыми, правильно?

Заключение

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