Большие погрешности...

[AnryGTR]

Здравствуйте уважаемые гуру этого форума!
Проблемка с вычислениями. FireBird 1.5.3. Есть следующие таблицы:

Код: Выделить всё

юзеры     - ID, FIO,...
проценты - id_юзера, процент, дата_начал_действия, дата_конца_действия
заказы    - ID, id_Юзера, оплата, скидка, ID_услуги, дата,...
прайс      - ID_услуги, цена, дата_начал_действия, дата_конца_действия

Так вот мне нужно посчитать скока бабла в заданный период(между d1 и d2) будут иметь юзеры, у которых соответственно имеются проценты в таблице "Проценты", с общих поступлений, не учитывая те заказы которые выполнял юзер под номером 19 и 20, пишу

следующее:

Код: Выделить всё

  select ю.ID,
   min(ю.FIO),
   cast(sum((п.цена - п.цена/100*з.скидка)*з.оплата/100*проц.процент) as numeric(10,2))
   from заказы з 
   left join проценты проц
   on(з.дата >= проц.дата_начала_действия and з.дата <= проц.дата_конца_действия and 
   з.оплата = 1 and проц.процент > 0 and з.id_юзера <> 19 and з.id_юзера <> 20 and 
   з.дата >= d1 and з.дата <= d2)
  left join юзеры ю
  on(проц.id_юзера = ю.id)
  left join прайс п 
  on(з.id_услуги = п.id_услуги and з.дата>=п.дата_начала_действия and 
   з.дата<=п.дата_конца_действия)
  where ю.категория = 1 and ю.id <> 19 and ю.id <> 20 
  group by ю.id

В итоге, если сверить полученные суммы, то они почему-то "немного" неверны, т.е. если в другом отчёте посмотреть общую сумму которую принесли заказы, и отнять от неё общую сумму заказов выполненных юзерами 19 и 20, а затем вычислить соответствующий процент, то полученные цифры почему-то "расходятся" с цифрами из вышеописанного запроса. Процент пока что у всех один и тот же, чтобы легче было проверить. Например, при подсчёте в ручную юзер N имеет сумму 545380,85
а запрос выдаёт мне сумму 545398,35
или например в ручную - 389557,75
а запрос выдаёт - 389570,25...
Типы полей:

Код: Выделить всё

 прайс.цена       - numeric(10,2)
 заказы.скидка    - SMALLINT
 проценты.процент - numeric(2,2)

Вроде бы все формулы правильно написаны, да и текст запроса вроде тож, а почему такие погрешности?
пробовал убрать cast не помогло...

[Merlin]

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

[AnryGTR]

не ну понимаю погрешности в 1-2 рублика, но не такие же или и такие могут быть из-за приведения типов и т.п.?

[WildSery]

не ну понимаю погрешности в 1-2 рублика, но не такие же или и такие могут быть из-за приведения типов и т.п.?

Это как повезёт. Может даже и сойтись, если цены "прилижешь" под проценты свои.
Беда в том, что вычисление в каждой строке с округлением практически никогда (кроме указанного случая) не сойдётся с суммированием, а уже потом округлением.

Правь консерваторию. Храни к примеру сразу вычисленную построчно для суммирования без вычислений.
Это как с НДС - та же байда.

[Dimitry Sibiryakov]

(п.цена - п.цена/100*з.скидка)*з.оплата/100*проц.процент

Надеюсь, ты понимаешь, что тут при делении идет округление? Наставь скобок так, чтобы сначала все умножалось, а только потом делилось.

[WildSery]

Наставь скобок так, чтобы сначала все умножалось, а только потом делилось.

Не поможет. Всё равно округление построчное будет.
И с суммой, домноженной на тот же процент, не сойдётся.

[AnryGTR]

2 Dimitry Sibiryakov:
переписал формулу ваще без деления - результат тот же...

Странно - заменил as numeric(10,2) на as integer и погрешность стала оч маленькой, НО всё равно есть... как же быть?

2 WildSery:

Правь консерваторию. Храни к примеру сразу вычисленную построчно для суммирования без вычислений.

что-то не понял - как это? можно ли небольшой примерчик?

Проблема конечно не решена(пока) - рою литературу, но всё равно всем спасибо за советы...

[kdv]

заменил as numeric(10,2) на as integer и погрешность стала оч маленькой

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

[AnryGTR]

диалект 3-й...

[kdv]

в любом случае, последовательность вычислений и количество переменных влияют на округление.
Типичный пример про НДС 18% уже привели.
То есть, бывают такие числа, когда будет погрешность если НДС накидывать сверху, или наоборот, НДС вычитать из суммы.
Еще хуже когда вроде бы все с туда-обратно НДС нормально, но при умножении стоимости на N в конечной цифре опять лезут копейки.
Это я про 1С.

[AnryGTR]

значит это безвыходная ситуация и с ней придётся смириться?

[Slavik]

значит это безвыходная ситуация и с ней придётся смириться?

Угу, если литература уже вся перерыта, а мозги напрягать лень

Во-первых, тебе уже сказали, что при делении идёт округление, т.е. разделил на 100 -- два последних знака потерял (и так на каждом заказе). Сначала умножай, потом дели:
...*з.скидка/100... ...*проц.процент/100...

Во-вторых, кто тебе мешает сначала находить суммы, а потом вычислять по ним проценты? Правда, при схеме с изменяющимися во времени юзерскими процентами одним запросом в FB15 эта задача не решается (во всяком случае я сходу решения не вижу). Либо х.п., либо FB20.

[AnryGTR]

2 Slavik:
так написал же что формулу переписал вообще без знака деления всё с умножением и только...не помогло!
А насчёт временного изменения процентов юзеров эт ты прав...тока вот не мог бы ты поподробнее о том как это можно реализовать "одним запросом" в FB2?

[Slavik]

так написал же что формулу переписал вообще без знака деления всё с умножением и только...не помогло!

Вообще? даже чуть-чуть? Странно...

...поподробнее о том как это можно реализовать "одним запросом" в FB2?

Долгожданное чудо select from (select...):

Код: Выделить всё

select ю.ID, ю.ФИО, юз.Заработал
from (select Юзер, cast(sum(Сумма*0.01*Процент) as numeric(18,2) as Заработал
      from (select з.Юзер, пц.Процент,
                   cast(sum(п.Цена*(1-0.01*з.Скидка)) as numeric(18,2)) as Сумма
            from Заказы з
                 join Проценты пц on
                   пц.Юзер = з.Юзер and з.Дата between пц.ДатаНачала and пц.ДатаКонца
                 join Прайс п on
                   п.Услуга = з.Услуга and з.Дата between п.ДатаНачала and п.ДатаКонца
                 join Юзеры ю on
                   ю.ID = з.Юзер
            where з.Дата between :d1 and :d2
              and з.Оплачен = 1
              and з.Юзер not in (19, 20)
              and пц.Процент > 0
              and ю.Категория = 1
            group by з.Юзер, пц.Процент
          )
      group by Юзер
     ) юз
     join Юзеры ю on ю.ID = юз.Юзер
  order by ю.ФИО

P.S. Не люблю писать запросы за других - пол часа угробил. Своих головоломок хватает...

[AnryGTR]

2 Slavik:
Спасибо за пример!
А писать запросы за других не надо, мне хватило бы и этого:

Долгожданное чудо select from (select...)

Всем спасибо за ответы!