Как SQL Server хранит целочисленные значения размером более 2 ^ 24 в столбце FLOAT (53)?
У меня есть база данных SQL Server, из которой я могу читать целочисленные значения (путь) более 2 ^ 24 из столбца с типом данных float. Это меня смущает, поскольку эти значения должны быть слишком большими/длинными, чтобы их можно было точно представить как число с плавающей запятой. Мне удалось создать копию в моей локальной базе данных.
Насколько мне известно, 16777216 (=2^24) и 16777217 должны иметь одинаковое двоичное представление с плавающей запятой. Они оба сопоставлены с 0 10010111 00000000000000000000000.
Теперь подумайте об этом:
DROP TABLE IF EXISTS F_ING_FLOATS;
CREATE TABLE F_ING_FLOATS (COL FLOAT(53));
INSERT INTO F_ING_FLOATS
VALUES (16777216), (16777217);
Поскольку я ожидаю, что оба значения отображаются в одно и то же число с плавающей запятой, я также ожидаю, что различие между ними потеряется в БД. Однако запрос
SELECT
COL,
COL + 1 [COL + 1],
COL + 2 [COL + 2]
FROM
F_ING_FLOATS;
досадно возвращает правильные значения:
COL COL + 1 COL + 2
------------------------------------------
16777216 16777217 16777218
16777217 16777218 16777219
Я ожидал увидеть 16777216 повсюду в первых двух столбцах и 16777218 в обеих строках последнего столбца.
Что здесь происходит такого, что находится за пределами моего понимания? Разве SQL Server на самом деле не хранит 32-битные числа с плавающей запятой, хотя я определил COL FLOAT(53)?
Причина, по которой для меня это проблема, заключается в том, что значения из базы данных SQL Server загружаются в паркеты в озеро данных Azure. В озере данных я вижу именно то, что ожидаю: COL имеет две строки, обе со значением 16777216.
Я нахожусь в процессе понимания того, что на самом деле происходит. И я подозреваю, что SQL Server не совсем честен, когда говорит, что COL есть FLOAT(53).
Чтобы подробнее объяснить мое замешательство: я использую этот онлайн-конвертер, чтобы узнать, что такое внутреннее представление. Он сообщает мне, что 16777216 и 16777217 имеют одинаковое представление, что должно объяснить, почему паркеты в моем озере данных не могут их различить (или, по крайней мере, я думаю, что это объясняет это).
@ThomA, разве я не говорю прямо, что не ожидаю точно сохраненных значений? Я знаю, что числа с плавающей точкой являются приближениями, и ожидаю, что эти два целых числа будут иметь одинаковое приближение.
Кроме того, float имеет точность до 15 цифр, а не 8. Его размер составляет 8 байт, что составляет 64 бита. real (float(24)) — 32 бита.
Внутреннее представление обычно соответствует стандарту IEEE 754 . Несмотря на название float, с 53 битами это двойной точности. А с 53-битной точностью вы можете получить до 2⁵³ = 9007199254740992, прежде чем начнете терять цифры слева от десятичной точки.
@SteveSummit, понятно. Поэтому я ошибочно предположил, что числа будут сохранены в 32-битном формате. На самом деле они хранятся в 64-битном формате, что объясняет, почему они различимы. Проблема с моей копией в озере данных, вероятно, заключается в следующем: SQL Server использует двойную точность, а озеро данных использует одинарную точность.
@ThomA, ты согласен с моим последним комментарием?
Я ничего не знаю об озере данных, @gebruiker, поэтому не могу комментировать.
@gebruiker «...Я ожидаю, что эти два целых числа будут иметь одинаковое приближение...» — не ожидайте, что приблизительный тип данных будет обеспечивать точную точность. Даже если сегодня это работает хорошо, в следующей версии базы данных это может не работать. Я бы не стал основывать бизнес-логику на таком предположении.
Ответы 1
Для FLOAT(53) SQL использует форматbinary64 IEEE-754, который имеет 53-битное значение. Этот формат позволяет различать 16 777 216 и 16 777 217.
Для FLOAT(24) будет использоваться форматbinary32, и этот формат не может различать 16 777 216 и 16 777 217, поскольку он не может представлять 16 777 217.
(Обратите внимание, что 16 777 216 и 16 777 217 «не имеют одинакового двоичного представления с плавающей запятой». Скорее, 16 777 217 вообще не имеет никакого представления. Происходит следующее: когда 16 777 217 преобразуется в формат двоичный32, из текста или из другого числового формата , получается округленный результат — ровно 16 777 216; это различие имеет решающее значение для правильного анализа, понимания, проектирования и доказательства операций с плавающей запятой. числовые операции, но представленные значения являются точными.)
Это превосходное объяснение. Знаете ли вы, надежно ли округление? Т.е. Могу ли я с уверенностью предположить, что 16 777 217 всегда округляется в меньшую сторону при преобразовании в bin32, или его с таким же успехом можно округлить до 16 777 218?
@gebruiker: Привязка к четному округлению до ближайшего полностью определяется стандартом IEEE 754; результат всегда один и тот же во всех реализациях, соответствующих IEEE 754. Метод округления до ближайшего привязки к чету используется по умолчанию почти во всех реализациях с плавающей запятой, но я не знаю, что SQL указывает на то, будет ли его необходимо использовать.
«Это меня смущает, поскольку эти значения должны быть слишком большими/длинными, чтобы их можно было точно представить как число с плавающей запятой». Проблема в том, что вы предполагаете, что SQL Server будет точно хранить значение, когда вы доберетесь до таких больших цифр. В документации четко указано, что значение является «типом данных приблизительного числа»; как только вы начнете получать очень маленькое/большое значение, вы потеряете точность. Если вам нужны точные цифры,
floatникогда не будет правильным выбором; Числа с основанием 2 и 10 не «играют хорошо».