Che cos'è l'SQL avanzato?

Siete confusi sulle competenze SQL avanzate? Cosa sono? Questo articolo vi spiegherà cosa può significare SQL avanzato, in particolare come lo utilizziamo su LearnSQL.it.

Sono sicuro che vi capiterà spesso di trovare le frasi "competenze SQL avanzate" o "argomenti SQL avanzati". Leggete un articolo su SQL avanzato e siete soddisfatti di quanto questi argomenti avanzati sembrino facili. Poi parlate con qualcuno e vedete che tutto ciò che sapete è considerato una conoscenza di base dell'SQL. Come vi definite voi stessi? Vi considerate un utente SQL di base, intermedio o avanzato?

Advanced SQL È ovunque

Advanced SQL è ovunque. O almeno lo è la parola "avanzato". È usata molto comunemente dagli studenti e dagli utenti di SQL. Si trova nelle descrizioni dei corsi SQL, negli annunci di lavoro e nelle domande dei colloqui di lavoro. È presente nella letteratura SQL. Lo si sente quando i colleghi parlano al lavoro. È presente in numerosi articoli che cercano di definire cosa sia l'SQL avanzato.

In realtà, non sto cercando di dare una definizione di SQL avanzato. Sto cercando di dirvi un'altra cosa: non esiste un'unica definizione di SQL avanzato! E dovreste smettere di cercarla. Cosa dovreste fare invece?

Accogliere l'incoerenza

Proprio così! Dovete accettare il fatto che il termine "SQL avanzato" è usato in modo incoerente. Ha un significato diverso a seconda del contesto e di chi lo usa.

È logico che SQL avanzato includa una cosa per chi scrive report SQL e qualcosa di completamente diverso per chi assume un analista di dati. Uno sviluppatore di software avrà sicuramente un'altra definizione di ciò che include l'SQL avanzato.

L'immagine è chiara. Advanced SQL non può avere una sola definizione. Quando si legge di competenze avanzate in SQL, bisogna sempre considerare il contesto, chi parla e il suo pubblico.

Cosa potrebbe includere Advanced SQL?

Per esempio, su Stack Overflow c'è una discussione molto interessante sull'SQL avanzato.

La discussione è stata avviata da una persona alla ricerca di un lavoro in SQL che ha notato che ci sono molti lavori che richiedono "conoscenze avanzate di SQL". L'utente chiede cosa ci si deve aspettare da questo tipo di lavoro. Quali conoscenze sono considerate avanzate?

La prima risposta fornisce uno snippet di codice piuttosto lungo come misura della conoscenza avanzata. Anche se è piuttosto lungo, non è così complicato. Secondo questa risposta, l'SQL avanzato comprende la selezione di colonne, le funzioni aggregate come MIN() e MAX(), l'istruzione CASE WHEN, JOINs, la clausola WHERE, GROUP BY, la dichiarazione di variabili e le subquery.

La risposta seguente, invece, considera la maggior parte di questi argomenti di base o al massimo intermedi. Questo utente ritiene che gli argomenti avanzati di SQL includano le funzioni, le stored procedure, le query gerarchiche, i trigger, gli indici, la modellazione dei dati (forme normali, chiavi primarie e straniere, vincoli delle tabelle), le transazioni e molto altro ancora. Questa è la definizione di SQL avanzato più vicina alla mia e a quella che mi è stata insegnata nelle lezioni di SQL. Tuttavia, si trattava di un programma per amministratori di database; è comprensibile che queste conoscenze siano considerate avanzate. Alcuni specialisti di reporting e analisti di dati potrebbero non avere mai bisogno di usare queste cose.

È interessante notare che a volte JOINs è considerato avanzato, mentre la scrittura di stored procedure è ancora considerata una conoscenza di base. Posso capire perché un utente accenna al problema con JOINs. Anche se sono generalmente considerate conoscenze di base, molti utenti di SQL imparano argomenti molto più avanzati prima di capire veramente JOINs. È così che le nozioni di base diventano facilmente conoscenze avanzate. Non è raro trovare qualcuno che usa funzioni appariscenti, trigger e quant'altro, senza sapere come scrivere un semplice JOIN.

