forum.bitel.ru
http://forum.bitel.ru/

contract_balance. прогрессирующее расхождение сумм
http://forum.bitel.ru/viewtopic.php?f=22&t=2396		 Страница 1 из 1
Автор:  kompot [ 05 июн 2009, 13:42 ]
Заголовок сообщения:  contract_balance. прогрессирующее расхождение сумм
Имеются две таблицы:
contract_balance и её копия недельной давности contract_balance_bckup.

на текущий момент:
Код:
select sum(cb.summa1+cb.summa2-cb.summa3-cb.summa4)
from contract_balance cb
left join contract c on c.id=cb.cid
where c.gr&(1<<0)>0
and cb.mm=04 and cb.yy=2009


даёт:

2300471.22

и

Код:
select sum(cb.summa1+cb.summa2-cb.summa3-cb.summa4)
from contract_balance_bckup cb
left join contract c on c.id=cb.cid
where c.gr&(1<<0)>0
and cb.mm=04 and cb.yy=2009


даёт:

2300371.22

Как видно, за неделю появилась разница в 100 рублей.

Сравниваю кол-во записей в таблицах при (mm=04 and yy=2009) -- одинаковое.

Сравниваю построчно суммы:

Код:
select (cb.summa1+cb.summa2-cb.summa3-cb.summa4) as cb, (cb1.summa1+cb1.summa2-cb1.summa3-cb1.summa4) as cbb,
(cb.summa1+cb.summa2-cb.summa3-cb.summa4)-(cb1.summa1+cb1.summa2-cb1.summa3-cb1.summa4) as diff
from contract_balance cb
left join contract_balance_bckup cb1 on cb1.cid=cb.cid
left join contract c on c.id=cb.cid
where cb.yy=2009 and cb.mm=04 and cb1.yy=2009 and cb1.mm=04
and c.gr&(1<<0)>0


Результат:

Код:
 cb         cbb        diff   
 ---------  ---------  -------
 0          0          0       
 -1000      -1000      0       
 -2.33      -2.33      -0     
 -295.32    -295.32    -0     
 -1471.81   -1471.81   0       
 34.15      34.15      -0     
 -112.79    -112.79    0       
 320.18     320.18     0     


Дальше интереснее. Если в крайний запрос добавить условие:

Код:
and c.gr&(1<<0)>0
and (cb.summa1+cb.summa2-cb.summa3-cb.summa4)-(cb1.summa1+cb1.summa2-cb1.summa3-cb1.summa4)>0.001


и менять число в условии от 0.00001 до 0.1, то кол-во результатов, соотв. этому условию уменьшается от ~300 до 0.


вопрос 1. В связи с каким событием изменилась сумма? напомню, что это происходило не постепенно от 10 рублей до 100, например, а в один день примерно через неделю после сохранения копии таблицы.

вопрос 2. Почему вообще возникает такая ситуация, если столбцы сумм в тблицах имеют тип decimal(10,2)?

(Бухгалтерия не верит в мистику, и потихоньку сооружает для меня эшафот)

Спасибо.
Автор:  skn [ 05 июн 2009, 14:08 ]
Заголовок сообщения: 
попробуйте прочечать базу

такое случается когда повреждены индексы, в запросах в которых участвует индекс выдается одни результаты, а в которых не участвует - другие...
Автор:  kompot [ 05 июн 2009, 14:10 ]
Заголовок сообщения: 
не понял, что надо сделать?
Автор:  kompot [ 05 июн 2009, 14:42 ]
Заголовок сообщения: 
mysqlchek? сделал. ничего не изменилось


И причём здесь индексы, если я построчно сравниваю таблицы?
берем сумму из одной таблицы и сумму из другой таблицы, вычитаем одну из другой и видим, что разница не равна нулю и отличается от нуля на тысячные или десятитысячные доли единицы, в то время как значения сумм должны быть десятичными числами с двумя знаками после запятой.
Автор:  skn [ 05 июн 2009, 18:12 ]
Заголовок сообщения: 
ау вас группа у договора не могла поменять? кол-во записей одинаково?
Автор:  kompot [ 06 июн 2009, 09:53 ]
Заголовок сообщения: 
Кол-во записей одинаково, об этом я писал выше. Проверил первым делом, конечно. Но, я всё-таки ещё раз повторю, расхождение по все таблице - это следствие вот этого:

