Manter o total correto de cada pedido

Introdução

A PAPER & PIER vende pequenos produtos em duas bancas de mercado. Antes de fechar, seu posto precisa comparar o valor dos itens de cada pedido com o dinheiro efetivamente recebido e reembolsado. Um pedido pode conter vários itens e vários pagamentos reais. Juntar essas listas brutas sem cuidado pode fazer um pedido corretamente pago parecer incorreto.

Você criará três views reutilizáveis do SQLite que totalizam cada tipo de fato separadamente. Você precisa saber navegar pelo terminal e conhecer SELECT/WHERE em SQL. Agregações, junções, views e valores ausentes são explicados aqui. O SQLite e um registro de vendas sintético estão preparados; use Terminal durante todo o lab.

Preserve todas as tabelas, os IDs e os valores originais. Você pode criar ou substituir apenas as views item_totals, payment_totals e refund_totals e salvar suas definições em totals.sql. Os valores monetários são centavos inteiros; esta é uma reconciliação simplificada de recebimentos, sem impostos, liquidação por provedores de pagamento ou conversão de moedas.

Manter um total de itens por pedido

Nesta etapa, você consultará um pedido normal, verá como uma junção de dados brutos duplica valores monetários e salvará uma view com o total correto dos itens. Comece em Terminal. cd muda o diretório; este caminho absoluto também permite se recuperar após entrar no diretório errado:

cd /home/labex/project/sales-desk

sqlite3 abre o banco de dados indicado. -header exibe os nomes das colunas, -column alinha os valores, e o SQL entre aspas é executado antes de a conexão fechar. As aspas mantêm os espaços e pontos e vírgulas do SQL em um único argumento do shell. Consulte as identidades dos pedidos preparados:

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

Há quatro pedidos. A1 e A2 são pedidos diferentes, embora ambos tenham Alex Kim no campo de cliente. Agrupe pelo ID do pedido, nunca pelo nome do cliente. Consulte as linhas de itens e os eventos de pagamento de A1:

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

Seus itens custam 300 + 500 = 800 centavos. Os pagamentos P1 e P2 são dois eventos distintos e bem-sucedidos de 400 centavos, também totalizando 800. P3 falhou e não contribuiu com dinheiro. succeeded significa que o evento entra nos cálculos deste projeto; pending e failed não entram.

Uma junção combina linhas relacionadas. AS atribui um alias curto à tabela: i significa items, e p significa payments. ON especifica os IDs de pedido correspondentes. Antes de totalizar qualquer coisa, veja cada par correspondente 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;"

Há quatro linhas: cada um dos dois itens corresponde aos dois pagamentos. Esse é o comportamento real de uma junção. Não significa que ocorreram quatro itens ou quatro pagamentos. SUM soma a expressão ao longo de suas linhas de entrada. Nessa entrada duplicada, as duas somas estão erradas:

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

Os dois totais exibidos são 1600, o dobro do valor real. DISTINCT mantém valores diferentes, não identidades de pagamento diferentes. Ele não resolve esse problema:

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

O resultado é 400, descartando um pagamento legítimo de mesmo valor. Em vez disso, totalize cada lista de fatos separadamente. GROUP BY order_id gera uma linha de resultado para cada ID de pedido. quantity * unit_cents calcula o valor de cada item antes da soma. A granularidade resultante, ou seja, aquilo que uma linha de saída representa, é um pedido.

Uma view é um SELECT nomeado armazenado no banco de dados. Consultá-la executa esse SELECT sobre as linhas de origem atuais; ela não copia nem substitui os itens. Salve sua definição em totals.sql. No shell, cat grava o here-document literal a seguir no arquivo; > substitui o conteúdo antigo. Cole o SQL final sozinho em uma linha. Este bloco deve ser inserido no prompt do shell, não em um prompt interativo do 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 remove apenas uma definição anterior desta view, caso exista. Ele mantém as tabelas e os dados de origem intactos. CREATE VIEW salva a nova definição. < fornece o arquivo salvo como entrada do SQLite:

sqlite3 ledger.db < totals.sql

Nenhuma saída é esperada em caso de sucesso. Consulte a view real:

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

Espere A1 = 800, A2 = 250, A3 = 500 e A4 = 200 centavos, cada um em uma linha. A quantidade de dois de A3 está incluída. Agora você tem um primeiro artefato útil: uma definição de view salva que pode ser executada em outro registro de vendas compatível.

Se um arquivo estiver ausente, ls lista os arquivos deste diretório. Verifique o cd absoluto e a grafia de totals.sql. Um erro de sintaxe SQL identifica a instrução com falha; repita o bloco completo de criação para substituir um arquivo digitado incorretamente e aplique-o novamente. Se o shell ainda estiver esperando após o here-document, digite a linha final SQL que está faltando. Não exclua o registro de vendas para se recuperar.

Combinar totais monetários independentes

Nesta etapa, você adicionará totais de pagamentos e reembolsos bem-sucedidos ao script salvo e combinará os três fatos com uma linha por pedido, mantendo os pedidos não pagos. Comece em Terminal, em /home/labex/project/sales-desk.

A mesma regra de agregação se aplica a pagamentos e reembolsos: cada evento genuíno conta, incluindo valores iguais com IDs diferentes. Filtre status = 'succeeded' dentro do SELECT de cada fato, antes de agrupar. Um reembolso pendente não deve reduzir o dinheiro recebido. Consulte as evidências dos reembolsos:

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

A3 tem dois reembolsos separados e bem-sucedidos de 50 centavos e um reembolso pendente de 250 centavos. Seu total real de reembolsos é 100, não 50 nem 350.

Acrescente as outras duas definições de view. >> adiciona ao final de um arquivo existente em vez de substituir a view de itens. Se precisar repetir esse acréscimo ao se recuperar de um erro, primeiro reconstrua totals.sql usando o bloco completo da etapa anterior e depois acrescente este bloco uma 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

O script salvo pode ser reaplicado: cada definição é removida e recriada a partir dos fatos de origem inalterados. Consulte as views monetárias:

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

Os pagamentos têm A1 = 800, A3 = 600 e A4 = 250. Os reembolsos têm apenas A3 = 100. A2 está ausente de ambos porque não existe pagamento nem reembolso elegível. A ausência em um total de fatos não deve remover o pedido da reconciliação.

LEFT JOIN mantém todas as linhas à sua esquerda, mesmo quando não há correspondência à direita. Comece com orders e depois junte cada view já agregada pelo ID do pedido. Uma correspondência ausente fornece NULL em SQL, que significa ausência de valor, em vez de zero. COALESCE(value, 0) substitui por zero apenas quando esse total é NULL. As condições de status dos eventos já devem estar dentro das views monetárias; adicionar uma condição WHERE sobre uma linha ausente da junção poderia descartar o pedido não pago.

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

Interprete cada linha no contexto do funcionamento normal do negócio:

Cada view de fatos tem no máximo uma linha por pedido antes das junções, portanto um fato não pode multiplicar outro. Preserve as quatro tabelas originais e todos os seus registros; as três views e totals.sql são as únicas definições de banco de dados e o único arquivo que você precisa adicionar. O próximo lab começa com seu próprio registro de vendas novo e transforma esse raciocínio em uma consulta completa de reconciliação.

Resumo

Você demonstrou duas armadilhas dos totais monetários, salvou views reutilizáveis de fatos com uma linha por pedido e as combinou com LEFT JOIN e COALESCE. Eventos separados de mesmo valor continuaram sendo eventos reais, e os pedidos não pagos permaneceram visíveis.