Трудности округления в MS SQL Server

    Доброго дня, хабровчане! Пришлось мне в проекте столкнуться с точностью вычислений в MS SQL Server и я обнаружил не совсем интуитивное поведение при выполнении казалось бы интуитивных операций.

    Для затравки вопрос (попробуйте ответить на него, не выполняя):
    Каков будет результат операции?
    declare @var1 decimal(38,10) = 0.0000007,
            @var2 decimal(38,10) = 1;
    select @var1 * @var2;
    

    Ответ и объяснение под катом


    Итак, сначала ответ: 0.000001

    Какого?

    На самом деле ответ достаточно прост, но от этого не легче. Все дело в том, что при выполнении арифметических операций с десятичными числами результат может быть сильно больше исходных значений, например, если умножить 10^6 и 10^6, то получим 10^12. Это аж на 6 разрядов больше, чем исходные значения. Аналогично и с делением. Поэтому MS SQL при вычислении типа результирующего выражения применяет следующие правила:
    Operation
    Result precision
    Result scale *
    e1 + e2
    max(s1, s2) + max(p1-s1, p2-s2) + 1
    max(s1, s2)
    e1 — e2
    max(s1, s2) + max(p1-s1, p2-s2) + 1
    max(s1, s2)
    e1 * e2
    p1 + p2 + 1
    s1 + s2
    e1 / e2
    p1 — s1 + s2 + max(6, s1 + p2 + 1)
    max(6, s1 + p2 + 1)
    e1 { UNION | EXCEPT | INTERSECT } e2
    max(s1, s2) + max(p1-s1, p2-s2)
    max(s1, s2)
    e1 % e2
    min(p1-s1, p2 -s2) + max( s1,s2 )
    max(s1, s2)

    * precision и scale результата имеют абсолютный максимум, равный 38. Если значение precision превышает 38, то соответствующий scale уменьшается, чтобы по возможности предотвратить усечение интегральной части результата.

    В документации нет подробного описания как происходит округление и до каких пределов, но экспериментально у меня не получилось достичь округления больше, чем decimal(38,6).

    Отсюда и результат выражения в начале: 0.000001 ровно 6 знаков после запятой. Чтобы не быть голословным, выполним следующий запрос:
    declare @var1 decimal(38,10) = 0.0000007,
            @var2 decimal(38,10) = 1,
            @res sql_variant;
    set @res = @var1 * @var2;
    select @res, 
           SQL_VARIANT_PROPERTY(@res, 'BaseType') as BaseType, 
           SQL_VARIANT_PROPERTY(@res, 'Precision') as Precision,
           SQL_VARIANT_PROPERTY(@res, 'Scale') as Scale;
    

    Получим следующий результат:
    res
    BaseType
    Precision
    Scale
    0.000001
    decimal
    38
    6

    Как же с этим жить?

    Придется с этим смириться и всегда (абсолютно всегда!) очень деликатно выставлять точность. В нашем случае вот такой скрипт вернет ожидаемый результат:
    declare @var1 decimal(18,10) = 0.0000007,
            @var2 decimal(18,10) = 1,
            @res sql_variant;
    set @res = @var1 * @var2;
    select @res, 
           SQL_VARIANT_PROPERTY(@res, 'BaseType') as BaseType, 
           SQL_VARIANT_PROPERTY(@res, 'Precision') as Precision,
           SQL_VARIANT_PROPERTY(@res, 'Scale') as Scale;
    

    res
    BaseType
    Precision
    Scale
    0.00000070000000000000
    decimal
    37
    20

    Вместо послесловия

    И на последок еще немного sql-магии. Что будет в результате выполнения вот такого скрипта:
    declare @var1 decimal(38,10) = 0.0000007,
            @var2 int = 1;
    select @var1 * @var2, @var1 * 1;
    

    Ответ
    @var1 * @var2 = 0.000001
    @var1 * 1 = 0.00000070


    PS: так же надо быть внимательным с операциями аггрегации, потому что они тоже меняют точность результата. Вот хорошая статья, описывающая проблему.
    AdBlock has stolen the banner, but banners are not teeth — they will be back

    More
    Ads

    Comments 11

      +1
      Что может быть прекраснее:

      declare
        var1 number := 0.0000007;
        var2 number := 1;
      begin
        dbms_output.put_line(to_char(var1 * var2));
      end;
      
      -- result: .0000007
      
        0
        Эх… если бы СУБД выбирали разработчики. Мечты, мечты…
          +1
          1) 0.0[29]7
          2) 0.0[30]7
          select 0.000000000000000000000000000007*1;
          
          -- result 0.000000000000000000000000000007
          
          select 0.0000000000000000000000000000007*1;
          
          -- result 0.000000000000000000000000000001
          


          mysql 5.5.34
          0
          А почему бы не работать с float?

          declare @var1 float = 0.0000007,
          @var2 float = 1;
          select @var1 * @var2;

          7E-07
            +3
            Потому что система банковская, а деньги во float считать нельзя, ибо там появляются другие проблемы округления.
              0
              =) Вот так и знал, что вы сейчас так скажете.
                –1
                Во всех виденных мною банковских системах все деньги хранились в целых числах. В виде стотысячных единиц. И никаких проблем с округлениями — что в Оракле, что в МС СКЛ :)
                  +4
                  Не все числа можно сделать целыми. Банковская система — это не только деньги, а еще и проценты и алгоритмы их вычисления. А в алгоритмах присутствует возведение в дробную степень, деление и другие операции.
                    0
                    А вы попробуйте модные ныне биткоины хранить в целых числах :)
                      +2
                      «Ви-таки будете смеяться», но они в целых числах и хранятся :) Об этом в доке для разработчика написано прямым текстом.
                        0
                        Ок, вы выиграли.

              Only users with full accounts can post comments. Log in, please.