La funzione RANK in SQL Server è una sorta di funzione di classificazione. Questa funzione lo farà assegnare il numero a ciascuna riga all'interno della partizione di un output . Assegna il rango a ciascuna riga come uno più il rango della riga precedente. Quando la funzione RANK trova due valori identici all'interno della stessa partizione, assegna loro lo stesso numero di rango. Inoltre, il numero successivo nella classifica sarà il grado precedente più i numeri duplicati. Pertanto questa funzione non assegna sempre la classifica delle righe in ordine consecutivo.
Anche la funzione RANK è una sottoparte delle funzioni window. È necessario ricordare i seguenti punti durante l'utilizzo di questa funzione :
- Funziona sempre con la clausola OVER().
- Assegna un rango a ciascuna riga in base alla clausola ORDER BY.
- Assegna un rango a ciascuna riga in ordine consecutivo.
- Assegna sempre un rango alle righe, iniziando da uno per ogni nuova partizione.
NOTA: il rango assegna valori temporanei alle righe all'interno della partizione quando viene eseguita la query.
SQL Server fornisce le seguenti funzioni di classificazione :
- RANGO()
- NUMERO_RIGA()
- DENSO_RANGO()
- NTILE()
Impariamo ciascuna funzione di rango in dettaglio. Per prima cosa creeremo una tabella per la dimostrazione di tutte queste funzioni. Le seguenti istruzioni creano una tabella denominata rango_demo con tre colonne:
CREATE TABLE rank_demo ( first_name VARCHAR(60), last_name VARCHAR(40), city VARCHAR(30) );
Successivamente, inseriremo alcune righe in questa tabella come di seguito:
INSERT INTO rank_demo (first_name, last_name, city) VALUES ('Luisa', 'Evans', 'Texas'), ('Paul', 'Ward', 'Alaska'), ('Peter', 'Bennett', 'California'), ('Carlos', 'Patterson', 'New York'), ('Rose', 'Huges', 'Florida'), ('Marielia', 'Simmons', 'Texas'), ('Antonio', 'Butler', 'New York'), ('Diego', 'Cox', 'California');
Possiamo verificare la tabella utilizzando l'istruzione SELECT. Viene visualizzato l'output seguente:
RANGO() Funzione
Questa funzione viene utilizzata per determinare la classifica per ogni riga nel set di risultati. La sintassi seguente illustra l'utilizzo di una funzione RANK in SQL Server:
SELECT column_name RANK() OVER ( PARTITION BY expression ORDER BY expression [ASC|DESC]) AS 'my_rank' FROM table_name;
In questa sintassi,
- IL Clausola OVER imposta il partizionamento e l'ordinamento di un risultato prima che venga applicata la funzione della finestra associata.
- IL Clausola PARTITION BY divide l'output prodotto dalla clausola FROM nella partizione. Quindi la funzione viene applicata a ciascuna partizione e reinizializzata quando il confine della divisione attraversa le partizioni. Se non abbiamo definito questa clausola, la funzione tratterà tutte le righe come una singola partizione.
- IL ORDINATO DA è una clausola obbligatoria che determina l'ordine delle righe in modo discendente o crescente in base a uno o più nomi di colonna prima che venga applicata la funzione.
Esempio
Vediamo come funziona la funzione RANK() in SQL Server. L'istruzione seguente utilizzerà la funzione di classificazione per assegnare la numerazione a ciascuna riga:
SELECT first_name, last_name, city, RANK () OVER (ORDER BY city) AS Rank_No FROM rank_demo;
Dal momento che non abbiamo utilizzato il Clausola PARTITION BY , la funzione trattava l'intero risultato come una singola partizione. L'esecuzione dell'istruzione visualizzerà l'output seguente:
In questo output, possiamo vedere che alcune righe ottengono lo stesso rango perché hanno lo stesso valore in colonna cittadina . E il numero successivo nella classifica sarà il rango precedente più un numero di numeri duplicati.
istanza di Java
La seguente affermazione è un altro esempio dove utilizzeremo una clausola di partizione per che dividerà le righe in base a città colonna e assegnare una classifica a ciascuna riga all'interno di una partizione. L'ordine dell'output è basato su nome di battesimo :
SELECT first_name, last_name, city, RANK () OVER (PARTITION BY city ORDER BY first_name) AS Rank_No FROM rank_demo;
Verrà visualizzato l'output seguente:
NUMERO_RIGA() Funzione
Questa funzione è utilizzata per restituire il numero sequenziale univoco per ogni riga all'interno della sua partizione. La numerazione delle righe inizia da uno e aumenta di uno fino al raggiungimento del numero totale di righe della partizione. Restituirà i diversi ranghi per la riga avente valori simili che la rendono diversa dalla funzione RANK().
La sintassi seguente illustra l'uso di una funzione ROW_NUMBER() in SQL Server:
SELECT column_name ROW_NUMBER() OVER ( PARTITION BY expression ORDER BY expression [ASC|DESC]) AS 'rank_name' FROM table_name;
Esempio
Eseguire la query seguente per assegnare un numero di sequenza per ogni riga:
SELECT first_name, last_name, city, ROW_NUMBER() OVER(ORDER BY city) AS my_rank FROM rank_demo;
Si assegnerà la graduatoria per la classifica come da loro città . Qui possiamo vedere che assegna ranghi diversi alla riga che ha gli stessi valori di città.
Se cambiamo l'ordinamento da ascendente in discendente con la clausola ORDER BY, questa funzione modificherà di conseguenza anche il RANK. Vedere la dichiarazione seguente:
SELECT first_name, last_name, city, ROW_NUMBER() OVER(ORDER BY city DESC) AS my_rank FROM rank_demo;
Ecco il risultato:
DENSE_RANK() Funzione
Questa funzione assegna un rango univoco per ogni riga all'interno di una partizione secondo il valore della colonna specificato senza spazi vuoti. Specifica sempre la classifica in ordine consecutivo. Se otteniamo a valore duplicato , questa funzione gli assegnerà lo stesso rango e il rango successivo sarà il numero sequenziale successivo. Questa caratteristica differisce la funzione DENSE_RANK() dalla funzione RANK().
La sintassi seguente illustra l'uso di una funzione DENSE_RANK() in SQL Server:
SELECT column_name DENSE_RANK() OVER ( PARTITION BY expression ORDER BY expression [ASC|DESC]) AS 'rank_name' FROM table_name;
Esempio
La seguente query utilizza la funzione DENSE_RANK() per assegnare un numero di rango per ogni riga:
SELECT first_name, last_name, city, DENSE_RANK() OVER(ORDER BY city) AS my_rank FROM rank_demo;
Restituirà l'output seguente in cui possiamo vedere che i valori duplicati hanno lo stesso rango e il rango successivo sarà il numero sequenziale successivo.
È un altro esempio della funzione DENSE_RANK() che utilizza la clausola PARTITION BY. Questa clausola dividerà le righe in base alla colonna della città e l'ordine di un set di risultati è basato sul first_name:
è uguale alla stringa in Java
SELECT first_name, last_name, city, DENSE_RANK() OVER(PARTITION BY city ORDER BY first_name) AS my_rank FROM rank_demo;
Otterremo l'output seguente perché non esistono due nomi uguali. In questo caso, l'output è simile alla funzione RANK().
Aggiorniamo il nome con la seguente query:
Update rank_demo set first_name = 'Diego' WHERE city = 'California'
Ora esegui nuovamente la stessa query. Vedremo che questa tabella ha lo stesso nome Città della California . Pertanto, anche il rango è lo stesso per entrambi i nomi.
Funzione NTILE(N).
Questa funzione è utilizzata per distribuire le righe di una partizione ordinata in un numero predefinito (N) di gruppi approssimativamente uguali. Ogni gruppo di righe ottiene il proprio rango in base alla condizione definita e inizia la numerazione da un gruppo. Assegna a numero del secchio per ogni riga di un gruppo che rappresenta il gruppo a cui appartiene.
La sintassi seguente illustra l'utilizzo di una funzione NTILE() in SQL Server:
SELECT column_name NTILE(N) OVER ( PARTITION BY expression ORDER BY expression [ASC|DESC]) AS 'my_rank' FROM table_name;
Esempio
La seguente query utilizza la funzione NTILE() per assegnare un numero di classificazione per ogni riga:
SELECT first_name, last_name, city, NTILE(3) OVER(ORDER BY city) AS my_rank FROM rank_demo;
La tabella specificata ha otto record . quindi, il NILE(3) dice che il set di risultati deve avere a gruppo di tre record . L'esecuzione dell'istruzione visualizzerà l'output seguente:
Questo articolo spiegherà tutte le funzioni RANK utilizzate in SQL Server con vari esempi. Mostra anche le principali differenze tra queste funzioni. Queste funzioni sono molto utili agli sviluppatori per esplorare e gestire bene i propri dati.