Pare de escrever código extra – você pode fazer isso em SQL

Geshan Manandhar Blocked Unblock Seguir Seguindo 3 de janeiro Imagem de meditação de Pixabay

“SQL, Lisp e Haskell são as únicas linguagens de programação que vi, onde se gasta mais tempo pensando do que digitando.” – Philip Greenspun

Mesmo pensando mais do que digitando SQL (Structured Query Language), os engenheiros de software o utilizam como uma forma de extrair apenas os dados.

Geralmente, não aproveitamos o poder de manipulação de dados do SQL para fazer as alterações necessárias em nosso código.

Isso é bastante comum entre engenheiros de software que trabalham em aplicativos da web. Outra coisa que sentimos falta é que, se fizermos a manipulação diretamente no SQL, os dados extraídos terão o mesmo formato para qualquer linguagem de programação. Este post tem como objetivo esclarecer os poderes do SQL que você pode conhecer, mas geralmente não usa.

Tea Lights imagem de Pixabay

TL; DR

Aqui estão os destaques:

  • Use SQL para fazer matemática como soma, média etc.
  • Utilize-o para agrupar um a muitos valores relacionais, como obter categorias de produtos.
  • Aproveite o SQL para manipulação de strings como o CONCAT_WS para concatenar nome e sobrenome.
  • Explore o SQL para classificar por uma fórmula de prioridade personalizada.

Exemplos abaixo …

Algumas suposições

Abaixo estão algumas suposições feitas para este post:

  1. Só porque você pode fazê-lo em SQL, não significa que você precisa fazê-lo no SQL e usar os recursos do banco de dados. Sempre crie um perfil de sua solução e decida onde é melhor usá-la. Houve sugestões de que é mais difícil e caro dimensionar um banco de dados do que o código do aplicativo.
  2. Use o SQL de maneira inteligente e ideal. Sempre pense nos recursos necessários, como processador e memória. EXPLAIN é seu amigo para otimização de consultas.
  3. Este post não defende colocar toda a lógica no banco de dados como na forma de gatilhos, procedimentos armazenados ou visualizações. O código no banco de dados geralmente não pode ser colocado em um sistema de controle de versão e é difícil testar o código do banco de dados.
  4. O SQL é geralmente insensível a maiúsculas e minúsculas, portanto, tenha cuidado ao executar operações como o CONCAT ou qualquer outra manipulação de string.
  5. Em sistemas distribuídos, é um equilíbrio de trade-offs. O mesmo se aplica para decidir fazer algo no SQL ou na linguagem de programação. Avalie suas opções e escolha a melhor, dependendo do caso de uso.
  6. O exemplo abaixo usa MYSQL, portanto, a sintaxe e a implementação para outros tipos de SQL serão diferentes.

O exemplo

Será mais fácil explicar as superpotências do SQL colocando-as em ação em um exemplo. Abaixo está um esquema básico com 2 tabelas no MYSQL para um microsserviço de reembolso:

Existem 2 reembolsos e 7 pagamentos relacionados como dados de exemplo.

Algumas suposições

Para o esquema e aplicativos de exemplo de microsserviço de reembolso, as seguintes suposições são feitas:

  1. Microcesso e estrutura de dados de reembolso armazenam o fk_item (o id do item pedido / entregue), mas não é uma chave estrangeira difícil.
  2. O item pode ser reembolsado em dinheiro ou crédito pelo valor pago pelo mesmo.
  3. Os itens podem ser reembolsados muitas vezes, desde que o saldo restante possa cobrir o valor de reembolso solicitado para cada caixa e crédito. Por exemplo, digamos que o item recebeu 50 em dinheiro e 50 em crédito. 2 reembolsos de 20 em dinheiro e 20 créditos podem ser feitos. Portanto, após essas transações, o saldo será de 10 em dinheiro e 10 de crédito para esse item (50–20–20).
  4. Cada reembolso pode ter vários itens para pagamento. Cada pagamento pode ser do tipo em dinheiro ou crédito.
  5. Todos os valores são armazenados em centavos, de modo que são inteiros.

Agora vamos usar alguns poderes de SQL. Você pode encontrar o exemplo com as consultas relacionadas em execução neste SQL Fiddle .

Faça as contas no SQL

Como engenheiros de software, digamos que, se precisarmos encontrar o montante total em dinheiro e crédito reembolsado por um item, o que faríamos? Nós correríamos algo como:

SELECT fk_item, fk_refund, amount, is_cash FROM payment WHERE fk_item=2001;

Com os dados atuais, ele dará 3 linhas como abaixo:

Com estas 3 linhas, nós passamos por elas. Se for dinheiro, acumule-o na variável cashBalance, se não for somar a variável creditBalace. Mas em vez disso, seria muito mais fácil (e provavelmente mais rápido) fazer no SQL assim:

SELECT fk_item, SUM(amount) AS total_paid, IF(is_cash = 1, 'cash', 'credit') as type FROM payment WHERE fk_item = 2001 GROUP BY fk_item, is_cash;