Код:
select if((cb.summa1+cb.summa2-cb.summa3-cb.summa4)-(cb1.summa1+cb1.summa2-cb1.summa3-cb1.summa4)>0.00001,"YES","NO")
from contract_balance cb
left join contract_balance_bckup cb1 on cb1.cid=cb.cid
where cb.yy=2009 and cb.mm=04 and cb1.yy=2009 and cb1.mm=04
and cb1.cid=902


результат: YES

А откуда оно берётся, если в обеих таблицах тип всех слагаемых decimal(10,2) и они соответственно равны?
Автор:  Jimson [ 07 июн 2009, 04:41 ]
Заголовок сообщения: 
извиняюсь за возможную глупость, но кто мешает таки сравнить таблицы целиком за апрель и найти те строки где данные не идентичны ?
если разница в 1-2 копейки на куче договоров, то это одно, а если 100 рублей разницы на одном договоре то как бы...
Автор:  kompot [ 07 июн 2009, 13:28 ]
Заголовок сообщения: 
Jimson писал(а):
извиняюсь за возможную глупость, но кто мешает таки сравнить таблицы целиком за апрель и найти те строки где данные не идентичны ?
если разница в 1-2 копейки на куче договоров, то это одно, а если 100 рублей разницы на одном договоре то как бы...



Вы не внимательно читали мой первый пост.
Автор:  Jimson [ 07 июн 2009, 18:14 ]
Заголовок сообщения: 
читал, я спрашиваю почему нельзя сравнить "select *", чудес то не бывает, ну или сделать select с условиями на неравенство и увидить воочию как у тебя не будут равны два числа decimal(10,2)
Автор:  kompot [ 07 июн 2009, 22:45 ]
Заголовок сообщения: 
1) Третий sql-запрос в первом посте -- построчное сравнение.
2) Последний sql-запрос, который я приводил -- это то, о чём вы говорите для одного конкретного договора (cid указан явно).
Или я что-то не понимаю?
Автор:  Jimson [ 07 июн 2009, 23:06 ]
Заголовок сообщения: 
наверно я чего то не понимаю...
я вижу несхождение по двум агрегирующим выборкам, бог с ними
я вижу выборку где вычисляется разность и результат который дает нули в этой разности
а строки где разность будет не равна нулю есть ?
не будет ли проще сделать выборку с условием на <> что бы увидить сколько будет таких строк и будут ли они вообще
Автор:  kompot [ 08 июн 2009, 00:27 ]
Заголовок сообщения: 
таких строк будет около тысячи. но сравниваемые значения визуально (в формате decimal(10,2)) будут равны, понимаете?
Т.е., например:
select cbb.summa, cb.summa
from cbb, cb
where ...
and cb.summa!=cbb.summa

даст около тысячи вот таких строк:

122.10 122.10

Если попросить показать строки, в которых разница мжду суммами будет больше 1 копейки - таких строк будет 0.
Если попросить показать строки, в которых разница между суммами будет больше 0.001 копейки - то таких строк уже будет пара сотен. Понимаете?
Автор:  Jimson [ 08 июн 2009, 10:18 ]
Заголовок сообщения: 
Понимаю. Виновата mysql. Искать схожие проблемы с decimal через гугл пробовали ?
Автор:  skn [ 08 июн 2009, 12:03 ]
Заголовок сообщения: 
храняться значения в decimal(10,2) но для арифметических операция приводяться к float отсюда и разница

попробуйте исходящий остаток умножить на 100 и отбросить дробную часть а потом уже просумировать
Автор:  Jimson [ 08 июн 2009, 12:24 ]
Заголовок сообщения: 
он то "отбросит", а вы ? :)
Автор:  skn [ 08 июн 2009, 13:45 ]
Заголовок сообщения: 
мы стараемся считать средствами java в частности класс BigDecimal, а не средствами mysql

у вас не схождение в биллинге вылезло или на базе?
Автор:  Jimson [ 08 июн 2009, 15:52 ]
Заголовок сообщения: 
на самом деле достаточно странная проблема, если ошибка только из за округлений float то значит на одном вычислении ошибка может составить лишь копейку, следовательно для разницы в 100 рублей договоров должно быть 10к

