SQL Practice: 11 esercizi pratici sulle funzioni SQL a finestra con soluzioni dettagliate

In questo articolo presentiamo 11 esercizi pratici sulle funzioni finestra di SQL, con soluzioni e spiegazioni dettagliate.

Le funzioni finestra di SQL sono una funzione potente che ci permette di estrarre facilmente informazioni significative dai nostri dati, ma pochi corsi di SQL offrono esercizi sulle funzioni finestra di SQL. Questo rende piuttosto difficile esercitarsi con le funzioni finestra. In questo articolo vi forniremo 11 esercizi pratici sulle funzioni finestra.

Tutti gli esercizi presentati in questo articolo provengono dai nostri corsi interattivi Window Functions e da Window Functions Set di esercitazioni. Il corso Window Functions è un'esercitazione approfondita con oltre 200 esercizi. Consigliamo questo corso per imparare o ripassare le conoscenze sulle funzioni finestra di SQL. Window Functions Set di esercitazioni è un corso di esercitazione per chi conosce le funzioni delle finestre SQL e vuole fare più pratica.

Window Functions in breve

Le funzioni SQL window sono strumenti che aiutano ad analizzare i dati in diversi modi. Dal calcolo dei totali e delle medie mobili, al confronto dei dati all'interno di sottoinsiemi specifici, all'identificazione dei migliori risultati, fino alla creazione di classifiche e partizioni complesse, queste funzioni ci permettono di estrarre informazioni più profonde dai nostri dati e di svolgere facilmente compiti complessi.

Le funzioni della finestra SQL offrono una serie di strumenti versatili per migliorare l'analisi dei dati. Questo insieme di strumenti comprende:

• ROW_NUMBER(), RANK(), DENSE_RANK() e NTILE() per classificare i dati.
• SUM(), AVG(), COUNT(), MAX() e MIN() per le aggregazioni.
• LEAD() e LAG() per confrontare i dati tra le righe.
• FIRST_VALUE() e LAST_VALUE() per estrarre i valori limite.

Inoltre, la clausola OVER() consente di suddividere e ordinare con precisione i dati all'interno di queste funzioni, permettendo agli utenti di eseguire calcoli complessi su sottoinsiemi definiti di dati.

La padronanza delle funzioni SQL window sta diventando sempre più cruciale per i professionisti dei dati, gli analisti e gli ingegneri. Non solo permette di risolvere in modo efficiente sfide analitiche complesse, ma fornisce anche una comprensione più profonda dei dati. La pratica delle funzioni di finestra SQL migliorerà la vostra capacità di creare query avanzate e vi aiuterà a scoprire nuovi insight dai dati. Si tratta di un'abilità vitale nel mondo di oggi, incentrato sui dati.

Prima di affrontare gli esercizi, si consiglia di dare un'occhiata al nostro foglio informativo sulle funzioni delle finestre, che vi ricorderà l'elenco delle funzioni delle finestre e la loro sintassi.

SQL Window Functions Esercizi pratici: Negozio di film online

Prima di iniziare gli esercizi, diamo un'occhiata al set di dati che utilizzeremo.

Set di dati

I seguenti esercizi utilizzano il database del negozio di film online, che contiene sei tabelle.

• La tabella customer contiene informazioni su tutti i clienti registrati. Le colonne sono id, first_name, last_name, join_date e country.
• La tabella movie contiene i record di tutti i film disponibili nel negozio. Le colonne sono id, title, release_year, genre, e editor_ranking.
• La tabella review contiene le valutazioni dei film da parte dei clienti. Le colonne sono id, rating, customer_id (riferimenti alla tabella) e (riferimenti alla tabella). customer tabella) e movie _id (fa riferimento alla movie tabella).
• La tabella single_rental contiene informazioni sui film che sono stati noleggiati dai clienti per un certo periodo di tempo. Le colonne sono id, rental_date, rental_period, platform, customer_id (riferimenti alla tabella), (riferimenti alla tabella). customer tabella), movie _id (fa riferimento alla movie tabella), payment_date, e payment_amount.
• La tabella subscription memorizza i record di tutti i clienti che si sono iscritti al negozio. Le colonne sono id, length (in giorni), start_date, platform, payment_date, payment_amount, e customer_id (riferimenti alla tabella). customer tabella).
• La tabella giftcard contiene informazioni sulle carte regalo acquistate. Le colonne sono id, amount_worth, customer_id (riferimenti alla tabella), , e (riferimenti alla tabella). customer tabella), payment_date e payment_amount.

