Negli articoli precedenti di questa serie, abbiamo posto le basi su come creare un report. Faremo un ulteriore passo avanti e vedremo come utilizzare loperatore di tabella PIVOT di SQL Server. Inizieremo dalla semplice query e progrediremo lentamente verso SQL dinamico e PIVOT. Cominciamo.
Modello di dati e idea generale
Il modello di dati che utilizzeremo è lo stesso che abbiamo utilizzato in questa serie.
Il nostro compito oggi è creare un rapporto (utilizzando loperatore PIVOT di SQL Server) in cui ogni città sarà in una riga separata e dove conteremo il numero di tutti i risultati delle chiamate relativi a ciascuna città. Pertanto, tutti i risultati (tutti i valori del dizionario) saranno colonne nel nostro rapporto.
Poiché “unimmagine vale più di mille parole”, descriveremo anche il nostro obiettivo finale con limmagine.
Per ottenere ciò, dovremo utilizzare i dati di 4 tabelle call, call_outcome, customer e city. Pertanto, sarebbe opportuno esaminare più da vicino il modello di seguito e analizzare come sono correlate queste tabelle.
Inizieremo esaminando i dati nelle tabelle e, con ogni query, ci avvicineremo di un passo a il risultato desiderato (query). Lapproccio utilizzato qui è lo stesso di quello utilizzato nellarticolo Learn SQL: creare un report manualmente utilizzando le query SQL.
Categorie e dati del report
Il primo set di query con cui inizieremo è quello che esamina i dati attualmente presenti in tutte e 4 le tabelle che dovremo utilizzare per creare il report. Lo faremo in modo da poter confermare che il report finale ha restituito ciò che dovrebbe. Dopodiché, creeremo query wh che restituiscono le categorie dei rapporti e i dati dei rapporti, nonché le query della tabella PIVOT di SQL Server.
|
1
2
3
4
5
|
– – 1 – seleziona i dati di cui abbiamo bisogno
SELECT * FROM call;
SELEZIONA * FROM call_outcome;
SELEZIONA * DA cliente;
SELEZIONA * DA città;
|
Queste query sono semplici quanto potrebbero essere, quindi non cè niente di speciale da commentare riguardo alla loro sintassi. Per quanto riguarda i dati restituiti, dobbiamo essere consapevoli di quanto segue:
- La tabella delle città contiene 6 città, quindi dovremmo avere 6 righe nel rapporto finale
- Ci sono 3 possibili risultati nella tabella call_outcome, quindi dovremmo avere 3 colonne per i risultati (4 colonne in totale – > una è per il nome della città)
- La chiamata alla tabella ha 10 righe (solo 8 nellimmagine sopra), quindi nel tavolo finale, la somma di tutti i risultati dovrebbe essere 10 (ogni chiamata può avere esattamente 1 risultato)
La prossima cosa da fare è preparare le categorie di rapporti. Vogliamo avere una combinazione di tutte le città e di tutti i possibili risultati delle chiamate. A tal fine, utilizzeremo CROSS JOIN (prodotto cartesiano).
Avere tutte le categorie di rapporti garantisce che avremo una riga nel rapporto, non importa se quella coppia ha dati o meno. Ed è quello che vogliamo: vedere 0 in quella categoria di rapporti e non perdere completamente quella categoria.
La prossima cosa da fare è unire tutte e 4 le tabelle contenenti i dati di cui abbiamo bisogno.
La query in sé non è complessa. Nel risultato, dovremmo notare che abbiamo 10 righe, lo stesso numero del numero di chiamate che abbiamo nel database. Inoltre, ho utilizzato la funzione DATEDIFF per restituire la durata di ogni chiamata in secondi. Potrei semplicemente mettere call.id qui, ma volevo ricordare a noi stessi la funzione DATEDIFF che abbiamo menzionato in questo articolo. Potremmo utilizzare questa durata se avessimo bisogno di una SUM / AVG di secondi spesi per ciascuna categoria di rapporti (città & risultato della chiamata).
Rapporto senza SQL Server PIVOT TABLE
Ora siamo pronti per unire categorie e dati. Useremo entrambe le query menzionate in precedenza come sottoquery e ci uniremo a loro usando LEFT JOIN (in modo da avere tutte le categorie presenti nelloutput finale).
Abbiamo tutte le 18 combinazioni (6 città diverse * 3 risultati di chiamata diversi) e abbiamo anche tutte e 10 le chiamate presenti qui (righe con la durata della chiamata < > NULL).
Creiamo ora la query PIVOT di SQL Server.
TABELLA PIVOT di SQL Server (statica)
Finora, siamo riusciti a estrarre i dati di cui abbiamo bisogno e li abbiamo come elenco. Potremmo esportare i dati in Excel e fare trasformazioni lì. Lo faremo effettivamente nel prossimo articolo.Tuttavia, oggi, vogliamo risolvere questo problema utilizzando loperatore PIVOT di SQL Server. In questa sezione, tratteremo il PIVOT di SQL Server “statico”. Diamo prima unocchiata alla query utilizzata e al risultato.
Possiamo notare che il risultato è esattamente quello che volevamo. Abbiamo ogni città in una riga e tutte e 3 le categorie per i risultati delle chiamate in colonne separate. Se non ci sono dati per una certa coppia città-risultato, quella cella deve contenere il valore 0.
Commentiamo ora la query PIVOT di SQL Server. Queste sono alcune cose che trovo importante menzionare qui:
- Nella prima parte di la query – report_data, abbiamo copiato e incollato la query dalla sezione precedente. Lunica modifica è stata che non avevamo ORDER BY e GROUP BY in questa parte della query perché non possono essere applicati qui (dovrebbero andare dopo viene generato il risultato finale della query)
- La parte PIVOT della query è composta da 2 parti. Nella prima, definiremo quale funzione aggregata vogliamo applicare. Nel nostro caso, questa è – COUNT ( call_duration) In FOR parte del qu ery, definiremo le colonne. Elenchiamo letteralmente tutti i valori che vogliamo avere come colonne. Questo è hard-coding. Se aggiungiamo un nuovo valore alla tabella, ciò non influirà sulla query (e dovrebbe)
Questa query fa il suo lavoro e funzionerà perfettamente se non cambiamo mai (aggiungi nuovo , cancella, aggiorna i loro nomi) richiama i risultati nella relativa tabella. Questo potrebbe rivelarsi il problema nei casi in cui è necessario apportare modifiche al dizionario. Non vogliamo pensare se cè una query che non funziona come previsto. Per risolvere questo problema, avremo bisogno di utilizzare un nuovo concetto: SQL dinamico.
TABELLA PIVOT di SQL Server dinamico
Prima di passare al codice, spieghiamo brevemente cosa sia realmente lSQL dinamico è (ne daremo una revisione molto più dettagliata nei prossimi articoli). La spiegazione più semplice è che in SQL è possibile “creare” le query come stringhe e quindi passare quella stringa come parametro per la procedura memorizzata di SQL Server. Questa procedura eseguirà le istruzioni SQL memorizzate in quella stringa (ovviamente , se la sintassi è corretta).
Anche se questo potrebbe sembrare qualcosa di non così comunemente usato, ha alcuni punti in cui ti semplifica la vita. Creazione di una query di tabella PIVOT di SQL Server con numero di colonne è esattamente uno di questi casi.
Lidea di questo approccio è la seguente:
- Dichiareremo una variabile in cui memorizzare tutti i nomi di colonna (@colonne ) e la variabile in cui memorizzare la query completa (@query)
- Utilizzando la query SELECT, troveremo tutti i valori memorizzati nella colonna result_text. Il codice seguente – @columns + = QUOTENAME (TRIM (co.outcome_text)) + ,, aggiungerà il nome della colonna allelenco di tutti i nomi di colonna precedenti restituiti dalla query. Il risultato è che abbiamo tutti i nomi di colonna memorizzati in e la variabile @colonne
- Nella variabile @query, memorizzeremo la query completa della sezione precedente, ad eccezione della parte in cui sono definite le colonne. Otterremo questa parte dalla variabile @columns
- Lultima cosa che dobbiamo fare è passare la query completa come parametro alla procedura di sistema SQL Server sp_executesql
Il risultato finale è lo stesso della sezione precedente, ma questa volta siamo sicuri che la nostra query funzionerà anche se apportiamo modifiche a i valori risultato.outcome_text. Inoltre, puoi facilmente modificare questa query e calcolare qualsiasi altro valore, ad esempio, la durata della chiamata SUM / AVG per coppia città-risultato.
Conclusione
Loperatore PIVOT di SQL Server ti offre un visualizzazione completamente nuova di ciò che è possibile ottenere direttamente sul livello del database. Se combinato con lSQL dinamico, questo va anche oltre. Ti incoraggio vivamente a giocarci – non cè modo migliore di imparare che provarlo tu stesso. Nel prossimo articolo, mostreremo come potremmo utilizzare loutput di query SQL e creare tabelle e grafici in Excel (questo funzionerà non solo in SQL Server ma in generale).
Sommario
Impara SQL: CREATE DATABASE & CREATE TABLE Operazioni
Impara SQL: INSERT INTO TABLE
Impara SQL: chiave primaria
Impara SQL: chiave esterna
Impara SQL: istruzione SELECT
Impara SQL: INNER JOIN vs LEFT JOIN
Impara SQL: Script SQL
Impara SQL: Tipi di relazioni
Impara SQL: Join tabelle multiple
Impara SQL: funzioni aggregate
Impara SQL: come scrivere una query SELECT complessa?
Impara SQL: il database INFORMATION_SCHEMA
Impara SQL: tipi di dati SQL
Impara SQL: Teoria degli insiemi
Apprendimento di SQL: funzioni definite dallutente
Apprendimento di SQL: procedure memorizzate definite dallutente
Apprendimento di SQL: viste SQL
Impara SQL: trigger SQL
Impara SQL: pratica query SQL
Impara SQL: esempi di query SQL
Impara SQL: crea un rapporto manualmente utilizzando query SQL
Impara SQL: funzioni data e ora di SQL Server
Impara SQL: crea rapporti SQL Server utilizzando le funzioni di data e ora
Impara SQL: tabelle pivot di SQL Server
Impara SQL: esportazione di SQL Server in Excel
Impara SQL: Introduzione ai loop di SQL Server
Impara SQL: Cursori di SQL Server
Impara SQL: Best practice per SQL per leliminazione e laggiornamento dei dati
Impara SQL: convenzioni di denominazione
Impara SQL: processi relativi a SQL
Impara SQL: join non equi in SQL Server
Impara SQL: SQL Injection
- Autore
- Post recenti
I suoi impegni passati e presenti variano dalla progettazione e codifica di database allinsegnamento, alla consulenza e alla scrittura di database. Inoltre, non dimenticare, BI, creazione di algoritmi, scacchi, filatelia, 2 cani, 2 gatti, 1 moglie, 1 bambino …
Puoi trovarlo su LinkedIn
Visualizza tutti i post di Emil Drkusic
- Learn SQL: SQL Injection – 2 novembre 2020
- Learn SQL: Non-Equi Joins in SQL Server – 29 settembre 2020
- Learn SQL: Lavori relativi a SQL – 1 settembre 2020