Che cos'è Advanced SQL in LearnSQL.com?

Prima di spiegare cos'è l'SQL avanzato, è essenziale sapere cosa non è. Nei nostri corsi e articoli, l'SQL di base/intermedio è qualsiasi cosa in SQL-92. (Qui trovate la storia e i dettagli degli standard SQL se siete interessati a saperne di più). Questo include:

• Tutti i tipi di JOINs
• Funzioni aggregate
• GROUP BY
• HAVING
• Subquery
• Operazioni di insieme (UNION, UNION ALL, INTERSECT, MINUS)

È necessario conoscere questi argomenti se si pretende di conoscere SQL. Si tratta di argomenti da comprendere prima di passare ad argomenti più avanzati.

In generale, si considerano tre argomenti come "SQL avanzato":

• Funzioni finestra
• Espressioni di tabella comuni (CTE)
• GROUP BY le estensioni (ROLLUP, CUBE e GROUPING SETS).

Chiunque voglia imparare (o esercitarsi) su tutti e tre gli argomenti dovrebbe dare un'occhiata alla nostra traccia Advanced SQL . Naturalmente, questo non è l'unico corso di SQL avanzato in circolazione; abbiamo già recensito alcuni eccellenti corsi di SQL avanzato di altre piattaforme. Per ora, vediamo un esempio di ciascuno di questi argomenti.

Window Functions

Le funzioni SQL a finestra consentono di eseguire operazioni spesso necessarie per la creazione di report, come ad esempio classificare i dati, calcolare totali e medie mobili, trovare la differenza tra le righe, ecc. Per saperne di più, consultate il nostro corso Window Functions .