Ora che conosciamo bene il set di dati, passiamo agli esercizi pratici di SQL.

Esercizio 1: Classificare gli affitti per prezzo

Esercizio:

Per ogni singolo noleggio, mostrare il sito rental_date, il titolo del film noleggiato, il suo genere, l'importo del pagamento e la classifica del noleggio in termini di prezzo pagato (il noleggio più costoso dovrebbe avere rango = 1). La classifica deve essere creata separatamente per ogni genere di film. Consentire lo stesso rango per più righe e consentire gli spazi vuoti nella numerazione.

Soluzione:

SELECT
  rental_date,
  title,
  genre,
  payment_amount,
  RANK() OVER(PARTITION BY genre ORDER BY payment_amount DESC)
FROM movie
JOIN single_rental
  ON single_rental.movie_id = movie.id;

Spiegazione della soluzione:

L'istruzione ci dice di mostrare alcune informazioni su singoli noleggi e film. Pertanto, uniamo la tabella single_rental con la tabella movie sulla loro colonna comune (cioè la colonna movie_id ).

Successivamente, dobbiamo classificare tutti i noleggi in termini di prezzo pagato per ogni noleggio. Per farlo, utilizziamo RANK(). Poi, nella clausola OVER(), ordiniamo i dati in base alla colonna payment_amount in ordine decrescente, in modo che il noleggio più costoso abbia il rango 1.

Poiché la classifica deve essere creata separatamente per ogni genere cinematografico, nella clausola OVER() si suddividono i dati in base alla colonna del genere.

Perché abbiamo scelto RANK() invece di DENSE_RANK() o ROW_NUMBER()? L'istruzione dice che è consentito lo stesso rango per più righe; quindi, riduciamo le opzioni a RANK() e DENSE_RANK(). La funzione ROW_NUMBER() assegna numeri consecutivi come rango alle righe successive; non ammette più righe con lo stesso rango.

Sono consentite lacune nella numerazione delle righe, quindi è necessaria la funzione RANK(). DENSE_RANK() non salta alcun numero in una sequenza, anche se più righe hanno lo stesso rango. La tabella seguente presenta queste funzioni di classificazione e il loro funzionamento con un elenco di valori di dati:

VALUE	ROW_NUMBER()	RANK()	DENSE_RANK()
Apple	1	1	1
Apple	2	1	1
Apple	3	1	1
Carrot	4	4	2
Banana	5	5	3
Banana	6	5	3
Peach	7	7	4
Tomato	8	8	5

Consultate questo articolo per saperne di più sulle diverse funzioni di classificazione.

Esercizio 2: Trovare il^secondo cliente che acquista una carta regalo

Esercizio:

Mostrare il nome e il cognome del cliente che ha acquistato la seconda carta regalo più recente, insieme alla data del pagamento. Si supponga che venga assegnato un grado unico per ogni acquisto di carta regalo.

Soluzione:

WITH ranking AS (
  SELECT
    first_name,
    last_name,
    payment_date,
    ROW_NUMBER() OVER(ORDER BY payment_date DESC) AS rank
  FROM customer
  JOIN giftcard
    ON customer.id = giftcard.customer_id
)

SELECT
  first_name,
  last_name,
  payment_date
FROM ranking
WHERE rank = 2;

Spiegazione della soluzione:

Vogliamo mostrare le informazioni sui clienti e sui loro acquisti di carte regalo, quindi dobbiamo unire la tabella customer con la tabella giftcard sulla loro colonna comune, (customer_id).

Le istruzioni dicono di trovare il cliente che ha acquistato la seconda carta regalo più recente. Per fare ciò, classifichiamo innanzitutto gli acquisti di carte regalo utilizzando la funzione ROW_NUMBER(); supponiamo che venga assegnato un rango unico per ogni acquisto di carte regalo.

L'istruzione interna SELECT seleziona le informazioni sui clienti e le date di acquisto delle carte regalo. Quindi, si classificano le righe utilizzando la funzione ROW_NUMBER() per contrassegnare il secondo acquisto di carte regalo più recente (cioè il valore di rango 2).

