Funzioni LEAD e LAG
Fa parte della sezione Fondamenti del percorso SQL di Coddy. Lezione 61 di 72.
Le funzioni LEAD e LAG ci consentono di accedere al valore della riga corrente spostandoci indietro di n passaggi o avanti di n passaggi.
Ad esempio, se vogliamo calcolare il rapporto tra i ricavi di un'azienda nella riga corrente e quelli di un mese prima, possiamo estrarre il valore del mese precedente:
| id | revenue | month |
| 1 | 532 | 5 |
| 2 | 492 | 6 |
| 3 | 393 | 7 |
| 4 | 723 | 8 |
SELECT id, revenue, LAG(revenue, 1) OVER (ORDER BY MONTH) as prev_month_revenue
FROM table1 ORDER BY idQuesto creerà la seguente tabella:
| id | revenue | prev_month_revenue |
| 1 | 532 | |
| 2 | 492 | 532 |
| 3 | 393 | 492 |
| 4 | 723 | 393 |
In questo modo possiamo calcolare il rapporto prev_month_revenue/revenue.
Se invece usassimo la funzione LEAD, otterremmo il ricavo del mese successivo per ogni riga:
SELECT id, revenue, LEAD(revenue, 1) OVER (ORDER BY MONTH) as next_month_revenue
FROM table1 ORDER BY id| id | revenue | next_month_revenue |
| 1 | 532 | 492 |
| 2 | 492 | 393 |
| 3 | 393 | 723 |
| 4 | 723 |
Sfida
FacileTabelle e colonne disponibili:
air_conditioners:id,efficiency,strength,month
Confronta l'efficienza di ciascun condizionatore d'aria con quella del precedente.
Scrivi una query che mostri l'efficienza di ciascun condizionatore d'aria insieme a quella del condizionatore d'aria installato in precedenza (in base a id). Inoltre, calcola la differenza di efficienza tra il condizionatore d'aria corrente e quello precedente. Ordina il risultato in ordine crescente per id e efficiency.
Colonne previste nell'output:
idefficiencyprevious_efficiency(usando LAG)efficiency_difference(efficienza corrente - efficienza precedente)
Infine, racchiudi la query e filtra la prima riga, il cui previous_efficiency (e quindi efficiency_difference) è vuoto, ovvero NULL:
SELECT * FROM (
-- Your query here
)
WHERE previous_efficiency IS NOT NULLProvalo tu
Mettiti alla provaQuesta lezione include un breve quiz. Inizia la lezione per rispondere e tenere traccia dei tuoi progressi.
Tutte le lezioni di Fondamenti
4Altre parole chiave
La parola chiave INLa parola chiave BETWEENLa parola chiave LIKELa parola chiave ASRiepilogo - Modelli di telefoni cellulari
2Condizioni
Basi delle condizioniLa parola chiave ANDLa parola chiave ORLa parola chiave NOTCombinare più condizioniParentesiValori booleani5Operazioni aritmetiche
Operatori matematiciColonne matematicheL’operazione moduloLa funzione ROUND()8Statistica
Funzioni aggregate integrate - Parte 1Funzioni aggregate integrate - Parte 2Raggruppamento - Parte 1Raggruppamento - Parte 2Sottoquery - Parte 1Sottoquery - Parte 2Riepilogo - Negozio Total GainRiepilogo - Negozio di scooterRiepilogo - Caffetteria11Funzioni finestra, parte 1
Funzione ROW_NUMBERCriterio ORDER BYCriterio PARTITION BYPARTITION e ORDERFunzioni LEAD e LAGRipasso - LEAD e LAGRipasso - ImmaginiRipasso - Scatole3Formato di restituzione specifico
Valori nullOrdinare i risultati Parte 1Ordinare i risultati Parte 2Riepilogo - Azienda di sicurezza informaticaLimitare il numero di recordRiepilogo - Fabbrica di veicoli6Sfide introduttive
Ripasso - Elezione parlamentareRipasso - Arresto di un criminale da parte della poliziaRipasso - Contenitore per bevande da barRipasso - Progetta nuove colonne9Tabelle multiple
JOIN di base, parte 1JOIN di base, parte 2Riepilogo - JOINSelf joinRiepilogo - Self joinUNIONSemplificare le query, parola chiave WITHRiepilogo - Query con WITHRiepilogo - Impresa edile immobiliare12Funzioni finestra, parte 2
Funzioni RANK e DENSE_RANKRipasso: RANK e DENSE_RANKFunzione NTILEFunzioni di aggregazioneCriteri ROWS e RANGEEsercitati da solo: Playground SQL