а о то что ошибка вылазит в базе это в любом случае не хорошо, отчеты то мы строим по базе, в моем понимании это все же проблема mysql раз она decimal приводит неявно к float на операциях сложения и вычитания
Автор:  Amir [ 08 июн 2009, 17:09 ]
Заголовок сообщения: 
http://dev.mysql.com/doc/refman/5.0/en/ ... float.html ?
Автор:  Jimson [ 08 июн 2009, 19:39 ]
Заголовок сообщения: 
жесть, я вообще не врубаюсь в это:

Код:
 The problem cannot be solved by using ROUND() or similar functions, because the result is still a floating-point number:

mysql> SELECT i, ROUND(SUM(d1), 2) AS a, ROUND(SUM(d2), 2) AS b
    -> FROM t1 GROUP BY i HAVING a <> b;
+------+--------+-------+
| i    | a      | b     |
+------+--------+-------+
|    1 |  21.40 | 21.40 |
|    2 |  76.80 | 76.80 |
|    3 |   7.40 |  7.40 |
|    4 |  15.40 | 15.40 |
|    5 |   7.20 |  7.20 |
|    6 | -51.40 |  0.00 |
+------+--------+-------+


потипу round(x,2) <> round(x,2) и это нормально или как ?
Автор:  skn [ 08 июн 2009, 23:16 ]
Заголовок сообщения: 
насколько я понял результат round все также число в формате float, а то, что видно в распечатке можно считать округленным форматированным выводом

как вариант можете попробовать проапгрейдить БД, или хотя бы для эксперимента
Автор:  kompot [ 09 июн 2009, 14:14 ]
Заголовок сообщения: 
skn писал(а):
храняться значения в decimal(10,2) но для арифметических операция приводяться к float отсюда и разница

попробуйте исходящий остаток умножить на 100 и отбросить дробную часть а потом уже просумировать



Зачем мне это?
(Дальше следует небольшая речь о том, что у каждого из нас своя работа и так далее. По причине очевидности, я её опущу.)
Автор:  skn [ 09 июн 2009, 14:34 ]
Заголовок сообщения: 
kompot писал(а):
Зачем мне это?
(Дальше следует небольшая речь о том, что у каждого из нас своя работа и так далее. По причине очевидности, я её опущу.)


Затем, что если вы используете mysql напрямик для построения отчетов, мы ничем кроме советов помочь не сможем....
Автор:  kompot [ 09 июн 2009, 14:59 ]
Заголовок сообщения: 
Хорошо. А как вы делаете на java?
Вы обращаетесь к базе не с суммимрующим запросом, а построчно вытаскиваете значения из базы и средствами java их суммируете, так?
Т.е. если я буду использовать вместо java какой-нибудь, например, php, то такой способ не должен дать такого эффекта?
Автор:  Amir [ 09 июн 2009, 16:01 ]
Заголовок сообщения: 
При работе с числом с плавающей точкой погрешность будет возникать всегда. Во всех языках идет просто округление, поэтому число выглядит нормально.
Когда же вы пытаетесь сравнить
(cb.summa1+cb.summa2-cb.summa3-cb.summa4)-(cb1.summa1+cb1.summa2-cb1.summa3-cb1.summa4)>0.001
то точность результата разности mysql приходится делать также 0.001. А в этом месте уже вылезает погрешность плавающей точки.
Т.е.
(cb.summa1+cb.summa2-cb.summa3-cb.summa4)-(cb1.summa1+cb1.summa2-cb1.summa3-cb1.summa4)=0.00
(cb.summa1+cb.summa2-cb.summa3-cb.summa4)-(cb1.summa1+cb1.summa2-cb1.summa3-cb1.summa4)=0
будет TRUE
(cb.summa1+cb.summa2-cb.summa3-cb.summa4)-(cb1.summa1+cb1.summa2-cb1.summa3-cb1.summa4)>=0.01
false

Разность же ровно в 100 рублей вряд ли погрешность.

В mysql 5.0.7 с полем DECIMAL нормально, а также при использовании FLOAT с округлением (FLOAT(,2)), он автоматически округляет результат суммы.

Если измените конец запроса с
and (cb.summa1+cb.summa2-cb.summa3-cb.summa4)-(cb1.summa1+cb1.summa2-cb1.summa3-cb1.summa4)>0.001
на
and (cb.summa1+cb.summa2-cb.summa3-cb.summa4)!=(cb1.summa1+cb1.summa2-cb1.summa3-cb1.summa4)
должен быть нормальный результат.
Автор:  kompot [ 09 июн 2009, 16:17 ]
Заголовок сообщения: 
Нет, с этого я и начинал, когда искал разницу.