Questa istruzione interna a SELECT è una Common Table Expression (CTE). È racchiusa all'interno della clausola WITH e si chiama ranking. Selezioniamo i dati rilevanti da questa CTE e forniamo una condizione nella clausola WHERE per produrre solo la riga con rango pari a 2.

Perché è necessario definire una CTE e poi interrogarla? Perché non possiamo usare la colonna rank nella clausola WHERE dell'interno SELECT. Il motivo è l'ordine di esecuzione, che è: FROM, JOINs, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, e LIMIT. Quindi la colonna rank non è ancora definita nel momento in cui viene eseguita la clausola WHERE della clausola interna SELECT.

Esercizio 3: Calcolo del totale progressivo dei pagamenti

Esercizio:

Per ogni singolo affitto, mostrare i dati id, rental_date, payment_amount e il totale progressivo di payment_amounts di tutti gli affitti dal più vecchio (in termini di rental_date) alla riga corrente.

Soluzione:

SELECT
  id,
  rental_date,
  payment_amount,
  SUM(payment_amount) OVER(
    ORDER BY rental_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM single_rental;

Spiegazione della soluzione:

Per prima cosa, selezioniamo le informazioni relative a ogni singolo affitto dalla tabella single_rental tabella.

Successivamente, si troverà il totale progressivo degli importi di pagamento di tutti gli affitti utilizzando la funzione SUM() (che prende come argomento la colonna payment_amount ) con la clausola OVER(). Ecco un articolo che spiega i dettagli del totale progressivo e come calcolarlo in SQL.

L'istruzione dice di trovare il totale progressivo dalla data di affitto più vecchia fino alla data della riga corrente. Quindi, nella clausola OVER(), dobbiamo ordinare i dati in base alla colonna rental_date e poi definire ROWS da contare nel totale corrente, dalla data più vecchia (BETWEEN UNBOUNDED PRECEDING) fino alla data corrente (AND CURRENT ROW).

SQL Window Functions Esercizi pratici: Clinica sanitaria

di dati

I seguenti esercizi utilizzano un database di una clinica sanitaria che contiene due tabelle.

• La tabella doctor contiene informazioni sui medici. Le colonne sono id, first_name, last_name e age.
• La tabella procedure contiene informazioni sulle procedure eseguite dai medici sui pazienti. Le colonne sono id, procedure_date, doctor_id (riferimenti alla tabella), , , , , , (riferimenti alla tabella). doctor tabella), patient_id, category, name, price, e score.

Ora che conosciamo bene il set di dati, passiamo agli esercizi pratici di SQL.

Esercizio 4: Calcolo della media mobile dei punteggi

Esercizio:

Per ogni procedura, mostrare le seguenti informazioni: procedure_date, doctor_id, category, name, score e la media dei punteggi delle procedure della stessa categoria incluse nel seguente riquadro: le due righe precedenti, la riga corrente e le tre righe successive in termini di data della procedura.

Soluzione:

SELECT
  procedure_date,
  doctor_id,
  category,
  name,
  score,
  AVG(score) OVER(
    PARTITION BY category
    ORDER BY procedure_date
    ROWS BETWEEN 2 PRECEDING AND 3 FOLLOWING)
FROM procedure;

Spiegazione della soluzione:

Mostreremo le informazioni per ogni procedura selezionandole dalla tabella. procedure tabella.

Poi troveremo il punteggio medio delle procedure della stessa categoria. A tale scopo, utilizziamo la funzione AVG() con la colonna score come argomento. Segue la clausola OVER(), che suddivide il set di dati in categorie.

Inoltre, dobbiamo considerare solo le righe incluse nella finestra seguente: le due righe precedenti, la riga corrente e le tre righe successive in termini di data della procedura. Definiamo questa cornice di dati all'interno della clausola OVER(). Innanzitutto, ordiniamo il set di dati in base alla colonna procedure_date, in modo che le procedure siano elencate cronologicamente. Quindi si definiscono le righe da considerare per calcolare il valore medio del punteggio: due righe precedenti (BETWEEN 2 PRECEDING) e tre righe successive (AND 3 FOLLOWING), compresa la riga corrente.

Questa procedura è nota come media mobile. Per saperne di più, consultare Cos'è una media mobile e come calcolarla in SQL.

