Преобразование факторизованного числа в десятичный в SQL Server

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

то есть. 11,16 - 11,5 в десятичной системе. Это связано с тем, что 16 в базе 32 и 11.16 следует читать как 11 + 16/32 = 11,5

Я получаю 11.16 как строку, и мне нужно изменить его на 11.5 как числовое значение в базе данных SQL Server 2005.

Какой-нибудь более короткий способ сделать это, а не разделять строки, преобразовывать в численные значения, математику, преобразовывать в строку, конкатенатировать и затем преобразовывать в числовые?

2 ответа

Как функция, которая не требует разделения строк:

CREATE FUNCTION MyFunc
(
 @value varchar(10)
)
RETURNS float
AS
BEGIN
 declare @dValue float
 declare @fraction float
 Select @dvalue = convert(float, @value)
 Select @fraction = @dvalue - Convert(int, @dvalue)
 Select @dvalue = (@dvalue - @fraction) + ((@fraction * 100) / 32)
 RETURN @dvalue
END

ПРИМЕЧАНИЕ: Это предполагает, что "8/32" представляется как 0,08, а не 0,8

Вы должны настроить "varchar (10)" в соответствии с тем, что вам действительно нужно.


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

  • много миллионов строк и
  • относительно немного разных значений.

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

UPDATE myOriginialTable
SET DecValue = F2D.DecVal
FROM myOriginalTable T
JOIN FactoredToDecimalTable F2D on T.FValue = F2D.FValue

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

CREATE TABLE Factored32ToDec
( F32 DECIMAL decimal(12,2),
 Dec10 DECIMAL (12,2)
)
INSERT INTO Factored32ToDec VALUES (0, 0)
INSERT INTO Factored32ToDec VALUES (0.01, 1.0/32)
INSERT INTO Factored32ToDec VALUES (0.02, 2.0/32)
INSERT INTO Factored32ToDec VALUES (0.03, 3.0/32)
INSERT INTO Factored32ToDec VALUES (0.04, 4.0/32)
-- etc. (24 rows omitted)
INSERT INTO Factored32ToDec VALUES (0.30, 30.0/32)
INSERT INTO Factored32ToDec VALUES (0.31, 31.0/32)
UPDATE myOriginialTable
SET DecValue = FLOOR(FValue) + F2D.Dec10
FROM myOriginalTable T
JOIN Factored32ToDec F2D ON F2D.F32 = (FValue - FLOOR(FValue))

По существу, эта идея предполагает, что поиск в таблице [явно кэшированный] Factored32ToDec будет быстрее, чем соответствующая операция деления. Я не уверен, что это так... это нужно будет профилировать/протестировать. С другой стороны, я видел первый подход к поиску (для всех различных значений), используемый. Это может быть довольно эффективным способом решения проблемы, но пробег, который вы выберете, варьируется в зависимости от общего количества строк и количества различных значений (а также наличия/отсутствия различных индексов и других соображений например, обновление одного столбца по сравнению с двумя отдельными столбцами и т.д.).

Примечание: пользователь FLOOR() подразумевает, что значение под рукой является числовым; в некоторых случаях входные данные - это просто строка (которая "полезна"... не спрашивайте меня, почему... для хранения значений без начального нуля, то есть "X.1" - это X + 1/32, тогда как "X.10" - X + 10/32). В зависимости от ситуации, поэтому, возможно, необходимо разбить строку в десятичной точке, а не использовать FLOOR. BTW, когда кодируется как строки ( ".1" против .01), малый подход к поисковой таблице экономит много тестов по сравнению с арифметическим подходом.

licensed under cc by-sa 3.0 with attribution.