Questo tutorial esplora come lavorare con le funzioni PostgreSQL. Esploriamo anche la sintassi e i parametri della funzione e guardiamo alcuni esempi pratici.
Funzione PostgreSQL Rank()
Quanto segue mostra la sintassi della funzione rank() in PostgreSQL:
RANK() OVER (PARTITION BY espressione_partizione ORDER BY espressione_ordinamento [ASC|DESC])
Iniziamo chiamando la funzione rank(). Usiamo quindi la parola chiave OVER per indicare che desideriamo eseguire un'operazione di funzione finestra.
Il prossimo è PARTITION BY partition_expression. Questa clausola divide le righe in varie partizioni in base a una data espressione. La classifica viene eseguita in modo indipendente in ogni partizione in cui il valore intero di rango inizia da 1 per ogni nuova partizione.
Infine, abbiamo la clausola ORDER BY che specifica la colonna o l'espressione che determina l'ordine delle righe.
Esempio di funzione PostgreSQL Rank()
La seguente query mostra un esempio di base dell'utilizzo della funzione rank() di PostgreSQL:
SELECT valore, RANK() OVER (ORDER BY valore) AS rangoDA (
VALORI (10), (20), (5), (15), (10)
) AS dati(valore);
In questo esempio, classifichiamo i valori in base al loro ordine crescente. Le due occorrenze del valore sono assegnate con lo stesso rango in quanto sono simili e compaiono nella stessa posizione all'interno dell'ordine.
La tabella risultante è la seguente:
SELECT valore, RANK() OVER (PARTITION BY valore % 2 ORDER BY valore) AS rangoDA (
VALORI (10), (20), (5), (15), (10)
) AS dati(valore);
Esempio di funzione PostgreSQL Rank() con la clausola Partition By
Considera il seguente esempio:
SELECT valore, RANK() OVER (PARTITION BY valore % 2 ORDER BY valore) AS rangoDA (
VALORI (10), (20), (5), (15), (10)
) AS dati(valore);
In questo esempio, abbiamo aggiunto la clausola PARTITION BY con un'espressione che controlla se il valore è un numero pari o dispari. Questo dovrebbe effettivamente raggruppare i valori in due partizioni: una per i numeri pari (dove il resto è 0) e l'altra per i numeri dispari (dove il resto è 1).
L'insieme risultante è il seguente:
Esempio di funzione PostgreSQL Rank() combinando più funzionalità SQL
Puoi anche creare query più complesse utilizzando la funzione rank(). Ad esempio, prendi il database Pagila. Supponiamo di voler recuperare i primi cinque clienti in base ai loro pagamenti totali per l'affitto e di assegnare loro dei ranghi.
Possiamo usare la funzione rank() come mostrato nella seguente query:
SELECT customer_id, first_name, last_name, total_payments,RANK() OVER (ORDER BY total_payments DESC) AS rango
DA (
SELECT c.customer_id, c.first_name, c.last_name, SUM(p.amount) AS total_payments
DAL cliente c
JOIN pagamento p ON c.customer_id = p.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
) AS clienti_pagamenti
ORDINA PER rango
LIMITE 10;
In questo esempio, selezioniamo customer_id, first_name, last_name, total_payment e il rank() assegnato. Quindi, usiamo la funzione rank() per assegnare un rango a ciascun cliente in base al total_payment che viene ordinato in ordine decrescente.
La sottoquery interna ci consente di calcolare i pagamenti totali per ogni cliente unendo le tabelle cliente e pagamento. Quindi raggruppiamo il risultato in base alle colonne customer_id, first_name e last_name per recuperare la somma dei pagamenti per ciascun cliente.
Infine, nella query esterna, applichiamo la funzione rank() sul set di risultati e lo ordiniamo per total_payments in ordine crescente. Includiamo anche la clausola limite per recuperare solo le prime 10 righe.
Come puoi vedere, puoi generare dati più approfonditi combinando più funzionalità SQL come ordinamento, filtraggio, aggregazioni, join e altro.
Conclusione
Abbiamo esplorato come possiamo lavorare con la funzione rank() in PostgreSQL per recuperare il rango di una determinata riga da un set di risultati in base alle condizioni definite. Abbiamo anche spiegato come combinare la funzione rank() con altri strumenti SQL per creare query più complesse.