Esercizio 5: Trovare la differenza tra i prezzi di una procedura

Esercizio:

Per ogni procedura, mostrate le seguenti informazioni: id, procedure_date, name, price, price della procedura precedente (in termini di id) e la differenza tra questi due valori. Nominare le ultime due colonne previous_price e difference.

Soluzione:

SELECT
  id,
  procedure_date,
  name,
  price,
  LAG(price) OVER(ORDER BY id) AS previous_price,
  price - LAG(price) OVER(ORDER BY id) AS difference
FROM procedure;

Spiegazione della soluzione:

Anche in questo caso, iniziamo a selezionare le informazioni su ogni procedura dalla tabella. procedure tabella.

L'istruzione dice di mostrare il prezzo della procedura precedente. Per farlo, utilizziamo la funzione LAG(), che restituisce il valore della riga precedente per il suo argomento (qui, per la colonna price ). Per assicurarci di scegliere il prezzo della procedura precedente in termini di id, ordiniamo il set di dati in base alla colonna id nella clausola OVER(). La chiamiamo previous_price.

Ora che abbiamo il valore del prezzo e il valore del prezzo precedente, possiamo selezionare la differenza tra questi due valori. È sufficiente sottrarre la funzione LAG() dalla colonna price e assegnarle l'alias difference.

Per saperne di più, consultate Come calcolare la differenza tra due righe in SQL.

Esercizio 6: Trovare la differenza tra il prezzo attuale e il prezzo migliore

Esercizio:

Per ogni procedura, indicare il:

• procedure_date
• name
• price
• category
• score
• Prezzo della procedura migliore (in termini di punteggio) della stessa categoria (colonna best_procedure).
• Differenza tra questa price e la best_procedure (colonna difference).

Soluzione:

SELECT 
  procedure_date, 
  name, 
  price,
  category,
  score, 
  FIRST_VALUE(price) OVER(PARTITION BY category ORDER BY score DESC)
     AS best_procedure,
  price - FIRST_VALUE(price) OVER(PARTITION BY category 
     ORDER BY score DESC) AS difference
FROM procedure;

Spiegazione della soluzione:

Si inizia selezionando le informazioni su ciascuna procedura dalla tabella delle procedure.

Il passo successivo consiste nel trovare il prezzo della procedura migliore. Utilizziamo la funzione FIRST_VALUE(), che restituisce il primo valore di una partizione ordinata di un insieme di risultati. Per ottenere il prezzo della migliore procedura della stessa categoria, dobbiamo partizionare il set di dati in base alla colonna category. Per ottenere il prezzo della procedura migliore in termini di punteggio, dobbiamo ordinare il set di dati in base alla colonna del punteggio in ordine decrescente. Questa espressione viene chiamata best_procedure.

Infine, troviamo la differenza tra price e best_procedure sottraendo la funzione FIRST_VALUE() dalla colonna prezzo.

Esercizio 7: Trovare il miglior medico per procedura

Esercizio:

Scoprire quale medico è il migliore per ogni procedura. Per ogni procedura, selezionare il nome della procedura e il nome e cognome di tutti i medici che hanno ottenuto punteggi elevati (superiori o uguali al punteggio medio per questa procedura). Classificare i medici per ogni procedura in base al numero di volte in cui l'hanno eseguita. Quindi, mostrare i migliori medici per ogni procedura, ossia quelli che hanno un punteggio pari a 1.

Soluzione:

WITH cte AS (
  SELECT
    name,
    first_name,
    last_name,
    COUNT(*) c,
    RANK() OVER(PARTITION BY name ORDER BY count(*) DESC) AS rank
  FROM procedure p 
  JOIN doctor d
    ON p.doctor_id = d.id
  WHERE score >= (SELECT avg(score) 
                  FROM procedure pl 
                  WHERE pl.name = p.name)
  GROUP BY name, first_name, last_name
)

SELECT 
  name,
  first_name,
  last_name
FROM cte
WHERE rank = 1;

Spiegazione della soluzione:

Per prima cosa, selezioniamo il nome della procedura e le informazioni sui dottori, quindi uniamo la tabella procedure con la tabella doctor sulla loro colonna comune (doctor_id).