Resultando nisso:

O resultado é alcançado facilmente agora. Se você precisar do reembolso total para o item, basta alterar o GROUP BY para estar no fk_item e pronto. Para 2 e 3 registros, não será significativo. Se houver 20 reembolsos para esse item, a primeira solução com um loop é escrever mais código sem ganho. Como soma, outras funções SQL podem ser usadas também. Operações matemáticas simples, como soma , multiplicação, média , etc., podem ser fáceis com o SQL. Isso significa que não há mais loops.

Use GROUP_CONCAT para buscar valores de relação 1: m relacionados

Concat de grupo é uma operação robusta em bancos de dados SQL. É fundamental quando você precisa obter dados de um relacionamento para muitos.

Por exemplo, digamos que você deseja obter todas as tags de uma postagem no blog ou deseja obter todas as categorias de um produto. Com relação a esse exemplo de reembolso, um item pode ser reembolsado várias vezes. Assim, obteremos todos os reembolsos associados ao código do item. Para obter isso, executaremos apenas 1 consulta e obteremos sem nenhum loop no código, como abaixo:

SELECT fk_item, GROUP_CONCAT(DISTINCT fk_refund) refund_ids FROM payment WHERE fk_item = 2001;

Isto resulta em:

Agora sabemos que o item 2001 foi reembolsado duas vezes por dois reembolsos. Vai ser fácil para explodir os IDs de reembolso com , e prosseguir com qualquer operação relacionada. Esteja ciente de que o comprimento máximo de GROUP_CONCAT no MYSQL é de 1024 caracteres.

Manipulação de string

Muitas tarefas de manipulação de strings , como substring, concatenation, change case e string compare, podem ser feitas em SQL. Com este exemplo, mostrarei o uso de CONCAT_WS . É concat com um separador. Também pode ser usado para selecionar, por exemplo, um first_name e last_name com um espaço entre eles.

No caso de ter um nome do meio opcional, o COALESCE pode ser usado com o CONCAT_WS . Isso é algo para você explorar :).

Neste exemplo, selecionarei refund_nr com seu motivo relacionado:

SELECT CONCAT_WS("-", refund_nr, reason) AS refund_nr_with_reason FROM refund;

Resultando em:

Se isso precisar ser mostrado no documento de nota de crédito, por exemplo, nenhum código extra será necessário para unir os valores novamente. O SQL torna isso mais fácil novamente. Cuidado novamente que o SQL é uma linguagem que não diferencia maiúsculas de minúsculas.

Classificando com uma fórmula personalizada

Todos os engenheiros de software sabem que você pode classificar com base em uma coluna. Mas se você receber uma fórmula de prioridade personalizada para classificar, o que você faria? Provavelmente novamente recorrer ao código e loop para classificar. Então, vamos definir as regras de fórmula de prioridade para o exemplo acima:

  1. Reembolsos de clientes premium obtêm a prioridade mais alta (nós a cortamos com uma prioridade de 9999999999)
  2. Diferentemente dos clientes premium, as restituições em dinheiro têm uma prioridade de valor * 25 e, para crédito, é o valor * 20.

De acordo com as regras acima, é decidido que os clientes premium e a prioridade acima de 50000 (em centavos) serão processados primeiro. Em seguida, outros reembolsos serão processados. Vamos obter os reembolsos de prioridade abaixo:

SELECT r.refund_nr, r.reason, p.fk_item, p.amount, p.is_cash, IF(p.premium_customer = 1, 9999999999, p.amount * (IF(is_cash = 1, 25, 20))) AS priority FROM refund AS r INNER JOIN payment AS p ON r.id = p.fk_refund HAVING priority > 50000 ORDER BY priority DESC

Os resultados estão abaixo:

Com o uso adequado de IF no SQL, classificar por uma fórmula de prioridade personalizada é muito mais fácil do que tentar fazer isso com loops no código. Observe que mesmo quantidades menores, como 7,5 (750 centavos) e 9,0 (900 centavos), atingiram a prioridade mais alta, pois esses valores de pagamento de reembolso estavam associados a clientes premium.

Use os superpoderes do SQL para facilitar sua vida como engenheiro de software.

Você pode brincar com o exemplo e executar suas consultas no SQL Fiddle .

Conclusão

Existem outros truques de SQL que podem ajudá-lo como engenheiro de software. Como, UPDATE com INSERT usando ON DUPLICATE KEY UPDATE . Sempre que você tiver uma coceira para manipular dados extraídos de um banco de dados no código com loops, pense novamente. Como qualquer outra linguagem ou ferramenta, o SQL é poderoso, mas use-o com sabedoria. O principal argumento desta história é:

Explore o poder do SQL de maneira ótima e inteligente para escrever menos código porque “o melhor código é o código que nunca foi escrito”. Se não estiver escrito, não há necessidade de mantê-lo.

Se você chegou até aqui, por favor, não esqueça de adicionar algumas “palmas” :).

Obrigado pela leitura!