Блог

Данные

У меня есть фрейм данных pandas с несколькими столбцами. Один из них — это описание, сохраненное как object , а другой — общая сумма для этого описания, которая хранится как float64 . Похоже на это:

    id    desc amount
12345 Item 1A   15.9
12345 Item 2N   -3.9
12345 Item 1A   2.99
12345 Item  3  -5.15
12345 Item  3  -9.84 
  

Используя sqlalchemy, я подключаюсь к базе данных и экспортирую фрейм данных с помощью to_sql :

 con = sqlalchemy.create_engine("sqlite:///database.db")
df.to_sql(name = "table1", con = con, dtype = {"amount": sqlalchemy.Float()})
  

Проблема

В базе данных я выполняю следующий запрос, чтобы получить общую сумму, выплаченную этому идентификатору:

 SELECT id, SUM(amount) AS 'TOTAL PAID' FROM table1 GROUP BY ID;
  

База данных возвращает следующее:

    id            TOTAL PAID
12345 -5.35510483179458e-10
  

Правильный результат будет:

    id TOTAL PAID
12345          0
  

Я проверил, и в столбце amount в базе данных действительно указаны правильные суммы. Я думаю, что это может относиться к используемому типу данных, но я не уверен. Для всех идентификаторов результат равен 0. Идентификаторы с другим результатом в функции SUM отображаются нормально. Поскольку этот столбец относится к деньгам, мне нужно только сохранить две цифры после десятичной точки.

Здесь вы можете найти одну из записей, показывающих проблему.

Вопрос (TL; DR)

Почему SQLite показывает экспоненциальное число вместо 0 в результате агрегатной функции? Как я могу получить ожидаемый результат?

Редактировать

При использовании функции SUM она показывает не только это экспоненциальное число. Он показывает несколько других экспоненциальных чисел для разных идентификаторов. Есть некоторые идентификаторы, общая сумма которых также равна 0, но на них это не влияет. Между затронутыми и незатронутыми нет никакой разницы.

В SQLite такой тип данных, как FLOAT64 имеет REAL сходство, как это объясняется в 3.1. Определение сходства столбцов, поэтому могут возникать ошибки точности.
Даже в MySQL такая же ошибка точности произошла бы с типом данных REAL :https://www.db-fiddle.com/f/5Q5j3tnMnFieW9UHyMfFhK/0

В вашем случае вам понадобится тип данных, подобный DECIMAL(12,2) но он недоступен в SQLite.

Если вам нужен обходной путь, предполагая, что все значения имеют максимум 2 цифры после запятой, затем умножьте каждое значение на 100 и преобразуйте результат в INTEGER , который обеспечивает лучшую точность. Затем суммируйте значения и разделите на 100.0 :

 SELECT id, 
       SUM(CAST((100 * amount) AS INTEGER)) / 100.0 AS [TOTAL PAID] 
FROM table1 
GROUP BY ID;
  

Или используйте функцию ROUND() :

 SELECT id, 
       ROUND(SUM(amount), 2) AS [TOTAL PAID] 
FROM table1 
GROUP BY ID;
  

Смотрите демонстрацию.
Результаты:

 >    id | TOTAL PAID
> ----: | ---------:
> 12345 |          0
  

Из документации SQLite — Представление чисел :

SQLite4 не делает различий между целыми числами и числами с плавающей запятой

Это означает, что движок будет допускать ошибки точности, если вы используете какое-либо значение с плавающей запятой при вычислении суммы … и это ваш случай. В SQLite нет способа обойти это, если вы не применяете все значения INTEGER . Однако движок SQLite не будет применять его для вас. К сожалению, вам нужно будет переключиться на другой движок, чтобы исправить это.