CSV в SQLite: почему сумма после импорта оказалась неверной

1 200,50 должно означать 1200 рублей 50 копеек. Но простой CAST(... AS REAL) в SQLite превратит такую строку в 1.0 — без ошибки.

SQL Studio / Разборы SQL / Типы после CSV

Импортёр может сохранить денежный столбец как TEXT. Для SQLite 1 200,50, 300.25, пустая строка и ошибка — не одно и то же число. Нельзя просто применить SUM(CAST(... AS REAL)): SQLite распознаёт числовой префикс, а остальное иногда игнорирует. Правильный путь для денежных отчётов: уточнить формат входного CSV, проверить каждую запись, перевести только допустимые значения в целые копейки и отдельно посчитать отказавшие строки.

Какие строки пришли из файла

Предположим, CSV использует точку с запятой как разделитель полей, а строку вида 1;"1 200,50" — как сумму в кавычках. Для CSV с разделителем-запятой значение с десятичной запятой обязательно требует корректного цитирования; кавычки не являются частью суммы. Вопрос о кодировке и разделителе решается при импорте, а о формате денег — при очистке.

Семь полученных значений TEXT
idamount_txtСмысл проверки
11 200,50пробел тысяч, запятая копеек
2300.25точка как разделитель
3пустонеизвестная сумма
4ошибкане число
50,00действительный ноль
612,3один знак после запятой
71,2,3две десятичные запятые

Все примеры далее создают исходные строки через WITH ... VALUES: они самостоятельны и не используют реальную импортированную базу.

1. Правдоподобный неверный CAST

WITH raw(id, amount_txt) AS (
  VALUES (1,'1 200,50'), (2,'300.25'), (3,''),
         (4,'ошибка'), (5,'0,00'), (6,'12,3'), (7,'1,2,3')
)
SELECT id, amount_txt, CAST(amount_txt AS REAL) AS blind_rub
FROM raw ORDER BY id;
Полученная SQLite колонка blind_rub
idblind_rub
11.0
2300.25
30.0
40.0
50.0
612.0
71.0

Здесь даже мусор превращается в ноль, а запись с несколькими запятыми частично читается как единица. Это объясняет, почему отчёт может быть численно «похожим», но неверным. CAST в SQLite не выполняет строгую локализованную валидацию. Официальная справка по CAST описывает эту потерю информации.

2. Сначала проверка формата, потом копейки

Для этого учебного формата разрешим обычные ASCII-цифры, один десятичный разделитель (точка или запятая), строго две цифры копеек и обычные пробелы внутри целой части. Проверяем регулярность средствами GLOB, а затем конвертируем отдельно рубли и копейки в INTEGER. Значения в результате — копейки, не REAL-рубли. Это осознанный узкий контракт; тысячи других денежных форматов он не обязан понимать.

WITH raw(id, amount_txt) AS (
  VALUES (1,'1 200,50'), (2,'300.25'), (3,''),
         (4,'ошибка'), (5,'0,00'), (6,'12,3'), (7,'1,2,3')
),
prepared AS (
  SELECT id, amount_txt,
         REPLACE(REPLACE(TRIM(amount_txt), ' ', ''), ',', '.') AS normalized
  FROM raw
),
checked AS (
  SELECT id, amount_txt, normalized,
         CASE WHEN normalized <> ''
                   AND normalized NOT GLOB '*[^0-9.]*'
                   AND length(normalized) - length(replace(normalized, '.', '')) = 1
                   AND instr(normalized, '.') BETWEEN 2 AND 13
                   AND length(normalized) - instr(normalized, '.') = 2
              THEN CAST(substr(normalized, 1, instr(normalized, '.') - 1)
                        AS INTEGER) * 100
                 + CAST(substr(normalized, instr(normalized, '.') + 1)
                        AS INTEGER)
              ELSE NULL END AS amount_kop
  FROM prepared
)
SELECT id, amount_txt, amount_kop,
       CASE WHEN amount_kop IS NOT NULL THEN 'ok'
            WHEN amount_txt IS NULL OR trim(amount_txt) = '' THEN 'missing'
            ELSE 'reject' END AS verdict
FROM checked ORDER BY id;
Ожидаемые статусы и копейки
idamount_kopverdict
1120050ok
230025ok
3NULLmissing
4NULLreject
50ok
6NULLreject
7NULLreject

Принципиальное отличие: NULL — «значение отсутствует или отклонено», а 0 — подтверждённая нулевая сумма. Отклонённые строки оставляем для ручного разбора и не прибавляем к доходу. Если импортёр передал не пустую строку, а настоящее SQL NULL, выражение amount_txt IS NULL также пометит её как missing, а не как reject. В этом примере фильтр длины целой части также ограничивает диапазон расчёта: он не рассчитан на безразмерные числа или отрицательные суммы/возвраты. Пробелы всех видов (неразрывные, узкие) и валютные символы требуют отдельной согласованной нормализации.

3. Контроль импорта: сумма и число отбраковок

Суммировать нужно только проверенное поле; при этом число исходных записей должно сохраняться для аудита. Запрос повторяет этап очистки, чтобы целиком запускаться отдельно от предыдущего.

WITH raw(id, amount_txt) AS (
  VALUES (1,'1 200,50'), (2,'300.25'), (3,''),
         (4,'ошибка'), (5,'0,00'), (6,'12,3'), (7,'1,2,3')
),
prepared AS (
  SELECT id, amount_txt,
         REPLACE(REPLACE(TRIM(amount_txt), ' ', ''), ',', '.') AS normalized
  FROM raw
),
checked AS (
  SELECT id, amount_txt, normalized,
         CASE WHEN normalized <> ''
                   AND normalized NOT GLOB '*[^0-9.]*'
                   AND length(normalized) - length(replace(normalized, '.', '')) = 1
                   AND instr(normalized, '.') BETWEEN 2 AND 13
                   AND length(normalized) - instr(normalized, '.') = 2
              THEN CAST(substr(normalized, 1, instr(normalized, '.') - 1)
                        AS INTEGER) * 100
                 + CAST(substr(normalized, instr(normalized, '.') + 1)
                        AS INTEGER)
              ELSE NULL END AS amount_kop
  FROM prepared
)
SELECT COUNT(*) AS raw_rows,
       COUNT(amount_kop) AS accepted_rows,
       SUM(CASE WHEN amount_kop IS NULL THEN 1 ELSE 0 END) AS not_accepted,
       COALESCE(SUM(amount_kop), 0) AS accepted_kop
FROM checked;
Итог только по принятым данным
raw_rowsaccepted_rowsnot_acceptedaccepted_kop
734150075

Итого 1500 рублей 75 копеек по трём принятым строкам. Это не сумма всех семи строк: одна отсутствует и три отклонены. Нельзя тихо заменить эти четыре значения нулями — отчёт должен показывать неполноту данных.

Небольшое упражнение

Добавьте к набору строку (8,' 42,10 '), затем (9,'42 руб') и (10,NULL). Для последней ожидается missing вместо reject. Сначала запишите ожидаемые verdict и копейки, потом исполните SQL. В бесплатной SQLite-песочнице SQL Studio это можно сделать, ничего не импортируя. Если нужен именно импорт файла, отдельная инструкция по CSV показывает меню загрузки и ограничения размера.

Связанные разборы: COUNT и NULL при проверке данных, условная сумма CASE, как загрузить CSV. Здесь задача уже, чем импорт: не потерять деньги при приведении типа.


Для систематической практики отдельно существует курс SQL для аналитика на Stepik; там используется MySQL 8 с другими правилами приведения типов. Все примеры этой страницы проверены на SQLite.