Funzioni RANK e DENSE_RANK

Lezione 9 di 13 del corso SQL avanzato di Coddy.

ROW_NUMBER() è un tipo di funzione di classificazione, e ce ne sono altre due: RANK() e DENSE_RANK().

La funzione RANK() numera le righe come ROW_NUMBER(), ma assegna numeri identici alle righe con lo stesso valore e salta dei numeri. DENSE_RANK() è simile a RANK(), ma non salta numeri.

Per esempio:

id	level
1	5
2	6
3	6
4	7
5	7
6	5

SELECT id, 
	ROW_NUMBER() OVER (ORDER BY level) as row_num,
	RANK() OVER (ORDER BY level) as row_rank,
	DENSE_RANK() OVER (ORDER BY level) as row_dense_rank,
FROM table1

Questo restituirà:

id	level	row_num	row_rank	row_dense_rank
1	5	1	1	1
2	6	2	2	2
3	6	3	2	2
4	7	4	4	3
5	7	5	4	3
6	5	6	6	4

ROW_NUMBER() va da 1 a 6 con valori univoci, RANK() assegna gli stessi valori ai livelli uguali, ma salta 3 e 5, e anche DENSE_RANK() assegna gli stessi valori ai livelli uguali, ma non salta alcun valore.

Sfida

Medio

Regioni immaginarie specifiche ricevono medaglie. Il successo di una regione si misura in base al numero totale di medaglie che ha e ai punteggi ottenuti.

Una medaglia al vanadio vale 5 punti

Una medaglia d'argento vale 3 punti

Una medaglia di bronzo vale 1 punto

Calcola il punteggio di ogni regione e il numero totale di medaglie che ha, e chiama queste colonne rispettivamente total_score e total_medals. Assegna un rango denso alla colonna total_medals e chiama questa colonna final_rank.

Infine, ordina il risultato per final_rank e region e

Provalo tu

WITH region_total_medals AS (
    SELECT region, SUM(medal_count) as total_medals
    FROM medals
    GROUP BY region
), region_prepare_total_score AS (
    SELECT region, medal_color, medal_count, DENSE_RANK() OVER (ORDER BY medal_color) as medal_rank
    FROM medals
), region_total_score AS (
    SELECT region, SUM((medal_rank*2 - 1)*medal_count) as medal_rank
    FROM region_prepare_total_score
    GROUP BY region
)

SELECT region_total_medals.region, 
        total_medals, medal_rank,
        DENSE_RANK() OVER (ORDER BY total_medals) as final_rank
FROM region_total_score
JOIN region_total_medals ON region_total_medals.region = region_total_score.region

Tutte le lezioni di SQL avanzato

1Introduzione

Introduzione Semplificare le query con la parola chiave WITH

4Riepilogo

Sfida finale #1

2Funzioni finestra parte 1

Funzione ROW_NUMBER Criterio ORDER BY Criterio PARTITION BY Funzioni LEAD e LAG Sfida di ripasso #1 Sfida di ripasso #2

3Funzioni finestra, parte 2

Funzioni RANK e DENSE_RANKFunzione NTILE Funzioni di aggregazione Criterio ROWS e RANGE

Esercitati da solo: Compilatore Python online