Vediamo un esempio. Questo codice mostra la differenza nel numero di auto vendute annualmente, in base alla marca (cioè al marchio dell'auto):

SELECT	car_make,
		cars_sold,
		year,
		cars_sold - LAG(cars_sold) OVER (PARTITION BY car_make ORDER BY year) AS sales_diff
FROM cars_sale;

Per ottenere queste informazioni, occorre innanzitutto selezionare le colonne desiderate nel risultato: car_make, cars_sold, year. Per ottenere la differenza annuale, sottrarre le vendite dell'anno precedente da quelle dell'anno in corso: cars_sold - LAG(cars_sold) OVER (PARTITION BY car_make ORDER BY year) AS sales_diff. In questo caso, cars_sold indica le vendite dell'anno corrente. La funzione LAG() consente di recuperare i dati dalla riga precedente. La clausola OVER indica che si tratta di una funzione finestra. Segue la clausola PARTITION BY, utilizzata per definire la finestra (sottoinsieme di dati) che si desidera utilizzare. In questo caso, si tratta di car_make; ciò significa che la funzione calcolerà la differenza di vendita solo all'interno di una specifica marca di auto. Quando si imbatte in un'altra marca, la funzione si azzera e ricomincia a calcolare la differenza di vendita.

Infine, l'operazione è ordinata per anno in modo crescente. Perché? La funzione LAG() ci fornirà i dati della riga precedente. Quindi, se questa operazione viene eseguita per ogni anno in ordine crescente, "anno precedente" significa la riga precedente. È esattamente quello che ci serve.

Date un'occhiata al risultato:

Avete visto il valore NULL nella prima riga? Questo perché il 2015 è il primo anno; non ci sono dati precedenti che possano essere dedotti da esso. Se seguite i risultati, vedrete che ogni riga rappresenta la differenza tra le vendite annuali della riga corrente e quelle della riga precedente. Quando si raggiunge la riga in cui inizia Renault, c'è di nuovo NULL. Questo è ciò che fanno le funzioni finestra: lavorano sui dati all'interno di una determinata finestra. ho definito la finestra in base a car_make, quindi la funzione finestra si ripristina quando si ottiene un nuovo valore in questa colonna. È logico. Perché dovrei dedurre le vendite di Renault da quelle di Nissan? Voglio farlo per ogni marca di auto separatamente.

Espressioni di tabella comuni (CTE)

Le CTE consentono di scrivere query complesse senza utilizzare le subquery, mantenendo il codice semplice e lineare. Permettono di produrre report complessi in modo rapido ed efficiente. Inoltre, consentono di eseguire alcuni calcoli che altrimenti non si potrebbero fare.

Che cos'è un'espressione di tabella comune, vi chiederete? È un risultato temporaneo che si può usare nell'istruzione SELECT. Funziona come una tabella temporanea e può essere unita ad altre tabelle, ad altre CTE o a se stessa.

Possono essere utili se, ad esempio, si deve fare un resoconto del tempo trascorso su un particolare progetto. Da un lato, c'è una tabella contenente i dati relativi alla data in cui ogni dipendente ha lavorato a questo progetto. Sono presenti anche l'ora di inizio e l'ora di fine. Dall'altro lato, c'è una tabella contenente i nomi dei dipendenti. È necessario produrre una tabella che mostri il nome di ogni dipendente e il suo tempo medio trascorso su questo progetto.

Ecco come la CTE può aiutarvi:

WITH time_worked AS (
	SELECT	employee_id,
			end_time - start_time AS time
FROM project_timesheet
)
SELECT	e.first_name,
		e.last_name,
		AVG (tw.time) AS avg_time_worked
FROM employee e 
LEFT JOIN time_worked tw 
ON e.id = tw.employee_id
GROUP BY e.first_name, e.last_name;

Come funziona questo CTE? Ogni CTE si apre con la clausola WITH. Poi si deve dare un nome alla CTE; in questo caso, è time_worked. Poi si scrive un'istruzione SELECT. In questo caso, utilizzerò la CTE per calcolare il tempo impiegato da ciascun dipendente ogni volta che ha lavorato al progetto. Ho bisogno della CTE perché non ho queste informazioni dichiarate esplicitamente nella tabella; ho solo start_time e end_time. Per calcolare il tempo medio lavorato, il primo passo è ottenere il tempo lavorato. Per questo motivo, questa CTE deduce start_time da end_time e mostra il risultato nella colonna time. I dati sono presi dalla tabella project_timesheet.

Ora che ho scritto la CTE, posso utilizzarla nella prossima istruzione SELECT. Per prima cosa, prenderò il nome e il cognome dalla tabella employee. Poi utilizzerò la funzione AVG() sulla colonna time della CTE time_worked. Per fare ciò, ho usato la funzione LEFT JOIN - e l'ho usata esattamente come farei con qualsiasi altra tabella. Infine, i dati vengono raggruppati per nome e cognome dei dipendenti.

Il risultato è una piccola tabella come questa:

Se le CTE vi interessano, immaginate cosa sarete in grado di fare dopo aver terminato il nostro corso Recursive Queries . Oh, sì, non ho detto che una CTE può essere ricorsiva, il che significa che fa riferimento a se stessa. In questo modo, restituisce il risultato secondario e ripete il processo fino a restituire il risultato finale. Mentre le CTE possono essere non ricorsive, non esistono query ricorsive che non siano CTE. Se si vogliono imparare le query ricorsive, è indispensabile conoscere le CTE.

Estensioni GROUP BY

Le estensioni di SQL GROUP BY offrono ulteriori possibilità di raggruppare i dati. Ciò può aumentare la complessità dell'analisi dei dati e dei report creati.

Esistono tre estensioni GROUP BY:

• ROLLUP
• CUBE
• GROUPING SETS

A differenza del normale GROUP BY, ROLLUP consente di raggruppare i dati in più insiemi e di aggregare i risultati a diversi livelli. In parole povere, ROLLUP permette di calcolare totali e subtotali, proprio come nelle tabelle pivot di Excel.

L'estensione CUBE è simile, ma con una differenza fondamentale: CUBE genera subtotali per ogni combinazione delle colonne specificate.

Infine, c'è GROUPING SETs. Un insieme di raggruppamenti è un insieme di colonne utilizzate nella clausola GROUP BY. È possibile collegare diverse query contenenti GROUP BY se si usa UNION ALL. Tuttavia, più query si hanno, più il sistema diventa complicato. Si può ottenere lo stesso risultato, ma con query molto più ordinate, usando GROUPING SETS.

Lasciate che vi mostri come funziona ROLLUP. Supponiamo di lavorare per un negozio di chitarre che ha diverse sedi. A volte è necessario creare un report che mostri il numero totale di chitarre in magazzino. Ecco una query che consente di ottenere questo risultato a livello di produttore, modello e negozio:

SELECT	manufacturer,
		model,
		store,
		SUM(quantity) AS quantity_sum
FROM guitars
GROUP BY ROLLUP (manufacturer, model, store)
ORDER BY manufacturer;

Non sembra complicato. Si tratta di una semplice istruzione SELECT che fornisce le colonne manufacturer, model e store dalla tabella guitars. Ho utilizzato la funzione aggregata SUM() per ottenere la quantità. Poi ho scritto GROUP BY seguito immediatamente da ROLLUP. I dati saranno raggruppati in base alle colonne tra parentesi. Infine, il risultato è ordinato per produttore.

Cosa restituisce questa query? Date un'occhiata:

Dovrebbe essere più facile capire cosa intendo per livelli di raggruppamento diversi. Un piccolo suggerimento prima di continuare: Ogni volta che si vede un valore NULL, si tratta di un subtotale. Diamo un'occhiata alla tabella. Innanzitutto, ci sono 9 Fender Jazzmaster ad Amsterdam. Poi ci sono 32 Fender Jazzmaster a New York. La quantità totale è 41, come mostrato nella riga:

Il valore NULL indica che i dati sono raggruppati a livello di negozio. Il risultato è "ci sono 41 Fender Jazzmaster in totale, sia a New York che ad Amsterdam". Lo stesso calcolo viene fatto per ogni altro modello Fender, cioè Stratocaster e Telecaster. Poi c'è questa riga:

Cosa significa? Significa che ci sono in totale 592 Fender di tutti e tre i modelli in entrambi i negozi.

Lo stesso principio si applica alle Gibson. La quantità di chitarre presenti ad Amsterdam e a New York viene mostrata prima per il modello. Dopo aver fatto questo, si fa una somma subtotale delle quantità di entrambi i negozi. Questo viene fatto per tutti e tre i modelli Gibson: ES-335, Les Paul e SG. Poi c'è una riga che mostra il numero totale di tutti e tre i modelli di chitarra Gibson in entrambi i negozi (come nel caso di Fender):

Infine, c'è una riga che mostra il numero totale di chitarre, indipendentemente dal negozio, dal produttore o dal modello:

Sono certo che ora vorrete scoprire come funzionano i CUBI e gli IMPIANTI DI Raggruppamento. Per questo, vi consiglio di dare un'occhiata al corso sulle estensioni GROUP BY.

Questi argomenti avanzati sono utilizzati molto spesso dagli analisti di dati. Ho quindi preparato alcune costruzioni SQL per i miei colleghi analisti di dati. Se vi occupate di finanza, ecco alcune query SQL avanzate per l'analisi finanziaria.

Vi considerate un utente di Advanced SQL?

Come vi sentite ora? Ho aumentato la vostra fiducia? Se conoscete già le funzioni SQL window, le CTE e le estensioni GROUP BY, potete vantarvi delle vostre competenze SQL avanzate.

O forse ho fatto il contrario? Forse ho fatto vacillare la vostra fiducia quando vi siete resi conto di non sapere nulla degli argomenti avanzati di cui ho parlato in questo articolo.

Non preoccupatevi! Qualunque sia il vostro gruppo di appartenenza, ci sono LearnSQL.it corsi che vi aiuteranno a sviluppare le vostre conoscenze e competenze. Volete imparare le funzioni delle finestre? Nessun problema: consultate il nostro corso Window Functions . Siete interessati alle CTE? Potete imparare ed esercitarvi con il nostro corso Recursive Queries . Volete ottenere di più da GROUP BY? Il nostro corso GROUP BY Extensions in SQL vi offre tutte le informazioni necessarie.