Vogliamo selezionare tutti i medici che hanno ottenuto punteggi elevati (superiori o uguali al punteggio medio per questa procedura). A tale scopo, definiamo la condizione della clausola WHERE per la colonna punteggio. La colonna score deve memorizzare un valore uguale o superiore al punteggio medio per la procedura della riga corrente.

Classifichiamo i medici per procedura. Utilizzeremo la funzione RANK() con la clausola OVER(), in cui suddivideremo il set di dati in base al nome della procedura. Inoltre, dobbiamo classificare in termini di numero di volte in cui il medico ha eseguito questa procedura. Per ottenere il numero di volte in cui il medico ha eseguito questa procedura, occorre COUNT(*) raggruppando per il nome della procedura e per il nome e cognome del medico (cioè, stiamo raggruppando per tutte le colonne elencate nell'istruzione SELECT ).

Tutto ciò che abbiamo fatto finora è stato definire una Common Table Expression (CTE), ovvero l'istruzione interna SELECT racchiusa dalla clausola WITH e denominata cte.

Ora selezioniamo le colonne pertinenti da questa CTE. Per ottenere i migliori medici per ogni procedura (quelli che hanno un rango pari a 1), definiamo la clausola WHERE con la condizione per la colonna rank.

Perché è necessario definire una CTE e poi interrogarla? Perché non possiamo usare la colonna rank nella clausola WHERE dell'interno SELECT. Il motivo è l'ordine di esecuzione, che è: FROM, JOINs, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, e LIMIT. La colonna rank non è stata definita quando viene eseguita la clausola WHERE.

SQL Window Functions Esercizi pratici: Campionati di atletica

Set di dati

I seguenti esercizi utilizzano il database dei campionati di atletica che contiene otto tabelle.

• La competition memorizza le informazioni sulle competizioni. Le colonne sono id, name, start_date, end_date, year e location.
• La tabella delle discipline contiene informazioni su tutte le discipline podistiche (dalle corse a breve distanza (ad esempio i 100 metri) a quelle a lunga distanza (ad esempio la maratona)). Le colonne sono id, name, is_men e distance.
• La tabella event contiene informazioni sulla competizione e sulla disciplina per ogni evento. Le colonne sono id, competition_id (riferimento alla tabella) e (riferimento alla tabella). competition tabella) e discipline_id (riferimento alla discipline tabella).
• La tabella round memorizza i turni di ogni evento. Le colonne sono id, event_id (riferimento alla tabella), , e . event tabella), round_name, round_number e is_final.
• La tabella race tabella memorizza i dati per ogni gara di ogni girone. Le colonne sono id, round_id (riferimenti alla tabella), (come ) e . round tabella), round_name (come nella tabella), , (riferimenti alla tabella). round tabella), race_number, race_date, is_final (come nella tabella) e . round tabella), e wind.
• La athlete memorizza le informazioni sugli atleti che partecipano alla competizione. Le colonne sono id, first_name, last_name, nationality_id (riferimenti alla tabella) e . nationality tabella) e birth_date.
• La tabella nationality tabella contiene informazioni sui Paesi di provenienza degli atleti. Le colonne sono id, country_name, e country_abbr.
• La tabella result memorizza le informazioni relative a tutti i partecipanti a un determinato evento. Le colonne sono race_id (riferimenti alla tabella), (riferimenti alla tabella). race tabella), athlete_id (fa riferimento alla athlete tabella), result, place, is_dsq, is_dns, e is_dnf.

Ora che conosciamo bene il set di dati, passiamo agli esercizi pratici di SQL.

Esercizio 8: Calcolo della differenza tra le medie giornaliere della velocità del vento

Esercizio:

Per ogni data in cui c'è stata una gara, visualizzare race_date, la media del vento in questa data arrotondata a tre punti decimali e la differenza tra la velocità media del vento in questa data e la velocità media del vento nella data precedente, anch'essa arrotondata a tre punti decimali. Le colonne devono essere denominate race_date, avg_wind e avg_wind_delta.

Soluzione:

SELECT
  race_date,
  ROUND(AVG(wind), 3) AS avg_wind,
  ROUND(AVG(wind) - LAG(AVG(wind)) OVER(ORDER BY race_date), 3) 
     AS avg_wind_delta
FROM race
GROUP BY race_date;

Spiegazione della soluzione:

Vogliamo visualizzare le informazioni sulla gara per ogni data di gara, quindi selezioniamo i dati dalla tabella. race tabella.

