Use SQL para calcular um total em execução

o total em execução em SQL pode ser calculado de várias formas. Este artigo irá abranger dois métodos: as ligações e as funções da janela.

vamos primeiro olhar para como calcular o total de execução usando a junção interna. Ao fazer isso, você não só vai aprender mais sobre as condições de adesão, mas ver como pegar o resultado e resumi-lo, para obter o total de execução.

uma vez que você tenha visto como fazê-lo estilo “old school”, vamos usar a cláusula OVER para calcular os totais em execução usando uma função janela. Este método é mais recente e mais conciso de usar.

todos os exemplos para esta lição são baseados no Microsoft SQL Server Management Studio e no WideWorldImporters database. Você pode começar a usar estas ferramentas gratuitas usando o meu guia começar a usar o servidor SQL 2016.

o que é um total em execução?

nosso objetivo é calcular o total de execução que reinicia sempre que a data de Transaction muda. Totalizaremos O montante da transacção. Para cada fatura subsequente na data da transação, o total de execução deve igualar o total de faturação anterior mais o montante de transação atual.

verá isto em acção no seguinte exemplo. O total em circulação para a factura 3, é o total em circulação anterior de 3110,75 mais O montante da transacção da factura 3 103.50

Calculate A Running Total in SQL using an INNER JOIN

We first calculate the running total using the INNER JOINS. Este método revela mais da mecânica de calcular um total de execução do que usando a partição. Como tal, dá-lhe outra oportunidade de compreender as ligações internas e aplicar esses conceitos a outro caso de uso.

Existem três passos para resolver este problema:

