Mantener correcto el total de cada pedido

Introducción

PAPER & PIER vende pequeños artículos en dos puestos de mercado. Antes del cierre, su mostrador necesita comparar el valor de los artículos de cada pedido con el dinero realmente recibido y reembolsado. Un pedido puede contener varios artículos y varios pagos reales. Unir esas listas sin procesar de forma descuidada puede hacer que un pedido correctamente pagado parezca incorrecto.

Crearás tres vistas reutilizables de SQLite que sumen cada tipo de dato por separado. Necesitas conocimientos básicos para moverte por la terminal y de SELECT/WHERE en SQL. Aquí se explican las agregaciones, las uniones, las vistas y los valores ausentes. SQLite y un libro de registros sintético están preparados; usa Terminal durante todo el laboratorio.

Conserva todas las tablas, los ID y los valores originales. Solo puedes crear o reemplazar las vistas item_totals, payment_totals y refund_totals, y guardar sus definiciones en totals.sql. Los importes son centavos enteros; esta es una conciliación simplificada de cobros, sin impuestos, liquidación del proveedor de pagos ni conversión de divisas.

Mantener un total de artículos por pedido

En este paso, consultarás un pedido normal, verás cómo una unión de datos sin agregar duplica el dinero y guardarás una vista con totales de artículos correctos. Empieza en Terminal. cd cambia de directorio; esta ruta absoluta también permite recuperarte si entras en el directorio equivocado:

cd /home/labex/project/sales-desk

sqlite3 abre la base de datos indicada. -header imprime los nombres de las columnas, -column alinea los valores y el SQL entre comillas se ejecuta antes de cerrar la conexión. Las comillas mantienen los espacios y los puntos y coma del SQL en un único argumento del intérprete. Consulta las identidades de pedido preparadas:

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

Hay cuatro pedidos. A1 y A2 son pedidos distintos aunque las etiquetas de cliente de ambos digan Alex Kim. Agrupa por el ID del pedido, nunca por el nombre del cliente. Consulta las líneas de artículos y los eventos de pago de A1:

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

Sus artículos cuestan 300 + 500 = 800 centavos. Los pagos P1 y P2 son dos eventos distintos y completados de 400 centavos, que también suman 800. P3 falló y no aportó dinero. succeeded significa que el evento cuenta en los cálculos de este proyecto; pending y failed no cuentan.

Una unión combina filas relacionadas. AS asigna un alias corto de tabla: i representa los artículos y p los pagos. ON especifica los ID de pedido que deben coincidir. Antes de sumar nada, observa todos los pares coincidentes de 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;"

Hay cuatro filas: cada uno de los dos artículos coincide con ambos pagos. Este es el comportamiento real de una unión. No significa que hubiera cuatro artículos o cuatro pagos. SUM suma la expresión sobre sus filas de entrada. En esta entrada duplicada, ambas sumas son incorrectas:

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';"

Ambos totales mostrados son 1600, el doble del importe real. DISTINCT conserva valores diferentes, no identidades de pago diferentes. No puede corregir este problema:

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

El resultado es 400, pues descarta un pago legítimo del mismo importe. En su lugar, suma cada lista de datos por separado. GROUP BY order_id produce una fila de resultado por cada ID de pedido. quantity * unit_cents calcula el valor de cada artículo antes de sumarlo. La granularidad resultante, es decir, lo que representa una fila de salida, es un pedido.

Una vista es un SELECT con nombre almacenado en la base de datos. Leerla ejecuta ese SELECT sobre las filas fuente actuales; no copia ni reemplaza los artículos. Guarda su definición en totals.sql. En el intérprete, cat escribe el siguiente here-document literal en el archivo; > reemplaza su contenido anterior. Pega el SQL final solo en una línea. Este bloque corresponde al indicador del intérprete del sistema, no al indicador interactivo de 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 elimina únicamente una definición anterior de esta vista, si existe. Deja intactas las tablas y los datos fuente. CREATE VIEW guarda la nueva definición. < proporciona el archivo guardado como entrada de SQLite:

sqlite3 ledger.db < totals.sql

Si se ejecuta correctamente, no se espera ninguna salida. Consulta la vista real:

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

Deberías ver A1 = 800, A2 = 250, A3 = 500 y A4 = 200 centavos, cada uno en una fila. Se incluye la cantidad de dos de A3. Ya tienes un primer recurso útil: una definición de vista guardada que puede ejecutarse sobre otro libro de registros compatible.

Si falta un archivo, ls enumera los archivos de este directorio. Comprueba el cd absoluto y la escritura de totals.sql. Un error de sintaxis SQL identifica la instrucción que falla; repite el bloque de creación completo para reemplazar un archivo mal escrito y aplícalo de nuevo. Si el intérprete sigue esperando después del here-document, introduce la línea final SQL que falta. No borres el libro de registros para recuperarte.

Combinar totales de dinero independientes

En este paso, añadirás al script guardado los totales de pagos y reembolsos completados y combinarás los tres conjuntos de datos de una fila por pedido, conservando los pedidos sin pagar. Empieza en Terminal, en /home/labex/project/sales-desk.

La misma regla de agregación se aplica a pagos y reembolsos: cada evento real cuenta, incluidos los importes iguales con ID distintos. Filtra status = 'succeeded' dentro del SELECT de cada conjunto de datos, antes de agrupar. Un reembolso pendiente no debe reducir el dinero recibido. Consulta las pruebas de reembolsos:

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

A3 tiene dos reembolsos completados separados de 50 centavos y un reembolso pendiente de 250 centavos. Su total real de reembolsos es 100, no 50 ni 350.

Añade las otras dos definiciones de vista al final del archivo. >> añade al final de un archivo existente en lugar de reemplazar la vista de artículos. Si repites esta operación para recuperarte de un error, primero reconstruye totals.sql usando el bloque completo del paso anterior y después añade este bloque una vez:

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

El script guardado puede volver a aplicarse: cada definición se elimina y se vuelve a crear a partir de los datos fuente sin cambios. Consulta las vistas de dinero:

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

Los pagos tienen A1 = 800, A3 = 600 y A4 = 250. Los reembolsos solo tienen A3 = 100. A2 no aparece en ninguno porque no existe ningún pago ni reembolso que cumpla los criterios. La ausencia de un total de datos no debe eliminar el pedido de la conciliación.

LEFT JOIN conserva todas las filas de su izquierda aunque no exista una coincidencia a la derecha. Empieza con orders y une después cada vista ya agregada por ID de pedido. Una coincidencia ausente produce NULL de SQL, que significa que no hay valor, en lugar de cero. COALESCE(value, 0) sustituye por cero solo cuando ese total es NULL. Las condiciones sobre el estado del evento ya corresponden a las vistas de dinero; añadir una condición WHERE sobre una fila unida ausente podría descartar el pedido sin pagar.

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;"

Interpreta cada fila según la actividad normal del negocio:

Cada vista de datos tiene como máximo una fila por pedido antes de las uniones, por lo que un conjunto de datos no puede multiplicar otro. Conserva las cuatro tablas originales y todos sus registros; las tres vistas y totals.sql son las únicas definiciones de base de datos y el único archivo que necesitas añadir. El siguiente laboratorio comienza con su propio libro de registros nuevo y convierte este razonamiento en una consulta de conciliación completa.

Resumen

Detectaste dos errores habituales al sumar dinero, guardaste vistas reutilizables de datos con una fila por pedido y las combinaste con LEFT JOIN y COALESCE. Los eventos separados de igual importe siguieron contando como eventos reales y los pedidos sin pagar permanecieron visibles.