Получите правильный итог для каждого заказа

Введение

PAPER & PIER продаёт небольшие товары в двух рыночных киосках. Перед закрытием нужно сопоставить стоимость товаров каждого заказа с фактически полученными и возвращёнными деньгами. В одном заказе может быть несколько товарных позиций и несколько настоящих платежей. Неосторожное соединение этих исходных списков может создать впечатление, что с правильно оплаченным заказом что-то не так.

Вы создадите три представления SQLite для повторного использования, которые отдельно суммируют каждый вид фактов. Вам понадобятся базовые навыки навигации в терминале и знание SQL SELECT/WHERE. Агрегирование, соединения, представления и отсутствующие значения объясняются здесь. SQLite и синтетический журнал операций уже подготовлены; на протяжении всей работы используйте Terminal.

Сохраните все исходные таблицы, ID и значения. Вы можете создавать или заменять только представления item_totals, payment_totals и refund_totals и сохранять их определения в totals.sql. Суммы выражены целым числом центов; это упрощённая сверка поступлений без налогов, расчётов с платёжным провайдером или конвертации валют.

Получите один итог стоимости товаров для каждого заказа

На этом шаге вы прочитаете обычный заказ, увидите, как соединение исходных строк дублирует денежные суммы, и сохраните представление с правильным итогом стоимости товаров. Начните во вкладке Terminal. cd меняет каталог; этот абсолютный путь также поможет вернуться из ошибочно выбранного каталога:

cd /home/labex/project/sales-desk

sqlite3 открывает указанную базу данных. -header выводит названия столбцов, -column выравнивает значения, а SQL в кавычках выполняется перед закрытием соединения. Кавычки удерживают пробелы и точки с запятой SQL в одном аргументе оболочки. Прочитайте подготовленные идентификаторы заказов:

sqlite3 -header -column ledger.db "SELECT * FROM orders ORDER BY order_id;"

Есть четыре заказа. A1 и A2 — разные заказы, хотя в обоих имя покупателя указано как Alex Kim. Группируйте по ID заказа, никогда не по имени покупателя. Прочитайте товарные позиции и платёжные события A1:

sqlite3 -header -column ledger.db "SELECT * FROM items WHERE order_id = 'A1'; SELECT * FROM payments WHERE order_id = 'A1';"

Его товары стоят 300 + 500 = 800 центов. Платежи P1 и P2 — два отдельных успешных события по 400 центов, также дающие в сумме 800. P3 завершился неудачей и не принёс денег. succeeded означает, что событие учитывается в расчётах этого проекта; pending и failed не учитываются.

Соединение объединяет связанные строки. AS назначает короткий псевдоним таблицы: i означает товары, а p — платежи. ON задаёт совпадение ID заказа. Прежде чем что-либо суммировать, посмотрите все совпавшие пары для A1:

sqlite3 -header -column ledger.db "
SELECT i.item_id, i.unit_cents, p.payment_id, p.amount_cents
FROM items AS i
JOIN payments AS p ON p.order_id = i.order_id
WHERE i.order_id = 'A1' AND p.status = 'succeeded'
ORDER BY i.item_id, p.payment_id;"

Получаются четыре строки: каждая из двух товарных позиций соответствует обоим платежам. Это настоящее поведение соединения. Оно не означает, что было четыре товара или четыре платежа. SUM складывает значение выражения по своим входным строкам. На таких дублированных входных данных обе суммы неверны:

sqlite3 -header -column ledger.db "
SELECT SUM(i.quantity * i.unit_cents) AS joined_due,
       SUM(p.amount_cents) AS joined_paid
FROM items AS i
JOIN payments AS p ON p.order_id = i.order_id
WHERE i.order_id = 'A1' AND p.status = 'succeeded';"

Оба показанных итога равны 1600 — вдвое больше настоящей суммы. DISTINCT сохраняет разные значения, а не разные идентификаторы платежей. Исправить эту проблему он не может:

sqlite3 -header -column ledger.db "
SELECT SUM(DISTINCT amount_cents) AS wrong_paid
FROM payments WHERE order_id = 'A1' AND status = 'succeeded';"

Результат равен 400: один допустимый платёж с такой же суммой потерян. Вместо этого суммируйте каждый список фактов отдельно. GROUP BY order_id создаёт одну результирующую строку для каждого ID заказа. quantity * unit_cents вычисляет стоимость каждой товарной позиции перед сложением. Получившаяся гранулярность, то есть то, что представляет одна выходная строка, — один заказ.

Представление — это именованный SELECT, сохранённый в базе данных. При чтении он выполняет этот SELECT над текущими исходными строками, а не копирует и не заменяет товары. Сохраните его определение в totals.sql. В оболочке cat записывает следующий буквальный here-document в файл; > заменяет прежнее содержимое. Вставьте завершающий SQL на отдельной строке. Этот блок вводится в приглашении оболочки, а не в интерактивном приглашении SQLite:

cat > totals.sql <<'SQL'
DROP VIEW IF EXISTS item_totals;
CREATE VIEW item_totals AS
SELECT order_id, SUM(quantity * unit_cents) AS due_cents
FROM items
GROUP BY order_id;
SQL

DROP VIEW IF EXISTS удаляет только предыдущее определение этого представления, если оно есть. Исходные таблицы и данные остаются нетронутыми. CREATE VIEW сохраняет новое определение. < передаёт сохранённый файл на вход SQLite:

sqlite3 ledger.db < totals.sql

При успешном выполнении вывода не ожидается. Прочитайте настоящее представление:

sqlite3 -header -column ledger.db "SELECT * FROM item_totals ORDER BY order_id;"

Ожидаются A1 = 800, A2 = 250, A3 = 500 и A4 = 200 центов, каждый заказ в одной строке. Количество два у A3 учтено. Теперь у вас есть первый полезный результат: сохранённое определение представления, которое может работать с другим совместимым журналом операций.

Если файл не найден, ls выводит файлы этого каталога. Проверьте абсолютную команду cd и написание totals.sql. Синтаксическая ошибка SQL указывает на неудачную инструкцию; повторите полный блок создания, чтобы заменить файл с опечаткой, затем примените его снова. Если после here-document оболочка всё ещё ждёт ввод, введите недостающую завершающую строку SQL. Не удаляйте журнал операций для восстановления.

Объедините независимые денежные итоги

На этом шаге вы добавите итоги успешных платежей и возвратов в сохранённый скрипт, а затем объедините три набора фактов с одной строкой на заказ, сохранив неоплаченные заказы. Начните во вкладке Terminal в /home/labex/project/sales-desk.

Для платежей и возвратов действует то же правило агрегирования: каждое настоящее событие учитывается, включая одинаковые суммы с разными ID. Фильтруйте status = 'succeeded' внутри SELECT каждого вида фактов, до группировки. Ожидающий возврат не должен уменьшать полученные деньги. Прочитайте записи возвратов:

sqlite3 -header -column ledger.db "SELECT * FROM refunds ORDER BY refund_id;"

У A3 есть два отдельных успешных возврата по 50 центов и ожидающий возврат на 250 центов. Фактический итог возвратов — 100, а не 50 и не 350.

Добавьте определения двух других представлений. >> дописывает в конец существующего файла, не заменяя представление товаров. Если вы повторяете добавление при исправлении ошибки, сначала заново создайте totals.sql с помощью полного блока из предыдущего шага, затем добавьте этот блок один раз:

cat >> totals.sql <<'SQL'
DROP VIEW IF EXISTS payment_totals;
CREATE VIEW payment_totals AS
SELECT order_id, SUM(amount_cents) AS paid_cents
FROM payments
WHERE status = 'succeeded'
GROUP BY order_id;

DROP VIEW IF EXISTS refund_totals;
CREATE VIEW refund_totals AS
SELECT order_id, SUM(amount_cents) AS refunded_cents
FROM refunds
WHERE status = 'succeeded'
GROUP BY order_id;
SQL

sqlite3 ledger.db < totals.sql

Сохранённый скрипт можно применять повторно: каждое определение удаляется и создаётся заново из неизменённых исходных фактов. Прочитайте денежные представления:

sqlite3 -header -column ledger.db "SELECT * FROM payment_totals ORDER BY order_id; SELECT * FROM refund_totals ORDER BY order_id;"

В платежах есть A1 = 800, A3 = 600 и A4 = 250. В возвратах — только A3 = 100. A2 отсутствует в обоих представлениях, поскольку для него нет платежа или возврата, удовлетворяющего условиям. Отсутствие в итогах фактов не должно исключать заказ из сверки.

LEFT JOIN сохраняет каждую строку слева даже при отсутствии соответствия справа. Начните с orders, затем присоедините каждое уже агрегированное представление по ID заказа. При отсутствии соответствия получается SQL NULL, означающий отсутствие значения, а не ноль. COALESCE(value, 0) подставляет ноль только тогда, когда этот итог равен NULL. Условия статуса события уже находятся внутри денежных представлений; добавление условия WHERE для отсутствующей присоединённой строки может исключить неоплаченный заказ.

sqlite3 -header -column ledger.db "
SELECT o.order_id, o.customer,
       COALESCE(i.due_cents, 0) AS due_cents,
       COALESCE(p.paid_cents, 0) AS paid_cents,
       COALESCE(r.refunded_cents, 0) AS refunded_cents
FROM orders AS o
LEFT JOIN item_totals AS i ON i.order_id = o.order_id
LEFT JOIN payment_totals AS p ON p.order_id = o.order_id
LEFT JOIN refund_totals AS r ON r.order_id = o.order_id
ORDER BY o.order_id;"

Сопоставьте каждую строку с обычным ходом работы:

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

Резюме

Вы выявили две ловушки подсчёта денежных итогов, сохранили представления фактов для повторного использования с одной строкой на заказ и объединили их с помощью LEFT JOIN и COALESCE. Отдельные события с одинаковыми суммами остались настоящими отдельными событиями, а неоплаченные заказы — видимыми.