1. Obter linhas para a execução total
2. detalhes de Configuração para o total em execução usando associações internas
3. Calcular o total em execução, através do resumo de dados.Vamos começar!
   

   Passo 1-Obter linhas para executar Total

   a fim de calcular o total em execução, vamos consultar a tabela de transacções de clientes. Incluiremos o InvoiceID, a data de Transaction e o montante de Transaction no nosso resultado. É claro que o total de execução é calculado a partir do montante de transacção.

   aqui está a consulta para obter os dados básicos.

   SELECT InvoiceID ,TransactionDate ,TransactionAmountFROM Sales.CustomerTransactionsWHERE TransactionTypeID = 1ORDER BY TransactionDate

   Aqui estão os dados com os quais vamos trabalhar.

   

   na verdade, este passo destina-se a familiarizá-lo com a informação básica. Não há necessidade de o fazer. No entanto, às vezes eu gostaria de recomendar a consulta básica apenas para ver os dados. Além disso, também para garantir que não há anomalias ou situações especiais que eu preciso acomodar.

   Passo 2 – detalhes de configuração para a execução total usando junções internas

   para este passo, vamos obter os detalhes configurados para que possamos calcular o total de execução. A fim de fazer isso, vamos obter cada InvoiceID o montante da transação e todos os montantes da transação antes.

   para ser capaz de fazer isso, vamos juntar a tabela de Transações Customer para si.Se fizermos isto sem nenhuma condição de adesão, teremos todas as combinações de transacções, isto não é o que queremos.

   para garantir que obtemos a combinação adequada de linhas de cada tabela, adicionaremos duas condições de junção. Um para obter cada fatura e aqueles antes dele (Verde).

   o segundo garante que só incluímos facturas na mesma data de transacção (vermelho)

   SELECT T1.InvoiceID ,T2.InvoiceID ,T1.TransactionDate ,T1.TransactionAmount ,T2.TransactionAmountFROM Sales.CustomerTransactions T1 INNER JOIN Sales.CustomerTransactions T2 ON T1.InvoiceID >= T2.InvoiceID AND T1.TransactionDate = T2.TransactionDateWHERE T1.TransactionTypeID = 1ORDER BY T1.InvoiceID, T1.TransactionAmount

   vamos ver como isto funciona.

   a condição mais fácil de entender é onde combinamos TransactionDate. Isto garante que a correspondência de faturas tem uma data de transação comum. Se esta fosse a única adesão que fizemos, estaríamos a calcular um subtotal para todas as transacções dentro de uma data.Uma vez que queremos calcular o total de execução, temos de obter, de alguma forma, para cada facturação, O montante de transacção da factura e de todas as facturas anteriores. Em outras palavras, retorne todas as linhas correspondentes onde a fatura é maior ou igual às faturas correspondentes que estamos tentando total.

   

   If you look at the result above, you’ll see that for each fatura listed in the first column (T1.InvoiceID), é maior ou igual ao Invoiceid’s na segunda coluna (T2.InvoiceID).

   este é o resultado da condição de junção T1.InvoiceID > = T2.InvoiceID.

   o resultado desta junção e as condições de junção é que agora temos as matérias – primas para calcular o total de execução.

   Notice how the first, third, and fourth columns repeat. Podemos usar isso a nosso favor para resumir o resultado para chegar ao total de execução.

   Passo 3-calcular a Execução Total resumindo as linhas

   com a informação detalhada à mão, o passo final é resumir as linhas. Ao fazê-lo, podemos calcular os totais em execução.

   aqui está a consulta que usamos para realizar o resumo:

   SELECT T1.InvoiceID ,T1.TransactionDate ,T1.TransactionAmount ,Sum(T2.TransactionAmount) RunningTotalFROM Sales.CustomerTransactions T1 INNER JOIN Sales.CustomerTransactions T2 ON T1.InvoiceID >= T2.InvoiceID AND T1.TransactionDate = T2.TransactionDateWHERE T1.TransactionTypeID = 1GROUP BY T1.InvoiceID ,T1.TransactionDate ,T1.TransactionAmountORDER BY T1.InvoiceID ,T1.TransactionAmount

   repare como nos agrupamos por T1.InvoiceID, T1.TransactionDate, e T1.Montante de transacção. Estes são os valores que foram repetidos em nossos dados detalhados na Etapa 2.

   o total de execução é derivado do T2.Montante de transacção. Recorde-se que estes valores são montantes de Transacção de todas as facturas anteriores à factura apresentada. Em outras palavras, a fatura exibida é maior ou igual a eles.

   Isto permite-nos construir um total em execução.

   cada factura subsequente na lista, calcula o seu valor total, somando todo o montante de transacção da sua factura e dos montantes anteriores.

   

   Agora que você tem visto de uma forma tradicional para se chegar ao total de execução, e, talvez, ganhou uma maior apreciação de como usar associações e condições de associação para resolvê-lo, vamos olhar para um dos novos recursos do SQL, partições, e ver como eles podem ser usados para obter o mesmo resultado.

   calcule um total de execução em SQL usando uma cláusula OVER

   a cláusula OVER se uma declaração muito poderosa. Ele permite que você defina um conjunto de linhas, dentro de um conjunto de resultados que uma operação afeta.

   tal como OFFSET e FETCH, permite-nos obter uma gama específica de linhas a partir de um conjunto de resultados, a cláusula OVER permite-nos fazer uma operação semelhante, em relação à linha actual, para uma coluna específica.

   Usando sobre, podemos definir uma janela sobre um conjunto especificado de linhas, para as quais podemos aplicar funções, como a sum.Para que entendas o conceito, vamos dividir isto em dois passos.:

   1. Partition data using the OVER clause.
   2. ordenem partições com ordem.

   let’s roll.

   Passo 1-Dados de partição usando a cláusula

   quando dizemos que queremos criar um total de execução para todas as facturas dentro de uma data de Transaction, queremos dividir os nossos dados por TransactionDate. Para particionar os dados, podemos usar a cláusula over.

   na seguinte nota de declaração somamos O montante da transacção e, após a soma, existe uma cláusula sobre.

   notice Also there is no GROUP BY clause. Isto é surpreendente, tipicamente funções agregadas, como a soma, requerem um grupo por cláusula; por que é este o caso?

   uma vez que estamos usando a cláusula sobre, a soma é considerada uma função de janela – ela opera em qualquer linha definida na cláusula sobre.

   aqui está a função da janela que usaremos:

   SUM (TransactionAmount) sobre(partição por TransactionDate) RunningTotal

   o que torna esta função do windows é a cláusula sobre. obtém a partição da peça por TransactionDate. @ info / plain Isto significa que a soma opera em todas as linhas com a mesma TransactionDate. Isto define a janela das linhas que a função SUM afecta.

   aqui está a consulta até agora.

   SELECT InvoiceID ,TransactionDate ,TransactionAmount ,SUM(TransactionAmount) OVER(PARTITION BY TransactionDate) RunningTotalFROM Sales.CustomerTransactions T1WHERE TransactionTypeID = 1ORDER BY InvoiceID ,TransactionAmount

   

   Etapa 2 – a Ordem de Partições com Order BY

   Até este ponto temos particiona os dados e são capazes de calcular o subtotal para todos os TransactionAmount valores dentro de uma TransactionDate. O próximo passo é agora calcular o subtotal.

   para fazer isso podemos usar a ordem dentro da cláusula OVER para definir o “escopo” da função window. A ordem pela ordem indicada a ordem lógica que a função da janela opera.

   aqui está a função da janela que vamos usar:

   SUM(TransactionAmount) OVER (PARTITION BY TransactionDate ORDER BY InvoiceID) RunningTotal

   The difference between this window function and that from the first step, is ORDER BY InvoiceID. Isto especifica a ordem lógica para processar dentro da partição.

   sem a ordem pela ordem lógica é esperar até que estejamos no fim da janela para calcular a soma. Com a ordem por especificado, a ordem lógica é calcular uma soma para cada linha, incluindo valores de TransactionAmount anteriores dentro da janela.

   SELECT InvoiceID ,TransactionDate ,TransactionAmount ,SUM(TransactionAmount) OVER(PARTITION BY TransactionDate ORDER BY InvoiceID) RunningTotalFROM Sales.CustomerTransactions T1WHERE TransactionTypeID = 1ORDER BY InvoiceID ,TransactionAmount

   aqui está o resultado de executar a consulta.

   

   quando executou esta consulta, reparou quão mais rápido ele correu do que aquele que usava junções internas? Fiquei surpreendido. Eu sei que a operação de junção interna consome muitos recursos como as combinações ou linhas se tornam grandes, mas eu teria pensado que teria o mesmo caso para a solução usando mais.

   eu encorajaria você a olhar para o plano de consulta de cada consulta. Você vai começar a aprender um pouco sobre SQL quando começar a fazer isso.