Смотрите:

Код:
select count(*)
from contract_balance cb
left join contract_balance_bckup cb1 on cb1.cid=cb.cid
left join contract c on c.id=cb.cid
where cb.yy=2009 and cb.mm=04 and cb1.yy=2009 and cb1.mm=04
and c.gr&(1<<0)>0


результат:

Код:
 count(*)   
 -----------
 7834       


Это было кол-во всех строк. Теперь выберем только неравные суммы, как вы посоветоваали:

Код:
select (cb.summa1+cb.summa2-cb.summa3-cb.summa4) as cb, (cb1.summa1+cb1.summa2-cb1.summa3-cb1.summa4) as cbb,
(cb.summa1+cb.summa2-cb.summa3-cb.summa4)-(cb1.summa1+cb1.summa2-cb1.summa3-cb1.summa4) as diff
from contract_balance cb
left join contract_balance_bckup cb1 on cb1.cid=cb.cid
left join contract c on c.id=cb.cid
where cb.yy=2009 and cb.mm=04 and cb1.yy=2009 and cb1.mm=04
and c.gr&(1<<0)>0
and [b](cb.summa1+cb.summa2-cb.summa3-cb.summa4)!=(cb1.summa1+cb1.summa2-cb1.summa3-cb1.summa4)[/b]


результат:

Код:
 cb         cbb        diff   
 ---------  ---------  -------
 -2.33      -2.33      -0     
 -295.32    -295.32    -0     
 -1471.81   -1471.81   0       
 34.15      34.15      -0     
 -112.79    -112.79    0       
 320.18     320.18     0       
 -1643.82   -1643.82   -0     
 -330.79    -330.79    0       
 -5881.88   -5881.88   -0     
 . . . .

 1964 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 68/ms]

 [Executed: 6/9/09 4:06:35 PM YEKST ] [Execution: 301/ms]

Автор:  Amir [ 09 июн 2009, 16:40 ]
Заголовок сообщения: 
А версия mysql какая? Таблицу contract_balance_bckup как создавали? Выполните
SHOW COLUMNS FROM contract_balance
SHOW COLUMNS FROM contract_balance_bckup
Автор:  kompot [ 09 июн 2009, 16:46 ]
Заголовок сообщения: 
Ver 5.0.18


contract_balance:

Код:
 Field     Type                  Null     Key     Default     Extra   
 --------  --------------------  -------  ------  ----------  --------
 yy        smallint(5) unsigned  NO       PRI     0                   
 mm        tinyint(3) unsigned   NO       PRI     0                   
 cid       int(10) unsigned      NO       PRI     0                   
 summa1    decimal(10,2)         NO                                   
 summa2    decimal(10,2)         NO                                   
 summa3    decimal(10,2)         NO                                   
 summa4    decimal(10,2)         NO   


contract_balance_bckup:


Код:
 Field     Type                  Null     Key     Default     Extra   
 --------  --------------------  -------  ------  ----------  --------
 yy        smallint(5) unsigned  NO       PRI     0                   
 mm        tinyint(3) unsigned   NO       PRI     0                   
 cid       int(10) unsigned      NO       PRI     0                   
 summa1    float(10,2)           NO               0.00                 
 summa2    float(10,2)           NO               0.00                 
 summa3    float(10,2)           NO               0.00                 
 summa4    float(10,2)           NO               0.00   
Автор:  Amir [ 09 июн 2009, 17:00 ]
Заголовок сообщения: 
На 5.0.77 при использовании в обоих таблицах decimal (float(,2)) нормально.
Правда тут написано http://dev.mysql.com/doc/refman/5.0/en/ ... float.html что нужно перезагрузить таблицу, не уверен с какой версии у нас таблица contract_balance не передампивалась...

Теоретически такой дамп (из decimal в float) не корректен, т.е. какое-либо значение могло перенестись неверно.
Скажем в 4 байтном float -383130.27 будет -383130.28
Однако в 8 байтный double влезет нормально.
Страница 1 из 1 Часовой пояс: UTC + 5 часов [ Летнее время ]
Powered by phpBB® Forum Software © phpBB Group
http://www.phpbb.com/