Utilizzare SQL per calcolare un totale di esecuzione

Il totale di esecuzione in SQL può essere calcolato in diversi modi. Questo articolo tratterà due metodi: i Join e le funzioni della finestra.

Per prima cosa vedremo come calcolare il totale corrente usando il JOIN INTERNO. In questo modo, non solo imparerai di più sulle condizioni di join, ma vedrai come prendere il risultato e riassumerlo, per ottenere il totale di esecuzione.

Una volta che hai visto come farlo in stile “vecchia scuola”, useremo la clausola OVER per calcolare i totali correnti usando una funzione window. Questo metodo è più recente e più conciso da usare.

Tutti gli esempi di questa lezione sono basati su Microsoft SQL Server Management Studio e il database WideWorldImporters. È possibile iniziare a utilizzare questi strumenti gratuiti utilizzando la mia guida Iniziare a utilizzare SQL Server 2016.

Che cos’è un totale corrente?

Il nostro obiettivo è calcolare il totale corrente che si ripristina ogni volta che cambia TransactionDate. Totalizzeremo l’importo delle transazioni. Per ogni fattura successiva entro la data della transazione, il RunningTotal deve essere uguale al totale di esecuzione di InvoiceID precedente più il TransactionAmount corrente.

Lo vedrai in azione nell’esempio seguente. RunningTotal per Fattura 3, è il precedente RunningTotal di 3110.75 più l’importo della transazione della fattura 3 di 103.50

Calcola un Running Total in SQL usando un INNER JOIN

Per prima cosa calcoliamo il running total usando i INNER JOIN. Questo metodo rivela più della meccanica di calcolo di un totale di esecuzione rispetto all’utilizzo della PARTIZIONE. In quanto tale, ti dà un’altra opportunità di comprendere i JOIN INTERNI e applicare questi concetti a un altro caso d’uso.

Ci sono tre passaggi per risolvere questo problema:

1. Ottieni righe per il totale di esecuzione
2. Dettagli di installazione per il totale di esecuzione utilizzando inner join
3. Calcola il totale di esecuzione riepilogando i dati.

Iniziamo!

Fase 1 – Ottenere righe per il totale in esecuzione

Per calcolare il totale in esecuzione, interrogheremo la tabella CustomerTransactions. Includeremo InvoiceID, TransactionDate e TransactionAmount nel nostro risultato. Naturalmente, il totale corrente viene calcolato dalla transazioneimporto.

Ecco la query per ottenere i dati di base.

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

Ecco i dati con cui lavoreremo.

In realtà, questo passaggio ha lo scopo di farti conoscere le informazioni di base. Non c’è bisogno di farlo davvero. Tuttavia, a volte vorrei raccomandare la query di base solo per vedere i dati. Inoltre, per garantire anche che non ci siano anomalie o situazioni particolari che ho bisogno di ospitare.

Passo 2 – Dettagli di configurazione per il totale di esecuzione utilizzando Inner Join

Per questo passaggio, otterremo i dettagli impostati in modo da poter calcolare il totale di esecuzione. Per fare questo, otterremo ogni InvoiceID l’importo della transazione e tutti gli importi della transazione prima.

Per poterlo fare, uniremo la tabella CustomerTransactions a se stessa.

Se lo facciamo senza condizioni di join otterremmo ogni combinazione di transazioni, questo non è quello che vogliamo.

Per garantire la corretta combinazione di righe da ogni tabella, aggiungeremo due condizioni di join. Uno per ottenere ogni fattura e quelli precedenti (verde).

Il secondo assicura che includiamo solo le fatture alla stessa data di transazione (rosso)

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

Vediamo come funziona.

La condizione più semplice da capire è dove abbiniamo TransactionDate. Ciò garantisce che la corrispondenza delle fatture abbia una data di transazione comune. Se questo fosse l’unico join che abbiamo fatto, avremmo calcolato un totale secondario per tutte le transazioni entro una data.

Poiché vogliamo calcolare il totale corrente, dobbiamo in qualche modo ottenere per ogni InvoiceID il TransactionAmount per la fattura e tutte le fatture prima di esso. In altre parole, restituisci tutte le righe corrispondenti in cui la fattura è maggiore o uguale alle fatture corrispondenti che stiamo cercando di totalizzare.

Se si guarda il risultato di cui sopra, vedrete che per ogni fattura elencati nella prima colonna (T1.InvoiceID), È maggiore o uguale a InvoiceID nella seconda colonna (T2.InvoiceID).

Questo è il risultato della condizione di join T1.InvoiceID > = T2.InvoiceID.

Il risultato di questo join e delle condizioni join è che ora abbiamo le materie prime per calcolare il totale corrente.

Si noti come si ripetono la prima, la terza e la quarta colonna. Possiamo usare questo a nostro vantaggio per riassumere il risultato per arrivare al totale di esecuzione.

