SQL Studio / Разборы SQL / Типы после CSV
Импортёр может сохранить денежный столбец как TEXT. Для SQLite 1 200,50, 300.25, пустая строка и ошибка — не одно и то же число. Нельзя просто применить SUM(CAST(... AS REAL)): SQLite распознаёт числовой префикс, а остальное иногда игнорирует. Правильный путь для денежных отчётов: уточнить формат входного CSV, проверить каждую запись, перевести только допустимые значения в целые копейки и отдельно посчитать отказавшие строки.
Какие строки пришли из файла
Предположим, CSV использует точку с запятой как разделитель полей, а строку вида 1;"1 200,50" — как сумму в кавычках. Для CSV с разделителем-запятой значение с десятичной запятой обязательно требует корректного цитирования; кавычки не являются частью суммы. Вопрос о кодировке и разделителе решается при импорте, а о формате денег — при очистке.
| id | amount_txt | Смысл проверки |
|---|---|---|
| 1 | 1 200,50 | пробел тысяч, запятая копеек |
| 2 | 300.25 | точка как разделитель |
| 3 | пусто | неизвестная сумма |
| 4 | ошибка | не число |
| 5 | 0,00 | действительный ноль |
| 6 | 12,3 | один знак после запятой |
| 7 | 1,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;
| id | blind_rub |
|---|---|
| 1 | 1.0 |
| 2 | 300.25 |
| 3 | 0.0 |
| 4 | 0.0 |
| 5 | 0.0 |
| 6 | 12.0 |
| 7 | 1.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;
| id | amount_kop | verdict |
|---|---|---|
| 1 | 120050 | ok |
| 2 | 30025 | ok |
| 3 | NULL | missing |
| 4 | NULL | reject |
| 5 | 0 | ok |
| 6 | NULL | reject |
| 7 | NULL | reject |
Принципиальное отличие: 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_rows | accepted_rows | not_accepted | accepted_kop |
|---|---|---|---|
| 7 | 3 | 4 | 150075 |
Итого 1500 рублей 75 копеек по трём принятым строкам. Это не сумма всех семи строк: одна отсутствует и три отклонены. Нельзя тихо заменить эти четыре значения нулями — отчёт должен показывать неполноту данных.
Небольшое упражнение
Добавьте к набору строку (8,' 42,10 '), затем (9,'42 руб') и (10,NULL). Для последней ожидается missing вместо reject. Сначала запишите ожидаемые verdict и копейки, потом исполните SQL. В бесплатной SQLite-песочнице SQL Studio это можно сделать, ничего не импортируя. Если нужен именно импорт файла, отдельная инструкция по CSV показывает меню загрузки и ограничения размера.
Для систематической практики отдельно существует курс SQL для аналитика на Stepik; там используется MySQL 8 с другими правилами приведения типов. Все примеры этой страницы проверены на SQLite.