Per trovare la velocità media del vento in questa data, arrotondata a tre decimali, utilizziamo la funzione AVG() con la colonna wind come argomento. Quindi, la racchiudiamo nella funzione ROUND() e la arrotondiamo a tre decimali. Si noti che dobbiamo raggruppare per la colonna race_date, poiché utilizziamo la funzione aggregata AVG().

È possibile ottenere la media del vento alla data precedente utilizzando la funzione LAG() con il valore AVG(wind) come argomento. La clausola OVER() definisce l'ordinamento dell'intero set di dati in base alla colonna race_date, in modo da avere le righe di dati elencate cronologicamente.

Poiché vogliamo vedere la differenza tra la velocità media del vento in questa data e la velocità media del vento nella data precedente, sottraiamo LAG(AVG(wind)) da AVG(wind). E per arrotondare a tre cifre decimali, utilizziamo nuovamente la funzione ROUND().

Esercizio 9: Confronto tra i risultati migliori e quelli precedenti

Esercizio:

Per ogni donna che ha corso nella fase finale della maratona femminile di Rio, visualizzare le seguenti informazioni:

• Il piazzamento ottenuto nella gara.
• Il loro nome.
• Il cognome.
• comparison_to_best - La differenza tra il loro tempo e il miglior tempo in questa finale.
• comparison_to_previous - La differenza tra il loro tempo e il risultato dell'atleta che ha ottenuto il miglior risultato successivo.

Ordinare le righe in base alla colonna place.

Soluzione:

SELECT
  place,
  first_name,
  last_name,
  result - FIRST_VALUE(result) OVER (ORDER BY result) 
     AS comparison_to_best,
  result - LAG(result) OVER(ORDER BY result) 
     AS comparison_to_previous
FROM competition
JOIN event
  ON competition.id = event.competition_id
JOIN discipline
  ON discipline.id = event.discipline_id
JOIN round
  ON event.id = round.event_id
JOIN race
  ON round.id = race.round_id
JOIN result
  ON result.race_id = race.id 
JOIN athlete
  ON athlete.id = result.athlete_id
WHERE competition.name = 'Rio de Janeiro Olympic Games'
  AND discipline.name = 'Women''s Marathon'
  AND round.is_final IS TRUE
ORDER BY place;

Spiegazione della soluzione:

Utilizzeremo informazioni su gare, discipline, turni, atleti e risultati. Pertanto, dobbiamo unire tutte queste tabelle sulle loro colonne comuni, come indicato nell'introduzione al dataset.

L'istruzione dice di visualizzare le informazioni per ogni donna che ha corso nella fase finale della maratona femminile di Rio. Lo copriamo nella clausola WHERE che contiene le seguenti condizioni:

• Il nome della gara deve essere Rio de Janeiro Olympic Games.
• Il nome della disciplina deve essere Women's Marathon.
• La prova deve essere quella finale.

Quindi si seleziona la colonna luogo dalla tabella result e le colonne first_name e last_name dalla tabella athlete tabella.

Per trovare la differenza tra il loro tempo e il miglior tempo di questa finale, si utilizza la funzione FIRST_VALUE() con la colonna result come argomento. A questa segue la clausola OVER(), che ordina il set di dati in base alla colonna result. Poi sottraiamo questa funzione FIRST_VALUE() dalla riga corrente result. La chiamiamo comparison_to_best.

Per trovare la differenza tra il loro tempo e il risultato dell'atleta che ha ottenuto il piazzamento successivo, utilizziamo la funzione LAG() con la colonna result come argomento per ottenere il risultato precedente. Ancora una volta, questa funzione è seguita dalla clausola OVER() per ordinare il set di dati in base alla colonna result (per assicurarsi di ottenere il risultato successivo migliore). Quindi si sottrae questa funzione LAG() dalla riga corrente result. La chiamiamo comparison_to_previous.

Infine, ordiniamo le righe in base alla colonna place utilizzando la clausola ORDER BY.

SQL Window Functions Esercizi pratici: Statistiche del sito web

Set di dati

I seguenti esercizi utilizzano il database delle statistiche del sito web che contiene due tabelle.