Step 3-Calcola il totale di esecuzione riassumendo le righe

Con le informazioni dettagliate a portata di mano, il passo finale è quello di riassumere le righe. Così facendo ci permette di calcolare i totali correnti.

Ecco la query che usiamo per eseguire il riepilogo:

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

Notate come raggruppiamo per T1.InvoiceID, T1.TransactionDate, e T1.Importo delle transazioni. Questi sono i valori che sono stati ripetuti nei nostri dati dettagliati nel passaggio 2.

Il Totale corrente è derivato da T2.Importo delle transazioni. Richiamo questi valori sono TransactionAmount da tutte le fatture prima della fattura visualizzata. In altre parole, la fattura visualizzata è maggiore o uguale a loro.

Questo ci permette di costruire un totale di esecuzione.

Ogni fattura successiva nell’elenco, sta calcolando il suo valore RunningTotal sommando tutte le TransactionAmount dalla sua Fattura e quelle precedenti.

Ora che avete visto un modo tradizionale per arrivare al totale di esecuzione, e forse guadagnato un maggiore apprezzamento di utilizzo di join e le condizioni di join per risolverlo, diamo un’occhiata a una delle nuove funzionalità di SQL, partizioni, e vedere come essi possono essere utilizzati per ottenere lo stesso risultato.

Calcola un totale di esecuzione in SQL utilizzando una clausola OVER

La clausola OVER se un’istruzione molto potente. Consente di definire un insieme di righe, all’interno di un set di risultati che un’operazione influisce.

Proprio come OFFSET e FETCH ci permettono di recuperare un intervallo specifico di righe da un set di risultati, la clausola OVER ci consente di eseguire un’operazione simile, relativa alla riga corrente, per una colonna specifica.

Usando OVER, possiamo definire una finestra su un set specificato di righe, a cui possiamo applicare funzioni, come sum.

Per comprendere il concetto, lo suddivideremo in due passaggi:

1. Partizione dati utilizzando la clausola OVER.
2. Ordina partizioni con Ordine.

Andiamo.

Step 1 – Partition data using OVER Clause

Quando diciamo che vogliamo vogliamo creare un totale corrente per tutte le fatture all’interno di un TransactionDate, vogliamo partizionare i nostri dati per TransactionDate. Per partizionare i dati, possiamo usare la clausola over.

Nel seguente avviso di istruzione sommiamo il TransactionAmount e dopo la SOMMA c’è una clausola OVER.

Si noti inoltre che non esiste una clausola GROUP BY. Questo è sorprendente, in genere le funzioni aggregate, come SUM, richiedono una clausola GROUP BY; perché è così?

Poiché stiamo usando la clausola OVER, la SOMMA è considerata una funzione window – opera su qualsiasi riga definita nella clausola OVER.

Ecco la funzione della finestra che useremo:

SUM(TransactionAmount) OVER(PARTITION BY TransactionDate) RunningTotal

Ciò che rende questa funzione di Windows è la clausola OVER. controlla la PARTIZIONE DELLA parte PER TransactionDate. Ciò significa che la SOMMA opera su tutte le righe con la stessa TransactionDate. Questo definisce la finestra di righe che la funzione SUM influisce.

Ecco la query finora.

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

Step 2 – Ordina le partizioni con Order BY

Fino a questo punto abbiamo partizionato i dati e siamo in grado di calcolare un subtotale per tutti i valori TransactionAmount all’interno di TransactionDate. Il prossimo passo è ora calcolare il subtotale.

Per fare ciò possiamo usare ORDER BY all’interno della clausola OVER per definire l ‘ “ambito” della funzione window. L’ORDINE DA specificato l’ordine logico la funzione della finestra opera.

Ecco la funzione finestra che useremo:

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

La differenza tra questa funzione della finestra e quella del primo passaggio è ORDER BY InvoiceID. Specifica l’ordine logico da elaborare all’interno della partizione.

Senza l’ORDINE PER ordine logico è aspettare fino a quando siamo alla fine della finestra per calcolare la somma. Con l’ORDINE specificato, l’ordine logico consiste nel calcolare una somma per ogni riga inclusi i valori TransactionAmount precedenti all’interno della finestra.

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

Ecco il risultato dell’esecuzione della query.

Quando hai eseguito questa query hai notato quanto più veloce è stato eseguito rispetto a quello che utilizza INNER JOIN? Sono rimasto sorpreso. So che l’operazione di JOIN INTERNO consuma molte risorse man mano che le combinazioni o le righe diventano grandi, ma avrei pensato che avrebbe avuto lo stesso caso per la soluzione using OVER.

Ti incoraggio a guardare il piano di query di ogni query. Inizierai a imparare un po ‘ di SQL quando inizi a farlo.