• La tabella website contiene informazioni sui siti web. Le colonne sono id, name, budget e opened.
• La tabella statistics Le colonne sono website_id (riferimento alla tabella), , , , . websiteday , users, impressions, clicks, e revenue.

Ora che conosciamo bene il set di dati, passiamo agli esercizi pratici di SQL.

Esercizio 10: Guardare avanti con la funzione LEAD()

Esercizio:

Prendere le statistiche del sito web con id = 2 tra il 1° e il 14 maggio 2016 e mostrare il giorno, il numero di utenti e il numero di utenti 7 giorni dopo.

Si noti che le ultime 7 righe non hanno un valore nell'ultima colonna. Questo perché non è stato possibile trovare alcuna riga "a 7 giorni da oggi". Per questi casi, mostrare -1 invece di NULL se non viene trovato alcun valore LEAD().

Soluzione:

SELECT
  day,
  users,
  LEAD(users, 7, -1) OVER(ORDER BY day)
FROM statistics
WHERE website_id = 2
  AND day BETWEEN '2016-05-01' AND '2016-05-14';

Spiegazione della soluzione:

Mostreremo il giorno, il numero di utenti e il numero di utenti 7 giorni dopo. I primi due valori provengono dalla tabella statistics sono le colonne giorno e utenti. L'ultimo valore deve essere calcolato con la funzione LEAD().

Vogliamo vedere il valore della colonna utenti dopo sette giorni; quindi, passiamo la colonna users come primo argomento e il valore 7 come secondo argomento alla funzione LEAD(). Per assicurarci di mostrare -1 invece di NULL se non viene trovato alcun valore LEAD(), passiamo il terzo argomento come -1.

La funzione LEAD() è seguita dalla clausola OVER(). Questa clausola contiene la condizione per ordinare il set di dati in base alla colonna giorno, poiché le statistiche devono essere ordinate cronologicamente.

Per mostrare le statistiche del sito web con id = 2 tra il 1° e il 14 maggio 2016, è necessario definire le condizioni pertinenti nella clausola WHERE.

Esercizio 11: Guardare indietro con la funzione LAG()

Esercizio:

Mostrare le statistiche del sito web con id = 3 che includono il giorno, le entrate e le entrate di 3 giorni prima. Mostrare -1,00 per le righe senza valore di entrate 3 giorni prima.

Soluzione:

SELECT
  day,
  revenue,
  LAG(revenue, 3, -1.00) OVER(ORDER BY day)
FROM statistics
WHERE website_id = 3;

Spiegazione della soluzione:

Mostreremo il giorno, le entrate e le entrate di 3 giorni prima. I primi due valori provengono dalla tabella statistics: si tratta delle colonne giorno e ricavi. L'ultimo valore deve essere calcolato con la funzione LAG().

Vogliamo visualizzare il valore della colonna entrate di tre giorni prima della riga corrente; pertanto, passiamo la colonna entrate come primo argomento e il valore 3 come secondo argomento alla funzione LAG(). Per essere sicuri di visualizzare -1,00 per le righe senza valore di entrate di 3 giorni prima, passiamo il terzo argomento come -1,00.

La funzione LAG() è seguita dalla clausola OVER(). Questa contiene la condizione per ordinare il set di dati in base alla colonna day, poiché le statistiche devono essere ordinate cronologicamente.

Per mostrare le statistiche del sito web con id = 3, è necessario definire una condizione nella clausola WHERE.

Altre esercitazioni su SQL Window Functions

Gli esercizi pratici sulle funzioni della finestra SQL presentati in questo articolo forniscono una piattaforma completa per affinare le proprie capacità di SQL e di analisi dei dati, una query alla volta. Questi esercizi provengono dai nostri corsi; per trovare altri esercizi pratici, visitate i corsi collegati qui sotto.

1. Window Functions
2. Window Functions Set di esercitazioni

Se volete imparare o rinfrescare le vostre conoscenze sulle funzioni delle finestre, vi suggeriamo di iniziare con il corso Window Functions che offre un'esplorazione approfondita di questo argomento. Per chi desidera affinare le proprie competenze sulle funzioni delle finestre, è possibile esplorare il nostro set di esercizi Window Functions. Si tratta di 100 esercizi strutturati in tre parti distinte, ognuna delle quali utilizza un set di dati diverso.

Iscrivetevi subito e iniziate gratuitamente. Buona fortuna!